Агрегування в надбудові Power Pivot

Увага! : Цю статтю перекладено за допомогою служби машинного перекладу; див. застереження. Версію цієї статті англійською мовою див. тут для отримання довідки.

Збирання – це спосіб згортання, підсумовування чи групування даних. Якщо почати працювати з необробленими даними з таблиць чи інших джерел даних, дані часто однорівневі, тобто подробиць багато, але їх не було організовано або згруповано будь-яким чином. Нестача зведень чи структури може ускладнити виявлення шаблонів у даних. Важлива частина моделювання даних – це визначення збирань, які спрощують, узагальнюють чи підсумовують шаблони у відповідь на конкретне бізнес-питання.

Найпоширеніші агрегати, наприклад тих, хто використовує AVERAGE, COUNT, DISTINCTCOUNT, MAX, MINабо сума можна створити в міру автоматично за допомогою функції «Автосума». Інші типи агрегати, наприклад AVERAGEX, COUNTX, COUNTROWSабо SUMXповертають таблицю та вимагають формули, створені за допомогою Виразів аналізу даних (DAX).

Основні відомості про агрегування в надбудові Power Pivot

Вибір груп для збирання

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

Вибір способу групування даних залежить від бізнес-питання. Наприклад, збирання можуть відповісти на наведені нижче запитання:

Кількість   Скільки було транзакцій за місяць?

Середні значення    Який був середній обсяг продажів по продавцю за цей місяць?

Мінімальне та максимальне значення    Які області продажів були в першій п’ятірці відповідно до кількості проданих одиниць?

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

Якщо дані ще не містить значення, які можна використовувати для групування, як-от категорії продукту або ім'я географічного регіону, де знаходиться магазин, може знадобитися ввести групи до даних за допомогою додавання категорії. Під час створення групи в програмі Excel, потрібно вручну введіть або виберіть елемент групи, яку потрібно використовувати з-поміж стовпців на аркуші. Проте, реляційні системи, ієрархії, наприклад категорії продуктів часто зберігаються в іншу таблицю замість того, чи значення таблиці. Зазвичай таблиці category зв'язана з даними fact яку ключа. Припустімо, ви знайдете, що дані містять ідентифікатори продукту, але не імена продуктів або їх категорії. Щоб додати категорію плоских аркуша Excel, потрібно скопіювати в стовпець, що містить імена категорій. З Power Pivotможна імпортувати таблиці product category до моделі даних, створити зв'язок між таблиці, що містить числових даних і списку категорій продуктів і потім за допомогою категорій, щоб групувати дані. Додаткові відомості про створення зв'язку між таблицями.

Вибір функції для збирання

Визначивши та додавши групування, потрібно вирішити, які математичні функції застосовуватимуться для агрегування. Часто слово "агрегація" використовується як синонім для математичних або статистичних операцій, які застосовуються в агрегаціях, таких як сума, середнє значення, мінімальне значення або кількість. Проте надбудова Power Pivot дає змогу створювати настроювані формули для агрегування, а не тільки використовувати стандартні агрегації, які є в надбудові Power Pivot і програмі Excel.

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

Відфільтрована кількість   Скільки було транзакцій за місяць за винятком вікна для обслуговування в кінці місяця?

Відношення з використанням середніх значень за певний час    На скільки відсотків обсяг продажів було збільшено чи зменшено у порівнянні з тим же періодом часу у минулому році?

Згруповані мінімальні та максимальні значення    Які області продажів були першими для кожної категорії продукту чи для кожного просування продажів?

Додавання збирань до формул та зведених таблиць

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

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

  • У міруможна створити динамічні агрегати, які за допомогою фільтрів, визначені у формулі і фільтрів введених макет зведеної таблиці та вибір роздільники, заголовки стовпців і рядків. Показники за допомогою стандартної агрегації можна створити в Power Pivot за допомогою функції «Автосума» або за допомогою формули. Також можна створити неявних міри, використовуючи стандартні агрегації зведеної таблиці в програмі Excel.

Додавання групувань до зведеної таблиці

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

Якщо додати до зведеної таблиці категорії, але не пов’язати дані категорій із фактичними даними, можна отримати помилку або неочікувані результати. Зазвичай надбудова Power Pivot спробує виправити проблему, автоматично виявивши й запропонувавши зв’язки. Докладні відомості див. в статті Робота зі зв’язками у зведених таблицях.

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

Робота з групуваннями у формулі

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

Іншими словами, якщо потрібно створити формулу, яка групує значення за категорією, потрібно спочатку використати зв’язок, щоб підключити таблицю з детальними даними та таблиці з категоріями, а потім побудувати формулу.

Додаткові відомості щодо того, як створювати формули, які використовують підстановки, див. за посиланням Підстановки у формулах PowerPivot.

Використання фільтрів у збираннях

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

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

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

Додаткові відомості див. у статті Фільтрування даних у формулах.

Порівняння функцій збирання Excel та функцій збирання DAX

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

Стандартні функції збирання

Функція

Використання

AVERAGE

Повертає середнє значення (середнє арифметичне) усіх чисел у стовпці.

AVERAGEA

Повертає середнє значення (середнє арифметичне) усіх значень у стовпці. Обробляє текст і нечислові значення.

COUNT

Рахує кількість числових значень у стовпці.

COUNTA

Рахує кількість непустих значень у стовпці.

MAX

Повертає найбільше числове значення у стовпці.

MAXX

Повертає найбільше значення з набору виразів, обчислених через таблицю.

MIN

Повертає найменше числове значення у стовпці.

MINX

Повертає найменше значення з набору виразів, обчислених через таблицю.

SUM

Додає всі числа у стовпці.

Функції збирання DAX

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

У таблиці нижче наведено список функцій збирання, доступних у формулах DAX.

Функція

Використання

AVERAGEX

Усереднює набір виразів, обчислених у таблиці.

COUNTAX

Рахує кількість наборів виразів, обчислених у таблиці.

COUNTBLANK

Рахує кількість пустих значень у стовпці.

COUNTX

Рахує загальну кількість рядків у таблиці.

COUNTROWS

Рахує кількість рядків, повернутих з такої вкладеної функції таблиці як функція фільтра.

SUMX

Повертає суму з набору виразів, обчислених через таблицю.

Відмінності між функціями збирання DAX та Excel

Хоча ці функції ті ж імена, як їх колеги Excel, вони використовують Power Pivotаналітики в пам'яті обробник і переписати для роботи з таблицями та стовпцями. Не можна використовувати у формулі DAX у книзі Excel та навпаки. Їх можна використовувати лише у вікні Power Pivot та у зведених таблицях, на основі Power Pivot даних. Крім того, хоча функції мають однакові імена, поведінку можуть різнитися. Щоб отримати додаткові відомості перегляньте розділи посилання окрему функцію.

Спосіб обчислення стовпців у збиранні також різниться від способу обробки збирань програмою Excel. Приклад може допомогти ілюструвати.

Допустимо, потрібно отримати суму значень у стовпці "Обсяг" у таблиці "Продажі". Створюється наведена нижче формула:

=SUM('Sales'[Amount])

У найпростішому випадку функція отримує значення з одного невідфільтрованого стовпця та повертає такий самий результат, як і програма Excel, яка завжди просто підсумовує значення в стовпці Amount. Проте в надбудові Power Pivot формулу буде інтерпретовано таким чином: отримати значення в стовпці Amount для кожного рядка таблиці Sales, а потім скласти ці окремі значення. Надбудова Power Pivot обробляє кожен рядок, для якого виконується агрегування, і обчислює одне скалярне значення для кожного рядка, а потім виконує агрегування для цих значень. Тому результат формули може бути інший, якщо до таблиці застосовано фільтри, або значення обчислюються на основі інших агрегацій, які може бути відфільтровано. Докладні відомості див. в статті Контекст у формулах DAX.

Функції часового аналізу DAX

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

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

Функція

Використання

CLOSINGBALANCEMONTH

CLOSINGBALANCEQUARTER

CLOSINGBALANCEYEAR

Обчислює значення в календарному кінці заданого періоду.

OPENINGBALANCEMONTH

OPENINGBALANCEQUARTER

OPENINGBALANCEYEAR

Обчислює значення в календарному кінці періоду до заданого періоду.

TOTALMTD

TOTALYTD

TOTALQTD

Обчислює значення через інтервал, який починається у перший день періоду та закінчується в найпізнішу дату у вказаному стовпці дат.

Інші функції у розділі функцій часового аналізу (Функції часового аналізу) – це функції, які можна використовувати для отримання дат чи настроюваних діапазонів дат для використання у збиранні. Наприклад, можна використовувати функцію DATESINPERIOD, щоб повернути діапазон дат, та використати цей набір дат як аргумент до іншої функції, щоб обчислити настроюване збирання лише для цих дат.

Примітка : Застереження про машинний переклад: Цю статтю перекладено комп’ютерною системою без втручання людини. Корпорація Майкрософт пропонує таку послугу, щоб іншомовні користувачі могли дізнаватися про продукти, служби й технології Microsoft. Оскільки статтю перекладено за допомогою служби машинного перекладу, вона може містити смислові, синтаксичні або граматичні помилки.

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×