зміст
Слайсери у зведених таблицях можна використовувати не тільки класичним способом – для фільтрації вихідних даних, а й для перемикання між різними типами обчислень в області значень:
Реалізувати це дуже просто – все, що вам потрібно, це пара формул і допоміжна таблиця. Що ж, все це ми зробимо не в звичайному резюме, а в резюме, побудованому за моделлю даних Power Pivot.
Крок 1. Підключення надбудови Power Pivot
Якщо вкладки надбудови Power Pivot не відображаються у вашому Excel, спершу її потрібно ввімкнути. Для цього є два варіанти:
- таб розробник - кнопка Надбудови COM (Розробник — надбудови COM)
- Файл – Параметри – Надбудови – Надбудови COM – Перейти (Файл — Параметри — Надбудови — Надбудови COM — Перейти до)
Якщо це не допомогло, спробуйте перезапустити Microsoft Excel.
Крок 2. Завантажте дані в модель даних Power Pivot
В якості початкових даних у нас буде дві таблиці:
Перший – це таблиця з продажами, за якою ми згодом будемо будувати зведення. Друга – допоміжна таблиця, куди вносяться назви для кнопок майбутнього зрізу.
Обидві ці таблиці потрібно перетворити на «розумні» (динамічні) за допомогою комбінації клавіш Ctrl+T або команда Домашня сторінка – Формат у вигляді таблиці (Домашня сторінка — форматувати як таблицю) і бажано давати їм розумні назви на табл Конструктор (Дизайн). Нехай, наприклад, Sales и Послуги.
Після цього кожну таблицю по черзі потрібно завантажити в модель даних – для цього ми використовуємо вкладку powerpivot button Додати до моделі даних (Додати до моделі даних).
Викликаються обчислювані поля у зведеній таблиці за моделлю даних заходи. Створимо такт, який буде відображати назву натиснутої кнопки на майбутньому зрізі. Для цього в будь-якій з наших таблиць виділіть будь-яку порожню комірку в нижній панелі обчислень і введіть у рядок формул таку конструкцію:
Тут назва міри стоїть на першому місці (Натиснута кнопка), а потім після двокрапки та знака рівності формула для обчислення за допомогою функції ЗНАЧЕННЯ DAX, вбудований у Power Pivot.
Якщо повторити це не в Power Pivot, а в Power BI, то двокрапка не потрібна, а замість неї ЗНАЧЕННЯ ви можете використовувати його більш сучасний аналог – функцію SELECTEDVALUE.
Ми не звертаємо увагу на помилки в нижній частині вікна, які з’являються після введення формули – вони виникають, тому що у нас ще немає підсумку і зрізу, в якому щось натискається.
Наступним кроком є створення міри для різних варіантів розрахунку в залежності від значення попередньої міри Натиснута кнопка. Тут формула трохи складніша:
Давайте розберемо це по частинах:
- функція ВИМИКАЧ – аналог вкладеного IF – перевіряє виконання заданих умов і повертає різні значення в залежності від виконання деяких з них.
- функція ІСТИНА() – дає логічне значення «істина», щоб умови, перевірені пізніше функцією SWITCH, працювали, лише якщо вони виконані, тобто істина.
- Потім ми перевіряємо значення показника натиснутої кнопки та обчислюємо кінцевий результат для трьох різних варіантів – як суму вартості, середнього чека та кількості унікальних користувачів. Щоб підрахувати унікальні значення, використовуйте функцію DISTINCTCOUNT, а для округлення – КРУГЛИЙ.
- Якщо жодна з трьох вищезазначених умов не виконується, то відображається останній аргумент функції SWITCH – ми встановлюємо його як фіктивну за допомогою функції БЛАНК().
Крок 5. Побудова резюме та додавання зрізу
Залишилося повернутися з Power Pivot до Excel і створити там зведену таблицю для всіх наших даних і заходів. Для цього у вікні Power Pivot увімк Головний команда вибору вкладки зведена таблиця (Домашня сторінка — зведена таблиця).
Тоді:
- Кидаємо поле Product З таблиці Sales до області ряди (Рядки).
- Кидаючи туди поле Результат З таблиці Послуги.
- Клацніть правою кнопкою миші на полі Результаті вибрати команду Додайте як шматочок (Додати як роздільник).
- Метання другого такту Висновок З таблиці Послуги до області Цінності (Значення).
Ось, власне, і всі хитрощі. Тепер ви можете натискати на кнопки зрізу – і підсумки у зведеній таблиці перемикаються на потрібну вам функцію.
Краса 🙂
- Переваги Pivot за моделлю даних
- Аналіз плану-факту у зведеній таблиці на Power Pivot
- Створіть базу даних у Excel за допомогою надбудови Power Pivot