Функция ФИЛЬТР в Excel и чем её заменить
ФИЛЬТР, УНИК и СОРТ работают только в Excel 2021 и 365. Разбираем их синтаксис и собираем то же самое из функций, которые есть у всех.
Коротко
ФИЛЬТР возвращает не одно значение, а сразу все подходящие строки: =ФИЛЬТР(B5:E12;C5:C12="Москва"). Результат «разливается» по соседним ячейкам сам — это и есть динамические массивы.
Их нет в Excel 2016 и Excel 2019. ФИЛЬТР, УНИК, СОРТ, СОРТПО и ПОСЛЕД появились вместе с динамическими массивами в Microsoft 365 и есть в Excel 2021 и новее. В старой версии формула покажет #ИМЯ?, а файл, отправленный коллеге, развалится у него, а не у вас.
Хорошая новость: почти всё, ради чего берут ФИЛЬТР, делается тем, что есть везде. Посмотреть отобранное — автофильтр. Сложить или посчитать отобранное — СУММЕСЛИ и СЧЁТЕСЛИ. Итог по видимым строкам — ПРОМЕЖУТОЧНЫЕ.ИТОГИ или АГРЕГАТ. Убрать повторы — «Удалить дубликаты» на вкладке «Данные». Отсортировать — обычная сортировка или НАИБОЛЬШИЙ.
То же самое формулой в Excel
=СУММЕСЛИ($C$5:$C$12;$H$4;$E$5:$E$12) — сумма по отобранному городу=СЧЁТЕСЛИ($C$5:$C$12;$H$4) — сколько строк подходит=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;$E$5:$E$12) — сумма видимых строк — работает вместе с автофильтром=АГРЕГАТ(9;5;$E$5:$E$12) — то же АГРЕГАТом: 9 — сумма, 5 — пропускать скрытые строки=НАИБОЛЬШИЙ($E$5:$E$12;1) — первая по величине сделка=СУММПРОИЗВ(($C$5:$C$12=$H$4)*($E$5:$E$12>50000)) — сколько строк подходит под два условия сразу=ФИЛЬТР(B5:E12;C5:C12=$H$4) — то же одной функцией — но только Excel 2021, 365 и Google Таблицы=УНИК(C5:C12) — список городов без повторов; в Excel 2016 это «Удалить дубликаты»Файл: отбор без функции ФИЛЬТР
Восемь сделок с автофильтром и выбором города. Восемь примеров: сумма и счёт по условию, итог по видимым строкам, АГРЕГАТ, максимум по условию и подсчёт по двум условиям. Всё открывается в Excel 2016.
Файл
filtr-bez-filtra.xlsx · формулы работают в Excel 2016 и новее
и в Google Таблицах · без макросов · формулы проходят автопроверку
перед публикацией
Пошагово
- Проверьте версию: наберите
=ФИЛЬТР(и посмотрите на подсказку. Функции нет в списке — у вас Excel 2016 или 2019, и дальше по пункту 3. - Есть —
=ФИЛЬТР(что_вернуть;условие;"ничего не найдено"). Условие — это сравнение целого диапазона:C5:C12="Москва". Место под результат оставьте пустым, он разольётся сам. - Нет ФИЛЬТРА и нужно посмотреть строки — автофильтр: выделите шапку, «Данные» → «Фильтр», выберите значение.
- Нужно сложить или посчитать отобранное — СУММЕСЛИ и СЧЁТЕСЛИ; итог по видимым строкам — ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;…) или АГРЕГАТ(9;5;…).
- Нужен список без повторов — «Данные» → «Удалить дубликаты» по копии столбца.
- Нужна сортировка — обычная кнопка сортировки или НАИБОЛЬШИЙ и НАИМЕНЬШИЙ, если сортировать надо формулой.
Где обычно ломается
#ИМЯ?у ФИЛЬТР, УНИК и СОРТ. Их нет в Excel 2016 и 2019. Проверьте не формулу, а версию.#ПЕРЕНОС!— результату некуда разлиться: в ячейках справа или снизу что-то есть. Освободите место, формула не виновата.- Файл уехал к коллеге со старым Excel. Динамические массивы у него не заработают, и виноватым окажетесь вы. Если книга уходит наружу — собирайте на СУММЕСЛИ и автофильтре.
- ПРОМЕЖУТОЧНЫЕ.ИТОГИ считает не то. Код 9 пропускает только отфильтрованное, 109 — ещё и скрытое вручную. У АГРЕГАТ то же самое вторым аргументом: 5 — пропускать скрытые строки.
- «Удалить дубликаты» правит данные. Она не показывает список без повторов, а стирает строки. Работайте на копии столбца.