Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() MS EXCEL

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

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами EXCEL: Автофильтром и Промежуточными итогами. См. Файл примера.

Синтаксис функции

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

Номер_функции — это число от 1 до 11, которое указывает какую функцию использовать при вычислении итогов внутри списка. 

Например, функция СУММ() имеет код 9. Функция СУММ() также имеет код 109, т.е. можно записать формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;A2:A10) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;A2:A10). В чем различие - читайте ниже. Обычно используют коды функций от 1 до 11.

Ссылка1; Ссылка2; — от 1 до 29 ссылок на диапазон, для которых подводятся итоги (обычно используется один диапазон).

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

Важно: Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() разработана для столбцов данных или вертикальных наборов данных. Она не предназначена для строк данных или горизонтальных наборов данных (ее использование в этом случае может приводить к непредсказуемым результатам).

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Автофильтр

Пусть имеется исходная таблица.

Применим Автофильтр и отберем только строки с товаром Товар1. Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает сумму товаров Товар1, следовательно будем использовать код функции 9 или 109.

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

Если бы мы записали формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;B11:B20) или =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(103;B11:B20), то мы бы подсчитали число отобранных фильтром значений (5).

Таким образом, эта функция "чувствует" скрыта ли строка автофильтром или нет. Это свойство используется в статье Автоматическая перенумерация строк при применении фильтра.

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и Скрытые строки

Пусть имеется та же исходная таблица. Скроем строки с товаром Товар2  через меню Главная/ Ячейки/ Формат/ Скрыть или отобразить или через контекстное меню.

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() и средство EXCEL Промежуточные итоги

Пусть имеется также исходная таблица. Создадим структуру с использованием встроенного средства EXCEL - Промежуточные итоги.

Скроем строки с Товар2, нажав на соответствующую кнопку "минус" в структуре.

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

Другие функции

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

Воспользуемся той же исходной таблицей.

Применим Автофильтр и отберем только строки с товаром Товар1. Пусть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подсчитает среднее геометрическое цен товаров Товар1 (пример не очень жизненный, но он показывает принцип). Будем использовать код функции 3 - подсчет значений.

Для подсчета будем использовать формулу массива (см. файл примера, лист2)

=СРГЕОМ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(B10:B19)))-1;));B10:B19;""))

С помощью выражения СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(B10:B19)))-1 в качестве второго аргумента функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() подается не один диапазон, а несколько (равного числу строк). Если строка скрыта, то вместо цены выводится значение Пустой текст "", которое игнорируется функцией СРГЕОМ(). Таким образом, подсчитывается среднее геометрическое цен товара Товар1.