Навигация:  Ключевые приемы >

Автоматическое подведение итогов в списках

Print this Topic Previous pageReturn to chapter overviewNext page

Постановка задачи

Имеем следующую таблицу:

Учет продаж товаров. Одна строка - одна продажа, с полной информацией кто, когда, куда и на сколько продал. Все как обычно. Размер таблицы - несколько сотен строк. Для особо впечатлительных - несколько тысяч.

Задача - подсчитать суммарное количество товаров и денег по каждому заказчику.

Решение

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

к библиотеке   3GL   к оглавлению   к экономической информатике   4GL - визуальным средам

Знаете ли Вы, что только в 1990-х доплеровские измерения радиотелескопами показали скорость Маринова для CMB (космического микроволнового излучения), которую он открыл в 1974. Естественно, о Маринове никто не хотел вспоминать. Подробнее читайте в FAQ по эфирной физике.

НОВОСТИ ФОРУМА

Форум Рыцари теории эфира


Рыцари теории эфира
 10.11.2021 - 12:37: ПЕРСОНАЛИИ - Personalias -> WHO IS WHO - КТО ЕСТЬ КТО - Карим_Хайдаров.
10.11.2021 - 12:36: СОВЕСТЬ - Conscience -> РАСЧЕЛОВЕЧИВАНИЕ ЧЕЛОВЕКА. КОМУ ЭТО НАДО? - Карим_Хайдаров.
10.11.2021 - 12:36: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от д.м.н. Александра Алексеевича Редько - Карим_Хайдаров.
10.11.2021 - 12:35: ЭКОЛОГИЯ - Ecology -> Биологическая безопасность населения - Карим_Хайдаров.
10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> Проблема государственного терроризма - Карим_Хайдаров.
10.11.2021 - 12:34: ВОЙНА, ПОЛИТИКА И НАУКА - War, Politics and Science -> ПРАВОСУДИЯ.НЕТ - Карим_Хайдаров.
10.11.2021 - 12:34: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вадима Глогера, США - Карим_Хайдаров.
10.11.2021 - 09:18: НОВЫЕ ТЕХНОЛОГИИ - New Technologies -> Волновая генетика Петра Гаряева, 5G-контроль и управление - Карим_Хайдаров.
10.11.2021 - 09:18: ЭКОЛОГИЯ - Ecology -> ЭКОЛОГИЯ ДЛЯ ВСЕХ - Карим_Хайдаров.
10.11.2021 - 09:16: ЭКОЛОГИЯ - Ecology -> ПРОБЛЕМЫ МЕДИЦИНЫ - Карим_Хайдаров.
10.11.2021 - 09:15: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Екатерины Коваленко - Карим_Хайдаров.
10.11.2021 - 09:13: ВОСПИТАНИЕ, ПРОСВЕЩЕНИЕ, ОБРАЗОВАНИЕ - Upbringing, Inlightening, Education -> Просвещение от Вильгельма Варкентина - Карим_Хайдаров.
Bourabai Research - Технологии XXI века Bourabai Research Institution