동점일때 다른기준으로 순위 매기기 - SUMPRODUCT 함수

[하늬바람]

오늘은 SUMPRODUCT함수를 다룹니다.  오늘은 석차를 낼때 사용하는 예를 들어 설명해 보겠습니다.

주어진 배열에서 해당 요소를 모두 곱하고 그 곱의 합계를 반환합니다.

구문

SUMPRODUCT(array1,array2,array3, ...)

array1, array2, array3, ...  곱한 후 더할 값이 들어 있는 배열을 지정합니다. 2개에서 255개 사이의 배열을 사용할 수 있습니다.

주의

• 인수로 사용하는 배열의 차원은 모두 같아야 합니다. 차원이 같지 않으면 #VALUE! 오류 값이 반환됩니다.
• 숫자가 아닌 배열 항목은 0으로 처리됩니다.

SUMPRODUCT함수는 다음에 이야기하게 될 "배열함수"와 깊은 관련이 있습니다. 영어로는 ARRAY인데, 학교 다닐때 "행렬"이라는 이름으로 배운 기억이 있는데 엑셀에서는 "배열"이라는 표현을 씁니다. 배열로 수식을 작성하는 것은 저도 아직 정확하게 이해는 하지 못했지만, 대충 감은 잡고 있는데, 그 감을 잡는데 이 SUMPROCUT함수가 도움이 되었습니다.

이 함수는 위에 있는 설명에도 있듯이, 주어진 배열을 모두 곱한 뒤에 그 합계를 반환하는 함수입니다.
(2,4) 라는 배열과 (3,5) 라는 배열이 있을때,

SUMPRODUCT({2,4},{3,5})라고 하면

2*3 + 4*5 를 계산하여 26을 반환합니다.

같은 식을 다음과 같이 표현할 수도 있습니다.

A1:A2 의 배열과 B1:B2의 배열이 * (논리곱)으로 연결되어있습니다. 이때의 계산식은 위에서 설명한 것과 같이,

2*3 + 4*5

입니다. 그런데, 다음처럼 + (논리합)으로 연결되면

(2+3) +  (4+5)

를 계산하여 14를 반환합니다.

여기까지 SUMPRODUCT함수의 기본적인 설명이고, 이제 RANK함수를 이용해 순위를 구할때 유용하게 쓸 수 있는 방법을 알아봅시다.

가장 기본적인 방식입니다. 지난번 글에서 예로 든 방식이죠. 그런데 이 방식에서는 동점자일때 무조건 같은 순위로 표시합니다.

다음과 같이 수식을 써 봅시다.

=RANK(E2,$E$2:$E$5)&"("&COUNTIF($E$2:$E$5,E2)&")"

		=RANK(E2,$E$2:$E$5)	E2:E5의 범위에서 E2의 순위를 구하고
		&"("	그 뒤에 괄호 여는 기호 "("를 삽입하고
		&COUNTIF($E$2:$E$5,E2)	그 뒤에 E2:E5의 범위에서 E2와 점수가 같은 사람수를 구해서 넣고
		&")"	괄호 닫는 기호 ")"를 삽입하라

그러면 보시는 것처럼 동점자수를 그 뒤에 괄호 속에 표시하게 됩니다. 그런데 모양이 약간 어색합니다. 동점자가 없을때는 아예 괄호를 쓰지 않는게 보통이지요. 그래서 다음과 같이 수식을 수정합니다.

=RANK(E2,$E$2:$E$5)&IF(COUNTIF($E$2:$E$5,E2)=1,"","("&COUNTIF($E$2:$E$5,E2)&")")

=RANK(E2,$E$2:$E$5)	E2:E5의 범위에서 E2의 순위를 구하고,
&	그 뒤에 다음의 결과값을 덧붙인다
IF(COUNTIF($E$2:$E$5,E2)=1,	만약 E2:E5범위에서 E2와 같은 값이 1이라면, (즉 자기자신 이외에는 같은 값을 가진 이가 없음)
"",	공백을 출력하고
"("&	1보다 크다면 (동점자가 있다면) 괄호여는 기호를 삽입하고
COUNTIF($E$2:$E$5,E2)&	E2:E5범위에서 E2와 같은 값의 개수를 출력하고
")")	괄호 닫는 기호를 삽입하라

그 결과는 위의 그림과 같습니다. 이제 제대로 된 모양 같네요 ^^ 하지만 명심할 것은, 모양을 보기 좋게 만들수록 수식은 점점 더 어려워진다는 것입니다. 실무자 입장에서는 편하게 수식을 만들고 싶고, 관리자입장에서는 보기 편했으면 좋겠고..... 갈등이죠 ^^


그런데, 두번째 기준으로 영어점수를 추가하여, 동점자 중에서도 국어점수가 높은 학생을 더 높은 순위로 주려면 어떻게 해야할까요? 이제 SUMPRODUCT함수가 등장합니다.

설명을 하기위해 학생들의 성적을 약간 바꾸었습니다. 학생3은 총점으로는 공동1위이지만 국어점수를 두번째 기준으로 주면 3위가 됩니다. 다음 수식을 씁니다.

=RANK(E4,$E$2:$E$5)+SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))

잘 보시면 앞의 RANK(E4,$E$2:$E$5) 부분은 총점으로 순위를 구하는 수식인데, 그 뒤에 더하기기호(+)가 붙어있습니다. 그 뒤의 수식에서 동점자중에서 자신보다 국어점수가 높은 학생수를 구한 다음 순위에 더한다는 의미입니다. 즉, 이전의 RANK함수에서는 공동1위였던 학생3은 자신의 순위 1에다가, 총점이 자기와 같으면서 국어점수가 자기보다 높은 학생의 수 2(학생2,학생4)를 더하여 3위가 되는 것입니다.

SUMPRODUCT함수는 각 배열의 인수끼리 곱해서 더한다고 했죠?
위의 수식에서 인수는 ($E$2:$E$5=E2)와 ($B$2:$B$5>B2)) 의 배열에 붙은 수식 두 개입니다. 숫자가 들어있으면 글그대로 곱하고 더하면 되지만, 지금처럼 수식이 들어있는 경우는 배열의 각 셀마다 주어진 수식에 대한 논리값(참,거짓)을 구하여, 곱하기대신에 논리곱을, 더하기 대신에 논리합을 구합니다. 논리합과 논리곱에 대해서는 이전 글을 참고하세요.

여기서는 총점이 E2의 값과 같다는 조건과 국어점수가 E2보다 크다는 두 조건을 제시하고 있습니다.  각 배열에서 두 조건의 참,거짓을 따져봅시다.
총점이 있는 E열부터 보면,

총점이 275와 같다는 조건에 참인 것은 위와 같습니다.

두번째 조건인 국어점수가 B4보다 큰가는 조건에 대해서는

위와 같은 결과값이 나올것입니다.

앞의 수식
SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))
에서 두 조건 사이가 논리곱(*)으로 연결되어 있습니다. 이전의 글에서 알아본 것처럼, 논리곱일때는 두 조건이 모두 참일때만 참이 된다고 했습니다. 그러므로 최종적인 결과값은 다음과 같습니다.

결국 두 가지 조건을 다 만족하는 것은 학생2와 학생4의 두 사람 뿐임을 알 수 있습니다. 따라서,

=SUMPRODUCT(($E$2:$E$5=E4)*($B$2:$B$5>B4))

의 수식에서는 2가 반환됩니다.

SUMPRODUCT함수는 제대로 이해하고 있으면 매우 유용합니다. 수식이 좀 길어져서 복잡해 보인다는 것이 단점이지만, 그것도 남들에게는 뭔가 엄청나게 대단한 것을 하는 능력자인것처럼 보이게 하는 효과를 거두기도 합니다.^^



p.s. 총점과 국어점수까지 같다면, 그 다음 조건으로 수학점수를 주고 싶다면 어떻게 할까요? 이 글을 쓰기 전에 연구를 해봤지만, 아직 답을 얻지 못했습니다. 뒤에  IF함수를 이용해 조건을 더 길게 주어야 할 것 같은데...... 아무튼, 실제 사용하기위해 함수식을 만들어야하는 상황은 아니므로, SUMPRODUCT함수를 이해하는데 도움이 되는 정도에서 동점자순위 구하기는 마치겠습니다