Выпадающий список с поиском в excel
Одна из популярных операций, которую, изучающие Excel, хотят освоить в первую очередь, это умение создавать выпадающий список в ячейке.
Чем хорош такой выпадающий список в ячейке?
- Это основа простого пользовательского интерфейса. Проще этого нет ничего. Никаких форм - выпадающий список встроен непосредственно в ячейку Excel
- Легко создавать, не требует никакого программирования
- Позволяет безопасно получить от пользователя строго регламентированный ввод, так как пользователь лимитирован значениями из списка
Я предлагаю разобрать самый удобный и современный способ сделать это, расчитанный на Excel 2007 и выше.
Шаг 1 - создание таблицы-источника
Ячейки, которые станут источником для будущего списка, желательно разместить на отдельном листе, снабдить их заголовком, убедиться, что снизу этот столбец ничто не ограничивает (так как список может впоследствии расти).
Выделяем этот диапазон и выполняем команду ВСТАВКА -> Таблица , отмечаем галку Таблица с заголовками , жмём OK.
Наш справочник превращается в так называемую "умную таблицу", что заметно по тому, что к таблице применилось стандартное форматирование по умолчанию.
Умные таблицы заслуживают отдельного разговора, но в нашем конкретном случае они нам любезны тем, что:
- Умеют расти вниз, путём обычного дописывания значений снизу. То есть таблица "понимает", что снизу приписали ещё одно новое значение и автоматически себя расширяет без дополнительных команд от пользователя
- Умеют возвращать формулой свои столбцы (об этом ниже)
Шаг 2 - даём таблице название
На вкладке КОНСТРУКТОР даём нашей умной таблице имя. Этот шаг можно было бы не выполнять, так как Excel автоматически даёт всем объектам имена, в частности для таблиц это "Таблица1", "Таблица2" и т.д., но давайте воспитывать в себе правильные привычки и вовремя давать нашим таблицам осознанные имена-идентификаторы, это может нам сильно облегчить жизнь впоследствии. Дайте нашей таблице имя tblAgents, где tbl - префикс, который мы будем давать всем нашим умным таблицам, а Agents - говорит о том, что мы выбираем имя торгового агента.
Шаг 3 - создаём именованный диапазон для нашего списка
Теперь необходимо создать именованный диапазон, который будет возвращать столбец из tblAgents . Выбираем команду ФОРМУЛЫ -> Диспетчер имен и Создать.
Создаём диапазон с именем listAgents, который ссылается на следующую формулу =tblAgents[Имя] , где в квадратных скобках название заголовка единственной колонки нашей таблицы.
Таким образом, у нас уже 2 именованных диапазона, ссылающихся на таблицу агентов:
Шаг 4 - Создание выпадающего списка
Ну и в качестве заключительного шага выбираем любую ячейку, в которой нам необходимо иметь выпадающий список, выполняем команду ДАННЫЕ -> Проверка данных. , задаём Тип данных равным Список , а в поле источник через клавишу F3 выбираем именованный диапазон listAgents .
. и получаем желаемый результат:
Классический выпадающий список в ячейке листа 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) , а в качестве источника введем имя нашего созданного диапазона со знаком равно перед ним:
Вот и всё. Можно жать на ОК и наслаждаться результатом:
Для пущего удобства при вводе с клавиатуры можно использовать Ctrl + Enter вместо Enter после ввода текста (так активная ячейка не уходит вниз) и сочетание клавиш Alt + стрелка вниз , чтобы развернуть выпадающий список без мыши.
В принципе, можно было бы и не продолжать, но недавно Microsoft выкатила обновление вычислительного движка Excel, который теперь поддерживает динамические массивы и имеет специальные функции для работы с ними. Большинству пользователей они станут доступны в ближайшие месяцы, но даже если пока этих возможностей в вашем Excel нет - грех не показать как элементарно с их помощью решается наша задача.
Всё, что мы делали на Шагах 1-3 заменяется одной(!) формулой, где новая функция ФИЛЬТР (FILTER) отбирает из исходного диапазона A2:A251 только те фильмы, которые содержат заданную подстроку.
И всё. Никаких именованных диапазонов и медленных СМЕЩ, никаких танцев с дополнительными столбцами и формулами. Песня!
Выпадающие списки в Excel - это тема, которая интересует многих пользователей программы, ведь с помощью таких списков можно существенно облегчить ввод информации в таблицу или создать удобный интерфейс для доступа к данным. Но если выпадающий список имеет слишком много элементов, то быстро найти нужный из них становится затруднительным. В этой ситуации будет полезен выпадающий список с возможностью поиска.
Например, нужно по имени человека выводить его номер телефона.
В обычном выпадающем списке будут перечислены все имена из телефонной книги (таблицы с данными) и быстро найти нужного человека не так-то просто. Однако если список имеет возможность поиска, то можно ввести какой-то набор символов и в списке отобразятся только те данные, в которых этот набор символов встречается, причем неважно в какой части.
Как и многое в Excel создать такой список можно разными способами. У каждого из них есть свои плюсы и минусы.
Excel постоянно совершенствуется и в нем появляются инструменты, которые позволяют существенно упростить ряд задач, поэтому я расскажу о двух способах создания выпадающего списка с быстрым поиском.
Файлы с примерами можно скачать здесь .
Первый вариант выпадающего списка с поиском (динамические массивы)
Итак, есть исходная таблица в два столбца. Первый содержит список имен, а второй телефоны. В еще одну из ячеек первой строки введем набор символов, который мы хотим найти, то есть смоделируем поисковое поле (введем " сми ").
Ниже этой ячейки мы должны сформировать список только из тех данных исходной таблицы, которые содержат введенные выше символы.
Поможет в этом функция ПОИСК ( SEARCH ), которая ищет заданный набор символов и выдает либо порядковый номер первого символа в тексте, либо ошибку, если символы обнаружены не были.
В качестве искомого текста указываем ссылку на ячейку выше, а искать мы будем в первой ячейке столбца с именами. Последний аргумент функции необязателен и в нашем случае указывать его не будем.
Что мы получили в итоге?
Цифра "1" указывает нам позицию введенных символов в выбранном нами тексте (строка начинается с этих символов).
Если введем « але », то получим цифру 9, которая указывает на девятую позицию. Именно девятым будет первый символ искомого текста в этом имени (пробел также учитывается).
Если же введем « ива », то получим ошибку ЗНАЧ! , которая указывает на то, что такой комбинации символов в тексте не найдено.
Так работает функция ПОИСК и нам лишь остается указать весь диапазон, на который она должна распространяться. Для этого откорректируем формулу и подставим сюда весь диапазон - выбираем первую его ячейку (А2), а затем нажимаем сочетание клавиш Ctrl + Shift + стрелка вниз . Будет выбран весь неразрывный диапазон значений. В моем случае А2:А30. Нажимаем Enter и получаем диапазон значений, который представляет собой динамический массив . На это также указывает синий контур, обрамляющий значения.
Динамические массивы в Excel появились несколько лет назад, но до сих пор они доступны лишь пользователям Microsoft Office 365 и в онлайн-версии офисного пакета. В других версиях и редакциях Excel динамических массивов пока нет и вряд ли они там появится. Скорее всего в коробочной редакции Excel их стоит ждать лишь в следующей версии офисного пакета в 22-ом году.
Тем не менее, динамические массивы и функции с ними связанные очень сильно расширяют возможности Excel и я уверен, что в дальнейшем они получат еще более широкий функционал. По этой причине, хоть данный способ пока и не универсален, но он максимально прост, удобен и в перспективе будет востребован. Поэтому я детально рассмотрю весь процесс создания выпадающего списка с помощью динамических массивов и их функций.
Итак, мы получили динамический массив. Если в его ячейках отражается любая цифра, то это означает, что искомая комбинация символов была найдена, а ошибка свидетельствует об обратном.
Фактически нас интересуют только ячейки с числами, при этом сами значения неважны. Поэтому можем переконвертировать полученные значения в формат ИСТИНА/ЛОЖЬ .
Для этого воспользуемся функцией ЕЧИСЛО ( ISNUMBER ) и обернем ей созданную ранее формулу. В результате если в ячейке будет находиться число, то функция выдаст ИСТИНУ , иначе ЛОЖЬ .
Осталось лишь отфильтровать значения, ведь нас интересует только ИСТИНА . Воспользуемся функцией ФИЛЬТР ( FILTER ), которая позволяет работать с динамическими массивами.
Обернем созданную ранее формулу функцией ФИЛЬТР .
Первый аргумент функции - массив . Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем « не найдено ».
Все, формула готова!
Вот так просто мы получили список из имен, которые удовлетворяют условиям поиска. Осталось лишь создать выпадающий список. Для этого откорректируем формулу и подставим в функцию ПОИСК ссылку на ячейку, в которой будет выпадающий список. В моем примере эти ячейки находятся на втором листе.
В предыдущих заметкам я рассмотрел самый простой вариант реализации выпадающего списка с поиском.
Давайте рассмотрим второй, более сложный, но и более универсальный вариант.
Так как нам нужно обойтись без динамических массивов, то создадим вспомогательный столбец (1), в котором с помощью той же функции ПОИСК будем искать введенную комбинацию символов (2). При этом не забываем сделать ссылку на ячейку с будущим списком К2 абсолютной.
Далее по аналогии с помощью функции ЕЧИСЛО конвертируем полученные значения в ИСТИНУ или ЛОЖЬ .
Теперь преобразуем ЛОЖЬ в 0, а ИСТИНУ в последовательность чисел, с помощью которых в дальнейшем сформируем список. То есть сделаем так, чтобы вместо каждого значения ИСТИНА была порядковая цифра - 1,2,3 и т.д. Сделать это довольно просто с помощью обычной функции ЕСЛИ - если вычисленное формулой значение ИСТИНА , то выбираем максимальное значение в столбце и прибавляем к нему единицу, иначе, то есть в случае ЛЖИ , выводим ноль.
В результате получим пронумерованный перечень подходящих значений, который нужно перенести в отдельную таблицу. Именно эту таблицу мы и будем использовать для формирования элементов выпадающего списка.
Сделать это можно, например, с помощью функции ВПР . Для этого вручную добавим вспомогательный столбец с порядковыми номерами (в столбце F) и будем его использовать в функции ВПР .
Размножим формулу автозаполнением на весь диапазон, который по размеру равен исходной таблице.
Осталось выделить только действительные (неошибочные) значения и сформировать из них диапазон, который в дальнейшем будем использовать для выпадающего списка.
Так как количество элементов в этом диапазоне будет все время меняться в зависимости от введенных в ячейку K2 символов, то применим небольшую хитрость - создадим именованный диапазон .
Для этого на вкладке Формулы (1) выберем инструмент Диспетчер имен (2) и создадим новое имя (3). В качестве имени диапазона можно указать что угодно, например, « фио » (4). Ну а так как размеры диапазоны все время будут изменяться, то без формул (5) тут не обойтись. Нам пригодится функция СМЕЩ ( OFFSET ).
Данная функция возвращает ссылку на диапазон, отстоящий от начальной ячейки на заданное число строк и столбцов, то есть как раз то, что нам нужно, ведь начальная ячейка нам известна.
Укажем абсолютную ссылку на начальную ячейку G2, ну а так как сдвиг вниз и вправо у нас отсутствует, то следующие два аргумента будут равны нулю. Четвертый аргумент будет определять размер диапазона и это будет максимальное значение из вспомогательного столбца первой таблицы. Получить его очень просто с помощью функции МАКС . Ну и так как диапазон у нас имеет ширину в один столбец, то последний аргумент будет единицей.
Уже сегодня выпадающие списки с поиском в Excel в основном создают с помощью динамических массивов – быстро, просто, динамично. Единственная проблема заключается в том, что динамические массивы пока, к сожалению, доступны лишь пользователям Office 365, а стандартным пользователям без платной подписки они станут доступными скорее всего лишь в новом пакете Microsoft Office 2021-го года (где-то в октябре).
Поэтому я решил сделать видео о том, как можно создавать универсальные выпадающие списки с поиском лишь с помощью классических функций, в результате чего такой подход был бы применим, пожалуй, в любой версии Excel.
Так что предлагаю посмотреть следующее видео и тоже попробовать создать подобный выпадающий список:
MS, Libreoffice & Google docs
535 постов 13.4K подписчик
Правила сообщества
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.
Давно хотел спросить - а почему посты не текстовые? Смотреть 16 минут то, что можно прочитать за пару минут, и настроить за несколько минут, не очень радует.
Уже спрашивал в других темах, поиск по одному слову это круто, а можно искать по двум словам? Или трем и т.д.
т.е. найти "корица молотая" по запросу "ко мо"
Подскажите, как реализовать такой поиск не в одной ячейке, а чтобы поиск с выпадающим списком был в каждой новой строке одного и того же столбца. Иными словами в вашем примере - это одна абс. ячейка, а надо чтобы каждая новая строка искала и выпадал список.
Подскажите, пожалуйста, где можно поискать специалиста по LibreOffice? Объясню зачем: есть задача перейти с MS Office на LibreOffice:
Перевести формирования отчетов из MS Office (Excel/Word) на LibreOffice (Calc Writer). Предлагаемое решение: Переписать функции библиотеки runtime.EXCEL и runtime.WORD. Пояснения: Для формирования отчетов используется набор процедур и функций двух библиотек (runtime.EXCEL и runtime.WORD), с помощью которых формируется текст скрипта на VBS.
Готовый текст скрипта включает в себя все необходимое для формирования офисного документа, начиная от создания необходимого com-объекта, формирование необходимых данных в открытом документе и, при необходимости, сохранение готового документа.
Ищем человека на постоянную работу, с возможностью полностью удаленной работы (но для любителей офиса - готовы предложить и оффис).
Читайте также: