Сегодня мы рассмотрим функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() в Excel и Вы поймете почему Вам стоит ее использовать.
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() в Excel обеспечивает быстрый и простой способ суммирования табличных данных. Обычно эта функция используется для отображения только итогов по разным категориям.
При использовании функции СУММ() для суммирования значений при условии примененного фильтра функция добавляет как видимые, так и скрытые ячейки. Следовательно, если перед Вами стоит задача посчитать сумму только отфильтрованного диапазона, то функция СУММ() нам не подходит, а с этой задачей справится как раз функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), которая вычисляет только видимые ячейки в диапазоне.
Принцип работы ее прост и состоит из двух вещей:
1. тип промежуточного итога;
2. диапазон данных.
ПРОМЕЖУТОЧНЫЕ.ИТОГИ(тип итога; диапазон данных)
Если с диапазоном все понятно, то что же такое "тип промежуточного итога".
Тип промежуточного итога зависит от Вашей задачи и может быть - суммой, средним значением, произведением и т.д.
Обозначается Тип промежуточного итога соответствующим номером функции.
Давайте рассмотрим пример работы двух формул СУММ() и ПРОМЕЖУТОЧНЫЕ.ИТОГИ() с типом промежуточного итога сумма - 9
Из имеющихся данных нам необходимо отфильтровать объем продаж за 2018 год. Применим соответствующий фильтр в столбце "Год"
В случае функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() все данные в скрытых фильтром ячейках в расчет не берутся и расчет идет только по отображаемым ячейкам, тогда как в случае функции СУММ() расчет не зависит от примененного фильтра и к учету берется весь указанный диапазон.
Еще очень показательный пример и веский аргумент использовать функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(), который позволит Вам избежать досадных ошибок и задвоений в расчетах.
Допустим наши данные в таблице структурированы по Продавцу и под каждым Продавцом выведен итог объема его продаж с использованием функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ(). В данном примере нам необходимо посчитать общий объем продаж, также в сравнении посмотрим как с этой задачей справится стандартная функция СУММ()
Мы видим, что при использовании функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ() итоги объема продаж под каждым продавцом посчитанные с использованием этой же функции не учитываются, тем самым не позволяя цифрам задвоиться.
Что касается функции СУММ(), она в данном случае с задачей не справляется и дважды учитывает и показатели по годам и итоги по продавцам.
Еще одна замечательная особенность функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ()
Лично я функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() стараюсь использую всегда, особенно когда создаю нумерованные списки, в которых важно отражать последовательность строк нарастающим итогом 1,2,3,4 .... и т.д. вне зависимости применен фильтр или нет. Получается некая динамическая нумерация.
Итак, как же сделать динамическую нумерацию строк?
Для этого нам понадобится список и функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() , в которой в качестве типа промежуточного итога мы будет использовать номер функции - 2, соответствующий формуле СЧЁТ().
В строке А2 запишем формулу ПРОМЕЖУТОЧНЫЕ.ИТОГИ(2;$C$2:C2) и здесь обратите внимание, что ячейка С2 закреплена и будет неизменна когда мы нашу функцию продлим вниз.
Особенность закрепления начала диапазона в данном случае, это произвести расчет количества значений между закрепленной ячейкой и текущей.
Например:
в диапазоне $C$2:C3 - 2 значения;
в диапазоне $C$2:C4 - 3 значения и т.д.
Главное условие такого способа, чтобы ячейки, по которым мы ведем подсчет были заполнены.
В итоге мы получим следующий результат:
Теперь применим фильтр по продавцу "Петров" и увидим, что нумерация изменилась вместе с фильтром
Вот такая вот полезная функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ() особенно когда Вы научитесь применять ее совместно с другими функциями Excel.