메인 컨텐츠로 가기

Excel에서 검색 가능한 드롭다운 목록에 대한 최고의 가이드

Excel에서 드롭다운 목록을 만들면 데이터 입력이 간소화되고 오류가 최소화됩니다. 그러나 데이터 세트가 클수록 긴 목록을 스크롤하는 것이 번거로워집니다. 항목을 입력하고 빠르게 찾는 것이 더 쉽지 않습니까? ㅏ "검색 가능한 드롭다운 목록"는 이러한 편리함을 제공합니다. 이 가이드에서는 Excel에서 이러한 목록을 설정하는 네 가지 방법을 안내합니다.


Video


Excel 365에서 검색 가능한 드롭다운 목록

Excel 365에는 데이터 유효성 검사 드롭다운 목록에 많은 기대를 모았던 기능, 즉 목록 내에서 검색하는 기능이 도입되었습니다. 검색 가능한 기능을 통해 사용자는 보다 효율적인 방법으로 항목을 빠르게 찾고 선택할 수 있습니다. 평소대로 드롭다운 목록을 삽입한 후 드롭다운 목록이 있는 셀을 클릭하고 입력을 시작하면 됩니다. 목록은 입력된 텍스트와 일치하도록 즉시 필터링됩니다.

이 경우에는 다음을 입력합니다. 셀 및 드롭다운 목록에서 검색어로 시작하는 도시를 필터링합니다. 같은 샌프란시스코샌디에고. 그런 다음 마우스로 결과를 선택하거나 화살표 키를 사용하고 Enter를 누를 수 있습니다.

노트:
  • 검색은 각 단어의 첫 글자부터 시작됩니다. 드롭다운 목록에서 단어의 시작 문자와 일치하지 않는 문자를 입력하면 목록에 일치하는 항목이 표시되지 않습니다.
  • 이 기능은 최신 버전의 Excel 365에서만 사용할 수 있습니다.
  • 사용 중인 Excel 버전이 이 기능을 지원하지 않는 경우 다음을 권장합니다. 검색 가능한 드롭 다운 목록 특징 Kutools for Excel. Excel 버전 제한은 없으며, 일단 활성화되면 관련 텍스트를 입력하기만 하면 드롭다운 목록에서 원하는 항목을 쉽게 검색할 수 있습니다. 자세한 단계보기.

검색 가능한 드롭다운 목록 만들기(Excel 2019 이상)

Excel 2019 이상 버전을 사용하는 경우 이 섹션의 방법을 사용하여 Excel에서 드롭다운 목록을 검색 가능하게 만들 수도 있습니다.

Sheet2의 A2:A2 범위(왼쪽 이미지)에 있는 데이터를 사용하여 Sheet8(오른쪽 이미지)의 셀 A1에 드롭다운 목록을 만들었다고 가정하고 다음 단계에 따라 목록을 검색 가능하게 만듭니다.

1단계. 검색 항목을 나열하는 도우미 열 만들기

여기에는 소스 데이터와 일치하는 항목을 나열하는 도우미 열이 필요합니다. 이 경우에는 도우미 열을 생성하겠습니다. D열 of Sheet1.

  1. 첫 번째 셀을 선택하세요. D1 D열에 "와 같은 열 헤더를 입력합니다.검색 결과" 이 경우
  2. D2 셀에 다음 수식을 입력하고 다음을 누릅니다. 엔터 버튼.
    =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")
노트:
  • 이 공식에서 A2 : A8 소스 데이터 범위입니다. 시트2!A2 드롭다운 목록의 위치는 드롭다운 목록이 Sheet2의 A2에 있음을 의미합니다. 자신의 데이터에 따라 변경하십시오.
  • Sheet2의 A2에 있는 드롭다운 목록에서 항목을 선택하지 않으면 위 이미지에 표시된 대로 수식에 소스 데이터의 모든 항목이 표시됩니다. 반대로 항목이 선택되면 D2는 해당 항목을 수식의 결과로 표시합니다.
2단계: 드롭다운 목록 재구성
  1. 드롭다운 목록 셀을 선택한 다음(이 경우 Sheet2의 A2 셀을 선택함) 선택으로 이동합니다. Data > 데이터 유효성 검사 > 데이터 유효성 검사.
  2. . 데이터 유효성 검사 대화 상자에서 다음과 같이 구성해야 합니다.
    1. 아래의 설정 탭에서 버튼의 출처 상자.
    2. 데이터 유효성 검사 대화 상자가 Sheet1로 리디렉션되면 2단계의 수식을 사용하여 셀(예: D1)을 선택하고 # 기호를 클릭하고 닫기 버튼을 클릭합니다.
    3. 로 이동 오류 경고 탭에서 유효하지 않은 데이터 입력 후 오류 경고 표시 확인란을 선택하고 마지막으로 OK 버튼을 클릭하여 변경 사항을 저장하십시오.
결과

이제 Sheet2의 A2 셀에 있는 드롭다운 목록을 검색할 수 있습니다. 셀에 텍스트를 입력하고 드롭다운 화살표를 클릭하여 드롭다운 목록을 확장하면 입력한 텍스트와 일치하도록 필터링된 목록이 즉시 표시됩니다.

노트:
  • 이 방법은 Excel 2019 이상 버전에서만 사용할 수 있습니다.
  • 이 방법은 한 번에 하나의 드롭다운 목록 셀에서만 작동합니다. Sheet3의 A8~A2 셀에서 드롭다운 목록을 검색할 수 있도록 하려면 각 셀에 대해 앞서 언급한 단계를 반복해야 합니다.
  • 드롭다운 목록 셀에 텍스트를 입력하면 드롭다운 목록이 자동으로 확장되지 않으므로 수동으로 확장하려면 드롭다운 화살표를 클릭해야 합니다.

쉽게 검색 가능한 드롭다운 목록 생성(모든 Excel 버전에 해당)

위 방법의 다양한 제한 사항을 고려하면 다음과 같은 매우 효과적인 도구가 있습니다. Kutools for Excel's 검색 가능한 드롭 다운 목록 특징. 이 기능은 모든 Excel 버전에서 사용할 수 있으며, 지정된 범위 내의 모든 드롭다운 목록뿐만 아니라 현재 워크시트 또는 현재 통합 문서의 모든 드롭다운 목록에서도 작동하므로 Excel에서 원하는 항목을 쉽게 검색할 수 있습니다. 드롭다운 목록.

다운로드 및 설치 Kutools for Excel, 고르다 쿠툴 > 드롭 다운 목록 > 검색 가능한 드롭 다운 목록 이 기능을 활성화하려면 에서 검색 가능한 드롭 다운 목록 대화 상자에서 다음을 수행해야합니다.

  1. 필요한 옵션을 지정하세요. 범위 설정 드롭 다운 목록.
    이 경우 지정된 범위의 드롭다운 목록을 검색 가능하게 만들어야 하므로 범위 지정 옵션을 선택하고 범위를 선택하세요.
  2. OK.
결과

지정된 범위의 드롭다운 목록 셀을 클릭하면 오른쪽에 목록 상자가 나타납니다. 목록을 즉시 필터링하려면 텍스트를 입력한 다음 항목을 선택하거나 화살표 키를 사용하고 Enter 키를 눌러 셀에 추가하세요.

노트:
  • 이 기능은 세 가지 유용한 옵션을 제공합니다.
    • 범위 지정: 이 옵션을 선택하고 셀 범위를 지정하면 지정된 범위의 드롭다운 목록만 검색 가능합니다.
    • 현재 통합 문서: 이 옵션을 선택하면 현재 통합 문서의 모든 드롭다운 목록을 검색할 수 있습니다.
    • 현재 워크 시트: 이 옵션을 선택하면 현재 워크시트의 드롭다운 목록만 검색 가능합니다.
  • 이 기능은 단어 내의 임의 위치에서 검색. 즉, 단어 중간이나 끝에 있는 문자를 입력하더라도 일치하는 항목이 계속 찾아 표시되므로 더욱 포괄적이고 사용자 친화적인 검색 환경을 제공합니다.
  • 이 기능에 대해 더 자세히 알고 싶으시면 이 페이지를 방문하십시오.
  • 이 기능을 적용하려면 다운로드 및 설치 Kutools for Excel 먼저.

콤보 상자 및 VBA를 사용하여 검색 가능한 드롭다운 목록 만들기(더 복잡함)

특정 드롭다운 목록 유형을 지정하지 않고 단순히 검색 가능한 드롭다운 목록을 생성하려는 경우. 이 섹션에서는 VBA 코드가 포함된 콤보 상자를 사용하여 작업을 수행하는 대체 접근 방식을 제공합니다.

아래 스크린샷과 같이 열 A에 국가 이름 목록이 있고 이제 이를 searchale 드롭다운 목록의 소스 데이터로 사용하려는 경우 다음과 같이 수행하면 됩니다.

워크시트에 데이터 유효성 검사 드롭다운 목록 대신 콤보 상자를 삽입해야 합니다.

  1. 경우 개발자 탭이 리본에 표시되지 않는 경우 활성화할 수 있습니다. 개발자 다음과 같이 탭.
    1. Excel 2010 이상 버전에서는 입양 부모로서의 귀하의 적합성을 결정하기 위해 미국 이민국에 > 옵션 . 그리고에 엑셀 옵션 대화 상자에서 리본 맞춤 설정 왼쪽 창에서 리본 사용자 지정 목록 상자로 이동하여 개발자 상자를 클릭 한 다음 OK 단추. 스크린 샷보기 :
    2. Excel 2007에서 Office 버튼> Excel 옵션. 에서 Excel 옵션 대화 상자에서 인기순 왼쪽 창에서 리본에 개발자 탭 표시 상자를 클릭하고 마지막으로 OK 버튼을 클릭합니다.
  2. 보여주신 후 개발자 탭에서 개발자 > 끼워 넣다 > 콤보 박스.
  3. 워크시트에 콤보 상자를 그리고 마우스 오른쪽 버튼으로 클릭한 다음 선택 등록 오른쪽 클릭 메뉴에서.
  4. . 등록 대화 상자에서 다음을 수행해야합니다.
    1. 선택 거짓 에서 자동 단어 선택 들;
    2. 에서 셀을 지정합니다. 연결된 셀 들. 이 경우 A12를 입력합니다.
    3. 선택 2-fmMatchEntryNone 에서 매치 엔트리 들;
    4. 타입 드롭 다운 목록목록 채우기 범위 들;
    5. 닫기 등록 대화 상자. 스크린 샷보기 :
  5. 이제 다음을 클릭하여 디자인 모드를 끄십시오. 개발자 > 디자인 모드.
  6. C2 등 빈 셀을 선택하고 아래 수식을 입력한 후 을 누릅니다. 엔터 버튼. 자동 채우기 핸들을 C9 셀로 끌어 동일한 수식으로 셀을 자동으로 채웁니다. 스크린샷 참조:
    =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    노트:
    1. $ A $ 12 로 지정한 셀입니다. 연결된 셀 4단계에서;
    2. 위 단계를 마친 후 이제 테스트할 수 있습니다. 콤보 상자에 문자 C를 입력하면 문자 C가 포함된 셀을 참조하는 수식 셀이 숫자 1로 채워지는 것을 볼 수 있습니다.
  7. D2 셀을 선택하고 아래 수식을 입력한 후 키를 누릅니다. 엔터 버튼. 그런 다음 자동 채우기 핸들을 D9 셀로 드래그합니다.
    =IF(C2=1,COUNTIF($C$2:C2,1),"")
  8. E2 셀을 선택하고 아래 수식을 입력한 후 다음을 누르세요. 엔터 버튼. 그런 다음 자동 채우기 핸들을 E9로 끌어 동일한 수식을 적용합니다.
    =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")
  9. 이제 이름 범위를 만들어야 합니다. 클릭하세요 공식 > 이름 정의.
  10. . 새 이름 대화 상자에서 다음을 입력하십시오. 드롭 다운 목록 에서 성함 상자에 아래 수식을 입력하십시오. 을 참고하여 상자를 클릭 한 다음 OK 버튼을 클릭합니다.
    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    
  11. 이제 다음을 클릭하여 디자인 모드를 켭니다. 개발자 > 디자인 모드. 그런 다음 콤보 상자를 두 번 클릭하여 엽니다. 응용 프로그램 용 Microsoft Visual Basic 창.
  12. 아래 VBA 코드를 복사하여 코드 편집기에 붙여넣습니다.
    VBA 코드 : 드롭 다운 목록을 검색 가능하게 만들기
    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub
  13. 를 누르십시오 다른 + Q 닫는 키 응용 프로그램 용 Microsoft Visual Basic 창.

이제부터 콤보 상자에 문자를 입력하면 퍼지 검색을 수행한 다음 해당 값을 목록에 나열합니다.

주의 사항: 나중에 사용할 수 있도록 VBA 코드를 유지하려면 이 통합 문서를 Excel 매크로 사용 통합 문서 파일로 저장해야 합니다.

최고의 사무 생산성 도구

Kutools for Excel - 군중에서 눈에 띄는 데 도움이

인기 기능: 중복 항목 찾기, 강조 표시 또는 식별  |  빈 행 삭제  |  데이터 손실 없이 열이나 셀 결합  |  수식없이 반올림 ...
슈퍼 VLookup: 다중 기준  |  다중 값  |  여러 시트에 걸쳐  |  퍼지 조회...
고급 드롭 다운 목록: 간편한 드롭다운 목록  |  종속 드롭다운 목록  |  다중 선택 드롭 다운 목록...
열 관리자: 특정 개수의 열 추가  |  열 이동  |  Toggle 숨겨진 열의 가시성 상태  열 비교 동일하고 다른 셀 선택 ...
특색 지어진 특징: 그리드 포커스  |  디자인보기  |  큰 수식 바  |  통합 문서 및 시트 관리자 | 리소스 라이브러리 (자동 텍스트)  |  날짜 선택기  |  워크 시트 결합  |  셀 암호화/해독  |  목록으로 이메일 보내기  |  슈퍼 필터  |  특수 필터 (굵게/기울임꼴/취소선 필터링...) ...
상위 15개 도구 세트12 본문 도구 (텍스트 추가, 문자 제거 ~)  |  50+ 거래차트 유형 (Gantt 차트 ~)  |  40+ 실용 방식 (생일을 기준으로 나이 계산 ~)  |  19 삽입 도구 (QR 코드 삽입, 경로에서 그림 삽입 ~)  |  12 매출 상승 도구 (숫자를 단어로, 환율 ~)  |  7 병합 및 분할 도구 (고급 결합 행, Excel 셀 분할 ~)  |  ... 그리고 더

Kutools for Excel 300개 이상의 기능을 자랑합니다. 필요한 것을 클릭 한 번으로 확인하세요...


Office Tab - Microsoft Office(Excel 포함)에서 탭 읽기 및 편집 활성화

  • 수십 개의 열린 문서간에 전환하는 데 XNUMX 초가 걸립니다!
  • 매일 수백 번의 마우스 클릭을 줄이고 마우스 손에 작별 인사를하십시오.
  • 여러 문서를 보고 편집할 때 생산성이 50% 향상됩니다.
  • Chrome, Edge 및 Firefox와 마찬가지로 Office(Excel 포함)에 효율적인 탭을 제공합니다.
Comments (67)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Perfect idea for me. But I have a problem with the "ROWS" formula.
I mean point 8.
When I use your formula (in the drop-down list I have nothing entered, as you can see in point 8) in the first cell is "INDIA".
And pick up the cells with the "spilled" error. What I need to change for the formula to work properly.

E1 - India
E2 - #SPILL!
E3 -#SPILL!
E4 - #SPILL!
E5 - #SPILL!
E6 -#SPILL!
E7 - #SPILL!
E8 - India
E9 - Brazil
E10 - Italy
E11 - Japan
E12 - United State
E13 - Francy
E14 - Germany

You also see that there are more poems appearing than yours.
This comment was minimized by the moderator on the site
Hi Przamek PL,
Sory, I cannot reproduce the problem you mentioned. Can you provide us with your data for tesing? If you don't mind, upload your sample file here.
This comment was minimized by the moderator on the site
Thank you for your message.
I was able to run your example correctly.
I have a reflection now ...
How to apply your solution to the UseForm form?

I would like to select a person from the list in the form, then I would have information about the age of this person elsewhere in the form. Such a simple example. Difficult?
This comment was minimized by the moderator on the site
Hi Przemek PF,
This method does not work in UserForm. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Somehow excel will not let me fill in the ListFillRange with ANYTHING. so also not the DropDownList. I did all the steps but am not able to get a flashing cursor and when I type no drop down list appears. any solutions?
This comment was minimized by the moderator on the site
Hi Marloes, This problem can't be solved yet. Make sure the ListFillRange is on the same sheet as your list box. 
This comment was minimized by the moderator on the site
I've just purchased kutools to use this function. Is it possible to have two or more different searchable drop down lists (i.e. referncing different lists of valid entries) on the same sheet?
This comment was minimized by the moderator on the site
Hi Marc,The feature does not support two or more different searchable drop down lists on the same sheet. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
how to use this dropdown in vba form any konw please reply
This comment was minimized by the moderator on the site
Hi, I made an action list for internal use with automatic email reminders in Excel, based on macro and vba. in a cell you select which person to send the reminder to, in a next cell you select which person to CC etc. Is it a good idea to copy this dropdownlist a few 100 times to every possible entry that I supply ? And is it possible to add a rule: Per row a particular person can only be selected once?
This comment was minimized by the moderator on the site
I have around 80000 data while running excel is hang
This comment was minimized by the moderator on the site
Sir How to use this in excel userform combobox....? plz help
This comment was minimized by the moderator on the site
Hi Sourav Singha,
Can't use it in a userform combobox. Sorry for the inconvenience.
This comment was minimized by the moderator on the site
Is there a way to make it call up a hyperlink? My email is
This comment was minimized by the moderator on the site
Hi Josh,
Sorry can;t help you with that yet.
This comment was minimized by the moderator on the site
I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
This comment was minimized by the moderator on the site
Hi Vrezh,
Sorry this kind of problem can't be solved yet. Thank you for your comment.
This comment was minimized by the moderator on the site
How can I use this? I have two problem
1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
I need for I can search with typing too, so simple(not active-x) combo is wrong.

2nd how set padding? - my combo text when I search is not see whole because itt has padding.

3th if my source is C column, how drop empty elements from list
This comment was minimized by the moderator on the site
Hi Steve Olah,
Sorry can't help you with that. Any question about Excel, please don’t hesitate to post in our forum: https://www.extendoffice.com/forum.html.
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations