Использование фильтра в выпадающем списке на основе динамических массивов
Сегодня рассмотрим фильтрацию выпадающего списка, созданную на основе динамических массивов. Такие массивы доступны в версиях Excel начиная с офиса 2021, а также в MS Office 365.
Шаг 1. Начнем нашу работу.
Имеется некий список в диапазоне А2:А15 на листе «проверка». Нужно создать выпадающий список на основе этих данных в ячейке А1 листа «Список»
Перейдем на лист «Проверка» и вставим в ячейку В1 ссылку на ячейку будущего списка, после чего введем в ячейку, где будем создавать список, заведомо верный текст.
Рисунок 1
Находясь на листе «проверка», в ячейке B2 пишем формулу
=ЕЧИСЛО(ПОИСК($B$1;A2:A15))
Здесь:
ПОИСК – последовательно проверяет вхождение текста из ячейки И1 в каждом элементе. Списка.
ЕЧИСЛО – преобразует результаты работы поискав логические выражения.
Таким образом мы создали формулу , которая проверяет динамически изменяемый массив ячеек и выводит для каждой из них значение ИСТИНА, если текст в ячейке найден, и значение ЛОЖЬ, если поиск завершился ошибкой.
Рисунок 2
Шаг 2. Применим функцию ФИЛЬТР
Далее обернем созданную формулу функцией ФИЛЬТР.
Ее синтаксис:
=ФИЛЬТР(диапазон фильтрации; условие фильтрации; значение в случае ошибочных данных).
В наше случае:
- Диапазон для фильтрации – диапазон с исходным списком на листе «проверка»
- Условие фильтрации – наша исходная формула
- Значение в случае ошибки – «Недоступно!»
Наша формула выглядит теперь так:
=ФИЛЬТР(A2:A15;ЕЧИСЛО(ПОИСК($B$1;A2:A15));”Недоступно”) 
Рисунок 3
Шаг 3. Создадим выпадающий список
Осталось создать выпадающий список в ячейке A1 листа «Список». Переходим на него, выделяем нужную ячейку и даем команду
«Данные» 🡪 «проверка данных».
Выполним настройку.
Вкладка параметры
Тип данных – список.
В строке источник указываем ссылку на ячейку B2 листа «Проверка», добавив в конце знак решетки. Получаем строку:
=Проверка!$B$2#
Рисунок 4
Вкладка «Сообщение об ошибке»:
Снимаем галочку «Выводит сообщение об ошибке»
Рисунок 5
Теперь, если указать текст, имеющийся в ячейках исходного списка, то результат будет таким:
Рисунок 6
А если указать отсутствующий в ячейках списка текст, то увидим сообщение, что оно недоступно.
Рисунок 7
Подведем итог.
На нашем занятии мы научились фильтровать выпадающий список на основе одноименной функции в свежих версиях Excel. Обратите внимание, что указанный способ гораздо проще и быстрее, чем универсальный метод, рассмотренный нами ранее для всех версий программы. Как обычно, вы можете скачать исходный файл для тренировки и самостоятельно выполнить работу по созданию списка, рассмотренную нами в текущем занятии.
На этом урок подошел к концу. Желаем вам успехов и до встречи на занятиях!
