Excel에서 검색 가능한 드롭다운 목록에 대한 궁극적인 가이드

Excel에서 드롭다운 목록을 생성하면 데이터 입력이 간소화되고 오류가 최소화됩니다. 하지만 더 큰 데이터 세트에서는 긴 목록을 스크롤하는 것이 번거로울 수 있습니다. 입력하면서 원하는 항목을 빠르게 찾을 수 있다면 더 쉬울까요? '드롭다운 목록 검색 가능하게 만들기' 기능은 이러한 편의를 제공합니다. 이 가이드에서는 Excel에서 이러한 목록을 설정하는 네 가지 방법을 안내합니다.

• Excel 365에서 검색 가능한 드롭다운 목록
• 검색 가능한 드롭다운 목록 생성 (Excel 2019 및 이후 버전용)
• 모든 Excel 버전에서 쉽게 검색 가능한 드롭다운 목록 생성
• 콤보 박스와 VBA를 사용하여 검색 가능한 드롭다운 목록 생성 (더 복잡함)

비디오: 검색 가능한 드롭다운 목록 생성

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

Excel 365는 데이터 유효성 검사 드롭다운 목록에 기대已久的 기능인 목록 내 검색 기능을 도입했습니다. 검색 기능을 통해 사용자는 보다 효율적으로 항목을 찾고 선택할 수 있습니다. 일반적인 방법으로 드롭다운 목록을 삽입한 후, 드롭다운 목록이 있는 셀을 클릭하고 입력하기만 하면 목록이 즉시 입력된 텍스트와 일치하도록 필터링됩니다.

이 경우, 셀에 San이라고 입력하면 드롭다운 목록은 San Francisco 및 San Diego와 같이 검색어 San으로 시작하는 도시들을 필터링합니다. 그런 다음 마우스로 결과를 선택하거나 화살표 키를 사용하여 Enter 키를 누릅니다.

검색 가능한 드롭다운 목록 생성 (Excel 2019 및 이후 버전용)

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

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

1단계. 검색 항목을 나열하는 보조 열 생성

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

1. D열의 첫 번째 셀 D1을 선택하고 열 머리글을 입력합니다. 예를 들어, 이 경우 "검색 결과"를 입력합니다.
2. 다음 수식을 D2 셀에 입력하고 Enter 키를 누릅니다..

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

   

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

1. 드롭다운 목록 셀을 선택합니다(이 경우 Sheet2의 A2 셀을 선택). 그런 다음 데이터 > 데이터 유효성 검사 > 데이터 유효성 검사를 선택합니다.
   
2. 다음 창에서 데이터 유효성 검사 창에서 다음과 같이 구성해야 합니다.
   1. 아래의 설정 탭에서 버튼을 클릭합니다. 소스 상자에서.
      
   2. 데이터 유효성 검사 대화 상자는 Sheet1로 리디렉션되며, 1단계의 수식이 포함된 셀(예: D2)을 선택하고 # 기호를 추가한 다음 닫기 버튼을 클릭합니다.
      
   3. 오류 경고 탭으로 이동하여 잘못된 데이터를 입력한 후 오류 경고를 표시 옵션의 체크박스를 해제하고 마지막으로 확인 버튼을 클릭하여 변경 사항을 저장합니다.
      

결과

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

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

위 방법들의 다양한 제한 사항을 감안할 때, 여기 매우 효과적인 도구를 소개합니다 - Kutools for Excel의 Make Drop-down List Searchable, Auto-popup 기능입니다. 이 기능은 모든 Excel 버전에서 사용할 수 있으며 간단한 설정으로 드롭다운 목록에서 원하는 항목을 쉽게 검색할 수 있습니다.

Kutools for Excel을 다운로드하고 설치한 후, Kutools > 드롭다운 목록 > Make Drop-down List Searchable, Auto-popup를 선택하여 이 기능을 활성화합니다. Make the Drop-down List Searchable 대화 상자에서 다음을 수행해야 합니다.

1. 검색 가능한 드롭다운 목록으로 설정해야 하는 드롭다운 목록이 포함된 범위를 선택합니다.
2. 클릭 확인 설정을 완료합니다.
   

결과

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

콤보 박스와 VBA를 사용하여 검색 가능한 드롭다운 목록 생성 (더 복잡함)

특정 드롭다운 목록 유형을 지정하지 않고도 검색 가능한 드롭다운 목록을 생성하려는 경우 이 섹션에서는 대안적인 접근 방식을 제공합니다: 콤보 박스와 VBA 코드를 사용하여 작업을 수행합니다.

아래 스크린샷과 같이 A열에 국가 이름 목록이 있다고 가정하고, 이제 이를 검색 가능한 드롭다운 목록의 소스 데이터로 사용하려고 합니다. 다음 단계에 따라 이를 수행할 수 있습니다.

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

1. 만약 개발 도구 탭이 리본에 표시되지 않으면 다음과 같이 개발 도구 탭을 활성화할 수 있습니다.
   1. Excel 2010 이상 버전에서는 파일 > 옵션을 클릭합니다. Excel 옵션 대화 상자에서 왼쪽 창에서 리본 사용자 정의를 클릭합니다. 리본 사용자 정의 목록 상자로 이동하여 개발 도구 상자를 선택하고 확인 버튼을 클릭합니다. 스크린샷 참조:
      
   2. Excel 2007에서는 Office 버튼 > Excel 옵션을 클릭합니다. Excel 옵션 대화 상자에서 왼쪽 창에서 인기 있는 기능을 클릭하고 리본에 개발 도구 탭 표시 상자를 선택한 다음 확인 버튼을 클릭합니다.
      
2. 개발 도구 탭이 표시되면 개발 도구 > 삽입 > 콤보 박스를 클릭합니다.
   
3. 워크시트에 콤보 박스를 그리고, 마우스 오른쪽 버튼을 클릭한 다음 마우스 오른쪽 클릭 메뉴에서 속성을 선택합니다.
   
4. 다음 창에서 속성 창에서 다음을 수행해야 합니다.
   1. AutoWordSelect 필드에서 False를 선택합니다;
   2. LinkedCell 필드에 셀을 지정합니다. 이 경우 A12를 입력합니다;
   3. MatchEntry 필드에서 2-fmMatchEntryNone을 선택합니다;
   4. ListFillRange 필드에 DropDownList을 입력합니다;
   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 창을 닫습니다.

이제부터 콤보 박스에 문자를 입력하면 퍼지 검색을 수행하고 관련 값을 목록에 표시합니다.

관련 기사: