엑셀 그룹별 순위 구하기 공식
엑셀 그룹별 순위 구하기 목차 바로가기
함수 공식
=SUMPRODUCT(($그룹범위=그룹명)*($값범위>시작값))+1
인수 설명
| 인수 | 설명 |
| $그룹범위 | 그룹별로 순위를 구할 그룹이 입력된 범위입니다. 절대참조로 입력하는 것에 주의합니다. |
| 그룹명 | 그룹범위의 시작셀입니다. |
| $값범위 | 순위를 구할 값이 입력된 범위입니다. 절대참조로 입력하는 것에 주의합니다. |
| 시작값 | 값범위의 시작셀입니다. |
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀공식] 엑셀 그룹별 순위 구하기 공식예제파일
호환성
운영체제 호환성 Windows 버전 모든 엑셀 버전에서 사용 가능합니다. Mac 버전 모든 엑셀 버전에서 사용 가능합니다. 사용된 기초 함수
엑셀 그룹별 랭킹 구하기 공식 알아보기
공식 설명
본 공식은 값의 순위를 그룹별로 나눠서 계산하는 공식입니다. 예를들어, A그룹이 4명, B그룹이 4명 있을 경우 A그룹으로 1~4위, B그룹으로 1~4위가 나뉘어 순위가 계산됩니다.
본 공식에는 RANK 함수 대신 SUMPRODUCT 함수가 사용됩니다. 만약 그룹별 순위를 구하되 중복되는 등수 없이 그룹별 순위를 계산하려면 아래 공식을 사용하세요.
=SUMPRODUCT(($그룹범위=그룹명)*($값범위>시작값))+COUNTIFS($그룹명머릿글:그룹명머릿글,그룹명,$시작값머릿글:시작값머릿글,시작값)+1
'그룹별 순위를 중복값 없이 계산합니다.중복값 없이 그룹별 순위를 계산하는 공식에는 확장범위가 사용됩니다. 확장범위에 대한 자세한 설명은 아래 관련 링크를 참고하세요.
공식의 동작원리
- [$값범위>시작값]은 값범위 내에서 현재 값보다 클 경우 TRUE를 반환합니다.
=($값범위>시작값)
={32,12,27,26,22,39}>27
={TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}
'값 범위내에서 27보다 큰 값은 2개입니다. - [$그룹범위=그룹명]은 그룹범위의 값이 그룹명과 같을 경우 TRUE를 반환합니다.
=($그룹범위=그룹명)
={A그룹,A그룹,A그룹,B그룹,B그룹,B그룹}=A그룹
={TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}
'그룹범위의 값이 A그룹과 같을 경우 TRUE를 반환합니다. - ($그룹범위=그룹명)*($값범위>시작값) 은 그룹명이 동일하면서 현재 값보다 큰 값의 개수를 계산합니다. TRUE*TRUE 일 경우만 1이 반환되고, 나머지는 0이 반환됩니다.
=($그룹범위=그룹명)*($갑범위>시작값)
={TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}*{TRUE,FALSE,FALSE,FALSE,FALSE,TRUE}
={1,0,0,0,0,0}
'그룹범위가 A그룹이면서 27보다 클 경우 TRUE(1)을 반환합니다. - =SUMPRODUCT(($그룹범위=그룹명)*($값범위>시작값)) 은 반환된 배열의 합계를 계산합니다.
=SUMPRODUCT({1,0,0,0,0,0})
=1 - 마지막으로 뒤에 1을 더해 그룹별 순위 구하기 공식이 마무리합니다.
=SUMPRODUCT(($그룹범위=그룹명)*($값범위>시작값))+1
=1+1
=2 '27은 A그룹내에서 2번째로 큰 값입니다.
자주묻는질문
자주묻는질문1. 오름차순으로 정렬하려면 어떻게 해야 하나요?
각 그룹별 순위를 오름차순으로 정렬하려면 공식의 부등호를 반대로 입력합니다.
=SUMPRODUCT(($그룹범위=그룹명)*($값범위<시작값))+1 - [$값범위>시작값]은 값범위 내에서 현재 값보다 클 경우 TRUE를 반환합니다.
