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

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

Требования к таблицам для подведения промежуточных итогов

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

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

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

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

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

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

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

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

После этого непосредственно начинаем работу:

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

   

2. После этого осуществляем случайный выбор любой ячейки. Далее смотрим на ленту. Интересующая нас вкладка называется «Данные». Там же находится нужная нам кнопка, которую можно найти в группе «Структура».

   

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

После выполнения всех операций таблица обретет такой вид.

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

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

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

Также возможно изменение расположения промежуточных итогов. Есть возможность разместить их над группой, а не под ней. Для этого необходимо убрать флажок рядом с пунктом «Итоги под данными».

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

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

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

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

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

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

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

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

Давайте на реальном примере разберем, как правильно применять эту функцию.

1. Добавляем отдельный ряд. Даем ему имя. Пусть это будет, как вариант, «сумма отобранных значений».
2. Активируем фильтрацию информации так, чтобы в диапазоне оставались лишь те числа, которые располагаются на одном ряду с текстом «Обеденная группа «Амадис».
3. Далее вводим формулу в ячейку B2 (или какую-угодно, в которую есть возможность вывести промежуточный результат) =ПРОМЕЖУТОЧНЫЕ.ИТОГИ(9;B3:B15).

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

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

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(1;B3:B15)

В случае с поиском максимального значения, функция была бы такой.

=ПРОМЕЖУТОЧНЫЕ.ИТОГИ(4;B3:B15)

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ () и Автофильтр

Давайте представим, что у нас есть таблица с номерами товаров и их стоимостью.

Давайте используем функцию «Автофильтр», чтобы отображать лишь ряды, описывающие товар номер 1. И давайте тогда применим эту функцию, чтобы узнать сумму товаров, отмеченных номером 1. Соответственно, нам требуется функция 9 или 109.

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

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ () и Скрытые строки

Предположим, у нас есть такая же таблица, к которой не был применен автофильтр. Ряды, озаглавленные «Товар 2» мы попробуем просто скрыть стандартным способом. То есть, воспользоваться или контекстным меню, или перейти по пути Главная – Ячейки – Формат – Скрыть.

Здесь пользователю необходимо убедиться, что используется тот код, подходящий под скрытые строки. Если говорить проще, то для поиска промежуточной суммы необходимо ввести код 109, а не 9. Последний вариант не чувствителен к скрытым строкам. Поэтому при ручной фильтрации данных нужно использовать коды, начиная 101 и заканчивая 111. Обозначения соответствующие описанным выше, просто нужно к ним добавить 100. 

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

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

Теперь давайте отфильтруем таблицу, оставив только Товар1 в ней.

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

Другие функции

В целом, количество функций, которые могут использоваться для подсчета промежуточного значения, достаточное. Но может оказаться так, что нужно включать другие. Предположим, перед нами стоит задача посчитать не среднее арифметическое, а среднее геометрическое. Эта функция выглядит, как СРГЕОМ(), и ее нет в описанном выше списке, но выкрутиться из этой ситуации получится. Для этого давайте откроем такую же таблицу.

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

Сама формула будет такой:

=СРГЕОМ(ЕСЛИ(ПРОМЕЖУТОЧНЫЕ.ИТОГИ(3;СМЕЩ($B$10;СТРОКА(ДВССЫЛ(«A1:A»&ЧСТРОК(B10:B19)))-1;));B10:B19;»»))

Как видим, мы используем сочетание функций СТРОКА и ДВССЫЛ в том месте, где расположен второй аргумент функции. Так можно добиться того, чтобы второму аргументу передавалось несколько диапазонов. Главное – убедиться в том, чтобы не было скрытой строки, потому что тогда помимо стоимости будет отображаться значение «Пустой текст», а оно функцией СРГЕОМ() не учитывается. 

Формула «Промежуточные итоги» в Эксель (примеры)

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

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

Наш диапазон выглядит следующим образом.

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

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

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

Написание формулы промежуточных итогов вручную

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

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

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

Добавление промежуточных итогов возможно и в сводной таблице. Этого нужно воспользоваться вкладкой «Итоги и фильтры» в ее параметрах. Чтобы осуществить вывод итогов на экран по отдельным значениям, необходимо воспользоваться кнопкой фильтра (в правой части названия колонки).

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