Реклама
Этот сайт использует Яндекс.Метрику для анализа посещаемости. Подробнее
Сортировка и фильтрация

Как создать выпадающий список с поиском в Excel?

Вероятность: 50%
Краткий ответ

В поддерживаемых сборках Excel для Microsoft 365 у списков проверки данных есть автодополнение. Если оно недоступно, можно выделить ячейку для поискового запроса и построить отфильтрованный источник функцией ФИЛЬТР в Microsoft 365 или Excel 2021/2024.

Подробный ответ

Для длинного списка удобно автодополнение или отдельное поле поиска. Возможности зависят от платформы и сборки Excel.

Автодополнение списка в Microsoft 365

Создайте список через Данные → Проверка данных → Список и начните вводить значение. В поддерживаемых сборках список предлагает совпадения. Microsoft выпускала эту возможность для Windows, а затем для других платформ; наличие подписки само по себе не гарантирует одинаковое поведение всех клиентов. Если подсказки не появляются, используйте отдельный поисковый диапазон.

Поиск через ФИЛЬТР в Microsoft 365 и Excel 2021/2024

  1. Разместите текстовые варианты без ошибок в E2:E100. Ячейку A1 оставьте для поискового запроса.
  2. В свободной ячейке G2 вне таблицы Excel введите:
    =ФИЛЬТР(E2:E100;(E2:E100<>"")*ЕЧИСЛО(ПОИСК($A$1;E2:E100));"")
  3. Оставьте область под G2 свободной для результатов.
  4. В ячейке выбора B1 настройте проверку данных «Список», источник — =$G$2#. Если источник находится на другом листе, создайте имя со ссылкой на его разлив и используйте это имя.

ПОИСК не различает регистр. ЕЧИСЛО превращает найденные позиции в ИСТИНА, а ошибки ненайденного текста — в ЛОЖЬ. ЕСНД здесь не подходит: ПОИСК возвращает #ЗНАЧ!, а не #Н/Д. При отсутствии совпадений формула возвращает пустую строку, поэтому список может содержать пустой пункт. Изменение запроса не очищает прежний выбор в B1: проверьте его заново. Символы * и ? в запросе действуют как маски; для буквального поиска используйте ~* и ~?.

Старые версии и ComboBox

В Excel 2016/2019 функции ФИЛЬТР нет. Можно фильтровать справочник обычным автофильтром и выбирать из результата либо разработать отдельную форму VBA. Формула СМЕЩ с ПОИСКПОЗ и СЧЁТЕСЛИ возвращает непрерывный участок и не собирает разрозненные совпадения.

ActiveX ComboBox на листе поддерживается в настольном Excel для Windows, но не на Mac и не в веб-версии. Свойства ListFillRange и LinkedCell связывают список и ячейку, а MatchEntry управляет автодополнением; сами по себе они не реализуют фильтрацию по подстроке. Для неё нужен код. В Microsoft 365 и Office 2024 ActiveX по умолчанию отключён.

Выбор способа

Сначала проверьте встроенное автодополнение. Для поиска через формулу разделяйте ячейку запроса и ячейку выбора, чтобы не создавать циклическую зависимость.

Практическая деталь

Поиск через отдельную ячейку и ФИЛЬТР не является вводом внутри выпадающего списка. Выделите место для результатов и используйте ссылку на разлившийся диапазон как источник списка.

Мы используем cookies для улучшения работы сайта. Продолжая использовать сайт, вы соглашаетесь с политикой конфиденциальности.