Функция промежуточные итоги: Функция Excel Промежуточные.итоги()

Содержание

Функция Excel Промежуточные.итоги() | Что важно знать о

Функция «Промежуточные итоги()» используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами Excel (например, автофильтрами).

Для демонстрации пользы от функции Excel Промежуточные.итоги() рассмотрим урезанную выгрузку из базы мероприятий.

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

Мероприятия можно фильтровать по разным параметрам, но нам для сравнения хотелось бы понимать, показатели по отфильтрованным параметрам и сравнивать с ситуацией в целом по всем направлениям.

Для этого можно воспользоваться функцией Excel =Промежуточные.итоги(). Функция =Промежуточные.итоги() делает расчет по отфильтрованным значениям.

В формуле можно задать расчет:

Средней – номер 1 или 101 – для выборки
Счет значений – номер 3 или 103
Сумма — 9 или 109
И т.д.

Если номер функции трехзначный, например, 109, то функция работает как промежуточные итоги, т.е. рассчитывает значения по отфильтрованным параметрам.
Если номер однозначный или двузначный 2 или 11, то формула промежуточные.итоги() работает, как стандартная формула по выбранной функции.

Перейдем к нашим мероприятиям:

У нас есть выгрузка из базы по следующим столбцам:

Ситуация
Действия
Кол-во клиентов
Объём_Продаж_До
Объём_Продаж_План
Объём_Продаж_Факт
Затраты_План
Затраты_Факт

Скачайте файл с примером

Для оценки ситуации в целом введем стандартную формулу суммирования:

Для расчета промежуточных итогов по выбранным параметрам введем =ПРОМЕЖУТОЧНЫЕ.

ИТОГИ(109;R[3]C:R[65533]C), где

109 – функция суммы для расчета промежуточных итогов;
R[3]C:R[65533]C – ссылка на диапазон суммирования.

Теперь, отфильтровав столбец «Ситуация» — «Ситуация 4» и «Действие» — Действие 2 и 3, мы получим перерасчет промежуточных итогов по каждому из показателей и можем их сравнить с показателями «Итого»:

В результате: видно, что % прироста продаж по отфильтрованным мероприятиям выше, чем по всем, причем планировали результаты получить ниже, чем по итого, а получили выше. Т.е. сработали лучше, чем планировали и лучше чем по всем мероприятиям.

Формула Excel =промежуточные.итоги() – отличный инструмент для расчета показателей по отфильтрованным параметрам.

Скачайте файл с примером

Если есть вопросы — обращайтесь!

Присоединяйтесь к нам!

Скачивайте бесплатные приложения для прогнозирования и бизнес-анализа:

Novo Forecast Lite — автоматический расчет прогноза в Excel.
4analytics — ABC-XYZ-анализ и анализ выбросов в Excel.
Qlik Sense Desktop и QlikView Personal Edition — BI-системы для анализа и визуализации данных.

Тестируйте возможности платных решений:

Novo Forecast PRO — прогнозирование в Excel для больших массивов данных.

Получите 10 рекомендаций по повышению точности прогнозов до 90% и выше.

Зарегистрируйтесь и скачайте решения

Статья полезная? Поделитесь с друзьями

Добавить комментарий

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel для промежуточных расчетов

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

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

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

Суммирование только видимых ячеек в фильтре Excel

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

Рассчитать промежуточные итоги по продажам гитары марки Ibanez.

Вид исходной таблицы данных:

Используем фильтр для отбора данных, которые относятся к гитарам марки Ibanez. Для этого выделим всю таблицу или просто перейдите курсором на любую ячейку таблицы и воспользуйтесь инструментом «ДАННЫЕ»-«Фильтр». Теперь исходная таблица имеет следующий вид:

Нажмем на раскрывающийся список в столбце B («Марка товара») и установим флажок только напротив названия «Ibanez»:

После нажатия на кнопку «ОК» таблица примет следующий вид:

Как видно, некоторые строки теперь являются скрытыми. Если применить обычную функцию СУММ, будет произведен расчет для всех строк исходной таблицы:

Вместо этого в ячейке C24 будем использовать следующую функцию:

Описание аргументов:

9 – числовое значение, соответствующее использованию функции СУММ для получения промежуточных итогов;
C4:C20 – диапазон ячеек, содержащих данные о стоимости гитар (при этом все другие гитары, кроме марки Ibanez, в расчете не учитываются).

Аналогично выполним расчет для количества проданных гитар и общей выручки («Сумма). В результате получим:

Для сравнения приведем результаты, полученные с использованием обычной функцией СУММ:

Несмотря на то, что часть строк скрыта благодаря использованию фильтра, функция СУММ учитывает все строки в расчете.

Выборочное суммирование ячеек таблицы в Excel

Пример 2. Для таблицы, используемой в первом примере, получить промежуточные итоги для каждой марки товара по дате продаж.

Отключите автофильтр и выделите исходную таблицу данных из первого примера. Теперь воспользуемся инструментом «Промежуточный итог» во вкладке «Данные» на панели инструментов:

В открывшемся диалоговом окне выберем наименование столбца «Дата» в качестве критерия «При каждом изменении в:». Следующей опцией является операция, которая будет проводиться над данными. Выберем «Сумма» для суммирования значений.

Критерий «Добавить итоги по:» позволяет выбрать столбцы, для которых будет выполняться операция суммирования. Установим флажки также напротив «Заменить текущие итоги» и «Итоги под данными»:

После нажатия на кнопку «ОК» исходная таблица примет следующий вид:

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

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

Примеры формул для расчетов промежуточных итогов в таблице Excel

Пример 3. В таблице Excel представлены данные о продаже джинсов и брюк на протяжении нескольких дней с указанием размера и стоимости покупки. Определить значение моды (наиболее популярного размера) для джинсов на 23.

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

Вид исходной таблицы данных:

Отфильтруем данные с использованием критериев «джинсы» и указанная в условии дата:

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не содержит встроенных функций для расчета моды и среднего отклонения. Для расчета моды используем следующую формулу (формула массива CTRL+SHIFT+ENTER):

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

Для расчета среднего отклонения используем похожую формулу:

Результаты вычислений:

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

Особенности использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Excel

Рассматриваемая функция имеет следующую синтаксическую запись:

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

Описание аргументов:

номер_функции – обязательный для заполнения аргумент, принимающий числовые значения из диапазонов от 1 до 11 и от 101 до 111, характеризующие номер используемой функции для расчета промежуточных итогов: СРЗНАЧ, СЧЁТ, СЧЁТЗ, МАКС, МИН, ПРОИЗВЕД, СТАНДОТКЛОН, СТАНДОТКЛОНП, СУММ, ДИСП и ДИСПР соответственно. При этом функции, обозначенные числами от 1 до 11, используются тогда, когда в расчет требуется также включить строки, которые были скрыты вручную. Функции, обозначенные числами от 101 до 111, игнорируют скрытые вручную строки при расчетах. Строки, которые были скрыты в связи с применением фильтров, в расчетах не учитываются в любом случае;

ссылка1 – обязательный аргумент, принимающий ссылку на диапазон ячеек с числовыми данными, для которых требуется выполнить расчет промежуточных итогов;
[ссылка2];…] – вторая и последующие ссылки на диапазоны ячеек, для значений которых выполняется расчет промежуточных итогов. Максимальное количество аргументов – 254.

Примечания:

Если в качестве аргументов ссылка1, [ссылка2];…] были переданы диапазоны ячеек, в которые включены ячейки, содержащие промежуточные итоги, полученные с помощью функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ, они учтены не будут чтобы не повлиять на итоговый результат.

В отфильтрованной таблице отображаются только те строки, содержащиеся значения в которых удовлетворяют поставленным условиям (используемым фильтрам). Некоторые строки могут быть скрыты вручную с использованием опции «Скрыть строки». Такие строки также могут быть исключены из результата, возвращаемого функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ, если в качестве ее первого аргумента было указано число из диапазона от 101 до 111.
Основное свойство рассматриваемой функции (выполнение операций только над отфильтрованными данными) применимо только для таблиц, данные в которых фильтруются по строкам, а не по столбцам. Например, при расчете промежуточных итогов в горизонтальной таблице, в которой в результате применения фильтра были скрыты несколько столбцов, функция =ПРОМЕЖУТОЧНЫЕ. ИТОГИ(1;A1:F1) вернет среднее значение для всех величин, находящихся в диапазоне A1:F1, несмотря на то, что некоторые столбцы являются скрытыми.
Если в качестве аргументов ссылка1, [ссылка2];…] были переданы ссылки на диапазоны ячеек, находящиеся на другом листе или в другой книге Excel (такие ссылки называются трехмерными), функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ вернет код ошибки #ЗНАЧ!.

Функция

ПРОМЕЖУТОЧНЫЙ ИТОГ — служба поддержки Майкрософт

Excel

Формулы и функции

Дополнительные функции

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Дополнительно… Меньше

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

Описание

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

Синтаксис

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

Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ имеет следующие аргументы:

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

Примечания

org/ListItem»>
Если внутри ref1, ref2,… (или вложенных промежуточных итогов) есть другие промежуточные итоги, эти вложенные промежуточные итоги игнорируются во избежание двойного счета.
Для констант function_num от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ включает значения строк, скрытых Команда Скрыть строки в подменю Скрыть и показать команды Формат в группе Ячейки на вкладке Главная в настольном приложении Excel. Используйте эти константы, если вы хотите подсчитать скрытые и нескрытые числа в списке. Для констант function_Num от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ игнорирует значения строк, скрытых командой Hide Rows . Используйте эти константы, если вы хотите подытожить только нескрытые числа в списке.
org/ListItem»>
Функция ПРОМЕЖУТОЧНЫЕ ИТОГО игнорирует все строки, не включенные в результат фильтрации, независимо от того, какое значение function_num вы используете.
Функция ПРОМЕЖУТОЧНЫЕ ИТОГО предназначена для столбцов данных или вертикальных диапазонов. Он не предназначен для строк данных или горизонтальных диапазонов. Например, когда вы подсчитываете горизонтальный диапазон, используя номер_функции, равный 101 или больше, например ПРОМЕЖУТОЧНЫЙ ИТОГ (109,B2:G2), скрытие столбца не влияет на промежуточный итог. Но скрытие строки в промежуточном итоге вертикального диапазона влияет на промежуточный итог.
Если какие-либо ссылки являются трехмерными ссылками, ПРОМЕЖУТОЧНЫЙ ИТОГ возвращает #ЗНАЧ! значение ошибки.

Пример

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

Данные
120
10
150
23
Формула	Описание	Результат
=ПРОМЕЖУТОЧНЫЙ ИТОГ(9,A2:A5)	Сумма промежуточных сумм ячеек A2:A5 с использованием 9 в качестве первого аргумента.	303
=ПРОМЕЖУТОЧНЫЙ ИТОГ(1,A2:A5)	Среднее значение промежуточного итога ячеек A2:A5 с использованием 1 в качестве первого аргумента.	75,75
Примечания
Функция ПРОМЕЖУТОЧНЫЕ ИТОГО всегда требует числового аргумента (от 1 до 11, от 101 до 111) в качестве первого аргумента. Этот числовой аргумент применяется к промежуточному итогу значений (диапазоны ячеек, именованные диапазоны), которые указаны в качестве следующих аргументов.

Excel ПРОМЕЖУТОЧНЫЙ ИТОГ Примеры функций

Главная > Формулы > Сумма > Итого

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

Примечание : Microsoft Excel также имеет функцию «Промежуточные итоги», которая группирует элементы в списке и добавляет одну или несколько строк промежуточных итогов. Вы можете найти команду «Промежуточный итог» на ленте Excel, вкладка «Данные» в группе «Структура».

Субтомальная функция

Создайте быстрые субтотальные

Субтомальные аргументы

Подтотальные номера функций

Subtotal Formula

Получить файл образца

Урок

.

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

В этом видео показано, как использовать ПРОМЕЖУТОЧНЫЙ ИТОГ или более новую функцию АГРЕГАТ для работы с отфильтрованными данными.

Использование ПРОМЕЖУТОЧНЫХ ИТОГОВ с отфильтрованными списками

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

Над списком есть общее количество:

В ячейке E1 используется функция COUNT, и хотя видно только 6 заказов, количество заказов отображается как 37.
В ячейке E2 используется функция ПРОМЕЖУТОЧНЫЙ ИТОГ, которая показывает количество только видимых строк.

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

Создайте быстрый ПРОМЕЖУТОЧНЫЙ ИТОГ

Во-первых, вот быстрый способ создать формулу ПРОМЕЖУТОЧНОГО ИТОГО для суммирования сумм в списке:

Применить фильтр к списку. В этом примере столбец «Регион» фильтруется по «Запад».
В строке непосредственно под списком выберите ячейку, в которой вы хотите получить общую сумму.
На вкладке «Главная» Excel нажмите кнопку «Автосумма».
- ИЛИ, на клавиатуре нажмите клавишу Alt и коснитесь клавиши со знаком равенства (Alt + =).
- ИЛИ, щелкните стрелку для команды Автосумма и выберите одно из других имен функций, например СЧЕТЧИК ЧИСЕЛ

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

=ПРОМЕЖУТОЧНЫЙ ИТОГ(109,tblProducts[Стоимость за единицу])

ПРИМЕЧАНИЕ . Если список представляет собой отформатированную таблицу Excel, он может автоматически расшириться и поместить формулу в строку Итого. Чтобы отменить этот шаг, сразу же нажмите Ctrl+Z.

Аргументы синтаксиса ПРОМЕЖУТОЧНЫЙ ИТОГ

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

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

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

Примечания к функциям ПРОМЕЖУТОЧНЫХ ИТОГОВ

Вот несколько замечаний по использованию функции ПРОМЕЖУТОЧНЫЕ ИТОГОВ:

Если в указанном диапазоне есть другие формулы ПРОМЕЖУТОЧНЫХ ИТОГОВ, эти вложенные промежуточные итоги игнорируются. Это предотвратит дублирование сумм в общей сумме.
Функция ПРОМЕЖУТОЧНЫЕ ИТОГО не работает с трехмерными ссылками на нескольких листах. Если включены трехмерные ссылки, результатом формулы является #ЗНАЧ! ошибка
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ предназначена для подсчета итогов в столбцах, где игнорируются скрытые строки. Если вы используете функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ для подсчета итогов по строкам, она НЕ будет игнорировать суммы в скрытых столбцах.

ПРОМЕЖУТОЧНЫЙ ИТОГ Аргументы Пример

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

=ПРОМЕЖУТОЧНЫЙ ИТОГ(109,tblProducts[Unit Cost])

номер_функции : первый аргумент — это число, которое сообщает Excel , какую итоговую функцию использовать в промежуточном итоге. 109 в этом примере указывает Excel использовать функцию СУММ. См. полный список сводной функции номера в соседнем разделе
ref1 : Второй аргумент — ссылка на диапазон ячеек , которые должны быть подытожены. В этом примере он суммирует числа в столбце «Стоимость единицы» таблицы с именем tblProducts..

Номера функций промежуточного итога

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

Чтобы выбрать функцию из списка, дважды щелкните ее.

11 Функции для промежуточного итога

11 функций перечислены в алфавитном порядке, и каждая функция имеет два номера:

один в диапазоне от 1 до 11
и другой в диапазоне от 101 до 111

Функция	Номера
СРЕДНЯЯ	1 или 101
СЧЕТ	2 или 102
СЧЁТ	3 или 103
МАКС	4 или 104
МИН	5 или 105
ПРОДУКТ	6 или 106
СТДВ	7 или 107
СТДЕВП	8 или 108
СУММА	9 или 109
ВАР	10 или 110
ВАРП	11 или 111

Диапазоны номеров функций

Существует одно ключевое различие между двумя наборами номеров.

Если строки скрыты фильтром
- функции в обоих диапазонах номеров будут исключать отфильтрованные ячейки
Если строки скрыты вручную :
- функции в нижнем диапазоне номеров (1-11) будут включать эти ячейки
- функции в верхнем диапазоне номеров (101-111) будут исключать эти ячейки

Примечание : Строки, которые вы форматируете с нулевой высотой , НЕ БУДУТ включены ни в один из типов промежуточных итогов.

Гибкая ПРОМЕЖУТОЧНАЯ ИТОГО Формула

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

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

Функция промежуточные итоги: Функция Excel Промежуточные.итоги() | Что важно знать о