Как использовать функцию ПРОМЕЖУТОЧНЫЙ ИТОГ в Excel -

Резюме

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

Цель

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

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

Число, представляющее определенный вид промежуточных итогов.

Синтаксис

= ПРОМЕЖУТОЧНЫЙ ИТОГ (номер_функции; ссылка1; (ссылка2);…)

Аргументы

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

Версия

Excel 2003

Примечания по использованию

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

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

Примеры

Ниже приведены несколько примеров SUBTOTAL, настроенных для SUM, COUNT и AVERAGE значений в диапазоне. Обратите внимание, единственное отличие - это значение, используемое для аргумента function_num :

=SUBTOTAL(109,range) // SUM =SUBTOTAL(103,range) // COUNT =SUBTOTAL(101,range) // AVERAGE

Доступные расчеты

Поведение SUBTOTAL контролируется аргументом function_num , который предоставляется как числовое значение. Доступно 11 функций, каждая с двумя вариантами, как показано в таблице ниже. Обратите внимание, что значения являются «парными» (например, 1-101, 2-102, 3-103 и так далее). Это связано с тем, как SUBTOTAL обрабатывает скрытые вручную строки. Когда function_num находится между 1-11, SUBTOTAL включает ячейки, которые были скрыты вручную. Когда function_num находится между 101-111, SUBTOTAL исключает значения в строках, которые были скрыты вручную.

Функция	Включить скрытые	Игнорировать скрытый
СРЕДНИЙ	1	101
COUNT	2	102
COUNTA	3	103
МАКСИМУМ	4	104
MIN	5	105
ТОВАР	6	106
СТАНДОТКЛОН	7	107
STDEVP	8	108
СУММ	9	109
VAR	10	110
VARP	11	111

Примечание. SUBTOTAL всегда игнорирует значения в ячейках, скрытых фильтром. Значения в строках, которые были «отфильтрованы», никогда не включаются, независимо от function_num .

ПРОМЕЖУТОЧНЫЙ ИТОГ в таблицах Excel

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

ПРОМЕЖУТОЧНЫЙ ИТОГ с очертаниями

В Excel есть функция промежуточных итогов, которая автоматически вставляет формулы ПРОМЕЖУТОЧНЫЙ ИТОГ в отсортированные данные. Вы можете найти эту функцию в Data> Outline> Subtotal. В формулах ПРОМЕЖУТОЧНОГО ИТОГА, вставленных таким образом, используются стандартные номера функций 1-11. Это позволяет отображать промежуточные итоги, даже если строки скрыты и отображаются при свертывании и развертывании структуры.

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

Заметки

Когда function_num находится между 1-11, SUBTOTAL включает значения, которые скрыты
Когда function_num находится между 101-111, SUBTOTAL исключает значения, которые скрыты
В отфильтрованных списках SUBTOTAL всегда игнорирует значения в скрытых строках, независимо от function_num .
SUBTOTAL игнорирует другие формулы SUBTOTAL, которые существуют в ссылках, чтобы предотвратить двойной подсчет.
SUBTOTAL предназначен для работы с вертикальными значениями данных, расположенными вертикально. В горизонтальные диапазоны всегда включаются значения в скрытых столбцах.