Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ позволяет очень гибко производить требуемые операции над значениями диапазона. В Excel есть инструменты, которые по умолчанию используют эту функцию и поэтому многие пользователи даже не догадываются об ее существовании, но обо всем по порядку.
Например, у нас есть таблица с расчетами и необходимо подвести итоги по некоторым столбцам, то есть посчитать сумму всех значений.
Обычно это делается с помощью функции СУММ или, что тоже самое, с помощью автосуммирования, которое можно вставить сочетанием клавиш Alt + =.
Основной минус этого метода заключается в том, что если в диапазоне нужно будет скрыть ряд строк, то итоговое значение никак не изменится.
Да, скрытие строк используется не часто, так как такой подход к работе крайне неудобен, но группировка данных в таблице по сути также является скрытием строк.
Например, в таблице нужно всех сотрудников распределить по отделам и в итоге документ должен выглядеть следующим образом.
Лично я стараюсь избегать таблиц, сконструированных подобным образом и предпочитаю иметь дело с умными и сводными таблицами, однако, такой подход к созданию таблиц довольно часто встречается на практике.
Здесь также можем подвести итоги с помощью функции суммирования, выбрав весь диапазон значений.
Но с помощью инструмента "Структура" сотрудники могут быть сгруппированы по отделами, а итоги нужно выводить только по видимым строкам. Давайте сгруппируем сотрудников и затем убедимся, что функция СУММ все равно будет выводить общую сумму значений всех ячеек, в том числе и скрытых.
Итоговое значение никак не зависит от состояния группы (развернута она или свернута), поскольку в формуле указан весь диапазон значений.
Вот здесь и приходит на помощью функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
У функции два основных аргумента. Первый - это номер используемой при расчете функции, который берется из раскрывающегося списка.
Второй аргумент - это диапазон значений, над которым производятся вычисления. При этом можно задать несколько диапазонов или ячеек.
Список функций, которые можно использовать, довольно обширный, но на практике в основном используются наиболее популярные функции вроде СУММ, СРЗНАЧ или МАКС.
Также обратите внимание на то, что функции дублируются, то есть одни и те же функции представлены в списке дважды и по факту мы имеем доступ лишь к одиннадцати функциям.
Дело в том, что функции с кодом от 1 до 11 будут учитывать строки, которые были скрыты вручную, а функции с 101 по 111 будут такие строки пропускать, а именно это нам и нужно.
То есть функция с кодом 9 будет работать также, как и вставленная нами ранее функция СУММ, а функция с номером 109 пропустит скрытые строки. Используем ее и выберем весь диапазон значений выше.
Проверяем работу функции и убеждаемся, что итоговое значение изменяется при скрытии сотрудников того или иного отдела.
Давайте по аналогии создадим промежуточные итоги по каждому отделу, но так как внутри отдела скрытых строк нет, то воспользуемся обычной функцией СУММ с кодом 9.
И тут может возникнуть вполне резонный вопрос - зачем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ, когда мы фактически производим элементарное суммирование, с которым справится обычная функция СУММ?
Чтобы ответить на этот вопрос создадим промежуточные итоги для каждого отдела. Если мы будем скрывать строки по отдельным отделам, то промежуточные итоги по каждому отделу будут выводиться, но они не будут учитываться в общем итоговом значении.
То есть функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ не учитывает значения таких же вложенных функций.
Ну и есть еще один нюанс использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ с обычными диапазонами значений. Скрывать строки в диапазоне можно не только вручную или с помощью группировки, но и используя автофильтр.
В этом случае все используемые в ПРОМЕЖУТОЧНЫЕ.ИТОГИ функции будут вести себя одинаково, то есть будут учитывать значения только из видимых строк. Это означает, что не важно будет ли использоваться функция СУММ с кодом 109 или 9. Они обе в данной ситуации будут работать корректно.
И тут мы логично переходим к умным таблицам, которые по умолчанию содержат фильтры в заголовках столбцов. Напомню, что любой неразрывный диапазон значений можно преобразовать в умную таблицу, например, с помощью сочетания клавиш Ctrl + T.
Фильтры включаются отключаются на вкладке Конструктор таблиц. Здесь же можно вывести строку с итогами и это то, о чем я говорил в самом начале видео - многие пользователи используют итоги, не вникая в суть этой опции.
В итоговой строке используется функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ и в этом легко убедиться, посмотрев в строку формул.
Итоговая строка в умной таблице находится в самом низу, что не всегда может быть удобным, особенно при работе с очень большими таблицами. Но зная как итоги формируются, мы можем подвести итоги в любой другой ячейке, например, в первых двух строках листа можем подсчитать общую сумму всех зарплат, а также среднее значение.
Теперь с помощью фильтров умной таблицы мы можем в ней оставить только нужные значения и в соответствующих ячейках будут рассчитаны общая сумма и среднее.
И еще один пример использования функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ.
У нас есть таблица, в которой в первом столбце выводится порядковый номер строки.
Если этот номер сделать прогрессией или с помощью формул, то в случае скрытия части строк таблицы эта нумерация никак не изменится.
И вот тут удобно использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ. Воспользуемся функцией СЧЁТЗ из второго ряда, то есть учитывающую скрытые строки. Функция СЧЁТЗ подсчитывает количество заполненных ячеек в диапазоне. Диапазон значений у нас будет плавающий - то есть в каждой последующей ячейке диапазон должен расширяться на одну. Сделать это довольно просто с помощью абсолютной ссылки.
Значения мы будем подсчитывать в соседнем столбце, поэтому в качестве диапазона введем B2:B2, но первую ссылку зафиксируем. В итоге при автозаполнении формулы по диапазону эта вторая ссылка будет изменяться, а первая останется зафиксированной.
Теперь можем скрыть строки и убедимся, что нумерация изменится.
Таким образом функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ можно использовать для создания автонумерации строк таблиц.
Ссылки на мои ресурсы по Excel
★ Телеграм
