Агрегації – це спосіб згортання, зведення або групування даних. Коли ви починаєте з необроблених даних із таблиць або інших джерел даних, вони часто необроблені, тобто містять багато деталей, але їх жодним чином не впорядковано й не згруповано. Відсутність зведень або структури може ускладнити виявлення закономірностей у даних. Важливою частиною моделювання даних є визначення агрегувань, які спрощують, абстрагують або узагальнюють закономірності у відповіді на конкретне бізнес-питання.
Найпоширеніші агрегати, наприклад такі, що використовують функції AVERAGE,COUNT,DISTINCTCOUNT,MAX,Min або SUM , можна створити в міру автоматично за допомогою функції "Автосума". Інші типи агрегувань, як-от AVERAGEX,COUNTX, COUNTROWS або SUMX, повертають таблицю та вимагають створення формули за допомогою виразів аналізу даних (DAX).
Загальні відомості про агрегування в надбудові Power Pivot
Вибір груп для агрегації
Під час агрегації дані групуються за такими атрибутами, як товари, ціни, регіони або дати, а потім визначаються формули, які працюють з усіма даними у групі. Наприклад, коли ви створюєте підсумок для року, створюється агрегація. Якщо потім створити співвідношення за попередній рік порівняно з попереднім роком і представити їх у відсотках, це вже інший тип агрегації.
Рішення про те, як згрупувати дані, визначається бізнес-питанням. Наприклад, агрегації можуть відповідати на такі запитання:
Лічильники Скільки транзакцій було за місяць?
Середні показники Якими були середні обсяги продажів у цьому місяці за продавцями?
Мінімальне та максимальне значення Які райони продажу увійшли до п'ятірки лідерів за кількістю проданих одиниць?
Щоб створити обчислення, яке відповідатиме на ці запитання, потрібно мати докладні дані з числами для підрахунку або підсумовування, які має бути пов'язано з групами, які використовуватимуться для впорядкування результатів.
Якщо в даних ще немає значень, які можна використовувати для групування (наприклад, категорія продуктів або назва географічного регіону, де розташовано магазин), можливо, потрібно групувати дані, додаючи категорії. Під час створення груп в Excel необхідно вручну ввести або вибрати потрібні групи в стовпцях аркуша. Однак у реляційній системі ієрархії, такі як категорії для продуктів, часто зберігаються в таблицях, відмінних від таблиць фактів або значень. Зазвичай таблиця категорій зв'язується з даними фактів за певним ключем. Наприклад, ви виявили, що ваші дані містять ідентифікатори товарів, але не містять назви продуктів або їхні категорії. Щоб додати категорію до плоского аркуша Excel, потрібно скопіювати стовпець, який містить імена категорій. Надбудова Power Pivot дає змогу імпортувати таблицю категорій продуктів до моделі даних, створити зв'язок між таблицею з числовими даними та списком категорій продуктів, а потім групувати дані за категоріями. Докладні відомості див. в статті "Створення зв'язку між таблицями".
Вибір функції для агрегації
Визначивши та додавши потрібні групування, необхідно вирішити, які математичні функції використовувати для агрегування. Часто слово агрегація використовується як синонім математичних або статистичних операцій, які використовуються в агрегаціях, таких як суми, середні значення, мінімуми або кількості. Проте надбудова Power Pivot дає змогу створювати настроювані формули для агрегації, на додачу до стандартних агрегаційних функцій як у надбудові Power Pivot, так і в програмі Excel.
Наприклад, маючи такий самий набір значень і групувань, що й у попередніх прикладах, можна створити настроювані агрегації, які відповідають на такі запитання:
Відфільтровані лічильники Скільки транзакцій було за місяць, без урахування періоду обслуговування на кінець місяця?
Коефіцієнти із використанням середніх значень у різні проміжки часу Яким був відсотковий приріст або спад продажів у порівнянні з аналогічним періодом минулого року?
Згруповано мінімальне та максимальне значення Які райони збуту посіли перші місця в кожній категорії продукції або в кожному стимулі збуту?
Додавання агрегації до формул і зведених таблиць
Маючи загальне уявлення про те, як дані слід групувати, щоб вони були змістовними, і значення для роботи, ви можете вирішити, чи потрібно створити зведену таблицю або створити обчислення в межах таблиці. Надбудова Power Pivot розширює та покращує можливості програми Excel, щоб створювати агрегації, як-от суми, лічильники та середні значення. Настроювані агрегації в надбудові Power Pivot можна створювати у вікні Power Pivot або в області зведеної таблиці Excel.
- В обчислюваному стовпці можна створювати агрегації, які враховують контекст поточного рядка, щоб отримати пов'язані рядки з іншої таблиці, а потім підсумовувати, підраховувати або усереднювати ці значення в пов'язаних рядках.
- У мірі можна створювати динамічні агрегації, які використовують як фільтри, визначені у формулі, так і фільтри, накладені макетом зведеної таблиці, а також вибір роздільників, заголовків стовпців і рядків. Міри зі стандартними агрегаціями можна створити в надбудові Power Pivot за допомогою функції "Автосума" або формули. Неявні міри можна також створювати за допомогою стандартних агрегацій у зведеній таблиці в програмі Excel.
Додавання групувань до зведеної таблиці
Проектуючи зведену таблицю, перетягніть поля, які представляють групи, категорії або ієрархії, до розділу стовпців і рядків зведеної таблиці, щоб згрупувати дані. Потім перетягніть поля з числовими значеннями до області значень, щоб їх можна було підрахувати, обчислити середнє значення або підсумувати.
Якщо до зведеної таблиці додати категорії, але дані категорій не пов'язано з даними фактів, може статися помилка або дивні результати. Зазвичай надбудова Power Pivot намагається вирішити проблему, автоматично виявляючи та пропонуючи зв'язки. Докладні відомості див. в статті "Робота зі зв'язками у зведених таблицях".
Поля можна також перетягнути до роздільників, щоб вибрати певні групи даних для перегляду. Роздільники дають змогу інтерактивно групувати, сортувати та фільтрувати результати у зведеній таблиці.
Робота з групуваннями у формулі
Крім того, за допомогою групувань і категорій можна агрегувати дані, які зберігаються в таблицях. Це дає змогу створювати зв'язки між таблицями, а потім – формули, які використовують ці зв'язки для пошуку пов'язаних значень.
Іншими словами, якщо потрібно створити формулу, у якій значення групуються за категоріями, слід спочатку скористатися зв'язком, щоб з'єднати таблицю з докладними даними та таблицями з категоріями, а потім створити формулу.
Докладні відомості про створення формул, у яких використовуються підстановки, див. в статті "Підстановки у формулах Power Pivot".
Використання фільтрів в агрегаціях
Нова функція Power Pivot – можливість застосовувати фільтри до стовпців і таблиць даних не лише в інтерфейсі користувача та в зведеній таблиці або діаграмі, але й у формулах, що використовуються для обчислення агрегацій. Фільтри можна використовувати у формулах як в обчислюваних стовпцях, так і в аргументах s.
Наприклад, у нових функціях збирання DAX можна вказати як аргумент не значення, за якими потрібно сумувати або рахувати, а цілу таблицю. Якщо до таблиці не застосовано фільтри, функція збирання працюватиме для всіх значень у вказаному стовпці таблиці. Однак у формулах DAX можна створити динамічний або статичний фільтр у таблиці, щоб агрегація працювала з різними підмножинами даних залежно від умови фільтрування та поточного контексту.
Поєднуючи умови та фільтри у формулах, можна створювати агрегати, які змінюються залежно від значень, введених у формулах, або залежно від вибору рядків, заголовків і заголовків стовпців у зведеній таблиці.
Докладні відомості див. в статті "Фільтрування даних у формулах".
Порівняння функцій агрегації Excel і DAX
У таблиці нижче наведено список деяких стандартних функцій агрегування, які надає програма Excel, а також посилання на те, як їх реалізувати в надбудові Power Pivot. Ці функції у версії DAX функціонують майже так само, як і версія Excel, з деякими незначними відмінностями в синтаксисі та обробці певних типів даних.
StandardStandard Агрегатні функції
| Функція | Логічне значення |
|---|---|
| AVERAGE | Ця функція повертає середнє (середнє арифметичне) усіх чисел у стовпці. |
| AVERAGEA | Ця функція повертає середнє (середнє арифметичне) усіх значень у стовпці. Обробляє текстові та нечислові значення. |
| COUNT | Підраховує кількість числових значень у стовпці. |
| COUNTA | Підраховує кількість непустих значень у стовпці. |
| MAX | Повертає найбільше числове значення у стовпці. |
| MAXX | Повертає найбільше значення з набору виразів, обчислених у таблиці. |
| MIN | Повертає найменше числове значення у стовпці. |
| ПУСТУНКА | Повертає найменше значення з набору виразів, обчислених у таблиці. |
| SUM | Додає всі числа у стовпці. |
Функції агрегації DAX
DAX включає функції агрегування, які дають змогу вказати таблицю, для якої потрібно виконати агрегацію. Таким чином, замість простого додавання або усереднення значень у стовпці ці функції дають змогу створювати вирази, які динамічно визначають дані для агрегації.
У таблиці нижче наведено список функцій агрегування, доступних у DAX.
| Функція | Логічне значення |
|---|---|
| УСЕРЕДНЕНЕ ЗНАЧЕННЯ | Усереднює набір виразів, обчислених у таблиці. |
| COUNTAX | Підраховує кількість виразів, обчислених у таблиці. |
| COUNTBLANK | Підраховує кількість пустих значень у стовпці. |
| COUNTX (КІЛЬКІСТЬ КОРИСТУВАЧІВ) | Підраховує загальну кількість рядків у таблиці. |
| COUNTROWS (КІЛЬКІСТЬ РЯДКІВ) | Рахує кількість рядків, повернутих вкладеною функцією таблиці, наприклад функцією FILTER. |
| SUMX (СУМА) | Повертає суму сукупності виразів, обчислених у таблиці. |
Відмінності між функціями агрегування DAX і Excel
Хоча ці функції мають ті ж імена, що й їхні аналоги в Excel, вони використовують вбудований у пам'ять механізм аналітики Power Pivot і були переписані для роботи з таблицями та стовпцями. У книзі Excel не можна використовувати формулу DAX і навпаки. Їх можна використовувати лише у вікні Power Pivot і у зведених таблицях, створених на основі даних Power Pivot. Крім того, хоча функції мають однакові назви, поведінка може дещо відрізнятися. Докладні відомості див. в окремих довідкових статтях.
Спосіб обчислення стовпців в агрегації відрізняється від того, як програма Excel обробляє агрегації. Для прикладу можна навести приклад.
Припустімо, що вам потрібно отримати суму значень у стовпці "Сума" таблиці "Продажі", тому потрібно створити таку формулу:
=SUM('Sales'[Amount])
У найпростішому випадку функція отримує значення з одного невідфільтрованого стовпця, а результат такий самий, як і в програмі Excel, яка завжди просто підсумовує значення у стовпці «Сума». Проте в надбудові Power Pivot формула інтерпретується так: "Отримайте значення в "Сумі" для кожного рядка таблиці "Продажі", а потім підсумуйте ці окремі значення. Надбудова Power Pivot обчислює кожен рядок, у якому виконується агрегація, і обчислює одне скалярне значення для кожного рядка, а потім агрегує ці значення. Таким чином, результат формули може відрізнятися, якщо до таблиці застосовано фільтри або якщо значення обчислюються на основі інших агрегацій, які можуть бути відфільтровані. Докладні відомості див. в статті "Контекст у формулах DAX".
Функції часового аналізу DAX
На додаток до функцій збирання таблиць, описаних у попередньому розділі, мова DAX містить функції агрегування, які працюють із зазначеними датами й часом для забезпечення вбудованого часового аналізу. Ці функції використовують діапазони дат для отримання пов'язаних значень і агрегації значень. Ви також можете порівняти значення в різних діапазонах дат.
У таблиці нижче наведено функції часового аналізу, які можна використовувати для агрегації.
| Функція | Логічне значення |
|---|---|
|
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR |
Обчислює значення на кінець календаря певного періоду. |
|
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR |
Обчислює значення на кінець календаря періоду до заданого періоду. |
|
TOTALMTD TOTALYTD TOTALQTD |
Обчислює значення за інтервал, який починається в перший день періоду та завершується найпізнішою датою у вказаному стовпці дат. |
Інші функції, наведені в розділі "Часовий аналіз" (Функції часового аналізу) – це функції, які можна використовувати, щоб отримувати дати або настроювані діапазони дат для використання в агрегації. Наприклад, за допомогою функції DATESINPERIOD можна повернути діапазон дат і використати цей набір дат як аргумент до іншої функції, щоб обчислити настроюване агрегування лише для цих дат.