2차원 배열 역추적, 데이타 재정렬

http://kin.naver.com/detail/detail.php?d1id=1&dir_id=1050202&eid=1EKkAC0DHGd9Wi20+Q52+/64VwwrSOu+

 

제시하신 과제는 일반적인 함수, 기법으로 구현하기 매우 어려운 유형으로 보입니다. x, y축을 동시에 유동참조를 걸어야 하기 때문인데요, 아래 예시와 같이 방법은 있습니다. 다만...소수를 이용하여 위치 파악을 하는 방식이라서 데이타 수 (과제에서는 학교 수)가 몇 백개 수준이 되면 다 잡아내지 못하는 한계는 있겠습니다.

 

<Sheet1> ; 데이타가 B2:I10과 같이 있을 때,

	B	C	D	E	F	G	H	I	J	K
2	학교	수	갑	을	병	정	무	기
3	계	12	2	1	4	1	2	2
4	a	2			○		○			2
5	b	2	○		○					3
6	c	1				○				5
7	d	2		○	○					7
8	e	1						○		11
9	f	2			○			○		13
10	g	2	○				○			17
11			51	7	546	5	34	143

 

D11 =PRODUCT(IF(D4:D10<>"",$K4:$K10,1)) ; 수식입력 후 CTRL+SHIFT+ENTER(배열수식), D11 잡아 오른쪽으로 드래그, I11까지 채워 복사. K4:K10에 소수를 순차적으로 나열해 둠.

 

<Sheet2>

	B	C	D	E	F	G	H	I	J	K
2	학생	계		2	3	5	7	11	13	17
3	갑	2	51	b	g
4	을	1	7	d
5	병	4	546	a	b	d	f
6	정	1	5	c
7	무	2	34	a	g
8	기	2	143	e	e

 

E2 =OFFSET(Sheet1!$K$4,COLUMN()-COLUMN($E2),0)

B3 =OFFSET(Sheet1!$D$2,0,ROW()-ROW(B$3))

C3 =HLOOKUP($B3,Sheet1!$D$2:$I$3,2,0)

D3 =HLOOKUP($B3,Sheet1!$D$2:$I$11,10,0)

E3 =IF(COLUMN()-COLUMN($D2)>$C3,"",INDEX(Sheet1!$B$4:$B$10,MATCH(MIN(IF(MOD($D3,$E$2:$K$2)=0,$E$2:$K$2)),$E$2:$K$2,0),1))

F3 =IF(COLUMN()-COLUMN($D2)>$C3,"",INDEX(Sheet1!$B$4:$B$10,MATCH(MIN(IF((MOD($D3,$E$2:$K$2)=0)*(VLOOKUP(E3,Sheet1!$B$4:$K$10,10)<$E$2:$K$2),$E$2:$K$2)),$E$2:$K$2,0),1))

 

E3, F3는 수식 입력 후 CTRL+SHIFT+ENTER (배열수식)하셔야 합니다. 이후 E2 잡아 F2에 복사, F2:F3 잡아 K2:K3에 복사, B3:K3 잡아 아래로 데이타 행 만큼 복사하면 표가 완성됩니다.

 

덧붙임 : 

채택 후 주신 화일로 작업을 해보니, 역시 소수 방식은 한계가 있습니다. (과제의 지원학교 수가 많아지면 엑셀이 나눗셈을 못함. 즉 MOD 함수로 위치 추적을 할 수 없음. 곱셈 결과 1,000,000이 한계인 듯. 따라서 이럴 때는 시트를 새로 만들어 if함수로 걍 나열, small 배열수식으로 빈칸을 메워 offset으로 가로세로를 바꾸는 것이 가장 속편할 듯 함. (시트 3개 추가)

 

따라서 (소수를 활용한) 이 방식은 인원 수가 많고 학교수가 적은데 사람 당 지원학교의 수가 2~3개에 불과할 때 유효할 것임 --+