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

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

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

Видео: Создание Сделать выпадающий список доступным для поиска

Сделать выпадающий список доступным для поиска в Excel 365

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

В данном случае я ввожу San в ячейку, и раскрывающийся список отображает только города, начинающиеся с этого запроса — например, San Francisco и San Diego. Затем вы можете выбрать нужный вариант с помощью мыши или использовать клавиши со стрелками и нажать 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)),"Not Found")

   

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

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

Результат

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

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

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

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

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

Результат

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

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

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

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

Вам нужно вставить на лист поле со списком (Combo Box), а не раскрывающийся список проверки данных.

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— это ячейка, которую Вы указали как LinkedCellна шаге 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 для приложений.
12. Скопируйте и вставьте приведённый ниже код VBA в редактор кода.
    
    Код VBA: сделать выпадающий список доступным для поиска

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

13. Нажмите клавиши Alt + Q, чтобы закрыть окно Microsoft Visual Basic для приложений.

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

См. также: