이번 포스팅은 엑셀의 순위, 위치 관련 함수 입니다..
여러 데이터중에 원하는 값을 추출할때 쓰이는 함수인데, 가령 데이터중 몇번째 값, 최저값, 최대값 등등도 표현가능 합니다.
엑셀의 순위 및 위치 관련 함수는 데이터 집합 내에서 값의 상대적 위치, 즉 순위를 계산하거나 특정 순위의 값을 추출하는 데 사용됩니다. 이 함수들은 성적, 판매량, 점수, 매출 등 다양한 실무 데이터에서 상위/하위 순위 분석, 성과 평가, 경쟁력 비교 등에 널리 활용됩니다. 대표적인 함수로 RANK, RANK.EQ, RANK.AVG, LARGE, SMALL, PERCENTRANK 등이 있습니다. 아래에서 각 함수의 원리와 구체적 예시를 5개씩 들어 자세히 설명합니다.
1. RANK, RANK.EQ, RANK.AVG 함수
기능 및 특징
- RANK: 값이 데이터 집합에서 몇 번째 순위인지 반환합니다. (내림차순/오름차순 선택 가능)
- RANK.EQ: RANK 함수와 동일하며, 동점자에게 동일한 순위를 부여합니다. (엑셀 2010 이후 표준)
- RANK.AVG: 동점자에게 평균 순위를 부여합니다.
기본 구문
- =RANK(number, ref, [order])
- =RANK.EQ(number, ref, [order])
- =RANK.AVG(number, ref, [order])
예제 5개
- 성적 순위 계산
text
=RANK(B2, $B$2:$B$6)
B2 셀의 점수가 B2~B6 범위에서 몇 등인지 반환합니다.
- 오름차순(작은 값이 1등) 순위
text
=RANK.EQ(B2, $B$2:$B$6, 1)
B2 셀의 값이 작을수록 높은 순위(1등)가 됩니다.
- 동점자 평균 순위
text
=RANK.AVG(B2, $B$2:$B$6)
동점자가 있을 때 평균 순위를 반환합니다.
- 판매량 순위
text
=RANK.EQ(C3, $C$2:$C$10)
C3 셀의 판매량이 전체에서 몇 등인지 계산합니다.
- 공동 순위 처리
2. LARGE 함수
기능 및 특징
- 데이터 집합에서 N번째로 큰 값을 반환합니다.
- 상위 성적, 매출, 점수 등 순위별 값을 추출할 때 유용합니다.
기본 구문
- =LARGE(array, k)
예제 5개
- 최고 점수(1등) 추출
text
=LARGE(A1:A10, 1)
A1~A10 중 가장 큰 값(1등) 반환
- 2등 점수 추출
text
=LARGE(A1:A10, 2)
두 번째로 큰 값(2등)
- 상위 3개 점수 합계
text
=SUM(LARGE(A1:A10, {1,2,3}))
상위 3개 점수의 합
- 판매량 상위 5개 제품 추출
text
=LARGE(B2:B20, 5)
다섯 번째로 큰 판매량
- 동점자 처리
3. SMALL 함수
기능 및 특징
- 데이터 집합에서 N번째로 작은 값을 반환합니다.
- 하위 점수, 최저 매출 등 하위 순위 분석에 유용합니다.
기본 구문
- =SMALL(array, k)
예제 5개
- 최저 점수(꼴찌) 추출
text
=SMALL(A1:A10, 1)
가장 작은 값(최저 점수)
- 꼴찌에서 두 번째 점수
text
=SMALL(A1:A10, 2)
두 번째로 작은 값
- 하위 3개 점수 평균
text
=AVERAGE(SMALL(A1:A10, {1,2,3}))
하위 3개 점수의 평균
- 최저 매출 제품 찾기
text
=SMALL(B2:B20, 1)
B2~B20 중 최저 매출
- 동점자 처리
4. PERCENTRANK, PERCENTRANK.INC, PERCENTRANK.EXC 함수
기능 및 특징
- 데이터 집합 내에서 특정 값이 상대적으로 어느 위치(백분위)에 있는지를 0~1 사이의 소수로 반환합니다.
- 백분위수 분석, 성적 등급 구분, 통계적 분포 분석에 활용됩니다.
기본 구문
- =PERCENTRANK(array, x, [significance])
- =PERCENTRANK.INC(array, x, [significance])
- =PERCENTRANK.EXC(array, x, [significance])
예제 5개
- 점수의 백분위 위치 확인
text
=PERCENTRANK(A1:A10, 85)
85점이 전체에서 몇 % 위치인지(0~1)
- 상위 10% 이내 학생 찾기
text
=PERCENTRANK(A1:A100, B2) >= 0.9
B2 점수가 상위 10% 이내인지 논리값 반환
- 소수점 3자리까지 백분위
text
=PERCENTRANK.INC(A1:A10, 90, 3)
90점의 백분위 위치를 소수점 3자리로 반환
- 백분위수 등급 구분
text
=IF(PERCENTRANK(A1:A100, B2)>=0.8, "상위 20%", "기타")
상위 20% 여부 표시
- 배열 수식으로 전체 백분위 구하기
text
=PERCENTRANK.EXC(A1:A10, A1:A10)
각 값의 백분위 위치를 배열로 반환
5. 실무 활용 팁 및 주의사항
- RANK, RANK.EQ, RANK.AVG는 동점자 처리 방식이 다르므로 목적에 맞게 선택해야 합니다.
- LARGE/SMALL은 순위별로 값을 직접 추출할 때 매우 유용합니다.
- PERCENTRANK 계열 함수는 데이터의 상대적 위치를 정량적으로 파악할 수 있어 등급 구분, 보너스 지급, 장학금 선발 등 다양한 평가 기준에 활용됩니다.
- 순위 함수 사용 시 **데이터 범위는 절대참조($A$1:$A$10)**로 지정하는 것이 좋습니다.
- 동점자 처리: RANK.EQ는 동점자에 동일 순위를, RANK.AVG는 평균 순위를 부여합니다.
예) 1등 2명 → RANK.EQ: 1,1,3 / RANK.AVG: 1.5,1.5,3
6. 요약 표
| 함수명 | 기능 및 특징 | 사용 예시 |
| RANK, RANK.EQ | 값의 순위 반환(내림/오름차순, 동점자 동일 순위) | =RANK(B2, $B$2:$B$10) |
| RANK.AVG | 동점자 평균 순위 부여 | =RANK.AVG(B2, $B$2:$B$10) |
| LARGE | N번째로 큰 값 반환 | =LARGE(A1:A10, 3) |
| SMALL | N번째로 작은 값 반환 | =SMALL(A1:A10, 2) |
| PERCENTRANK | 값의 백분위 위치(0~1) 반환 | =PERCENTRANK(A1:A10, 85) |
| PERCENTRANK.INC | 포함 백분위 위치 반환 | =PERCENTRANK.INC(A1:A10, 85) |
| PERCENTRANK.EXC | 제외 백분위 위치 반환 | =PERCENTRANK.EXC(A1:A10, 85) |
엑셀의 순위 및 위치 관련 함수는 데이터의 상대적 위치와 순위를 빠르고 정확하게 파악할 수 있게 해주며, 실무에서 성적, 매출, 실적 등 다양한 순위 분석과 평가에 필수적으로 활용됩니다.
각 함수의 특징과 예시를 참고해 실제 데이터에 맞게 응용하면, 복잡한 데이터에서도 핵심 정보를 효과적으로 추출할 수 있습니다.
