조건에 맞는 숫자를 순서대로 출력

	B	C	D	E	F	G	H	I	J	K	L
2
3		기준	11	22	33	44	55	66	77	88	99
4		상황1	33		55			22		66	99
5		상황1 미사용값	11	44	77	88
6		상황2	99	33			88	77		66	44
7		상황2 미사용값	11	22	55
8		상황3		55		33					22
9		상황3 미사용값	11	44	66	77	88	99

 

D5 =IF(MIN(IF(COUNTIF(D4:L4,D$3:L$3)=0,D$3:L$3,MAX($D$3:$L$3)+1))>MAX($D$3:$L$3),"",MIN(IF(COUNTIF(D4:L4,D$3:L$3)=0,D$3:L$3,MAX($D$3:$L$3)+1)))

E5 =IF(ISNA(MATCH(D5,$D$3:$L$3,0)),"",IF(MIN(IF(COLUMN($D$3:$L$3)-COLUMN($C2)>MATCH(D5,$D$3:$L$3,0),IF(COUNTIF($D4:$L4,$D$3:$L$3)=0,$D$3:$L$3,MAX($D$3:$L$3)+1)),MAX($D$3:$L$3)+1)>MAX($D$3:$L$3),"",MIN(IF(COLUMN($D$3:$L$3)-COLUMN($C2)>MATCH(D5,$D$3:$L$3,0),IF(COUNTIF($D4:$L4,$D$3:$L$3)=0,$D$3:$L$3,MAX($D$3:$L$3)+1),MAX($D$3:$L$3)+1))))

 

둘 다 배열수식이므로 입력 후 CTRL+SHIFT+ENTER.

E5 잡아 L5까지 복사, D5:L5 잡아 D7, D9에 복사해 넣으면 작업표가 완성됩니다.

 

간단한 설명 : D5(최초 검색 셀)에서 MIN+배열 수식으로 조건을 만족하는 최소값 검색. 만약 조건 만족하는 셀이 없으면 빈 칸 표시. E5에서는 앞서 검색한 D5보다 오른쪽에 있는 데이타를 대상으로 (D5 위치를 MATCH로 구해 COLUMN함수로 비교) MIN+배열수식으로 조건을 만족하는 최소값 검색. 역시 그런 값이 없으면 (MAX+1이면) 빈칸 표시. MATCH함수 때 #N/A 표시를 막기 위해 사전 확인하여 필요하면 빈칸 출력함. 주의: MATCH 함수로 직전 찾은 데이타의 오른쪽만 검색하는 로직이므로 기준행이 오름차순으로 정렬되어 있어야 함.

 

감상문 : 이 작업을 함으로써 배열수식은 AND 구문에서는 먹지 않음을 확인함. IF내에서 일일이 나열해야 합니다. 글치만 우회해서라도 순서대로 나열하는 배열수식이 가능함을 확인한 것은 제 나름대로 소득이네요. 수식이 복잡해 보이지만 기본구조는 별 것 아닙니다. 찬찬히 따라가며 시도해보십사 합니다.