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

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

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

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

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

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

Побудувати зведену таблицю можна за допомогою Мастера сводных таблиц и диаграмм(рис. 10.6).

Рисунок 10.6 - Вікно Мастера сводных

таблиц и диаграмм

 

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

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

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

Розв’язання

На листі Лист 1 побудована таблиця з інформацією про групу Е-71 (див. рис. 7).

Рисунок 7 - Інформація про групу Е-71

 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Потім з’явиться заповнене вікно макета, рис.10.

Рисунок 10 - Завдання умов

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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