Агрегирания в Power Pivot

Обединенията са начин за свиване, обобщаване или групиране на данни. Когато започнете с необработените данни от таблици или други източници на данни, данните често са плоски, което означава, че има много подробности, но те не са организирани или групирани по какъвто и да е начин. Тази липса на резюмета или структура може да затруднява откриването на модели в данните. Важна част от моделирането на данни е да се дефинират агрегирания, които опростяват, абстрактни или обобщават модели в отговор на конкретен бизнес въпрос.

Най-често срещаните обединения, като например тези, които използват 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, с някои малки разлики в синтаксиса и обработката на определени типове данни.

Стандартни функции за агрегиране

Функция

Използване

Средно

Връща средната стойност (средноаритметичното) на всички числа в дадена колона.

Средно

Връща средната стойност (средноаритметичното) на всички стойности в дадена колона. Обработва текст и нечислови стойности.

Преброяване

Преброява числовите стойности в дадена колона.

Count

Преброява стойностите в колона, които не са празни.

MAX

Връща най-голямата цифрова стойност в дадена колона.

MAXX

Връща най-голямата стойност от набор от изрази, оценени спрямо таблица.

мин

Връща най-малката цифрова стойност в дадена колона.

кокетка

Връща най-малката стойност от набор от изрази, оценени спрямо таблица.

Сумиране

Сумира всички числа в дадена колона.

Функции на DAX за агрегиране

DAX включва функции за агрегиране, които ви позволяват да зададете таблица, в която трябва да се извърши агрегирането. Следователно, вместо просто да добавяте или осреднявате стойностите в колона, тези функции ви позволяват да създадете израз, който динамично дефинира данните за обединение.

В следващата таблица са изредени функциите за агрегиране, които са налични в DAX.

Функция

Използване

AVERAGEX

Осреднява набор от изрази, оценени спрямо таблица.

COUNTAX

Преброява набор от изрази, оценени спрямо таблица.

COUNTBLANK

Преброява празните стойности в дадена колона.

COUNTX

Преброява общия брой редове в дадена таблица.

COUNTROWS

Преброява редовете, върнати от вложена функция на таблица, като например функцията Filter.

SUMX

Връща сумата на набор от изрази, оценени спрямо таблица.

Разлики между функции на DAX и Excel за агрегиране

Макар че тези функции имат същите имена като техните колеги на Excel, те използват системата за анализ на паметта на Power Pivot и са пренаписани за работа с таблици и колони. Не можете да използвате формула DAX в работна книга на Excel и обратно. Те могат да се използват само в прозореца на 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, за да върнете диапазон от дати, и да използвате този набор от дати като аргумент към друга функция, за да изчислите агрегиране по избор само за тези дати.

Забележка:  Тази страница е преведена чрез автоматизация и може да съдържа граматически грешки и несъответствия. Нашата цел е това съдържание да ви бъде полезно. Можете ли да ни кажете дали информацията е била полезна за вас? Ето статията на английски за справка.

Нуждаете се от още помощ?

Разширете уменията си в Office
Преглед на обучението
Получавайте първи новите функции
Присъединете се към участниците в Office Insider

Беше ли полезна тази информация?

Благодарим ви за обратната връзка!

Благодарим ви за вашата обратна връзка. Изглежда, че ще бъде полезно да ви свържем с един от нашите агенти по поддръжката на Office.

×