Выпадающий список с быстрым поиском
Классический выпадающий список в ячейке листа 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) и добавить к ней знак #, чтобы получить ссылку на весь динамический массив:
И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!
Ссылки по теме
- Что такое динамические массивы в Excel
- Разбор трех основных функций динамических массивов: СОРТ, ФИЛЬТР и УНИК
- 4 способа создать выпадающий список на листе Excel
Например, мне нужно, чтобы при отборе показывался дополнительный столбик с данными PEGI.
В противном случае в выпадающих списках всех ячеек будут формироваться перечни, отобранные на основании маске данных, введенных в первую ячейку... Более того, после того как в ней будет выбрано конкретное значение обновится и сам перечень фильмов в столбце Отбор. Это приведет к тому, что во всех ячейках, включая первую, выбор будет ограничен одним конкретным фильмом - только что выбранном. А для возврата возможности выбора по маске ее потребуется заново вводить в первую ячейку.
Спасибо!
В вот как найти "максимальное значение среди вышестоящих" в умных таблицах?
В принципе МАКС($A$1:A1) в столбце умной таблицы тоже работает, но на большом перечне очень тормозит
Может есть какой более хитрый, неведомый нам способ:
Благодарю за интересную идею.
Могу ли я уточнить следующее: можно ли спрятать результат вычисления функции ФИЛЬТР() сразу внутрь именованного диапазона (минуя его публикацию во вспомогательных ячейках листа)? Или такой виртуальный перечень не получится использовать в качестве источника данных для выпадающего списка?
К сожалению, пока я не могу проверить работу новых формул лично...
Подскажите, есть ли возможность создания динамического выпадающего списка?
Задача такая (пример): есть перечень типов телефонов, которые можем отремонтировать.
Клиент получает эксель файл для заявки. В нем умная таблица, в которой он пишет модель телефона, а справа через ВПР выдается возможность ремонта и стоимость. Проблема в том, что могут написать модель NW-34x, а у нас в перечне NW_34x.
Необходимо, чтобы при наборе NW вниз выпадал сразу список, как, к примеру на сайтах живой поиск по товарам.
Или это не для Excel?
Спасибо!
Подскажите, если мне нужно два отбора по одному списку на разных листах EXCEL, тогда следует продублировать все вышеуказанные операции для другой ячейки?
Спасибо! очень интересное решение.
Только у меня вопрос, если выпадающие списки используются нескольких строках и каждый раз нужно фильтровать тогда как поступать?
Подскажите пожалуйста, как можно сделать выпадающий список на 2 условия. Например условие 1 детектив, условие 2 - отец. Чтобы показаны были фильмы в которых выполняется такое условие. И так, чтобы можно было менять эти условия произвольно. Усложняется все тем, что очень большое кол-во строк и есть определенное кол-во информации которая повторяется.
Например, это реестр заявок, где каждая строка - это уникальная заявка, у которой присвоен код статьи бюджета. Вот в каждой из строк нужно построчным поиском из раскрывающегося списка сделать выбор.
А в Excel танцы с бубнами, приходится доп ячейку делать, чтобы в ячейке с выпадающим списком выпадало что надо, и для длинного списка использовать эту одну ячейку... эх...и для разных списков использующих в качсестве источника выпадающего списка те же данные городить новый рейтинг..
И чтобы формула могла искать по числам, то формат столбца должна быть текстовой.
Я вообще не понимаю, как функция МАКС может выбирать максимальное значение из множества ЛОЖЬ и ИСТИНА. Она ведь должна выбирать максимальное из чисел. Даже в описании к функции МАКС написано: "Логические и текстовые значения игнорируются"....
Спасибо за статьи. Воспользовался функцией фильтр для создания выпадающего списка с поиском. Вот только не могу сообразить, как сделать так, чтобы в каждой следующей строке таблицы выпадающий список ссылался на тот же диапазон и работал поиск. Смысл такой, что у меня есть база из которой сотрудники в каждой новой строке (на каждый день) поиском должны выбирать контрагента не вбивая его руками. Если его нет в базе, то соответственно должны внести его в базу.
А у меня получается, что при вводе первой строки она выдаёт значение из базы и следующая строка ссылается при поиске на предыдущий отбор, а мне надо, чтобы она захватывала опять всю базу формировала новый отбор и т.д. Я в душе понимаю, что это сделать можно. Но что-то заклинило и всё))) Заранее благодарен за помощь. Очень нужно.
По идее по логике при входе в выпадающий список на следующей строке он должен формировать новый отбор, как бы стирая старый, но при этом сохранять значение, которое было выбрано в выпадающем списке ранее строкой выше.. А вот как это технически сделать я не знаю((
Дело в том, что я создал таблицу, в каждой строке которой есть выпадающей список, и чтобы он корректно работал, отбор я делаю в той же строке, что и выпадающий список, естественно при таком способе отбор через поиск работает только, когда поиск выдаёт только один результат, в противном случае (когда результат 2 или более значений) второе и последующие значения упираются в ячейки, в которых уже есть данные другого отбора и поиск не работает.
Но вот не могу понять логики последнего шага с "помещением" поименованного списка в ячейку поиска. Ведь при этом выбор становится возможным только из значений списка и при вводе произвольного текста для поиска выдаёт ошибку.
Именно это у меня и происходит. Или я чего не понимаю?
Спасибо за Ваш труд, очень информативно.
Но, к сожалению, я ищу другую нужную мне опцию, которой я так и не нашёл на просторах:
Как сделать, чтобы при одном только наведении курсора на ячейку, сразу же всплывал список выбора и оставалось только кликнуть в нужное значение одним кликом?
Коллега утверждает, что видел такое недавно.
Спасибо Вам, в любом случае!