Функция «Промежуточный итог» в таблицах Google

Система подсчёта промежуточного итога в таблицах Google представляет собой расширенный функционал для работы с данными.

Существует три основных способа применения функции «Промежуточный итог»:

1. Вычисление промежуточных итогов для списков данных
2. Вычисление показателей с помощью отфильтрованных и/или скрытых данных
3. Как динамический селектор функций

Как видно, функция довольно разноплановая.

Однако она не так хорошо изучена, и поэтому, полагаю, не используется в полной мере. Функция достаточно сложная для начинающих, так как включает в себя использование кода функции для контроля её работы, о чём будет рассказано далее.

Образец подсчёта промежуточного итога

Кликните здесь, чтобы посмотреть табличку и сделать копию.

Это делается очень просто: Файл → Создать копию…

Если у вас не получается раздобыть доступа к образцу, значит имеется проблема с настройками Google Workspace вашей организации. Чтобы увидеть её, кликните по ссылке, которую затем откройте в окне инкогнито.

Теперь рассмотрим синтаксис:

Синтаксис команды «Промежуточный итог»

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(код_функции; диапазон_1; [диапазон_2; …])

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

Код функции ­– число, определяющее тип операции, которую функция «Промежуточный итог» будет выполнять с вашими данными. Например, число 9 соответствует функции СУММ.

Примерная формула промежуточного итога может быть такой:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; A1:A10)

Обратите внимание, что число 9 – первый аргумент этой функции. Это означает, что в данном конкретном примере функция SUM будет применена к диапазону A1:A10.

С «Промежуточным итогом» доступно 11 различных вариантов выполнения функций, для каждого из которых определяется, использовать или игнорировать скрытые строки данных.

Если номер кода функции находится между 1 и 11, то скрытые ряды включаются в расчёт.

Если номер кода функции находится между 100 и 111, то скрытые ряды игнорируются при вычислении.

Примечание: строки данных, прошедшие фильтрацию, никогда не вносятся в промежуточный итог, вне зависимости от кода функции.

Ниже представлены варианты, доступные для опции кода функции:

Агрегация	Описание агрегации	Код, включающий скрытые значения	Код, игнорирующий скрытые значения
Average() СРЗНАЧ()	Среднее арифметическое	1	101
Count СЧЁТ()	Количество ячеек	2	102
Counta СЧЁТЗ()	Количество заполненных ячеек	3	103
Max МАКС()	Максимальное значение	4	104
Min МИН()	Минимальное значение	5	105
Product ПРОИЗВЕД()	Произведение чисел	6	106
Standard Deviation СТАНДОТКЛОН()	Стандартное отклонение по выборке	7	107
Standard Deviation Population СТАНДОТКЛОНП()	Стандартное отклонение по генеральной совокупности	8	108
Sum СУММ()	Суммирование диапазона	9	109
Variance ДИСП()	Дисперсия по диапазону	10	110
Variance Population ДИСПР()	Дисперсия по генеральной совокупности	11	111

Использование функции «Промежуточные итоги» для создания промежуточных итогов

Предположим, что у вас есть следующий набор данных, где каждая подтаблица содержит промежуточный итог с использованием функции СУММ:

=СУММ(C2:C5)

При высчитывании общей суммы с помощью функции СУММ, вы рискуете дважды пересчитать доход.

Функция СУММ увеличивает значения дохода и промежуточные итоги. Это значит, что общая сумма будет в два раза больше, чем должна быть. Это ПЛОХО!

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

= C6 + C13 + C20

Это не подходит, потому что выбирать каждый из них утомительно и, следовательно, можно легко допустить ошибку.

Однако эта проблема решаема, если использовать функцию «Промежуточный итог» в таблицах Google.

Замените каждую из формул СУММ формулами, использующими функцию «Промежуточный итог», например,

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C5)

Когда вы вычисляете общую сумму, вновь используя функцию «Промежуточный итог», она не будет подсчитывать значения дважды. Функция «Промежуточный итог» игнорирует другие функции промежуточного итога, указанные в таблице выше:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C20)

В этот раз выдаётся правильный ответ – 51 385 долларов США.

Примечание: в основном лучше использовать сводные таблицы для анализа данных и расчёта промежуточных итогов. Они более удобны в использовании и не занимают много времени.

Использование функции «Промежуточный итог» для отфильтрованных или скрытых данных

Предположим, что у вас есть этот набор данных:

И у вас есть эти три формулы согласно данным:

=СУММ(D2:D21)

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9; D2:D21)

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109; D2:D21)

Данные, прошедшие фильтрацию

Используя фильтрующую функцию, выбор пал на ячейку «Квартира» из области недвижимости.

Формула СУММ не меняется и по-прежнему возвращает общую сумму всего набора данных.

Однако две формулы промежуточного итога обновляются и теперь показывают только общую сумму для отфильтрованного подмножества данных. Они имеют коды функций 9 и 109 соответственно, которые обозначают операцию по суммированию.

Скрытые ряды

Если также сейчас скроем несколько рядов, выделив их щелчком правой кнопки мыши и выбрав «Скрыть строки…», то вывод функции итогового промежуточного итога обновится.

Так как у него имеется код функции 109, то он сейчас также игнорирует скрытые ряды, в то время как формула с кодом функции 9 не совершает этого действия.

Примечание по скрытым столбцам: функция «Промежуточный итог» не учитывает скрытые столбцы. Если вы используете «Промежуточный итог» по строке, то он всегда включает все колонки. По этой причине он используется в списках данных, выполненных столбцами.

Создавайте динамический селектор функций с формулой промежуточного итога в таблицах Google

Используя приведенный выше список кодов функций в качестве таблицы подстановки, можно создать динамический селектор, чтобы пользователь мог выбрать определённую функцию для её применения в промежуточном итоге:

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

Выберите пустую ячейку (в данном примере C24) и перейдите в меню:

Данные → Проверка данных

Выберите раскрывающийся список и выделите имена агрегирования (Average, Count, Counta…) в качестве диапазона.

Эта формула поиска вернёт код на основе выбранного раскрывающегося списка:

=ВПР(C24;F24:G34;2;false)

Если хотите, смело используйте ИНДЕКС + ПОИСКПОЗ (или же просто ИНДЕКС) вместо ВПР. Я пользовался ВПР, так как большинство пользователей хорошо с ним знакомы.

Затем этот код можно включить в формулу промежуточного итога:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(ВПР(C24;F24:G34;2;false); D2:D21)

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

Это достигается посредством включения оператора ЕСЛИ:

=ЕСЛИ(C24="Yes";0;100)

Выдаётся ответ 0 или 100, который можно добавить к коду функции, чтобы выбрать диапазон от 1 до 11, или диапазон от 100 до 111 (см. таблицу кодов функций, указанной ранее).

Полная динамическая формула промежуточного итога приобретает следующий вид:

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(ЕСЛИ(C24="Yes";0;100) +
ВПР(C24;F24:G34;2;false); D2:D21)

Изучение функции «Промежуточный итог» также входит в содержание 27-го урока моего бесплатного 30-дневного курса «Усовершенствованные формулы».

Дополнительно об этом можно прочитать в документации Google.

Автор: Ben Collins

Перевод: редакция ExcelExcel