Agregácie v doplnku Power Pivot

Agregácie sú spôsobom zbalenia, zhrnutia alebo zoskupenia údajov. Keď začnete s nespracovanými údajmi z tabuliek alebo iných zdrojov, údaje sú často jednostavné, čo znamená, že obsahujú veľa podrobností, ale neboli nijako usporiadané ani zoskupené. Chýbajúce súhrny alebo štruktúry môžu sťažiť zisťovanie vzorov v údajoch. Dôležitou súčasťou modelovania údajov je definovanie agregácií, ktoré zjednodušujú, abstrahujú alebo sumarizujú vzory ako odpoveď na konkrétnu obchodnú otázku.

Najbežnejšie agregácie, napríklad agregácie používajúce funkcie AVERAGE,COUNT,DISTINCTCOUNT,MAX, MIN alebo SUM , môžete v miere vytvoriť automaticky pomocou funkcie Automatický súčet. Iné typy agregácií, napríklad AVERAGEX, COUNTX, COUNTROWS alebo SUMX, vrátia tabuľku a vyžadujú vzorec vytvorený pomocou jazyka DAX (Data Analysis Expressions).

Princíp agregácií v doplnku Power Pivot

Výber skupín na agregáciu

Keď agregujete údaje, zoskupujete údaje podľa atribútov, ako je produkt, cena, oblasť alebo dátum, a potom definujete vzorec, ktorý bude pracovať so všetkými údajmi v skupine. Keď napríklad vytvoríte celkovú sumu za rok, vytvárate agregáciu. Ak potom vytvoríte pomer pre tento rok a prezentujete ho v percentách, použije sa iný typ agregácie.

Rozhodnutie o spôsobe zoskupenia údajov závisí od obchodnej otázky. Agregácie môžu napríklad odpovedať na nasledujúce otázky:

Počty Koľko transakcií sa uskutočnilo za mesiac?

Priemery Aký bol priemerný predaj v tomto mesiaci podľa predajcov?

Minimálne a maximálne hodnoty Ktoré predajné štvrte boli medzi prvými piatimi z hľadiska počtu predaných jednotiek?

Ak chcete vytvoriť výpočet s odpoveďami na tieto otázky, potrebujete mať podrobné údaje obsahujúce čísla, ktoré chcete spočítať alebo sčítať, a tieto číselné údaje musia určitým spôsobom súvisieť so skupinami, ktoré použijete na usporiadanie výsledkov.

Ak údaje ešte neobsahujú hodnoty, ktoré možno použiť na zoskupenie, ako je napríklad kategória produktu alebo názov geografickej oblasti, kde sa obchod nachádza, môžete do svojich údajov zaviesť skupiny pridaním kategórií. Pri vytváraní skupín v Exceli je potrebné manuálne zadať alebo vybrať skupiny, ktoré chcete použiť spomedzi stĺpcov v hárku. V relačnom systéme sú však hierarchie, ako napríklad kategórie produktov, často uložené v inej tabuľke, než je tabuľka faktov alebo hodnôt. Tabuľka kategórií je zvyčajne nejakým kľúčom prepojená s údajmi faktov. Predpokladajme napríklad, že zistíte, že údaje obsahujú identifikácie produktov, ale nie názvy produktov ani ich kategórie. Ak chcete pridať kategóriu do plochého excelového hárka, museli by ste skopírovať stĺpec, ktorý obsahoval názvy kategórií. Pomocou doplnku Power Pivot môžete importovať tabuľku kategórií produktov do svojho dátového modelu, vytvoriť vzťah medzi tabuľkou s číselnými údajmi a zoznamom kategórií produktov a potom použiť kategórie na zoskupenie údajov. Ďalšie informácie nájdete v téme Vytvorenie vzťahu medzi tabuľkami.

Výber funkcie pre agregáciu

Po identifikácii a pridaní zoskupení, ktoré sa majú použiť, sa musíte rozhodnúť, ktoré matematické funkcie chcete použiť na agregáciu. Slovo agregácia sa často používa ako synonymum pre matematické a štatistické operácie, ktoré sa používajú v agregáciách, ako sú napríklad súčty, priemery, minimum alebo počty. Power Pivot vám však okrem štandardných agregácií, ktoré sa nachádzajú v doplnku Power Pivot aj Exceli, umožňuje vytvárať vlastné vzorce na agregáciu.

Na základe rovnakej množiny hodnôt a zoskupení, aká bola použitá v predchádzajúcich príkladoch, môžete napríklad vytvoriť vlastné agregácie s odpoveďami na nasledujúce otázky:

Filtrované počty Koľko transakcií sa uskutočnilo za mesiac bez obdobia údržby na konci mesiaca?

Pomery používajúce priemery za určité obdobie Aký bol percentuálny nárast alebo pokles tržieb v porovnaní s rovnakým obdobím minulého roka?

Zoskupené minimálne a maximálne hodnoty Ktoré predajné štvrte sa umiestnili na popredných miestach v jednotlivých kategóriách produktov alebo v rámci každej akcie predaja?

Pridanie agregácií do vzorcov a kontingenčných tabuliek

Keď máte všeobecnú predstavu o tom, ako majú byť údaje zoskupené, aby boli zmysluplné, a hodnotách, s ktorými chcete pracovať, môžete sa rozhodnúť, či vytvoríte kontingenčnú tabuľku, alebo vytvoríte výpočty v rámci tabuľky. Power Pivot rozširuje a vylepšuje natívne schopnosti Excelu vytvárať agregácie, ako sú napríklad súčty, počty alebo priemery. V doplnku Power Pivot môžete vytvárať vlastné agregácie buď v okne doplnku Power Pivot, alebo v oblasti excelovej kontingenčnej tabuľky.

• Vo vypočítavanom stĺpci môžete vytvárať agregácie, ktoré berú do úvahy kontext aktuálneho riadka na načítanie súvisiacich riadkov z inej tabuľky a potom tieto hodnoty sčítať, spočítať alebo vypočítať ich priemer v súvisiacich riadkoch.
• V miere môžete vytvárať dynamické agregácie, ktoré používajú filtre definované vo vzorci a filtre vytvorené návrhom kontingenčnej tabuľky a výberom rýchlych filtrov, záhlaví stĺpcov a záhlaví riadkov. Miery pomocou štandardných agregácií možno vytvoriť v doplnku Power Pivot pomocou funkcie Automatický súčet alebo vytvorením vzorca. Implicitné miery môžete vytvoriť aj pomocou štandardných agregácií v kontingenčnej tabuľke v Exceli.

Pridanie zoskupení do kontingenčnej tabuľky

Pri navrhovaní kontingenčnej tabuľky presúvate polia, ktoré predstavujú zoskupenia, kategórie alebo hierarchie, do sekcie stĺpcov a riadkov kontingenčnej tabuľky, kde sa údaje zoskupia. Potom presuňte polia, ktoré obsahujú číselné hodnoty, do oblasti hodnôt, aby ich bolo možné spočítať, spriemerovať alebo sčítať.

Ak do kontingenčnej tabuľky pridáte kategórie, ale údaje kategórií nesúvisia s údajmi faktov, môže sa vyskytnúť chyba alebo zvláštne výsledky. Power Pivot sa zvyčajne pokúsi problém vyriešiť automatickým zistením a navrhnutím vzťahov. Ďalšie informácie nájdete v téme Práca so vzťahmi v kontingenčných tabuľkách.

Polia môžete do rýchlych filtrov presunúť aj myšou a vybrať tak určité skupiny údajov na zobrazenie. Rýchle filtre umožňujú interaktívne zoskupovať, zoraďovať a filtrovať výsledky v kontingenčnej tabuľke.

Práca so zoskupeniami vo vzorci

Zoskupenia a kategórie môžete použiť aj na agregáciu údajov uložených v tabuľkách tak, že vytvoríte vzťahy medzi tabuľkami a potom vytvoríte vzorce, ktoré tieto vzťahy využívajú na vyhľadávanie súvisiacich hodnôt.

Inými slovami, ak chcete vytvoriť vzorec, ktorý zoskupuje hodnoty podľa kategórie, najskôr by ste mali použiť vzťah na prepojenie tabuľky obsahujúcej podrobné údaje a tabuliek obsahujúcich kategórie a potom vytvoriť vzorec.

Ďalšie informácie o vytváraní vzorcov, ktoré využívajú vyhľadávania, nájdete v téme Vyhľadávania vo vzorcoch doplnku Power Pivot.

Používanie filtrov v agregáciách

Novou funkciou doplnku Power Pivot je možnosť používať filtre na stĺpce a tabuľky s údajmi, a to nielen v používateľskom rozhraní, v rámci kontingenčnej tabuľky alebo grafu, ale aj vo vzorcoch, ktoré používate na výpočet agregácií. Filtre je možné použiť vo vzorcoch vo vypočítaných stĺpcoch aj v s.

V nových agregačných funkciách jazyka DAX napríklad môžete namiesto hodnoty, nad ktorými sa má sčítať alebo spočítať, zadať ako argument celú tabuľku. Ak ste v tejto tabuľke nepoužili žiadne filtre, agregačná funkcia bude fungovať pre všetky hodnoty v určenom stĺpci tabuľky. V jazyku DAX však môžete v tabuľke vytvoriť dynamický aj statický filter, aby agregácia pracovala s inou podmnožinou údajov v závislosti od podmienky filtra a aktuálneho kontextu.

Kombináciou podmienok a filtrov vo vzorcoch môžete vytvoriť agregácie, ktoré sa menia v závislosti od hodnôt poskytnutých vo vzorcoch alebo ktoré sa menia v závislosti od výberu riadkov, záhlaví riadkov a stĺpcov v kontingenčnej tabuľke.

Ďalšie informácie nájdete v téme Filtrovanie údajov vo vzorcoch.

Porovnanie agregačných funkcií Excelu a agregačných funkcií jazyka DAX

V nasledujúcej tabuľke nájdete zoznam niektorých štandardných agregačných funkcií, ktoré poskytuje Excel, a prepojenia na implementáciu týchto funkcií v Power Pivote. Verzia DAX týchto funkcií sa správa veľmi podobne ako verzia Excelu a obsahuje niekoľko menších rozdielov v syntaxi a spracovaní určitých typov údajov.

Štandardné agregačné funkcie

Funkcia	Použitie
AVERAGE	Vráti priemernú hodnotu (aritmetický priemer) všetkých čísel v stĺpci.
AVERAGEA	Vráti priemernú hodnotu (aritmetický priemer) všetkých hodnôt v stĺpci. Slúži na spracovanie textových a nečíselných hodnôt.
COUNT	Spočíta počet číselných hodnôt v stĺpci.
COUNTA	Spočíta počet hodnôt v stĺpci, ktoré nie sú prázdne.
MAX	Vráti najväčšiu číselnú hodnotu v stĺpci.
MAXX	Vráti najväčšiu hodnotu z množiny výrazov vyhodnotených v tabuľke.
MIN	Vráti najmenšiu číselnú hodnotu v stĺpci.
MINX	Vráti najmenšiu hodnotu z množiny výrazov vyhodnotených v tabuľke.
SUM	Sčíta všetky čísla v stĺpci.

Agregačné funkcie jazyka DAX

Jazyk DAX obsahuje agregačné funkcie, ktoré umožňujú určiť tabuľku, v ktorej sa má vykonať agregácia. Preto namiesto len sčítania alebo priemerovania hodnôt v stĺpci tieto funkcie umožňujú vytvoriť výraz, ktorý dynamicky definuje údaje, ktoré sa majú agregovať.

V nasledujúcej tabuľke sú uvedené agregačné funkcie, ktoré sú k dispozícii v jazyku DAX.

Funkcia	Použitie
AVERAGEX	Vypočíta priemer pre množinu výrazov vyhodnotených v tabuľke.
COUNTAX	Spočíta množinu výrazov vyhodnotených v tabuľke.
COUNTBLANK	Spočíta počet prázdnych hodnôt v stĺpci.
COUNTX	Spočíta celkový počet riadkov v tabuľke.
COUNTROWS (POČETPOČET)	Spočíta počet riadkov vrátených z funkcie vnorenej tabuľky, ako je napríklad funkcia filtra.
SUMX	Vráti súčet množiny výrazov vyhodnotených v tabuľke.

Rozdiely medzi agregačnými funkciami jazyka DAX a Excelu

Hoci tieto funkcie majú rovnaké názvy ako ich náprotivky v Exceli, používajú nástroj na analýzu pamäte doplnku Power Pivot v pamäti a boli prepísané tak, aby fungovali s tabuľkami a stĺpcami. Vzorec jazyka DAX nemožno použiť v excelovom zošite a naopak. Možno ich použiť iba v okne doplnku Power Pivot a v kontingenčných tabuľkách založených na údajoch doplnku Power Pivot. Hoci majú funkcie rovnaké názvy, správanie sa môže mierne líšiť. Ďalšie informácie nájdete v referenčných témach pre jednotlivé funkcie.

Spôsob, akým sa stĺpce vyhodnocujú v agregácii, sa tiež líši od spôsobu, akým Excel spracúva agregácie. Ilustráciu tu možno uviesť ako príklad.

Predpokladajme, že chcete získať súčet hodnôt v stĺpci Čiastka v tabuľke Predaj, takže vytvoríte nasledujúci vzorec:

=SUM('Sales'[Amount])

V najjednoduchšom prípade funkcia získa hodnoty z jedného nefiltrovaného stĺpca a výsledok je rovnaký ako v Exceli, kde sa vždy len sčítajú hodnoty v stĺpci Suma. V doplnku Power Pivot sa však vzorec interpretuje ako "Získajte hodnotu v poli Čiastka pre každý riadok tabuľky Predaj a potom jednotlivé hodnoty sčítajte. Power Pivot vyhodnotí každý riadok, v ktorom sa vykoná agregácia, a pre každý riadok vypočíta jednu skalárnu hodnotu a potom tieto hodnoty agreguje. Výsledok vzorca preto môže byť odlišný, ak sa v tabuľke použijú filtre alebo ak sa hodnoty vypočítajú na základe iných agregácií, ktoré je možné filtrovať. Ďalšie informácie nájdete v kontexte vo vzorcoch jazyka DAX.

Funkcie časovej inteligencie jazyka DAX

Okrem funkcií na zhromažďovanie tabuliek, ktoré sú popísané v predchádzajúcej časti, obsahuje jazyk DAX agregačné funkcie, ktoré pracujú s dátumami a časmi, ktoré zadáte, a poskytujú tak vstavanú časovú inteligenciu. Tieto funkcie používajú rozsahy dátumov na získanie súvisiacich hodnôt a agregáciu hodnôt. Môžete tiež porovnávať hodnoty v rámci rozsahov dátumov.

Nasledujúca tabuľka obsahuje funkcie časovej inteligencie, ktoré možno použiť na agregáciu.

Funkcia	Použitie
ZÁVEREČNÝ ZOSTATOKMESIAC ZÁVEREČNÝ ZOSTATOKŠTVRŤROK ZÁVEREČNÝ BILANCIA	Vypočíta hodnotu na konci kalendárneho obdobia.
OPENINGBALANCEMONTH OPENINGBALANCEQUARTER OPENINGBALANCEYEAR	Vypočíta hodnotu na konci kalendárneho obdobia pred daným obdobím.
TOTALMTD (CELKOM) TOTALYTD (CELKOM) TOTALQTD	Vypočíta hodnotu v intervale, ktorý sa začína prvým dňom obdobia a končí najneskorším dátumom v určenom stĺpci dátumov.

Ďalšie funkcie v časti Funkcia časovej inteligencie (Funkcie časovej inteligencie) sú funkcie, ktoré možno použiť na načítanie dátumov alebo vlastných rozsahov dátumov na agregáciu. Funkciu DATESINPERIOD môžete napríklad použiť na vrátenie rozsahu dátumov a túto množinu dátumov môžete použiť ako argument pre inú funkciu na výpočet vlastnej agregácie iba pre tieto dátumy.