Kurz: Importovanie údajov do Excelu a vytvorenie dátového modelu

Kurz obsahuje tieto časti:

• Importovanie údajov z databázy

• Importovanie údajov z tabuľkového hárka

• Importovanie údajov pomocou kopírovania a prilepovania

• Vytvorenie vzťahu medzi importovanými údajmi

• Kontrolný bod a kvíz

Na konci kurzu sa nachádza kvíz, v ktorom môžete otestovať, čo ste sa naučili.

V tejto sérii kurzov sa používajú údaje o olympijských medailách, usporiadateľských krajinách a rôznych olympijských športových podujatiach. Odporúčame prejsť všetkými krokmi kurzu v tomto poradí. 

Importovanie údajov z databázy

V tomto kurze začneme prázdnym zošitom. Cieľom tejto časti je pripojiť sa k externému zdroju údajov a importovať tieto údaje do Excelu na ďalšiu analýzu.

Začneme stiahnutím niektorých údajov z internetu. Údaje sa týkajú olympijských medailí a tvoria databázu Microsoft Accessu.

1. Kliknutím na nasledujúce prepojenie stiahnete súbory, ktoré budeme v tejto sérii kurzov používať. Stiahnite si každý zo štyroch súborov do umiestnenia, ktoré je ľahko prístupné, ako napríklad Stiahnuté súbory alebo Moje dokumenty, alebo do nového priečinka, ktorý vytvoríte:
   databáza programu Access
   > OlympicMedals.accdb > OlympicSports.xlsx excelového zošita
   > Population.xlsx excelového zošita
   > DiscImage_table.xlsx excelového zošita

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

3. Kliknite na položky Údaje > Získať externé údaje > Z programu Access. Pás s nástrojmi sa dynamicky prispôsobí šírke zošita, preto sa príkazy na páse s nástrojmi môžu javiť trochu inak ako na nasledujúcich obrazovkách. Na prvej obrazovke sa zobrazuje pás s nástrojmi, keď je zošit široký, na druhom obrázku zošit, ktorého veľkosť bola zmenená tak, aby zaberala iba časť obrazovky.
   
   
   
   
   
    

4. Vyberte stiahnutý súbor OlympicMedals.accdb a kliknite na položku Otvoriť. Zobrazí sa nasledujúce okno Výber tabuľky obsahujúce tabuľky nájdené v databáze. Tabuľky v databáze sú podobné zošitom alebo tabuľkám v Exceli. Začiarknite políčko Povoliť výber viacerých tabuliek a vyberte všetky tabuľky. Potom kliknite na tlačidlo OK.
   
   

5. Zobrazí sa okno Import údajov.

   Poznámka: Všimnite si začiarkavacie políčko v dolnej časti okna, ktoré umožňuje pridať tieto údaje do dátového modelu, zobrazené na nasledujúcej obrazovke. Dátový model sa vytvorí automaticky pri importe alebo práci s dvoma alebo viacerými tabuľkami súčasne. Dátový model integruje tabuľky a umožňuje rozsiahlu analýzu pomocou kontingenčných tabuliek, Power Pivot a Funkcie Power View. Pri importovaní tabuliek z databázy sa existujúce vzťahy medzi týmito tabuľkami použijú na vytvorenie dátového modelu v Exceli. Dátový model je v Exceli priehľadný, ale môžete ho zobraziť a upraviť priamo pomocou doplnku Power Pivot. Dátový model je podrobnejšie popísaný ďalej v tomto kurze.

   
   Vyberte možnosť Zostava kontingenčnej tabuľky , ktorá importuje tabuľky do Excelu a pripraví kontingenčnú tabuľku na analýzu importovaných tabuliek, a kliknite na tlačidlo OK.
   
   

6. Po importovaní údajov sa z importovaných tabuliek vytvorí kontingenčná tabuľka.
   
   

Po importovaní údajov do Excelu a automatickom vytvorení dátového modelu môžete začať údaje skúmať.

Prieskum údajov pomocou kontingenčnej tabuľky

Skúmanie importovaných údajov pomocou kontingenčnej tabuľky je jednoduché. V kontingenčnej tabuľke potiahnite myšou polia (podobne ako stĺpce v Exceli) z tabuliek (napríklad tabuliek, ktoré ste práve naimportovali z accessovej databázy) do rôznych oblastí kontingenčnej tabuľky, čím upravíte zobrazenie svojich údajov. Kontingenčná tabuľka obsahuje štyri oblasti: FILTRE, STĹPCE, RIADKY a HODNOTY.

Na určenie oblasti, do ktorej sa má pole presunúť, môže chvíľu trvať experimentovanie. Zo svojich tabuliek môžete presúvať ľubovoľný počet polí, kým kontingenčná tabuľka nezobrazí údaje podľa vašich predstáv. Neváhajte a preskúmajte presunutím polí do rôznych oblastí kontingenčnej tabuľky. usporiadanie polí v kontingenčnej tabuľke nemá vplyv na základné údaje.

Preskúmajme údaje o olympijských medailách v kontingenčnej tabuľke, pričom začneme olympijskými medailistami usporiadanými podľa disciplíny, druhu medaily a krajiny či oblasti športovca.

1. V poliach kontingenčnej tabuľky rozbaľte tabuľku Medals kliknutím na šípku vedľa nej. V rozbalenej tabuľke Medals nájdite pole NOC_CountryRegion a potiahnite ho do oblasti STĹPCE. NOC znamená National Olympic Committees (Národné olympijské výbory), čiže organizačné jednotky pre danú krajinu alebo oblasť.

2. Potom z tabuľky Disciplines potiahnite položku Discipline do oblasti RIADKY.

3. Položku Disciplines vyfiltrujeme tak, aby obsahovala iba päť športov: lukostreľbu, skok do vody, šerm, krasokorčuľovanie a rýchlokorčuľovanie. Môžete to urobiť v oblasti polí kontingenčnej tabuľky alebo vo filtri Menovky riadkov v samotnej kontingenčnej tabuľke.

   1. Kliknutím na ľubovoľné miesto v kontingenčnej tabuľke skontrolujte, či je vybratá kontingenčná tabuľka programu Excel. V zozname polí kontingenčnej tabuľky , kde je tabuľka Disciplines rozbalená, ukážte kurzorom na pole Discipline a napravo od poľa sa zobrazí šípka rozbaľovacieho zoznamu. Kliknite na rozbaľovací zoznam, kliknutím na položku (Vybrať všetko) odstráňte všetky výbery, potom sa posuňte nadol a vyberte možnosti Lukostreľba, Skoky do potápania, Oplotenie, Krasokarovanie a Rýchlokorčuľovanie. Kliknite na tlačidlo OK.

   2. Alebo v časti Menovky riadkov kontingenčnej tabuľky kliknite na rozbaľovací zoznam vedľa položky Menovky riadkov v kontingenčnej tabuľke a potom kliknutím na položku (Vybrať všetko) zrušte všetky označenia, prejdite nadol a vyberte možnosti Archery, Diving, Fencing, Figure Skating a Speed Skating. Kliknite na tlačidlo OK.

4. V poliach kontingenčnej tabuľky potiahnite z tabuľky Medals položku Medal do oblasti HODNOTY. Keďže hodnoty musia byť číselné, Excel automaticky zmení hodnotu medailí na hodnotu počet medailí.

5. Z tabuľky Medals vyberte znovu položku Medal a potiahnite ju do oblasti FILTRE.

6. Vyfiltrujeme kontingenčnú tabuľku tak, aby sa zobrazovali iba krajiny či oblasti s celkovým počtom medailí viac než 90. Postup:

   1. V kontingenčnej tabuľke kliknite na rozbaľovací zozname napravo od položky Označenia stĺpcov.

   2. Vyberte možnosť Filtre hodnôt a potom položku Väčšie ako.

   3. Do posledného poľa (napravo) zadajte hodnotu 90. Kliknite na tlačidlo OK.
      

Vaša kontingenčná tabuľka bude vyzerať ako na tejto obrazovke.

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

Čo však v prípade, že vaše údaje pochádzajú z rozličných zdrojov alebo ich importujete neskôr? Vzťahy s novými údajmi môžete zvyčajne vytvoriť 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, ako vytvoriť nové vzťahy.

Importovanie údajov z tabuľkového hárka

Importujme teraz údaje z iného zdroja, tentoraz z existujúceho zošita, a potom zadajme vzťahy medzi existujúcimi údajmi a novými údajmi. Vzťahy vám v Exceli umožňujú analyzovať kolekcie údajov a vytvoriť zaujímavé a pôsobivé vizualizácie z importovaných údajov.

Začneme vytvorením prázdneho zošita, do ktorého potom importujeme údaje z excelového zošita.

1. Vložte nový excelový zošit a pomenujte ho Sports.

2. Prejdite do priečinka obsahujúceho stiahnuté vzorové údajové súbory a otvorte súbor OlympicSports.xlsx.

3. Vyberte a skopírujte údaje do hárka1. Ak vyberiete bunku s údajmi, napríklad bunku A1, môžete stlačením kombinácie klávesov Ctrl + A vybrať všetky susediace údaje. Zavrite zošit OlympicSports.xlsx.

4. V zošite Sports umiestnite kurzor do bunky A1 a údaje prilepte.

5. Kým sú údaje zvýraznené, stlačte kombináciu klávesov Ctrl + T, čím údaje naformátujete ako tabuľku. Údaje môžete ako tabuľku naformátovať aj z pása s nástrojmi výberom položiek DOMOV > Formátovať ako tabuľku. Keďže údaje obsahujú hlavičky, začiarknite políčko Tabuľka obsahuje hlavičky v zobrazenom okne Vytvorenie tabuľky, ako je to zobrazené tu.
   
   
   
   Formátovanie údajov ako tabuľky má mnoho výhod. K tabuľke môžete priradiť názov, pomocou ktorého je jednoduché ju identifikovať. Medzi tabuľkami môžete vytvoriť vzťahy a v kontingenčných tabuľkách, doplnku Power Pivot a Power View umožniť skúmanie a analýzu.

6. Pomenujte tabuľku. Výberom položiek NÁSTROJE TABULIEK > NÁVRH > Vlastnosti vyhľadajte pole Názov tabuľky a zadajte názov Sports. Zošit vyzerá ako na nasledujúcej obrazovke.
   

7. Uložte zošit.

Importovanie údajov pomocou kopírovania a prilepovania

Po importovaní údajov z excelového zošita importujeme údaje z tabuľky, ktorú nájdeme na webovej stránke, alebo z iného zdroja, z ktorého môžeme kopírovať a prilepovať do Excelu. V nasledujúcich krokoch pridáte z tabuľky údaje o usporiadateľských olympijských mestách.

1. Vložte nový excelový zošit a pomenujte ho Hosts.

2. Vyberte a skopírujte nasledujúcu tabuľku vrátane hlavičiek tabuľky.

1. V Exceli umiestnite kurzor do bunky A1 a zošita Hosts a údaje prilepte.

2. Naformátujte údaje ako tabuľku. Ako už bolo uvedené vyššie v kurze, stlačte kombináciu klávesov Ctrl + T, čím naformátujete údaje ako tabuľku, alebo vyberte položky DOMOV > Formátovať ako tabuľku. Keďže údaje majú hlavičky, začiarknite políčko Tabuľka obsahuje hlavičky v zobrazenom okne Vytvorenie tabuľky.

3. Pomenujte tabuľku. Výberom položiek NÁSTROJE TABULIEK > NÁVRH > Vlastnosti vyhľadajte pole Názov tabuľky a zadajte názov Hosts.

4. Vyberte stĺpec Edition a z karty DOMOV ho naformátujte ako číslo s 0 desatinnými miestami.

5. Uložte zošit. Váš zošit bude vyzerať ako na tejto obrazovke.

Po vytvorení excelového zošita s tabuľkami môžete vytvoriť medzi nimi vzťah. Vytvorením vzťahov medzi tabuľkami môžete kombinovať údaje z dvoch tabuliek.

Vytvorenie vzťahu medzi importovanými údajmi

Môžete okamžite začať používať polia v kontingenčnej tabuľke z importovaných tabuliek. Ak Excel nedokáže určiť, ako začleniť pole do kontingenčnej tabuľky, vzťah sa musí vytvoriť pomocou existujúceho dátového modelu. V nasledujúcich krokoch zistíte, ako vytvoriť vzťah medzi údajmi naimportovanými z rôznych zdrojov.

1. V hárku Hárok1 v hornej častipolí kontingenčnej tabuľky kliknutím na položkuVšetko zobrazte úplný zoznam dostupných tabuliek, ako je to znázornené na nasledujúcej obrazovke.
   

2. Posuňte sa v zozname tak, aby sa zobrazili nové tabuľky, ktoré ste práve pridali.

3. Rozbaľte položku Sports a vyberte možnosť Sport, čím ju pridáte do kontingenčnej tabuľky. Všimnite si, že Excel zobrazí výzvu na vytvorenie vzťahu, ako je to zobrazené na nasledujúcej obrazovke.
   
    

   Toto oznámenie sa vyskytne, pretože ste v tabuľke použili polia, ktoré nie sú súčasťou základného dátového modelu. Tabuľku môžete do dátového modelu pridať aj vytvorením vzťahu v tabuľke, ktorá sa už nachádza v dátovom modeli. Ak chcete vytvoriť vzťah, jedna z tabuliek musí obsahovať stĺpec s jedinečnými neopakovanými hodnotami. Vo vzorových údajoch obsahuje tabuľka Disciplines importovaná z databázy pole s kódmi športov SportID. Tieto isté kódy športov sa v importovaných excelových údajoch zobrazujú ako pole. Vytvorme vzťah.

4. Kliknite na položku VYTVORIŤ... vo zvýraznenej oblasti polí kontingenčnej tabuľky, čím sa otvorí dialógové okno Vytvorenie vzťahu, ako je to zobrazené na nasledujúcej obrazovke.
   
   

5. V položke Tabuľka vyberte z rozbaľovacieho zoznamu možnosť Disciplines.

6. V časti Stĺpec (cudzí) vyberte položku SportID.

7. V položke Súvisiaca tabuľka vyberte položku Sports.

8. V položke Súvisiac stĺpec (primárny) vyberte položku SportID.

9. Kliknite na tlačidlo OK.

Kontingenčná tabuľka sa zmení tak, že bude obsahovať nový vzťah. Kontingenčná tabuľka však nebude vyzerať správne hneď, a to z dôvodu usporiadania polí v oblasti RIADKY. Discipline je podkategóriou daného športu, keďže je však položka Discipline uvedená v oblasti RIADKY nad položkou Sport, nie je usporiadaná správne. Toto neželané usporiadanie sa zobrazuje na nasledujúcej obrazovke.

1. V oblasti RIADKY premiestnite položku Sport nad položku Discipline. Je to oveľa lepšie a kontingenčná tabuľka zobrazuje údaje tak, ako ich chcete zobraziť, ako je to znázornené na nasledujúcej obrazovke.
   
   

Excel na pozadí zostaví dátový model, ktorý možno globálne použiť v celom zošite, v kontingenčnej tabuľke, kontingenčnom grafe, v doplnku Power Pivot alebo v zostave Power View. Vzťahy tabuľky tvoria základ dátového modelu a určujú cesty navigácie a výpočtov.

V ďalšom kurze rozšírenie vzťahov dátového modelu pomocou Excelu, Power Pivota JAZYKA DAX môžete stavať na tom, čo ste sa tu naučili, a prechádzať rozšírením dátového modelu pomocou výkonného a vizuálneho doplnku Excelu nazývaného Power Pivot. Naučíte sa tiež vypočítať stĺpce v tabuľke a použiť tento vypočítaný stĺpec, aby ste do dátového modelu mohli pridať inak nesúvisiacu tabuľku.

Kontrolný bod a kvíz

Čo ste sa naučili

Teraz máte excelový zošit obsahujúci kontingenčnú tabuľku s prístupom k údajom z rôznych tabuliek, z ktorých niektoré ste importovali samostatne. Naučili ste sa importovať z databázy, z iného excelového zošita a pomocou kopírovania a prilepovania údajov do Excelu.

Na to, aby údaje spolu fungovali, ste museli vytvoriť vzťah tabuliek, ktorý Excel používa na koreláciu riadkov. Tiež ste zistili, že stĺpce v jednej tabuľke, ktoré korelujú údaje v inej tabuľke, sú základným predpokladom vytvorenia vzťahov a vyhľadania súvisiacich riadkov.

Ste pripravení na ďalší kurz z tejto série. Prepojenie:

Kurz: Rozšírenie vzťahov dátového modelu pomocou Excelu, doplnku Power Pivot a jazyka DAX

KVÍZ

Chcete vedieť, ako dobre ste si zapamätali, čo ste sa naučili? Máte šancu. Nasledujúci kvíz sa týka funkcií, možností alebo požiadaviek, ktoré ste sa naučili v tomto kurze. Na konci stránky nájdete správne odpovede. Veľa šťastia!

Otázka 1: Prečo je dôležité skonvertovať importované údaje do tabuliek?

A: Nemusia sa konvertovať na tabuľky, pretože všetky importované údaje sa na tabuľky premenia automaticky.

B: Po skonvertovaní údajov na tabuľky sa údaje z dátového modelu vylúčia. Až po vylúčení z dátového modelu sú dostupné v kontingenčných tabuľkách, doplnku Power Pivot a Power View.

C: Po skonvertovaní importovaných údajov na tabuľky sa môžu tieto údaje vložiť do dátového modelu a sprístupniť v kontingenčných tabuľkách, doplnku Power Pivot a Power View.

D: Importované údaje sa nedajú skonvertovať na tabuľky.

Otázka 2: Ktoré z nasledujúcich zdrojov údajov je možné importovať do Excelu a začleniť do dátového modelu?

A: Accessové databázy a mnohé ďalšie databázy.

B: Existujúce excelové súbory.

C: Všetko, čo sa dá skopírovať a prilepiť do Excelu a naformátovať ako tabuľka vrátane tabuliek údajov na webových stránkach, v dokumentoch alebo iných súborov, ktoré sa dajú prilepiť do Excelu.

D: Všetky vyššie uvedené.

Otázka 3: Čo sa stane v kontingenčnej tabuľke po zmene usporiadania polí v štyroch oblastiach polí kontingenčnej tabuľky?

A: Nič. Usporiadanie polí sa po ich umiestnení do oblastí polí kontingenčnej tabuľky nedá zmeniť.

B: Formát kontingenčnej tabuľky sa zmení tak, aby obsahoval dané rozloženie, ale základné údaje to neovplyvní.

C: Formát kontingenčnej tabuľky sa zmení tak, aby obsahoval dané rozloženie, a všetky základné údaje sa natrvalo zmenia.

D: Zmenia sa základné údaje, čo má za následok vytvorenie nových súborov údajov.

Otázka 4: Čo sa vyžaduje pri vytváraní vzťahu medzi tabuľkami?

A: Ani jedna tabuľka nemôže obsahovať stĺpec obsahujúci jedinečné neopakované hodnoty.

B: Jedna tabuľka nesmie byť súčasťou excelového zošita.

C: Stĺpce sa nesmú skonvertovať na tabuľky.

D: Nič z vyššie uvedeného nie je správne.

Odpovede kvízu

1. Správna odpoveď: C

2. Správna odpoveď: D

3. Správna odpoveď: B

4. Správna odpoveď: D