Полное руководство по поисковому выпадающему списку в Excel

Создание выпадающих списков в Excel упрощает ввод данных и минимизирует ошибки. Однако при работе с большими наборами данных прокрутка длинных списков становится утомительной. Разве не проще было бы просто начать вводить текст и быстро находить нужный элемент? «Поисковый выпадающий список» предлагает такую возможность. В этом руководстве мы расскажем о четырех методах настройки такого списка в Excel.

• Поисковый выпадающий список в Excel 365
• Создание поискового выпадающего списка (для Excel 2019 и выше)
• Простое создание поискового выпадающего списка (для всех версий Excel)
• Создание поискового выпадающего списка с помощью поля со списком и VBA (более сложный метод)

Видео: Создание поискового выпадающего списка

Поисковый выпадающий список в Excel 365

Excel 365 представил долгожданную функцию для выпадающих списков проверки данных: возможность поиска внутри списка. Благодаря этой функции пользователи могут быстро находить и выбирать элементы более эффективно. После обычного создания выпадающего списка просто щелкните ячейку с выпадающим списком и начните вводить текст. Список мгновенно отфильтруется, чтобы соответствовать введенному тексту.

В данном случае я ввожу San в ячейке, и выпадающий список фильтрует города, начинающиеся с поискового запроса San, такие как Сан-Франциско и Сан-Диего. Затем вы можете выбрать результат с помощью мыши или использовать клавиши со стрелками и нажать Enter.

Создание поискового выпадающего списка (для Excel 2019 и выше)

Если вы используете Excel 2019 или более поздние версии, метод из этого раздела также можно использовать для создания поискового выпадающего списка в Excel.

Предположим, что вы уже создали выпадающий список в ячейке A2 листа Sheet2 (изображение справа), используя данные из диапазона A2:A8 листа Sheet1 (изображение слева). Следуйте этим шагам, чтобы сделать список поисковым.

Шаг 1. Создайте вспомогательный столбец, который будет перечислять элементы поиска

Здесь нам нужен вспомогательный столбец для перечисления элементов, соответствующих исходным данным. В данном случае я создам вспомогательный столбец в столбце D листа Sheet1.

1. Выберите первую ячейку D1 в столбце D и введите заголовок столбца, например «Результаты поиска» в данном случае.
2. Введите следующую формулу в ячейку D2 и нажмите Enter.

   =FILTER(A2:A8,ISNUMBER(SEARCH(Sheet2!A2,A2:A8)),"Не найдено")

   

Шаг 2: Перенастройте выпадающий список

1. Выберите ячейку с выпадающим списком (в данном случае я выбираю ячейку A2 листа Sheet2), затем перейдите к Данные > Проверка данных > Проверка данных.
   
2. В открывшемся окне Проверка данных диалогового окна необходимо выполнить следующие настройки.
   1. На вкладке Параметры щелкните кнопку в поле Источник .
      
   2. Диалоговое окно Проверка данных перенаправит вас на Sheet1, выберите ячейку (например, D2) с формулой из Шага 1, добавьте символ # и нажмите кнопку Закрыть .
      
   3. Перейдите на вкладку Предупреждение об ошибке, снимите флажок Показывать предупреждение об ошибке после ввода недопустимых данных и, наконец, нажмите кнопку ОК, чтобы сохранить изменения.
      

Результат

Теперь выпадающий список в ячейке A2 листа Sheet2 стал поисковым. Введите текст в ячейку, нажмите стрелку раскрывающегося списка, чтобы развернуть его, и вы увидите, как список мгновенно фильтруется в соответствии с введенным текстом.

Простое создание поискового выпадающего списка (для всех версий Excel)

Учитывая различные ограничения вышеупомянутых методов, вот очень эффективный инструмент для вас — функция Kutools для Excel «Сделать выпадающий список доступным для поиска, Автоподсказка». Эта функция доступна во всех версиях Excel и позволяет легко найти нужный элемент в выпадающем списке с простой настройкой.

После загрузки и установки Kutools для Excel, выберите Kutools > Выпадающий список > Сделать выпадающий список доступным для поиска, Автоподсказка, чтобы включить эту функцию. В диалоговом окне Сделать выпадающий список доступным для поиска вам нужно:

1. Выберите диапазон, содержащий выпадающие списки, которые необходимо сделать доступными для поиска.
2. Нажмите ОК для завершения настроек.
   

Результат

Когда вы щелкаете ячейку с выпадающим списком в указанном диапазоне, справа появляется окно списка. Введите текст, чтобы мгновенно отфильтровать список, затем выберите элемент или используйте клавиши со стрелками и нажмите Enter, чтобы добавить его в ячейку.

Создание поискового выпадающего списка с помощью поля со списком и VBA (более сложный метод)

Если вы просто хотите создать поисковый выпадающий список без указания конкретного типа выпадающего списка, этот раздел предоставляет альтернативный подход: использование поля со списком с кодом VBA для выполнения задачи.

Предположим, у вас есть список названий стран в столбце A, как показано на скриншоте ниже, и теперь вы хотите использовать их как источник данных для поисковых выпадающих списков. Вот как это можно сделать.

Вам нужно вставить поле со списком вместо выпадающего списка проверки данных на вашем листе.

1. Если вкладка Разработчик не отображается на ленте, вы можете включить вкладку Разработчик следующим образом.
   1. В Excel 2010 или более поздних версиях нажмите Файл > Параметры. В диалоговом окне Параметры Excel нажмите Настройка ленты в левой панели. Перейдите к списку Настройка ленты, отметьте Разработчик и нажмите кнопку ОК. Смотрите скриншот:
      
   2. В Excel 2007 нажмите кнопку Office > Параметры Excel. В диалоговом окне Параметры Excel нажмите Общие в левой панели, отметьте Показать вкладку Разработчик на ленте и, наконец, нажмите кнопку ОК.
      
2. После отображения вкладки Разработчик нажмите Разработчик > Вставка > Поле со списком.
   
3. Нарисуйте поле со списком на листе, щелкните его правой кнопкой мыши и выберите Свойства из контекстного меню.
   
4. В открывшемся окне Свойства диалогового окна вам нужно:
   1. Выберите Нет в поле АвтоВыборСлова;
   2. Укажите ячейку в поле ПривязаннаяЯчейка. В данном случае мы вводим 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;
   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.

Теперь, когда символ вводится в поле со списком, он выполняет нечеткий поиск и затем выводит соответствующие значения в списке.

Связанные статьи: