Kurz: Analýza údajov kontingenčnej tabuľky pomocou údajového modelu v programe Excel 2013

Za necelú hodinu sa naučíte vytvoriť v Exceli zostavu kontingenčnej tabuľky, ktorá kombinuje údaje z viacerých tabuliek. V prvej časti tohto kurzu je vysvetlený import údajov a ich prieskum. V druhej časti použijete doplnok PowerPivot na úpravu údajového modelu, ktorý stojí za zostavou, a naučíte sa pridať do zostáv funkcie Power View výpočty, hierarchie a optimalizácie.

Začnime importom údajov.

  1. Stiahnite si vzorové údaje (ContosoV2) pre tento kurz. Podrobnosti nájdete v téme Získanie vzorových údajov pre kurzy jazyka DAX a údajového modelu. Extrahujte a uložte údajové súbory do umiestnenia s jednoduchým prístupom, napríklad do priečinkov Prevzaté súbory alebo Dokumenty.

  2. V Exceli otvorte prázdny zošit.

  3. Kliknite na položky Údaje > Získať externé údaje > Z programu Access.

  4. Prejdite do priečinka, ktorý obsahuje súbory so vzorovými údajmi, a vyberte položku ContosoSales.

  5. Kliknite na tlačidlo Otvoriť. Pretože sa pripájate k databázovému súboru obsahujúcemu viacero tabuliek, zobrazí sa dialógové okno Výber tabuľky, aby ste mohli vybrať, ktoré tabuľky sa majú importovať.

    Dialógové okno Výber tabuľky

  6. V okne Výber tabuľky začiarknite políčko Povoliť výber viacerých tabuliek.

  7. Vyberte všetky tabuľky a kliknite na tlačidlo OK.

  8. V dialógovom okne Import údajov kliknite na položku Zostava kontingenčnej tabuľky a kliknite na tlačidlo OK.

    Poznámky : 

    • Možno ešte neviete, no práve ste vytvorili údajový model. Model predstavuje vrstvu integrácie údajov, ktorá sa automaticky vytvorí vtedy, keď do zostavy kontingenčnej tabuľky súčasne importujete viacero tabuliek alebo vtedy, keď v nej súčasne pracujete s viacerými tabuľkami.

    • V Exceli model zvyčajne nie je viditeľný, ale môžete ho zobraziť a upraviť pomocou doplnku PowerPivot . Prítomnosť údajového modelu je v Exceli zrejmá vtedy, keď v zozname polí kontingenčnej tabuľky vidíte kolekciu tabuliek. Model môžete vytvoriť viacerými spôsobmi, podrobnosti nájdete v téme Vytvorenie dátového modelu v programe Excel .

Prieskum údajov pomocou kontingenčnej tabuľky

Prieskum údajov sa uskutoční jednoduchým presunutím polí do oblastí Hodnoty, Stĺpce a Riadky v rámci zoznamu polí kontingenčnej tabuľky.

  1. V zozname polí sa posúvajte nadol, až kým nenájdete tabuľku FactSales.

  2. Kliknite na položku SalesAmount (Objem predaja). Keďže ide o číselné údaje, Excel automaticky umiestni položku SalesAmount (Objem predaja) do oblasti Hodnoty.

  3. Z tabuľky DimDate (Dátum) presuňte položku CalendarYear (Kalendárny rok) do oblasti Stĺpce.

  4. Z tabuľky DimProductSubcategory (Podkategória produktu) presuňte položku ProductSubcategoryName (Názov podkategórie produktu) do oblasti Riadky.

  5. Z tabuľky DimProduct (Produkt) presuňte položku BrandName (Názov značky) do oblasti Riadky, pričom ju umiestnite pod podkategóriu.

Vaša kontingenčná tabuľka by sa mala podobať na toto zobrazenie.

Kontingenčná tabuľka so vzorovými údajmi

S vynaložením minimálneho úsilia ste teraz vytvorili základnú kontingenčnú tabuľku obsahujúcu polia zo štyroch odlišných tabuliek. Táto úloha bola taká jednoduchá vďaka existujúcim vzťahom medzi tabuľkami. Pretože vzťahy medzi tabuľkami existovali v zdroji a všetky tabuľky ste importovali v rámci jedinej operácie, Excel dokázal tieto vzťahy znova vytvoriť aj v modeli.

Čo však v prípade, že vaše údaje pochádzajú z rozličných zdrojov alebo ich importujete neskôr? Nové údaje môžete zvyčajne zahrnúť tak, že vytvoríte vzťahy založené na zhodných stĺpcoch. Ďalším krokom potom bude import dodatočných tabuliek, ako aj zistenie požiadaviek a krokov potrebných na vytvorenie nových vzťahov.

Pridanie ďalších tabuliek

Ak sa chcete naučiť nastaviť vzťahy tabuliek, je potrebné, aby ste mali ďalšie neprepojené tabuľky, s ktorými môžete pracovať. Pomocou tohto kroku získate zostávajúce údaje použité v tomto kurze tak, že importujete jeden dodatočný databázový súbor a prilepíte údaje z ďalších dvoch zošitov.

Pridanie kategórií produktov

  1. V zošite otvorte nový hárok. Použijete ho na uloženie ďalších údajov.

  2. Kliknite na položky Údaje > Získať externé údaje > Z programu Access.

  3. Prejdite do priečinka, ktorý obsahuje súbory so vzorovými údajmi, a vyberte položku ProductCategories. Kliknite na tlačidlo Otvoriť.

  4. V dialógovom okne Import údajov vyberte položku Tabuľka a kliknite na tlačidlo OK.

Pridanie geografických údajov

  1. Vložte ďalší hárok.

  2. Zo súborov so vzorovými údajmi otvorte súbor Geography.xlsx, umiestnite kurzor do bunky A1 a potom stlačením klávesov Ctrl+Shift+End vyberte všetky údaje.

  3. Skopírujte údaje do schránky.

  4. Prilepte údaje do prázdneho hárka, ktorý ste práve pridali.

  5. Kliknite na položku Formátovať ako tabuľku a vyberte ľubovoľný štýl. Formátovanie údajov ako tabuľku vám umožní nazvať ich, čo bude užitočné neskôr pri definovaní vzťahov.

  6. V dialógovom okne Formátovať ako tabuľku skontrolujte, či je začiarknuté políčko Tabuľka obsahuje hlavičky. Kliknite na tlačidlo OK.

  7. Tabuľku nazvite Geografia. Na karte Nástroje tabuliek > Návrh zadajte do poľa Názov tabuľky výraz Geografia.

  8. Zatvorte súbor Geography.xlsx, aby ste ho vymazali z pracovného priestoru.

Pridanie údajov obchodu

  • Zopakujte predchádzajúce kroky pre súbor Stores.xlsx a prilepte jeho obsah do prázdneho hárka. Tabuľku nazvite Obchody.

Teraz by ste mali mať štyri hárky. Hárok1 obsahuje kontingenčnú tabuľku, Hárok2 obsahuje tabuľku ProductCategories, Hárok3 obsahuje tabuľku Geografia a Hárok4 obsahuje tabuľku Obchody. Pretože ste venovali čas pomenovaniu každej tabuľky, nasledujúci krok, vytvorenie vzťahov, bude omnoho jednoduchší.

Použitie polí z novoimportovaných tabuliek

Polia z tabuliek, ktoré ste práve importovali, môžete začať ihneď používať. Ak Excel nedokáže zistiť, ako pole zahrnúť do zostavy kontingenčnej tabuľky, zobrazí sa výzva na vytvorenie vzťahu tabuľky, ktorý novú tabuľku priradí k tej, ktorá už je súčasťou modelu.

  1. V hornej časti zoznamu polí kontingenčnej tabuľky kliknutím na položku Všetko zobrazte úplný zoznam tabuliek, ktoré sú k dispozícii.

  2. Posuňte sa do dolnej časti zoznamu. Tu nájdete nové tabuľky, ktoré ste práve pridali.

  3. Rozbaľte zoznam Obchody.

  4. Presuňte položku StoreName (Názov obchodu) do oblasti Filtre.

  5. Všimnite si, že Excel zobrazí výzvu na vytvorenie vzťahu. Toto oznámenie sa zobrazí, pretože ste použili polia z tabuľky, ktorá s modelom nesúvisí.

  6. Kliknutím na tlačidlo Vytvoriť otvorte dialógové okno Vytvorenie vzťahu.

  7. V časti Tabuľka vyberte položku FactSales. V používaných vzorových údajoch obsahuje tabuľka FactSales podrobné informácie o predaji a cene týkajúce sa podnikania spoločnosti Contoso, ako aj kľúče k iným tabuľkám vrátane kódov obchodu nachádzajúcich sa aj v súbore Stores.xlsx, ktorý ste importovali v predchádzajúcom kroku.

  8. V stĺpci (cudzí) vyberte položku StoreKey.

  9. V súvisiacej tabuľke vyberte položku Stores (Obchody).

  10. V súvisiacom stĺpci (primárny) vyberte položku StoreKey.

  11. Kliknite na tlačidlo OK.

Excel na pozadí zostaví údajový model, ktorý možno globálne použiť v celom zošite v ľubovoľnom počte kontingenčných tabuliek, grafov alebo zostáv Power View. Základom pre tento model sú vzťahy tabuliek, ktoré určujú navigáciu a výpočet údajov v zostave kontingenčnej tabuľky. V nasledujúcej úlohe manuálne vytvoríte vzťahy na prepojenie údajov, ktoré ste práve importovali.

Pridanie vzťahov

Pre všetky nové tabuľky, ktoré importujete, môžete systematicky vytvárať vzťahy tabuliek. Ak zošit zdieľate s kolegami, tí môžu preddefinované vzťahy oceniť, keďže údaje nemusia poznať tak dobre ako vy.

Pri manuálnom vytváraní vzťahov budete naraz pracovať s dvomi tabuľkami. Pre každú tabuľku vyberiete stĺpce, ktoré Excelu oznámia, akým spôsobom sa majú vyhľadať súvisiace riadky v ďalšej tabuľke.

Nová úloha

Vytvorenie vzťahu medzi podkategóriou ProductSubcategory a kategóriou ProductCategory

  1. V Exceli kliknite na položkyÚdaje > Vzťahy > Nový.

  2. V časti Tabuľka vyberte položku DimProductSubcategory.

  3. V stĺpci (cudzí) vyberte položku ProductCategoryKey.

  4. V časti Súvisiaca tabuľka vyberte položku Table_ProductCategory.accdb.

  5. V súvisiacom stĺpci (primárny) vyberte položku ProductCategoryKey.

  6. Kliknite na tlačidlo OK.

  7. Zavrite dialógové okno Správa vzťahov.

Pridanie kategórií do kontingenčnej tabuľky

Hoci sa údajový model aktualizoval, aby zahŕňal ďalšie tabuľky a vzťahy, kontingenčná tabuľka ich ešte nepoužíva. V tejto úlohe pridáte do zoznamu polí kontingenčnej tabuľky kategóriu ProductCategory (Kategória produktu).

  1. V poliach kontingenčnej tabuľky kliknutím na položku Všetko zobrazte tabuľky existujúce v údajovom modeli.

  2. Posuňte sa na spodok zoznamu.

  3. Z oblasti Riadky odstráňte položku BrandName (Názov značky).

  4. Rozbaľte súbor Table_DimProductCategories.accdb.

  5. Presuňte položku ProductCategoryName (Názov kategórie produktu) do oblasti Riadky a umiestnite ju nad položku ProductSubcategory (Podkategória produktu).

  6. Na table Polia kontingenčnej tabuľky kliknutím na položku Aktívne overte, či tabuľky, ktoré ste práve použili, sú už pre kontingenčnú tabuľku aktívne.

Kontrolný bod: Zopakujte si, čo ste sa naučili

Teraz máte kontingenčnú tabuľku, ktorá obsahuje údaje z viacerých tabuliek, pričom niektoré z nich ste importovali v neskoršom kroku. Na spojenie týchto údajov je potrebné vytvoriť vzťahy tabuliek, ktoré Excel používa na koreláciu riadkov. Zistili ste, že stĺpce, ktoré obsahujú zhodné údaje, sú nevyhnutné na vyhľadávanie súvisiacich riadkov. V súboroch so vzorovými údajmi obsahujú všetky tabuľky stĺpec, ktorý možno na tento účel použiť.

Hoci je už kontingenčná tabuľka funkčná, pravdepodobne ste si všimli niekoľko vecí, ktoré by sa mohli vylepšiť. Zdá sa, že v zozname polí kontingenčnej tabuľky sa nachádzajú ďalšie tabuľky (DimEntity) a stĺpce (ETLLoadID), ktoré nesúvisia s podnikaním spoločnosti Contoso. Okrem toho sme ešte stále neintegrovali údaje z tabuľky Geografia.

Ďalší krok: Zobrazenie a rozšírenie modelu pomocou doplnku PowerPivot

V ďalšej skupine úloh použijete doplnok Microsoft Office PowerPivot v Microsoft Exceli 2013 na rozšírenie modelu. Zistíte, že vzťahy dokážete vytvoriť omnoho jednoduchšie pomocou zobrazenia diagramu, ktoré je v tomto doplnku k dispozícii. Doplnok tiež použijete na vytvorenie výpočtov a hierarchií, na skrytie položiek, ktoré sa nemajú v zozname polí zobrazovať, a na optimalizáciu údajov s cieľom ďalšieho vytvárania zostáv.

Poznámka :  Doplnok PowerPivot v Microsoft Exceli 2013 je k dispozícii v balíku Office Professional Plus. Ďalšie informácie nájdete v téme DoplnokPower Pivot v Microsoft Exceli 2013.

Pridanie doplnku PowerPivot na pás s nástrojmi Excelu zapnutím doplnku PowerPivot.

  1. Kliknite na položky Súbor > Možnosti > Doplnky.

  2. V poli Spravovať kliknite na položku Doplnky architektúry COM a potom kliknite na tlačidlo Spustiť.

  1. Začiarknite políčko Microsoft Office PowerPivot v Microsoft Exceli 2013 a potom kliknite na tlačidlo OK.

Na páse s nástrojmi pribudla karta PowerPivot.

Pridanie vzťahu pomocou zobrazenia diagramu v doplnku PowerPivot

  1. V Exceli kliknite na Hárok3, čím sa hárok stane aktívnym. Hárok3 obsahuje tabuľku Geografia, ktorú ste importovali predtým.

  2. Na páse s nástrojmi kliknite na položky PowerPivot > Pridať do modelu údajov. Týmto krokom do modelu pridáte tabuľku Geografia a tiež otvoríte doplnok PowerPivot, ktorý použijete na vykonanie ostatných krokov úlohy.

  3. Všimnite si, že okno PowerPivot zobrazuje všetky tabuľky modelu vrátane tabuľky Geografia. Kliknite na niekoľko tabuliek. V doplnku sa zobrazia všetky údaje, ktoré model obsahuje.

  4. V okne doplnku PowerPivot kliknite v časti Zobraziť na položku Zobrazenie diagramu.

  5. Pomocou pruhu jazdca zmeňte veľkosť diagramu tak, aby ste mohli vidieť všetky objekty v diagrame. Všimnite si, že dve tabuľky nesúvisia so zvyškom diagramu: DimEntity a Geografia.

  6. Pravým tlačidlom myši kliknite na tabuľku DimEntity a potom kliknite na položku Odstrániť. Táto tabuľka je artefaktom pôvodnej databázy a nie je v modeli potrebná.

  7. Priblížte tabuľku Geografia, aby bolo vidno všetky jej polia. Pomocou jazdca môžete zväčšiť diagram tabuľky.

  8. Všimnite si, že tabuľka Geografia obsahuje stĺpec GeographyKey. Ten obsahuje hodnoty, ktoré jedinečne identifikujú každý riadok tabuľky Geografia. Zistime, či tento kľúč používajú aj iné tabuľky v modeli. Ak áno, môžeme vytvoriť vzťah, ktorý spája tabuľku Geografia so zvyškom modelu.

  9. Kliknite na položku Hľadať.

  10. V časti Hľadať metaúdaje zadajte výraz GeographyKey.

  11. Niekoľkokrát kliknite na položku Hľadať nasledujúce. Všimnite si, že stĺpec GeographyKey sa zobrazí v tabuľke Geografia aj v tabuľke Obchody.

  12. Premiestnite tabuľku Geografia tak, aby sa nachádzala vedľa tabuľky Obchody.

  13. Presuňte stĺpec GeographyKey v tabuľke Stores (Obchody) k stĺpcu GeographyKey v tabuľke Geografia. PowerPivot nakreslí čiaru medzi týmito dvomi stĺpcami, ktorá naznačuje ich vzťah.

V tejto úlohe ste sa naučili nový spôsob pridávania tabuliek a vytvárania vzťahov. Teraz máte úplne integrovaný model so všetkými prepojenými tabuľkami, ktoré sú k dispozícii pre kontingenčnú tabuľku v Hárku1.

Tip :  V zobrazení diagramu je niekoľko úplne rozbalených diagramov tabuliek zobrazujúcich stĺpce ako ETLLoadID, LoadDate a UpdateDate. Tieto konkrétne polia sú artefaktmi z pôvodného skladu údajov spoločnosti Contoso pridané na podporu rozbaľovania údajov a operácie načítavania. Vo svojom modeli ich nebudete potrebovať. Ak ich chcete odstrániť, zvýraznite príslušné pole, kliknite naň pravým tlačidlom myši a potom kliknite na položku Odstrániť .

Vytvorenie vypočítavaného stĺpca

V doplnku PowerPivot môžete na pridávanie výpočtov používať výrazy DAX (Data Analysis Expression). V tejto úlohe vypočítate celkový zisk a pridáte vypočítavané stĺpce, ktoré odkazujú na hodnoty údajov z iných tabuliek. Neskôr uvidíte, ako stĺpce s odkazmi používať na zjednodušenie modelu.

  1. V okne PowerPivot prepnite späť na zobrazenie údajov.

  2. Premenujte tabuľku Table_ProductCategories accdb vhodnejším názvom. Na túto tabuľku budete odkazovať pri vykonávaní nasledujúcich krokov a kratší názov tabuľky umožní jednoduchšie čítanie výpočtov. Kliknite pravým tlačidlom myši na názov tabuľky, kliknite na položku Premenovať, zadajte výraz ProductCategories (Kategória produktu) a stlačte kláves Enter.

  3. Vyberte tabuľku FactSales.

  4. Kliknite na položky Návrh > Stĺpce > Pridať.

  5. Do riadka vzorcov nad tabuľkou zadajte nasledujúci vzorec. Automatické dokončovanie pomáha pri zadávaní úplných názvoch stĺpcov a tabuliek a zobrazuje zoznam dostupných funkcií. Môžete tiež kliknúť na príslušný stĺpec a PowerPivot do vzorca pridá názov stĺpca.

    = [SalesAmount] - [TotalCost] - [ReturnAmount]

  6. Po vytvorení vzorca stlačte kláves Enter, čím sa vzorec prijme.

    Hodnoty pre všetky riadky vo vypočítavanom stĺpci sa vyplnia. Ak sa posuniete nadol cez celú tabuľku, uvidíte, že riadky môžu pre tento stĺpec obsahovať rôzne hodnoty na základe údajov v jednotlivých riadkoch.

  7. Pravým tlačidlom myši kliknite na položku Vypočítaný stĺpec1 a vyberte položku Premenovať stĺpec. Zadajte výraz Zisk a stlačte kláves Enter.

  8. Teraz vyberte tabuľku DimProduct .

  9. Kliknite na položky Návrh > Stĺpce > Pridať.

  10. Do riadka vzorcov nad tabuľkou zadajte nasledujúci vzorec.

    = RELATED(ProductCategories[ProductCategoryName])

    Funkcia RELATED vráti hodnotu zo súvisiacej tabuľky. V tomto prípade tabuľka ProductCategories (Kategória produktu) zahŕňa názvy kategórií produktov, ktoré budú v tabuľke DimProduct (Produkt) užitočné pri zostavovaní hierarchie zahŕňajúcej informácie o kategórii. Ďalšie informácie o tejto funkcii nájdete v téme Funkcia RELATED (DAX).

  11. Po vytvorení vzorca stlačte kláves Enter, čím sa vzorec prijme.

    Hodnoty pre všetky riadky vo vypočítavanom stĺpci sa vyplnia. Ak sa posuniete nadol cez celú tabuľku, uvidíte, že každý riadok teraz obsahuje názov kategórie produktu.

  12. Pravým tlačidlom myši kliknite na položku Vypočítaný stĺpec1 a vyberte položku Premenovať stĺpec. Zadajte výraz KategóriaProduktu a stlačte kláves Enter.

  13. Kliknite na položky Návrh > Stĺpce > Pridať.

  14. Do riadka vzorcov nad tabuľkou zadajte nasledujúci vzorec a potom použitie vzorca potvrďte stlačením klávesu Enter.

    = RELATED(DimProductSubcategory[ProductSubcategoryName])

  15. Pravým tlačidlom myši kliknite na položku Vypočítaný stĺpec1 a vyberte položku Premenovať stĺpec. Zadajte výraz PodkategóriaProduktu a stlačte kláves Enter.

Vytvorenie hierarchie

Väčšina modelov obsahuje údaje, ktoré sú vo svojej podstate hierarchické. Medzi bežné príklady patria údaje kalendára, geografické údaje a kategórie produktov. Vytváranie hierarchií je užitočné, pretože môžete presunúť jednu položku (hierarchiu) do zostavy namiesto vytvárania a usporadúvania tých istých polí znova a znova.

  1. V doplnku PowerPivot prepnite na zobrazenie diagramu. Rozbaľte tabuľku DimDate, aby ste ľahšie videli všetky jej polia.

  2. Stlačte a podržte kláves Ctrl a kliknite na stĺpec CalendarYear, CalendarQuarter a CalendarMonth (musíte sa posunúť v dolnej časti tabuľky).

  3. Označte tri stĺpce, kliknite na ne pravým tlačidlom myši a potom kliknite na položku Vytvoriť hierarchiu. Nadradený uzol hierarchie s názvom Hierarchia 1, sa vytvorí v dolnej časti tabuľky a vybraté stĺpce sa skopírujú pod hierarchiu ako podriadené uzly.

  4. Zadajte výraz Dátumy ako názov novej hierarchie.

  5. Pridajte do hierarchie stĺpec FullDateLabel (Označenie s úplným dátumom). Pravým tlačidlom myši kliknite na položku FullDateLabel (Označenie s úplným dátumom) a vyberte položku Pridať do hierarchie. Vyberte položku Dátum. Stĺpec FullDateLabel (Označenie s úplným dátumom) obsahuje celý dátum vrátane roka, mesiaca a dňa. Presvedčte sa, že stĺpec FullDateLabel (Označenie s úplným dátumom) sa v hierarchii zobrazuje ako posledný. Máte k dispozícii hierarchiu s viacerými úrovňami zahŕňajúcu rok, štvrťrok, mesiac a jednotlivé dni kalendára.

  6. Ešte stále v zobrazení diagramu ukážte na tabuľku DimProduct a potom kliknite na tlačidlo Vytvoriť hierarchiu v hlavičke tabuľky. Nadradený uzol prázdnej hierarchie sa zobrazí v spodnej časti tabuľky.

  7. Zadajte výraz Kategórie produktov ako názov novej hierarchie.

  8. Ak chcete vytvoriť podriadené uzly hierarchie, presuňte stĺpce ProductCategory (Kategória produktu) a ProductSubcategory (Podkategória produktu) do hierarchie.

  9. Pravým tlačidlom myši kliknite na názov ProductName a vyberte položku Pridať do hierarchie. Vyberte položku Kategórie produktov.

Teraz, keď už ovládate niekoľko rôznych spôsobov vytvorenia hierarchie, ich môžete použiť v kontingenčnej tabuľke.

  1. Vráťte sa späť do Excelu.

  2. V hárku Hárok1 (hárok obsahujúci kontingenčnú tabuľku) odstráňte polia v oblasti Riadky.

  3. Nahraďte ich novou hierarchiou Kategórie produktov v tabuľke DimProduct.

  4. Podobne nahraďte stĺpec CalendarYear (Kalendárny rok) v oblasti Stĺpce hierarchiou Dátumy v tabuľke DimDate (Dátum).

Keď preskúmate údaje teraz, ihneď zbadáte výhody používania hierarchií. Môžete nezávisle rozbaliť a zavrieť rôzne oblasti kontingenčnej tabuľky, pričom budete disponovať väčšími možnosťami ovládania spôsobu využitia dostupného priestoru. Okrem toho pridaním jednej hierarchie do oblastí Riadky a Stĺpce okamžite prejdete na množstvo podrobností bez toho, že by ste museli na získanie podobného efektu skladať viacero polí.

Skrytie stĺpcov

Teraz ste vytvorili hierarchiu kategórií produktov a umiestnili ste ju do tabuľky DimProduct, a tak už viac nepotrebujete pole DimProductCategory ani DimProductSubcategory v zozname polí kontingenčnej tabuľky. V tejto úlohe sa naučíte, ako skryť nepotrebné tabuľky a stĺpce, ktoré zaberajú miesto v zozname polí kontingenčnej tabuľky. Skrytím tabuliek a stĺpcov vylepšíte možnosti tvorby zostáv bez ovplyvnenia modelu, ktorý poskytuje vzťahy medzi údajmi a výpočty.

Nová úloha

Môžete skryť jednotlivé stĺpce, rozsah stĺpcov alebo celú tabuľku. Názvy tabuliek a stĺpcov sa zobrazujú sivou farbou, čím sa naznačuje, že sú skryté pre klientov, ktorí na vytváranie zostáv využívajú model. Skryté stĺpce sa v modeli zobrazujú sivou farbou s cieľom naznačiť ich stav, no v zobrazení údajov ostávajú viditeľné, aby ste s nimi mohli naďalej pracovať.

  1. Skontrolujte, či je v doplnku PowerPivot vybraté zobrazenie údajov.

  2. V dolnej časti kariet kliknite pravým tlačidlom myši na pole DimProductSubcategory a začiarknite políčko Skryť v klientskych nástrojoch.

  3. Postup zopakujte pre tabuľku ProductCategories.

  4. Otvorte tabuľku DimProduct.

  5. Pravým tlačidlom kliknite na nasledujúce stĺpce a potom kliknite na položku Skryť v klientskych nástrojoch:

    • ProductKey (Kód Product Key)

    • ProductLabel (Označenie produktu)

    • ProductSubcategory (Podkategória produktu)

  6. Vyberte viacero susediacich stĺpcov. Začnite so stĺpcom ClassID (Identifikácia triedy) a pokračujte až k stĺpcu Podkategória produktu na konci. Kliknutím pravým tlačidlom myši ich skryte.

  7. Postup zopakujte pre ďalšie tabuľky, čím odstráňte identifikácie, kľúče alebo ostatné podrobnosti, ktoré nechcete použiť v tejto zostave.

Pomocou zoznamu polí kontingenčnej tabuľky prepnite späť do Excelu do hárka Hárok1 a pozrite si rozdiel. Počet tabuliek sa znížil a tabuľka DimProduct obsahuje len tie položky, ktoré pravdepodobne použijete pri analýze predaja.

Vytvorenie zostavy Power View

Zostavy kontingenčnej tabuľky nie sú jediným typom zostavy, ktorá využíva výhody údajového modelu. Použitím toho istého modelu, ktorý ste práve zostavili, môžete pridať hárok doplnku Power View a vyskúšať niektoré rozloženia, ktoré ponúka.

  1. V Exceli kliknite na položky Vložiť > Power View.

    Poznámka :  Ak doplnok Power View v tomto počítači používate po prvý raz, zobrazí sa výzva, aby ste najskôr doplnok povolili a nainštalovali doplnok Silverlight.

  2. Na table Polia doplnku Power View kliknite na šípku vedľa tabuľky FactSales a potom kliknite na položku SalesAmount (Objem predaja).

  3. Rozbaľte tabuľku Geografia kliknite na položku RegionCountryName (Názov krajiny alebo oblasti).

  4. Na páse s nástrojmi kliknite na položku Mapa.

  5. Zobrazí sa zostava mapy. Posunutím rohu môžete zmeniť jej veľkosť. Modré kruhy rozličnej veľkosti na mape signalizujú výsledky predaja pre rôzne krajiny alebo oblasti.

Optimalizácia na tvorbu zostáv funkcie Power View

Vykonaním niekoľkých drobných zmien modelu zabezpečíte intuitívnejšie odpovede pri navrhovaní zostáv Power View. V tejto úlohe pridáte URL adresy webových lokalít pre niekoľkých výrobcov a následne tieto údaje kategorizujete ako webové URL adresy, takže URL adresy sa budú zobrazovať ako prepojenia.

Ako prvý krok pridajte URL adresu do svojho zošita.

  1. Otvorte v Exceli nový hárok a skopírujte tieto hodnoty:

URLAdresaVýrobcu

IDVýrobcu

http://www.contoso.com

Contoso, LTD

http://www.adventure-works.com

Adventure Works

http://www.fabrikam.com

Fabrikam, Inc.

  1. Formátujte bunky ako tabuľku a potom tabuľku pomenujte ako URL adresa.

  2. Vytvorte vzťah medzi tabuľkou URL adresa a tabuľkou obsahujúcou mená výrobcov, DimProduct (Produkt):

    1. Kliknite na položky Údaje > Vzťahy. Zobrazí sa dialógové okno Vytvoriť vzťah.

    2. Kliknite na tlačidlo Nový.

    3. V časti Tabuľka vyberte položku DimProduct.

    4. V časti Stĺpec vyberte položku Manufacturer.

    5. V časti Súvisiaca tabuľka vyberte položku URL adresa.

    6. V časti Súvisiaci stĺpec (primárny) vyberte položku ManufacturerID.

Ak chcete porovnať výsledky pred a po, vytvorte novú zostavu Power View a pridajte do nej tabuľky FactSales | SalesAmount, dimProduct | Manufacturer a URL adresa | ManufacturerURL. Všimnite si, že URL adresy sa zobrazujú ako statický text.

Vykreslenie URL ako aktívneho hypertextového prepojenia vyžaduje kategorizáciu. Na kategorizáciu stĺpca je potrebné použiť PowerPivot.

  1. V doplnku PowerPivot otvorte položku URL adresa.

  2. Vyberte položku ManufacturerURL (URL adresa výrobcu).

  3. Kliknite na položky Rozšírené > Vlastnosti zostáv > Kategória údajov: Nekategorizované.

  4. Kliknite na šípku nadol.

  5. Vyberte položku Webová URL adresa.

  6. V Exceli kliknite na položky Vložiť > Power View.

  7. V poliach doplnku Power View vyberte tabuľky FactSales | SalesAmount, dimProduct | Manufacturer a URL adresa | ManufacturerURL. Tentoraz sa URL adresy zobrazia ako skutočné hypertextové prepojenia.

Medzi ďalšie optimalizácie funkcie Power View patrí definovanie predvolenej množiny polí pre každú tabuľku a nastavenie vlastností, ktoré určujú, či sa riadky s opakujúcimi sa údajmi agregujú alebo uvádzajú nezávisle. Ďalšie informácie nájdete v článkoch Konfigurácia predvolenej množiny polí pre zostavy funkcie Power ViewKonfigurácia vlastností správania tabuľky pre zostavy funkcie Power View.

Vytvorenie vypočítavaných polí

Pri druhej úlohe, preskúmavaní údajov pomocou kontingenčnej tabuľky, ste klikli na pole SalesAmount (Objem predaja) v zobrazení polí kontingenčnej tabuľky. Keďže stĺpec SalesAmount (Objem predaja) je numerický, automaticky sa v kontingenčnej tabuľke umiestnil do oblasti Hodnoty. Súčet stĺpca SalesAmount (Objem predaja) bol potom pripravený na výpočet objemov predaja pre ľubovoľné použité filtre. V tomto prípade sa najskôr nepoužívajú žiadne filtre, no potom sa použijú filtre CalendarYear (Kalendárny rok), ProductSubcategoryName (Názov podkategórie produktu) a BrandName (Názov značky).

To čo ste vlastne urobili, je, že ste vytvorili implicitné vypočítavané pole, zjednodušujúce analýzu predaných objemov z tabuľky FactSales v porovnaní s inými poľami, napríklad kategóriou produktov, oblasťou a dátumami. Implicitné vypočítavané polia Excel vytvorí vtedy, keď pole presuniete do oblasti Hodnota alebo keď kliknete na číselné pole, ako v prípade stĺpca SalesAmount (Objem predaja). Implicitné vypočítavané polia sú vzorcami, ktoré používajú štandardné funkcie agregácie, akými sú automaticky vytvorené funkcie SUM, COUNT a AVERAGE.

Existujú však aj iné typy vypočítavaných polí. V doplnku PowerPivot môžete vytvoriť aj explicitné vypočítavané polia. Na rozdiel od implicitných vypočítavaných polí, ktoré je možné použiť len v kontingenčnej tabuľke, v ktorej boli vytvorené, je explicitné vypočítavané polia možné použiť v ľubovoľnej kontingenčnej tabuľke zošita alebo v ľubovoľnej zostave používajúcej ako zdroj údajov údajový model. V explicitných vypočítavaných poliach vytvorených v doplnku PowerPivot môžete používať automatický súčet a automaticky vytvoriť vypočítavané polia pomocou štandardných agregácií alebo môžete pomocou vzorcov s výrazmi DAX (Data Analysis Expression) vytvoriť vlastné.

Ako predpokladáte, tvorba vypočítavaných polí vám môže výrazne pomôcť s analýzou údajov, preto sa ich rýchlo naučme vytvárať.

Ak používate funkciu Automatický súčet, vytvorenie vypočítavaných polí v doplnku PowerPivot je jednoduché.

  1. V tabuľke FactSales kliknite na stĺpec Zisk.

  2. Kliknite na položky Výpočty > Automatický súčet. Všimnite si nové vypočítavané pole nazvané Suma z Zisk, ktoré sa automaticky vytvorilo v bunke v oblasti výpočtu priamo pod stĺpcom Zisk.

  3. V excelovom hárku Hárok1 kliknite v zozname polí v stĺpci FactSales na pole Suma z Zisk.

Hotovo. Toto je všetko, čo treba spraviť, aby ste vytvorili vypočítavané pole pomocou štandardnej agregácie v doplnku PowerPivot. Ako vidíte, za niekoľko minút ste vytvorili vypočítavané pole so súčtom ziskov a pridali ho do kontingenčnej tabuľky, čím ste zjednodušili analýzu ziskov podľa použitých filtrov. V tomto prípade sa zobrazí pole Suma z Zisk filtrované hierarchiami Kategória produktov a Dátumy.

Čo však v prípade, keď potrebujete podrobnejšiu analýzu, napríklad čísla predaja pre určitý kanál, produkt alebo kategóriu? Na to je potrebné vytvoriť ďalšie vypočítavané pole, ktoré na základe použitých filtrov spočíta počet riadkov, pričom každý z nich slúži pre jeden predaj uvedený v tabuľke FactSales.

  1. V tabuľke FactSales kliknite na stĺpec SalesKey.

  2. V časti Výpočty kliknite na šípku nadol pri položkách Automatický súčet > Počet.

  3. Kliknite pravým tlačidlom myši na položku Počet SalesKey v oblasti výpočtov, vyberte možnosť Premenovať a premenujte nové vypočítavané pole. Zadajte výraz Počet a potom stlačte kláves Enter.

  4. V excelovom hárku Hárok1 kliknite v zozname polí v stĺpci FactSales, na položku Počet.

Všimnite si, že do kontingenčnej tabuľky sa pridal nový stĺpec Počet zobrazujúci počet predajov podľa použitých filtrov. Rovnako ako v prípade vypočítavaného poľa Suma z Zisk sa stĺpec Počet zobrazí po filtrovaní hierarchiami Kategória produktov a Dátumy.

Vytvorme ďalšie vypočítavané pole. Tentokrát pôjde o vypočítavané pole počítajúce percento celkového predaja pre konkrétny kontext alebo filter. Na rozdiel od predchádzajúcich vypočítavaných polí vytvorených pomocou automatického súhrnu sa tentoraz vzorec zadá manuálne.

  1. V tabuľke FactSales kliknite v oblasti výpočtu na prázdnu bunku. Tip: Bunka vľavo hore je výborným miestom, kde možno začať umiestňovať vypočítavané polia. Dajú sa tak jednoduchšie nájsť. Všetky vypočítavané polia v oblasti výpočtu je možné presúvať.

  2. Písaním do riadka vzorcov a využitím funkcie IntelliSense vytvorte nasledujúci vzorec: Percentage of All Products:=[Count]/CALCULATE([Count], ALL(DimProduct))

  3. Stlačte kláves ENTER a potvrďte vzorec.

  4. V excelovom hárku nazvanom Hárok1 kliknite v zozname polí v tabuľke FactSales na pole Percentage of All Products (Percento všetkých produktov).

  5. V kontingenčnej tabuľke vyberte viacero stĺpcov Percentage of All Products (Percento všetkých produktov).

  6. Na karte Domov kliknite na položky Číslo > Percento. Na formátovanie nového stĺpca použite dve desatinné miesta.

Toto nové vypočítavané pole vypočíta percento celkového predaja pre daný kontext filtra. V tomto prípade sú kontextom filtra stále hierarchie Kategória produktov a Dátumy. Môžete napríklad vidieť, že percento celkového predaja produktov v prípade počítačov sa v priebehu rokov zvýšilo.

Tvorba vzorcov pre vypočítavané stĺpce a vypočítavané polia bude pomerne jednoduchá, ak budete vedieť vytvárať excelové vzorce. Bez ohľadu na to, či už poznáte excelové vzorce alebo nie, skvelým spôsobom, ako sa oboznámiť so základmi vzorcov DAX, je prejsť si lekcie kurzu Stručný úvod: Základy vzorcov DAX za 30 minút.

Uloženie práce

Uložte si svoj zošit, aby ste ho mohli použiť v iných kurzoch alebo na ďalšie objavovanie.

Ďalšie kroky

Napriek tomu, že údaje môžete jednoducho importovať z Excelu, pomocou doplnku PowerPivot je import rýchlejší a efektívnejší. Môžete filtrovať importované údaje a vylúčiť stĺpce, ktoré nebudete potrebovať. Môžete si tiež vybrať, či údaje načíta zostavovač dotazov alebo riadok dotazov. V ďalšom kroku sa oboznámite s týmito alternatívnymi prístupmi: Načítanie údajov z údajového informačného kanála v doplnku Power Pivot a Import údajov zo služby Analysis Services alebo doplnku Power Pivot.

Zostavy funkcie Power View sú navrhnuté tak, aby fungovali s údajovými modelmi podobnými tomu, aký ste práve zostavili. Čítajte ďalej a získajte ďalšie informácie o vizualizáciách bohatých údajov, ktoré funkcia Power View umožňuje v programe Excel: Spustenie funkcie Power View v programe Excel 2013Power View: skúmanie, vizualizácia a prezentovanie údajov.

Skúste svoj údajový model vylepšiť, aby vytváral ešte lepšie zostavy Power View, pomocou pokynov v téme Kurz: Optimalizácia modelu údajov na vytváranie zostáv funkcie Power View

Rozšírte svoje zručnosti
Preskúmať školenie
Buďte medzi prvými, ktorí získajú nové funkcie
Pridajte sa k insiderom pre Office

Boli tieto informácie užitočné?

Ďakujeme za vaše pripomienky!

Ďakujeme vám za pripomienky. Pravdepodobne vám pomôže, ak vás spojíme s pracovníkom podpory pre Office.

×