엑셀 그룹별 순위 구하기에 대해서 알아보겠습니다. 엑셀에서 순위를 구하기 위한 함수는 RANK 함수가 있습니다. 하지만 그룹별 순위를 구하기 위해서는 그룹이라는 조건에 맞는 수식을 구성하여 순위를 구해야 합니다. 오늘은 COUNTFIS 함수를 이용하여 단일, 다중 조건에 맞는 항목을 선택하여 순위를 구하는 수식을 작성해보겠습니다.
1. COUNTIFS 함수
그룹별 순위를 구하기 위해서 사용할 함수는 COUNTIFS 함수입니다. COUNTIFS 함수는 범위 내에서 주어진 조건에 맞는 셀의 개수를 세는 함수입니다. COUNTIFS 함수를 이용하여 조건(그룹)에 맞는 셀을 찾아 순위를 부여하도록 하겠습니다. COUNIFS 함수의 구성은 다음과 같습니다.
= COUNTIFS ( 비교 영역1, 비교값1, 비교 영역2, 비교값2, 비교 영역3, 비교값3…….)
1) 그룹별 순위 구하기 – 단일 조건
COUNTIFS 함수를 이용하여 그룹별 순위를 구하기 위해서 비교 영역1과 비교값1은 “그룹”을 나누는 조건을 작성하고 비교 영역2와 비교값2는 순위를 매기기 위한 조건으로 사용하겠습니다. 영역은 절대참조로 지정하시기 바랍니다. 수식의 구성은 아래와 같습니다.
=COUNTIFS( $그룹 영역, 그룹, $비교 영역, “>”&비교값 ) + 1
위 수식은 그룹 영역에서 해당 그룹을 찾고, 비교 영역에서 비교값 보다 큰 값의 셀 개수를 찾아 반환합니다. 만약 비교값이 가장 큰 값일 때는 0을 반환하게 되며 이를 방지하기 위해서 +1을 입력하여 가장 높은 순위로 지정합니다.
가장 큰 값을 1위로 지정하기 위해 “>”&비교값으로 지정하였고, 만약 가장 낮은 값을 1위로 지정할 때는 “<“&비교값을 입력합니다.
내림차순 = “>”&비교값
오름차순 = “<“&비교값
아래 그림은 같은 메뉴의 판매량 순위를 확인하기 위한 예시입니다. 위 수식을 적용하여 순위를 구하면 메뉴별 판매량 순위를 확인할 수 있습니다. 예시에서는 아메리카노 그룹에서 GRANDE의 판매량이 가장 많은 것을 확인할 수 있습니다.
2) 그룹별 순위 구하기 – 다중 조건
그룹을 세분화하기 위해서 비교영역과 비교값이 여러 개 필요할 때가 있습니다. 기존 수식에 조건만 추가하면 여러 조건에 만족하는 순위를 구할 수 있습니다. 영역은 절대참조로 지정하시기 바랍니다. 수식의 구성은 아래와 같습니다.
=COUNTIFS( $그룹 영역1, 그룹1, $그룹 영역2, 그룹2, $비교 영역, “>”&비교값 ) + 1
위에 내용을 이해하셨다면 조건만 하나 더 늘어났다는 것을 눈치채셨을 겁니다. 아래 그림은 지점과 메뉴가 같을 때 판매량을 알아보기 위한 예시입니다.
결과를 보시면 A 지점의 아메리카노 중에서 VENTI 사이즈가 가장 많이 팔리고 있다는 것을 알 수 있습니다.
COUNTIFS 함수를 이용하면 조건을 세분화하거나 간소화하여 조건에 맞는 순위를 파악할 수 있는 장점이 있습니다.
마치며
오늘은 COUNTIFS 함수를 이용하여 그룹별 순위를 구하는 방법을 알아보았습니다. 오늘 내용을 간단히 정리하겠습니다.
① COUNTIFS 함수를 이용하여 조건에 만족하는 셀의 개수를 파악할 수 있다.
② 수식의 조건을 통해서 그룹을 세분화 또는 간소화하여 순위를 구할 수 있다.
③ 등호를 설정하여 오름차순, 내림차순으로 순위를 지정할 수 있다.