요일별 배달 목록 생성

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

 

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

 

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	요일	수
2	동	호	규격	수량		규격	수량		동	호	규격	수량	요일
3	101	1604	C우유	1	1011604C우유	C우유	6		108	105	A우유	2	월수금
4	103	105	A우유	2	103105A우유	A우유	4		106	304	B우유	3	화목토
5	105	203	B우유	5	105203B우유	B우유	5		107	507	A우유	1	월목
6	105	604	C우유	1	105604C우유	#REF!	#REF!		105	203	B우유	5	월수금
7	106	901	C우유	2	106901C우유	0	0		106	105	C우유	1	월
8	108	105	A우유	2	108105A우유	0	0		101	607	C우유	2	화
9	108	1002	C우유	2	1081002C우유	0	0		103	105	A우유	2	월수
10	0	0	#REF!	#REF!	#REF!	0	0		102	1007	A우유	1	화목
11	0	0	#REF!	#REF!	#REF!	0	0		101	1604	C우유	1	월수금
12	0	0	#REF!	#REF!	#REF!	0	0		108	1002	C우유	2	화수금
13	0	0	#REF!	#REF!	#REF!	0	0		106	1104	D우유	1	화금
14	0	0	#REF!	#REF!	#REF!	0	0		105	604	C우유	1	월화수목
15	0	0	#REF!	#REF!	#REF!	0	0		104	802	D우유	3	화
16	0	0	#REF!	#REF!	#REF!	0	0		106	901	C우유	2	수

 

<주소 목록> 

A3 =MIN(IF((ISNUMBER(FIND(B$1,M$3:M$16,1)))*(COUNTIF(E$2:E2,I$3:I$16&J$3:J$16&K$3:K$16)=0),I$3:I$16))
B3 =MIN(IF((ISNUMBER(FIND(B$1,M$3:M$16,1)))*(I$3:I$16=A3)*(COUNTIF(E$2:E2,I$3:I$16&J$3:J$16&K$3:K$16)=0),J$3:J$16))
C3 =INDEX(K$3:K$16,IF(A2&B2=A3&B3,MIN(IF((ISNUMBER(FIND(B$1,M$3:M$16,1)))*(I$3:I$16=A3)*(J$3:J$16=B3)*(K$3:K$16<>C2),ROW(K$3:K$16),66000)),MIN(IF((ISNUMBER(FIND(B$1,M$3:M$16,1)))*(I$3:I$16=A3)*(J$3:J$16=B3),ROW(K$3:K$16),66000)))-ROW(K$3)+1,1)
D3 =SUMPRODUCT((I$3:I$16=A3)*(J$3:J$16=B3)*(K$3:K$16=C3)*(NOT(ISERROR(FIND(B$1,M$3:M$16,1))))*L$3:L$16)
E3 =A3&B3&C3

 

<출고 목록>
F3 =C3
G3 =SUM(IF(ISERROR(C$3:C$16),0,IF(C$3:C$16=F3,D$3:D$16,0)))
F4 =OFFSET(C$3,MIN(IF(COUNTIF(F$3:F3,C$3:C$15)=0,ROW(C$3:C$15),66000))-ROW(C$3),0) 

 

A3, B3, C3, G3, F4는 배열수식입니다. 입력 후 CTRL+SHIFT+ENTER 하세요. 색상이 없는 셀은 드래그 복사입니다. I3:M16과 같은 형식으로 주소별 목록 작성 (일단 위와 같이 같은 시트에서 만든 후 모든 작업을 마친 후 I3:M16의 주소를 잘라내기, 다른 시트에 붙이는 것을 추천함. 모든 수식이 바뀐 시트주소를 붙인 형태로 자동으로 바뀜. 향후 추가/삭제할 때는 행삽입/삭제를 하면 아래의 수식이 역시 자동으로 바뀜.)