Агрегиранията са начин за свиване, обобщаване или групиране на данни. Когато започнете със необработени данни от таблици или други източници на данни, данните често са плоски, което означава, че има много подробности, но не са организирани или групирани по никакъв начин. Тази липса на резюмета или структура може да затрудни откриването на модели в данните. Важна част от моделирането на данни е да се дефинират агрегации, които опростяват, абстрактни или обобщават модели в отговор на конкретен бизнес въпрос.
Най-често срещаните агрегации, като например тези, които използват 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, с някои незначителни разлики в синтаксиса и обработката на определени типове данни.
Стандартни функции за агрегиране
|
Функция |
Използвайте |
|
Връща средната стойност (средноаритметично) на всички числа в колона. |
|
|
Връща средната стойност (средноаритметично) на всички стойности в колона. Обработва текстови и нечислени стойности. |
|
|
Преброяване на числовите стойности в колона. |
|
|
Преброяване на стойностите в колона, които не са празни. |
|
|
Връща най-голямата числова стойност в колона. |
|
|
Връща най-голямата стойност от набор от изрази, оценени в таблица. |
|
|
Връща най-малката числова стойност в колона. |
|
|
Връща най-малката стойност от набор от изрази, оценени в таблица. |
|
|
Добавя всички числа в колона. |
Функции за агрегиране на DAX
DAX включва функции за агрегиране, които ви позволяват да зададете таблица, върху която да се извърши агрегирането. Следователно, вместо просто да добавяте или осредявате стойностите в колона, тези функции ви позволяват да създадете израз, който динамично дефинира данните за агрегиране.
Следващата таблица описва функциите за агрегиране, които са налични в DAX.
|
Функция |
Използвайте |
|
Средни стойности на набор от изрази, оценени в таблица. |
|
|
Преброява набор от изрази, оценени в таблица. |
|
|
Преброяване на празните стойности в колона. |
|
|
Преброяване на общия брой редове в таблица. |
|
|
Преброи броя на редовете, върнати от вложена функция на таблица, като например функцията за филтриране. |
|
|
Връща сумата от набор от изрази, оценени в таблица. |
Разлики между DAX и Excel функции за агрегиране
Въпреки че тези функции имат същите имена като техните Excel партньори, те използват Power Pivot на анализа в паметта и са пренаписани, за да работят с таблици и колони. Не можете да използвате формула на DAX в Excel работна книга и обратно. Те могат да се използват само в Power Pivot и в обобщени таблици, които се базират на Power Pivot данни. Освен това, въпреки че функциите имат еднакви имена, поведението може да е малко по-различно. За повече информация вижте темите за справка за отделните функции.
Начинът, по който колоните се оценяват в агрегиране, също се различава от начина, по който Excel обработва агрегирането. Примерът може да ви помогне да илюстрирате.
Да предположим, че искате да получите сума от стойностите в колоната Сума в таблицата Продажби, така че да създадете следната формула:
=SUM('Sales'[Amount])
В най-простия случай функцията получава стойностите от една нефилтриирана колона и резултатът е същият като в Excel, което винаги само добавя стойностите в колоната "Сума". Обаче в Power Pivot формулата се интерпретира като "Получаване на стойността в сума за всеки ред от таблицата "Продажби" и след това се добавят тези отделни стойности. Power Pivot всеки ред, върху който се извършва агрегирането, и изчислява една-единствена стойност за всеки ред и след това извършва агрегиране на тези стойности. Следователно резултатът от формулата може да бъде различен, ако към таблица са приложени филтри или ако стойностите се изчисляват на базата на други агрегации, които може да са филтрирани. За повече информация вижте Контекст във формули на DAX.
DAX функции за интелигентно време
В допълнение към функциите за агрегиране на таблица, описани в предишния раздел, DAX има функции за агрегиране, които работят с дати и часове, които сте задали, за да предоставят вградена времева интелигентност. Тези функции използват диапазони от дати, за да получите свързани стойности и да агрегират стойностите. Можете също да сравнявате стойности между диапазони от дати.
Следващата таблица описва функциите за интелигентно време, които могат да се използват за агрегиране.
|
Функция |
Използвайте |
|
Изчислява стойност в края на календара на дадения период. |
|
|
Изчислява стойност в края на календара на периода преди дадения период. |
|
|
Изчислява стойност през интервала, който започва от първия ден на периода, и завършва на най-късната дата в указаната колона за дата. |
Другите функции в секцията "Времева интелигентност"(функцииза интелигентно време) са функции, които могат да се използват за извличане на дати или диапазони от дати по избор, които да се използват в агрегиране. Можете например да използвате функцията DATESINPERIOD, за да върнете диапазон от дати и да използвате този набор от дати като аргумент към друга функция, за да изчислите агрегиране по избор само за тези дати.
