엑셀 데이터 유효성 부분일치 검색 - 목록상자 만들기
데이터 유효성 부분일치 검색 목차 바로가기
영상강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 데이터 유효성 부분일치 목록상자예제파일[엑셀고급] 데이터 유효성 부분일치 목록상자완성파일
강의에 사용된 기초 함수
데이터유효성 부분일치 검색 동작원리 이해하기
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 동적범위를 데이터유효성 목록에 적용합니다. - [오류메시지] 탭으로 이동한 뒤, '유효하지 않은 데이터를 입력하면 오류메시지 표시' 체크박스를 비활성화 합니다. [확인]을 눌러 종료합니다.
데이터 유효성 오류메세지 표시를 비활성화합니다. - 선택한 범위에 데이터 유효성 부분일치 검색 목록상자 추가가 완료되었습니다.
데이터유효성 부분일치 검색 목록상자가 적용되었습니다.
본문에서는 데이터라고 나오기는 하네요.
14강 데디어 유효성 목록 상자 - 부분일치 검색ISNUMBER/SEARCH 공식과 동적범위를 활용한 부분일치 검색 목록상자 만들기00:31:59
수정했습니다.
확인해주셔서 감사드립니다.
매크로를 사용하면 파일이 가벼워질 수 있습니다. 다만 매크로에 대한 많은 공부가 필요할 수도 있습니다.
만약 매크로 사용이 어려울 경우에는, 엑셀 버전을 최신버전(MS365)으로 업데이트 하신 뒤 FILTER 함수로 매우 쉽게 문제를 해결하실 수도 있습니다.^^
답변이 도움이 되셨길 바랍니다. 감사합니다.
매크로를 이용한 것도 한번 강의해 주세요..
그런데 궁금한게 있어요 검색값은 E3으로 고정되어져 있는데
E4,E5,E6.... 계속해서 같은 결과값을 내고 싶으면 어떻게 해야 하나요?
너무너무 궁금해요 ㅠㅠ
뒷부분에 정리해드린 넓은 범위에 부분일치 검색 공식 목록상자 만들기 내용을 확인해보시겠어요?
공식에서 검색값이 들어가는 인수만 $E$3 으로 고정해주시면 됩니다 :)
업무에 살짝 변형하여 사용하고 있는데
검색 값 입력 시 꼭 엔터를 입력한다음 유효성 목록이 검색이 되는데
입력하는 실시간으로 바꿀려면 어떻게 해야할까요?
예를들어 가문을 검색값으로 입력하면 엔터를 치고 다시 검색어 입력 셀로 돌아가 돌아가
알트 + 아래 키를 눌러 드롭다운 메뉴에서 선택해주는데 그게아니고
셀에 가문 타이핑 후 알트 + 아래 키를 눌렀을때 바로 유효성목록이 검색되고 드롭다운 메뉴에도 바로 보이게 끔요
말씀해주신 내용은 엑셀 기능이나 함수만으로는 구현이 불가능합니다.
강의에서 소개해드린 내용은 반드시 값이 먼저 입력 되어야만, 함수를 통해 목록이 갱신된 후 목록상자로 출력할 수 있기 때문에 말씀하신 방법은 구현이 불가능합니다.
만약 개발도구 ActiveX 콤보박스를 사용하셔도 괜찮으시면, 원하시는 내용을 구현할 수 있습니다.
목록 원본으로 입력한 값이 올바른 범위가 아니여서 그렇습니다.
이름관리자에서 입력하신 범위가 올바른 범위로 잘 반환되는지 다시 한번 확인해보세요.^^ 또는 함게 올려드린 완성파일을 열어 수식을 검토해보시길 바랍니다.
감사합니다.
제가 이렇게 하려는 이유는 raw data와 업무용 파일을 나눠서 사용하고 싶어서 그렇습니다
동적범위를 사용할 경우 다른 파일의 시트를 참조하는 방법은 동적범위에 사용하는 함수 OFFSET 특성상 불가능합니다.