Функции FILTER, SORT и UNIQUE в Google Таблицах и Excel

Короткий ответ

FILTER, SORT и UNIQUE — формулы динамических массивов. Вы пишете формулу в одну ячейку, а результат сам «разливается» на нужное число строк и столбцов. Когда в исходной таблице появляются новые строки, отчёт пересчитывается автоматически.

• FILTER — оставляет строки, которые подходят под условия.
• SORT — сортирует диапазон по одному или нескольким столбцам.
• UNIQUE — возвращает список без повторов.

В Google Таблицах функции есть давно, в Excel — в Microsoft 365 и Excel 2021 и новее. В русской версии Excel они называются ФИЛЬТР, СОРТ и УНИК, а аргументы обычно разделяются точкой с запятой.

Во всех примерах ниже лист заказов: A — дата, B — клиент, C — город, D — менеджер, E — сумма, F — статус.

FILTER: живой отчёт по условию

Все оплаченные заказы:

=FILTER(A2:F100; F2:F100="Оплачен")

Несколько условий «И». Умножайте условия друг на друга — это работает и в Excel, и в Google Таблицах. Сумма в ячейке I1:

=FILTER(A2:F100; (F2:F100="Оплачен")*(E2:E100>=I1))

В Google Таблицах условия можно перечислить и отдельными аргументами: =FILTER(A2:F; F2:F="Оплачен"; E2:E>=I1).

Условия «ИЛИ». Складывайте условия:

=FILTER(A2:F100; (C2:C100="Ташкент")+(C2:C100="Самарканд"))

Пустой результат. Если ничего не найдено, Excel вернёт ошибку #CALC!, а Google Таблицы — #N/A. В Excel используйте третий аргумент, в Таблицах — IFNA:

Excel:   =FILTER(A2:F100; F2:F100="Оплачен"; "Нет данных")
Sheets:  =IFNA(FILTER(A2:F; F2:F="Оплачен"); "Нет данных")

Чтобы менеджер выбирал условие сам, ссылайтесь не на текст, а на ячейку с выпадающим списком.

SORT: порядок без ручной сортировки

Синтаксис различается: в Google Таблицах направление задаётся TRUE или FALSE, в Excel — 1 или −1.

Sheets:  =SORT(A2:F100; 5; FALSE)
Excel:   =SORT(A2:F100; 5; -1)

Обе формулы сортируют заказы по сумме (пятый столбец диапазона) по убыванию.

Сортировка по нескольким столбцам — сначала город по алфавиту, затем сумма по убыванию:

Sheets:  =SORT(A2:F100; 3; TRUE; 5; FALSE)
Excel:   =SORTBY(A2:F100; C2:C100; 1; E2:E100; -1)

В Excel для нескольких ключей удобнее SORTBY (в русской версии СОРТПО): после диапазона идут пары «столбец сортировки — направление».

UNIQUE: список клиентов без повторов

=UNIQUE(B2:B100)

Если в диапазоне есть пустые ячейки, в результат попадёт пустое значение (в Excel — 0). Уберите их фильтром:

=UNIQUE(FILTER(B2:B100; B2:B100<>""))

Количество уникальных клиентов — =COUNTA(UNIQUE(FILTER(B2:B100; B2:B100<>""))).

Третий аргумент UNIQUE со значением TRUE оставляет только значения, которые встречаются ровно один раз, — например, клиентов с одним заказом:

=UNIQUE(B2:B100; FALSE; TRUE)

Комбинации

Отсортированный список клиентов из Ташкента, у которых есть оплаченные заказы:

=SORT(UNIQUE(FILTER(B2:B100; (C2:C100="Ташкент")*(F2:F100="Оплачен"))))

Читайте формулу изнутри наружу: отобрать, убрать повторы, отсортировать.

Сводка «клиент — сумма» в Excel: в H2 список клиентов =SORT(UNIQUE(B2:B100)), в I2 сумма по каждому =SUMIF(B2:B100; H2#; E2:E100). Знак # ссылается на весь разлитый диапазон, и сводка растёт вместе со списком.

Частые ошибки

• #SPILL! в Excel или #REF! в Google Таблицах — ячейки, куда должен разлиться результат, заняты. Очистите место под формулой.
• Разные размеры диапазонов. Диапазон данных и диапазоны условий должны начинаться и заканчиваться на одних строках.
• Числа, сохранённые как текст. Условие E2:E100>=I1 не сработает для текстовых сумм — сначала приведите данные к числам.
• Ручные правки в результате. Внутри разлитого диапазона ничего не вводите: это сломает формулу.
• Целые столбцы в Excel. Ссылки вида B:B работают, но замедляют книгу; лучше оформить данные как умную таблицу.