Выпадающий список с быстрым поиском

Классический выпадающий список в ячейке листа Excel, сделанный через Данные - Проверка (Data - Validation) - простая и удобная штука, которую ежедневно применяют очень многие пользователи. Однако, у этого списка есть один весьма серьезный недостаток - в нём нет быстрого поиска по первым символам, т.е. фильтрации (отбора) только тех значений, куда введённый фрагмент входит как подстрока. Это серьезно ухудшает удобство пользования даже если в списке всего пара-тройка десятков позиций, а при нескольких сотнях убивает юзабилити напрочь.

Давайте рассмотрим как всё же реализовать подобный трюк. В качестве подопытного кролика возьмём список 250 лучших фильмов по версии IMDb:

Выпаюащий список с поиском

Конечная цель - создать выпадающий список (ячейка G3), в котором можно будет быстро находить нужные фильмы, введя только жанр, год или фрагмент названия, например "гамп".

Шаг 1. Определяем, кто нам нужен

Сначала нам нужно понять, какие из исходных ячеек нужно показывать в списке, т.е. определить содержится ли введённый в выпадающем списке текст (например, жанр "детектив") в названии фильма. Для этого добавим слева от исходных данных еще один столбец с функцией ПОИСК (SEARCH), которая ищет заданную подстроку в тексте и выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:

Ищем вхождения функцией ПОИСК

Теперь завернем нашу формулу в функцию проверки ЕЧИСЛО (ISNUMBER), которая превратит числа в логическую ИСТИНУ (TRUE), а ошибки - в ЛОЖЬ (FALSE):

Превращаем в ИСТИНУ и ЛОЖЬ

Теперь сделаем так, чтобы ЛОЖЬ превратилась в 0, а вместо ИСТИНА в столбце появились последовательно возрастающие индексы-числа 1,2,3... и т.д. Это можно сделать с помощью добавления к нашей же формуле ещё парочки функций:

Создаем индексы

Здесь функция ЕСЛИ (IF) проверяет что мы имеем (ИСТИНУ или ЛОЖЬ), и

  • если была ИСТИНА, то выводит максимальное значение из всех вышестоящих чисел + 1
  • если была ЛОЖЬ, то выводит 0

Шаг 2. Отбираем в отдельный список

Дальше - проще. Теперь банальной функцией ВПР (VLOOKUP) просто выведём все найденные названия (я добавил столбец с порядковыми номерами для удобства):

Извлекаем нужные элементы

После этого можно поиграться, вводя в жёлтую ячейку G2 разные слова и фразы и понаблюдать за тем, как наши формулы отбирают только подходящие фильмы:

Тестируем отбор

Шаг 3. Создаем именованный диапазон

Теперь создадим именованный диапазон, который будет ссылаться на отобранные фильмы. Для этого выбрем на вкладке Формулы команды Диспетчер имен - Создать (Formulas - Name Manager - Create):

Создаем именованный диапазон

Имя диапазона может быть любым (например, Фильмы), а самое главное - это функция СМЕЩ (OFFSET), которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:

=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота; ширина)

Синтаксис СМЕЩ

У нас:

  • В качестве начальной ячейки задаём первую ячейку списка отобранных элементов (E2).
  • Сдвиги вниз и вправо у нас отсутствуют, т.е. равны нулю.
  • Высота диапазона у нас соответствует максимальному значению индекса из столбца А.
  • Ширина диапазона - 1 столбец.

Осталось сделать выпадающий список.

Шаг 4. Создаем выпадающий список

Выделим жёлтую ячейку (G2) и выберем на вкладке Данные команду Проверка данных (Data - Validation). В открывшемся окне выбрем Список (List) в поле Тип данных (Allow), а в качестве источника введем имя нашего созданного диапазона со знаком равно перед ним:

Создаем выпадающий список

Чтобы Excel не ругался при вводе на неточное совпадение наших фраз с исходным списком, на вкладке Сообщение об ошибке (Error Alert) в этом окне нужно выключить флажок Выводить сообщение об ошибке (Show error alert):

Отключаем сообщение об ошибке

Вот и всё. Можно жать на ОК и наслаждаться результатом:

Выпадающий список с поиском

Для пущего удобства при вводе с клавиатуры можно использовать Ctrl+Enter вместо Enter после ввода текста (так активная ячейка не уходит вниз) и сочетание клавиш Alt+стрелка вниз, чтобы развернуть выпадающий список без мыши.

P.S. 

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

Вып.список на динамических массивах

Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новая функция ФИЛЬТР (FILTER) отбирает из исходного диапазона A2:A251 только те фильмы, которые содержат заданную подстроку.

А дальше останется при создании выпадающего списка указать в качестве источника первую ячейку диапазона отобранных фильмов (C2) и добавить к ней знак #, чтобы получить ссылку на весь динамический массив:

Создание вып.списка на динамическом массиве

И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!

Ссылки по теме




06.10.2019 13:17:34
Николай, спасибо за урок, очень полезно, но как сделать если я хочу чтобы несколько столбцов показывались при поиске ?
Например, мне нужно, чтобы при отборе показывался дополнительный столбик с данными PEGI.

http://ipic.su/img/img7/fs/kiss_25kb.1570354655.png
06.10.2019 14:51:02
Боюсь, что никак - стандартный выпадающий список не поддерживает отображение нескольких столбцов. Так что либо приклеивать рейтинг к названию, либо потом после ввода уже подтягивать через ВПР.
07.10.2019 09:24:51
А можно наглядный пример приклеивания рейтинга и подтягивания через ВПР ?
Jun
25.10.2019 17:11:09
Очень легко доделать. Скорректируйте первую строку Столбца отбор на: =СЦЕПИТЬ(ВПР(D2;A:B;2;0);", ";ВПР(D2;A:C;3;0))
Николай, спасибо огромное за отличный прием!!! Можно ли выпадающий список с тем же условием распространить на другие ячейки?
06.10.2019 19:26:43
Присоединяюсь, как поступить если ячеек с выпадающим списком несколько а не одна?
10.10.2019 15:28:12
Насколько я понимаю, для каждой ячейки с проверкой данных потребуется формировать свой диапазон Отбора.
В противном случае в выпадающих списках всех ячеек будут формироваться перечни, отобранные на основании маске данных, введенных в первую ячейку... Более того, после того как в ней будет выбрано конкретное значение обновится и сам перечень фильмов в столбце Отбор. Это приведет к тому, что во всех ячейках, включая первую, выбор будет ограничен одним конкретным фильмом - только что выбранном. А для возврата возможности выбора по маске ее потребуется заново вводить в первую ячейку.
07.10.2019 10:52:26
Отличный вопрос. :)
08.10.2019 17:57:31
в 4 шаге, выделите нужный диапазон ячеек в столбце. в местo G2
08.10.2019 14:28:24
Как всегда - доступно, понятно.
Спасибо!

В вот как найти "максимальное значение среди вышестоящих" в умных таблицах?
В принципе МАКС($A$1:A1) в столбце умной таблицы тоже работает, но на большом перечне очень тормозит :-(
Может есть какой более хитрый, неведомый нам способ: :-)
30.10.2019 13:00:04
Формулами не знаю, а вот в PQ можно реализовать такое и работать будет очень шустро. Если такое решение интересно, то выкладывайте пример на форум и формулируйте задачу.
10.10.2019 15:35:52
Николай,
Благодарю за интересную идею.
Могу ли я уточнить следующее: можно ли спрятать результат вычисления функции ФИЛЬТР() сразу внутрь именованного диапазона (минуя его публикацию во вспомогательных ячейках листа)? Или такой виртуальный перечень не получится использовать в качестве источника данных для выпадающего списка?
К сожалению, пока я не могу проверить работу новых формул лично...  
06.11.2019 09:16:10
Пробовал и то и другое пока готовил материал - не сработало :(
Jun
25.10.2019 17:21:01
Доброго дня! Огромное спасибо за уроки! Очень многому научился у Вас.
Подскажите, есть ли возможность создания динамического выпадающего списка?

Задача такая (пример): есть перечень типов телефонов, которые можем отремонтировать.
Клиент получает эксель файл для заявки. В нем умная таблица, в которой он пишет модель телефона, а справа через ВПР выдается возможность ремонта и стоимость. Проблема в том, что могут написать модель NW-34x, а у нас в перечне NW_34x.
Необходимо, чтобы при наборе NW вниз выпадал сразу список, как, к примеру на сайтах живой поиск по товарам.
Или это не для Excel?
:)

Спасибо!
Наверх