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

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

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

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

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

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

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

Щоб пояснити застосування зведених таблиць, розглянемо при­клад.

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

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

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

Розв’язання

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

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

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

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

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

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

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

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

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

 

 

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

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

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

 

 

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

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

 

 

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

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

 

 

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

 

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

 

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

 

 

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