Агрегирането е начин за свиване, обобщаване или групиране на данни. Когато започнете с необработени данни от таблици или други източници на данни, данните често са плоски, което означава, че има много подробности, но те не са били организирани или групирани по никакъв начин. Тази липса на резюмета или структура може да затрудни откриването на модели в данните. Важна част от моделирането на данни е да се дефинират агрегирания, които опростяват, абстрактни или обобщават модели в отговор на конкретен бизнес въпрос.
Най-често срещаните агрегирания, като например тези, които използват 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, за да върнете диапазон от дати, и да използвате този набор от дати като аргумент на друга функция, за да изчислите агрегиране по избор само за тези дати.
