Как сделать промежуточные итоги в Excel
![](http://officeassist.ru/wp-content/uploads/12926/tmp-f522d832-7f2a-4502-96df-771de83ede61-360x135.jpg)
Постановка задачи
Имеем следующую таблицу:
![](http://officeassist.ru/wp-content/uploads/12926/tmp-f522d832-7f2a-4502-96df-771de83ede61.jpg)
Учет продаж товаров. Одна строка — одна продажа, с полной информацией кто, когда, куда и на сколько продал. Все как обычно.Размер таблицы — несколько сотен строк. Для особо впечатлительных — несколько тысяч.
Задача — подсчитать суммарное количество товаров и денег по каждому заказчику.
Решение
Как один из вариантов — для решения подобной проблемы можно использовать сводную таблицу.Другой вариант — не городить огород с построением сводных таблиц, а решить проблему с помощью Итогов (Subtotals). Методика следующая:
Шаг 1. Сортировка
Сначала необходимо отсортировать таблицу, чтобы сформировать группы строк, по которым в дальнейшем будут подсчитываться итоги. В нашем случае, таблицу нужно отсортировать по столбцу Заказчик. Самый простой способ это сделать — выделить любую ячейку в этом столбце и найти на панели инструментов кнопку
Шаг 2. Подведение итогов
Далее идем в меню Данные — Итоги (Data — Subtotals) и видим вот такое диалоговое окно Промежуточные итоги:
![](http://officeassist.ru/wp-content/uploads/12926/tmp-a145cd53-bba4-49a8-86b1-fe9f2078b7db.jpg)
В окне:
Служебные флажки в нижней части окна:
Жмем на ОК и получаем таблицу, куда автоматически включены итоги — по каждому заказчику будет добавлена строка:
Задача решена!
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Фактически, подведение итогов — это на самом деле всего лишь автоматическая вставка специальной функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), которую хорошо видно в строке формул при выделении любой ячейки с итогами:
![](http://officeassist.ru/wp-content/uploads/12926/tmp-b49b1db1-681b-4e46-9459-7e27e5589ffe.jpg)
Со вторым аргументом этой функции все понятно — это интервал подсчитываемых ячеек, а вот первый аргумент (на рисунке — число 9) поинтереснее. Первый аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ — это код математической операции итогов:
1 — среднее арифметическое 2 — количество чисел 3 — количество непустых ячеек 4 — максимум 5 — минимум 6 — произведение 7 — среднеквадратическое отклонение 8 — среднеквадратическое отклонение по генеральной совокупности 9 — сумма 10 — дисперсия 11 — дисперсия по генеральной совокупности
Хитрость в том, что, если указать любой из описанных выше кодов, то функция будет подсчитывать результат по всему заданному диапазону, а если указать код, который больше на 100, то функция будет считать только по видимым ячейкам указанного диапазона. Т.е. если указать 9, то функция будет суммировать все данные в ячейках диапазона J9:J582, независимо от того — видны ли ячейки или нет, а если указать код 109 — то суммироваться будут только видимые ячейки.
Такая хитрость часто применяется, чтобы суммировать результаты, полученные после Автофильтра.
Группировка и копирование результатов итогов
При подведении итогов Excel всегда сам производит группировку. Поэтому отсортированные группы можно быстро и удобно сворачивать-разворачивать с помощью знаков «плюс» и «минус» слева от таблицы. В свернутом состоянии отчет выглядит компактно и информативно:
![](http://officeassist.ru/wp-content/uploads/12926/tmp-25c60a24-4a00-45d0-928e-64f0d8cf6de7.jpg)
Одно «НО». Если выделить такой отчет и попытаться его скопировать, например, на чистый лист — то скопируются все скрытые строки-столбцы, а не только выделенные строчки итогов. Похожий глюк, кстати, возникает в Excel достаточно часто и не только при использовании Итогов. Проблема решается просто:
![](http://officeassist.ru/wp-content/uploads/12926/tmp-b8112ec8-3a05-4100-bb31-ae347d95c07b.jpg)
Как раз в нем-то можно выбрать вариант Только видимые ячейки (Visible cells) и после нажатия на ОК, копировать и вставлять результаты Итогов на другой лист — скрытые ячейки не выделяются и не будут копироваться.