Создание стандартного выпадающего списка в Excel — задача простая, но если в вашем перечне сотни элементов, искать нужный вручную становится неудобно. Умный выпадающий список с автопоиском (автозаполнением) позволяет вводить первые буквы слова и сразу видеть отфильтрованные варианты. В этой инструкции мы разберем самый надежный способ реализации такого списка с помощью динамических массивов.
Видеоинструкция
Шаг 1: Подготовка исходных данных
Для начала создайте список элементов, которые будут отображаться в меню. Например, введите список товаров или имен в столбце A (начиная с A2). Преобразуйте этот диапазон в «умную таблицу», выделив его и нажав комбинацию клавиш Ctrl + T. Это позволит списку автоматически расширяться при добавлении новых строк.
Шаг 2: Создание динамического фильтра
Вспомогательный столбец поможет нам фильтровать данные «на лету». В ячейку C2 мы будем вводить поисковый запрос, а в столбце D выведем отфильтрованный список. Введите в ячейку D2 следующую формулу:
=ФИЛЬТР(A2:A100; ЕЧИСЛО(ПОИСК(C2; A2:A100)))Эта формула ищет текст из ячейки C2 в диапазоне данных и возвращает только подходящие совпадения.
Шаг 3: Настройка проверки данных
Теперь привяжем наш динамический список к целевой ячейке ввода (C2):
- Выделите ячейку
C2. - Перейдите на вкладку Данные и выберите пункт Проверка данных. Если у вас возникли проблемы с интерфейсом, например, Пропала вкладка Данные в Excel: как вернуть её, сначала восстановите панель инструментов.
- В поле «Тип данных» выберите Список.
- В поле «Источник» введите формулу со знаком решетки на конце, чтобы сослаться на динамический диапазон:
=$D$2# - Перейдите на вкладку «Сообщение об ошибке» и снимите галочку с пункта «Показывать ошибку», чтобы Excel разрешал вводить недописанные слова для поиска. Нажмите ОК.
Частые ошибки / Устранение неполадок
Ошибка #ПОДМЕСЬ! (#SPILL!) в формуле фильтра: убедитесь, что под ячейкой D2 (где находится формула фильтрации) достаточно пустых строк. Если там есть другие данные, Excel не сможет развернуть динамический массив.
Список не реагирует на ввод: проверьте, отключили ли вы предупреждение об ошибке в настройках проверки данных. Если этого не сделать, программа заблокирует ввод любого текста, которого еще нет в списке.
При работе со сложными отчетами пользователи часто сталкиваются и с другими трудностями. Например, полезно знать, Почему не считает накопительный итог в сводной таблице, чтобы избежать искажения аналитики. А если в процессе настройки таблица аварийно закрылась, не паникуйте — изучите руководство о том, что делать, если Пропали последние файлы в Excel: как вернуть список недавних документов.
Дополнительно: Альтернативный способ для Excel 365
В последних версиях Microsoft 365 функция автопоиска встроена по умолчанию. Вам достаточно создать обычный выпадающий список через «Проверку данных», и при вводе текста в ячейку Excel сам начнет фильтровать элементы. Никаких дополнительных формул вводить не требуется. Чтобы применить изменения, достаточно нажать Enter.
Часто задаваемые вопросы
Работает ли автопоиск в старых версиях Excel?
В старых версиях (до Excel 2021) автопоиск можно реализовать только через формулы динамических массивов или макросы VBA.
Зачем нужен символ решетки (#) в источнике данных?
Символ # указывает Excel, что нужно брать весь динамический диапазон, который автоматически меняет свой размер в зависимости от результатов фильтрации.
