Функция ФИЛЬТР динамических массивов в Excel

Это перевод четвертой главы книги Bill Jelen. Excel Dynamic Arrays Straight to the Point. К содержанию.

Фильтрация по одному условию

Функция Фильтр() принимает массив в качестве аргумента, выделяет строки в соответствии с условиями, и возвращает результат в диапазон, разливающийся вправо и вниз от ячейки с формулой.

Рис. 20. Исходные данные

Скачать заметку в формате Word или pdf, примеры в формате Excel

Синтаксис функции:

Рис. 21. Синтаксис функции Фильтр()

Допустим, вы хотите представить только игроков из команды Red. Вводим Red в F1. Тогда формула в ячейке E4: =ФИЛЬТР(A4:C16;B4:B16=F1;"Нет данных"). Обратите внимание, что вам не нужно использовать в формуле клавишу F4 или знаки доллара. Хотя формула возвращает несколько результатов, она одна, и ее не нужно протягивать.

Рис. 22. Одна формула возвращает всех игроков команды Red

Изменяя значение в ячейке F1, вы измените содержание диапазона Е3:G10. Необязательный третий аргумент функции Фильтр() пригодится, если найти требуемые значения в исходном диапазоне не удалось. Если же третий аргумент отсутствует, функция вернет ошибку #ВЫЧИСЛ!

Рис. 23. Укажите в третьем аргументе, что отражать, если ничего не найдено

Если требуется, чтобы все поля в динамическом диапазоне были заполнены, когда ничего не найдено, используйте константу массива в качестве третьего аргумента: =ФИЛЬТР(A4:C16;B4:B16=F1;{"Не найдено";"Нет команды";0})

Рис. 24. Константа массива в качестве третьего аргумента

Функция Фильтр() с несколькими условиями

Допустим, вы хотите отфильтровать список по двум критериям, заключите оба элемента в круглые скобки и перемножьте их:

Рис. 25. Несколько условий для фильтрации

Если же вам нужно, чтобы истинным было хотя бы одно из нескольких условий, используйте сложение:

Рис. 26. Фильтрация, когда хотя бы одно из условий истинно