Функция ФИЛЬТР динамических массивов в 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. Фильтрация, когда хотя бы одно из условий истинно

Перед фильтрацией массив был отсортирован по трем столбцам.

 

2 комментария для “Функция ФИЛЬТР динамических массивов в Excel”

  1. Виктор Косенков

    Сергей Викторович, спасибо за новый перевод интересной и полезной книги.
    В этой главе отсутствует ссылка на примеры в формате Excel.
    Методом дедукции удалось определить, что ссылка на файл должна выглядеть примерно так:
    https://baguzin.ru/wp/wp-content/uploads/2020/09/Funktsiya-FILTR-dinamicheskih-massivov-v-Excel.-Primery.xlsx
    С уважением, Виктор Косенков.

Добавить комментарий

Ваш адрес email не будет опубликован.