Подробный ответ
Для длинного списка удобно автодополнение или отдельное поле поиска. Возможности зависят от платформы и сборки Excel.
Автодополнение списка в Microsoft 365
Создайте список через Данные → Проверка данных → Список и начните вводить значение. В поддерживаемых сборках список предлагает совпадения. Microsoft выпускала эту возможность для Windows, а затем для других платформ; наличие подписки само по себе не гарантирует одинаковое поведение всех клиентов. Если подсказки не появляются, используйте отдельный поисковый диапазон.
Поиск через ФИЛЬТР в Microsoft 365 и Excel 2021/2024
- Разместите текстовые варианты без ошибок в E2:E100. Ячейку A1 оставьте для поискового запроса.
- В свободной ячейке G2 вне таблицы Excel введите:
=ФИЛЬТР(E2:E100;(E2:E100<>"")*ЕЧИСЛО(ПОИСК($A$1;E2:E100));"") - Оставьте область под G2 свободной для результатов.
- В ячейке выбора B1 настройте проверку данных «Список», источник —
=$G$2#. Если источник находится на другом листе, создайте имя со ссылкой на его разлив и используйте это имя.
ПОИСК не различает регистр. ЕЧИСЛО превращает найденные позиции в ИСТИНА, а ошибки ненайденного текста — в ЛОЖЬ. ЕСНД здесь не подходит: ПОИСК возвращает #ЗНАЧ!, а не #Н/Д. При отсутствии совпадений формула возвращает пустую строку, поэтому список может содержать пустой пункт. Изменение запроса не очищает прежний выбор в B1: проверьте его заново. Символы * и ? в запросе действуют как маски; для буквального поиска используйте ~* и ~?.
Старые версии и ComboBox
В Excel 2016/2019 функции ФИЛЬТР нет. Можно фильтровать справочник обычным автофильтром и выбирать из результата либо разработать отдельную форму VBA. Формула СМЕЩ с ПОИСКПОЗ и СЧЁТЕСЛИ возвращает непрерывный участок и не собирает разрозненные совпадения.
ActiveX ComboBox на листе поддерживается в настольном Excel для Windows, но не на Mac и не в веб-версии. Свойства ListFillRange и LinkedCell связывают список и ячейку, а MatchEntry управляет автодополнением; сами по себе они не реализуют фильтрацию по подстроке. Для неё нужен код. В Microsoft 365 и Office 2024 ActiveX по умолчанию отключён.
Выбор способа
Сначала проверьте встроенное автодополнение. Для поиска через формулу разделяйте ячейку запроса и ячейку выбора, чтобы не создавать циклическую зависимость.
Практическая деталь
Поиск через отдельную ячейку и ФИЛЬТР не является вводом внутри выпадающего списка. Выделите место для результатов и используйте ссылку на разлившийся диапазон как источник списка.