Технологія створення зведених таблиць

Зведені таблиці – це спеціалізовані засоби Microsoft Excel, які об'єднують у собі всі розглянуті вище засоби роботи зі списками (сор­тування, фільтрація, підсумки, консолідація).

Зведена таблиця – це плоска або об’ємна (складається з декількох сторінок або шарів) прямокутна таблиця, яка дає можливість виконати складний аналіз великих масивів даних.

У такій таблиці поряд із звичайними списками можуть використовуватися дані з іншої зведеної таблиці, а також запити до зовнішніх даних. Для підсумкових розрахунків можна вибрати потрібну функцію зведення або інший метод обчислення.

Обчислення здійснюють для одного або декількох полів вихідного списку, визначених як поля даних. Інші поля вихідного списку можуть бути використані для групування даних у рядках і стовпцях зведеної таблиці.

Крім того, зведена та­блиця утворюється так, що значення певних полів можуть виступати в ролі заголовків стовпців.

Побудувати зведену таблицю можна за допомогою Майстер зведених таблиць та діаграм (Див. рис. 17).

Рисунок 17. Вікно Майстра зведених таблиць та діаграм

Приклад 2. Необхідно отримати зведену інформацію про групу. Підготувати звіти про:

середню стипендію дівчат у розрізі спеціальності та форми навчання;

сумарну стипендію студентів кожної зі спеціальностей в розрізі форми навчання на окремих листах.

На Аркуші 6 побудована таблиця з інформацією про групу (Див. рис. 18).

Рисунок 18. Інформація про групу

Необхідно виконати команду Дані Зведена таблиця.

На першому кроці роботи майстра вибирається, де знаходяться дані, на основі яких будується зведена таблиця. Також тут можна за­дати, що створюємо – зведену таблицю чи зведену діаграму.

На другому кроці задаємо діапазон даних, на основі якого будує­мо зведену таблицю (або декілька діапазонів консолідації) (Див. рис 19).

Рисунок 19. Визначення діапазону даних

На наступному кроці вибираємо, де буде розміщена зведена та­блиця. Крім того, тут можна задати структуру зведеної таблиці (кнопка Макет) та налаштувати її параметри (кнопка Параметри):

При натисканні на кнопку Макет з'являється вікно (Див. рис. 20), в якому вправій частині відображаються поля таблиці (наприклад, Прізвище, Стать). У лівій частині розміщені області зведеної таблиці.

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

Рисунок 20. Розміщення даних в областях зведеної таблиці

Область Дані містить поля, які будуть розміщені на перетині рядків і стовпців і для яких будуть обчислені підсумки.

Перетягуванням полів у потрібну область можна сформувати структуру зведеної таблиці.

Для того щоб у зведеній таблиці розрахувати середнє значення за полем Ставка, необхідно у вікні Майстра зведених таблиць та діаграм двічі натиснути лівою клавішею миші на назву поля в області даних та у вікні Обчислення поля зведеної таблиці вибрати необхідну опе­рацію (Середнє) (Див. рис. 21).

Рисунок 21. Завдання обчислень за полем Стипендія

Щоб установити фільтр для відображення сторінки з інформаці­єю про жінок, у списку Заховати елементи вибрати «ч».

Вікно Параметри зведеної таблиці (Див. рис. 22) дозволяє задати такі налаштування зведеної таблиці:

Рисунок 22. Параметри зведеної таблиці

Для зміни структури зведеної таблиці необхідно розмістити курсор на довільній комірці зведеної таблиці та скористатися пік­тограмою Майстер зведених таблиць панелі інструментів Зведені таблиці (Див. рис. 23).

Рисунок 23. Заповнені дані в областях зведеної таблиці

Для відображення інформації на окремих сторінках необхідно розмістити курсор на довільній комірці зведеної таблиці та ско­ристатися піктограмою Відобразити сторінки панелі інструментів Зведені таблиці.

У вікні Відображення сторінок вибрати поле, по якому будуть відо­бражені сторінки (Посада). Після цього до книги будуть додані сторін­ки, кожна з яких відповідає одному зі значень поля Посада (Див. рис. 24).

Рисунок 24. Приклад зведеної таблиці із застосованим фільтром на відбір працівників-жінок

На основі зведеної таблиці можна будувати зведену діаграму. Необхідно зауважити, що при цьому на діаграмі можна також буде зада­вати фільтри відбору даних. Змінити ж діапазон даних, на основі якого побудується зведена таблиця, не можна. Для побудови зведе­ної діаграми необхідно скористатися одним із відомих способів по­будови діаграм (Див. рис 25).

Рисунок 25. Діаграма, побудована на основі

зведеної таблиці

Завдання для самостійної роботи

1. Базу даних, згідно індивідуального варіанту, імпортувати в формат Microsoft Excel.

Питання для самоконтролю

1 Обчислення підсумків в Excel.

2 Консолідація даних.

3 Що таке зведена таблиця?

4 За допомогою чого можна налаштовувати зведені таблиці?

5 Які є параметри зведеної таблиці?

6 Як викликається зведена таблиця?

7 Які параметри налаштування має зведена таблиця?