Промежуточные итоги в Excel с примерами функций

Подвести промежуточные итоги в таблице Excel можно при помощи интегрированных формул и соответственной команды в группе «Структура» на вкладке «Данные».

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

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

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

Нужно подсчитать выручку от реализации отдельных групп продуктов. Если употреблять фильтр, то можно получить однотипные записи по данному аспекту отбора. Но значения придется подсчитывать вручную. Потому воспользуемся иным инвентарем Microsoft Excel – командой «Промежуточные итоги».

Чтоб функция выдала верный итог, проверьте спектр на соответствие последующим условиям:

Таблица оформлена в виде обычного перечня либо базы данных.
1-ая строчка – наименования столбцов.
В столбцах содержатся однотипные значения.
В таблице нет пустых строк либо столбцов.

Отсортируем спектр по значению первого столбца – однотипные данные должны оказаться рядом.
Выделяем всякую ячейку в таблице. Избираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».
Заполняем диалоговое окно «Промежуточные итоги». В поле «При любом изменении в» избираем условие для отбора данных (в примере – «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.
Закрываем диалоговое окно, нажав клавишу ОК. Начальная таблица приобретает последующий вид:

Если свернуть строчки в подгруппах (надавить на «минусы» слева от номеров строк), то получим таблицу лишь из промежных итогов:

При любом изменении столбца «Заглавие» пересчитывается промежный результат в столбце «Реализации».

Чтоб за каждым промежным итогом следовал разрыв странички, в диалоговом окне поставьте галочку «Конец странички меж группами».

Чтоб промежуточные данные показывались НАД группой, снимите условие «Итоги под данными».

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

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

Формула «Промежуточные итоги» в Excel: примеры

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

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

– СРЗНАЧ (среднее арифметическое);
– СЧЕТ (количество ячеек);
– СЧЕТЗ (количество непустых ячеек);
– МАКС (наибольшее значение в спектре);
– МИН (малое значение);
– ПРОИЗВЕД (произведение чисел);
– СТАНДОТКЛОН (обычное отклонение по выборке);
– СТАНДОТКЛОНП (обычное отклонение по генеральной совокупы);
– СУММ;
– ДИСП (дисперсия по выборке);
– ДИСПР (дисперсия по генеральной совокупы).

Ссылка 1 – неотклонимый аргумент, указывающий на именованный спектр для нахождения промежных итогов.

Индивидуальности «работы» функции:

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

Разглядим на примере внедрение функции:

Создаем доп строчку для отображения промежных итогов. К примеру, «сумма отобранных значений».
Включим фильтр. Оставим в таблице лишь данные по значению «Обеденная группа «Амадис»».
В ячейку В2 введем формулу: .

Формула для среднего значения промежного итога спектра (для прихожей «Ретро»): .

Формула для наибольшего значения (для спален): .

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

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

При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
Чтоб применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Характеристики» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Жмем клавишу «Характеристики поля». В открывшемся меню избираем «остальные». Назначаем подходящую функцию для промежных итогов.
Для выведения на экран итогов по отдельным значениям используйте клавишу фильтра в правом углу наименования столбца.

Интересно почитать: Впр excel функции

В меню «Характеристики сводной таблицы» («Характеристики» — «Сводная таблица») доступна вкладка «Итоги и фильтры».

Таковым образом, для отображения промежных итогов в перечнях Excel применяется три метода: команда группы «Структура», интегрированная функция и сводная таблица.

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

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

Нужно подсчитать выручку от реализации отдельных групп продуктов. Если употреблять фильтр, то можно получить однотипные записи по данному аспекту отбора. Но значения придется подсчитывать вручную. Потому воспользуемся иным инвентарем Microsoft Excel — командой «Промежуточные итоги».

Чтоб функция выдала верный итог, проверьте спектр на соответствие последующим условиям:

Таблица оформлена в виде обычного перечня либо базы данных.
1-ая строчка — наименования столбцов.
В столбцах содержатся однотипные значения.
В таблице нет пустых строк либо столбцов.

Отсортируем спектр по значению первого столбца — однотипные данные должны оказаться рядом.
Выделяем всякую ячейку в таблице. Избираем на ленте вкладку «Данные». Группа «Структура» — команда «Промежуточные итоги».
Заполняем диалоговое окно «Промежуточные итоги». В поле «При любом изменении в» избираем условие для отбора данных (в примере — «Значение»). В поле «Операция» назначаем функцию («Сумма»). В поле «Добавить по» следует пометить столбцы, к значениям которых применится функция.
Закрываем диалоговое окно, нажав клавишу ОК. Начальная таблица приобретает последующий вид:

При любом изменении столбца «Заглавие» пересчитывается промежный результат в столбце «Реализации».

Чтоб промежуточные данные показывались НАД группой, снимите условие «Итоги под данными».

Формула «Промежуточные итоги» в Excel: примеры

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

— СРЗНАЧ (среднее арифметическое);
— СЧЕТ (количество ячеек);
— СЧЕТЗ (количество непустых ячеек);
— МАКС (наибольшее значение в спектре);
— МИН (малое значение);
— ПРОИЗВЕД (произведение чисел);
— СТАНДОТКЛОН (обычное отклонение по выборке);
— СТАНДОТКЛОНП (обычное отклонение по генеральной совокупы);
— СУММ;
— ДИСП (дисперсия по выборке);
— ДИСПР (дисперсия по генеральной совокупы).

Интересно почитать: В эксель функция если то

Ссылка 1 — неотклонимый аргумент, указывающий на именованный спектр для нахождения промежных итогов.

Индивидуальности «работы» функции:

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

Разглядим на примере внедрение функции:

Создаем доп строчку для отображения промежных итогов. К примеру, «сумма отобранных значений».
Включим фильтр. Оставим в таблице лишь данные по значению «Обеденная группа «Амадис»».
В ячейку В2 введем формулу: .

Формула для среднего значения промежного итога спектра (для прихожей «Ретро»): .

Формула для наибольшего значения (для спален): .

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

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

При формировании сводного отчета уже заложена автоматическая функция суммирования для расчета итогов.
Чтоб применить другую функцию, в разделе «Работа со сводными таблицами» на вкладке «Характеристики» находим группу «Активное поле». Курсор должен стоять в ячейке того столбца, к значениям которого будет применяться функция. Жмем клавишу «Характеристики поля». В открывшемся меню избираем «остальные». Назначаем подходящую функцию для промежных итогов.
Для выведения на экран итогов по отдельным значениям используйте клавишу фильтра в правом углу наименования столбца.

Скачать примеры с промежными итогами

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

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Веба Excel 2019 Excel 2016 Excel 2019 для Mac Excel 2013 Excel 2010 Excel 2007 Excel 2016 для Mac Excel для Mac 2011 Excel Starter 2010 Добавочно… Меньше

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

Описание

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

Синтаксис

Аргументы функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ описаны ниже.

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

(с включением укрытых значений)

(с исключением укрытых значений)

Ссылка1 Неотклонимый. 1-ый именованный спектр либо ссылка, для которых требуется вычислить промежуточные итоги.

Ссылка2;… Необязательный. Именованные спектры либо ссылки 2-254, для которых требуется вычислить промежуточные итоги.

Примечания

Если уже имеются формулы подведения итогов снутри аргументов «ссылка1;ссылка2;…» (вложенные итоги), эти вложенные итоги игнорируются, чтоб избежать двойного суммирования.

Для констант «номер_функции» от 1 до 11 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ учитывает значения строк, укрытых при помощи команды Скрыть строчки (меню Формат, подменю Скрыть либо показать) в группе Ячейки на вкладке Основная в настольном приложении Excel. Эти константы употребляются для получения промежных итогов с учетом укрытых и нескрытых чисел перечня. Для констант «номер_функции» от 101 до 111 функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ исключает значения строк, укрытых при помощи команды Скрыть строчки. Эти константы употребляются для получения промежных итогов с учетом лишь нескрытых чисел перечня.

Интересно почитать: Функция в excel степень

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

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

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

Пример

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

Excel works!

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

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

Функция промежуточные итоги в Excel имеет 2 аргумента:

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

2. Ссылка. Это тот спектр, по которому необходимо отыскать результат.

Пример. Посчитайте сумму по столбцу Сумма для всех месяцев, где продано больше 20 единиц.

Как вы видите на первой картинке, мы отобрали фильтром все значение по столбцу «Продано единиц» огромные 20. В ячейке С15 записываем формулу вида

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

Прошу не забывать, что это действие можно создать функцией =СУММЕСЛИ()

Промежуточные итоги в Excel, сумма лишь видимых ячеек

Как посчитать лишь те ячейки, которые вы не делали сокрытыми?

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

Промежуточные итоги в Excel. Сделать таблицу на отдельном листе

т.е. с промежными итогами по каждой группе строк, к примеру, по полугодиям в нашем примере.

Что необходимо создать:

1. Удостоверьтесь, что данные отсортированы, хотя бы по одному столбцу

2. Изберите спектр

3. Зайдите в ленте инструментов в меню Данные, раздел Структура и изберите Промежуточные итоги

В открывшемся меню делаем опции, избираем по какому столбцу сортироваться (Полугодие) и что созодать (Операция), а так же в каком столбце проводить расчет:

Обязано получиться как на рисунке выше.

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

— при таком методе расчета итогов Excel группирует данные (плюсы слева на панели), т.е. вы сможете скрывать излишние данные. Подробнее тут

— если используете возможность Таблица (Вставка -Таблица), в эту интерактивную таблицу при расчете суммы автоматом добавляется «Строчка итогов», которая почти во всем подменяет промежуточные итоги. В открывшейся вкладке Работа с таблицами – Конструктор вы сможете настроить интерактивную таблицу практически под любые условия. Я пока практически не использую эту возможность, т.к. таковая таблица даже на 10 тыщ строк может существенно грузить систему. Хотя в Excel я работаю до 500 тыс. строк без заморочек, даже без PowerPivot.