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

Привет, дорогой читатель! 👋

Помните, как в прошлый раз мы превращали Excel в строгого контролера с помощью проверки данных? Сегодня пойдем еще дальше и замахнемся на святое — на тот самый момент, когда вы тыкаете в выпадающий список из 500 позиций и начинаете безнадежно скроллить в поисках нужной. Знакомая боль? 😫

Обычные выпадающие списки в Excel не умеют искать по мере ввода. Но это не значит, что смириться и страдать — единственный выход. Да, макросы (VBA) могли бы решить проблему, но они пугают новичков и часто запрещены корпоративной политикой безопасности.

Хорошая новость: сделать поиск с автодополнением без VBA можно! И сегодня я покажу два рабочих способа. Поехали! 🚀

🧠 Как это работает (общая идея)

Стандартная проверка данных в качестве источника списка принимает только фиксированный диапазон или формулу. Мы пойдем другим путем:

1. Создадим вспомогательную формулу, которая будет динамически фильтровать исходный список по тому, что вы вводите в ячейку.
2. В качестве источника выпадающего списка укажем результат этой формулы.
3. Отключим назойливое сообщение об ошибке — ведь мы разрешаем вводить то, чего пока нет в списке (поисковый запрос).

В итоге вы печатаете буквы, а список на лету подстраивается, показывая только подходящие варианты. Магия? Нет, просто немного формул! ✨

Способ 1️⃣: Для счастливых обладателей Excel 365 или 2021 (Самый простой)

Если у вас современная версия Excel, считайте, что вам повезло. Функции ФИЛЬТР и ПОИСК сделают всю грязную работу за вас.

📝 Пошаговая инструкция

1. Исходные данные: предположим, ваш список значений (например, список товаров) находится в столбце A: A2:A100.
2. Ячейка для поиска: выберите ячейку, где будет ваш "умный" выпадающий список. Пусть это будет B2.
3. Главная формула (вспомогательный столбец): в любой свободной ячейке, скажем C2, пишем формулу:

=ФИЛЬТР(A2:A100; ЕЧИСЛО(ПОИСК(B2; A2:A100)); "Ничего не найдено")

1. Разбор полетов:
   ПОИСК(B2; A2:A100) — ищет текст из B2 внутри каждого значения в столбце A.
   ЕЧИСЛО(...) — превращает результаты поиска в ИСТИНА/ЛОЖЬ (нашел — ИСТИНА, не нашел — ЛОЖЬ).
   ФИЛЬТР(...) — отбирает только те строки, где ИСТИНА.
   Последний аргумент — текст, который появится, если ничего не найдено.
2. Настройка проверки данных:
   Выделите ячейку B2.
   Вкладка «Данные» → «Проверка данных».
   Тип данных: «Список».
   Источник: =$C$2# (знак решетки # — указывает на "вытекший" диапазон динамического массива, который создала функция ФИЛЬТР).
   Перейдите на вкладку «Сообщение об ошибке» и снимите галочку «Выводить сообщение об ошибке». Это важно! Иначе Excel будет ругаться каждый раз, когда вы вводите поисковый запрос, которого еще нет в списке.
3. Как это работает: Начинайте вводить текст в ячейку B2 — например, «ябл». Формула в C2 мгновенно отфильтрует список, оставив только «Яблоки», «Яблочный сок» и т.д. Теперь нажмите на стрелку выпадающего списка в B2 — он покажет только эти отфильтрованные варианты! Выбирайте нужный.

✨ Плюсы: Быстро, элегантно, без лишних телодвижений. 😥 Минусы: Работает только в Excel 365 и 2021, где есть динамические массивы и функция ФИЛЬТР.

Способ 2️⃣: Классика жанра для всех версий Excel (через СМЕЩ)

У вас Excel 2016, 2019 или даже старше? Не беда. На помощь придет старая добрая связка функций СМЕЩ, ПОИСКПОЗ и СЧЁТЕСЛИ. Этот метод чуть сложнее, зато работает везде.

📝 Пошаговая инструкция

Исходные данные — список в столбце A: A2:A20. Ячейка поиска — B2.

1. Создаем вспомогательный столбец с фильтрацией: например, столбец C. В ячейку C2 вводим формулу (это формула массива, в старых версиях Excel ее нужно вводить через Ctrl+Shift+Enter):

=ЕСЛИОШИБКА(ИНДЕКС(A$2:A$100; НАИМЕНЬШИЙ(ЕСЛИ(ЕЧИСЛО(ПОИСК(B$2; A$2:A$100)); СТРОКА(A$2:A$100)-СТРОКА(A$2)+1); СТРОКА(C1))); "")

в строке формул формула ОБЯЗАТЕЛЬНО должна быть выделена фигурными скобками после нажатия комбинации клавиш Ctrl+Shift+Enter

• Разберем по косточкам чуть позже. Сейчас важно понять суть: формула "прочесывает" исходный список и вытаскивает в столбец C только те позиции, которые содержат текст из ячейки B2. Если ничего не найдено — ячейка остается пустой.

Важно: Протяните эту формулу вниз по столбцу C на столько строк, сколько максимально может быть результатов (например, до C20).

Пример работы формулы

2. Создаем динамический диапазон для списка: Чтобы выпадающий список не показывал кучу пустых строк внизу, нам нужно, чтобы он "обрезался" по факту. Для этого:
Переходим на вкладку «Формулы» → «Диспетчер имен» → «Создать».
Имя: ДинамическийСписок (можно назвать как угодно, но без пробелов).
Область: «Книга» или «Лист».
Формула: =СМЕЩ(Лист1!$C$2;0;0;СЧЁТЕСЛИ(Лист1!$C:$C;"?*");1)Как это работает:
СМЕЩ начинает отсчет от ячейки C2.
СЧЁТЕСЛИ(Лист1!$C:$C;"?*") считает количество НЕПУСТЫХ ячеек в столбце C. Именно столько строк и будет в нашем динамическом диапазоне.

3. Настройка проверки данных:
Выделите ячейку B2.
«Данные» → «Проверка данных».
Тип данных: «Список».
Источник: =ДинамическийСписок.
Не забудьте на вкладке «Сообщение об ошибке» снять галочку «Выводить сообщение об ошибке».

4. Как это работает: Вы вводите в B2 текст, нажимаете F9 или Enter (принудительный пересчет формул, это костыль, но без него никак), столбец C обновляется, показывая только подходящие значения, а выпадающий список в B2 дает их выбрать.

Пример работы. В списке представлены позиции с буквой "о"

✨ Плюсы: Работает в любой версии Excel. 😥 Минусы: Сложная формула, требует вспомогательного столбца, нужно иногда вручную нажимать F9 или Enter для обновления списка.

Способ 3️⃣: Надстройки в помощь (Самый простой, если можно ставить программы)

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

• Search deList: Бесплатная надстройка. После установки в любой ячейке с проверкой данных достаточно нажать ALT + → (или ALT + RIGHT), чтобы открылось удобное окно поиска. Вводите часть слова — и список мгновенно фильтруется.
• MulTEx: Мощный комбайн для продвинутых пользователей. В числе прочего умеет показывать рядом с ячейкой со списком окошко с поиском и фильтрацией элементов.

✨ Плюсы: Не нужно ничего настраивать, работает "из коробки", очень удобно. 😥 Минусы: Требует установки, не везде разрешено.

💎 Итог: какой способ выбрать?

Да, стандартная проверка данных не умеет искать по мере ввода. Но, как видите, это ограничение легко обходится — формулами или небольшими бесплатными инструментами. И все это без единой строчки VBA! 💪

Попробуйте любой из способов на своих данных — и вы больше никогда не вернетесь к бесконечному скроллингу длинных списков.

🔥 Ставьте лайк, если хотите еще больше неочевидных лайфхаков для Excel! А какой способ выбрали вы? Или, может, знаете еще один метод? Делитесь в комментариях — будет жарко! 👇

Не забывайте подписываться на канал, чтобы не пропустить новые фишки. Успешной работы с данными! 🚀

⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇⬇

Материалы по Эксель. Содержание данного канала:

https://dzen.ru/a/ZhpQXTxmQDShWlXf

⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆⬆

На сегодня все!

Спасибо за внимание!