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

Классический выпадающий список в ячейке листа 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
Насколько я понимаю, для каждой ячейки с проверкой данных потребуется формировать свой диапазон Отбора.
В противном случае в выпадающих списках всех ячеек будут формироваться перечни, отобранные на основании маске данных, введенных в первую ячейку... Более того, после того как в ней будет выбрано конкретное значение обновится и сам перечень фильмов в столбце Отбор. Это приведет к тому, что во всех ячейках, включая первую, выбор будет ограничен одним конкретным фильмом - только что выбранном. А для возврата возможности выбора по маске ее потребуется заново вводить в первую ячейку.
06.02.2020 17:15:51
Добрый день. Вам удалось найти решение? как распространить этот прием на другие ячейки?
07.10.2019 10:52:26
Отличный вопрос. :)
08.10.2019 17:57:31
в 4 шаге, выделите нужный диапазон ячеек в столбце. в местo G2
А как в шаге 1 не только на $G$3, но и на остальные ячейки направить условие для поиска? У меня в каждой строке постоянно выбор идет т из одного и того же списка.
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?
:)

Спасибо!
Добрый день! Спасибо за урок.

Подскажите, если мне нужно два отбора по одному списку на разных листах EXCEL, тогда следует продублировать все вышеуказанные операции для другой ячейки?
08.01.2020 16:59:52
здравствуйте Николай

Спасибо! очень интересное решение.

Только у меня вопрос, если выпадающие списки используются нескольких строках и каждый раз нужно фильтровать тогда как поступать?
28.10.2020 21:30:04
Николай, большое спасибо за урок.
Подскажите пожалуйста, как можно сделать выпадающий список на 2 условия. Например условие 1 детектив, условие 2 - отец. Чтобы показаны были фильмы в которых выполняется такое условие. И так, чтобы можно было менять эти условия произвольно. Усложняется все тем, что очень большое кол-во строк и есть определенное кол-во информации которая повторяется.
15.11.2020 14:27:40
ДОбрый день, Николай. Замечательный пример, в том числе с использованием динамических диапазонов. Но есть задача, когда надо построчный поиск применять не в одной единственной ячейке, а в разных.
Например, это реестр заявок, где каждая строка - это уникальная заявка, у которой присвоен код статьи бюджета. Вот в каждой из строк нужно построчным поиском из раскрывающегося списка сделать выбор.
18.11.2020 12:11:23
Спасибо Николай, очень полезно. Гугл таблицы во многом отстают от Excel,  а тут обогнали - там это встроено в выпадающие списки сразу.
А в Excel танцы с бубнами, приходится доп ячейку делать, чтобы в ячейке с выпадающим списком выпадало что надо, и для длинного списка использовать эту одну ячейку... эх...и для разных списков использующих в качсестве источника выпадающего списка те же данные городить новый рейтинг..
08.12.2020 23:06:51
Николой Павлов, можно зашитить выпадающий список?
10.01.2021 03:27:29
Выпадающий список с быстрым поиском в нескольких ячейках:
http://planetaexcel.ru/forum/index.php?PAGE_NAME=message&FID=1&TID=136166&MID=1108162
03.12.2021 22:53:54
Да это сколько же отборов надо рисовать... :facepalm: Должен быть другой способ, раз это динамический массив. У меня образно 5000 строк в которой из каждой надо в процессе из базы поиском подтащить... Как то должен исчезать предыдущий отбор... Возможно ли это?...
20.01.2021 08:48:41
Огонь!:)
21.01.2021 06:52:44
Есть минус: прин наличии в ячейке какого-либо значения - весь список для выбора не выпадает.
16.02.2021 18:08:42
Николай, спасибо большое!!! Интересно, а возможно ли, чтобы в ячейке, до момента выбора, отображалась бы надпись, например: "Выберете одно из значений"
19.02.2021 12:48:06
Для этого можно воспользоваться надстройкой Nerv
19.02.2021 09:29:48
Если кто хочет задать выпадающий список к разным ячейкам в столбце, то можно дополнить формулу =IF(ISNUMBER(SEARCH(LOOKUP("zzz";G:G);B2));MAX($A$1:A1)+1;0)
И чтобы формула могла искать по числам, то формат столбца должна быть текстовой.
10.07.2021 11:10:59
Выдаёт нули.
10.06.2021 18:25:58
Формула ЕСЛИ(ЕЧИСЛО(ПОИСК(R3C7;RC[1]));МАКС(R1C1:R[-1]C)+1;0) выдаёт мне нули во всех строках, включая те, где есть совпадения. Т. е. где ЕЧИСЛО(ПОИСК(R3C7;RC[1]))=ИСТИНА.
Я вообще не понимаю, как функция МАКС может выбирать максимальное значение из множества ЛОЖЬ и ИСТИНА. Она ведь должна выбирать максимальное из чисел. Даже в описании к функции МАКС написано: "Логические и текстовые значения игнорируются"....
03.12.2021 21:47:37
Николай, добрый вечер!
Спасибо за статьи. Воспользовался функцией фильтр для создания выпадающего списка с поиском. Вот только не могу сообразить, как сделать так, чтобы в каждой следующей строке таблицы выпадающий список ссылался на тот же диапазон и работал поиск. Смысл такой, что у меня есть база из которой сотрудники в каждой новой строке (на каждый день) поиском должны выбирать контрагента не вбивая его руками. Если его нет в базе, то соответственно должны внести его в базу.
А у меня получается, что при вводе первой строки она выдаёт значение из базы и следующая строка ссылается при поиске на предыдущий отбор, а мне надо, чтобы она захватывала опять всю базу формировала новый отбор и т.д. Я в душе понимаю, что это сделать можно. Но что-то заклинило и всё))) Заранее благодарен за помощь. Очень нужно.
По идее по логике при входе в выпадающий список на следующей строке он должен формировать новый отбор, как бы стирая старый, но при этом сохранять значение, которое было выбрано в выпадающем списке ранее строкой выше.. А вот как это технически сделать я не знаю((
03.12.2021 22:17:42
Если это из разряда фантастики буду искать другие варианты)) Только скажите плиз.
10.12.2021 15:27:01
Люди добрые ну хоть намекните как двигаться по данному вопросу... Сам принцип.
20.01.2022 08:10:02
Для привязки списка к разным ячейкам можно воспользоваться трюком использования летучей CELL. тут не детально но описывал
Выпадающий список с поиском без VBA (planetaexcel.ru)
23.01.2022 00:46:45
Николай, спасибо за видео. Но есть вопрос. В вашем варианте таблица с фильмами статичная. а как можно провести схожие манипуляции, если таблица с данными выгружается на лист из PQ. и выгружаемая таблица после обновлений может иметь разное количество строк. Соответственно, формула, возвращающая количество встречающихся значений должна двигаться в зависимости от количества строк в таблице. Возможно ли решить данную задачу ? если да, то каким образом? (желательно не прибегая к VBA  и макросам). Благодарю.
05.08.2022 23:49:04
Добрый день. Полезный урок. Я уже давно пользуюсь данным способом с вашей помощью. Но у меня возникла необходимость делать отбор не в столбец, а в строку. Можно-ли это как-то организовать?
Дело в том, что я создал таблицу, в каждой строке которой есть выпадающей список, и чтобы он корректно работал, отбор я делаю в той же строке, что и выпадающий список, естественно при таком способе отбор через поиск работает только, когда поиск выдаёт только один результат, в противном случае (когда результат 2 или более значений) второе и последующие значения упираются в ячейки, в которых уже есть данные другого отбора и поиск не работает.
17.02.2023 11:25:06
Интересный урок, как раз под мою задачу с большими списками.
Но вот не могу понять логики последнего шага с "помещением" поименованного списка в ячейку поиска. Ведь при этом выбор становится возможным только из значений списка и при вводе произвольного текста для поиска выдаёт ошибку.
Именно это у меня и происходит. Или я чего не понимаю?
31.07.2023 10:36:52
Николай, подскажите идею, как сделать следующее. В колонке нужно вводить короткие названия контрагентов, отдельно есть умная таблица состоящая из двух колонок короткого наименования и полного. Хотелось бы чтобы по длинному названию вводилось сокращенное. На сколько легко это реализовать выпадающим списком?
18.12.2023 14:56:16
Николай, Добрый день!
Спасибо за Ваш труд, очень информативно.
Но, к сожалению, я ищу другую нужную мне опцию, которой я так и не нашёл на просторах:

Как сделать, чтобы при одном только наведении курсора на ячейку, сразу же всплывал список выбора и оставалось только кликнуть в нужное значение одним кликом?
Коллега утверждает, что видел такое недавно.

Спасибо Вам, в любом случае!
Наверх