Функция промежуточные итоги в 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% и выше.

Промежуточные итоги в Эксель

Автор: rf-webmaestro · Опубликовано 05.04.2018 · Обновлено 08.02.2019

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

При этом есть обязательные условия:

• наименования должны повторяться больше одного раза;
• записи должны быть расположены в один столбец.

Рассмотрим каждый способ более детально.

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

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

1. Создайте таблицу, которая соответствует требованиям, описанным выше.

1. Выделите весь диапазон значений. Используйте иконку сортировки. Выберите инструмент «от А до Я».

1. Благодаря этому все клетки будут отсортированы по наименованию товара.

1. После этого сделайте активной какую-нибудь клетку (необязательно выделять всё целиком). Перейдите на вкладку «Данные». Используйте инструмент «Структура».

1. В появившемся меню выберите указанную кнопку.
2. Далее укажите нужную операцию. Чтобы продолжить, нажмите на «OK».

1. Исход будет следующим. В левой части редактора появятся дополнительные кнопки.

1. Если хотите, можете нажать на иконку «-». Благодаря этому будет происходить группировка ячеек. Вследствие данного действия останутся только результаты расчетов.

Если этот фильтр вам не нравится, вы можете вернуть всё обратно при помощи кнопок с символом «+».

Вложенность имеет несколько уровней. Можно свернуть всю информацию целиком.

Как сделать несколько результатов

Для этой цели потребуется выполнить следующие шаги.

1. Допустим, что у вас уже есть какое-нибудь подведение итогов. Мы будем использовать наш пример. Сделайте активной любую ячейку. Затем нажмите на инструмент «Структура».

1. После этого снова выбираем знакомый нам пункт.

1. Далее выбираем какую-нибудь другую операцию для расчета. Обязательно уберите галочку возле пункта «Заменить текущие итоги».

1. Только после этого можно нажать на кнопку «OK».

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

Как убрать промежуточные итоги

Процесс происходит почти так же, как при создании.

1. Выделите весь диапазон клеток. Нажмите на пункт меню «Структура».

1. Выберите указанный инструмент.

1. Кликните на кнопку «Убрать всё».

1. В результате этого редактор Эксель примет свой прежний вид.

Таким способом вы сможете удалить любое количество промежуточных подсчетов.

Расчёты при помощи функции

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

Для этого достаточно совершить несколько простых действий.

1. Перейдите на любую клетку. Нажмите на иконку «Fx». В окне «Вставка функции» выберите категорию «Полный алфавитный перечень». Найдите там нужный пункт. Кликните на кнопку «OK».

1. После этого появится окно, в котором вас попросят указать аргументы функции.

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

Обратите внимание на то, что здесь используется формула для подсчета обычной суммы. Функция СУММЕСЛИ в этом списке отсутствует.

1. В качестве примера поставим цифру 9.

1. Затем нужно кликнуть на второе поле, иначе выделенный диапазон ячеек подставится в первую строчку, а это приведет к ошибке.

1. Затем выделите какие-нибудь значения. В нашем случае посчитаем сумму продаж первого товара. В конце кликаем на «OK».

1. Результат будет следующим.

В данном случае мы посчитали сумму только для одного товара. Если хотите, можете повторить те же самые действия для каждого пункта по отдельности. Либо просто скопировать формулу и вручную исправить диапазон ячеек. Кроме этого, желательно оформить расчеты так, чтобы было понятно, какому именно товару соответствует результат вычислений.

Использование сводной таблицы Excel

Чтобы воспользоваться данной возможностью, нужно сделать следующее:

1. Укажите нужный диапазон.
2. Перейдите на вкладку «Вставка».
3. Используйте инструмент «Таблица».
4. Выберите нужный пункт.

1. Подберите что-то самое оптимальное для вас. Количество вариантов зависит от выделенной информации.
2. После этого кликните на «OK».

1. По умолчанию редактор Excel попробует сложить все значения в таблице.

Для того чтобы это изменить, нужно выполнить следующие шаги:

1. Сделайте двойной клик по заголовку указанного поля.
2. Выберите нужную вам операцию. Например, «Среднее значение».
3. Сохраняем изменения нажатием на «OK».

1. Вследствие этого вы увидите следующее:

Заключение

В данном самоучителе мы рассмотрели все основные способы создания промежуточных итогов в редакторе Эксель. Если у вас что-то не получилось, возможно, ваша информация не отвечает указанным в начале требованиям.

Видеоинструкция

Если у вас возникли какие-нибудь трудности, попробуйте посмотреть приложенное ниже видео. Там рассмотрены различные нюансы с дополнительными комментариями.

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

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

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

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

Скачать заметку в формате Word или pdf, примеры в формате Excel

ПРОМЕЖУТОЧНЫЕ.ИТОГИ может выполнить 11 операций. Первый аргумент функции указывает ей на следующие операции: (1) СРЗНАЧ, (2) СЧЁТ, (3) СЧЁТЗ, (4) МАКС, (5) МИН, (6) ПРОИЗВЕД, (7) СТАНДОТКЛОН, (8) СТАНДОТКЛОНП, (9) СУММ, (10) ДИСП, (11) ДИСПР. При добавлении сотни выполняются те же операции, но только над видимыми ячейкам. Например, 104 найдет максимум среди видимых ячеек. Под видимыми имеется ввиду, не видимые на экране (например, 120 строк не уместятся на экране), а не скрытые, командой Скрыть.

В ячейке Е566 (см. рис. 1) используется формула =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(109;E2:E564). Excel возвращает сумму только видимых (не скрытых) ячеек в диапазоне, а именно – Е2;Е30;Е72;Е78;Е564.

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

Рис. 2. Формула не игнорирует ячейки в скрытых столбцах

Дополнительные сведения: существует необычное исключение в поведении функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Когда строки были скрыты по какой-либо из команд фильтра (расширенный фильтр, автофильтр или фильтр), Excel суммирует только видимые строки даже в варианте ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон). Нет необходимости использовать версию 109 (рис. 3). Здесь фильтр используется для поиска записей Chevron.

Рис. 3. Достаточно аргумента 9 если строки скрыты в результате применения фильтра

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

1. Выбрать любую ячейку в вашем наборе данных.
2. Пройдите по меню ДАННЫЕ –>Фильтр (или нажмите Alt + Ы, а затем не отпуская Alt, нажмите Ф; или нажмите Ctrl+Shift+L). Excel добавляет фильтр (выпадающее меню) для всех заголовков столбцов.
3. Откройте одно из выпадающих меню, например, Customer. Снимите флажок Выделить все, а затем выберите одного клиента. В нашем примере – Chevron.
4. Выберите ячейки непосредственно под отфильтрованными данными. В нашем примере –ячейки Е565:H565.
5. Нажмите клавиши Alt+= или щелкните значок Автосумма (меню ГЛАВНАЯ). Вместо того, чтобы использовать СУММ, Excel применит функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;диапазон), которая просуммирует только строки, выбранные фильтром (см. рис. 3).

В Excel 2010 появилась еще одна подобная функция – АГРЕГАТ (подробнее см. Сравнение массивов и выборки по одному или нескольким условиям; раздел Функция АГРЕГАТ). Она имеет больше функций в своем «репертуаре» и больше опций, какие строки исключать, а какие обрабатывать. Основное ее достоинство – обработка ошибочных значений (например, #ДЕЛ/0!). К сожалению, эта функция также не применима к суммированию видимых столбцов.

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

Промежуточные итоги в Excel

Доброго времени суток друзья!

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

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

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

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

А теперь давайте на созданном примере посмотрим поэтапно, как это осуществить:

1. Первым делом нам нужно правильно отсортировать наши данные, для которых мы будем подводить итог. Мы выбираем нужные нам данные, по которым будем создавать промежуточный итог и предварительно выделив всю таблицу, производим сортировку по указанному столбику от меньшего к большему.
2. Вторым этапом будет выбор на вкладке «Данные», в группе «Структура», команду «Промежуточный итог».
3. В диалоговом окне «Промежуточные итоги», во-первых, вам нужно в списке поля «При каждом изменении в» выбрать столбик, для которого создаем промежуточные итоги, во-вторых, в списке поля «Операция» мы выбираем какой тип функции нам нужен (количество, сумма и т.п.), в-третьих, в поле «Добавить итоги» вы определяете столбик по которому будет формироваться итог. После этого вы с чистой совестью вы нажимаете кнопочку «ОК».
4. Данные на вашем листе автоматически будут сгруппированы по признаку, который вы установили, и под каждой группой будет создан промежуточный итог.

Работа со сгруппированными данными

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

Нажимая на иконки группирования данных, вы скрываете и отображаете ту структуру данных, которая вам наиболее актуальна на данный момент. К примеру, можно отобразить только общий итог, отобразить итоги по группам данных и полный разворот данных в деталях. Также, всё это можно проделать, используя пиктограммы «Скрыть детали» или «Показать».

Убрать промежуточные итоги

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

1. Открыть вкладку на панели управления «Данные» и в блоке «Структура» нажимаете на пиктограмму «Промежуточный итог».
2. В появившемся диалоговом окне «Промежуточные итоги» нажимаем кнопку «Убрать всё».
3. После нажатия все ваши данные разгруппируются, а промежуточные итоги будут удалены.

Если же вам всё-таки промежуточные итоги нужно оставить, а вот группирование не нужно, вы можете в панели задач на вкладке на вкладке «Данные» в выпадающем меню кнопки «Разгруппировать» выбрать пункт «Удалить структуру», структура группировки будет удалена, оставив вам на прощание промежуточные итоги.

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Не забудьте поблагодарить автора!

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

Вычисление промежуточных итогов в Excel с примерами

Функция вычисления промежуточных итогов в Excel – это простой и удобный способ анализа данных определенного столбца или таблицы.

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

Кстати, чтобы эффективнее работать с таблицами можете ознакомиться с нашим материалом Горячие клавиши Excel — Самые необходимые варианты.

Листы документа Excel содержат большое количество информации. Часто просматривать и искать данные неудобно.

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

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

Обобщить несколько групп листа можно с помощью функции «Промежуточные итоги».

Рассмотрим детальнее принцип работы этой функции и её реализацию во всех версиях табличного процессора Excel.

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

В программе Excel опция отображается в виде ПРОМЕЖУТОЧНЫЕ.ИТОГИ (№; ссылка 1; ссылка 2; ссылка3;…;ссылкаN), где номер – обозначение функции, ссылка – столбец, по которому подводится итог.

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

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

Синтаксис опции итогов указан в таблице: