엑셀 함수 정리: 배열 수식, 순위/지표순위/순위자 목록 구하기(RANK, SUMPRODUCT)

 

안녕하세요:) 핀아입니다. 이번에는 여러가지의 엑셀 함수 없이도 간단한 수식으로 원하는 값을 구할 수 있는 배열 수식과 성과관리를 위한 순위 관련 함수에 대해 알려드리겠습니다. 조건식을 사용한 배열 수식으로 활용하면 COUNTIF, SUMIF와 같은 함수를 사용하지 않으셔도 되는데 함수 활용이 더 편하신 분들은 기존대로 사용하시면 됩니다.

---

1. 범위와 배열의 차이

범위 -> 원하는 행과 열을 : 기호로 지정하는 것입니다. (ex: A1:B1) 배열 -> 원하는 행과 열을 {} 기호를 활용하여 구분하는 것입니다. (ex: {"A1",값;"A2",값})

: 배열 수식 사용시 엑셀 버전 2016, 2019는 그냥 입력해도 반영되지만, 그 외 버전에서는 입력 후 Ctrl + Shift + Enter를 눌러야 반영됩니다.

 

 

---

2. 배열 수식

{=SUM(조건식*더할값)}

(1) 예시: 신규, 변경, 취소 조건에 맞는 매출액, 건수 값 산정

배열 수식 예시

{=SUM(($D$6:$D$10=E1)*$G$6:$G$10)}

: 구분이 '신규'인 거래내역의 매출액 합계를 구하는 배열 수식으로 9,000+5,000+65,000을 더한 79,000입니다. 이를 일반 SUMIF 함수로 표현하면 =SUMIF($D$6:$D$10,E1,$G$6:$G$10) 입니다.

 

{=SUM(($D$6:$D$10=E1)*1)}

: 구분이 '신규'인 거래내역의 건수 합계를 구하는 배열 수식으로 총 3건입니다. 이를 COUNTIF 함수로 표현하면 =COUNTIF($D$6:$D$10,E1) 입니다. 배열 수식을 사용하든 함수를 사용하든 동일한 값이 산출됩니다.

 

 

---

3. 성과관리 순위 구하기

=RANK(순위를 구할 수, 집계범위, [순위 지정방식])

: 숫자 데이터의 순위를 구할 수 있는 RANK 함수입니다. 순위 지정방식인 3번째 인수는 0은 내림차순, 1은 오름차순으로 순위를 구합니다.

 

(1) 예시: RANK 함수를 활용한 성과관리 데이터 점수 합계의 순위 구하기

RANK 함수 예제

=RANK(I3,$I$3:$I$18,0)

: RANK 함수는 맨 오른쪽 '순위'에 점수합계의 순위를 산정하여 기재할 수 있도록 도와주는 함수입니다. 집계 범위는 절대 참조 범위로 지정해야 하며, 82.8점은 2순위입니다.

 

 

---

4. 성과관리 지표순위 구하기

=SUMPRODUCT( (성과목표범위 = 지표) * (점수범위 >= 점수) )

: SUMPRODUCT 함수는 범위의 항목을 곱하여 더한 값을 반환하고 조건식을 활용하여 배열 수식을 이용할 수 있습니다. 지표순위는 성과목표 지표를 기준으로 순위를 구하는 것입니다. 성과목표범위의 값이 지표와 같을 시 1로 같지 않으면 0으로 같은 지표인 점수끼리 비교 가능합니다. 점수범위에서 순위를 구할 대상의 점수보다 큰 값이 있을 경우 +1이 되면서 순위를 산정하게 됩니다.

 

(1) 예시: SUMPRODUCT 함수를 활용한 성과관리 지표순위 구하기

SUMPRODUCT 함수 예제

=SUMPRODUCT(($B$3:$B$18=B3)*($G$3:$G$18>=G3))

: 성과목표 A, B, C, D지표들끼리 점수를 비교하여 지표순위에 순위를 구하는 함수입니다. 예를 들어 노란색 음영 표시인 A지표의 고00 23.25점, 김00 18.5점, 박00 23점, 이00 17.75점을 순위를 매기면 고00이 가장 높은 점수로 1순위가 되었습니다.

 

 

지금까지 순위를 구할 수 있는 다양한 함수와 기본 함수만 사용하여 배열 수식으로 값을 구하는 방법을 설명해드렸습니다. 배열 수식은 저도 낯선 방법인데 이러한 방법도 있다는 것을 참고해주시고 값을 산정하는 방법은 다양하게 있으니 사용자 편의에 따라 활용하시면 됩니다.