Применяем фильтр в выпадающем списке Excel (все версии).
Для чего вообще в выпадающем списке нужен фильтр?
В ходе работы часто возникает ситуация, кода необходимо для внесения данных выбирать нужные значения из заданного перечня. Такой способ позволяет не только ускорить введение данных, но и избавиться от возможных ошибок при наборе текста. Этот инструмент называется «Выпадающий список и создается командой: «Данные» – «проверка данных» – «тип данных» – «Список»
Как видно, работа по созданию списка не трудна. Однако, при наличии в списке множества записей, то ориентироваться в нем уже непросто. Способ поиска с помощью ввода текста-шаблона или с помощью сортировки в данном случае часто не только невозможен, но и бесполезен
Для отбора строк с нужным текстом удобнее всего применить фильтр. Применение фильтрации списка в Excel позволит отобрать требуемые значения на основе содержимого составляющих выпадающего списка.
В течении нашего занятия создадим фильтр выпадающего списка в ячейке, основываясь на функциях, имеющихся в любой версии MS Excel. Это позволит применить данный способ в любой ситуации. Способ достаточно прост и быстр. При создании фильтра кратко будут описаны функции, которые используются, и те параметры этих функций, которые необходимо использовать. Чтобы создать такой фильтр, нам понадобится всего пять шагов.
Исходная ситуация следующая. Имеется два листа – ИтоговыйСписок и Проверка. На листе ИтоговыйСписок в ячейке А1 будет задаваться проверочный текст, а в ячейке А2 будет выводится выпадающий список, отфильтрованный согласно заданному проверочному тексту и полученный на основе диапазона А2:А15 на листе Проверка. Вместо указанного списка может быть любой перечень данных – список сотрудников, перечень оборудования, перечень поставщиков, список листов книги и так далее.
Приступим к работе. Будьте внимательны и отслеживайте текст формул и функций, которые применятся.
Шаг первый. Проверяем вхождение шаблонного текста в список.
Для такой проверки можно применить функции ПОИСК или НАЙТИ. Функция НАЙТИ чувствительна к регистру, поэтому применим функцию ПОИСК во избежание ошибок с несовпадающим регистром. Ее синтаксис
=ПОИСК(искомый текст; строка для поиска наличия искомого текста).
Если данный текст в указанной строке содержится, то результатом будет номер первого знака, с которого начинается заданный текст для поиска в указанной строке. Если текст найден не будет, мы увидим значение ошибки.
Введем в ячейку A1 листа ИтоговыйСписок текст, который явно присутствует в ячейках нашего списка на листе проверка. Следом на листе проверка в ячейке В2 введем формулу
=ПОИСК(ИтоговыйСписок!$A$1;Проверка!A2)
Обратите внимание – ячейка с листа ИтоговыйСписок, где лежит проверяемый текст, имеет абсолютный адрес. Протягиваем формулу вниз и зададим для полученного столбца для удобства название «Вхождение»
Рисунок 1
Все работает, можно двигаться дальше. Но привязка к определенной ячейки для проверки не очень хорошее решение. Те, кого все же это устраивает, могут сразу перейти к третьему шагу. Для меня же при достаточно большой таблице неудобно каждый раз возвращаться в ее начало, чтобы поменять условие для фильтра. Поэтому я перехожу ко второму шагу.
Шаг второй. Выведем возрастающую нумерацию для ячеек списка на листе проверка, в которых имеется проверочное слово.
Чтобы это сделать, применим функцию ЕСЛИ в столбце С листа проверка. Она позволяет проверить условие и вывести один из двух вариантов в зависимости от его соблюдения.
В качестве условия применим функцию ЕЧИСЛО, с помощью которой обернем нашу функцию поиск. Если функция ПОИСК найдет нужное слово в списке, то результатом будет цифра. Тогда функция ЕЧИСЛО выведет результат ИСТИНА, а если нет, то результатом функции ЕЧИСЛО будет ложь.
Для параметра ИСТИНА функции ЕСЛИ зададим автоматическую нумерацию полученных строк с найденными числами. Для этого возьмем максимум из предыдущих ячеек и прибавим к полученному единицу. Так как в начале никаких предыдущих ячеек фактически нет, то для функции МАКС возьмем диапазон С1:С1 и закрепим первую ячейку. В результате при копировании формулы вниз будем получать диапазоны C1:C2, C1:C3 и так далее.
Ну и для параметра ЛОЖЬ выведем пустое значение в виде двух кавычек подряд. В результате получаем такую запись.
=ЕСЛИ(ЕЧИСЛО(B2);МАКС($C$1:C1)+1;0)
Рисунок 2
Шаг третий. Соберем вместе все значения, соответствующие нашему искомому параметру.
В ячейке D2 листа проверка. Напротив ее первой ячейки применим функцию ИНДЕКС. Ее параметры – адрес исходного диапазона, номер строки и номер столбца этого диапазона, из пересечения которых надо взять данные. Исходным диапазоном будет диапазон А1:С121 на листе проверка. Номер нужной строки найдем с помощью функции ПОИСКПОЗ, указав для поиска с помощью функции СТРОКА номер строки верхней ячейки и запустив точный поиск в помощью типа сопоставления равного нулю. Номер столбца зададим первый.
=ИНДЕКС($A$2:$A$15;ПОИСКПОЗ(СТРОКА()-1;$C$2:$C$15;0);1)
Рисунок 3
Как видим, после перечня строк, содержащих последнее введение значение на листе список, для позиций с недоступными (проще говоря – не найденными) данными появилась ошибка Н/Д.. Избавимся от нее, обернув нашу формулу функцией ЕСЛИОШИБКА и указав в качестве второго параметра этой функции ноль. В результате получим формулу
=ЕСЛИОШИБКА(ИНДЕКС($A$2:$A$15;ПОИСКПОЗ(СТРОКА(D1);$C$2:$C$15;0);1);0)
Назовем добавленный столбец «Список»
Рисунок 4
Шаг четвертый. Сформируем на основе полученного перечня источник для выпадающего списка и применим его.
Переходим на лист ИтоговыйСписок. Выделяем ячейку А2, переходим на вкладку данные и нажимаем кнопку «проверка данных».
Рисунок 5
Тип данных выбираем «Список», а в строке “источник” введем функцию СМЕЩ. С ее помощью зададим диапазон, ячейки которого будут использованы в нашем списке. Параметры функции СМЕЩ по очереди в нашем случае:
1. начальная ячейка, относительно которой будем двигаться. В нашем случае это ячейка D2 на листе проверка. Не забываем ее закрепить.
2. сдвиг по строкам. В нашем случае его нет, поэтому просто отделим место этого параметра стандартным разделителем.
3. сдвиг по колонкам. Его тоже не будет.
4. количество строк в формируемом диапазоне. Чтобы это узнать, от общего количества заполненных ячеек в колонке D листа проверка отнимем количество числовых ячеек в этом же столбце. Найти количество тех и других можно функциями СЧЕТЗ и СЧЕТ соответственно. Необходимо так же учесть заголовок, поэтому из результата расчета надо дополнительно вычесть 1
5. Количество столбцов в формируемом диапазоне. У нас будет только одна колонка.
В итоге получаем
=СМЕЩ(Проверка!$D$2;;;СЧЁТЗ(Проверка!$D:$D)-СЧЁТ(Проверка!$D:$D)-1;1)
Рисунок 6
Рисунок 7
На первый взгляд все готово, но остались проблемы
Во-первых, мы вводим данные в отдельной ячейку. Желательно это делать в самой ячейке с списком.
Во-вторых, при вводе для поиска отсутствующих данных появится ошибка. Это не совсем удобно.
Исправим эту ситуацию.
Прежде всего на листе Проверка в ячейке E1 сделаем ссылку на ячейку с выпадающим списком листа ИтоговыйСписок.
Рисунок 8
После этого изменим формулу в столбце В листа Проверка, указав в качестве строки поиска содержимое ячейки E1 текущего листа.
Рисунок 9
Теперь откроем настройку проверки данных ячейки с списком и перейдем на вкладку «Сообщение об ошибке». Укажем вид действия «сообщение и введем само сообщение. Например, укажем заголовок «Недоступно» и укажем в сообщении текст «Содержимое не найдено»
Рисунок 10
При вводе строки для поиска будет появляться сообщение об ошибке. После нажатия кнопки ОК результатом ошибочного ввода будет само введенное значение, а вот при вводе имеющегося текста увидим нужный список
Рисунок 11
Подведем итог.
Мы научились фильтровать выпадающий список в Excel, применяя стандартные формулы и функции. Данный подход позволил упростить работу по применению выпадающего списка на практике. Этот способ работает во всех версиях Excel/ В следующих материалах мы расскажем, как упростить данную работу в версиях Microsoft Office 365, а также в версиях не меньше MS Office 2021. Вы можете самостоятельно попробовать выполнить работу уроку, скачав учебный фал по ссылке. Удалите в нем все формулу и попробуйте написать их заново.
Мы же желаем успехов в работе и применении в ней полученных сегодня знаний!
