Содержание:
При работе в 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)
Если вы посмотрите на приведенную выше таблицу, вы увидите, что использование 9 в синтаксисе применит функцию SUM к заданному диапазону B2: B7, включая любые скрытые значения.
Чтобы исключить любые скрытые значения, вам придется вместо этого использовать эквивалентный трехзначный функциональный элемент управления 109.
На следующем снимке экрана вы можете видеть, что мы добавили второй набор данных к промежуточному итогу.
Обратите внимание, что квадратные скобки «[», указанные в примере синтаксиса, не обязательны и приведут к ошибке при вводе в формулу. Они просто обозначают начало и конец диапазонов, которые вы можете ввести в синтаксис, и на самом деле не являются частью вычислений.
Почему вы должны использовать функцию SUBTOTAL
Может показаться, что вместо этого для расчета каждой промежуточной суммы проще использовать стандартную встроенную функцию. В конце концов, в нашем первом примере вы могли бы так же легко использовать синтаксис = SUM, (B2: B7) и все равно получить правильный ответ.
Хотя это правда, но для более сложных электронных таблиц лучше использовать функцию SUBTOTAL.
В следующем примере функция SUM использовалась для вычисления промежуточных и общих итогов в электронной таблице.
Вы можете избежать этого, используя синтаксис SUM функции =SUM(C9+C18+C27), чтобы получить правильный ответ. Но это гораздо более медленный и утомительный способ, чем использование функции SUBTOTAL. Также намного проще ошибиться, щелкая вручную или вводя каждое поле вручную.
Вот как это выглядит при использовании функции SUBTOTAL для того же набора данных.
Эта концепция применяется ко всем сопоставимым функциям управления, используемым функцией SUBTOTAL, а не только SUM.
Взаимодействие функции SUBTOTAL с отфильтрованными и скрытыми данными
Взгляните на следующий набор данных.
- C15 = SUM(C2: C13)
- C16 = SUBTOTAL(9; C2: C13)
- C17 = SUBTOTAL(109; C2: C13)
Как видите, каждая из этих формул дает одинаковый правильный ответ на общее количество продаж. Но когда вы начинаете фильтровать или скрывать данные, каждая формула будет по-разному взаимодействовать с электронной таблицей.
Отфильтрованные данные
В следующем примере мы отфильтровали по брендам, чтобы показать продажи только одного.
Итак, убедитесь, что вы выбрали правильную функцию относительно того, хотите ли вы включать отфильтрованные данные в итоги вашей электронной таблицы.
Скрытые данные
В последнем примере мы также добавили в набор данных скрытую строку. Обратите внимание, что цифра в ячейке C17 изменилась. Это потому, что мы использовали элемент управления 109, который игнорирует скрытые строки. То же самое применимо к скрытым столбцам.
Заключение
На первый взгляд функция SUBTOTAL может показаться немного пугающей. Тем не менее, если вы сосредоточитесь на элементах управления функциями, вы сэкономите массу времени при работе с электронными таблицами.
Если вы нашли это руководство по подсчету уникальных промежуточных итогов в Google Таблицах полезным, возможно, вам будет интересно узнать о промежуточных итогах.