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