В привычном виде, выпадающий список Excel, сделанный через вкладку Данные содержит некоторые недостатки – нет фильтра, нет возможности отражать только определенные элементы в списке по условию. При работе на больших данных может возникать сложность с поиском нужного значения. Давайте рассмотрим каким образом можно создать в выпадающем списке функцию поиска, чтобы в список попадали только те значения, которые содержат часть слова, указанного в ячейке.
Выглядеть это будет следующим образом:
Получение порядкового номер значений
Для начала нам нужно добавить столбец с указанием порядкового номера значений, которые мы отобрали исходя из поиска. За основу возьмем функцию ПОИСК, которая будет искать заданную комбинацию в тексте (в нашем примере будем искать “ова”).
- Введя сначала
ПОИСК($L$3;B2)мы получим номер элемента строки, где обнаружено значение. Если значения в строке нет, вернется ошибка. - Теперь добавим функцию
ЕЧИСЛО–ЕЧИСЛО(ПОИСК($G$3;B2)), чтобы определить является ли значение в ячейке числом. - И последнее – обернем всё это в функцию
ЕСЛИ–ЕСЛИ(ЕЧИСЛО(ПОИСК($G$3;B2));МАКС($A$1:A1)+1;0), чтобы в столбце у нас были порядковые номера или нули.
Создание нового списка и имени
Добавим еще одну таблицу с тем же объемом строк. В первом столбце укажем порядковые номера, а во втором через ВПР подставим значения – ВПР(D2;$A$2:$B$11;2;0). Функцию ЕСЛИОШИБКА добавил исключительно для лучшего восприятия.
И осталось лишь добавить новое имя в через “Диспетчер имен”. Для этого:
- Перейдем во вкладку “Формулы”. Где-то по середине в ленте будет пункт “Задать имя”. Появится окошко, в котором укажем имя и диапазон. В диапазоне разместите формулу
СМЕЩ–СМЕЩ($E$2;0;0;МАКС($A:$A);1).
Создание выпадающего списка
Сейчас мы готовы создать выпадающий список. В выпадающем списке в качестве источника укажем “=ФИО” (или нажмите клавишу F3 и выберите имя из созданных ранее).
И, чтобы система не выдавала ошибку при вводе значения не из списка – уберем сообщение об ошибке
