|
=RANK(E2,$E$2:$E$5) | E2:E5의 범위에서 E2의 순위를 구하고 | ||
&"(" | 그 뒤에 괄호 여는 기호 "("를 삽입하고 | ||
&COUNTIF($E$2:$E$5,E2) | 그 뒤에 E2:E5의 범위에서 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) 부분은 총점으로 순위를 구하는 수식인데, 그 뒤에 더하기기호(+)가 붙어있습니다. 그 뒤의 수식에서 동점자중에서 자신보다 국어점수가 높은 학생수를 구한 다음 순위에 더한다는 의미입니다. 즉, 이전의 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의 두 사람 뿐임을 알 수 있습니다. 따라서,
의 수식에서는 2가 반환됩니다.
SUMPRODUCT함수는 제대로 이해하고 있으면 매우 유용합니다. 수식이 좀 길어져서 복잡해 보인다는 것이 단점이지만, 그것도 남들에게는 뭔가 엄청나게 대단한 것을 하는 능력자인것처럼 보이게 하는 효과를 거두기도 합니다.^^
p.s. 총점과 국어점수까지 같다면, 그 다음 조건으로 수학점수를 주고 싶다면 어떻게 할까요? 이 글을 쓰기 전에 연구를 해봤지만, 아직 답을 얻지 못했습니다. 뒤에 IF함수를 이용해 조건을 더 길게 주어야 할 것 같은데...... 아무튼, 실제 사용하기위해 함수식을 만들어야하는 상황은 아니므로, SUMPRODUCT함수를 이해하는데 도움이 되는 정도에서 동점자순위 구하기는 마치겠습니다