Subtotal excel как пользоваться

Обновлено: 07.07.2024

Создает subtotals для диапазона (или текущего региона, если диапазон является одной ячейкой).

Синтаксис

выражения. Subtotal (GroupBy, Function, TotalList, Replace, PageBreaks, SummaryBelowData)

выражение: переменная, представляющая объект Range.

Параметры

Имя Обязательный или необязательный Тип данных Описание
GroupBy Обязательный Long Поле для группы в качестве однона основе смещения. Дополнительные сведения см. в примере.
Function Обязательный XlConsolidationFunction Подтособная функция.
TotalList Обязательный Variant Массив из 1-базирующегося поля смещений, указывающих поля, к которым добавляются подтталы. Дополнительные сведения см. в примере.
Replace Необязательный Variant True to replace existing subtotals. Значение по умолчанию — True.
PageBreaks Необязательный Variant True для добавления разрывов страниц после каждой группы. Значение по умолчанию — False.
SummaryBelowData Необязательный XlSummaryRow Помещает сводные данные относительно subtotal.

Возвращаемое значение

Пример

В этом примере создаются подтолковые для выбора на листе1. Subtotals — это суммы, сгруппировав каждое изменение в поле, а подтталы добавляются в поля два и три.

Поддержка и обратная связь

Есть вопросы или отзывы, касающиеся Office VBA или этой статьи? Руководство по другим способам получения поддержки и отправки отзывов см. в статье Поддержка Office VBA и обратная связь.

В этой статье описаны синтаксис формулы и использование функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel.

Описание

Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Синтаксис

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.

Номер_функции
(с включением скрытых значений)

Номер_функции
(с исключением скрытых значений)

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

Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.

Примечания

Если уже имеются формулы подведения итогов внутри аргументов "ссылка1;ссылка2;. " (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.

Для констант "номер_функции" от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант "номер_функции" от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы "номер_функции".

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы "номер_функции" от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

This article describes the formula syntax and usage of the SUBTOTAL function in Microsoft Excel.

Description

Returns a subtotal in a list or database. It is generally easier to create a list with subtotals by using the Subtotal command in the Outline group on the Data tab in the Excel desktop application. Once the subtotal list is created, you can modify it by editing the SUBTOTAL function.

Syntax

The SUBTOTAL function syntax has the following arguments:

Function_num Required. The number 1-11 or 101-111 that specifies the function to use for the subtotal. 1-11 includes manually-hidden rows, while 101-111 excludes them; filtered-out cells are always excluded.

Function_num
(includes hidden values)

Function_num
(ignores hidden values)

Ref1 Required. The first named range or reference for which you want the subtotal.

Ref2. Optional. Named ranges or references 2 to 254 for which you want the subtotal.

Remarks

If there are other subtotals within ref1, ref2,… (or nested subtotals), these nested subtotals are ignored to avoid double counting.

For the function_num constants from 1 to 11, the SUBTOTAL function includes the values of rows hidden by the Hide Rows command under the Hide & Unhide submenu of the Format command in the Cells group on the Home tab in the Excel desktop application. Use these constants when you want to subtotal hidden and nonhidden numbers in a list. For the function_Num constants from 101 to 111, the SUBTOTAL function ignores values of rows hidden by the Hide Rows command. Use these constants when you want to subtotal only nonhidden numbers in a list.

The SUBTOTAL function ignores any rows that are not included in the result of a filter, no matter which function_num value you use.

The SUBTOTAL function is designed for columns of data, or vertical ranges. It is not designed for rows of data, or horizontal ranges. For example, when you subtotal a horizontal range using a function_num of 101 or greater, such as SUBTOTAL(109,B2:G2), hiding a column does not affect the subtotal. But, hiding a row in a subtotal of a vertical range does affect the subtotal.

Example

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.


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

Подводная формула в Excel

Формула для функции SUBTOTAL выглядит следующим образом:


SUBTOTAL (function_num, ref1, (ref2), …) где

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

Аргумент function_num применяется на основе упомянутых ниже двух критериев или метода

ref1, (ref2): одна или несколько ссылок на ячейки, содержащие значения, которые необходимо выполнить для промежуточного итога.

Как использовать SUBTOTAL функцию в Excel?

Давайте посмотрим, как работает функция SUBTOTAL в Excel.

Вы можете скачать этот шаблон SUBTOTAL Function Excel здесь - Шаблон SUBTOTAL Function Excel

В приведенном ниже примере таблица содержит квартальные данные о продажах, где отдельные кварталы в столбце E и данные о продажах в столбце F.


Функция суммы применяется в ячейке «F16», т. Е. = SUM (F4: F15).


Возвращает или приводит к значению 1184 .


Функция SUM возвращает то же значение. т.е. 1184, вместо 194 для данных Q4, если строки скрыты фильтром (ниже упомянутый скриншот).


Если мне нужно только правильное значение SUM для данных Q4, мне нужно использовать функцию SUBTOTAL.

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

Теперь давайте рассчитаем данные продаж по Q1, Q2, Q3 или Q4 по отдельности для отфильтрованных данных.

Используйте 109 в качестве аргумента function_num в SUBTOTAL функции для суммирования данных. то есть = СУБТОТАЛЬНО (109, C17: C28)


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

Здесь значение промежуточного итога изменяется динамически в соответствии с фильтром, оно найдет сумму значений только в видимых строках; скрытые строки игнорируются функцией SUBTOTAL. (Ниже упомянуто, например)

Q4


Q3


Q2


Q1


Пример №2 - СУБТОТАЛЬНАЯ функция для скрытых строк вручную

В листе Excel строки 15, 16, 19 и 20 были скрыты с помощью параметра форматирования строки, выделив эти строки, щелкнув правой кнопкой мыши и выбрав Скрыть.


Функция промежуточной суммы используется в ячейке C23 электронной таблицы для вычисления суммы видимых ячеек.


Мы должны использовать 109 в качестве аргумента function_num в функции SUBTOTAL, чтобы суммировать данные.

то есть = СУБТОТАЛЬНО (109, C14: C21)


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


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

Пример № 3 - СУБТОТАЛЬНАЯ функция для нормального значения

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


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

В ячейке применяется СУБТОТАЛЬНАЯ функция С14, С15 и С16.


Для вычисления значения SUM используется функция № 9 или 109 в функции промежуточного итога, где она вычисляет сумму указанного диапазона, т.е. = SUBTOTAL (9, C4: C11)


Это даст нам сумму всех значений в C4: C11, при условии, что ни одно из них не будет отфильтровано и строки будут скрыты в указанном диапазоне.


Чтобы вычислить значение MIN, функция номер 5 или 105 используется в функции промежуточного итога, где она найдет наименьшее значение в указанном диапазоне. В ячейке C15 применяется функция SUBTOTAL, т.е. = SUBTOTAL (5, C4: C11)


Он найдет наименьшее значение в указанном диапазоне, то есть C4: C11


Для расчета значения MAX используется функция № 4 или 104 в функции промежуточного итога, в которой она находит наибольшее значение в указанном диапазоне. В ячейке C16 применяется функция SUBTOTAL, т.е. = SUBTOTAL (4, C4: C11)


Он обнаружит самое большое значение в указанном диапазоне, то есть C4: C11.


Что нужно помнить о СУБТОТАЛЬНОЙ функции

Рекомендуемые статьи

Это было руководство по подведению итогов. Здесь мы обсуждаем SUBTOTAL Formula и как использовать SUBTOTAL Function вместе с примером Excel и загружаемыми шаблонами Excel. Вы также можете посмотреть на эти полезные функции в Excel -

Читайте также: