Как сделать промежуточные итоги в excel

Обновлено: 04.07.2024

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

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

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

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

Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами 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 – ссылка на диапазон суммирования.

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

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

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

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

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

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

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

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

Здравствуйте, друзья. Сегодня расскажу отличный способ, как посчитать промежуточные итоги в таблице Excel, получив приятное преимущество. Изложенный здесь материал обязателен для продвинутых пользователей, поэтому, читаем до конца!

Вот пример таблицы. Здесь есть продажи менеджеров по месяцам. Для каждого менеджера указан регион, в котором находятся его клиенты.

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

Её аргументы:

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

Предусмотрены такие функции:

Функция Excel

Код, когда скрытые строки учитываются

Код, когда скрытые строки НЕ учитываются

Описание

Количество ячеек, содержащих числа

Количество непустых ячеек

Максимальное в массиве

Минимальное в массиве

Произведение всех элементов

Стандартное отклонение по выборке

Стандартное отклонение по генеральной совокупности

Сумма всех элементов

Дисперсия по выборке

Дисперсия по генеральной совокупности

Тогда формулу суммирования можно записать так:

Первым аргументом указана девятка – код функции суммирования. Вторым – все продажи в месяце.

Так в чём преимущество, если функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ выглядит сложнее? Я приведу несколько:

1. Для интерактивных таблиц, где клиент сам указывает, какой показатель вывести на экран, эта функция позволяет легко организовать изменение показателя в пределах ячейки.
2. Используется лишь одна функция, в которой достаточно изменить один код, чтобы пересчитать все итоги
3. Самое важное: функцию можно настроить на отображение итогов лишь для видимых ячеек!

В январе эта величина составила 485 шт.

Думаю, вы оценили эту возможность, т.к. большинство функций Эксель так не умеют, а функционал действительно полезный. Следует оговориться еще о нескольких особенностях и повторить уже названные:

• Коды 1-11 используются для итогов, включая скрытые строки
• Коды 101-111 применяют для получения результатов без учета скрытых
• Функция ВСЕГДА исключает из расчета строки, скрытые автофильтром
• Ф-я ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает из расчета ячейки, которые тоже содержат эту функцию
• Когда вы используете функцию для горизонтальных массивов, скрытые столбцы никогда не исключаются!

Это всё, что я планировал рассказать. Делитесь статьёй с друзьями, если она вам понравилась, а если возникли сложности – пишите комментарии!

Почему не работает промежуточный итог в Excel?

Если команда Итоги неактивна, это означает, что промежуточные итоги нельзя добавить в таблицу. . Щелкните правой кнопкой мыши ячейку в таблице, найдите пункт"Таблица", а затем выберите пункт "Преобразовать в диапазон".

Как подвести промежуточные итоги в списке?

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

Как посчитать общий итог в Excel?

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

Как создать вложенные итоги в программе MS Excel?

Как переместить итоги в сводной таблице?

Вы можете переместить таблицу, чтобы вставить ячейки, строки или столбцы на месте отчета. Щелкните сводную таблицу. В области "Средства работы со таблицами"на вкладке "Анализ" нажмите кнопку "Переместить таблицу". Отобразилось диалоговое окно "Перемещение pivotTable".

Как вычисляются промежуточные итоги?

Промежуточные итоги вычисляются с помощью итоговая функция, например Sum или Average,с помощью функции SUBTOTAL. Для каждого столбца можно отобразить несколько типов итогов. Общие итоги получаются из детали, а не из значений в сумме.

Как произвести автоматическое подведение итогов?

1. Щелкните мышью в любой ячейке исходного списка и дайте команду Данные a Итоги.
2. Выберите следующий столбец, по которому необходимо подвести итоги, из списка При каждом изменении в. .
3. Выключите режим Заменить текущие итоги в окне Промежуточные итоги и нажмите кнопку OK.

Как сделать группировку строк в Excel?

Чтобы разгруппировать строки, выделите их, а затем на вкладке Данные в группе Структура выберите команду Разгруппировать. для группы, а затем на вкладке Данные в группе Структуру нажмите кнопку Разгруппировать.

Какая команда используется для подведения промежуточных итогов?

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

Как отобразить общий итог в сводной диаграмме?

Как работает функция промежуточные итоги?

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

Как включить строку итогов?

1. Щелкните любое место таблицы.
2. Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.
3. Строка итогов будет вставлена в нижней части таблицы. .
4. Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка.

Как посчитать данные в таблице Эксель?

Как суммировать числа в Excel?

Выберем ячейку рядом с числами, которые вы хотите свести, нажмите кнопку Авто сумма на вкладке Главная и нажмите ввод , и все готово. Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

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

Синтаксис

Синтаксис функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ в Microsoft Excel:

Аргументы или параметры

номер_функции Тип создаваемого промежуточного итога, но будьте осторожны при выборе номер_функции . номер_функции может быть значением от 1 до 11, которое включает скрытые значения, или значением от 101 до 111, которое игнорирует скрытые значения при вычислении.

Значение	Пояснение (включает скрытые значения)
1	СРЗНАЧ
2	СЧЁТ
3	СЧЁТЗ
4	МАКС
5	МИН
6	ПРОИЗВЕД
7	СТАНДОТКЛОН
8	СТАНДОТКЛОНП
9	СУММ
10	ДИСП
11	ДИСПР

Значение	Пояснение (игнорирует скрытые значения)
101	СРЗНАЧ
102	СЧЁТ
103	СЧЁТЗ
104	МАКС
105	МИН
106	ПРОИЗВЕД
107	СТАНДОТКЛОН
108	СТАНДОТКЛОНП
109	СУММ
110	ДИСП
111	ДИСПР

ссылка1;[ссылка2];… Диапазоны ячеек, для которых вы хотите подвести промежуточный итог.

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ возвращает числовое значение.

Применение

• Excel для Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 для Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

Тип функции

Пример (как функция рабочего листа)

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

На основании, приведенной выше электронной таблицы Excel, будут возвращены следующие примеры функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ:

Читайте также:

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