Переваги Pivot за моделлю даних

При створенні зведеної таблиці в Excel в першому ж діалоговому вікні, де нас просять встановити початковий діапазон і вибрати місце для вставки зведеної таблиці, нижче є непомітний, але дуже важливий прапорець – Додайте ці дані до моделі даних (Додайте ці дані до моделі даних) і, трохи вище, перемикач Використовуйте модель даних цієї книги (Використовуйте модель даних цієї книги):

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

Однак перш ніж розглядати ці «плюшки» зблизька, давайте спочатку розберемося, що, власне, являє собою ця модель даних?

Що таке модель даних

Модель даних (скорочено MD або DM = модель даних) — це спеціальна область у файлі Excel, де можна зберігати табличні дані — одну або кілька таблиць, зв’язаних, за бажанням, одна з одною. Насправді це невелика база даних (куб OLAP), вбудована в книгу Excel. У порівнянні з класичним зберіганням даних у формі звичайних (або розумних) таблиць на аркушах самого Excel, модель даних має кілька суттєвих переваг:

• Столи можуть бути до 2 мільярди рядків, а на аркуші Excel поміщається трохи більше 1 млн.
• Незважаючи на гігантські розміри, обробка таких таблиць (фільтрація, сортування, обчислення по них, побудова зведення та ін.) дуже швидко Набагато швидше, ніж сам Excel.
• З даними в моделі можна виконувати додаткові (за бажанням дуже складні) обчислення за допомогою вбудована мова DAX.
  
• Уся інформація, завантажена в модель даних, є дуже сильно стиснутий використовує спеціальний вбудований архіватор і досить помірно збільшує розмір вихідного файлу Excel.
  

Модель керується та обчислюється спеціальною надбудовою, вбудованою в Microsoft Excel – powerpivotпро яку я вже писав. Щоб увімкнути його, на вкладці розробник клацання Надбудови COM (Розробник — надбудови COM) і позначте відповідне поле:

Якщо вкладки розробник (розробник)ви не можете побачити його на стрічці, ви можете включити його через Файл – Параметри – Налаштування стрічки (Файл — Параметри — Налаштувати стрічку). Якщо у вікні, показаному вище, у списку надбудов COM у вас немає Power Pivot, то він не включений у вашу версію Microsoft Office 🙁

На вкладці Power Pivot, що з’явиться, буде велика світло-зелена кнопка управління (керувати), натиснувши на яке, відкриється вікно Power Pivot поверх Excel, де ми побачимо вміст моделі даних поточної книги:

Важлива примітка: робоча книга Excel може містити лише одну модель даних.

Завантажте таблиці в модель даних

Щоб завантажити дані в модель, спочатку ми перетворюємо таблицю на динамічну «розумну» комбінацію клавіш Ctrl+T і дайте йому зрозумілу назву на вкладці Конструктор (Дизайн). Це обов'язковий крок.

Тоді ви можете скористатися будь-яким із трьох методів на вибір:

• натисніть кнопку Додати до моделі (Додати до моделі даних) таб powerpivot таб Головна (Головна).
  
• Вибір команд Вставка – зведена таблиця (Вставити — зведена таблиця) і поставте прапорець Додайте ці дані до моделі даних (Додайте ці дані до моделі даних). У цьому випадку за даними, завантаженими в Модель, також відразу будується зведена таблиця.
  
• На вкладці Додатково дані (Дата) натисніть на кнопку З таблиці/діапазону (З таблиці/діапазону)щоб завантажити нашу таблицю в редактор Power Query. Цей шлях найдовший, але, при бажанні, тут можна виконати додаткову очистку даних, редагування і всілякі перетворення, в яких Power Query дуже сильний.

  Потім зведені дані завантажуються в модель за допомогою команди Головна — Закрийте та завантажте — Закрийте та завантажте в… (Додому — Закрити&Завантажити — Закрити&Завантажити до…). У вікні, що відкриється, виберіть опцію Просто створіть зв'язок (тільки створити з'єднання) і, головне, поставити галочку Додайте ці дані до моделі даних (Додайте ці дані до моделі даних).
  

Ми створюємо зведення моделі даних

Щоб побудувати зведену модель даних, ви можете використовувати будь-який із трьох підходів:

• Натисніть кнопку зведена таблиця (Зведена таблиця) у вікні Power Pivot.
• Виберіть команди в Excel Вставка – зведена таблиця і перейти в режим Використовуйте модель даних цієї книги (Вставити — зведена таблиця — використовувати модель даних цієї книги).
• Вибір команд Вставка – зведена таблиця (Вставити — зведена таблиця) і поставте прапорець Додайте ці дані до моделі даних (Додайте ці дані до моделі даних). Поточну «розумну» таблицю буде завантажено в модель, а для всієї моделі буде створено зведену таблицю.

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

Перевага 1: зв’язки між таблицями без використання формул

Звичайний підсумок можна побудувати лише з використанням даних з однієї вихідної таблиці. Якщо у вас їх кілька, наприклад продажі, прайс-лист, довідник клієнтів, реєстр договорів і т.д., то вам спочатку доведеться зібрати дані з усіх таблиць в одну за допомогою таких функцій, як VLOOKUP. (ВПР), ПОКАЖЧИК (ІНДЕКС), БІЛЬШ ЕКСПОЗИЦІЙНИЙ (МАТЧ), SUMMESLIMN (SUMIFS) і тому подібне. Це довго, виснажливо і змушує ваш Excel «роздумувати» з великою кількістю даних.

У випадку зведення моделі даних все набагато простіше. Досить один раз налаштувати зв’язки між таблицями у вікні Power Pivot – і готово. Для цього на табл powerpivot натисніть кнопку управління (керувати) а потім у вікні, що з’явиться – кнопку Перегляд діаграми (Вигляд діаграми). Залишилося перетягнути загальні (ключові) назви стовпців (полів) між таблицями для створення зв’язків:

Після цього в резюме для моделі даних ви можете кинути в область підсумків (рядки, стовпці, фільтри, значення) будь-які поля з будь-яких пов’язаних таблиць – все буде зв’язано та обчислено автоматично:

Перевага 2: підраховуйте унікальні значення

Звичайна зведена таблиця дає нам можливість вибрати одну з кількох вбудованих функцій обчислення: сума, середнє, підрахунок, мінімум, максимум тощо. У підсумку моделі даних до стандартного списку додано дуже корисну функцію для підрахунку кількість унікальних (неповторюваних значень). З його допомогою, наприклад, ви можете легко підрахувати кількість унікальних одиниць товару (асортименту), які ми продаємо в кожному місті.

Клацніть правою кнопкою миші на полі – команда Параметри поля значення і на вкладці операція Вибирати Кількість різних елементів (Окрема кількість):

Перевага 3: спеціальні формули DAX

Іноді доводиться виконувати різні додаткові обчислення у зведених таблицях. У звичайних підсумках це робиться за допомогою обчислюваних полів і об’єктів, тоді як у підсумку моделі даних використовуються показники на спеціальній мові DAX (DAX = Data Analysis Expressions).

Щоб створити міру, виберіть на вкладці powerpivot Command Міри – Створити міру (Міри — Нова міра) або просто клацніть правою кнопкою миші на таблиці у списку Pivot Fields і виберіть Додайте міру (Додати міру) в контекстному меню:

У вікні, що відкриється, встановіть:

• назва таблиціде буде зберігатися створена міра.
• Назва міри – будь-яка зрозуміла вам назва нового поля.
• Опис – необов’язковий.
• Formula – найголовніше, тому що тут ми або вручну вводимо, або натискаємо на кнопку fx і виберіть зі списку функцію DAX, яка повинна обчислити результат, коли ми потім перекинемо нашу міру в область значень.
• У нижній частині вікна ви можете відразу встановити формат числа для міри в списку Категорія.
  

Мова DAX не завжди проста для розуміння, оскільки оперує не окремими значеннями, а цілими стовпцями та таблицями, тобто вимагає деякої перебудови мислення після класичних формул Excel. Однак воно того варте, адже потужність його можливостей в обробці великих обсягів даних важко переоцінити.

Перевага 4: користувацькі ієрархії полів

Часто при створенні стандартних звітів доводиться закидати однакові комбінації полів у зведені таблиці в заданій послідовності, наприклад Рік-Квартал-Місяць-Деньабо Категорія-Товарабо Країна-Місто-Клієнт і т. д. У підсумку моделі даних ця проблема легко вирішується шляхом створення власної ієрархії — настроювані набори полів.

У вікні Power Pivot перейдіть у режим діаграми за допомогою кнопки Перегляд діаграми таб Головна (Домашня сторінка — Перегляд схеми), виберіть за допомогою Ctrl потрібні поля та клацніть їх правою кнопкою миші. Контекстне меню буде містити команду Створення ієрархії (Створити ієрархію):

Створену ієрархію можна перейменувати і перетягнути в неї мишкою потрібні поля, щоб потім одним рухом їх перекинути в підсумок:

Перевага 5: індивідуальні трафарети

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

Для цього на табл Аналіз зведеної таблиці у спадному списку Поля, елементи та набори є відповідні команди (Аналізуйте — Філдс, Іtems & Sets — створити набір на основі елементів рядка/стовпця):

У вікні, що відкриється, ви можете вибірково видалити, додати або змінити положення будь-яких елементів і зберегти отриманий набір під новим ім'ям:

Усі створені набори відображатимуться на панелі «Поля зведеної таблиці» в окремій папці, звідки їх можна вільно перетягувати в області рядків і стовпців будь-якої нової зведеної таблиці:

Перевага 6: Вибіркове приховання таблиць і стовпців

Це хоч і невелика, але в деяких випадках дуже приємна перевага. Клацнувши правою кнопкою миші назву поля або вкладку таблиці у вікні Power Pivot, ви можете вибрати команду Приховати від Client Toolkit (Сховати від інструментів клієнта):

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

Перевага 7. Розширена деталізація

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

У підсумку моделі даних цей зручний інструмент працює більш тонко. Ставши на будь-яку комірку з результатом, який нас цікавить, можна натиснути на спливаючий поруч значок з лупою (наз. Express Trends), а потім виберіть будь-яке поле, яке вас цікавить, у будь-якій пов’язаній таблиці:

Після цього поточне значення (Model = Explorer) піде в область фільтра, а зведення буде побудовано по офісах:

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

Перевага 8: Перетворення Pivot на Cube функції

Якщо вибрати будь-яку клітинку в підсумку для моделі даних, а потім вибрати на вкладці Аналіз зведеної таблиці Command Інструменти OLAP – перетворення на формули (Аналіз — Інструменти OLAP — Перетворити на формули), тоді весь підсумок буде автоматично перетворено на формули. Тепер значення полів в області рядків і стовпців і результати в області значень будуть отримані з моделі даних за допомогою спеціальних функцій куба: CUBEVALUE і CUBEMEMBER:

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

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

• Аналіз плану фактів у зведеній таблиці за допомогою Power Pivot і Power Query
• Зведена таблиця з багаторядковим заголовком
• Створіть базу даних у Excel за допомогою Power Pivot