Выпадающий список с быстрым поиском в Excel (без VBA)

Выпадающие списки в Excel — это тема, которая интересует многих пользователей программы, ведь с помощью таких списков можно существенно облегчить ввод информации в таблицу или создать удобный интерфейс для доступа к данным. Но если выпадающий список имеет слишком много элементов, то быстро найти нужный из них становится затруднительным. В этой ситуации будет полезен выпадающий список с возможностью поиска.

Например, нужно по имени человека выводить его номер телефона.

В обычном выпадающем списке будут перечислены все имена из телефонной книги (таблицы с данными) и быстро найти нужного человека не так-то просто. Однако если список имеет возможность поиска, то можно ввести какой-то набор символов и в списке отобразятся только те данные, в которых этот набор символов встречается, причем неважно в какой части.

Как и многое в Excel создать такой список можно разными способами. У каждого из них есть свои плюсы и минусы.

Excel постоянно совершенствуется и в нем появляются инструменты, которые позволяют существенно упростить ряд задач, поэтому я расскажу о двух способах создания выпадающего списка с быстрым поиском.

Первый вариант выпадающего списка с поиском (динамические массивы)

Итак, есть исходная таблица в два столбца. Первый содержит список имен, а второй телефоны. В еще одну из ячеек первой строки введем набор символов, который мы хотим найти, то есть смоделируем поисковое поле (введем » сми «).

Ниже этой ячейки мы должны сформировать список только из тех данных исходной таблицы, которые содержат введенные выше символы.

Поможет в этом функция ПОИСК ( SEARCH ), которая ищет заданный набор символов и выдает либо порядковый номер первого символа в тексте, либо ошибку, если символы обнаружены не были.

В качестве искомого текста указываем ссылку на ячейку выше, а искать мы будем в первой ячейке столбца с именами. Последний аргумент функции необязателен и в нашем случае указывать его не будем.

Цифра «1» указывает нам позицию введенных символов в выбранном нами тексте (строка начинается с этих символов).

Если введем « але », то получим цифру 9, которая указывает на девятую позицию. Именно девятым будет первый символ искомого текста в этом имени (пробел также учитывается).

Если же введем « ива », то получим ошибку ЗНАЧ! , которая указывает на то, что такой комбинации символов в тексте не найдено.

Динамические массивы в Excel появились несколько лет назад, но до сих пор они доступны лишь пользователям Microsoft Office 365 и в онлайн-версии офисного пакета. В других версиях и редакциях Excel динамических массивов пока нет и вряд ли они там появится. Скорее всего в коробочной редакции Excel их стоит ждать лишь в следующей версии офисного пакета в 22-ом году.

Итак, мы получили динамический массив. Если в его ячейках отражается любая цифра, то это означает, что искомая комбинация символов была найдена, а ошибка свидетельствует об обратном.

Фактически нас интересуют только ячейки с числами, при этом сами значения неважны. Поэтому можем переконвертировать полученные значения в формат ИСТИНА/ЛОЖЬ .

Для этого воспользуемся функцией ЕЧИСЛО ( ISNUMBER ) и обернем ей созданную ранее формулу. В результате если в ячейке будет находиться число, то функция выдаст ИСТИНУ , иначе ЛОЖЬ .

Осталось лишь отфильтровать значения, ведь нас интересует только ИСТИНА . Воспользуемся функцией ФИЛЬТР ( FILTER ), которая позволяет работать с динамическими массивами.

Обернем созданную ранее формулу функцией ФИЛЬТР .

Первый аргумент функции — массив . Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем « не найдено ».

Вот так просто мы получили список из имен, которые удовлетворяют условиям поиска. Осталось лишь создать выпадающий список. Для этого откорректируем формулу и подставим в функцию ПОИСК ссылку на ячейку, в которой будет выпадающий список. В моем примере эти ячейки находятся на втором листе.

Однако, если у вас десятки (а то и сотни) листов, а также, если вы хотите найти лист с определенным названием, такой метод вряд ли будет хорош. Создайте отдельную страницу в книге, на которой разместите указатель, связав гиперссылкой каждую строку с соответствующим листом. Вы сможете создать фильтр или находить листы контекстным поиском. Указатель можно создать двумя способами. [1]

Функция SEARCH (ПОИСК) в Excel. Как использовать? ЭКСЕЛЬ ХАК

Выберите свою диаграмму и перейдите на вкладку «Формат», которая появится после того, как вы это сделаете. В крайнем левом углу ленты щелкните раскрывающийся список «Элементы диаграммы» в разделе «Текущий выбор».

Однако, если у вас десятки (а то и сотни) листов, а также, если вы хотите найти лист с определенным названием, такой метод вряд ли будет хорош. Создайте отдельную страницу в книге, на которой разместите указатель, связав гиперссылкой каждую строку с соответствующим листом. Вы сможете создать фильтр или находить листы контекстным поиском. Указатель можно создать двумя способами. [1]

Выпадающий список с контекстным поиском — Пользовательский интерфейс — Excel — Каталог статей — Perfect Excel

Выберите свою диаграмму и перейдите на вкладку «Формат», которая появится после того, как вы это сделаете. В крайнем левом углу ленты щелкните раскрывающийся список «Элементы диаграммы» в разделе «Текущий выбор».

Первый аргумент функции — массив . Укажем диапазон с именами А2:А30. Далее нужно указать то, что нужно включить в новый диапазон, а это вычисляет ранее созданная формула. В качестве третьего аргумента зададим значение, которое будет выводиться в случае отсутствия искомых символов в тексте. Например, напишем « не найдено ».

= ЕСЛИ( $F2 ; СЧЁТЕСЛИ ( $F$2:$F2 ; ИСТИНА ); «»)

• Текст содержит : для поиска ячеек, которые содержат искомое слово как часть своего содержимого.
• Текст не содержит : найти ячейки, в которых нет искомого слова.
• Текст начинается с : для поиска ячеек, которые начинаются с искомого слова.
• Текст заканчивается на : найти ячейки, заканчивающиеся искомым словом.
• Текст точно : найти ячейки, содержащие точно и только искомое слово.

Вы можете сделать это, выбрав ячейку, которая содержит текст, и нажмите CTRL + K, затем выберите опцию/вкладку «Место в этом документе» и выберите вкладку, которую вы хотите активировать. Если вы нажмете на текст (теперь это ссылка), настроенный лист станет активным/выбранным.