엑셀 데이터 유효성 부분일치 검색 - 목록상자 만들기
데이터 유효성 부분일치 검색 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 데이터 유효성 부분일치 목록상자예제파일[엑셀고급] 데이터 유효성 부분일치 목록상자완성파일
강의에 사용된 기초 함수
데이터유효성 부분일치 검색 동작원리 이해하기
ISNUMBER + SEARCH 부분일치 검색공식
ISNUMBER / SEARCH 부분일치 검색 공식의 동작원리는 아래 관련 포스트에서 단계별로 자세히 설명해 드렸습니다. 특정 단어 포함여부 검색을 하는 ISNUMBER/SEARCH 공식의 자세한 설명이 궁금하신 분은 아래 관련 링크를 참고하세요.
IF/MAX 함수 + 확장범위 응용하기
IF 함수와 MAX 함수 그리고 확장범위를 같이 응용하면, 특정 조건을 만족할 경우에 1씩 증가하는 순번이 출력되도록 만들 수 있습니다. 공식은 아래와 같습니다.
=IF(조건,MAX($머릿글셀:머릿글셀)+1,0)위 공식에 사용된 확장범위에 대한 내용은 아래 관련 포스트에서 자세히 설명해드렸습니다.
예를 들어, 아래 공식을 시트의 A2셀부터 아래방향으로 자동채우기 한다고 가정하겠습니다.
=IF(ISEVEN(ROW()),MAX($A$1:A1)+1,0)- 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 - 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 - 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씩 증가하는 순번을 만들 수도 있습니다.
=ROWS($A$1:A1)
'함수를 입력한 뒤 아래 방향으로 자동채우기 하면 1,2,3,4... 순번이 생성됩니다.=COLUMNS($A$1:A1)
'함수를 입력한 뒤 오른쪽 방향으로 자동채우기 하면 1,2,3,4... 순번이 생성됩니다.COUNTIF 함수 동적범위 만들기
이전 강의에서 OFFSET 함수와 COUNTA 함수를 사용한 동적범위를 알아봤습니다. 대부분의 경우는 OFFSET / COUNTA 동적범위를 사용할 수 있지만, '수식으로 반환되는 값이 빈칸인 값을 제외해야 할 경우' 에는 OFFSET / COUNTA 동적범위를 사용할 수 없습니다.
COUNTA 함수는 겉으로는 '빈칸' 이지만 안에 수식이 입력되어 있으면 해당 셀을 개수에 포함합니다.
따라서, 수식의 결과값으로 빈칸을 반환하는 셀을 동적범위에서 제외해야 할 경우에는 아래에 적어드린 OFFSET / COUNTIF 동적범위를 사용합니다.
=OFFSET($시작셀,,,COUNTIF($범위,"*?")위 공식에서 사용된 "*?" 는 와일드카드입니다. "*?"를 조건으로 입력하면 '셀의 결과값으로 최소 한 글자를 포함하는 셀'을 선택하게 됩니다. 와일드카드에 대한 내용은 아래 관련 포스트에서 자세히 설명해 드렸습니다.
데이터 유효성 부분일치 검색 목록상자 만들기 (365 이전버전)
- 예제파일의 [대한민국영화목록(ㄱ)-365이전] 시트로 이동합니다. E3셀에 검색할 단어를 입력합니다. 예제로 '가문'을 입력하겠습니다.
예제파일 E3셀에 검색할 단어를 입력합니다. - B3셀에 아래 수식을 입력한 뒤, 아래 방향으로 자동채우기 합니다. 수식을 자동채우기 하면 영화제목에 '가문'을 포함할 경우 1씩 증가하는 순번이 반환됩니다.
=IF(ISNUMBER(SEARCH($E$3,C3)),MAX($B$2:B2)+1,0)
수식을 입력하면 단어를 포함하는 제목 옆에 순번이 출력됩니다. - 예제파일의 G3셀에 아래 수식을 입력한 뒤, 아래방향으로 넉넉하게 자동채우기 합니다. 예제로 G31셀까지 자동채우기 하겠습니다. 수식을 자동채우기하면 '가문'을 포함하는 영화 제목만 나열되는 것을 확인할 수 있습니다.
=IFERROR(VLOOKUP(ROWS($G$2:G2),B:C,2,0),"")
특정 단어를 포함하는 영화 목록이 출력됩니다. - E3셀에 검색할 단어를 변경합니다. 단어를 변경하면 해당 단어를 포함하는 영화제목만 목록으로 반환되는 것을 확인할 수 있습니다.
단어를 변경하면 나타나는 영화 목록도 변경됩니다. - 동적범위를 생성합니다. [수식] - [이름관리자]로 이동하거나, 단축키 CTRL + F3 키를 눌러 이름관리자를 실행합니다. 이후 [새로만들기] 버튼을 클릭하여 아래와 같이 새로운 이름정의범위를 생성합니다. 사용된 동적범위의 동작원리는 영상강의에서 자세히 설명해드렸습니다.
이름 : 유효성목록상자
참조대상 : =OFFSET('대한민국영화목록(ㄱ)-365이전'!$G$3,,,COUNTIF('대한민국영화목록(ㄱ)-365이전'!$G$3:$G$31,"*?"))
'유효성목록상자'라는 이름으로 이름정의범위를 생성합니다. - 데이터유효성 목록 상자를 적용할 셀인 E3셀을 선택합니다. [데이터] - [데이터 유효성 검사]로 이동합니다. 제한대상은 '목록'으로 선택합니다.
데이터 유효성 검사로 목록을 적용합니다. - '원본' 을 선택한 뒤, 키보드 F3키를 누르면 방금 전에 생성한 이름정의범위가 나타납니다. '유효성목록상자'를 선택합니다.
원본 범위로 '유효성목록상자' 이름 정의범위를 선택합니다. - [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
오류메시지 표시를 비활성화 합니다. - 데이터 유효성 부분일치 검색 목록상자가 완성되었습니다. 원하는 단어를 검색한 뒤, ALT + ↓ 를 입력하면 해당 단어를 포함하는 영화제목이 목록상자로 출력됩니다.
부분일치 검색 데이터 유효성 목록이 완성되었습니다.
데이터 유효성 부분일치 검색 목록상자 만들기 (Microsoft 365 버전)
Microsoft 365 버전 사용자는 FILTER 함수와 # 기호를 이용한 동적범위를 사용하여 아주 간단하게 부분일치 검색 목록상자를 만들 수 있습니다.
- 예제파일의 [대한민국영화목록(ㄱ)-365사용자] 시트로 이동합니다. D3셀에 검색할 단어를 입력합니다. 예제로 '결혼'을 입력하겠습니다.
부분일치로 검색할 단어를 입력합니다. - F3셀에 아래 수식을 입력합니다. 수식을 입력하면 '결혼'을 포함하는 영화제목이 동적배열로 반환됩니다.
=FILTER(B3:B132,ISNUMBER(SEARCH(D3,B3:B132)))
F3셀에 FILTER 함수를 입력합니다. - FILTER 함수 이외에도 UNIQUE 함수를 사용하여 고유값만 반환하거나, SORT 함수를 사용하여 영화제목을 오름차순 또는 내림차순으로 정렬할 수도 있습니다.
SORT 함수와 UNIQUE 함수를 같이 응용할 수도 있습니다. - 데이터유효성 목록상자를 적용할 D3셀을 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택합니다. '원본'에는 아래 수식을 입력합니다. '#' 기호를 사용하면 배열로 반환된 범위를 동적으로 선택할 수 있습니다. (365 사용자)
=F3#
# 기호를 이용한 동적범위를 목록상자에 적용합니다. - [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
유효성 오류메세지 표시를 비활성화 합니다. - 데이터 유효성 부분일치 검색 목록상자가 마무리 되었습니다. 이처럼 365 사용자는 아주 간단하게 목록상자를 생성할 수 있습니다.
데이터 유효성 부분일치 검색 목록이 완성되었습니다.
넓은 범위에 부분일치 검색 목록상자 만들기 (Microsoft 365 버전)
Microsoft 365 버전 사용자는 TRANSPOSE 함수 하나만 더 사용하면 부분일치 검색 목록상자를 넓은 범위에도 손쉽게 생성할 수 있습니다.
- 예제파일의 [대한민국영화목록(ㄱ)-넓은범위적용] 시트로 이동합니다. D3셀과 D4셀에 검색할 단어를 입력합니다. 예제로 '가문'과 '결혼'을 입력하겠습니다.
예제파일의 D3, D4, D5... 셀에 검색할 단어를 입력합니다. - F3셀에 아래 수식을 입력한 뒤, 아래 방향으로 자동채우기 합니다.
=TRANSPOSE(FILTER($B$3:$B$132,ISNUMBER(SEARCH(D3,$B$3:$B$132))))
TRANSPOSE 함수와 FILTER 함수 응용공식을 F3셀에 입력한 뒤 자동채우기 합니다. - D3:D8 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택한 뒤, '원본'에는 아래 수식을 입력합니다.
=$F3#
# 기호를 이용한 동적범위를 데이터유효성 목록에 적용합니다. - [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
유효성 검사 오류메시지 표시를 비활성화합니다. - 부분일치 검색 목록상자를 넓은 범위에 생성 하였습니다.
데이터 유효성 검사 목록을 넓은 범위에 적용하였습니다.
넓은 범위에 부분일치 검색 목록상자 만들기 (365 이전버전)
365 이전 버전 사용자는 부분일치 검색 목록상자를 내장함수만으로 구현할 수 없습니다. 따라서 홈페이지에 올려드린 xFILTER 함수 추가기능을 설치하신 뒤, 아래 순서대로 진행하시면 넓은 범위에 부분일치 검색 목록상자를 생성할 수 있습니다.
추가기능 함수 설치 및 사용시 주의사항은 아래 관련 링크를 참고하세요.
- 예제파일의 [대한민국영화목록(ㄱ)-넓은범위적용(365이전)} 시트로 이동합니다. D3셀과 D3셀에 검색할 단어를 입력합니다. 예제로 '가문'과 '결혼'을 입력하겠습니다.
예제파일 D3, D4 .. 셀에 데이터유효성 목록으로 검색할 단어를 입력합니다. - F3:AC3 범위를 선택한 뒤, 아래 공식을 CTRL + SHIFT + ENTER 로 배열수식으로 입력합니다. 배열수식을 입력하면 '가문'을 포함하는 영화 목록이 나열되며, 나머지 셀에는 #N/A 오류가 출력됩니다.
=TRANSPOSE(xFilter($B$3:$B$132,ISNUMBER(SEARCH($D3,$B$3:$B$132)),,TRUE))
xFILTER 함수를 미리 선택한 범위에 배열수식으로 입력합니다. - F3:AC3 범위를 선택한 뒤, 아래방향으로 자동채우기 합니다.
입력한 수식을 아래방향으로 자동채우기 합니다. - D3:D8 범위를 선택한 뒤, [데이터] - [데이터 유효성 검사]로 이동합니다. '제한대상은' 목록을 선택한 뒤, '원본'에는 아래 수식을 입력합니다. 수식의 동작원리는 영상강의에서 단계별로 자세히 설명해드렸습니다.
=OFFSET($F3,,,,COUNTIF($F3:$AC3,"<>#N/A"))
OFFSET/COUNTIF 동적범위를 데이터유효성 목록에 적용합니다. - [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
데이터 유효성 오류메세지 표시를 비활성화합니다. - 선택한 범위에 데이터 유효성 부분일치 검색 목록상자 추가가 완료되었습니다.
데이터유효성 부분일치 검색 목록상자가 적용되었습니다.
