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

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


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

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

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

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

Номер_функции (включая скрытые значения) Номер_функции (за исключением скрытых значений) Функция
1 101 СРЗНАЧ
2 102 СЧЁТ
3 103 СЧЁТЗ
4 104 МАКС
5 105 МИН
6 106 ПРОИЗВЕД
7 107 СТАНДОТКЛОН
8 108 СТАНДОТКЛОНП
9 109 СУММ
10 110 ДИСП
11 111 ДИСПР

Например, функция СУММ() имеет код 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 .


Комментарии
Только для авторизованных пользователей

Аноним, 28 февраля 2019 г.
как прописать формулу если хочу в ячейке B7 отобразился товар 1, а не среднегеметрическое значение, то есть данные со столбца А
Михаил, 5 марта 2019 г.
Вот формула =(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(B10:B19)))-1;));A10:A19;""))
Аноним, 30 мая 2019 г.
как ПРОМЕЖУТОЧНЫЕ.ИТОГИ суммировать только больше нуля?
Михаил, 1 июня 2019 г.
Самый простой вариант - создать дополнительный столбец, в котором разместить только те значения, которые >0. Это можно сделать простой формулой =(B10>$E$7)*B10. В ячейку Е7 введите критерий, т.е. 0. Можно также использовать функцию ЕСЛИ(). Далее используйте стандартную формулу =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;C10:C19). В файле примера на листе3 я сделал таблицу. Если доп.столбец неудобен, то придется написать громоздкую формулу =СУММ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ("A1:A"&ЧСТРОК(B10:B19)))-1;));B10:B19;0)*(--((B10:B19)>$E$7))) и ввести ее как формулу массива.
(только для авторизованных пользователей)

© Copyright 2013 - 2020 Excel2.ru. All Rights Reserved