요일별 배달 목록 만들기

아파트 단지의 요일별 우유배달 목록을 자동으로 생성하기임. 지식in Q&A 문답 과정에서 만들어 보니 SUMPRODUCT, 배열수식, OFFSET, INDEX, FIND, NOT, ISERROR, COUNTIF 따위 수식을 써서 다중조건을 따지며 데이타를 가져와 정렬하고 합산하는 기법의 종합세트가 된 바, 공유하고자 오픈백과에 올릴 생각을 함...

 

일단 표를 마우스로 긁어 복사, 엑셀시트에 붙이시고 수식 역시 긁어 CTRL+C, 해당 주소셀의 수식창에 붙인 후 찬찬히 뜯어보십사 함.

 

	B	C	D	E	F	G	H	I	J	K	L	M	N
2	요일	월
3	동	호	규격	수량		규격	수량		동	호	규격	수량	요일
4	201	105	A우유	2	201105A우유	A우유	3		201	105	A우유	2	월수금
5	201	105	C우유	1	201105C우유	C우유	1		203	304	B우유	3	화목토
6	201	203	B우유	5	201203B우유	B우유	5		204	106	A우유	1	월목
7	204	106	A우유	1	204106A우유	#REF!	###		201	203	B우유	5	월수금
8	0	0	#REF!	###	#REF!	#REF!	###		201	105	C우유	1	월

 

J4:N8과 같은 형식으로 주소별 목록 작성 (일단 위와 같이 같은 시트에서 만든 후 모든 작업을 마친 후 J4:N8의 주소를 잘라내기, 다른 시트에 붙이는 것을 추천함. 모든 수식이 바뀐 시트주소를 붙인 형태로 자동으로 바뀜. 향후 추가/삭제할 때는 행삽입/삭제를 하면 아래의 수식이 역시 자동으로 바뀜.)

 

아래 수식에서 B4, C4, D4, B5, C5, D5, H4, G5는 입력 후 CTRL+SHIFT+ENTER 해야 함 (배열수식).

 

<주소별 목록>

B4 =MIN(IF(NOT(ISERROR(FIND(C2,N4:N8,1))),J4:J8))

C4 =MIN(IF(NOT(ISERROR(FIND(C2,N4:N8,1)))*(J4:J8=B4),K4:K8))

D4 =INDEX(L$4:L$8,MIN(IF((NOT(ISERROR(FIND(C2,N$4:N$8,1))))*(J$4:J$8=B4)*(K$4:K$8=C4),ROW(L$4:L$8),66000))-ROW(L$4)+1,1)

E4 =SUMPRODUCT((J$4:J$8=B4)*(K$4:K$8=C4)*(L$4:L$8=D4)*(NOT(ISERROR(FIND(C$2,N$4:N$8,1))))*M$4:M$8)

F4 =B4&C4&D4  ; 계산을 위한 보조셀. 눈에 거슬리면 나중에 '숨기기'

B5 =MIN(IF((NOT(ISERROR(FIND(C$2,N$4:N$8,1))))*(COUNTIF(F$4:F4,J$4:J$8&K$4:K$8&L$4:L$8)=0),J$4:J$8))

C5 =MIN(IF((NOT(ISERROR(FIND(C$2,N$4:N$8,1))))*(J$4:J$8=B5)*(COUNTIF(F$4:F4,J$4:J$8&K$4:K$8&L$4:L$8)=0),K$4:K$8))

D5 =INDEX(L$4:L$8,IF(B4&C4=B5&C5,MIN(IF((NOT(ISERROR(FIND(C$2,N$4:N$8,1))))*(J$4:J$8=B5)*(K$4:K$8=C5)*(L$4:L$8<>D4),ROW(L$4:L$8),66000)),MIN(IF((NOT(ISERROR(FIND(C$2,N$4:N$8,1))))*(J$4:J$8=B5)*(K$4:K$8=C5),ROW(L$4:L$8),66000)))-ROW(L$4)+1,1)

;E4:F4 잡아 E5:F5에 복사

 

<출고 목록>

G4 =D4

H4 =SUM(IF(ISERROR(D$4:D$8),0,IF(D$4:D$8=G4,E$4:E$8,0)))

G5 =OFFSET(D$4,MIN(IF(COUNTIF(G$4:G4,D$4:D$7)=0,ROW(D$4:D$7),66000))-ROW(D$4),0)

; H4 잡아 H5에 복사. B5:H5 전체 잡아 아래로 데이타가 다 나올 때 까지 (D열에 #REF! 에러가 뜰 때 까지) 복사하면 작업 끝 - 요일 마다 다를 것이므로 주의를 요함.

 

수식 설명 :

B4:C4에서 요일을 만족하는 동, 호의 최소값을 가져와 표를 초기화함. 구한 값으로 D4에서는 구한 동, 호가 나오는 행 중 최소값을 계산, INDEX로 규격을 가져옴. B5:C5부터는 바로 위 행에서 구한 값 이외 요일을 만족하는 동, 호 수를 가져옴. (이 작업을 위해 보조셀 F4에 유동참조 비교를 위한 계산을 해둠) D5에서는 동호수가 직전 행과 다르면 D4와 같은 로직, 같으면 직전 규격과 다른 규격의 행만 탐색함.