Автоматичне підбиття підсумків у списках

Маємо таку таблицю:

Облік продажу товарів. Один рядок – один продаж, з повною інформацією хто, коли, куди і на скільки продав. Все як завжди. Розмір таблиці – кілька сотень рядків. Для особливо вразливих – кілька тисяч.

Завдання - підрахувати сумарну кількість товарів та грошей за кожним замовником.

Як один із варіантів - для вирішення подібної проблеми можна використовувати зведену таблицю. Інший варіант - не городити город із побудовою зведених таблиць, а вирішити проблему за допомогою Підсумків (Subtotals). Методика така:

Крок 1. Сортування

Спочатку необхідно відсортувати таблицю, щоб сформувати групи рядків, якими надалі підраховуватимуться результати. У нашому випадку таблицю потрібно відсортувати по стовпцю Замовник. Найпростіший спосіб це зробити - виділити будь-яку комірку в цьому стовпці і знайти на панелі інструментів кнопку

Крок 2. Підбиття підсумків

Далі йдемо в меню Дані - Підсумки (Data - Subtotals) і бачимо таке діалогове вікно Проміжні підсумки :

·	При кожній зміні (At each change in) - вказуємо поле, за яким відсортована наша таблиця

·	Операція (Use function) – вибираємо математичну функцію розрахунку. У більшості випадків це, звичайно, сума, але можливі інші варіанти (середнє, мінімум-максимум, кількість і т.д.)

·	Додати підсумки (Add subtotals to) - відзначаємо галочками ті стовпці, за якими хочемо підрахувати суму. У нашому прикладі – це Кількість та Вартість продажу, а так – хоч усе.

Службові прапорці у нижній частині вікна:

·	Замінити поточні підсумки (Replacecurrent 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) і після натискання на ОК , копіювати і вставляти результати Підсумків на інший аркуш - приховані осередки не виділяються і не копіюватимуться.