인문 자연 섞여있는 표에서 계열별 순위산출하기

출처: 오피스튜터
http://www.officetutor.co.kr/board/Dtype/bfrmvw.asp?f_tn=Dqa_excel_n2&f_bno=97770&page=&fchk=brd_title&fval=%BC%F8%C0%A7


반별로 성적을 모으면, 인문, 자연계가 섞여 있습니다. 이럴떄, 인문 자연 따로 시트를 만들어 계열별 석차를 낼 수도 있습니다만, 한 시트안에서 수식을 이용해서 해결하고자 합니다.

원래 순위를 구하는 함수는 rank입니다. 인문에 해당하는 범위(I4~I8)까지만 rank함수속에 넣으면 인문계안에서만 순위를 구할 수 있습니다. 그런데 문제는 rank함수를 쓰면 그림처럼 국어점수를 기준으로 정렬을 다시했을때, 인문자연이 섞여버려서 전혀 엉뚱한 결과가 보여진다는 것입니다.

그래서 오른쪽에 있는 것처럼 sumproduct함수를 사용합니다. 그림에 보이는 함수는
=IF(ISBLANK(D4),"",SUMPRODUCT((D4=$D$4:$D$28)*(I4<$I$4:$I$28))+1)
이라고 쓰여있습니다. 한 부분씩 분석해 보겠습니다.

if함수는 아시죠? =if(조건,참일때결과,거짓일때결과)

isblank함수는 뒤의 셀주소에 데이타가 없으면 TRUE를, 있으면 FALSE를 반환합니다.

따라서, K4셀에 있는 수식중
 =IF(ISBLANK(D4),"",
여기까지의 의미는

만약 D4열이 비어있으면 TRUE를 반환하고, TRUE를 반환받았으면 K4셀을 빈칸""으로 만들어라는 의미입니다.

여기서는 D4셀에 "인문"이라는 데이타가 들어있으므로, K4셀에는 빈칸이 출력되지 않고, if절의 나머지 부분에 있는 수식이 적용됩니다. 여기까지는 시험을 안본 학생의 데이타를 순위에서 빼기 위한 수식입니다. 크게 중요하지는 않습니다.
이 다음이  중요합니다.

SUMPRODUCT((D4=$D$4:$D$28)*(I4<$I$4:$I$28))+1)

이전 글에서 언급한 적이 있는 sumproduct함수는, 그 정확한 의미를 잘 모르기에 새로운 수식을 만들지는 못합니다.  하지만 남이 만들어 놓은 수식은 대충은 이해하고 있습니다.

위의 수식은, $D$4:$D$28의 배열(인문/자연이 표시되는 열)속에서 D4셀의 내용(인문)과 같은 값이 있는, 즉, "인문"이라고 표시된 셀의 수를 추출하여
그 중에서 I4셀의 값(김1학생의 총점)보다 더 큰 값이 있는 셀의 수를 골라
+1을 하라는 뜻입니다. (1등인 학생의 경우 0 이 출력될 것이므로)

그러면 인문/자연이 섞여 있어도 계열별 석차가 출력됩니다.