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

Пример использования функции =ПРОМЕЖУТОЧНЫЕ.ИТОГИ Google-таблицы

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

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

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

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

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

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

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

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

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

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

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

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

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

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

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

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

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

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

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

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

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

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

АгрегацияОписание агрегацииКод, включающий скрытые значенияКод, игнорирующий скрытые значения
Average()
СРЗНАЧ()
Среднее арифметическое1101
Count
СЧЁТ()
Количество ячеек2102
Counta
СЧЁТЗ()
Количество заполненных ячеек3103
Max
МАКС()
Максимальное значение4104
Min
МИН()
Минимальное значение5105
Product
ПРОИЗВЕД()
Произведение чисел6106
Standard Deviation
СТАНДОТКЛОН()
Стандартное отклонение по выборке7107
Standard Deviation Population
СТАНДОТКЛОНП()
Стандартное отклонение по генеральной совокупности8108
Sum
СУММ()
Суммирование диапазона9109
Variance
ДИСП()
Дисперсия по диапазону10110
Variance Population
ДИСПР()
Дисперсия по генеральной совокупности11111

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

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

=СУММ(C2:C5)
Функция «Промежуточный итог» в таблицах Google

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

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

Рекомендуем курс Excel по анализу данных от Skypro — очень глубокое и яркое погружение в Эксель.

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

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

= C6 + C13 + C20

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

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

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C5)
Функция «Промежуточный итог» в таблицах Google

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C2:C20)
Функция «Промежуточный итог» в таблицах Google

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

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

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

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

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

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

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

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

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

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

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

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

Скрытые ряды

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

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

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

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

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

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

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

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

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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

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

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

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

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

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

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

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

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

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

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

=ЕСЛИ(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

Узнайте Excel как свои пять пальцев на курсе по таблицам от Skillbox

Оцените статью, пожалуйста
Добавить комментарий