Звідні таблиці
Звідна
таблиця покликана допомогти користувачеві в інтерактивному режимі упорядкувати і узагальнити велику кількість даних, приведених в списках, таблицях і в базах даних. Перегляд великих таблиць вимагає значних витрат часу. Звідні таблиці плануються так, щоб наочно відображувати інформацію, що цікавить користувача. На їх основі можна створити діаграму, яка відображуватиме всі зміни, що сталися.
Якщо звичайні таблиці можуть бути лише двовимірними, то звідні
таблиці багатовимірні, що дозволяє уникнути дублювання даних. Створюючи
звідну таблицю, користувач вказує, які Поля і які елементи мають
бути представлені в ній. Наприклад, якщо у вас є списки товарів, які
продаються в різних магазинах, то назви товарів утворюватимуть поля, а їх
конкретна кількість в кожному магазині — елементи. Дані по магазинах, які
розташовані в різних містах, можна розташувати на окремих листах. Звідна таблиця допоможе вам проаналізувати сумарний продаж конкретних товарів по тижнях, місяцях, кварталах, позбавить від необхідності переглядати все наявні списки. У звідну таблицю можна включати проміжні і підсумкові суми, розрахункові поля.
Нові дані вносяться до вихідних таблиць, а звідні таблиці призначені лише для читання. Після створення звіту звідної таблиці її структуру можна змінити, перетягуючи поля і елементи за допомогою миші. Звідна таблиця дозволяє узагальнити і проаналізувати дані, які знаходяться в зовнішніх джерелах даних, створених без використання Excel. Для наочнішого відображення даних, що містяться в звідній таблиці, можна на їх основі створити діаграму. При створенні звідної таблиці можна використовувати базу даних, наприклад, таблицю, створену в Access.
Створення звідної таблиці
Як приклад розглянемо створення звідної таблиці, що дозволяє на основі таблиць з вихідними даними виконати аналіз продажу певних товарів в різних містах Росії. У книзі, приведеній на мал. 18.16, показаний продаж декількох моделей автомобілів: Волга, Жигулі, Ока в різних містах Росії: у Москві, Саратові і Тулі. Кожне місто показане на окремому аркуші. Передбачається, що звідні таблиці складаються по чотирьох місяцях: січень, лютий, березень і квітень. Таблиці відформатували з використанням команди
Автоформат (AutoFormat) у меню Формат (Format) . Вибраний зразок з підписом
Простій (Simple) .
Мал. 18.16
Вихідний список для складання звідної таблиці
Створення
звідної таблиці бажано почати з виділення вічка усередині використовуваного списку (це дозволяє автоматично виділити діапазон, що містить вихідні дані).
Положенням перемикача в групі Створити таблицю на основі даних, що знаходяться: (Create Pivot table from data in:)
встановите перемикач в положення: у декількох діапазонах консолідації (Multiple consolidation ranges) оскільки джерела даних для створення звідної таблиці, розташовані на різних аркуші Excel.
Призначення інших положень перемикача:
- у списку або базі даних Microsoft Office Excel (Microsoft Office Excel list or Database ) — дозволяє створити
звіт звідної таблиці або звідної діаграми за даними рядків або стовпців
з підписами, розташованих на одному аркуші Excel;
- у зовнішньому джерелі даних (External data source) — дозволяє створити звіт звідної таблиці або звідної
діаграми за даними зовнішнього файлу або бази даних, наприклад,
Microsoft Access, SQL Server, Paradox;.
- у декількох діапазонах
консолідації — дозволяє створити звіт звідної таблиці або звідної
діаграми за даними рядків або стовпців з підписами, розташованих в
декількох діапазонах консолідації;
- у іншій звідній таблиці або звідній діаграмі (Another PivotTable report or Pivot Chart report) — використовується для створення звіту звідної таблиці або звідної діаграми за даними іншого звіту звідної таблиці в активній книзі.
Мал. 18.17
Вікно майстра звідних таблиць
У розділі Вигляд створюваного звіту (What kind of report do you want to create?) поставте перемикач в положення
звідна таблиця (PivotTable) для створення лише звідної таблиці і натискуйте кнопку
Далі (Next).
Якщо вихідні дані розташовані на декількох листах, то в наступному діалоговому вікні
Майстер звідних таблиць і діаграм — крок 2а з 3 (PivotTable and Pivot Chart Wizard — Step 2a of3) поставте перемикач в положення
Створити одне поле сторінки (Create а single page field for ті) оскільки всі листи аналогічні і відрізняються лише містом, в якому реалізовувалася продукція. Натискуйте кнопку
Далі (Next) .
На екрані відображуватиметься діалогове вікно Майстер звідних таблиць і діаграм — крок 26 з 3 (PivotTable and PivotChart Wizard — Step 2b of 3) . Клацніть мишею в полі
Діапазон (Range) (мал. 18.18). Виділите по черзі на всіх листах вічка з А1 по Е4, і натискуйте кнопку
Додати (Add) після кожного виділення для додавання діапазону до списку вихідних діапазонів. Заслання на вихідну область буде додано в список
Всі заслання (All references). Кнопка згортання вікна праворуч від поля дозволяє згортати діалогове вікно для
виділення кожного діапазону. Повторне натиснення на цю кнопку відновлює вікно.
Можна
зрушити діалогове вікно, аби був видний один з кутів діапазону, що виділявся. Поки йде виділення діапазону діалогове вікно автоматично згортається.
Мал.
18.18 Виділення
діапазонів таблиць, що підлягають консолідації
Якщо вихідна таблиця знаходиться в іншій книзі, до неї можна перейти за допомогою кнопки
Огляд (Browse) . Закінчивши вибір даних для звіту звідної таблиці, натискуйте кнопку
Далі (Next).
На останньому кроці майстра звідних таблиць і діаграм вам запропонують положенням перемикача задати місце, де слід помістити звідну таблицю (мал. 18.19):
- новий аркуш (New worksheet) — звіт
звідної таблиці буде розміщений на новому аркуші тієї ж книги;
- існуючий аркуш (Existing worksheet) —
звіт звідної таблиці буде розміщений на тому ж аркуші, де знаходиться вихідна таблиця. Кнопка згортання діалогового вікна праворуч від поля введення тимчасово прибирає діалогове вікно з екрану, що дозволяє вказати діапазон вічок, де буде розташована звідна таблиця, шляхом виділення вічок на аркуші. Після цього можна натискувати цю кнопку ще раз для відновлення діалогового вікна.
Кнопку Готово (Finish) доцільно натискувати перш, ніж кнопку Макет (Layout) в наступних випадках:
- використовуються зовнішні дані
з низькою швидкістю завантаження і оновлення;
- планується створення поля сторінки і потрібно задати параметр запиту для кожного елементу окремо;
Мал. 18.19
Вибір місця розташування звідної таблиці
- планується створити декілька полів даних і потрібно вказати порядок їх відображення.
Звідну таблицю (мал. 18,20) можна створити шляхом перетягання заголовків полів в необхідну зону аркуша. Для деяких зовнішніх джерел даних, особливо для великих баз даних, .это може виявитися зручнішим в порівнянні з налаштуванням макету безпосередньо на аркуші. Так, якщо звіт створюється на основі даних куба (за допомогою майстра створення куба в Microsoft Query), налаштування макету в діалоговому вікні може значно скоротити час, який потрібний для витягання даних. Тут же можна вибрати параметри для створення полів сторінок, аби дані елементів витягувалися окремо. Параметри полів сторінок доступні лише в тому випадку, якщо джерелом даних звіту не є куб.
Мал. 18.20
Завдання місця розташування полів звідної таблиці
Вправи
1. Складіть список замовлень книг для постачання в різні бібліотеки декількох міст. Проаналізуйте сумарні замовлення на конкретні книги по містах.
2. Складіть список працівників вашої фірми з основними анкетними даними, використовуючи форму (мал. 18.4). Виконаєте пошук даних і їх сортування по заданих критеріях.
3. Створіть звідну таблицю з продажу товарів трьох найменувань за чотири місяці: січень, лютий, березень і квітень для трьох міст: Тула, Орел і Пенза. Відформатуйте таблицю за допомогою команди
Автоформат (AutoFormat) у меню Формат (Format). Перейменуйте ярлички листів по назвах міст. Порахуйте виручку (у тисячах рублів) по місяцях і товарах в різних містах і загальну виручку.
Мал. 18.21
Діалогове вікно, використовуване для автоматичного обчислення полів звідної таблиці
Таблиці з даними магазинів матимуть вигляд:
Таблиця №1 місто Тула
|
Січень
|
Лютий
|
Березень
|
Апрель
|
Овочі
|
20
|
30
|
14
|
23
|
Фрукти
|
30
|
48
|
15
|
24
|
Ягоди
|
25
|
24
|
16
|
25
|
Таблиця №2 місто Орел
|
Січень
|
Лютий
|
Березень
|
Апрель
|
|
31
|
21
|
31
|
25
|
Д)п\лкты
|
32
|
22
|
32
|
23
|
Ягоди
|
33
|
23
|
33
|
24
|
Таблиця №3 місто Пенза
|
Січень
|
Лютий
|
Березень
|
Апрель
|
Овочі
|
12
|
24
|
24
|
23
|
Фрукти
|
14
|
21
|
45
|
33
|
Ягоди
|
17
|
26
|
44
|
32
|
Роботу виконаєте в наступному порядку:
- Клацніть мишею у вічку А2.
Введіть текст Овочі. Аналогічно у вічка A3 і А4 введіть відповідно
Фрукти і Ягоди.
- Виділите вічка з А1 по Е4 і виберіть команду Автоформат (AutoFonnat) у меню
Формат (Format).
- У списку форматів виберіть Класичний! (Classicl) і
натискуйте кнопку ОК.
- Клацніть правою кнопкою миші по ярлику першого аркуша і виберіть в контекстному меню команду
Переместіть/ськопіровать (Move or Copy) . і встановите в діалоговому вікні, що з'явилося, прапорець
Створити копію (Create а сміттю) .
- Аналогічним чином створіть
третю копію аркуша.
- Клацніть правою кнопкою миші по ярлику Аркуш 1(3) (Sheet 1(3)). Виберіть команду
Перейменувати (Rename). У полі з ім'ям аркуша
введіть Тула. Аналогічним чином двом іншим листам з таблицею привласніть
назви міст Орла, Пенза.
- Заповните дані по реалізації
продукції по кожному з трьох міст, як показано в таблицях 1, 2 і 3
нижче.
- Для побудови звідної таблиці виберіть в меню Дані (Data) команду
Звідна таблиця (Pivot Table and PivotChart Report). Встановите перемикач в
положення В декількох діапазонах консолідації (Multiple consolidation
ranges), оскільки дані розташовані на декількох листах книги і
натискуйте кнопку Далі.
- У наступному діалоговому вікні Майстер звідних таблиць і діаграм — крок 2а з 3 (PivotTable and PivotChart Wizard — Step 2a of3)
поставте перемикач в положення Створити одне поле сторінки (Create а single page field for me) оскільки всі листи аналогічні і відрізняються лише містом, в якому реалізовувалася продукція. Клацніть мишею в полі Діапазон (Range). Виділите по черзі на всіх листах вічка з Al no E4, і натискуйте кнопку
Додати (Add) після кожного виділення. Кнопка згортання вікна праворуч від поля дозволяє згортати діалогове вікно для виділення кожного діапазону. Повторне натиснення на цю кнопку відновлює вікно. Потім натискуйте кнопку
Далі (Next).
- Поставте перемикач в положення Помістити таблицю на новий аркуш (New worksheet) і натискуйте кнопку
Готово (Finish ).
- У звідній таблиці, що з'явилася, двічі клацніть по полю із словом Рядок (Row) . Відкриється діалогове вікно
Обчислення звідної таблиці PivotTable Field) (мал. 18.21). Введіть слово Товар замість Рядок
і натискуйте ОК. Аналогічно Стовпець поміняйте на Місяць, а сторінка на
Місто. Після створення звідної таблиці значення доходів підсумовані по
місяцях і назвах товарів. Для аналізу доходів по різних містах відкрийте
список міст, що розкривається, у вічку В1, виділите те місто який вас
цікавить і натискуйте кнопку ОК. Ви отримаєте підсумовані дані по даному
місту.
- Клацніть за списком, що розкривається, Товари і зніміть галочок поряд з тими товарами, які вас не цікавить. У таблиці відображуватимуться звідні дані без цього продукту.
Виводи
1. Аби упорядкувати дані по декількох полях, виділите діапазон вічок, який необхідно відсортувати, і виберіть команду
Сортування (Sort) у меню Дані (Data).
2. Аби спростити введення і редагування даних при складанні списків в Excel, встановите курсор в одному з вічок списку і виберіть в меню
Дані (Data) команду Форма (Form).
3. Для
прогнозування залежності виділите діапазон вічок, що містить вихідні значення, і використовуйте діалогове вікно, що відображується після вибору в меню Правка (Edit)
команди Заповнити (Fill), Прогресія (Series) .
4. Знайти
аргумент, що забезпечує результат, що задається, дозволяє команда Підбір параметра (Goal Seek ) у меню
Сервіс (Tools). Рішення знаходиться шляхом послідовних ітерацій.
5. Аби узагальнити однорідні дані, розташовані в декількох областях таблиці або на різних листах, в одній таблиці, вкажіть верхнє ліве вічко кінцевої області, де мають бути поміщені консолідовані дані, і виберіть команду
Консолідація (Consolidate) у меню Дані (Data) .
6. Аби створити звідну таблицю, виберіть команду Звідна таблиця (Pivot Table and PivotChart Report) у меню
Дані (Data) . Майстер звідних таблиць полегшує обробку великих масивів даних і здобуття підсумкових результатів в зручному вигляді.
|