최초 작성일: 2000-02-09
최종 수정일: 2026-09-29
조회수: 20 회
작성자: 권현욱 (엑셀러)
강의 제목: 부서별 상위 3명 추려내기

들어가기 전에

오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.

설 연휴 잘들 쉬셨는지요? 어떤 경우이든 현재의 생활, 지금 이 순간을 충실히 보내는 것이 가장 현명하게 사는 방법이 아닌가 싶습니다. 뉴밀레니엄이 어떻고 저떻고하며 수선을 떨어도 한 며칠만 지나면 이렇게 담담해지지 않습니까. 누군가가 그러더군요 "행복이라는 것은 행복했던 순간의 총량"이라고. 매 순간순간 행복을 느끼며 사시기를…

설연휴 기분을 털어내고 현실로 돌아와서.. 연휴기간 동안 잠들어 있던 두뇌를 깨우기 위해 약간 머리를 써야하는 문제를 하나 풀어보도록 할까요? ^^

오늘 설명드릴 내용은 처음보면 좀 까다롭게 느껴지실지도 모르겠습니다. 아주 오래 전에 설명드린 배열수식(Array Formula)과 몇가지 함수들의 조합을 통한 자료추출에 대해 설명드립니다. 이것을 잘 이해하시면 또 한단계 도약입니다.

어느 분이 보내오신 질문메일을 약간 수정해서 띄웁니다. 혹시 질문하시는 분 중에서 자신의 자료는 다른 사람이 보면 안되겠다 싶은 것은 꼭 명기해 주시기 바랍니다. 안 그러면 제 맘대로(?) 인용을 할 테니깐…

질문을 살펴볼까요.

저자: 권현욱(엑셀러), 아이엑셀러 대표

필자는 Excel 컨설턴트, 작가, 그리고 크리에이터입니다. 현재 Microsoft Excel MVP이며, 『챗GPT+엑셀 업무자동화 정석』을 비롯한 10여 권의 도서를 집필했습니다. Excel 자동화 및 생산성 향상 분야에서 25년 넘는 경력을 보유하고 있습니다.

권현욱(엑셀러) 님의 최신 포스트:

최신 글을 불러오는 중...

부서별 상위 3명 추려내기

핵심 요약

IF 배열 조건으로 특정 조직에 해당하는 실적만 먼저 추려낼 수 있습니다.
LARGE 함수를 배열수식과 결합하면 조직별 1위, 2위, 3위 값을 차례로 구할 수 있습니다.
배열수식은 수식 입력 후 Ctrl+Shift+Enter로 확정하고 순위 인수만 바꾸어 아래 순위를 계산합니다.

<표1>은 소스데이터로써 각조직별/관리자별 실적을 집계한 것이고 <표2>는 출력할 양식입니다. 질문하신 분이 원하는 것은, 각 조직별로 상위자 3등 까지의 실적을 어떻게 쉽고 효율적으로 추출할 수 없는가 하는 것입니다.

어떻게 하면 될까요? 깝깝한 노릇이지요. 조직이 한두개도 아니고, 더군다나 각 조직별로 관리자 수도 만만치가 않은데 말입니다.

여기서는 설명의 편의상 질문하신 분이 보내온 자료를 축약해서 표시한 것입니다. 원래 자료는 "조직"이 35개 정도, "관리자명"이 600명 정도되는 것이었습니다. 이 정도되는 자료를 수작업으로 처리한다면? 생각만해도 끔찍하시지요? 한숨이 앞을 가리지요? "또 밤새는겨?" 하시겠지요? ^^

이제 이런 일로 밤을 새는 일은 없어야겠지요 적어도 우리 회사에서는요!!!

이 과제를 해결하기 위해서는, (1) 배열수식(Array Formula) (2) Large 함수 (3) (다중)If 함수 이 세 가지를 확실히 알고 있어야 합니다.

<표1> 소스 데이터				<표2> 추출 데이터
조직	관리자명	직급	실적	관리조직	처별 1위	처별 2위	처별 3위
0013	A92	010150	12,650,000
0013	A104	010150	11,800,000	0013
0013	A118	010150	11,305,000	0025
0013	A232	010150	7,400,000	0028
0013	A248	010150	7,025,000	0030
0013	A306	010150	5,715,000	0031
0013	A371	010150	4,025,000	0051
0013	A375	010150	3,955,000	0081
0013	A380	010150	3,810,000	0082
0013	A445	010150	1,595,000	0083
0013	A483	010150	0	1011
0013	A509	010150	0	1012
0013	A591	010150	0	1014
0025	A116	010150	11,375,000	1015
0025	A121	010150	11,110,000	1016
0025	A228	010150	7,540,000	1017
0025	A271	010150	6,525,000	1018
0025	A304	010150	5,790,000	1052
0025	A314	010150	5,575,000	1110
0025	A382	010150	3,745,000	1111
0025	A392	010150	3,530,000	1112
0025	A418	010150	2,625,000	1114
0025	A447	010150	1,435,000	1210
0025	A495	010150	0	1211
0025	A504	010150	0	1213
0025	A559	010150	0	1214
0025	A561	010150	0	6010
0025	A579	010150	0	6011
0028	A17	010150	24,300,000	6012
0028	A40	010150	18,545,000	6013
0028	A58	010150	15,515,000	6014

하나씩 살펴보도록 하지요.

<표1> 소스 데이터				<표2> 추출 데이터
조직	관리자명	직급	실적	관리조직	처별 1위	처별 2위	처별 3위
0013	A92	010150	12,650,000	0013	12,650,000	24,875,000	24,300,000
0013	A104	010150	11,800,000	0025
0013	A118	010150	11,305,000	0028
0013	A232	010150	7,400,000	0030
0013	A248	010150	7,025,000	0031
0013	A306	010150	5,715,000	0051
0013	A371	010150	4,025,000	0081
0013	A375	010150	3,955,000	0082
0013	A380	010150	3,810,000	0083
0013	A445	010150	1,595,000	1011
0013	A483	010150	0	1012
0013	A509	010150	0	1014
0013	A591	010150	0	1015
0025	A116	010150	11,375,000	1016
0025	A121	010150	11,110,000	1017
0025	A228	010150	7,540,000	1018
0025	A271	010150	6,525,000	1052
0025	A304	010150	5,790,000	1110
0025	A314	010150	5,575,000	1111
0025	A382	010150	3,745,000	1112
0025	A392	010150	3,530,000	1114
0025	A418	010150	2,625,000	1210
0025	A447	010150	1,435,000	1211
0025	A495	010150	0	1213
0025	A504	010150	0	1214
0025	A559	010150	0	6010
0025	A561	010150	0	6011
0025	A579	010150	0	6012
0028	A17	010150	24,300,000	6013
0028	A40	010150	18,545,000	6014
0028	A58	010150	15,515,000	6015
0028	A59	010150	15,325,000	7010
0028	A80	010150	13,645,000	7012
0028	A91	010150	12,735,000	7013
0028	A101	010150	11,910,000	총 합계
0028	A127	010150	10,980,000
0028	A151	010150	10,265,000
0028	A155	010150	10,110,000
0028	A190	010150	8,800,000
0028	A202	010150	8,515,000
0028	A213	010150	8,150,000
0028	A214	010150	8,075,000
0028	A240	010150	7,210,000
0028	A242	010150	7,160,000
0028	A259	010150	6,765,000
0028	A270	010150	6,525,000
0028	A288	010150	6,135,000
0028	A299	010150	5,880,000
0028	A305	010150	5,770,000
0028	A308	010150	5,660,000
0028	A354	010150	4,500,000
0028	A374	010150	3,960,000
0028	A383	010150	3,715,000
0028	A390	010150	3,550,000
0028	A457	010150	885,000
0028	A465	010150	560,000
0028	A487	010150	0
0028	A494	010150	0
0028	A508	010150	0
0028	A512	010150	0
0028	A520	010150	0
0030	A129	010150	10,965,000
0030	A134	010150	10,840,000
0030	A169	010150	9,630,000
0030	A193	010150	8,665,000
0030	A207	010150	8,430,000
0030	A217	010150	8,025,000
0030	A285	010150	6,195,000
0030	A346	010150	4,840,000
0030	A410	010150	2,800,000
0030	A426	010150	2,465,000
0030	A476	010150	60,000
0030	A556	010150	0
0031	A14	010150	25,870,000
0031	A16	010150	24,875,000
0031	A21	010150	22,695,000
0031	A25	010150	21,885,000
0031	A32	010150	19,935,000
0031	A47	010150	17,510,000
0031	A60	010150	15,245,000
0031	A67	010150	14,765,000
0031	A70	010150	14,650,000
0031	A71	010150	14,495,000
0031	A123	010150	11,080,000
0031	A176	010150	9,410,000
0031	A216	010150	8,065,000
0031	A268	010150	6,530,000
0031	A309	010150	5,645,000
0031	A352	010150	4,590,000
0031	A525	010150	0
0051	A68	010150	14,715,000
0051	A76	010150	14,075,000
0051	A83	010150	13,525,000
0051	A88	010150	13,005,000

참고: 위 표에 보이는 0013의 처별 2위와 3위 값(24,875,000과 24,300,000)은 표1의 0013 조직 실적과 맞지 않습니다. 표1의 0013 조직 값으로 계산하면 1위 12,650,000, 2위 11,800,000, 3위 11,305,000입니다. 또한 본문의 수식 예에는 G76, D163, B164처럼 서로 다른 셀 참조가 쓰여 있는데, 원문 워크시트의 서로 다른 위치에서 옮겨 온 것으로 보이며 실제로는 각 행의 관리조직 코드가 있는 셀을 참조하면 됩니다. 아래 본문의 빨간 동그라미는 원문 그림의 표시로 이 페이지에는 남아 있지 않으며, 수식 끝의 순위 인수(2, 3)를 뜻합니다. 표는 설명을 위해 원래 자료의 일부만 옮긴 것입니다.

이 과제를 해결하기 위해서는 배열수식, large()함수, if()함수에 대해 완전히 이해하고 있어야 한다고 설명을 드렸지요? 배열수식과 if 함수는 다들 아실테고… 혹시나도 '그런걸 언제 갈쳐줬어?' 하시는 분이 있으면 지난 강좌파일들과 도움말을 참조하세요. 분명히 설명을 드렸으니까.

Large() 함수는 주어진 배열요소 중에서 n번째 큰 값을 return해 주는 함수입니다. (배열요소가 어떻고 뭐 n번째 값을 또 뭐 return 어째?) 딸깍, 딸깍(←먼 소리? 골치 아파서 파일 다시 닫는소리!)

아직 시작도 안했는데 벌써 골치 아프지요? 이 고비를 잘 넘기셔야 합니다. 서두에서 언급했다시피 이 단계를 잘 소화하면 한단계 도약입니다. 도약하는 것이 그렇게 쉽다면 개나 걸이나 다 하게요? ^^

쉽게 말해서 "여러 값들 중에서 n번째로 큰 값이 무어냐?"에 대한 해답을 알려주는 함수가 바로 Large 함수입니다. 아무 셀에나 가서

=LARGE({1,2,3,4,5},3)

라고 입력하고 엔터키를 탁 쳐보세요.

3	`=LARGE({1,2,3,4,5},3)`

즉 "{1,2,3,4,5} 중에서 3번째로 큰 값을 구하라"고 명령을 내리니까 "3"하고 대답하는 것이지요.(기특하게시리…)

그러면 "처별 1위"를 구하는 공식을 들여다 볼까요.

{=LARGE(IF(G76=$B$39:$B$560,$E$39:$E$560),1)}

무슨 암호문 같지요? 안에서부터 하나씩 분해해서 살펴보면…

① if(g76=$b$39:$b$560,$e$39:$e$560) b39:b560 영역(셀들) 중에서 셀값이 g76셀(즉 조직코드가 "0013")과 같은 e39:e560 즉, 실적을 먼저 추려냅니다. 이 때 이 추려낸 값을 화면상에 바로 보여주는 것이 아니고 메모리 상에 보관하고 있습니다.

② 그런 다음 large(배열요소,1) 이니까, ①에서 구해진 실적들 중에서 1번째로 큰 값을 구해주는 것입니다.

③ 여기까지 입력하셨으면 바로 엔터키를 치는 것이 아니고, Ctrl + Shift +Enter키를 함께 쳐 주어야 합니다(→매우 중요). 그래야 컴퓨터(EXCEL)가 '아, 이 부분은 배열수식이로구나'하고 인식을 합니다.

그리고 공식 맨앞과 맨뒤의 중괄호({})는 손으로 입력한 것이 아니고 Ctrl + Shift + Enter키를 함께 치면 저절로 생겨납니다.

이렇게 하면 관리조직이 "0013"이고 실적이 가장 높은 값이 자동으로 구해집니다. 이제 이 공식을 아래로 주욱 복사하면 각 관리조직에서 실적이 가장 좋은 것만 나타나게 되지요.

다음으로 각 조직별 2위와 3위 실적은 1과 거의 같습니다. 아래에서 빨간 동그라미 부분, 즉 2번째로 큰 값인가 3번째로 큰 값인가 하는 부분만 손을 봐준 후 마찬가지로 공식을 아래로 주~욱 복사하면 끝!

=LARGE(IF(D163=$B$39:$B$560,$E$39:$E$560),2)

=LARGE(IF(B164=$B$39:$B$560,$E$39:$E$560),3)

이상입니다. 어떠신지… 이번 시간은 여기까지…

정리 — 부서별 상위 3명 추려내기

구분	내용
문제	조직별로 실적 상위 3명의 값을 추출
필요한 기능	배열수식, LARGE 함수, IF 함수
1위 수식	`{=LARGE(IF(G76=$B$39:$B$560,$E$39:$E$560),1)}`
2위와 3위	수식 끝의 순위 인수를 2, 3으로 변경
IF의 역할	해당 조직 코드와 같은 행의 실적만 추려서 메모리에 보관
LARGE의 역할	추려낸 실적 중 n번째로 큰 값을 반환
입력 방법	Ctrl + Shift + Enter 키로 입력하고 아래로 복사