Аналіз даних за допомогою зведених таблиць – ЧАСТИНА 1

Зведена таблиця є аналітичним інструментом, що дозволяє підсумовувати інформацію з полів списку Excel, декількох діапазонів консолідації, реляційної бази даних або куба OLAP При створенні зведеної таблиці ви можете задати потрібні поля, спосіб організації (макет) таблиці і тип виконуваних обчислень Після побудови таблиці можна змінити її розташування для перегляду даних під іншим кутом зору Саме свобода зміни орієнтації таблиці, наприклад транспонування заголовків стовпців в заголовки рядків і навпаки, дала зведеної таблиці (pivot table) її назва і зробила її потужним аналітичним засобом

Зведена таблиця повязана з вихідними даними, і для неї підтримується автоматичне оновлення (перерахунок) через задані інтервали часу Вибір, як оновлювати дані – автоматично або на вимогу, – також у владі користувача

Знайомтеся: зведена таблиця

На рис 301 показаний список, що містить інформацію про продажі для невеликої видавничої фірми Список впорядковано по роках, кварталах, номерам в каталозі, каналами поширення, кількістю проданих книг і отриманим від продажу засобам У ньому наведено дані за вісім кварталів (2002 і 2003 роки), і фірма використовує три канали розповсюдження – всередині країни, міжнародний та замовлення поштою За допомогою Майстра зведених таблиць і діаграм ми можемо швидко перетворити цей «плоский» список в таблицю, яку легко переглядати та аналізувати Один з варіантів допустимого розташування даних в такій таблиці продемонстрований на рис 302

Рис 301 Не так просто побачити останній рядок в плоскому списку, подібному цього Але Майстер зведених таблиць і діаграм допоможе виправити становище

Рис 302 Ця зведена таблиця представляє підсумковий огляд інформації вихідного списку

Тут поля Рік і Квартал розташовані по осі стовпців таблиці, а поля Інв № і Канал – по осі рядків У таблиці виводяться підсумки продажів для кожного перетину стовпця і рядка Осередок Нб, наприклад, показує відсутність прибутку в першому кварталі 2003 року для книги з реєстраційним номером 23534, поширюваної всередині країни Величина -488 означає, що повернення книг за цей період часу з даного каналу розповсюдження перевищив обсяг продажів

Excel додатково виводить вікно, що містить список полів зведеної таблиці На рис

302 цей список включає в себе шість полів Щоб перебудувати таблицю, достатньо перетягнути значки полів на інші місця

ПРИМІТКА

Якщо присутність списку полів зведеної таблиці на екрані заважає вашій роботі, закрийте його, клацнувши на кнопці Закрити Для того щоб відновити список, клацніть на кнопці Відобразити список полів (Show Field List) на панелі інструментів Зведені таблиці

У рядках 9, 13, 17 і 21 на рис 302 відображаються проміжні підсумки для різних елементів поля Інв № Стовпець G містить проміжні підсумки для чотирьох кварталів 2002 року За кордонами малюнка знаходяться додаткові проміжні результати, а самі крайні рядок і стовпець зведеної таблиці відведені під загальні підсумки Так, якщо прокрутити таблицю з рис 302 до нижньої осередку шпальти С, ми дізнаємося підсумки продажів за перший квартал 2002 року по всіх виданням і для всіх каналів розповсюдження Майстер зведених таблиць і діаграм генерує ці

проміжні та загальні підсумки автоматично, якщо ви спеціально не вкажете зворотне Зведена таблиця, показана на рис 302, дозволяє легко знайти практично будь-яку інформацію, що міститься у вихідному списку Єдине, що не виводиться, – це

число проданих книг При бажанні ми могли б дещо ускладнити таблицю, щоб

представити цю інформацію Припустимо, що нам не потрібна занадто детальна інформація Зведена таблиця легко впорається і з цим бажанням Малюнок 303 – один з багатьох варіантів представлення тієї ж таблиці, де увага акцентується на конкретному зрізі даних Тут ми транспоновану поле Канал з осі рядків на вісь стовпців і перемістили поля Рік і Квартал на вісь сторінок Таким чином ми можемо Фільтрувати дані для конкретного року і кварталу Якщо потрібно переглянути різні часові періоди, досить вибрати потрібні значення в розкривних списках поруч з заголовками полів Рік і Квартал

Рис 303 Зведена таблиця також дозволяє акцентувати увагу на конкретному зрізі даних

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

Зведені таблиці створюються на основі даних чотирьох типів джерел:

► списку Excel

► зовнішнього джерела даних

► декількох діапазонів консолідації (окремих списків Excel, які Майстер зведених таблиць і діаграм обєднує при побудові зведеної таблиці)

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

У прикладі, який ми розглянемо далі, ми створимо зведену таблицю зі списку

Excel, показаного на рис 301

Запуск Майстра зведених таблиць і діаграм

Щоб приступити до створення зведеної таблиці, виділіть клітинку у списку, на основі якого ви хочете створити таблицю Потім виберіть у меню Дані (Data) команду Зведена таблиця (PivotTable And PivotChart Report) Excel запускає Майстер зведених таблиць і діаграм Цей майстер буде супроводжувати вас у процесі створення таблиці, підрозділяється на перераховані нижче кроки

1 Завдання типу джерела даних і виду створюваного звіту

2 Вказівка ​​місцезнаходження вихідних даних

3 Вказівка ​​місця розміщення таблиці

Коли зазначені кроки будуть виконані, за допомогою вікна Список полів зведеної таблиці (PivotTable Field List) можна задати поля, які повинні бути присутніми в таблиці, спосіб її організації і вироблені над нею обчислення

Крок 1: завдання типу джерела даних

Перше діалогове вікно Майстра зведених таблиць і діаграм представлено на рис

Рис 304 Майстер зведених таблиць і діаграм може працювати з чотирма типами джерел даних

Подивіться на малюнок у лівій частині вікна У вікні тільки два перемикача, але, щоб ви

випадково не помилилися, при виборі варіанту У зовнішньому джерелі даних (External

Data Source) тут зявиться зображення ЕОМ, передавальної дані на термінал А якщо ви встановите перемикач в положення В іншій зведеній таблиці або зведеній діаграмі, замість таблиці на малюнку зявиться зображення діаграми

Крок 2: вказівка ​​місцезнаходження вихідних даних

Після вибору типу джерела даних натисніть кнопку Далі (Next) Майстер зведених таблиць і діаграм відкриє своє друге діалогове вікно, що пропонує вказати місце, де знаходяться вихідні дані Якщо джерелом даних є список Excel і перед вибором команди Зведена таблиця ви виділили тільки одну клітинку в цьому списку, майстер самостійно заповнить це вікно, і від вас буде потрібно тільки підтвердження Вид другого діалогового вікна Майстра зведених таблиць і діаграм для випадків, коли джерелом даних служить список Excel, представлений на рис 305

Рис 305 Якщо джерелом даних є список Excel, підтвердіть або вкажіть його місцезнаходження в цьому діалоговому вікні

Якщо джерелом даних є список Excel, на початку кожного шпальти списку повинно знаходитися унікальне імя поля Якщо вихідний список знаходиться в закритій книзі Excel, натисніть кнопку Огляд (Browse)

Крок 3: зазначення місця для розміщення таблиці

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

Рис 306 Щоб помістити зведену таблицю на існуючий робочий лист,

введіть імя діапазону або посилання в текстове поле

Щоб помістити таблицю на новий робочий лист (найбезпечніший вибір), встановіть перемикач в положення Новий лист (New Worksheet) В іншому випадку виберіть варіант Існуючий лист (Existing Worksheet) і введіть імя діапазону або посилання

Перед натисненням кнопки Готово (Finish) ви можете клацнути на кнопці Параметри (Options) і в діалоговому вікні Параметри зведеної таблиці (PivotTable Options) задати деякі параметри зведеної таблиці, наприклад вказати інтервал часу для автоматичного оновлення таблиці Але в цей діалогове вікно ви завжди вправі повернутися і пізніше

РАДА

Після створення зведеної таблиці її легко перемістити в інше місце Для цього виділіть в таблиці будь-яку клітинку та в меню Дані виберіть команду Зведена таблиця Потім виберіть один з положень: Новий лист або Існуючий лист і вкажіть у текстовому полі імя діапазону або його адресу

ВИРІШЕННЯ ПРОБЛЕМ

Іноді при натисканні кнопки Готово в останньому вікні Майстра зведених таблиць і діаграм на екрані зявляється повідомлення про недостатню кількість памяті Якщо у зведеній таблиці використовується запит до зовнішніх даними, створіть її заново, але, перед тим як клацнути на кнопці Готово, натисніть кнопку Параметри та встановіть прапорець Оптимізувати память (Optimize Memory)

Якщо помилка повториться, створіть зведену таблицю ще раз і на останньому кроці клацніть на кнопці Макет (Layout) Перетягніть одне або кілька полів в область сторінок, а потім двічі клацніть на заголовку одного з полів У діалоговому вікні Обчислення поля зведеної таблиці (PivotTable Field) натисніть кнопку Додатково (Advanced) і потім встановіть перемикач в положення Оновлювати зовнішні дані при виборі кожного елемента (Query External Data Source As You Select Each Page Field Item) Якщо і ці дії не призведуть до успіху, спростите вашу таблицю, зменшивши число полів або задавши

Джерело: Ефективна робота: Microsoft Office Excel 2003 / М Додж, К Стінсон – СПб: Питер, 2005 – 1088 с: ил

Схожі статті:


Сподобалася стаття? Ви можете залишити відгук або підписатися на RSS , щоб автоматично отримувати інформацію про нові статтях.

Коментарів поки що немає.

Ваш отзыв

Поділ на параграфи відбувається автоматично, адреса електронної пошти ніколи не буде опублікований, допустимий HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

*