В этой статье продолжим разговор о функциях динамических массивов и рассмотрим, пожалуй, самую интересную из них - функцию ФИЛЬТР. С ее помощью очень просто решать задачи, для которых ранее нужно было задействовать сочетания функций, а в некоторых ситуациях применять формулы массива.
Рассмотрим работу с функцией ФИЛЬТР на примерах (скачать файл).
Замена функции ВПР
Классическая ситуация - необходимо получить информацию по определенным заказам.
Если отчет должен содержать информацию по конкретному заказу, то задача относительно легко решается с помощью функции ВПР или связки функций ИНДЕКС и ПОИСКПОЗ. Для решения можно воспользоваться вспомогательной строкой с номерами столбцов в исходной таблице.
Затем в формуле использовать эту нумерацию для возвращения нужного значения.
Такую формулу можно "протянуть" по всей строке отчета и получить необходимый результат.
Но вот если нужно получить отчет по заказам определенного покупателя, то функции ВПР и ИНДЕКС+ПОИСКПОЗ будут бессильны, так как они всегда возвращают только первое найденное совпадение.
Без использования динамических массивов задача решается весьма непростой формулой массива, и этому способу я посвятил отдельную подробную статью, однако функция ФИЛЬТР делает решение элементарным.
У функции ФИЛЬТР три аргумента.
Первый - массив, диапазон данных, который необходимо подвергнуть фильтрации. Второй - включить, определяет условия, при выполнении которых данные будут включены в итоговый массив, возвращаемый функцией. И последний аргумент - если_пусто, является необязательным. В нем указывается, что нужно вернуть, если ни одно из условий второго аргумента не выполняется. Если ничего не указать, то будет возвращена ошибка #ВЫЧИСЛ! (в англоязычной версии - #CALC!).
Нам нужно получить отфильтрованную таблицу без первого столбца, поэтому выбираем только нужный диапазон. При этом в итоговую таблицу нужно включить только те строки, которые содержат номер заказа, поэтому в качестве условия проверяем, что в первом столбце есть значение из ячейки с номером заказа.
Получим тот же результат, что и с помощью функции ВПР, но намного быстрее и проще, без преобразования ссылок в абсолютные, без создания вспомогательных строк и потягивания формулы по диапазону.
В качестве критерия мы задали не совсем привычную для многих пользователей Excel конструкцию:
Чтобы разобраться с тем, как это работает, предлагаю перейти в режим редактирования формулы, выделить условие и нажать клавишу F9.
В таком случае выделенная часть формулы заменяется вычисленным значением и мы получим результат, который и сможем проанализировать. Так как мы имеем дело с диапазоном (A2:A20), то и на выходе получим массив значений. Для каждой ячейки указанного диапазона исходной таблицы было проверено условие и возвращено соответствующее значение - ИСТИНА, если оно соблюдалось и ЛОЖЬ, если нет. В итоге мы получили массив констант и в фильтр попали только те значения, проверка в которых в результате вернула ИСТИНУ.
Чтобы вернуть преобразованное в значение условие, достаточно выйти из ячейки без сохранения, то есть нажать клавишу Esc. Если нажать Enter, то переведенные в значения части формулы так и останутся в виде массива констант и с этим нужно быть внимательным.
Итак, формула работает, но вот если указанного номера заказа в исходной таблице нет, то получим ошибку #ВЫЧИСЛ!, а в ячейках с функцией ВПР - #Н/Д.
Убрать ошибку можно дополнительной проверкой, с помощью функции ЕСЛИОШИБКА.
Но в случае с функцией ФИЛЬТР все еще проще - в ее третьем аргументе можно задать, что выводить в случае отсутствия искомых данных. Например, укажем строку - «Нет данных». Она выводится в первой ячейке динамического диапазона.
Но в некоторых ситуациях может понадобиться выводить конкретное значение для каждого столбца, поэтому в аргументах функции детализируем сообщение, указав его в качестве массива констант.
Поиск всех совпадений
Теперь решим вторую задачу - вернем все совпадения, то есть выведем полную информацию по указанному заказчику.
Фактически формула будет аналогичной, но так как в отличии от номера заказа наименование заказчика встречается в исходной таблице не один раз, то будут выведены все строки таблицы, которые соответствуют указанному критерию.
Таким образом можно очень быстро сформировать отчет на базе исходной таблицы. Да, это аналог стандартных фильтров, которые можно включить для диапазона или использовать в умных таблицах, но далеко не всегда это можно сделать, ведь исходная таблица может использоваться одновременно для нескольких задач, например, на ее основе будет создан дашборд, и поэтому ее фильтрация будет неуместна. А вот отдельный отчет на ее основе может как раз стать частью общего дашборда, при этом можно автоматизировать процесс ввода наименования заказчика, заменив его выпадающим списком.
Перед этим преобразуем исходную таблицу в умную, чтобы воспользоваться преимуществами умных таблиц.
Создать данные для списка можно с помощью рассмотренной в предыдущей статье функции УНИК.
Затем задействовать весь динамический диапазон в качестве источника данных для списка.
В результате при появлении новых заказчиков в исходной таблице будет автоматически обновляться и динамический диапазон, а значит и данные в выпадающем списке.
Теперь при создании формулы будут задействованы ссылки, характерные для умных таблиц.
Теперь новые данные будут также автоматически подтягиваться в отчет при их появлении в исходной таблице.
Кастомизация отчета
Мы получили полный аналог исходной таблицы с включенным соответствующим фильтром, но может потребоваться вставить в отчет не все столбцы, а лишь конкретные. Например, нам в отчете не нужен заказчик, так как он выбирается в выпадающем списке, да и цену с количеством товара можно убрать, оставив лишь общую сумму заказа. Ну а город можно перенести на вторую позицию и разместить после столбца с менеджерами.
Реализовать все это поможет функция ВЫБОР, которая, как мне кажется, весьма недооценена пользователями Excel. Эта функция отлично работает в связке с другими функциями, что я сейчас и хочу продемонстрировать.
Ее синтаксис прост - сначала указывается порядковый номер значения в списке (номер_индекса), а затем перечисляются значения самого списка.
Чтобы продемонстрировать, как это работает сначала создам формулу только с функцией ВЫБОР. Первый аргумент - номер_индекса, но в качестве аргумента можно использовать не одно число, а массив констант. Затем зададим список для выбора - это будут столбцы таблицы в нужном нам порядке.
В таком виде функция вернет динамический диапазон со значениями столбцов в указанном порядке.
Осталось лишь отфильтровать полученный диапазон и тут на помощь приходит функция ФИЛЬТР с ранее заданным условием. В итоге мы получили отчет только с нужной нам информацией и в нужной последовательности.
Несколько условий
Следующая задача состоит в том, чтобы создать отчет, включающий в себя данные удовлетворяющие сразу нескольким условиям. Например, выведем количество и общую сумму заказов, сделанных определенным заказчиком через определенного менеджера. Соответствующие выпадающие списки уже готовы. Они были созданы по тем же принципам, что и рассмотренные ранее.
Осталось лишь рассчитать необходимые значения. Для этого сначала с помощью функции ФИЛЬТР выведем перечень сумм заказов, удовлетворяющих двум условиям. В качестве массива будем использовать столбец с суммами заказов, а в качестве условий соответствующие диапазоны.
Как я продемонстрировал ранее, подобное условие представляет собой массив констант - ИСИНА и ЛОЖЬ, при этом в Excel они могут быть записаны цифрами - соответственно 1 и 0. Поэтому для того, чтобы одновременно применить второе условие, достаточно умножить его на первое. В итоге получим два массива нулей и единиц, которые будут между собой попарно перемножены и дадут третий результирующий массив нулей и единиц. Именно он и будет выступать критерием фильтрации.
Получим динамический массив сумм, удовлетворяющих обоим условиям и останется лишь подсчитать количество элементов в массиве, что будет равноценно количеству заказов. Сделать это можно с помощью функции СЧЁТЗ.
Ту же самую формулу можем обернуть функцией СУММ и получим итоговую сумму всех заказов, удовлетворяющих выбранным условиям.
Зависимые списки
И еще одна задача, с решением которой может помочь функция ФИЛЬТР.
Необходимо создать зависимые списки, так чтобы при выборе категории в первом, во втором отображались только те товары, которые ей соответствуют. В итоге нужно рассчитать общую сумму всех заказов по этим двум критериям.
С помощью функции УНИК создадим перечень уникальных значений для категорий.
Теперь на основе этого перечня ранее описанным способом в ячейке J5 создадим выпадающий список.
Для создания второго перечня сначала задействуем функцию ФИЛЬТР. С ее помощью выведем из столбца с товарами лишь те, которые удовлетворяют выбранной в первом выпадающем списке категории.
В результате получим список с повторяющимися значениями, а значит нужно обернуть функцию ФИЛЬТР функцией УНИК, чтобы получить список уникальных значений.
Данные для второго выпадающего списка готовы и можем его создать. Останется лишь посчитать общую сумму заказов, для чего можно применить функцию СУММЕСЛИМН.
Посчитаем значения из столбца с суммами заказов, которые будут соответствовать двум условиям - категория товара соответствует указанной в первом выпадающем списке, и сам товар соответствует указанному во втором.
Задача решена.
Ну и в заключение, в очередной раз хотел бы акцентировать ваше внимание на том, что хоть функции динамических массивов в значительной степени и упрощают многие рутинные задачи, но при их использовании не стоит забывать о том, что отсутствует их совместимость с предыдущими версиями Excel, где динамических массивов еще не было. Поэтому если создаваемый вами документ предполагается использовать на компьютерах, где установлен Microsoft Excel старше 2021-ого, то не стоит их применять. В остальных случаях функции динамических массивов окажут неоценимую помощью при создании отчетов и анализе данных.
Чтобы скачать курс по основам Microsoft Excel нажмите на эту ссылку
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм
★ Серия видеокурсов "Microsoft Excel Шаг за Шагом"
