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

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

Видео: Создание раскрывающегося списка с возможностью поиска

Раскрывающийся список с возможностью поиска в Excel 365

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

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

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

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

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

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

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

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

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

   

Шаг 2. Настройте раскрывающийся список заново.

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

Результат

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

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

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

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

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

Результат

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

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

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

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

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

1. Если же линия индикатора Застройщик вкладка не отображается на ленте, вы можете включить Застройщик вкладку следующим образом.
   1. В Excel 2010 или более поздних версиях нажмите кнопку Файл > Опции. И в Параметры Excel диалоговое окно, нажмите Настроить ленту на левой панели. Перейдите к списку «Настроить ленту», установите флажок Застройщик поле, а затем щелкните OK кнопка. Смотрите скриншот:
      
   2. В Excel 2007 щелкните в Москве кнопка> Параметры Excel. В Параметры Excel диалоговое окно, нажмите Популярное на левой панели проверьте Показать вкладку разработчика на ленте поле и, наконец, щелкните OK .
      
2. После показа Застройщик вкладку нажмите Застройщик > Вставить > Поле со списком.
   
3. Нарисуйте поле со списком на листе, щелкните его правой кнопкой мыши и выберите Объекты из контекстного меню.
   
4. В Объекты диалоговое окно, вам необходимо:
   1. Выберите Ложь в Автовыбор слов поле;
   2. Укажите ячейку в Связанная ячейка поле. В этом случае мы вводим A12;
   3. Выберите 2-fmMatchEntryNone в МатчВвод поле;
   4. Тип Раскрывающийся список в СписокЗаполнитьДиапазон поле;
   5. Закрой Объекты диалоговое окно. Смотрите скриншот:
      
5. Теперь выключите режим разработки, нажав Застройщик > Режим проектирования.
6. Выберите пустую ячейку, например C2, введите формулу ниже и нажмите Enter. Они перетаскивают его дескриптор автозаполнения в ячейку C9, чтобы автоматически заполнить ячейки одной и той же формулой. Смотрите скриншот:

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

   
   Заметки:

   1. 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. В Новое имя диалоговое окно, введите Раскрывающийся список в ФИО поле, введите приведенную ниже формулу в поле Относится к поле, а затем щелкните 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 для приложений окно.

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

Статьи по теме: