빈셀 제외 조합 자동 출력, 해당 승수 구하기 (소소닭)

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=2zndtRO5dDY2wpuSwSkhXYU7dQ2NflHT

 

	B	C	D	E	F	G	H	I	J	K	L	M
2	2006	소	0.4	닭	0.1	돼지	0.5			3	12	소
3	2007	소	0.2	닭	0.8	x				2	4	닭
4	2008	x		닭	0.7	돼지	0.3			2	2	돼지
5
6
7	순번	2006	2007	2008	2006	2007	2008	값		소	소	x
8	1	소	소	닭	0.4	0.2	0.7	0.056		닭	닭	닭
9	2	소	소	돼지	0.4	0.2	0.3	0.024		돼지	x	돼지
10	3	소	닭	닭	0.4	0.8	0.7	0.224
11	4	소	닭	돼지	0.4	0.8	0.3	0.096		1	1	0
12	5	닭	소	닭	0.1	0.2	0.7	0.014		2	2	1
13	6	닭	소	돼지	0.1	0.2	0.3	0.006		3	2	2
14	7	닭	닭	닭	0.1	0.8	0.7	0.056
15	8	닭	닭	돼지	0.1	0.8	0.3	0.024	0	소	소	닭
16	9	돼지	소	닭	0.5	0.2	0.7	0.07	1	닭	닭	돼지
17	10	돼지	소	돼지	0.5	0.2	0.3	0.03	2	돼지	#N/A	###
18	11	돼지	닭	닭	0.5	0.8	0.7	0.28
19	12	돼지	닭	돼지	0.5	0.8	0.3	0.12
20	13	#N/A	소	닭	#N/A	0.2	0.7	#N/A

 

원본 데이타가 B2:H4에 있을 때 (물론 몇행 몇열이라도 마찬가지),

K2 =COUNTA(M$2:M$4)-COUNTIF(C2:H2,"x") ; 해당 년도 데이타 수

L2 =PRODUCT(K2:K$4) ; 그 해 포함 마지막 년도까지 곱셈

K7 =OFFSET($C$2,COLUMN(A1)-1,ROW(A1)*2-2) ; 가로데이타를 결과표에 맞게 재정렬

K11 =IF(K7<>"x",1,0)+K10 ; 전처리

K15 =INDEX($M$2:$M$4,MATCH(ROW(A1),K$11:K$13,0),1) ; 해당 열 해당 번호에 표시될 데이타

B8 =ROW(A1) ; 일련번호

C8 =VLOOKUP(INT(($B8-1)/L$3),$J$15:$M$17,2,0)

D8 =VLOOKUP(MOD(INT(($B8-1)/L$4),K$3),$J$15:$M$17,3,0)

E8 =VLOOKUP(MOD($B8-1,K$4),$J$15:$M$17,4,0)

F8 =OFFSET($B$2,0,MATCH(C8,$C$2:$H$2,0)+1) ; 해당 항목 해당년도의 값

G8 =OFFSET($B$3,0,MATCH(D8,$C$3:$H$3,0)+1)

H8 =OFFSET($B$4,0,MATCH(E8,$C$4:$H$4,0)+1)

I8 =PRODUCT(F8:H8)

 

K2 잡아 K2:M4, K7 잡아 K7:M9, K11 잡아 K11:M13, K15 잡아 K15:M17에 복사, B8:I8 잡아 아래로 데이타가 다 나올 때 까지 (C열에 #N/A가 뜰 때 까지) 드래그 채워넣으시면 표가 완성됩니다. 그 외 색 표시가 안된 셀(M2:M4, J15:J17)은 손으로 입력하세요. (데이타가 많으면 자동으로 표시되게 할 수는 있지만 별도의 수식을 써야 합니다) 데이타가 많을 경우 C8 =VLOOKUP(MOD(INT(($B20-1)/OFFSET($L$1,COLUMN(B$1),0)),OFFSET($K$1,COLUMN(A$1),0)),$O$12:$AC$14,COLUMN(B$1),0) 를 오른쪽으로 드래그 복사, 마지막 열만 E8을 쓰면 편리합니다.