Від моделі даних до звітності

Модель визначає, що можна поєднувати. Якщо між двома таблицями існує шлях зв'язку, будь-яка зведена таблиця може поєднувати поля з обох — без необхідності у формулах. Якщо шляху немає, з'єднання виконати неможливо.

Три бізнес-питання

1. Який регіон приносить найбільший дохід?

• Source table: Customers;
• Rows: Region;
• Columns: Category (Products);
• Values:Total Sales (measure).

2. Яка динаміка продажів по місяцях?

• Source table: Dates;
• Rows: Year, then Month Name;
• Values: Total Sales (measure).

3. Як порівнюються сегменти клієнтів?

• Source table: Customers;
• Rows: Segment;
• Values: Total Sales, Transaction Count, Average Order Value.

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

Сирі числа у зведеній таблиці важко читати, особливо при спільному використанні з зацікавленими сторонами. Застосування грошового формату до будь-якої грошової міри безпосередньо у зведеній таблиці:

1. Клікніть будь-яку клітинку у стовпці міри, яку потрібно відформатувати;
2. Перейдіть до PivotTable Analyze → Field Settings;
3. Натисніть Number Format у нижній частині діалогового вікна;
4. Виберіть Currency, оберіть відповідний символ і натисніть OK.

Коректне сортування назв місяців

Назви місяців є текстовими значеннями. Excel за замовчуванням сортує текст в алфавітному порядку — що розміщує April перед January і February перед March. У будь-якій часовій зведеній таблиці це потрібно виправити, щоб дані мали сенс.

1. Клацніть правою кнопкою миші будь-яку назву місяця в області рядків зведеної таблиці
2. Виберіть Sort → More Sort Options;
3. Оберіть Ascending для сортування від January до December;
4. Для повного контролю над порядком використовуйте опцію Manual, щоб перетягнути місяці у правильній послідовності.

Модель як рушій звітності

Кожна з трьох зведених таблиць одночасно використовує різні таблиці моделі. Зведена таблиця 1 поєднує Customers, Products і Sales в одному поданні.

До моделювання даних об'єднання підсумків Region, Category та Sales в одній таблиці вимагало формул VLOOKUP або SUMIFS, які потрібно було переписувати щоразу при зміні даних. Завдяки моделі той самий результат досягається простим перетягуванням трьох полів у зведену таблицю — і вона автоматично оновлюється при завантаженні нових даних.

Завдання

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

Зведена таблиця 1 — Дохід за сегментом і категорією (назва аркуша: PT_Task1)

Бізнес-питання: Який сегмент клієнтів генерує найбільший дохід і чи відрізняється розподіл по категоріях у різних сегментах?

Вставте зведену таблицю на основі моделі (Вставка → Зведена таблиця → Використати модель даних цієї книги) на новий аркуш з назвою PT_Task1, далі:

• Додайте Segment з таблиці Customers до Рядків.
• Додайте Category з таблиці Products до Стовпців.
• Додайте міру [Total Sales] з таблиці Sales до Значень.
• Відформатуйте значення як валюту з двома знаками після коми.

Зведена таблиця 2 — Кількість транзакцій по місяцях (назва аркуша: PT_Task2)

Бізнес-питання: Скільки замовлень було зроблено щомісяця і в якому кварталі був найбільший обсяг?

Вставте другу зведену таблицю на основі моделі на новий аркуш з назвою PT_Task2, далі:

• Додайте Quarter з таблиці Dates до Рядків.
• Додайте MonthName з таблиці Dates до Рядків, під Quarter.
• Додайте міру [Transaction Count] з таблиці Sales до Значень.
• Відформатуйте значення як цілі числа (без десяткових знаків).

Перевірте: підсумки по кварталах повинні дорівнювати сумі місячних значень Transaction Count у цьому кварталі. Якщо це не так, перевірте, чи правильно вкладені рядки (Quarter зовнішній, MonthName внутрішній).

Зведена таблиця 3 — Три міри по регіонах (назва аркуша: PT_Task3)

Бізнес-питання: Як чотири регіони порівнюються за загальним доходом, кількістю замовлень і середнім розміром замовлення?

Вставте третю зведену таблицю на основі моделі на новий аркуш з назвою PT_Task3, далі:

• Додайте Region з таблиці Customers до Рядків.
• Додайте [Total Sales], [Transaction Count] і [Avg Order Value] з таблиці Sales до Значень.
• Відформатуйте Total Sales і Avg Order Value як валюту. Відформатуйте Transaction Count як ціле число.