Практичне заняття №2
Аналіз даних на основі зведених таблиць
1 Поняття про зведених таблицях
Зведена таблиця (СТ) дозволяє швидко витягти з великих баз даних необхідну інформацію, завдяки її можливості одночасно виконувати різні операції (підведення підсумків, сортування і фільтрацію). Застосовувати інструмент СТ рекомендується для великих таблиць, де є різні повторення значень в стовпцях і (або) рядках.
Майстер зведених таблиць
Для побудови і модифікації СТ використовується Майстер своднихтабліц. викликається за допомогою команди Дані → Зведена таблиця. Перед побудовою зведеної таблиці необхідно прибрати всі раніше створені проміжні підсумки і накладені фільтри.
Побудова СТ виконується в чотири етапи (кроки):
На першому кроці пропонується вибрати джерело даних для побудови таблиці. Створити СТ можна на основі даних, що знаходяться в одному з нижченаведених джерел:
а) в списку або базі даних будь-якого листа Microsoft Excel.
б) в зовнішньому джерелі даних, яким може бути база даних, текстовий файл або будь-який інший джерело, крім книги Microsoft Excel.
в) в декількох діапазонах консолідації, тобто в декількох областях одного або більше листів Excel. При цьому списки і листи повинні мати однакові заголовки рядків і стовпців.
г) в інший зведеної таблиці, яку можна використовувати для створення на її основі нової СТ. Нова СТ буде пов'язана з тими ж вихідними даними. При оновленні даних СТ - джерела, інша СТ також оновлюється.
На другому кроці в діалоговому вікні вказується діапазон списку або бази даних, які повинні бути зведені.
У загальному випадку повне ім'я діапазону задається у вигляді: [імя_ книги] імя_ліста! Інтервал;
Якщо СТ будується в тій же книзі, де знаходиться вихідна таблиця, то ім'я книги вказувати не обов'язково.
На третьому кроці в макеті таблиці створюється структура СТ і визначаються її функції. Макет представлений в центрі вікна і складається з областей: рядок, стовпець, сторінка і дані. Праворуч від макета відображаються всі імена полів (заголовки стовпців) в заданому інтервалі вихідної таблиці.
Розміщення полів в певну область макета виконується шляхом їх "перетягування" при натиснутій лівій кнопки миші. Щоб видалити поле з області макета, його перетягують за межі макета. Видалення поля призведе до приховування в СТ всіх залежних від нього величин, але не вплине на вихідні дані.
Кожне поле в областяхстолбец, рядок, сторінка може розміщуватися лише один раз, а в області дані воно може і повторитися з різними підсумковими функціями.
Кожна область макета, куди розміщуються поля вихідної таблиці, має своє призначення, що визначає зовнішній вигляд СТ і її функції:
Знайомство зі зведеними таблицями на прикладі.
1 .Создайте на першому аркуші книги таблицю продажів:
У таблиці видно хто, що, коли і на яку суму продав. Дізнаємося загальну суму продажів по кожному продавцеві, створивши зведену таблицю.
1. Клацніть курсором на осередок A1, щоб Excel зрозумів з даними ми збираємося працювати.
2. Виберіть в меню Дані → Зведена таблиця. Далі з'явиться серія діалогів, для настройки зведеної таблиці.
3. У першому діалозі потрібно вказати параметри зведеної таблиці. Нічого не міняйте в ньому і натисніть Далі.
4. Другий діалог попросить виділити дані для зведеної таблиці. Excel сам виділив всю нашу таблицю (тому що у нас курсор стоїть на осередку A1), нічого не міняйте тут і натисніть Далі.
5. У третьому діалозі потрібно вказати, де будемо створювати зведену таблицю. Виберіть прапор Існуючий лист, потім клацніть осередок F1 і натисніть готово.
Тепер на нашому листі з'явилася форма зведеної таблиці і список полів.
Форма зведеної таблиці містить три області для перетягування в них полів: для стовпців, для рядків і для даних.
З діалогу Список полів зведеної таблиці перетягніть поле Прізвище в область Перетягніть сюди поля рядків. Форма зведеної таблиці трохи змінилася.
Excel вибрав все унікальні значення стовпця Прізвище та розташував їх у рядках нашого майбутнього звіту.
Тепер перетягніть поле Сума в область даних.
Excel порахував суму продажів для кожного продавця. Але для більшого розуміння можливостей зведеної таблиці вкажемо ще область стовпців. Ця область здається тепер прихованої, але насправді її видно. Перенесіть поле Дата на правий верхній кут звіту, ось сюди:
Як тільки ви відпустите кнопку миші, звіт відразу зміниться, показавши динаміку продажів по днях для кожного продавця.
Звіт вийшов занадто докладним. Згрупуємо його по місяцях.
1. Клацніть правою кнопкою по полю звіту Дата, в меню оберіть Група і структура, потім Групувати.
2. У наступному діалозі Excel просить нас вказати, як саме групувати і відразу пропонує по місяцях (виділено). Нічого не змінюємо і натискаємо ОК.
Тепер дата продажу в звіті згрупована по місяцях.
Щоб змінити звіт, наприклад, замінити поле рядків, просто перетягніть це поле зі звіту за його межі. А потім перенесіть туди інше поле, наприклад, поле Продукт.
Отримали звіт з продажу продуктів по місяцях.
А ось що вийде, якщо ми замінимо поле Дата на Прізвище.
Можливості зведених таблиць здаються безмежними. Отримайте наступну зведену таблицю: