엑셀 데이터에서 상위 3등까지의 합계나 하위 3등의 평균을 구하느라 일일이 정렬하고 계셨나요? SUM, AVERAGE 함수와 LARGE, SMALL 함수를 배열상수({})로 묶어 정렬 없이 1초 만에 상위/하위 N개의 데이터를 집계하는 실무 치트키를 대공개합니다.
1. 정렬 노가다를 없애주는 핵심 엔진: LARGE 함수와 SMALL 함수의 기본 원리
수식을 조립하기 전에, 오늘 우리의 손과 발이 되어줄 두 가지 핵심 함수인 LARGE와 SMALL의 메커니즘을 정확히 이해해야 합니다. 보통 몇 번째로 큰 값을 찾을 때 사용하는 함수들인데요.
- LARGE 함수 기본 구조:
=LARGE(범위, n)→ 지정한 범위에서 'n번째'로 큰 값을 반환합니다. - SMALL 함수 기본 구조:
=SMALL(범위, n)→ 지정한 범위에서 'n번째'로 작은 값을 반환합니다.
예를 들어 점수 범위가 지정되어 있을 때 =LARGE(범위, 1)이라고 쓰면 전체 1등 점수가 나오고, =LARGE(범위, 3)이라고 쓰면 딱 3등의 점수 '하나'만 쏙 뽑아내 줍니다. 하지만 우리가 원하는 것은 3등의 점수 하나가 아니라, 1등, 2등, 3등의 점수를 '모두' 모아서 한 번에 더하거나 평균을 내는 것입니다. 그렇다면 이 세 개의 값을 어떻게 함수에 한 번에 집어넣을 수 있을까요? 여기서 엑셀 고수들만 아는 비밀무기인 '배열상수' 개념이 등장합니다.
2. 중첩 수식의 핵심 키포인트: 배열상수 괄호 `{ }` 완벽 분석
1등, 2등, 3등의 데이터를 동시에 엑셀에게 인식시키려면 숫자를 쉼표로 나열한 뒤, 일반 괄호가 아닌 물결 모양의 중괄호 `{ }`로 감싸주어야 합니다. 이를 엑셀에서는 '배열상수(Array Constant)'라고 부릅니다.
수식을 작성할 때 단순히 1, 2, 3이라고 쓰면 엑셀은 이를 인식하지 못하거나 에러를 내지만, `{1,2,3}`이라고 묶어서 LARGE 함수의 n 자리에 넣어주면, 엑셀 내부적으로는 [1등 점수, 2등 점수, 3등 점수]라는 3개의 값 주머니를 한 번에 만들어 냅니다. 이 원리를 활용해 합계 함수인 SUM과 조합하면 다음과 같은 완벽한 상위 합계 공식이 완성됩니다.
💡 상위 3명 점수 합계 최종 공식: =SUM(LARGE(점수범위, {1,2,3}))
이 수식의 내부 동작을 쪼개어 보면 엑셀이 얼마나 영리하게 움직이는지 알 수 있습니다. 엑셀은 먼저 내부의 LARGE(점수범위, {1,2,3})을 실행하여 범위 내의 1등, 2등, 3등 점수를 각각 추출합니다. 만약 1등이 95점, 2등이 90점, 3등이 85점이라면 엑셀 내부 주머니에는 `{95, 90, 85}`라는 데이터 배열이 저장됩니다. 그다음, 바깥을 감싸고 있는 SUM 함수가 이 주머니 안의 숫자들을 가볍게 모두 더하여(`95 + 90 + 85 = 270`) 최종 결과값을 화면에 띄워주는 것입니다. 정렬을 전혀 하지 않았는데도 상위권 점수만 쏙 골라 더해지는 마법 같은 순간이죠.
3. 응용 확장! 상위/하위 평균 구하기 및 N개 개수 늘리는 실무 팁
이 마스터 수식의 구조를 완벽하게 이해하셨다면, 회사의 다양한 요구사항에 맞춰 수식을 자유자재로 변형하는 것은 식은 죽 먹기입니다. 함수 이름과 배열상수의 숫자만 쓱 갈아끼워 주면 되기 때문입니다.
- 상위 3명의 평균 점수를 구해라!
바깥의 SUM 함수를 평균 함수인 AVERAGE로만 교체해 주면 됩니다.
👉 수식:=AVERAGE(LARGE(점수범위, {1,2,3})) - 하위 3명의 점수 합계와 평균을 구해라!
큰 값을 구하는 LARGE 대신, 작은 값을 추적하는 SMALL 함수로 엔진을 바꿔줍니다.
👉 하위 합계:=SUM(SMALL(점수범위, {1,2,3}))
👉 하위 평균:=AVERAGE(SMALL(점수범위, {1,2,3}))
💡 여기서 드리는 실무 스케일업 팁!
만약 팀장님이 "최 대리, 3명 말고 상위 5명이나 하위 10명으로 기준을 늘려서 다시 계산해 봐"라고 지시한다면 어떻게 해야 할까요? 당황하지 마시고 중괄호 안의 배열 범위만 늘려주시면 됩니다. 상위 5명이라면 중괄호 안을 `{1,2,3,4,5}`로 적어주시면 되고, 하위 10명이라면 `{1,2,3,4,5,6,7,8,9,10}`까지 숫자를 채워 넣어주시면 아주 간단하게 확장됩니다.
비즈니스 통계 데이터를 다룰 때, 매번 마우스 우클릭으로 정렬을 필터링하며 원본 표를 망가뜨리지 마세요. 오늘 함께 심도 있게 알아본 [SUM/AVERAGE + LARGE/SMALL + 배열상수] 삼총사 조합만 잘 기억해 두신다면, 아무리 방대한 로우 데이터가 밀려와도 내가 원하는 핵심 상위/하위 지표를 가장 안전하고 우아하게 추출해 낼 수 있습니다. 내일 출근해서 보고서 작성하실 때 꼭 서먹했던 중괄호`{}` 키를 눌러 적용해 보시고, 에러 없는 완벽한 데이터로 칭찬받으며 기분 좋게 칼퇴하시길 바랍니다. 감사합니다! :)