코드 순차에 맞춰 형태 데이타 집계 (SMALL, SUMPRODUCT)

	B	C	D	E	F	G	H	I	J	K	L	M	N	O
2	사용자	형태	코드	수량	코드	수량	코드	수량	코드	수량
3	홍길동	A	111	1	222	2	333	3	444	4		111	1	1
4	김말자	B	111	3	444	4			222	11		111	1	1
5	김영자	A	111	1	222	2	333	3	444	4		111	1	1
6	노길동	B	111	10	555	6						111	1	1
7	홍길동	A	222	5								222	2	2
8												222	2	2
9			A		B							222	2	2
10			코드	수량	코드	수량						222	2	2
11			111	2	111	13						333	3	2
12			222	9	222	11						333	3	2
13			333	6	444	4						444	4	3
14			444	8	555	6						444	4	3
15			#N/A	#N/A	#N/A	#N/A						444	4	3
16												555	4	4
17												#NUM!	#NUM!	#NUM!

 

M3 =SMALL((D$3:D$7,F$3:F$7,H$3:H$7,J$3:J$7),ROW()-ROW(M$3)+1) ; 사용한 모든 코드 나열

N3 =IF(OR($M3=$F8,SUMPRODUCT(N($C$3:$C$7=D$9),N($D$3:$D$7=$M3)+N($F$3:$F$7=$M3)+N($H$3:$H$7=$M3)+N($J$3:$J$7=$M3))=0),G8,G8+1) ; A형태 카운터

O3 =IF(OR($M3=$F8,SUMPRODUCT(N($C$3:$C$7=F$9),N($D$3:$D$7=$M3)+N($F$3:$F$7=$M3)+N($H$3:$H$7=$M3)+N($J$3:$J$7=$M3))=0),H8,H8+1) ; B형태 카운터

; N2, O2는 비워 둠. M3:O3 잡아 #NUM!(코드가 사용된 수 만큼)이 뜰 때 까지 아래로 복사.

 

D11 =INDEX(M$3:M$16,MATCH(ROW()-ROW(D$10),N$3:N$16,0),1)

F11 =INDEX(M$3:M$16,MATCH(ROW()-ROW(D$10),O$3:O$16,0),1)

E11 =SUMPRODUCT(N(C$3:C$7=D$9),N(D$3:D$7=D11)*E$3:E$7+N(F$3:F$7=D11)*G$3:G$7+N(H$3:H$7=D11)*I$3:I$7+N(J$3:J$7=D11)*K$3:K$7)

G11 =SUMPRODUCT(N(C$3:C$7=F$9),N(D$3:D$7=F11)*E$3:E$7+N(F$3:F$7=F11)*G$3:G$7+N(H$3:H$7=F11)*I$3:I$7+N(J$3:J$7=F11)*K$3:K$7)

; D11:G11잡아 데이타 수량 만큼 (#N/A!가 양쪽 모두 뜰 때 까지) 아래로 복사하면 작업 끝.

 

제시된 예제는 참조범위가 네 개로 흩어져 있어 보조셀을 사용할 수 밖에 없었습니다. M열에서 SMALL함수로써 사용된 코드를 모두 나열합니다. A형태/B형태 별로 코드가 사용되었는지를 N열, O열에 각각 누적카운트합니다. D11, E11에 각 형태별 사용된 코드를 ROW함수로 일련번호를 발생시켜 MATCH를 걸어 INDEX로 가져옵니다. 마지막으로 SUMPRODUCT로 코드에 해당하는 수량을 모아 표시합니다.