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

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

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 - визуальным средам

Знаете ли Вы, что релятивистское объяснение феномену CMB (космическому микроволновому излучению) придумал человек выдающейся фантазии Иосиф Шкловский (помните книжку миллионного тиража "Вселенная, жизнь, разум"?). Он выдвинул совершенно абсурдную идею, заключавшуюся в том, что это есть "реликтовое" излучение, оставшееся после "Большого Взрыва", то есть от момента "рождения" Вселенной. Хотя из простой логики следует, что Вселенная есть всё, а значит, у нее нет ни начала, ни конца... Подробнее читайте в 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