데이터 유효성 목록 상자 - 부분일치 검색

데이터 유효성 부분일치 검색의 동작 원리

ISNUMBER 함수와 SEARCH 함수를 조합한 부분일치 검색 공식의 동작 원리는 아래 관련 포스트에서 단계별로 자세히 설명해 드렸습니다. 특정 단어의 포함 여부를 확인하는 ISNUMBER/SEARCH 공식의 상세한 동작 방식이 궁금하신 분은 아래 관련 링크를 참고하시기 바랍니다.

IF 함수와 MAX 함수, 그리고 확장 범위의 응용

IF 함수와 MAX 함수, 그리고 확장 범위를 함께 응용하면 특정 조건을 만족할 때마다 1씩 증가하는 순번을 출력하는 수식을 작성할 수 있습니다. 공식은 다음과 같습니다.

이해를 돕기 위해, 아래 공식을 시트의 A2셀부터 아래 방향으로 자동 채우기 한다고 가정해 보겠습니다.

1. A2셀의 경우 : =IF(ISEVEN(ROW()),MAX($A$1:A1)+1,0)
   ' ROW() 함수는 2를 반환하고, ISEVEN 함수는 TRUE를 반환합니다.
   ' MAX($A$1:A1)은 0이므로 MAX($A$1:A1)+1은 1을 반환합니다.
   =IF(ISEVEN(ROW()),MAX($A$1:A1)+1,0)
   =IF(TRUE,0+1,0)
   =1
2. A3셀의 경우 : =IF(ISEVEN(ROW()),MAX($A$1:A2)+1,0)
   ' ROW() 함수는 3을 반환하고, ISEVEN 함수는 FALSE를 반환합니다.
   ' -> 결과적으로 0을 반환하며 마무리됩니다.
   =IF(ISEVEN(ROW()),MAX($A$1:A2)+1,0)
   =IF(FALSE,1+1,0)
   =0
3. A4셀의 경우 : =IF(ISEVEN(ROW()),MAX($A$1:A3)+1,0)
   ' ROW() 함수는 4를 반환하고, ISEVEN 함수는 TRUE를 반환합니다.
   ' MAX($A$1:A3)은 1이므로 MAX($A$1:A3)+1은 2를 반환합니다.
   =IF(ISEVEN(ROW()),MAX($A$1:A3)+1,0)
   =IF(TRUE,1+1,0)
   =2

ROWS 함수와 확장 범위의 응용

ROWS 함수와 확장 범위를 응용하면 시작 셀부터 아래 방향으로 1씩 증가하는 순번을 손쉽게 만들 수 있습니다. 같은 원리로 COLUMNS 함수를 활용하면 시작 셀부터 오른쪽 방향으로 1씩 증가하는 순번도 동일하게 구현할 수 있습니다.

COUNTIF 함수로 동적 범위 만들기

이전 강의에서 OFFSET 함수와 COUNTA 함수를 활용한 동적 범위를 살펴보았습니다. 대부분의 상황에서는 OFFSET/COUNTA 동적 범위로 충분하지만, 수식의 결과가 빈칸인 셀을 동적 범위에서 제외해야 하는 경우에는 OFFSET/COUNTA 조합으로는 정확한 결과를 얻을 수 없습니다.

COUNTA 함수는 화면상으로는 빈칸이더라도 수식이 입력된 셀을 개수에 포함합니다.

따라서 수식의 결과값으로 빈칸을 반환하는 셀을 동적 범위에서 제외해야 할 때는 아래의 OFFSET/COUNTIF 조합 동적 범위를 사용합니다.

위 공식에 사용된 "*?"는 와일드카드 표현입니다. 조건 자리에 "*?"를 입력하면 결과값이 최소 한 글자 이상인 셀만 선택하게 됩니다. 와일드카드의 자세한 동작 방식은 아래 관련 포스트에서 별도로 설명해 두었습니다.

데이터 유효성 부분일치 검색 목록 상자 만들기 (Microsoft 365 이전 버전)

1. 예제 파일의 [대한민국영화목록(ㄱ)-365이전] 시트로 이동합니다. E3셀에 검색할 단어를 입력합니다. 예시로 '가문'을 입력하겠습니다.
2. B3셀에 아래 수식을 입력한 뒤 아래 방향으로 자동 채우기 합니다. 수식을 자동 채우기 하면 영화 제목이 '가문'을 포함할 때마다 1씩 증가하는 순번이 반환됩니다.
   =IF(ISNUMBER(SEARCH($E$3,C3)),MAX($B$2:B2)+1,0)

   

3. 예제 파일의 G3셀에 아래 수식을 입력한 뒤 아래 방향으로 넉넉하게 자동 채우기 합니다. 예시로는 G31셀까지 자동 채우기 하겠습니다. 수식을 자동 채우기 하면 '가문'을 포함하는 영화 제목만 차례대로 나열되는 것을 확인할 수 있습니다.
   =IFERROR(VLOOKUP(ROWS($G$2:G2),B:C,2,0),"")

   

4. E3셀의 검색 단어를 변경합니다. 단어를 바꾸면 해당 단어를 포함하는 영화 제목만 목록으로 반환되는 것을 확인할 수 있습니다.
5. 이번에는 동적 범위를 생성합니다. [수식] - [이름 관리자]로 이동하거나 단축키 CTRL + F3 키를 눌러 이름 관리자를 실행합니다. 이어서 [새로 만들기] 버튼을 클릭한 뒤 아래와 같이 새로운 이름 정의 범위를 생성합니다. 사용된 동적 범위의 동작 원리는 영상 강의에서 단계별로 자세히 설명해 드렸습니다.
   이름 : 유효성목록상자
   참조대상 : =OFFSET('대한민국영화목록(ㄱ)-365이전'!$G$3,,,COUNTIF('대한민국영화목록(ㄱ)-365이전'!$G$3:$G$31,"*?"))

   

6. 데이터 유효성 목록 상자를 적용할 셀인 E3셀을 선택합니다. [데이터] - [데이터 유효성 검사]로 이동한 뒤 제한 대상은 '목록'으로 선택합니다.
7. '원본'을 선택한 뒤 키보드 F3 키를 누르면 방금 생성한 이름 정의 범위가 나타납니다. 목록에서 '유효성목록상자'를 선택합니다.
8. [오류 메시지] 탭으로 이동한 뒤 '유효하지 않은 데이터를 입력하면 오류 메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
9. 데이터 유효성 부분일치 검색 목록 상자가 완성되었습니다. 원하는 단어를 검색한 뒤 ALT + ↓ 키를 입력하면 해당 단어를 포함하는 영화 제목이 목록 상자로 출력됩니다.

데이터 유효성 부분일치 검색 목록 상자 만들기 (Microsoft 365 버전)

Microsoft 365 버전 사용자는 FILTER 함수와 # 기호를 활용한 동적 범위를 사용해 부분일치 검색 목록 상자를 훨씬 간단하게 만들 수 있습니다.

1. 예제 파일의 [대한민국영화목록(ㄱ)-365사용자] 시트로 이동합니다. D3셀에 검색할 단어를 입력합니다. 예시로 '결혼'을 입력하겠습니다.
2. F3셀에 아래 수식을 입력합니다. 수식을 입력하면 '결혼'을 포함하는 영화 제목이 동적 배열로 한 번에 반환됩니다.
   =FILTER(B3:B132,ISNUMBER(SEARCH(D3,B3:B132)))

   

3. FILTER 함수 외에도 UNIQUE 함수를 함께 사용하면 고유값만 반환하거나, SORT 함수를 사용해 영화 제목을 오름차순 또는 내림차순으로 정렬할 수도 있습니다.
4. 데이터 유효성 목록 상자를 적용할 D3셀을 선택한 뒤 [데이터] - [데이터 유효성 검사]로 이동합니다. 제한 대상은 '목록'을 선택합니다. '원본'에는 아래 수식을 입력합니다. '#' 기호를 사용하면 배열로 반환된 범위를 동적으로 선택할 수 있습니다(365 사용자 한정).
   =F3#

   

5. [오류 메시지] 탭으로 이동한 뒤 '유효하지 않은 데이터를 입력하면 오류 메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
6. 데이터 유효성 부분일치 검색 목록 상자가 완성되었습니다. 이처럼 365 사용자는 매우 간단한 절차만으로 목록 상자를 생성할 수 있습니다.

넓은 범위에 부분일치 검색 목록 상자 만들기 (Microsoft 365 버전)

Microsoft 365 버전 사용자는 TRANSPOSE 함수 하나만 추가로 활용하면 부분일치 검색 목록 상자를 넓은 범위에도 손쉽게 적용할 수 있습니다.

1. 예제 파일의 [대한민국영화목록(ㄱ)-넓은범위적용] 시트로 이동합니다. D3셀과 D4셀에 검색할 단어를 각각 입력합니다. 예시로 '가문'과 '결혼'을 입력하겠습니다.
2. F3셀에 아래 수식을 입력한 뒤 아래 방향으로 자동 채우기 합니다.
   =TRANSPOSE(FILTER($B$3:$B$132,ISNUMBER(SEARCH(D3,$B$3:$B$132))))

   

3. D3:D8 범위를 선택한 뒤 [데이터] - [데이터 유효성 검사]로 이동합니다. 제한 대상은 '목록'으로 선택한 뒤 '원본'에는 아래 수식을 입력합니다.
   =$F3#

   

4. [오류 메시지] 탭으로 이동한 뒤 '유효하지 않은 데이터를 입력하면 오류 메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
5. 부분일치 검색 목록 상자를 넓은 범위까지 확장하였습니다.

넓은 범위에 부분일치 검색 목록 상자 만들기 (Microsoft 365 이전 버전)

Microsoft 365 이전 버전 사용자는 내장 함수만으로 넓은 범위의 부분일치 검색 목록 상자를 구현할 수 없습니다. 이 경우 홈페이지에 올려드린 xFILTER 함수 추가 기능을 설치한 뒤 아래 순서를 따라 진행하시면, 동일한 결과를 넓은 범위에서도 만들 수 있습니다.

추가 기능 설치 및 사용 시 주의 사항은 아래 관련 링크에서 자세히 안내해 드렸습니다.

1. 예제 파일의 [대한민국영화목록(ㄱ)-넓은범위적용(365이전)] 시트로 이동합니다. D3셀과 D4셀에 검색할 단어를 각각 입력합니다. 예시로 '가문'과 '결혼'을 입력하겠습니다.
2. F3:AC3 범위를 선택한 뒤 아래 공식을 CTRL + SHIFT + ENTER 로 배열 수식 형태로 입력합니다. 배열 수식을 입력하면 '가문'을 포함하는 영화 목록이 나열되며, 나머지 셀에는 #N/A 오류가 출력됩니다.
   =TRANSPOSE(xFilter($B$3:$B$132,ISNUMBER(SEARCH($D3,$B$3:$B$132)),,TRUE))

   

3. F3:AC3 범위를 선택한 뒤 아래 방향으로 자동 채우기 합니다.
4. D3:D8 범위를 선택한 뒤 [데이터] - [데이터 유효성 검사]로 이동합니다. 제한 대상은 '목록'으로 선택한 뒤 '원본'에는 아래 수식을 입력합니다. 수식의 동작 원리는 영상 강의에서 단계별로 자세히 설명해 드렸습니다.
   =OFFSET($F3,,,,COUNTIF($F3:$AC3,"<>#N/A"))

   

5. [오류 메시지] 탭으로 이동한 뒤 '유효하지 않은 데이터를 입력하면 오류 메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
6. 선택한 범위에 데이터 유효성 부분일치 검색 목록 상자 적용이 완료되었습니다.