- 최초 작성일: 2002-10-22
- 최종 수정일: 2026-09-29
- 조회수: 31 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 배열 수식으로 그룹별 순위 매기기 (RANK 응용)
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
Nature never deceives us : it is always we who deceive ourselves.
자연은 결코 우리를 기만하지 않는다. 자기를 기만하는 것은 늘 우리 자신이다.
자연은 참으로 정확하고 정직하다는 생각이 듭니다.
天災라는 것도 촐싹대는 인간의 관점에서 보니까 그런 것이지 한발짝 뒤로 물러서서 보면 스스로를 정화시키는 과정이지요.
- 최신 글을 불러오는 중...
배열 수식으로 그룹별 순위 매기기 (RANK 응용)
핵심 요약
여러 그룹이 섞여 있는 데이터에서 그룹마다 RANK 함수를 따로 적용할 필요 없이, SUM과 N 함수를 조합한 배열 수식 하나로 각 행의 그룹 내 순위를 한 번에 구할 수 있습니다.
- 핵심 수식: {=SUM(N(값<범위)*(그룹범위=자기그룹))+1}
- 입력 방법: 일반 수식처럼 입력한 뒤 Ctrl+Shift+Enter로 확정(배열 수식)
- 장점: 그룹이 불연속 영역에 흩어져 있어도 그대로 적용 가능
질문: 그룹별로 RANK 순위를 자동으로 구하고 싶어요
질문은 그룹별 RANK 함수 사용방법입니다. 아래와 같이 그룹, 평점, 순위 데이터가 있습니다.
| 그룹 | 평점 | 순위 |
|---|---|---|
| A1 | 99 | 1 |
| A1 | 66 | 4 |
| A1 | 77 | 3 |
| A1 | 82 | 2 |
| B1 | 10 | 3 |
| B1 | 80 | 1 |
| B1 | 20 | 2 |
연속되는 줄에서 A1그룹의 순위, B1그룹의 순위를 자동으로 구할 순 없나요? 단순히 A1그룹의 범위를 지정해 RANK 함수를 쓰고 B1그룹의 범위를 구해서 반복되는 RANK 작업 말고, 중첩함수식(예: RANK, MATCH)으로 응용하거나 사용자 정의 함수로 손쉽게 작업할 수 있는지 궁금합니다.
없을 리가 없겠지요? ^^V 아래의 표를 보세요. 자료를 조금 더 복잡하게 만들어 보았습니다. 4개의 그룹이 있으며 C1 그룹과 D1 그룹은 불연속적인 영역에 위치하고 있습니다. 하지만 데이터를 보니까 각 그룹별로 순위가 매겨져 있음을 알 수 있습니다.
| 그룹 | 평점 | 순위(RANK) | 그룹별 순위(배열수식) |
|---|---|---|---|
| A1 | =INT(RAND()*1000) | =RANK(C42,$C$42:$C$55) | {=SUM(N(C42<$C$42:$C$55)*($B$42:$B$55=B42))+1} |
| A1 | =INT(RAND()*1000) | =RANK(C43,$C$42:$C$55) | {=SUM(N(C43<$C$42:$C$55)*($B$42:$B$55=B43))+1} |
| A1 | =INT(RAND()*1000) | =RANK(C44,$C$42:$C$55) | {=SUM(N(C44<$C$42:$C$55)*($B$42:$B$55=B44))+1} |
| A1 | =INT(RAND()*1000) | =RANK(C45,$C$42:$C$55) | {=SUM(N(C45<$C$42:$C$55)*($B$42:$B$55=B45))+1} |
| B1 | =INT(RAND()*1000) | =RANK(C46,$C$42:$C$55) | {=SUM(N(C46<$C$42:$C$55)*($B$42:$B$55=B46))+1} |
| B1 | =INT(RAND()*1000) | =RANK(C47,$C$42:$C$55) | {=SUM(N(C47<$C$42:$C$55)*($B$42:$B$55=B47))+1} |
| B1 | =INT(RAND()*1000) | =RANK(C48,$C$42:$C$55) | {=SUM(N(C48<$C$42:$C$55)*($B$42:$B$55=B48))+1} |
| B1 | =INT(RAND()*1000) | =RANK(C49,$C$42:$C$55) | {=SUM(N(C49<$C$42:$C$55)*($B$42:$B$55=B49))+1} |
| C1 | =INT(RAND()*1000) | =RANK(C50,$C$42:$C$55) | {=SUM(N(C50<$C$42:$C$55)*($B$42:$B$55=B50))+1} |
| D1 | =INT(RAND()*1000) | =RANK(C51,$C$42:$C$55) | {=SUM(N(C51<$C$42:$C$55)*($B$42:$B$55=B51))+1} |
| D1 | =INT(RAND()*1000) | =RANK(C52,$C$42:$C$55) | {=SUM(N(C52<$C$42:$C$55)*($B$42:$B$55=B52))+1} |
| C1 | =INT(RAND()*1000) | =RANK(C53,$C$42:$C$55) | {=SUM(N(C53<$C$42:$C$55)*($B$42:$B$55=B53))+1} |
| C1 | =INT(RAND()*1000) | =RANK(C54,$C$42:$C$55) | {=SUM(N(C54<$C$42:$C$55)*($B$42:$B$55=B54))+1} |
| D1 | =INT(RAND()*1000) | =RANK(C55,$C$42:$C$55) | {=SUM(N(C55<$C$42:$C$55)*($B$42:$B$55=B55))+1} |
수식 뜯어보기
입력된 수식을 살펴 볼까요?
{=SUM(N(C38<$C$38:$C$51)*($B$38:$B$51=B38))+1}수식 좌우에 {}가 있는 것으로 보아 배열 수식이로군요. N이라는 생소한 함수가 나왔습니다. 이것은 '숫자로 변환된 값'을 구해주는 함수로, 사실 위와 같은 경우에는 없어도 상관없으나 만약을 대비하여 써 준 것입니다. N 함수에 대한 보다 상세한 설명을 보시려면 x0226 강좌를 참고하세요.
그 나머지 부분에 대해서는 배열 수식에 대해 조금만 조예가 있는 분이라면 설명드리지 않아도 아시리라 생각됩니다. 즉석 계산 기능, 즉 수식 편집 상태에서 위 색칠한 부분만 각각 범위를 정하고, <F9> 키를 눌러가며 확인해 보시면 쉽게 이해가 되실 것입니다.
정리 — 배열 수식으로 그룹별 순위 구하기
| 구성 요소 | 역할 |
|---|---|
N(값 < 전체범위) |
자기보다 큰 값들을 TRUE/FALSE로 비교 후 1/0으로 변환 |
(그룹범위 = 자기그룹) |
전체 데이터 중 같은 그룹인 행만 걸러냄 |
SUM(...)+1 |
같은 그룹 내에서 나보다 높은 값의 개수를 세어 순위로 환산 |
| Ctrl+Shift+Enter | 배열 수식으로 확정({} 표시가 자동으로 붙음) |
자주 묻는 질문 (FAQ)
Q1. 그룹마다 RANK 함수를 따로 걸지 않고 한 번에 순위를 구할 수 있나요?
네. 그룹이 섞여 있어도 SUM과 N 함수를 조합한 배열 수식 하나를 전체 범위에 채우기만 하면, 각 행마다 자기 그룹 안에서의 순위를 자동으로 계산해 줍니다.
Q2. 수식 앞뒤의 중괄호 {}는 직접 입력하나요?
아닙니다. 중괄호는 배열 수식임을 나타내는 표시로, 수식을 입력한 뒤 Ctrl+Shift+Enter를 눌러야 자동으로 붙습니다. 직접 타이핑하면 오류가 납니다.
Q3. 수식 안의 N 함수는 꼭 필요한가요?
이 경우에는 없어도 결과에는 차이가 없지만, 비교 결과(TRUE/FALSE)를 숫자로 확실히 변환해 계산 오류를 예방하기 위해 습관적으로 넣어 준 것입니다. N 함수에 대한 자세한 설명은 x0226 강좌를 참고하세요.
마치며
그룹이 몇 개든, 심지어 데이터가 뒤섞여 있어도 배열 수식 하나면 그룹별 순위를 한 번에 구할 수 있습니다. RANK 함수를 그룹마다 반복해서 걸고 계셨다면, 오늘 소개한 수식으로 한결 가볍게 정리해 보세요!
