Как сделать промежуточные итоги в 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) и после нажатия на ОК, копировать и вставлять результаты Итогов на другой лист — скрытые ячейки не выделяются и не будут копироваться.

Поделиться:
Нет комментариев

Добавить комментарий

Ваш e-mail не будет опубликован. Все поля обязательны для заполнения.

×
Рекомендуем посмотреть