Створення Зведених таблиць (PivotTables)

Опис

Зведена таблиця (Pivot Table) — це потужний інструмент для обчислення, узагальнення та аналізу даних, який дає змогу переглядати порівняння, закономірності та тенденції у ваших даних.

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

Приклад задачі

Припустимо, що для аналізу кредитного портфелю з бази даних в Excel вивантажується ось-така таблиця:

У ній кожний окремий рядок містить повну інформацію про одну кредитну угоду:

• назва позичальника;
• номер угоди;
• дата видачі та дата погашення по кредиту;
• тип кредиту;
• сума кредиту.

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

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

Відповіді на всі питання можна отримати легше, ніж Ви думаєте. Хвилини так за три, приблизно. За допомогою одного з найбільш приголомшливих інструментів Microsoft Excel — Зведених таблиць.

Поїхали...

Підготовка даних

Візьміть до уваги наступні поради при підготовці даних перед створенням Зведених таблиць:

• використовуйте чисті табличні дані для досягнення найкращих результатів;
• розташовуйте дані в стовпцях, а не в рядках;
• переконайтеся, що всі стовпці мають заголовки з одним рядком унікальних непорожніх підписів для кожного стовпця. Уникайте подвійних рядків заголовків або об'єднаних клітинок;
• відформатуйте дані як "розумну таблицю Excel" (клацніть будь-де на ваших даних, а потім виберіть «Вставити» - «Таблиця» (Insert - Table) в меню-стрічці (ribbon);
• якщо у вас є складні або вкладені дані (наприклад, крос-таблиця), використовуйте Power Query для їх перетворення (наприклад, можна скористатися макросом, щоб скасувати зведення даних), щоби вони були організовані в стовпці з одним рядком заголовка.

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

Виділяємо усю нашу таблицю з даними і тиснемо в меню Вставка - Зведена таблиця (Insert - PivotTable).

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

По замовчуванню Ексель вибирає помістити зведену таблицю на новий аркуш (New Worksheet), проте ми можемо, у разі необхідності, вручну вибрати поточний або будь-який інший аркуш книги (Existing Worksheet). Краще для цього вибирати новий аркуш - тоді немає ризику що зведена таблиця "перекриється" з вихідним списком і ми отримаємо купу циклічних посилань. Тиснемо кнопку OK і переходимо до найцікавішого — етапу конструювання нашого звіту.

Робота з макетом

Те, що Ви побачите, натиснувши кнопку OK називається макет (layout) зведеної таблиці:

Працювати з ним нескладно - треба перетягувати мишею назви стовпців (полів) з вікна Списку полів зведеної таблиці (PivotTable Field List) в області:

• рядків (Row Labels);
• стовпців (Column Labels);
• фільтрів (Report Filter);
• даних макета (Values).

Єдиний нюанс - робіть це точніше, не промахніться! Поїхали...

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

Форматування Зведеної таблиці

Вибираємо форматування зведеної таблиці в меню PivotTables Tools - Design:

Та отримуємо наступний вигляд:

Не так вже це все і складно, чи не так?

Недоліки

Єдиний недолік зведених таблиць - відсутність автоматичного оновлення (перерахунку) при зміні даних у вихідному списку. Для виконання такого перерахунку необхідно клацнути по зведеній таблиці правою кнопкою миші і вибрати в контекстному меню команду Оновити (Refresh).

Статті по темі:

• Перебудова таблиць (Unpivot)
• PowerPivot - додаток для розширення можливостей Зведених таблиць