엑셀 그룹별 순위 구하기 공식
엑셀 그룹별 순위 구하기 목차 바로가기
함수 공식
=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를 반환합니다.
여기서 그룹명머릿글이 어떤건가요?
표의 머릿글입니다. :)
1,2,3,3,4,5 로 출력하는 건 공식 하나만으론느 해결이 어려워 보입니다.
우측에 보조열을 추가해서 COUNTIF 함수로 중복되는 값이 있는지 확인 후 -1, -2를 빼주는 형태로..
단계별로 작성하셔야 할 듯 합니다.
네 가능합니다.
($그룹범위1=그룹명1)*($그룹범위2=그룹명2)*($값범위>시작값)
형식으로 추가 조건을 곱셈으로 연산해보세요.
1차 조건 동 순위시, 2차 조건으로 순위 매기는 방법은 없을까요?
여러가지 방법이 있습니다. 그 중 하나로 아래와 같이 수식을 작성해서 해결할 수 있을 듯 합니다.
=SUMPRODUCT(($그룹범위=그룹명)*($값범위>시작값))+SUMPRODUCT(($그룹범위=그룹명)*($값범위=시작값)*($보조범위>보조시작값))+1