|
Автоматическое подведение итогов в списках |
  
|
|
Автоматическое подведение итогов в списках |
|
Постановка задачи
Имеем следующую таблицу:
Учет продаж товаров. Одна строка - одна продажа, с полной информацией кто, когда, куда и на сколько продал. Все как обычно. Размер таблицы - несколько сотен строк. Для особо впечатлительных - несколько тысяч.
Задача - подсчитать суммарное количество товаров и денег по каждому заказчику.
Решение
Как один из вариантов - для решения подобной проблемы можно использовать сводную таблицу. Другой вариант - не городить огород с построением сводных таблиц, а решить проблему с помощью Итогов (Subtotals). Методика следующая:
Шаг 1. Сортировка
Сначала необходимо отсортировать таблицу, чтобы сформировать группы строк, по которым в дальнейшем будут подсчитываться итоги. В нашем случае, таблицу нужно отсортировать по столбцу Заказчик. Самый простой способ это сделать - выделить любую ячейку в этом столбце и найти на панели инструментов кнопку
Шаг 2. Подведение итогов
Далее идем в меню Данные - Итоги (Data - Subtotals) и видим вот такое диалоговое окно Промежуточные итоги:
В окне:
| · | При каждом изменении в (At each change in) - указываем поле, по которому отсортирована наша таблица |
| · | Операция (Use function) - выбираем математическую функцию расчета. В большинстве случаев это, конечно же, сумма, но возможны другие варианты (среднее, минимум-максимум, количество и т.д.) |
| · | Добавить итоги по (Add subtotals to) - отмечаем галочками те столбцы, по которым хотим подсчитать сумму. В нашем примере - это Количество и Стоимость продажи, а так - хоть все. |
Служебные флажки в нижней части окна:
| · | Заменить текущие итоги (Replace current subtotals) - если стоит, то каждый раз подводя итоги, вы будете стирать предыдущие. Если ее снять, то можно копить разные итоги, подводя их друг за другом в несколько заходов. |
| · | Конец страницы между группами (Page break between groups) - после каждой строки итогов будет вставлен разрыв страницы. Весьма удобно при печати отчетов. |
| · | Итоги под данными (Summary below data) - в некоторых странах (у америкосов, например) принято строку итогов помещать не внизу, под списком, а сверху. На любителя, вобщем. |
Жмем на ОК и получаем таблицу, куда автоматически включены итоги - по каждому заказчику будет добавлена строка:
Задача решена!
Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ
Фактически, подведение итогов - это на самом деле всего лишь автоматическая вставка специальной функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ (SUBTOTALS), которую хорошо видно в строке формул при выделении любой ячейки с итогами:
Со вторым аргументом этой функции все понятно - это интервал подсчитываемых ячеек, а вот первый аргумент (на рисунке - число 9) поинтереснее. Первый аргумент функции ПРОМЕЖУТОЧНЫЕ.ИТОГИ - это код математической операции итогов:
1 - среднее арифметическое
2 - количество чисел
3 - количество непустых ячеек
4 - максимум
5 - минимум
6 - произведение
7 - среднеквадратическое отклонение
8 - среднеквадратическое отклонение по генеральной совокупности
9 - сумма
10 - дисперсия
11 - дисперсия по генеральной совокупности
Хитрость в том, что, если указать любой из описанных выше кодов, то функция будет подсчитывать результат по всему заданному диапазону, а если указать код, который больше на 100, то функция будет считать только по видимым ячейкам указанного диапазона. Т.е. если указать 9, то функция будет суммировать все данные в ячейках диапазона J9:J582, независимо от того - видны ли ячейки или нет, а если указать код 109 - то суммироваться будут только видимые ячейки.
Такая хитрость часто применяется, чтобы суммировать результаты, полученные после Автофильтра.
Группировка и копирование результатов итогов
При подведении итогов Excel всегда сам производит группировку. Поэтому отсортированные группы можно быстро и удобно сворачивать-разворачивать с помощью знаков "плюс" и "минус" слева от таблицы. В свернутом состоянии отчет выглядит компактно и информативно:
Одно "НО". Если выделить такой отчет и попытаться его скопировать, например, на чистый лист - то скопируются все скрытые строки-столбцы, а не только выделенные строчки итогов. Похожий глюк, кстати, возникает в Excel достаточно часто и не только при использовании Итогов. Проблема решается просто:
| 1. | Выделите ячейки с итогами |
| 2. | Нажмите клавишу F5 и в открывшемся окне - кнопку Выделить (Special) |
| 3. | Появится окно Выделение группы ячеек: |
Как раз в нем-то можно выбрать вариант Только видимые ячейки (Visible cells) и после нажатия на ОК, копировать и вставлять результаты Итогов на другой лист - скрытые ячейки не выделяются и не будут копироваться.
|
|
 |
