Мгновенная фильтрация на лету
Представьте, что вам часто приходится фильтровать одну и ту же таблицу по одному или (что гораздо хуже) нескольким столбцам, в попытках найти там частичное совпадение с заданным текстом. Например, вот в такой таблице с данными о доставках находить людей по фамилии или имени, причем нужный человек может оказаться как в столбце Отправитель, так и Получатель:
Само собой, можно включить классический фильтр через Данные - Фильтр (Data - Filter), развернуть выпадающий список в столбце Отправитель и ввести имя требуемого человека в поле поиска, но ведь потом придётся куда-то скопировать результаты, очистить фильтрацию и повторить поиск ещё раз в столбце Получатель. А если столбцов для поиска будет не 2, а больше? Тоска зелёная, правда?
Но есть другой путь. Суть его в том, чтобы добавить на лист поле ввода ActiveX, куда мы будем вводить искомый текст, а затем написать формулу, которая будет искать и выводить все строки в таблице, где в заданных столбцах есть частичное текстовое совпадение с введёнными данными.
Добавляем текстовое поле ввода ActiveX
Во-первых, давайте конвертируем нашу исходную таблицу в динамическую "умную", используя сочетание клавиш Ctrl+T или команду Главная - Форматировать как таблицу (Home - Format as Table). Это не обязательно, но упростит нам дальнейшую работу и написание формул.
Во-вторых, для вставки текстового поля и последующей его настройки нам потребуется вкладка Разработчик (Developer). Если у вас её не видно, то щёлкните правой кнопкой мыши по любому месту ленты и выберите команду Настройка ленты (Customize ribbon), а затем включите соответствующий флажок.
После этого разверните выпадающий список Вставить (Insert) и выберите оттуда текстовое поле из блока ActiveX (не перепутайте с похожим полем из блока Элементы управления формы):
Нарисуйте поле ввода в любом удобном месте. Если при этом удерживать нажатой клавишу Alt, то рисование будет происходить ровно по границам ячеек.
| ! | Если при вставке поля у вас возникает сообщение об ошибке, то возможно у вас отключены элементы ActiveX по настройкам безопасности. Чтобы их разрешить выберите Файл - Параметры - Центр управления безопасностью - Параметры центра управления безопасностью - Параметры ActiveX (File - Options - Trust Center - Trust Center options - ActiveX settings) |
Теперь щёлкните правой кнопкой мыши по нарисованному полю и выберите команду Свойства (Properties) - откроется панель со списком всех настроек, которые есть у этого элемента. Здесь можно, при желании, поменять его дизайн (параметры BackColor, BorderColor, Font), ограничить длину вводимого текста (параметр MaxLenght) и т.д. Но главное свойство, которое нас интересует - это связанная ячейка (LinkedCell):
Введите в это поле адрес любой свободной ячейки (например A1) и нажмите Enter. Панель свойств после этого можно закрыть.
Чтобы протестировать как работает наше текстовое поле ввода, нам нужно переключиться из режима конструктора (т.е. режима разработки, где мы сейчас находимся) в рабочий режим. Для этого необходимо отжать соответствующую кнопку на вкладке Разработчик:
Теперь при вводе любых символов в поле они будут передаваться в связанную ячейку, причем - мгновенно, без дополнительных нажатий на Enter или щелчков мышью, как это бывает при вводе в ячейку с клавиатуры или использовании фильтра. А значит Excel будет автоматически запускать на листе пересчёт формул, которыми мы дальше и воспользуемся для фильтрации.
Фильтруем нужные строки
Осталось дело за малым - написать формулу, которая будет отбирать нужные нам строки - те, где в столбцах Отправитель или Получатель встречается текстовый фрагмент из ячейки A1.
Если у вас Excel версии 2021 или новее, то всё просто - достаточно использовать новую функцию ФИЛЬТР для решения этой задачи. Для начала давайте проверим отправителя:
Первый аргумент функции ФИЛЬТР - это фильтруемый массив, т.е. наша исходная таблица Заказы.
Второй аргумент - это критерий отбора, т.е. формула, возвращающая логические значения ИСТИНА или ЛОЖЬ, определяющие нужно ли включать очередную строку в результаты фильтрации. Здесь мы проверяем, входит ли подстрока из А1 в отправителя с помощью функции ПОИСК (SEARCH), которая возвращает либо число - порядковый номер символа, начиная с которого текст был найден, либо ошибку - если искомая подстрока в тексте не содержится. Этот факт мы проверяем с помощью функции ЕЧИСЛО (ISNUMBER) и используем как критерий для отбора нужных строк.
Чтобы выполнять поиск не по одному, а сразу по нескольким столбцам, проще всего склеить их значения в общую строку, используя символ сцепки & и любой подходящий символ как разделитель (например, дефис или подчеркивание). Так можно легко добавить в формулу проверку не только отправителя, но и получателя одновременно:
После этого ввод любого фрагмента текста в наше поле ActiveX приведёт к изменению ячейки А1, что, в свою очередь, вызовет пересчёт формул и обновление результатов фильтрации на лету.
Заодно можно легко посчитать количество найденных строк, используя функцию ЧСТРОК (ROWS) и ссылку на весь динамический массив с результатами фильтрации, начинающийся с ячейки B5:
Для перехвата ошибки #ВЫЧИСЛ! в тех случаях, когда введённый текст не найден ни в одной строке данных, можно использовать третий аргумент функции ФИЛЬТР:
=ФИЛЬТР(Заказы ; ЕЧИСЛО(ПОИСК(A1;Заказы[Отправитель]&"-"&Заказы[Получатель])) ; "нет данных")
Если нет функции ФИЛЬТР
Если вы используете старые версии Excel (2019 или древнее), где ещё не было функции ФИЛЬТР (FILTER), то для отбора нужных строк придётся действовать похитрее.
Во-первых, лучше добавить слева от будущих результатов фильтрации вспомогательную колонку с порядковыми номерами строк - она пригодится нам чуть позже и позволит решить задачу обычной формулой, а не медленной формулой массива.
Во-вторых, формула для фильтрации нужных строк только будет выглядеть так:
Здесь:
- Функция ЕСЛИ (IF) проверяет вхождение фрагмента из ячейки А1 в имя отправителя с помощью функций ПОИСК (SEARCH) и ЕЧИСЛО (ISNUMBER) и возвращает номер строки в таблице, где вхождение было найдено. Номер строки нам даёт функция СТРОКА (ROW), результат которой мы уменьшаем на 4, т.к. над таблицей у нас 3 пустых строки плюс шапка.
- Функция НАИМЕНЬШИЙ (SMALL) последовательно выдаёт все номера строк, где было найдено совпадение.
- Функция ИНДЕКС (INDEX) извлекает данные по очереди из каждого столбца таблицы по номерам строк, которые выдала функция НАИМЕНЬШИЙ.
- Поскольку мы заранее не можем знать, сколько именно совпадений найдется, то эту формулу (и нумерацию слева) придётся протянуть вниз с запасом. Чтобы на лишних строчках не появлялась ошибка - заворачиваем всю нашу эпическую формулу в функцию ЕСЛИОШИБКА (IFERROR).
Бонус
Вдогон к удобной мгновенной фильтрации бонусом идёт ещё один момент. Дело в том, что функция ПОИСК (SEARCH) поддерживает спецсимволы * (звездочка - любое количество любых символов) и ? (вопросительный знак - один любой символ). Соответственно, мы спокойно можем использовать их в поле ввода и искать не просто вхождение текста, а более сложные случаи. Например, запрос М*А найдет нам все фрагменты, начинающиеся на "М" и заканчивающиеся на "А", т.е. Марину, Машу, Милену, Максима и т.п. Мелочь, а приятно.
Также учтите, что функция ПОИСК не чувствительна к регистру. Если нужно искать строго с учётом регистра, то её можно заменить на аналогичную функцию НАЙТИ (FIND).
Ссылки по теме
- Многоразовый ВПР - ищем все совпадения, а не только первое
- Горизонтальная фильтрация столбцов в Excel
- Функции динамических массивов: ФИЛЬТР, СОРТ и УНИК
В 'Без функции ФИЛЬТР'!E2 всё же =СУММПРОИЗВ(--(B5:B1000<>"")).
Огромная благодарность!!!
Отдельное спасибо за, и, восхищение даром лаконичного, доходчивого повествования!!!
И я делаю отдельный фильтр конкретно на 1 столбец. Ищу накладную 31 , в итоге получаю список всех накладных , в которых есть сочетание 31 (31,131,231 и тд).
Суть вопроса , какую функцию можно внедрить чтоб при вводе числа 31 фильтр выдал список только с накладной 31?
На листе созданы 2 поля ввода для фильтра посредством одной формулы для одной и той же таблицы.
=ФИЛЬТР(Таблица1;ЕЧИСЛО(ПОИСК(B2;Таблица1[Цена]&"-"&Таблица1[Серийный номер]&"-"&Таблица1[Название]&"-"&Таблица[От кого];ПОИСК(J2;Таблица1[Накладная]&"-"&Таблица1[Номер документа])));"НЕТ данных")
Все работает как надо , но в случае 1-го фильтра ,в котором склеяно большенство столбцов , все устраивает . А вот во втором есть необходимость искать строго вводимые значения.
=ЕСЛИ($C$5="";
ЕСЛИ($C$2="";
ФИЛЬТР(Заказы;ЕЧИСЛО(ПОИСК($C$4;Заказы[Отправитель]&"-"&Заказы[Получатель]&"-"&Заказы[Стоимость]));"Нет данных");
ФИЛЬТР(Заказы;ЕЧИСЛО(ПОИСК($C$4;ДВССЫЛ("Заказы["&$C$2&"]")));"Нет данных"));
ФИЛЬТР(Заказы;Заказы[Код]=$C$5;"Код не найден"))
Немного расширил функционал поиска, т.к. полей для поиска в моих таблицах больше чем две.
Добавил возможность выбора полей поиска отдельно сохранив возможность поиска по всем полям таблицы кроме поля "Код". А также добавил точный поиск по полю "Код". К сожалению поиск точного совпадения, через единое поле не получается: ActiveX возвращает текст, а ФИЛЬТР его не распознает как число. Поэтому пришлось добавить приоритетное поле для точного поиска по полю "Код".
С5 - ячейка для поиска по "Коду"
С2 - Выпадающий список полей таблицы (Отправитель, Получатель, Стоимость)
С полем AcviveX связана ячейка С4