Как подвести промежуточные итоги в Google Таблицах (простое руководство)

Содержание:

SUBTOTAL — это функция в Google Таблицах, которая может быть невероятно универсальной. С правильными советами и инструментами она может быть динамичной и мощной, позволяя экспоненциально увеличивать функциональность ваших листов.

При использовании функции SUBTOTAL в Google Таблицах вы найдете различные способы ее использования и несколько приложений с отчетами.

Три основных способа её использования:

Применение различных основных функций (например, Sum, Count, Average) к списку данных.
Создание селектора отчетов для просмотра различной статистики из одного набора данных.
Расчет данных со скрытыми строками или без них.

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

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

Давайте начнем.

Google Таблицы — Функция SUBTOTAL

Синтаксис

Мы начнем с синтаксиса SUBTOTAL в Google Таблицах.

Функция SUBTOTAL требует двух или более аргументов.

Код функции.
По крайней мере, один диапазон для выполнения этой функции.

Коды функций изображены ниже и доступны в Google Таблицах в любое время. Для этого вам сначала нужно начать вводить функцию = SUBTOTAL.

Затем в разделе справки по формулам в левом нижнем углу вы увидите кнопку «Подробнее». Выберите это.

Обратите внимание: если раздел справки по формуле не отображается, появится поле с синим вопросительным знаком; щелкните по нему или нажмите F1, чтобы открыть раздел справки по формуле.

Код функции

После того, как вы нажали кнопку «Узнать больше», появится правая боковая панель; в ней вы увидите подробное объяснение каждого кода функции. Это может пригодиться, если вы забудете.

Коды функций следующие:

1 = AVERAGE
2 = COUNT
3 = COUNTA
4 = MAX
5 = MIN
6 = PRODUCT
7 = STDEV
8 = STDEVP
9 = SUM
10 = VAR
11 = VARP

Как видите, в функцию SUBTOTAL в Google Таблицах встроено 11 функций. Кроме того, вы можете указать функции игнорировать скрытые ячейки, изменив код функции. Вы используете тот же код, но за сотню. Чтобы запустить функции, игнорируя скрытые ячейки, можно использовать следующие коды:

101 = AVERAGE
102 = COUNT
103 = COUNTA
104 = MAX
105 = MIN
106 = PRODUCT
107 = STDEV
108 = STDEVP
109 = SUM
110 = VAR
111 = VARP

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

=SUBTOTAL(9; A1: A10)

Как ПОЛУЧИТЬ ПРОМЕЖУТОЧНЫЙ ИТОГ в Google Таблицах

Кто-то может спросить, зачем использовать функцию SUBTOTAL для выполнения функции SUM? Не было бы проще использовать функцию SUM? Эти вопросы вполне понятны и логичны. Однако бывают ситуации, когда функция SUBTOTAL может быть более ценной и эффективной.

Предположим, у вас есть следующий набор данных, и вы решили использовать функцию SUM вместо функции SUBTOTAL:

На приведенной ниже диаграмме вы увидите, что функция SUM находится в каждой итоговой ячейке, а общая итоговая ячейка — в нижней части таблицы.

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

Чтобы этого избежать, нам нужно изменить все функции SUM на функции SUBTOTAL. Функция SUBTOTAL не добавляет других функций промежуточных итогов; таким образом, вы получаете правильные итоги.

Google Таблицы ПРОМЕЖУТОЧНЫЙ ИТОГ для отфильтрованных или скрытых данных

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

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

Вы увидите соответствующую таблицу внизу листа.

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

Для этого выбираем раскрывающийся список зеленого фильтра в верхней части столбца «Месяц». Выберите месяц, о котором идет речь, и нажмите ОК.

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

Вы также заметите, что ячейки SUM в таблице ниже не изменились.

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

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

Как использовать функцию SUBTOTAL для создания селектора функций динамического отчета

Самый универсальный метод использования функции SUBTOTAL в Google Таблицах — это создание селектора функции отчета. Позволяет быстро изменять показатели, отображаемые в отчете, и может быть выполнено путем объединения различных функций и операций с функцией SUBTOTAL. Это относительно просто, и шаги описаны ниже.

Первым шагом в этом является создание таблицы функций для доступных функций SUBTOTAL.
- Мы будем использовать этот список для проверки данных (раскрывающийся список) и функции VLOOKUP на следующих этапах.

Затем вы создадите проверку данных, которая ссылается на этот список в ячейке, в которой вы хотите сделать свой селектор раскрывающимся.
- Для этого щелкните правой кнопкой мыши нужную ячейку, найдите Проверка данных и щелкните ее.
- Теперь вы увидите окно проверки данных. Выберите список из диапазона и введите диапазон столбца агрегации из списка, который мы создали на шаге 1.
- Теперь, когда вы создали раскрывающийся список проверки данных, вы должны увидеть стрелку в ячейке, которая позволяет дважды щелкнуть и выбрать нужный вариант.

Остальные шаги связаны с написанием функций для получения нужных нам результатов.
- Мы будем вкладывать функцию VLOOKUP в нашу функцию SUBTOTAL. Вложенность — это одновременное использование нескольких функций для получения желаемого результата. Для этого вы напишите в ячейке следующее.

=SUBTOTAL(VLOOKUP(E54,AH4:AI14,2,FALSE),F4:F44)

VLOOKUP просматривает таблицу, созданную нами на шаге 1, в диапазоне AH4: AI14. Она ищет содержимое в раскрывающейся ячейке, которую мы создали в E54. Найдя правильную строку, она ищет результат во втором столбце диапазона. Затем она вставляет это число в функцию VLOOKUP для кода функции.
Теперь создайте ячейку для функции «Игнорировать скрытые строки» и введите ниже. Это то же самое, что и выше, за исключением того, что мы добавили «100+» в раздел Vlookup, что заставит его использовать код функции игнорирования скрытых ячеек.

=SUBTOTAL(100+VLOOKUP(E54,AH4:AI14,2,FALSE),F4:F44)

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

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

=SUBTOTAL(IF(I58=TRUE,100+VLOOKUP(E58,AH4:AI14,2,FALSE),VLOOKUP(E58,AH4:AI14,2,FALSE)),F4:F44)

Заключение

Таблицы Google имеют множество полезных функций. Одна из них — функция SUBTOTAL. Она имеет очень динамичный характер и может принести пользу любой системе отчетности в Google Таблицах.