Как вычисляются промежуточные итоги кратко
Обновлено: 04.07.2024
Подведение промежуточных итогов – это удобная и наглядная возможность провести обработку записей списка для последующего анализа, создания диаграмм и вывода на печать.
При этом в список автоматически вставляются дополнительные строки, отражающие итоговые значения по выбранным ячейкам. При создании промежу точных итогов необходимо, чтобы в списке первая строка содержа ла название столбцов, а остальные строки — однотипные данные.
Пустые строки должны отсутствовать, а данные должны быть от сортированы. Промежуточные итоги вычисляются с помощью ито говой функции (как правило, СУММА или СРЕДНЕЕ , с использо ванием функции ПРОМЕЖУТОЧНЫЕ ИТОГИ ).
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for 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.
Описание
Возвращает промежуточный итог в список или базу данных. Обычно проще создать список с промежуточными итогами, используя в настольном приложении Excel команду Промежуточные итоги в группе Структура на вкладке Данные. Но если такой список уже создан, его можно модифицировать, изменив формулу с функцией ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
Синтаксис
Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.
Номер_функции — обязательный аргумент. Число от 1 до 11 или от 101 до 111, которое обозначает функцию, используемую для расчета промежуточных итогов. Функции с 1 по 11 учитывают строки, скрытые вручную, в то время как функции с 101 по 111 пропускают такие строки; отфильтрованные ячейки всегда исключаются.
Function_num
(включая скрытые строки)
Function_num
(игнорирует скрытые строки)
Ссылка1 Обязательный. Первый именованный диапазон или ссылка, для которых требуется вычислить промежуточные итоги.
Ссылка2;. Необязательный. Именованные диапазоны или ссылки 2—254, для которых требуется вычислить промежуточные итоги.
Примечания
Если уже имеются формулы подведения итогов внутри аргументов "ссылка1;ссылка2;. " (вложенные итоги), эти вложенные итоги игнорируются, чтобы избежать двойного суммирования.
Для констант "номер_функции" от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, скрытых с помощью команды Скрыть строки (меню Формат, подменю Скрыть или отобразить) в группе Ячейки на вкладке Главная в настольном приложении Excel. Эти константы используются для получения промежуточных итогов с учетом скрытых и нескрытых чисел списка. Для констант "номер_функции" от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, скрытых с помощью команды Скрыть строки. Эти константы используются для получения промежуточных итогов с учетом только нескрытых чисел списка.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает все строки, не включенные в результат фильтра, независимо от используемого значения константы "номер_функции".
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ применяется к столбцам данных или вертикальным наборам данных. Она не предназначена для строк данных или горизонтальных наборов данных. Так, при определении промежуточных итогов горизонтального набора данных с помощью значения константы "номер_функции" от 101 и выше (например, ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;B2:G2)), скрытие столбца не повлияет на результат. Однако на него повлияет скрытие строки при подведении промежуточного итога для вертикального набора данных.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Функция "Промежуточные итоги()" используется для вычисления промежуточного итога (сумма, среднее, количество значений и т.д.) в диапазоне, в котором имеются скрытые сроки.
Особенность функции состоит в том, что она предназначена для использования совместно с другими средствами 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% и выше.
Google-таблицы
Система подсчёта промежуточного итога в таблицах Google представляет собой расширенный функционал для работы с данными.
- Вычисление промежуточных итогов для списков данных
- Вычисление показателей с помощью отфильтрованных и/или скрытых данных
- Как динамический селектор функций
Как видно, функция довольно разноплановая.
Однако она не так хорошо изучена, и поэтому, полагаю, не используется в полной мере. Функция достаточно сложная для начинающих, так как включает в себя использование кода функции для контроля её работы, о чём будет рассказано далее.
Образец подсчёта промежуточного итога
Это делается очень просто: Файл → Создать копию…
Если у вас не получается раздобыть доступа к образцу, значит имеется проблема с настройками Google Workspace вашей организации. Чтобы увидеть её, кликните по ссылке, которую затем откройте в окне инкогнито.
Теперь рассмотрим синтаксис:
Для этого требуется два или более аргументов: сначала код функции, затем, по крайней мере, один диапазон данных для работы.
Примерная формула промежуточного итога может быть такой:
Обратите внимание, что число 9 – первый аргумент этой функции. Это означает, что в данном конкретном примере функция SUM будет применена к диапазону A1:A10.
Если номер кода функции находится между 1 и 11, то скрытые ряды включаются в расчёт.
Если номер кода функции находится между 100 и 111, то скрытые ряды игнорируются при вычислении.
Примечание : строки данных, прошедшие фильтрацию, никогда не вносятся в промежуточный итог, вне зависимости от кода функции.
Ниже представлены варианты, доступные для опции кода функции:
| Агрегация | Описание агрегации | Код, включающий скрытые значения | Код, игнорирующий скрытые значения |
| Average() СРЗНАЧ() | Среднее арифметическое | 1 | 101 |
| Count СЧЁТ() | Количество ячеек | 2 | 102 |
| Counta СЧЁТЗ() | Количество заполненных ячеек | 3 | 103 |
| Max МАКС() | Максимальное значение | 4 | 104 |
| Min МИН() | Минимальное значение | 5 | 105 |
| Product ПРОИЗВЕД() | Произведение чисел | 6 | 106 |
| Standard Deviation СТАНДОТКЛОН() | Стандартное отклонение по выборке | 7 | 107 |
| Standard Deviation Population СТАНДОТКЛОНП() | Стандартное отклонение по генеральной совокупности | 8 | 108 |
| Sum СУММ() | Суммирование диапазона | 9 | 109 |
| Variance ДИСП() | Дисперсия по диапазону | 10 | 110 |
| Variance Population ДИСПР() | Дисперсия по генеральной совокупности | 11 | 111 |
Предположим, что у вас есть следующий набор данных, где каждая подтаблица содержит промежуточный итог с использованием функции СУММ:
При высчитывании общей суммы с помощью функции СУММ, вы рискуете дважды пересчитать доход.
Функция СУММ увеличивает значения дохода и промежуточные итоги. Это значит, что общая сумма будет в два раза больше, чем должна быть. Это ПЛОХО!
Это не подходит, потому что выбирать каждый из них утомительно и, следовательно, можно легко допустить ошибку.
В этот раз выдаётся правильный ответ – 51 385 долларов США.
Примечание: в основном лучше использовать сводные таблицы для анализа данных и расчёта промежуточных итогов. Они более удобны в использовании и не занимают много времени.
Предположим, что у вас есть этот набор данных:
И у вас есть эти три формулы согласно данным:
Данные, прошедшие фильтрацию
Формула СУММ не меняется и по-прежнему возвращает общую сумму всего набора данных.
Однако две формулы промежуточного итога обновляются и теперь показывают только общую сумму для отфильтрованного подмножества данных. Они имеют коды функций 9 и 109 соответственно, которые обозначают операцию по суммированию.
Скрытые ряды
Так как у него имеется код функции 109, то он сейчас также игнорирует скрытые ряды, в то время как формула с кодом функции 9 не совершает этого действия.
Создавайте динамический селектор функций с формулой промежуточного итога в таблицах Google
Используя приведенный выше список кодов функций в качестве таблицы подстановки, можно создать динамический селектор, чтобы пользователь мог выбрать определённую функцию для её применения в промежуточном итоге:
Для начала создайте раскрывающийся список из списка методов агрегирования в таблице кодов функций, используя проверку данных.
Выберите пустую ячейку (в данном примере C24) и перейдите в меню:
Выберите раскрывающийся список и выделите имена агрегирования (Average, Count, Counta…) в качестве диапазона.
Эта формула поиска вернёт код на основе выбранного раскрывающегося списка:
Если хотите, смело используйте ИНДЕКС + ПОИСКПОЗ (или же просто ИНДЕКС) вместо ВПР. Я пользовался ВПР, так как большинство пользователей хорошо с ним знакомы.
Затем этот код можно включить в формулу промежуточного итога:
Есть возможность также добавить еще один раскрывающийся список, чтобы пользователь мог самостоятельно определиться: включать или игнорировать скрытые ряды:
Это достигается посредством включения оператора ЕСЛИ:
Выдаётся ответ 0 или 100, который можно добавить к коду функции, чтобы выбрать диапазон от 1 до 11, или диапазон от 100 до 111 (см. таблицу кодов функций, указанной ранее).
Полная динамическая формула промежуточного итога приобретает следующий вид:
Доброго времени суток друзья!
Эту статью я посвящаю такой возможности, как создать промежуточные итоги в Excel. Если вы даже слышали или знаете, что это такое, всё же будет полезно прочитать или послушать мою информацию. По большому счёту, промежуточные итоги – это автоматическое подведение итогов по заданному критерию и группирование их для удобства и хорошей визуализации данных.
Это достаточно полезный и удобный инструмент работы в электронных таблицах, который вам пригодится, а для большего удобства я советую использовать эти итоги в тандеме с группированием ячеек, это вообще супер сила. Также я прошу обратить ваше внимание, что перед работой с итогами, необходимо в обязательном порядке произвести правильную сортировку ваших данных.
Итак, давайте же более детально рассмотрим, что это за итоги, как их создать, как с ними работать, ну и собственно, как их убрать, если нужда в этой возможности отпадёт. И начнём все наши исследования сначала, а точнее с того, как можно создать промежуточный итог.
Как сделать промежуточные итоги в Excel
А теперь давайте на созданном примере посмотрим поэтапно, как это осуществить:
Работа со сгруппированными данными
После проведенных манипуляций, работа с данными значительно упростится. Данные можно удобно визуализировать, используя группировку данных и создавать столько уровней, сколько вам будет удобно, но, увы, не более 8 вложенных, с опыта могу сказать, что этого хватит за глаза.
Убрать промежуточные итоги
В случае, когда промежуточные итоги в Excel станут, не нужны, ну или потребуется перегруппировка по-новому ваших данных в таблице, в этом случае итоги можно удалить без потери качества исходных данных. Для этого нужно:
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!
Не забудьте поблагодарить автора!
Пока богатство еще не приобретено, стремление к нему изнуряет, будучи же приобретено, оно изводит заботами, когда же оно утрачено, мучает тоска по нем. Демокрит
Читайте также: