Agregace v Power Pivotu

Agregace představují způsob sbalení, shrnutí nebo seskupení dat. Když začínáte s nezpracovanými daty z tabulek nebo jiných zdrojů dat, jsou data často plochá, což znamená, že obsahují spoustu podrobností, ale nejsou nijak uspořádaná ani seskupená. Tento nedostatek souhrnů nebo struktury může ztížit zjišťování vzorců v datech. Důležitou součástí modelování dat je definování agregací, které zjednodušují, abstrahují nebo shrnují vzory v odpovědi na konkrétní obchodní otázku.

Většinu běžných agregací, jako jsou agregace s funkcemi AVERAGE,COUNT,DISTINCTCOUNT,MAX,MIN nebo SUM , se dají v míře vytvořit automaticky pomocí funkce AutoSum. Jiné typy agregací, jako jsou třeba AVERAGEX, COUNTX, COUNTROWS nebo SUMX, vracejí tabulku a vyžadují vzorec vytvořený pomocí jazyka DAX (Data Analysis Expressions).

Principy agregací v PowerPivotu

Volba skupin pro agregaci

Při agregaci dat seskupíte data podle atributů, jako jsou produkt, cena, oblast nebo datum, a potom definujete vzorec, který bude fungovat se všemi daty ve skupině. Když třeba vytváříte součet za rok, vytváříte agregaci. Pokud potom vytvoříte poměr tohoto roku oproti předchozímu roku a vykážete ho jako procenta, jedná se o jiný typ agregace.

Rozhodnutí o způsobu seskupení dat se řídí obchodní otázkou. Pomocí agregací můžete například odpovědět na tyto otázky:

Počet Kolik transakcí proběhlo za měsíc?

Průměry Jaké byly průměrné prodeje v tomto měsíci podle prodejců?

Minimální a maximální hodnoty Které prodejní čtvrti byly prvními pěti z hlediska prodaných jednotek?

Pokud chcete vytvořit výpočet, který odpoví na tyto otázky, musíte mít podrobná data obsahující čísla, která chcete spočítat nebo sečíst, a tato číselná data musí nějakým způsobem souviset se skupinami, které použijete k uspořádání výsledků.

Pokud data ještě neobsahují hodnoty, které byste mohli použít pro seskupení, například kategorii produktu nebo název zeměpisné oblasti, ve které se obchod nachází, můžete do dat přidat kategorie. Při vytváření skupin v aplikaci Excel je nutné požadované skupiny ručně zadat nebo vybrat ze sloupců listu. V relačním systému jsou však hierarchie, například kategorie produktů, často uloženy v jiné tabulce, než je tabulka faktů nebo hodnot. Tabulka kategorií je obvykle propojená s daty faktů nějakým klíčem. Předpokládejme například, že vaše data obsahují ID produktů, ale ne názvy produktů nebo jejich kategorií. Pokud byste chtěli kategorii přidat do plochého excelového listu, museli byste zkopírovat sloupec, který obsahoval názvy kategorií. Pomocí Power Pivotu můžete importovat tabulku kategorií výrobků do svého datového modelu, vytvořit relaci mezi tabulkou s číselnými údaji a seznamem kategorií výrobků a pak pomocí kategorií seskupit data. Další informace najdete v tématu Vytvoření relace mezi tabulkami.

Volba funkce pro agregaci

Po identifikaci a přidání seskupení, která se mají použít, je třeba rozhodnout, které matematické funkce se použijí pro agregaci. Slovo agregace se často používá jako synonymum pro matematické a statistické operace používané při agregacích, jako jsou součty, průměry, minimum nebo počty. Power Pivot vám ale umožňuje vytvářet vlastní vzorce pro agregaci navíc ke standardním agregacím, které jsou k dispozici v Power Pivotu i Excelu.

Pokud například používáte stejnou sadu hodnot a seskupení, jaké byly použity v předchozích příkladech, můžete vytvořit vlastní agregace, které odpovídají na následující otázky:

Filtrované počty Kolik transakcí proběhlo za měsíc (bez časového intervalu údržby na konci měsíce)?

Poměry pomocí průměrů v čase Jaký byl procentuální nárůst nebo pokles tržeb ve srovnání se stejným obdobím loňského roku?

Seskupené minimální a maximální hodnoty Které prodejní oblasti se umístily na předních místech v každé kategorii produktů nebo v každé podpoře prodeje?

Přidání agregací do vzorců a kontingenčních tabulek

Máte-li obecnou představu o tom, jak by měla být data uspořádána, aby byla smysluplná, a o hodnotách, se kterými chcete pracovat, můžete se rozhodnout, zda vytvoříte kontingenční tabulku nebo výpočty v tabulce. PowerPivot rozšiřuje a vylepšuje nativní schopnost Excelu vytvářet agregace, jako jsou součty, počty nebo průměry. V Power Pivotu můžete vytvářet vlastní agregace buď v okně Power Pivotu, nebo v oblasti kontingenční tabulky v Excelu.

• Ve výpočtovém sloupci můžete vytvořit agregace, které zohledňují kontext aktuálního řádku, aby načetly související řádky z jiné tabulky, a potom tyto hodnoty v souvisejících řádcích sečíst, spočítat nebo zprůměrovat.
• V míře můžete vytvářet dynamické agregace, které používají jak filtry definované ve vzorci, tak filtry vynucené návrhem kontingenční tabulky a výběrem průřezů, záhlaví sloupců a řádků. Míry používající standardní agregace se dají vytvořit v Power Pivotu pomocí funkce AutoSum nebo vytvořením vzorce. V kontingenční tabulce v Excelu můžete taky vytvořit implicitní míry pomocí standardních agregací.

Přidání seskupení do kontingenční tabulky

Při návrhu kontingenční tabulky přetahujete pole, která představují seskupení, kategorie nebo hierarchie, do oddílu sloupců a řádků kontingenční tabulky a tím data seskupíte. Potom pole obsahující číselné hodnoty přetáhněte do oblasti hodnot, aby se daly spočítat, zprůměrovat nebo sečíst.

Pokud přidáte do kontingenční tabulky kategorie, ale data kategorií nesouvisejí s daty faktů, může dojít k chybě nebo neobvyklým výsledkům. Power Pivot se obvykle pokusí problém vyřešit tak, že automaticky zjistí a navrhne relace. Další informace naleznete v tématu Práce s relacemi v kontingenčních tabulkách.

Pole můžete taky přetáhnout do průřezů a vybrat tak určité skupiny dat k zobrazení. Průřezy umožňují interaktivně seskupovat, řadit a filtrovat výsledky v kontingenční tabulce.

Práce se seskupeními ve vzorci

Pomocí seskupení a kategorií můžete taky agregovat data uložená v tabulkách vytvořením relací mezi tabulkami a následným vytvořením vzorců, které tyto relace využívají k vyhledání souvisejících hodnot.

Jinými slovy, pokud chcete vytvořit vzorec, který seskupuje hodnoty podle kategorie, je vhodné nejprve pomocí relace propojit tabulku obsahující podrobná data s tabulkami obsahujícími kategorie a potom vytvořit vzorec.

Další informace o vytváření vzorců využívajících vyhledávání najdete v tématu Vyhledávání ve vzorcích Power Pivotu.

Použití filtrů v agregacích

Novou funkcí Power Pivotu je možnost použít filtry na sloupce a tabulky dat, a to nejen v uživatelském rozhraní a v kontingenční tabulce nebo grafu, ale taky ve vzorcích, které používáte k výpočtu agregací. Filtry lze použít ve vzorcích jak v počítaných sloupcích, tak v s.

Například u nových agregačních funkcí jazyka DAX můžete místo zadávání hodnot, které chcete sečíst nebo počítat, zadat jako argument celou tabulku. Pokud byste v této tabulce nepoužili žádné filtry, agregační funkce by fungovala proti všem hodnotám v zadaném sloupci tabulky. V jazyce DAX však můžete v tabulce vytvořit dynamický nebo statický filtr, aby agregace fungovala na základě odlišné podmnožiny dat v závislosti na podmínkách filtru a aktuálním kontextu.

Kombinací podmínek a filtrů ve vzorcích můžete vytvořit agregace, které se mění v závislosti na hodnotách zadaných ve vzorcích nebo které se mění v závislosti na výběru záhlaví řádků a sloupců v kontingenční tabulce.

Další informace naleznete v tématu Filtrování dat ve vzorcích.

Porovnání agregačních funkcí Excelu a agregačních funkcí DAX

Následující tabulka obsahuje seznam některých standardních agregačních funkcí, které Excel poskytuje, a obsahuje odkazy na implementaci těchto funkcí v Power Pivotu. Verze jazyka DAX těchto funkcí se chová téměř stejně jako verze aplikace Excel, s určitými menšími rozdíly v syntaxi a zpracování určitých datových typů.

Standardní agregační funkce

Funkce	Použití
PRŮMĚR	Vrátí průměrnou hodnotu (aritmetický průměr) všech čísel ve sloupci.
AVERAGEA	Vrátí průměrnou hodnotu (aritmetický průměr) všech hodnot ve sloupci. Zpracovává textové a nečíselné hodnoty.
COUNT	Vypočítá počet číselných hodnot ve sloupci.
POČET2	Vrátí počet hodnot ve sloupci, které nejsou prázdné.
MAX	Vrátí největší číselnou hodnotu ve sloupci.
Funkce MAXX	Vrátí největší hodnotu ze sady výrazů vyhodnocených v tabulce.
MIN	Vrátí nejmenší číselnou hodnotu ve sloupci.
Funkce MINX	Vrátí nejmenší hodnotu ze sady výrazů vyhodnocených v tabulce.
SUMA	Sečte všechna čísla ve sloupci.

Agregační funkce jazyka DAX

Jazyk DAX obsahuje agregační funkce, které umožňují určit tabulku, ve které se má agregace provádět. Proto místo pouhého sčítání nebo zprůměrování hodnot ve sloupci umožňují tyto funkce vytvořit výraz, který dynamicky definuje data, která se mají agregovat.

V následující tabulce jsou uvedeny agregační funkce, které jsou k dispozici v jazyce DAX.

Funkce	Použití
AVERAGEX	Vypočítá průměr sady výrazů vyhodnocených v tabulce.
COUNTAX	Spočítá sadu výrazů vyhodnocených nad tabulkou.
COUNTBLANK	Vrátí počet prázdných hodnot ve sloupci.
Funkce COUNTX	Spočítá celkový počet řádků v tabulce.
COUNTROWS	Spočítá počet řádků vrácených z funkce vnořené tabulky, jako je například funkce FILTER.
Funkce SUMX	Vrátí součet sady výrazů vyhodnocených v tabulce.

Rozdíly mezi agregačními funkcemi jazyka DAX a Excel

I když mají tyto funkce stejné názvy jako jejich protějšky v Excelu, využívají modul Power Pivot pro analýzu v paměti a byly přepsány tak, aby fungovaly s tabulkami a sloupci. Vzorec jazyka DAX nelze použít v sešitu aplikace Excel (a naopak). Můžete je použít jenom v okně Power Pivotu a v kontingenčních tabulkách, které jsou založené na datech Power Pivotu. Přestože mají funkce stejné názvy, chování se může mírně lišit. Další informace najdete v referenčních tématech k jednotlivým funkcím.

Způsob, jakým jsou sloupce vyhodnocovány v agregaci, se také liší od způsobu, jakým Excel agregace zpracovává. Příklad by mohl pomoci ilustrovat.

Předpokládejme, že chcete získat součet hodnot ve sloupci Částka v tabulce Prodej, a vytvořte následující vzorec:

=SUM('Sales'[Amount])

V nejjednodušším případě získá funkce hodnoty z jednoho nefiltrovaného sloupce a výsledek bude stejný jako v Excelu, který vždy jenom sečte hodnoty ve sloupci Amount. V Power Pivotu se ale vzorec interpretuje jako "Získej hodnotu v argumentu Částka pro každý řádek tabulky Prodej a pak tyto jednotlivé hodnoty sečti. Power Pivot vyhodnotí každý řádek, ve kterém se provádí agregace, vypočítá pro každý řádek jednu skalární hodnotu a potom na těchto hodnotách provede agregaci. Výsledek vzorce se proto může lišit v případě, že byly v tabulce použity filtry nebo pokud jsou hodnoty vypočítány na základě jiných agregací, které mohou být filtrovány. Další informace najdete v tématu Kontext ve vzorcích jazyka DAX.

Funkce časového měřítka jazyka DAX

Kromě agregačních funkcí tabulek popsaných v předchozí části obsahuje jazyk DAX agregační funkce, které pracují se zadanými daty a časy a poskytují předdefinované časové měřítko. Tyto funkce používají rozsahy kalendářních dat k získání souvisejících hodnot a agregaci hodnot. Můžete taky porovnávat hodnoty v různých obdobích.

V následující tabulce jsou uvedené funkce časového měřítka, které je možné použít k agregaci.

Funkce	Použití
CLOSINGBALANCEMONTH CLOSINGBALANCEQUARTER CLOSINGBALANCEYEAR	Vypočítá hodnotu na konci kalendářního období daného období.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Vypočítá hodnotu na kalendářním konci období předcházejícího danému období.
TOTALMTD CELKEM TOTALQTD	Vypočítá hodnotu v intervalu, který začíná prvním dnem období a končí posledním datem v zadaném sloupci kalendářních dat.

Ostatní funkce v části Funkce časového měřítka (Funkce časového měřítka) jsou funkce, které lze použít k načtení kalendářních dat nebo vlastních rozsahů dat pro použití při agregaci. Pomocí funkce DATESINPERIOD můžete například vrátit rozsah kalendářních dat a tuto sadu dat použít jako argument jiné funkce k výpočtu vlastní agregace jenom pro tato data.