통계로 세상보기

다음과 같은 예제 자료가 있을때
엑셀을 이용해 구분에 따른 합계을 구해보자.

수식으로 처리하기엔 좀 복잡한데, 굳이 수식으로 처리한다면
아래 처럼 나타낼 수 있다.(집계①)

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))

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

GroupBy_SortBy.xls