Как сделать промежуточные итоги в Excel
Постановка задачи
Имеем следующую таблицу:
Учет продаж товаров. Одна строка — одна продажа, с полной информацией кто, когда, куда и на сколько продал. Все как обычно.Размер таблицы — несколько сотен строк. Для особо впечатлительных — несколько тысяч.
Задача — подсчитать суммарное количество товаров и денег по каждому заказчику.
Решение
Как один из вариантов — для решения подобной проблемы можно использовать сводную таблицу.Другой вариант — не городить огород с построением сводных таблиц, а решить проблему с помощью Итогов (Subtotals). Методика следующая:
Шаг 1. Сортировка
Сначала необходимо отсортировать таблицу, чтобы сформировать группы строк, по которым в дальнейшем будут подсчитываться итоги. В нашем случае, таблицу нужно отсортировать по столбцу Заказчик. Самый простой способ это сделать — выделить любую ячейку в этом столбце и найти на панели инструментов кнопку
Шаг 2. Подведение итогов
Далее идем в меню Данные — Итоги (Data — Subtotals) и видим вот такое диалоговое окно Промежуточные итоги:
В окне:
Служебные флажки в нижней части окна:
Жмем на ОК и получаем таблицу, куда автоматически включены итоги — по каждому заказчику будет добавлена строка:
Задача решена!
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Фактически, подведение итогов — это на самом деле всего лишь автоматическая вставка специальной функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), которую хорошо видно в строке формул при выделении любой ячейки с итогами:
Со вторым аргументом этой функции все понятно — это интервал подсчитываемых ячеек, а вот первый аргумент (на рисунке — число 9) поинтереснее. Первый аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ — это код математической операции итогов:
1 — среднее арифметическое 2 — количество чисел 3 — количество непустых ячеек 4 — максимум 5 — минимум 6 — произведение 7 — среднеквадратическое отклонение 8 — среднеквадратическое отклонение по генеральной совокупности 9 — сумма 10 — дисперсия 11 — дисперсия по генеральной совокупности
Хитрость в том, что, если указать любой из описанных выше кодов, то функция будет подсчитывать результат по всему заданному диапазону, а если указать код, который больше на 100, то функция будет считать только по видимым ячейкам указанного диапазона. Т.е. если указать 9, то функция будет суммировать все данные в ячейках диапазона J9:J582, независимо от того — видны ли ячейки или нет, а если указать код 109 — то суммироваться будут только видимые ячейки.
Такая хитрость часто применяется, чтобы суммировать результаты, полученные после Автофильтра.
Группировка и копирование результатов итогов
При подведении итогов Excel всегда сам производит группировку. Поэтому отсортированные группы можно быстро и удобно сворачивать-разворачивать с помощью знаков «плюс» и «минус» слева от таблицы. В свернутом состоянии отчет выглядит компактно и информативно:
Одно «НО». Если выделить такой отчет и попытаться его скопировать, например, на чистый лист — то скопируются все скрытые строки-столбцы, а не только выделенные строчки итогов. Похожий глюк, кстати, возникает в Excel достаточно часто и не только при использовании Итогов. Проблема решается просто:
Как раз в нем-то можно выбрать вариант Только видимые ячейки (Visible cells) и после нажатия на ОК, копировать и вставлять результаты Итогов на другой лист — скрытые ячейки не выделяются и не будут копироваться.
