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

Abstrakt: Toto je prvý zo série kurzov určených na oboznámenie a pohodlnú prácu s Excelom a jeho vstavanými funkciami kombinovania a analýzy údajov. Tieto kurzy obsahujú úplný postup vytvorenia a vylepšenia excelového zošita, vytvorenia dátového modelu a vytvorenia úžasných interaktívnych zostáv pomocou Power View. Kurzy sú navrhnuté s cieľom ukázať funkcie a možnosti nástrojov Microsoft Business Intelligence v Exceli, kontingenčných tabuľkách, doplnku Power Pivot a doplnku Power View. 

V týchto kurzoch sa naučíte, ako importovať a skúmať údaje v Exceli, vytvoriť a spresniť dátový model pomocou doplnku Power Pivot a vytvoriť pomocou Power View interaktívne zostavy, ktoré môžete publikovať, chrániť a zdieľať.

Séria obsahuje tieto kurzy:

1. Importovanie údajov do Excelu 2016 a vytvorenie dátového modelu
2. Rozšírenie vzťahov dátového modelu pomocou Excelu, doplnku Power Pivot a jazyka DAX
3. Vytvorenie mapových zostáv Power View
4. Začlenenie internetových údajov a predvolené nastavenie zostáv Power View
5. Power Pivot – Pomocník
6. Vytvorenie úžasných zostáv Power View – časť 2

V tomto kurze začneme s prázdnym excelovým zošitom.

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ť. Každý zo štyroch súborov stiahnite do jednoducho prístupného umiestnenia, ako sú napríklad priečinky Stiahnuté súbory alebo Moje dokumenty, alebo do nového priečinka, ktorý vytvoríte:
   > Accessová databáza OlympicMedals.accdb
   > OlympicSports.xlsx excelovom zošite
   > Population.xlsx excelovom zošite
   > DiscImage_table.xlsx excelovom zošite

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

3. Kliknite na položku > Data Get Data > from Database > from Microsoft Access Database. 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úcej obrazovke.

   

4. Vyberte stiahnutý súbor OlympicMedals.accdb a kliknite na položku Importovať. Zobrazí sa nasledujúce okno Navigátora 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 Vybrať viaceré tabuľky a vyberte všetky tabuľky. Potom kliknite na položku Načítať > a načítať do.

   

5. Zobrazí sa okno Import údajov.

   Poznámka

   Všimnite si začiarkavacie políčko v dolnej časti okna, ktoré vám umožňuje pridať tieto údaje do dátového modelu, ako je to znázornené na nasledujúcej obrazovke. Dátový model sa vytvorí automaticky pri importovaní dvoch alebo viacerých tabuliek alebo pri práci s nimi naraz. Dátový model integruje tabuľky a umožňuje rozsiahle analýzy pomocou kontingenčných tabuliek, doplnku Power Pivot a Power View. Pri importe tabuliek z databázy sa existujúce vzťahy databázy medzi týmito tabuľkami použijú na vytvorenie dátového modelu v Exceli. Dátový model je v Exceli transparentný, 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, pomocou ktorej sa do Excelu importujú tabuľky a pripraví kontingenčná tabuľka na analýzu importovaných tabuliek, a potom 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.

Určenie oblasti, do ktorej je potrebné presunúť pole, môže vyžadovať trochu experimentovania. Môžete presúvať ľubovoľný počet polí z tabuliek, až kým sa údaje v kontingenčnej tabuľke nezobrazia podľa vašich predstáv. Neváhajte preskúmať 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 Excelu. V zozname Polia kontingenčnej tabuľky , v ktorom je rozbalená tabuľka Disciplíny , ukážte myšou na jej 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)** zrušte všetky výbery, potom sa posuňte nadol a vyberte možnosť Archery, Diving, Fencing, Figure Skating a Speed Skating. 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žky 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.

   

   Naformátovanie údajov ako tabuľky prináša množstvo 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, v doplnku Power Pivot a Power View umožniť skúmanie a analýzu.

6. Pomenujte tabuľku. V časti VLASTNOSTI NÁVRHU > TABUĽKY vyhľadajte pole Názov tabuľky a zadajte výraz 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.

City	NOC_CountryRegion	Alpha-2 Code	Vydanie	Season
Melbourne / Stockholm	AUS	AS	1956	Summer
Sydney	AUS	AS	2000	Summer
Innsbruck	AUT	AT	1964	Winter
Innsbruck	AUT	AT	1976	Winter
Antwerp	BEL	BE	1920	Summer
Antwerp	BEL	BE	1920	Winter
Montreal	CAN	CA	1976	Summer
Lake Placid	CAN	CA	1980	Winter
Calgary	CAN	CA	1988	Winter
St. Moritz	SUI	SZ	1928	Winter
St. Moritz	SUI	SZ	1948	Winter
Beijing	CHN	CH	2008	Summer
Berlin	GER	GM	1936	Summer
Garmisch-Partenkirchen	GER	GM	1936	Winter
Barcelona	ESP	Balík Service Pack	1992	Summer
Helsinki	FIN	FI	1952	Summer
Paris	FRA	FR	1900	Summer
Paris	FRA	FR	1924	Summer
Chamonix	FRA	FR	1924	Winter
Grenoble	FRA	FR	1968	Winter
Albertville	FRA	FR	1992	Winter
London	GBR	UK	1908	Summer
London	GBR	UK	1908	Winter
London	GBR	UK	1948	Summer
Munich	GER	DE	1972	Summer
Athens	GRC	GR	2004	Summer
Cortina d'Ampezzo	ITA	IT	1956	Winter
Rome	ITA	IT	1960	Summer
Turin	ITA	IT	2006	Winter
Tokyo	JPN	JA	1964	Summer
Sapporo	JPN	JA	1972	Winter
Nagano	JPN	JA	1998	Winter
Seoul	KOR	KS	1988	Summer
Mexico	MEX	MX	1968	Summer
Amsterdam	NED	NL	1928	Summer
Oslo	NOR	NO	1952	Winter
Lillehammer	NOR	NO	1994	Winter
Stockholm	SWE	SW	1912	Summer
St Louis	USA	US	1904	Summer
Los Angeles	USA	US	1932	Summer
Lake Placid	USA	US	1932	Winter
Squaw Valley	USA	US	1960	Winter
Moscow	URS	RU	1980	Summer
Los Angeles	USA	US	1984	Summer
Atlanta	USA	US	1996	Summer
Salt Lake City	USA	US	2002	Winter
Sarajevo	YUG	YU	1984	Winter

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 a naformátujte údaje ako tabuľku alebo prejdite na kartu 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 časti VLASTNOSTI NÁVRHU > TABUĽKY 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 zobrazené 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 tabuľke vyberte v rozbaľovacom zozname položku Tabuľka dátového modelu: Disciplíny.

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

7. V časti Súvisiaca tabuľka vyberte položku Tabuľka dátového modelu: Športy.

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 presuňte položku Sport nad položku Discipline. Je to oveľa lepšie. Kontingenčná tabuľka zobrazuje údaje tak, ako ich chcete vidieť, ako je to znázornené na nasledujúcej obrazovke.

   

Excel na pozadí zostaví dátový model, ktorý možno použiť v celom zošite, v kontingenčnej tabuľke, kontingenčnom grafe, doplnku Power Pivot alebo v zostave funkcie 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 s názvom Rozšírenie vzťahov dátového modelu pomocou Excelu, doplnku Power Pivot** a jazyka DAX** budete stavať na tom, čo ste sa tu naučili, a prejdete krokmi rozšírenia dátového modelu pomocou výkonného 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 tak, aby bolo možné do dátového modelu pridať 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 funkcii 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