- 최초 작성일: 2025-01-25
- 최종 수정일: 2026-09-24
- 조회수: 31 회
- 작성자: 권현욱 (엑셀러)
- 강의 제목: 다양한 방법으로 지역별 순위 매기기
들어가기 전에
오래 전 엑셀 파일로 진행하던 강의를 웹 글로 복원한 것이라, "버튼을 눌러보세요"처럼 엑셀에서 직접 조작하라는 표현이 남아 있을 수 있습니다. 결과는 본문의 그림이나 표로 확인하실 수 있으며, 현재와 맞지 않는 내용은 보완하되 전반적인 스타일은 당시의 갬성(?)을 유지했습니다.
엑셀에서 순위를 구하고자 할 때에는 Rank 함수를 이용하면 쉽게 해결할 수 있습니다. 하지만 다음 테이블을 보세요.
![[완성 예] 지역별로 순위 구하기 표](https://www.iexceller.com/myxls/excel/practical/x0536/images/x0536_01_final_example_ranking_table.webp)
[완성 예] 지역별로 순위 구하기
각 지역별로 순위가 매겨져있습니다. 이것은 어떻게 한 것일까요?
- 1) 그간 숙달된 손과 눈을 부지런히 움직여 등수를 직접 입력한다.
- 2) 각 지역별 달성률을 범위로 지정하고 Rank 함수를 '지역' 숫자만큼 적용한다.
설마 이런 노동집약적(?)이거나 과격한 방법을 사용했을까요? 데이터량이 적거나 지역이 몇개 되지 않는 경우라면 그렇게 해도 되기는 하겠지만, 전혀 '엑셀스럽지 않은' 방법이라 하겠습니다.
이번 강좌에는 세 가지 방법으로 이 문제를 해결해보겠습니다.
- 최신 글을 불러오는 중...
다양한 방법으로 지역별 순위 매기기
핵심 요약: 그룹별 순위를 구하는 세 가지 방법
RANK 함수 하나로는 그룹별 순위를 구할 수 없습니다. SUMPRODUCT 조건 비교, 피벗 테이블 값 표시 형식, 배열 수식 세 가지 방법으로 같은 지역 안에서만 순위를 매길 수 있습니다.
- 방법 1: SUMPRODUCT(($A$2:$A$17=A2)*(E2<$E$2:$E$17))+1
- 방법 2: 피벗 테이블 — 값 표시 형식 → 내림차순 순위 지정(기준 필드: 매장)
- 방법 3: 같은 수식을 <Ctrl>+<Shift>+<Enter>로 입력하는 배열 수식
방법 1: 일반 함수 사용
'구리점'의 순위가 입력될 셀(F2)에 다음 수식을 입력하고 수식을 아래로 복사합니다.
=SUMPRODUCT(($A$2:$A$17=A2)*(E2<$E$2:$E$17))+1A2:A17 영역 중에서 A2셀 값과 같은지 비교해서(조건 비교), E2 셀에 들어있는 값보다 큰 것이 있다면 숫자를 1씩 증가시킵니다(순위 계산). 이 때 작업 대상 영역의 셀 주소는 '절대 참조'로, 개별 셀 주소는 '상대 참조' 형태로 지정한 점에 유의하세요.
방법 2: 피벗 테이블 사용
(1) 소스 데이터를 이용하여 그림과 같은 형태의 피벗 테이블을 작성합니다. '행 필드'에는 '지역'과 '매장', '데이터 필드'에는 '달성률' 필드를 각각 지정하였습니다.
(2) 값 필드 영역을 마우스 오른쪽 버튼으로 클릭하고 '값 표시 형식 - 내림차순 순위 지정' 메뉴를 선택합니다.
(3) '값 표시 형식' 대화상자에서 '필드 - 매장'을 선택하고 '확인' 버튼을 클릭합니다. 지역별로 순위가 구해집니다.
방법 3: 배열 수식 사용
배열 수식(Array Formula)을 이용하여 해결할 수도 있습니다. F2 셀에 { }를 제외한 나머지 수식을 입력하고 <Ctrl> + <Shift> + <Enter> 키를 함께 누른 후, 수식을 아래로 복사합니다. 중괄호 { }를 직접 입력하지 않도록 주의하세요!
=SUMPRODUCT(($A$2:$A$17=A2)*(E2<$E$2:$E$17))+1수식이 작동되는 원리는 Sumproduct 함수의 경우와 비슷합니다. 수식이 잘 이해되지 않는 분은 수식 입력줄에서 '(E2<$E$2:$E$17)' 부분과 '($A$2:$A$17=A2))+1' 부분을 각각 범위로 지정한 다음, <F9> 키를 눌러가며 차근차근 분석해 보시기 바랍니다.
이상의 방법 말고 다른 방법으로도 해결 가능합니다. 다른 좋은 아이디어가 떠오른 분은 공유해 주세요.
참고할 강좌
아주 오래 전에 만든 것이라 Zip 파일로 압축되어 있습니다. PC 환경에서 열어보세요.
참고: 이미지에 대하여
이 글은 원래 네이버 포스트에 게재되었던 글로, 네이버 포스트 서비스 종료로 네이버 블로그로 옮기는 과정에서 원본 이미지가 소실되었습니다. 위 이미지는 본문 설명을 바탕으로 재구성한 예시이며, 실제 데이터와는 다를 수 있습니다.
정리 — 그룹별 순위를 구하는 세 가지 방법
| 방법 | 핵심 동작 |
|---|---|
| 일반 함수 | SUMPRODUCT(($A$2:$A$17=A2)*(E2<$E$2:$E$17))+1 |
| 피벗 테이블 | 값 표시 형식 → 내림차순 순위 지정 → 기준 필드: 매장 |
| 배열 수식 | 같은 수식을 Ctrl+Shift+Enter로 입력 |
자주 묻는 질문 (FAQ)
Q1. RANK 함수로는 왜 그룹별 순위를 바로 구할 수 없나요?
RANK 함수는 지정한 범위 전체를 기준으로 순위를 매기기 때문에, 지역처럼 그룹별로 따로 순위를 매기려면 지역마다 범위를 나눠 RANK를 여러 번 적용해야 합니다. SUMPRODUCT 등을 쓰면 조건(같은 지역인지)과 비교(값이 더 큰지)를 함께 계산해 한 번에 그룹별 순위를 구할 수 있습니다.
Q2. SUMPRODUCT(($A$2:$A$17=A2)*(E2<$E$2:$E$17))+1 수식은 어떻게 작동하나요?
A2:A17 범위에서 현재 행(A2)과 같은 지역인 행을 찾고, 그 중에서 현재 행의 달성률(E2)보다 큰 값의 개수를 셉니다. 그 개수에 1을 더하면 같은 지역 안에서의 순위가 됩니다. 범위는 절대 참조로, 비교 대상 셀은 상대 참조로 지정하는 점이 핵심입니다.
Q3. 피벗 테이블로 그룹별 순위를 구하려면 어떻게 하나요?
행 필드에 지역과 매장을, 데이터 필드에 달성률을 넣어 피벗 테이블을 만든 뒤, 값 필드를 마우스 오른쪽 버튼으로 클릭해 '값 표시 형식 - 내림차순 순위 지정'을 선택합니다. '값 표시 형식' 대화상자에서 기준 필드를 '매장'으로 지정하면 지역별로 순위가 계산됩니다.
마치며
VBA에 대한 기초 지식을 공부하실 분은 아이엑셀러 닷컴 사이트 상단 메뉴에서 [Excel 강의] - [Excel 입문]을 먼저 보시면 이해하기 쉽습니다.
