Выпадающие списки в Excel - это тема, которая интересует многих пользователей программы, ведь с помощью таких списков можно существенно облегчить ввод информации в таблицу или создать удобный интерфейс для доступа к данным. Но если выпадающий список имеет слишком много элементов, то быстро найти нужный из них становится затруднительным. В этой ситуации будет полезен выпадающий список с возможностью поиска.
Например, нужно по имени человека выводить его номер телефона.
В обычном выпадающем списке будут перечислены все имена из телефонной книги (таблицы с данными) и быстро найти нужного человека не так-то просто. Однако если список имеет возможность поиска, то можно ввести какой-то набор символов и в списке отобразятся только те данные, в которых этот набор символов встречается, причем неважно в какой части.
Как и многое в Excel создать такой список можно разными способами. У каждого из них есть свои плюсы и минусы.
Excel постоянно совершенствуется и в нем появляются инструменты, которые позволяют существенно упростить ряд задач, поэтому я расскажу о двух способах создания выпадающего списка с быстрым поиском.
Файлы с примерами можно скачать здесь.
Первый вариант выпадающего списка с поиском (динамические массивы)
Итак, есть исходная таблица в два столбца. Первый содержит список имен, а второй телефоны. В еще одну из ячеек первой строки введем набор символов, который мы хотим найти, то есть смоделируем поисковое поле (введем "сми").
Ниже этой ячейки мы должны сформировать список только из тех данных исходной таблицы, которые содержат введенные выше символы.
Поможет в этом функция ПОИСК (SEARCH), которая ищет заданный набор символов и выдает либо порядковый номер первого символа в тексте, либо ошибку, если символы обнаружены не были.
В качестве искомого текста указываем ссылку на ячейку выше, а искать мы будем в первой ячейке столбца с именами. Последний аргумент функции необязателен и в нашем случае указывать его не будем.
Что мы получили в итоге?
Цифра "1" указывает нам позицию введенных символов в выбранном нами тексте (строка начинается с этих символов).
Если введем «але», то получим цифру 9, которая указывает на девятую позицию. Именно девятым будет первый символ искомого текста в этом имени (пробел также учитывается).
Если же введем «ива», то получим ошибку ЗНАЧ!, которая указывает на то, что такой комбинации символов в тексте не найдено.
Так работает функция ПОИСК и нам лишь остается указать весь диапазон, на который она должна распространяться. Для этого откорректируем формулу и подставим сюда весь диапазон - выбираем первую его ячейку (А2), а затем нажимаем сочетание клавиш Ctrl + Shift + стрелка вниз. Будет выбран весь неразрывный диапазон значений. В моем случае А2:А30. Нажимаем Enter и получаем диапазон значений, который представляет собой динамический массив. На это также указывает синий контур, обрамляющий значения.
Динамические массивы в Excel появились несколько лет назад, но до сих пор они доступны лишь пользователям Microsoft Office 365 и в онлайн-версии офисного пакета. В других версиях и редакциях Excel динамических массивов пока нет и вряд ли они там появится. Скорее всего в коробочной редакции Excel их стоит ждать лишь в следующей версии офисного пакета в 22-ом году.
Тем не менее, динамические массивы и функции с ними связанные очень сильно расширяют возможности Excel и я уверен, что в дальнейшем они получат еще более широкий функционал. По этой причине, хоть данный способ пока и не универсален, но он максимально прост, удобен и в перспективе будет востребован. Поэтому я детально рассмотрю весь процесс создания выпадающего списка с помощью динамических массивов и их функций.
Итак, мы получили динамический массив. Если в его ячейках отражается любая цифра, то это означает, что искомая комбинация символов была найдена, а ошибка свидетельствует об обратном.
Фактически нас интересуют только ячейки с числами, при этом сами значения неважны. Поэтому можем переконвертировать полученные значения в формат ИСТИНА/ЛОЖЬ.
Для этого воспользуемся функцией ЕЧИСЛО (ISNUMBER) и обернем ей созданную ранее формулу. В результате если в ячейке будет находиться число, то функция выдаст ИСТИНУ, иначе ЛОЖЬ.
Осталось лишь отфильтровать значения, ведь нас интересует только ИСТИНА. Воспользуемся функцией ФИЛЬТР (FILTER), которая позволяет работать с динамическими массивами.
Обернем созданную ранее формулу функцией ФИЛЬТР.
Первый аргумент функции - массив. Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем «не найдено».
Все, формула готова!
Вот так просто мы получили список из имен, которые удовлетворяют условиям поиска. Осталось лишь создать выпадающий список. Для этого откорректируем формулу и подставим в функцию ПОИСК ссылку на ячейку, в которой будет выпадающий список. В моем примере эти ячейки находятся на втором листе.
Теперь на вкладке Данные откроем окно проверки и создадим список.
В качестве источника данных укажем первую ячейку динамического массива и добавим к ней знак решетки, что позволит нам получить ссылку на весь динамический массив.
Если мы теперь введем что-то новое в поисковую строку, то получим ошибку, так как мы вводим неточное совпадение, которое отсутствует в списке.
Чтобы отключить это сообщение вновь вызовем окно проверки и на вкладке Сообщение об ошибке уберем флажок Выводить сообщение об ошибке.
Теперь при введении нового значения ошибка возникать не будет, а в выпадающем списке будут только нужные вхождения.
При работе с таким списком удобнее использовать не Enter, а сочетание Ctrl + Enter так как после ввода текста табличный курсор не будет смещаться вниз.
Осталось лишь подставить номер телефона по имени и сделать это можно различными способами, например, с помощью функции ВПР или связки функций ИНДЕКС и ПОИСКПОЗ. Однако, если уж мы говорим о динамических массивах и функциях по работе с ними, то решим эту задачу с помощью все той же функции ФИЛЬТР.
В качестве массива данных укажем диапазон с телефонами, затем напишем небольшое условие - данные из диапазона с именами должны соответствовать информации из соответствующей ячейки, в которой находится выпадающий список. Ну а третий аргумент определяет, что нужно выводить в случае отсутствия данных, укажем пустоту.
В итоге получаем нужный нам результат.
Ну и чтобы можно было автоматически расширять данные в выпадающем списке нужно преобразовать исходные данные с именами и телефонами в умную таблицу, например, с помощью сочетания клавиш Ctrl + T.
После этого вновь добавленные в таблицу данные будут автоматически "подхватываться" выпадающим списком.
Функции СОРТ (SORT) и УНИК (UNIQUE)
Ну и если уж речь зашла о динамических массивах, то нельзя не сказать о функциях СОРТ и УНИК.
Например, мы хотим, чтобы данные в выпадающем списке были отсортированы по алфавиту. Просто оборачиваем формулу функцией СОРТ и получаем результат - данные будут отсортированы в динамическом массиве, а значит и в выпадающем списке.
Ну а если данные в исходной таблице дублируются, а нужно получить только уникальные значения, то можно воспользоваться функцией УНИК. Эта функция убирает повторы и в результате мы получаем список без дубликатов.
Итак, мы получили выпадающий список с поиском в одной ячейке. Но как сделать так, чтобы его можно было использовать для заполнения данных в таблице?
Об этом в следующей заметке.
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
