Если ваша рабочая таблица Excel содержит скрытые строки, отфильтрованные данные или сгруппированные данные, используйте функцию SUBTOTAL. Функция SUBTOTAL может включать или исключать скрытые значения в расчетах. В дополнение к нахождению итога группы данных, Excel может рассчитать среднее, максимальное, минимальное, стандартное отклонение и дисперсию ваших данных. Вот как вставить промежуточные итоги в Excel.
Инструкции в этой статье относятся к Excel для Office 365, Excel 2019 и Excel 2016.
Синтаксис промежуточной функции
Используйте функцию SUBTOTAL в Excel, чтобы суммировать значения в рабочем листе различными способами. Это особенно полезно, когда ваша рабочая таблица содержит скрытые строки, которые вы хотите включить в расчет.
Синтаксис функции SUBTOTAL: SUBTOTAL (function_num, ref1, ref2,…)
Function_Num аргумент является обязательным и определяет тип математической операции , чтобы использовать для итога. Функция SUBTOTAL может добавлять числа, вычислять среднее значение выбранных чисел, находить максимальные и минимальные значения в диапазоне, подсчитывать количество значений в выбранном диапазоне и многое другое.
Функция SUBTOTAL игнорирует ячейки, которые не содержат данных, и ячейки с нечисловыми значениями.
Этот аргумент является числом и зависит от того, хотите ли вы включить в результат скрытые строки или исключить скрытые строки из результата. Эти строки могут быть скрыты вручную или скрыты фильтром.
Аргументы function_num включают в себя:
| Функция Задача | Function_Num | Function_Num |
|---|---|---|
| (включает скрытые значения) | (исключает скрытые значения) | |
| СРЕДНИЙ | 1 | 101 |
| COUNT | 2 | 102 |
| COUNTA | 3 | 103 |
| МАКСИМУМ | 4 | 104 |
| MIN | 5 | 105 |
| ТОВАР | 6 | 106 |
| STDEV | 7 | 107 |
| STDEVP | 8 | 108 |
| SUM | 9 | 109 |
| VAR | 10 | 110 |
| VARP | 11 | 111 |
Ссылочные аргументы function_num с 1 по 11 включают значения только в скрытых строках, когда команда Hide скрывает строки. При использовании команды «Фильтр» в промежуточных расчетах не учитываются скрытые результаты фильтрации.
Ref1 аргумент является обязательным. Это ячейки, используемые для вычисления результатов выбранного аргумента function_num. Этот аргумент может быть значением, отдельной ячейкой или диапазоном ячеек.
Ref2, … аргументы не являются обязательными. Это дополнительные ячейки, которые включены в расчет.
Используйте функцию SUBTOTAL со скрытыми строками
Функции Excel можно вводить вручную или с помощью диалогового окна «Аргументы функций». Чтобы проиллюстрировать, как ввести функцию вручную с помощью панели формул , в следующем примере используется аргумент COUNT function_num для подсчета количества значений в видимых строках, а также в видимых и скрытых строках.
Чтобы использовать функцию SUBTOTAL для подсчета количества строк на рабочем листе:
-
Начните с рабочего листа, который содержит несколько строк данных.
-
Выберите ячейку, которая будет содержать количество видимых строк.
-
В функциональной панели введите = SUBTOTAL . При вводе Excel предлагает функцию. Дважды щелкните функцию SUBTOTAL .
Чтобы использовать диалоговое окно «Аргументы функций» для ввода функции SUBTOTAL, перейдите к формулам и выберите Math & Trig > SUBTOTAL .
-
В раскрывающемся меню дважды щелкните аргумент 102 — COUNT function_num.
-
Введите запятую ( , ).
-
На листе выберите ячейки для включения в формулу.
-
Нажмите Enter, чтобы увидеть результат в ячейке, выбранной на шаге 2.
-
Выберите ячейку, которая будет содержать количество видимых и скрытых строк.
-
В функциональной панели введите = SUBTOTAL . При вводе Excel предлагает функцию. Дважды щелкните функцию SUBTOTAL .
-
В раскрывающемся меню дважды щелкните аргумент 2 — COUNT function_num, затем введите запятую ( , ).
-
На листе выберите ячейки, которые необходимо включить в формулу, и нажмите клавишу « Ввод» .
-
Скрыть несколько строк данных . В этом примере были скрыты строки только с продажами ниже $ 100 000.
Используйте функцию SUBTOTAL с отфильтрованными данными
Использование функции SUBTOTAL для отфильтрованных данных игнорирует данные в строках, которые были удалены фильтром. Каждый раз, когда меняется критерий фильтра, функция пересчитывается, чтобы показать промежуточный итог для видимых строк.
Чтобы использовать функцию SUBTOTAL для просмотра различий в результатах расчетов при фильтрации данных:
-
Создайте ПОДВОДНЫЕ формулы. Например, создайте формулы для определения промежуточных и средних значений отфильтрованных данных.
Не имеет значения, используете ли вы аргумент function_num для видимых или скрытых строк. Оба аргумента дают одинаковый результат в отфильтрованных данных.
-
Выберите любую ячейку в наборе данных.
-
Перейдите на главную страницу , затем выберите « Сортировка и фильтр» > « Фильтр» .
-
Используйте стрелки раскрывающегося списка для фильтрации данных листа .
-
Обратите внимание, как значения меняются каждый раз, когда вы выбираете разные критерии фильтрации.
Используйте функцию SUBTOTAL с сгруппированными данными
Когда данные сгруппированы, есть способ применить функцию SUBTOTAL к каждой отдельной группе, а затем вычислить общий итог для всего набора данных.
-
Выберите любую ячейку в наборе данных.
-
Выберите « Данные» > « Итог», чтобы открыть диалоговое окно « Итог ».
-
Выберите стрелку раскрывающегося списка « При каждом изменении в» и выберите группировку, для которой будет рассчитываться каждый промежуточный итог.
-
Выберите стрелку выпадающего меню Use function и выберите function_num.
-
В списке Добавить промежуточный итог выберите столбец, к которому будет применяться формула.
-
Выберите ОК .
-
Промежуточные итоги вставляются для каждой группы данных, а общая сумма вставляется внизу набора данных.
-
Чтобы изменить function_num, выделите любую ячейку в наборе данных и выберите « Данные» > « Итог» . Затем сделайте свой выбор в диалоговом окне Промежуточный итог.
