Excel 에서 드롭다운 목록 검색 가능하게 만들기을(를) 위한 최종 가이드

Excel 에서 드롭다운 목록을(를) 만들면 데이터 입력이 간소화되고 오류를 최소화할 수 있습니다。 그러나 더 큰 데이터 세트의 경우 긴 목록을 스크롤하는 것이 번거로워집니다。 항목을 입력만으로 빠르게 찾을 수 있다면 훨씬 편리하지 않을까요？ “드롭다운 목록 검색 가능하게 만들기"은 이러한 편의성을 제공합니다. 이 가이드에서는 Excel 에서 이러한 목록을 설정하는 네 가지 방법을 안내합니다。

• Excel 에서 드롭다운 목록 검색 가능하게 만들기 365
• 드롭다운 목록 검색 가능하게 만들기 만들기(Excel 2019 이상용)
• 모든 Excel 버전에서 쉽게 드롭다운 목록 검색 가능하게 만들기 만들기
• 콤보 상자와 VBA 로 드롭다운 목록 검색 가능하게 만들기 만들기(더 복잡함)

동영상： 드롭다운 목록 검색 가능하게 만들기 만들기

Excel 에서 드롭다운 목록 검색 가능하게 만들기 365

Excel 365 은(는) 데이터 유효성 검사 드롭다운 목록에 오랫동안 기다려온 기능인 목록 내 검색 기능을 도입했습니다。 검색 가능한 기능을 사용하면 사용자가 더 효율적으로 항목을 빠르게 찾아 선택할 수 있습니다。 평소처럼 드롭다운 목록을(를) 삽입한 후 드롭다운 목록이(가) 있는 셀을 클릭하고 입력을 시작하면 목록이 즉시 입력한 텍스트와 일치하도록 필터링됩니다。

이 예시에서는 셀에San을(를) 입력하면 드롭다운 목록이(가) 검색어San으로 시작하는 도시를 필터링합니다San Francisco및San Diego와 같이 말입니다。 그런 다음 마우스로 결과를 선택하거나 화살표 키를 사용해 Enter 키를 누르면 됩니다。

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

Excel 2019 이상 버전을 사용 중이라면 이 섹션의 방법을 사용하여 Excel 에서 드롭다운 목록을(를) 검색 가능하게 만들 수도 있습니다。

Sheet1 의 A2:A8 범위(왼쪽 이미지)에 있는 데이터를 사용해 Sheet2 의 A2 셀(오른쪽 이미지)에 드롭다운 목록을(를) 이미 생성했다고 가정하고， 다음 단계에 따라 목록을 검색 가능하게 만드세요。

단계 1. 검색 항목을(를) 나열하는 보조 열 만들기

여기서는 원본 데이터과(와) 일치하는 항목을 나열하기 위한 보조 열이 필요합니다。 이 예시에서는 보조 열을D 열에Sheet1에 생성하겠습니다。

1. D 열의 첫 번째 셀()D1)을 선택하고 열 머리글을 입력합니다。 예： “검색 결과“입니다。
2. D2 셀에 다음 수식을 입력하고Enter키를 누릅니다。

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Not Found")

   

단계 2: 드롭다운 목록 재구성하기

1. 드롭다운 목록 셀을 선택합니다(예: Sheet2 의 A2 셀)。 그런 다음데이터>데이터 유효성 검사>데이터 유효성 검사를 선택합니다。
   
2. 데이터 유효성 검사대화 상자에서 다음과 같이 설정합니다。
   1. 설정탭에서소스 상자의Source버튼을 클릭하세요。
      
   2. 데이터 유효성 검사대화 상자가 Sheet1 로 전환됩니다. 단계 1 의 수식이 포함된 셀(예： D2)을 선택하고#기호를 추가한 후닫기버튼을 클릭하세요。
      
   3. 오류 경고탭에서잘못된 데이터 입력 시 오류 경고 표시확인란의 선택을 취소하고 마지막으로확인버튼을 클릭하여 변경 사항을 저장하세요。
      

결과

Sheet2 의 A2 셀에 있는 드롭다운 목록이(가) 이제 검색 가능합니다。 셀에 텍스트를 입력하고 드롭다운 화살표를 클릭해 드롭다운 목록을(를) 확장하면 입력한 텍스트와 일치하도록 목록이 즉시 필터링됩니다。

모든 Excel 버전용으로 드롭다운 목록 검색 가능하게 만들기 쉽게 만들기

상기 방법들의 다양한 제한 사항을 고려하여， 여기에 매우 효과적인 도구를 소개합니다 –Kutools for Excel의드롭다운 목록 검색 가능 및 자동 팝업 기능입니다。 이 기능은 모든 Excel 버전에서 사용 가능하며 간단한 설정만으로 드롭다운 목록에서 원하는 항목을 쉽게 검색할 수 있습니다。

Kutools for Excel 을(를) 다운로드 및 설치한 후Kutools을(를) 선택하세요>드롭다운 목록>드롭다운 목록 검색 가능 및 자동 팝업 기능을(를) 활성화합니다。 표시되는드롭다운 목록 검색 가능하게 만들기대화 상자에서 다음을 수행해야 합니다： dialog box, you need to:

1. 드롭다운 목록 검색 가능하게 만들기로 설정할 드롭다운 목록이 포함된 범위를 선택합니다。
2. 확인을(를) 클릭하여 설정을 완료합니다。
   

결과

제한된 범위에서 드롭다운 목록 셀을 클릭하면 오른쪽에 목록 상자가 나타납니다。 텍스트를 입력하면 목록이 즉시 필터링되며， 항목을 선택하거나 화살표 키를 사용해Enter키를 눌러 셀에 추가할 수 있습니다。

콤보 상자와 VBA 로 드롭다운 목록 검색 가능하게 만들기 만들기(더 복잡함)

특정 드롭다운 목록 유형을 지정하지 않고 단순히 드롭다운 목록 검색 가능하게 만들기을(를) 만들고 싶다면， 이 섹션에서는 VBA 코드와 함께 콤보 상자를 사용하는 대체 방법을 제공합니다。

아래 스크린샷과 같이 A 열에 국가 이름 목록이 있고， 이를 검색 가능한 드롭다운 목록의 원본 데이터으로 사용하려는 경우 다음과 같이 진행하면 됩니다。

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

1. 리본에개발자탭이 표시되지 않으면 다음과 같이개발자탭을 활성화할 수 있습니다。
   1. Excel 2010 이상 버전에서는파일>옵션을 클릭하세요。 그런 다음Excel 옵션대화 상자에서 좌측 창의리본 사용자 지정을 클릭하세요。 리본 사용자 지정 목록 상자에서개발자확인란을 선택한 후확인버튼을 클릭하세요。 스크린샷 참조：
      
   2. Excel 2007 에서는Office버튼 >Excel 옵션을 클릭하세요。Excel 옵션대화 상자에서 좌측 창의일반을 클릭하고리본 리본 메뉴에 개발자 탭 표시확인란을 선택한 후 마지막으로확인버튼을 클릭하세요。
      
2. 개발자탭이 표시되면개발자>삽입>콤보 상자를 클릭합니다。
   
3. 워크시트에 콤보 상자를 그린 후 마우스 오른쪽 버튼으로 클릭하고 바로 가기 메뉴에서속성을(를) 선택합니다。
   
4. 속성대화 상자에서 다음을 수행합니다。
   1. False를AutoWordSelect필드에서 선택하세요。
   2. LinkedCell필드에 셀을 지정하세요. 이 경우 A12 를 입력합니다。
   3. 2-fmMatchEntryNone를MatchEntry필드에서 선택하세요。
   4. DropDownList를ListFillRange필드에 입력하세요。
   5. 속성대화 상자를 닫으세요。 스크린샷 참조：
      
5. 이제개발자>디자인 모드를 클릭하여 디자인 모드를 끕니다。
6. C2 와 같은 빈 셀을 선택하고 아래 수식을 입력한 후Enter키를 누릅니다。 그런 다음 자동 채우기 핸들을 C9 셀까지 끌어 동일한 수식으로 셀을 자동 채웁니다。 스크린샷 참조：

   =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))

   
   참고:

   1. $A$12는 단계 4 에서LinkedCell로 지정한 셀입니다。
   2. 위 단계를 완료한 후 테스트해 보세요: 콤보 상자에 문자 C 를 입력하면 문자 C 를 포함하는 셀을 참조하는 수식 셀이 숫자 1 으로 채워지는 것을 확인할 수 있습니다。
7. D2 셀을 선택하고 아래 수식을 입력한 후Enter키를 누릅니다。 그런 다음 자동 채우기 핸들을 D9 셀까지 끌어 동일한 수식을 적용합니다。

   =IF(C2=1,COUNTIF($C$2:C2,1),"")

   
8. E2 셀을 선택하고 아래 수식을 입력한 후Enter키를 누릅니다。 그런 다음 자동 채우기 핸들을 E9 셀까지 끌어 동일한 수식을 적용합니다。

   =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"")

   
9. 이제 이름 범위를 만들어야 합니다。수식>이름 정의를 클릭합니다。
   
10. 새 이름대화 상자에서DropDownList를이름상자에 입력하고， 아래 수식을참조 위치상자에 입력한 후확인버튼을 클릭합니다。

    =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1)
    

    
11. 이제개발자>디자인 모드를 클릭하여 디자인 모드를 켭니다。 그런 다음 콤보 상자를 두 번 클릭하여Microsoft Visual Basic for Applications창을 엽니다。
12. 아래 VBA 코드를 코드 편집기에 복사하여 붙여넣습니다。
    
    VBA 코드： 드롭다운 목록 검색 가능하게 만들기

    Private Sub ComboBox1_GotFocus()
    	ComboBox1.ListFillRange = "DropDownList"
    	Me.ComboBox1.DropDown
    End Sub

13. Alt+Q키를 눌러Microsoft Visual Basic for Applications창을 닫습니다。

이제 콤보 상자에 문자를 입력하면 퍼지 검색을 수행한 후 관련 값을 목록에 표시합니다。

관련 문서：