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

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

  • Вычисляет метрики со скрытыми или отфильтрованными строками данных
  • Динамически выбирает функции

Это малоиспользуемая функция из-за ее хитрости. Это не совсем удобно для новичков из-за необходимости использовать элементы управления для работы. Однако, если вы будете следовать этому руководству, вы быстро научитесь свободно работать с функцией SUBTOTAL.

Что такое функциональный контроль?

Элементы управления функциями сообщают определенной функции в Google Таблицах, как себя вести. В случае функции SUBTOTAL вы можете найти SUM, AVERAGE, MAX и т. д.

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

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

Вы даже можете создать динамический селектор функций, чтобы не запоминать каждый код.

SUBTOTAL- Элементы управления функциями

Используемая формула Код (включая скрытые значения) Код (без скрытых значений)
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

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

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

Давайте посмотрим, как элементы управления функциями вписываются в синтаксис.

SUBTOTAL Синтаксис

=SUBTOTAL(function_code, range1, [range2, ...])

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

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

В качестве примера воспользуемся следующими аргументами синтаксиса.

=SUBTOTAL(9, B2:B7)

Поскольку первый аргумент функции SUBTOTAL является элементом управления, он сообщает функции, что нужно применить желаемое агрегирование. В этом примере 9 — это выбранный функциональный элемент управления.

Если вы посмотрите на приведенную выше таблицу, вы увидите, что использование 9 в синтаксисе применит функцию SUM к заданному диапазону B2: B7, включая любые скрытые значения.

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

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

Это также будет включать в расчет набор данных из A2: A7.

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

Почему вы должны использовать функцию SUBTOTAL

Может показаться, что вместо этого для расчета каждой промежуточной суммы проще использовать стандартную встроенную функцию. В конце концов, в нашем первом примере вы могли бы так же легко использовать синтаксис = SUM, (B2: B7) и все равно получить правильный ответ.

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

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

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

Вы можете избежать этого, используя синтаксис SUM функции =SUM(C9+C18+C27), чтобы получить правильный ответ. Но это гораздо более медленный и утомительный способ, чем использование функции SUBTOTAL. Также намного проще ошибиться, щелкая вручную или вводя каждое поле вручную.

Вот как это выглядит при использовании функции SUBTOTAL для того же набора данных.

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

Эта концепция применяется ко всем сопоставимым функциям управления, используемым функцией SUBTOTAL, а не только SUM.

Взаимодействие функции SUBTOTAL с отфильтрованными и скрытыми данными

Взгляните на следующий набор данных.

В нем мы использовали следующие формулы в каждой из этих ячеек.

  • C15 = SUM(C2: C13)
  • C16 = SUBTOTAL(9; C2: C13)
  • C17 = SUBTOTAL(109; C2: C13)

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

Отфильтрованные данные

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

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

Итак, убедитесь, что вы выбрали правильную функцию относительно того, хотите ли вы включать отфильтрованные данные в итоги вашей электронной таблицы.

Скрытые данные

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

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

Заключение

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

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