Для отримання різної підсумкової інформації в MS Excel призначена команда Дані → Підсумки. Перед виконанням цієї команди дані повинні бути представлені у вигляді списку. Але перш ніж підбивати підсумки, потрібно обов'язково впорядкувати список.
За командою Підсумки з меню Дані відкривається діалогове вікно Проміжні підсумки. У ньому задаються поле, при кожній зміні значення якого будуть обчислюватися підсумкові значення, і операція яка буде застосовувати до значень полів, зазначених у списку Додати підсумки по.
Створення проміжних підсумків засновано на попередній сортування записів списку, при цьому важливий порядок сортування - склад і підпорядкованість ключовий сортування. Якщо сортування була виконана по полях: поле 1, поле 2, поле 3, тобто поле 1 є найстаршим в сортуванні, поле 2 визначає порядок сортування рядків список при однакових значеннях поля 1, а поле 3 задає порядок сортування при однакових значеннях і поля 1, і поля 2, - то і підведення підсумків має свій жорсткий порядок: поле 1 , поле 2, або поле 1.
Якщо таблиця вже містить підсумкові рядки, в неї можна додати нові підсумкові значення, розраховані за допомогою іншої функції. Для цього у вікні Проміжні підсумки слід зняти прапорець Замінити поточні підсумки і задати потрібне поле і функцію (операцію). Два інших прапорця дозволяють розміщувати підсумки під або над рядками даних і виводити кожну групу значень на окремому аркуші.
Якщо проміжні підсумки більше не потрібні, то список можна привести в початковий стан, для цього досить клацнути на кнопці Скасування. але відміна спрацьовує лише в тому випадку, якщо після форматування підсумків не було інших змін списку, в іншому випадку слід клацнути по кнопці Прибрати все. яка повертає список в початковий стан.
Команда Підсумки в меню дані дозволяє виконувати наступні дії:
- за окремим полю списку, використовуваному в якості поля угруповання, можна здійснювати формування підсумків різних видів операцій (функцій);
- для однакових значень поля угруповання можна формувати підсумки по одному або декількох полях списку, при цьому вид операції визначає, які поля можуть використовуватися для підведення підсумків. Так, для операцій Сума. Середнє. Максимальне. Мінімальна і т. П. Можуть вибиратися поля тільки числового типу. Для операції Кількість значень - поля будь-якого типу (числові, текстові, дати).
Вивчаючи процес підведення проміжних підсумків, будемо оперувати даними з таблиці відображеної на ріс.97.
Малюнок 97 - Таблиця-приклад
Завдання: За допомогою функції автоматичного обчислення підсумків визначити оборот кожного продавця за вказаний відрізок часу.
1. Встановіть покажчик осередку в будь-якому осередку таблиці і активізуйте команду Сортування з меню Дані. Сортований діапазон буде автоматично виділено. Задайте в діалоговому вікні Сортування критерії сортування, як показано на ріс.98.
Малюнок 98 - Діалогове вікно Сортування Діапазону
Після натискання кнопки ОК в діалоговому вікні Сортування діапазону дані в таблиці будуть відсортовані (в стовпці Продавець прізвища продавців будуть впорядковані за алфавітом).
2. Активізуйте одну з осередків таблиці і виберіть в меню Дані команду Підсумки. В результаті відкриється діалогове вікно Проміжні підсумки. У прихованому переліку При кожній зміні в виберіть заголовок стовпчика Продавець, для якого необхідно обчислювати проміжні підсумки після кожної зміни даних на робочому аркуші. Для обчислення підсумкової суми в поле списку Операція встановіть функцію Сума. Вкажіть також стовпець для обчислення підсумків, наприклад стовпець Оборот. Для цього активізуйте в області Додати підсумки по опцію Оборот і вимкніть опцію Дата. як показано на ріс.99. На завершення клацніть на кнопці ОК.
Малюнок 99 - Діалогове вікно Проміжні підсумки
В результаті таблиця буде доповнена рядками, що містять підсумкові значення для кожного продавця. В останню з вставлених в таблицю рядків включається інформація про загальному підсумку для всіх продавців (ріс.100).
Малюнок 100 - Таблиця-приклад після автоматичного обчислення підсумків
Для обробки даних зі стовпців, зазначених в діалоговому вікні Проміжні підсумки. можуть використовуватися такі функції, зазначені в таблиці 1:
Таблиця 23 - Функції для обробки даних за допомогою проміжних підсумків
Визначає значення дисперсії для генеральної вибірки
3. При обчисленні підсумків таблиця структурується. Створення рівнів структури призводить до підвищення наочності таблиці. Щоб відобразити на екрані тільки підсумкові дані, слід клацнути на кнопці другого рівня структури, внаслідок чого дані третього рівня (вихідні значення) будуть приховані (ріс.101). Для відновлення відображення початкового значень необхідно клацнути на кнопці третього рівня.
Малюнок 101 - Дані третього рівня приховані
Увага! Перед автоматичним обчисленням підсумків не забудьте впорядкувати дані в таблиці. В іншому випадку підсумкові значення будуть визначені для кожного запису.
4. Розгляньте ще одну можливість MS Excel - комбінування декількох підсумків (вкладених підсумків). Додайте в таблицю ще один показник - кількість автомобілів, проданих кожним продавцем.
a) Повторно відкрийте діалогове вікно Проміжні підсумки.
b) У поле При кожній зміні в виберіть елемент Продавець.
Щоб визначити кількість автомобілів, проданих кожним продавцем, встановіть в поле Операція функцію Кількість значень і активізуйте в області Додати підсумки по опцію Марка. Можна вказати будь-який стовпець, оскільки дані вже відсортовані по групах і необхідно визначити лише кількість рядків в групі.
Примітка: Для того щоб в таблиці відображалися всі підсумки (ріс.102), перед натисканням кнопки ОК слід вимкнути опцію Замінити поточні підсумки.
Для видалення рядків з підсумковими значеннями призначена кнопка Прибрати все. розташована в діалоговому вікні Проміжні підсумки.
Малюнок 102 -Таблиця-приклад після обчислення проміжних підсумків
Консолідація - це ще один із способів отримання підсумкової інформації, тобто агрегування відповідно до обраної функції обробки даних, представлених у вихідних областях-джерелах. Результат консолідації знаходиться в області призначення. Області-джерела можуть перебувати на різних аркушах або робочих книгах. У консолідації може брати участь до 255 областей-джерел, а самі джерела можуть бути закриті під час консолідації.
Для консолідації даних курсор встановлюється в область местоназначенія. Виконується команда Дані → Консолідація. вибирається варіант і задаються умови консолідації.
Існують наступні варіанти консолідації:
- консолідація по розташуванню для однаково організованих джерел (фіксоване розташування);
- консолідація зовнішніх даних.
При консолідації зовнішніх даних слід натиснути кнопку Погортати. в діалоговому вікні Погортати вибрати файл, який містить області-джерела для додавання до списку, а потім додати посилання на клітинку або вказати ім'я блоку осередків.
Перемикач Створювати Зв'язки з Вихідними Даними створює при консолідації зв'язку області призначення до областям-джерел.
Вивчаючи процес консолідації, будемо оперувати даними з таблиць, відображених на ріс.103 (а, б, в)
Малюнок 103 - Дані про обсяги продажів у філіях: а) - Оренбурзькому, б) - Бузулуцькому, в) - Орском
Завдання: Використовуючи операцію консолідації обчислити сумарний обсяг продажів автомобілів по всіх філіях.
1. У рядку «Всього» для кожної таблиці підсумуйте дані.
2. На Лісте4 активізуйте комірку А4, яка послужить початком діапазону комірок з підсумками.
3. Активізуйте команду Консолідація меню Дані.
4. В однойменному вікні Консолідація в поле Функція виберіть функцію, яка буде використовуватися при об'єднанні даних (а нашому прикладі - Сума).
8. Встановіть опцію значення лівого стовпця в області використовувати як імена діалогового вікна Консолідація. Таким чином ви задасте консолідацію по іменах. При цьому значення в рядках з однаковими мітками будуть підсумовані.
9. Встановіть опцію створювати зв'язку з вихідними даними (ріс.104), щоб між вихідними даними і результатами консолідації була встановлена динамічна зв'язок, що забезпечує автоматичне оновлення даних.
Малюнок 104 - Діалогове вікно Консолідація, в якому задані параметри консолідації
10. Після натискання кнопки ОК слід активізувати той робочий лист, в якому повинні знаходитися результати консолідації.
Отримана таблиця складається з двох стовпців. Перший містить назви фірм-виробників, у другому вказується кількість автомобілів, проданих всіма філіями фірми.
Завдання для самостійного виконання:
1. Таблицю з консолідованими даними відформатувати, задати межі (ріс.105).
2. Побудувати діаграму на основі таблиці з консолідованими даними.
Малюнок 105 - Консолідовані дані
Питання для самоконтролю
1. Як формуються підсумки в списках за заданими критеріями?
2. Перерахуйте функції, які можуть використовуватися при обчисленні підсумків?
3. Що таке консолідація таблиць?
4. Які варіанти консолідації ви знаєте?
5. Вкажіть основні особливості кожного виду консолідації.