짤막한 강좌

안녕하세요.

실습 파일 다운로드

완성 파일 다운로드

INDEX 함수를 이용해서 구현하는 방법을 소개했었습니다.

2023.10.20 - VLOOKUP 함수로는 구할 수 없는 조건에 해당하는 여러 개의 값을 찾아 나타내는 방법

지난 2021년 11월 출강에서 질문 받았던 윤**님의 업무에서 사용하는 파일입니다.

윤**님이 직접 만들어서 사용하던 중 저에게 질문을 해서 조금 수정했었습니다.

INDEX 함수를 이용해서 만들어졌고, FILTER 함수를 이용해서 수정하려고 했는데 손 놓고 있다가 이번에 다시 살펴봅니다.

데이터 시트에 데이터가 있습니다.

출력 시트는 INDEX 함수를 이용해서 구현되어 있습니다.

소개하려는 부분은 출력2 시트입니다.

먼저 출력 시트에 만들어져 있는 내용을 소개합니다.

[K6] 셀에 이름, [K7] 셀에 생년월일을 입력하면 [K8] 셀에 이름과 생년월일을 더해서 검색조건을 만들고 수식으로 데이터 시트에서 찾아 [C11:G20] 셀 범위에 결과를 나타냅니다.

[G11:H20] 셀 범위는 좀 특이한 서식이 설정되어 있는데, 셀 병합을 한 것이 아니라 [셀 서식]-[맞춤]에서 '선택 영역의 가운데로'를 설정해서 셀 중간에 걸쳐져 있는 것으로 보이는 것입니다.

[C11] 셀 수식입니다.

=IFERROR(INDEX(시작일,SMALL(IF((출력!$K$8=검색조건),MATCH(ROW(시작일),ROW(시작일),0),""),ROWS($A$2:A2))),"")

[D11] 셀 수식은 좀 다릅니다.

=IFERROR(IF(INDEX(종료일,SMALL(IF((출력!$K$8=검색조건),MATCH(ROW(종료일),ROW(종료일),0),""),ROWS($A$2:A2)))<TODAY(),INDEX(종료일,SMALL(IF((출력!$K$8=검색조건),MATCH(ROW(종료일),ROW(종료일),0),""),ROWS($A$2:A2))),TODAY()),"")

IF 함수를 추가해서 종료일이 활동 확인서를 발급하는 오늘보다 뒤의 날짜인 경우엔 오늘 날짜를 나타냅니다.

수식을 좀 더 쉽게 이해할 수 있도록 [이름 정의]를 했습니다.

작성된 수식은 배열 수식이기 때문에 수식을 마무리할 때 <Ctrl + Shift + Enter>를 눌러야 합니다.

이제 출력2 시트를 소개합니다.

서식은 출력 시트와 같습니다.

[C11] 셀 수식입니다.

=FILTER(출력2데이터,K8=검색조건,"")

엑셀 2019 이상에서 쓸 수 있는 FILTER 함수를 써서 수식이 단순합니다.

그런데 그대로 쓸 수 없습니다.

위에서 소개한 종료일 문제를 해결해야 합니다.

수식을 [C11] 셀에 입력하지 않고 [C32] 셀에 입력한 뒤 나타난 결과를 [C11] 셀에 가져오면서 필요한 처리를 해서 나타내는 것으로 문제를 해결합니다.

서식이 적용되어 있지 않기 때문에 [C32:D32] 셀 날짜가 숫자 데이터로 표시됩니다.

결과를 나타내는 것에는 문제가 없어서 따로 수정하지는 않습니다.

[C32] 셀의 기존 수식은 지우고, 새로 수식을 작성합니다.

=IF(C32<>"",C32,"")

[C32] 셀을 [G32] 셀까지 채우기하고 [채우기 옵션]에서 '서식 없이 채우기'를 선택합니다.

[D11] 셀 수식은 수정합니다.

=IF(D32<>"",IF(D32>TODAY(),TODAY(),D32),"")

[C11:G11] 셀 범위 선택한 뒤 [G20] 셀까지 채우기 합니다.

여기까지 수식을 작성해서 출력 시트에서 구현한 것과 똑같아 졌습니다.

그런데 출력 시트와 출력2 시트 둘 다에 문제가 있습니다.

경력사항을 나타내는 영역이 10개 행인데, 경력사항이 10개 이상인 경우에는 최근 경력을 표시하지 않는 문제가 있습니다.

활동 확인서 서식을 바꿔서 10개 이상인 경우에 모두 표시하도록 할 순 있지만 1장의 종이에 인쇄를 해야 하기 때문에 무조건 늘릴 수는 없습니다.

이 문제를 해결하기 위해서 출력(수정시-값붙이기) 시트가 준비되어 있습니다.

자동으로 처리되도록 하는 것보다 문제가 발생하는 경우가 많지 않기 때문에, 문제가 생기면 수작업을 하는 것이 더 효율적입니다.

INDEX 함수를 이용한 경우에는 10개 이상인지도 확인할 방법이 없습니다.

FILTER 함수를 이용하면 IF 문을 하나 추가해서 10개 이상인지 확인할 수 있습니다.

[C32] 셀 수식을 수정합니다.

=IF(ROWS(FILTER(출력2데이터,K8=검색조건,""))>10,"오류",FILTER(출력2데이터,K8=검색조건,""))

[K6] 셀에 박땡숙, [K7] 셀에 561120을 입력합니다.

현재 데이터 중 경력사항이 10건 이상인 유일한 데이터입니다.

출력(수정시-값붙이기) 시트에서 사용할 수 있도록 전체 경력사항을 나타내는 수식을 추가합니다.

[C43] 셀에 수식을 작성합니다.

=FILTER(출력2데이터,K8=검색조건,"")

필요하신 분께 도움이 되길 바랍니다.