다음과 같은 예제 자료가 있을 때, '구분'에 따른 합계를 구해보자.

수식으로 처리하기엔 좀 복잡하지만 굳이 수식으로 나타낸다면 아래와 같이 할 수는 있다. (집계①)

D3 셀 :
=IF(SUM(N((FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0)))>=ROW(A1),INDEX($A$2:$A$13,SMALL(IF(FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0,ROW($A$2:$A$13)-ROW($A$1)),ROW(A1))),"")

물론 배열수식이다.

E4 셀 :
=SUMIF($A$2:$A$13,D3,$B$2:$B$13)

여기서 구분별 합계 자료를 크기 순서대로 정렬하고자 한다면 수식은 더욱 복잡해진다.(집계②)

G3 셀 :
=IF(SUM(N((FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0)))>=ROW(A1),INDEX($A$2:$A$14,LARGE(((FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0)*(SUMIF($A$2:$A$14,$A$2:$A$14,$B$2:$B$14))=LARGE((FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0)*(SUMIF($A$2:$A$14,$A$2:$A$14,$B$2:$B$14)),ROW(A1)))*ROW(INDIRECT("1:"&ROWS($A$2:$A$14))),SUM((LARGE((FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0)*(SUMIF($A$2:$A$14,$A$2:$A$14,$B$2:$B$14)),ROW(INDIRECT("1:"&ROWS($A$2:$A$14))))=LARGE((FREQUENCY(MATCH($A$2:$A$13,$A$2:$A$13,0),MATCH($A$2:$A$13,$A$2:$A$13,0))>0)*(SUMIF($A$2:$A$14,$A$2:$A$14,$B$2:$B$14)),ROW(A1)))*(ROW(INDIRECT("1:"&ROWS($A$2:$A$14)))<=ROW(A1))))),"")

무식하면 용감하다고, 진짜 무식한 수식이 나와버렸다. 너무 무식해 보여 '이름 정의'를 이용해 대충 정리해봐도...

=IF(SUM(N((순서>0)))>=ROW(A1),INDEX(rng_구분,LARGE(((순서>0)*(부분합)=LARGE((순서>0)*(부분합),ROW(A1)))*위치,SUM((LARGE((순서>0)*(부분합),위치)=LARGE((순서>0)*(부분합),ROW(A1)))*(위치<=ROW(A1))))),"")

역시 무식하다는 말을 피하긴 어렵겠다.

그럼 이렇게 무식한 수식을 왜 생각했나? 엑셀이 지원하는 기능을 한번 고려해보라는 말을 하기 위해서다. 몇가지 방법이 있을텐데, '피벗 테이블'을 이용해 집계표를 먼저 구성한다.(집계③)

그리고 구분 필드를 선택해 내림차순 정렬을 한다. 이 때 '표시'를 지정한다면 지정된 순위 내에 자료만 피벗 테이블에 나타낼 수 있다.

 

나라면? 당연히 피벗 테이블을 이용할 것이다. 클릭 몇번이면 처리될 자료를 무식한 수식을 구성해 처리한다는 것은 ('배열수식 증후군' 증세가 있는 내가 봐도) 낭비(?)다.

그리고 혹시 생성한 피벗테이블 결과(집계③)를 다른 셀에서 불러오고자 한다면... 앞에서와는 비교도 안될 만큼의 아주 간단한 수식으로 처리할 수 있다.

P3 셀 :
=INDEX($M$3:$M$8,MATCH(Q3,$N$3:$N$8,0)+COUNTIF(Q$3:Q3,Q3)-1)

Q3 셀 :
=LARGE($N$3:$N$8,ROW(A1))

여기서 집계④는 집계③이 내림차순 정렬됐을 때에 한한다