Выпадающий список с быстрым поиском в Excel
Классический выпадающий список в ячейке листа Excel, сделанный через Данные - Проверка - простая и удобная штука, которую ежедневно применяют очень многие пользователи. Однако, у этого списка есть один весьма серьезный недостаток - в нём нет быстрого поиска по первым символам, т.е. фильтрации (отбора) только тех значений, куда введённый фрагмент входит как подстрока. Это серьезно ухудшает удобство пользования даже если в списке всего пара-тройка десятков позиций, а при нескольких сотнях убивает юзабилити напрочь.
Давайте рассмотрим, как всё же реализовать подобный трюк. В качестве подопытного кролика возьмём список 250 лучших фильмов по версии IMDb:
Конечная цель - создать выпадающий список (ячейка G3), в котором можно будет быстро находить нужные фильмы, введя только жанр, год или фрагмент названия, например "гамп".
Шаг 1. Определяем, кто нам нужен
Сначала нам нужно понять, какие из исходных ячеек нужно показывать в списке, т.е. определить, содержится ли введённый в выпадающем списке текст (например, жанр "детектив") в названии фильма.
Для этого добавим слева от исходных данных еще один столбец с функцией ПОИСК, которая ищет заданную подстроку в тексте и выдает либо порядковый номер символа, где он был обнаружен, либо ошибку, если его там нет:
Теперь завернем нашу формулу в функцию проверки ЕЧИСЛО, которая превратит числа в логическую ИСТИНУ (TRUE), а ошибки - в ЛОЖЬ (FALSE):
Теперь сделаем так, чтобы ЛОЖЬ превратилась в 0, а вместо ИСТИНА в столбце появились последовательно возрастающие индексы-числа 1,2,3... и т.д. Это можно сделать с помощью добавления к нашей же формуле ещё парочки функций:
Здесь функция ЕСЛИ проверяет что мы имеем (ИСТИНУ или ЛОЖЬ), и
- если была ИСТИНА, то выводит максимальное значение из всех вышестоящих чисел + 1;
- если была ЛОЖЬ, то выводит 0.
Шаг 2. Отбираем в отдельный список
Дальше - проще. Теперь банальной функцией ВПР просто выведем все найденные названия (я добавил столбец с порядковыми номерами для удобства):
После этого можно поиграться, вводя в жёлтую ячейку G2 разные слова и фразы и понаблюдать за тем, как наши формулы отбирают только подходящие фильмы:
Шаг 3. Создаем именованный диапазон
Теперь создадим именованный диапазон, который будет ссылаться на отобранные фильмы. Для этого выберем на вкладке Формулы команды Диспетчер имен - Создать:
Имя диапазона может быть любым (например, Фильмы), а самое главное - это функция СМЕЩ, которая и делает всю работу. Напомню её синтаксис, если вы подзабыли:
=СМЕЩ(начальная_ячейка; сдвиг_вниз; сдвиг_вправо; высота; ширина)
У нас:
- В качестве начальной ячейки задаём первую ячейку списка отобранных элементов (
E2); - Сдвиги вниз и вправо у нас отсутствуют, т.е. равны нулю;
- Высота диапазона у нас соответствует максимальному значению индекса из столбца А;
- Ширина диапазона - 1 столбец.
Осталось сделать выпадающий список.
Шаг 4. Создаем выпадающий список
Выделим жёлтую ячейку (G2) и выберем на вкладке Данные команду Проверка данных. В открывшемся окне выберем Список в поле Тип данных, а в качестве источника введем имя нашего созданного диапазона со знаком равно перед ним:
Чтобы Excel не ругался при вводе на неточное совпадение наших фраз с исходным списком, на вкладке Сообщение об ошибке в этом окне нужно выключить флажок Выводить сообщение об ошибке:
Вот и всё. Можно нажать на ОК и наслаждаться результатом.