Промежуточные итоги и функция АГРЕГАТ / AGGREGATE

Книга: Магия таблиц. 100+ приемов ускорения работы в Excel (и немного в Google Таблицах)

Промежуточные итоги

Если мы установим фильтр, выберем определенные строки и после этого применим Автосумму (Alt + = или на ленте на вкладке «Формулы») — будет введена не функция СУММ / SUM, а ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL. Она же вводится автоматически в таблицах, о которых мы говорили выше, в строке итогов.

Эта функция позволяет производить вычисление только с видимыми строками.

У нее такой синтаксис:

=SUBTOTAL(номер функции; диапазон; [еще диапазон];…)

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

Вот базовые функции (кроме них, есть еще стандартное отклонение и дисперсия):

• 1 и 101 — среднее;

• 2 и 102 — количество чисел;

• 3 и 103 — количество значений;

• 4 и 104 — максимум;

• 5 и 105 — минимум;

• 6 и 106 — произведение;

• 9 и 109 — сумма.

Каждая функция бывает в двух вариантах — коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

Короткий вариант — подсчет всех видимых строк (отфильтрованных) и скрытых вручную (через скрытие или группировку) строк.

Длинный вариант — подсчет только отфильтрованных строк, без скрытых вручную.

Если внутри диапазона уже есть другие функции SUBTOTAL, такие вложенные подытоги не будут учитываться. То есть задвоения в таком случае не будет.

Для столбцов функция работать не будет. То есть если применить ее к горизонтальному диапазону и скрыть столбцы, то они все равно попадут в расчет при любом коде функции.

В Google Таблицах функция тоже есть, и у нее аналогичный синтаксис.

Функция АГРЕГАТ / AGGREGATE

Функция АГРЕГАТ / AGGREGATE, как и ПРОМЕЖУТОЧНЫЕ.ИТОГИ, может выполнять разные вычисления (операция тоже задается первым аргументом) и игнорировать другие функции АГРЕГАТ, что удобно, чтобы промежуточные итоги и расчеты не попадали в общую сумму. Что именно нужно пропускать, задается во втором аргументе функции АГРЕГАТ:

Назад: Таблицы Excel (Tables)

Дальше: Работа с текстом