Kurz: Import dat do Excelu a vytvoření datového modelu

Anotace: Toto je první kurz v řadě sestavené tak, abyste se v ní seznámili a osvojili si používání Excelu a jeho integrovaných funkcí pro kombinování a analýzy dat. V těchto kurzech se od nuly sestaví a dál vypracuje excelový sešit a pak se vytvoří datový model a nakonec i skvělé interaktivní sestavy pomocí Power View. Kurzy jsou navržené tak, abychom v nich předvedli funkce Microsoft Business Intelligence a možnosti v Excelu, kontingenčních tabulkách, Power Pivotu a Power View. 

V těchto kurzech se naučíte importovat a zkoumat data v Excelu, sestavovat a upřesňovat datový model pomocí Power Pivotu a vytvářet interaktivní sestavy pomocí Power View, které můžete publikovat, chránit a sdílet.

Tato řada obsahuje následující kurzy:

1. Import dat do aplikace Excel 2016 a vytvoření datového modelu
2. Rozšíření relací datového modelu pomocí Excelu, Power Pivotu a jazyka DAX
3. Vytváření sestav Power View založených na mapě
4. Zahrnutí internetových dat a nastavení výchozích možností sestav Power View
5. Nápověda k Power Pivotu
6. Vytváření skvělých sestav Power View – Část 2

V tomto kurzu začnete s prázdným excelovým sešitem.

Tento kurz se skládá z následujících částí:

• Import dat z databáze
• Import dat z tabulkového kalkulátoru
• Import dat zkopírováním a vložením
• Vytvoření relace mezi importovanými daty
• Kontrolní bod a kvíz

Na konci tohoto kurzu je kvíz, kde si můžete otestovat, co jste se naučili.

V této řadě kurzů používáme data popisující olympijské medaile, hostitelské země a různé olympijské sportovní soutěže. Jednotlivé kurzy byste měli absolvovat v určeném pořadí. 

Import dat z databáze

V tomto kurzu začneme s prázdným sešitem. Cílem této části je připojení k externímu zdroji dat a import dotyčných dat do Excelu pro další analýzu.

Začněme stažením nějakých dat z internetu. Data popisují olympijské medaile a jde o databázi Microsoft Accessu.

1. Kliknutím na následující odkazy si stáhněte soubory, které budeme používat během této řady kurzů. Všechny čtyři soubory si stáhněte do umístění, které je snadno dostupné, třeba Stažené soubory nebo Dokumenty, nebo do nové složky, kterou vytvoříte:
   > OlympicMedals.accdb Accessová databáze
   > OlympicSports.xlsx excelového sešitu
   > Population.xlsx excelového sešitu
   > DiscImage_table.xlsx excelového sešitu

2. V Excelu otevřete prázdný sešit.

3. Klikněte na možnost Data > získat data > z databáze > aplikace Microsoft Access. Pás karet se dynamicky přizpůsobuje šířce sešitu, takže příkazy na pásu karet se můžou mírně odlišovat od následující obrazovky.

   

4. Vyberte stažený soubor OlympicMedals.accdb a klikněte na Importovat. Objeví se následující okno Navigátor, které zobrazuje tabulky v databázi. Tabulky v databázi jsou podobné listům nebo tabulkám v Excelu. Zaškrtněte políčko Vybrat více tabulek a vyberte všechny tabulky. Poté klikněte na Načíst > zatížení do.

   

5. Zobrazí se okno Importovat data.

   Poznámka

   Všimněte si zaškrtávacího políčka v dolní části okna, které umožňuje přidat tato data do datového modelu, jak je vidět na následující obrazovce. Datový model se vytvoří automaticky, když importujete nebo pracujete se dvěma nebo více tabulkami současně. Datový model integruje tabulky a umožňuje rozsáhlou analýzu pomocí kontingenčních tabulek, Power Pivotu a Power View. Při importu tabulek z databáze se existující databázové relace mezi těmito tabulkami použijí k vytvoření datového modelu v Excelu. Datový model je v Excelu průhledný, ale můžete ho zobrazit a upravit přímo pomocí doplňku Power Pivot. Datový model je podrobněji popsán dále v tomto kurzu.

   Vyberte možnost Sestava kontingenční tabulky, která naimportuje tabulky do Excelu a připraví kontingenční tabulku pro jejich analýzu, a klikněte na OK.

   

6. Jakmile se data naimportují, vytvoří se na základě naimportovaných tabulek kontingenční tabulka.

   

Po importu dat do Excelu a automatickém vytvoření datového modelu můžete prozkoumat data.

Prozkoumání dat pomocí kontingenční tabulky

Zkoumání importovaných dat pomocí kontingenční tabulky je snadné. V kontingenční tabulce přetahujete pole (podobají se sloupcům v Excelu) z tabulek (třeba z tabulek, které jste právě naimportovali z accessové databáze) do různých oblastí kontingenční tabulky a tím měníte způsob prezentace dat. Kontingenční tabulka má čtyři oblasti: FILTRY, SLOUPCE, ŘÁDKY a HODNOTY.

Může to vyžadovat trochu experimentování, než zjistíte, do které oblasti by se mělo určité pole přetáhnout. Z tabulek můžete přetáhnout jen pár polí, nebo naopak hodně, prostě tolik, kolik je potřeba, aby kontingenční tabulka data prezentovala přesně požadovaným způsobem. Nebojte se experimentovat přetahováním polí do různých oblastí kontingenční tabulky; uspořádání polí v kontingenční tabulce nemá na podkladová data žádný vliv.

Pojďme si v kontingenční tabulce prozkoumat údaje o olympijských medailích a začněme při tom s olympijskými medailisty uspořádanými podle disciplín, hodnot medailí a zemí nebo oblastí, odkud sportovci pochází.

1. V polích kontingenční tabulky rozbalte tabulku Medals kliknutím na šipku vedle ní. V rozbalené tabulce Medals vyhledejte pole NOC_CountryRegion a přetáhněte ho do oblasti SLOUPCE. NOC je zkratka pro národní olympijský výbor (National Olympic Committee), což je organizační jednotka v dané zemi nebo oblasti.

2. Pak v tabulce Disciplines přetáhněte pole Discipline do oblasti ŘÁDKY.

3. Vyfiltrujme disciplíny tak, aby se zobrazovalo jenom pět sportů: lukostřelba (Archery), skoky do vody (Diving), šerm (Fencing), krasobruslení (Figure Skating) a rychlobruslení (Speed Skating). Dá se to udělat přímo v oblasti polí kontingenční tabulky oblasti nebo z filtru Popisky řádků v samotné kontingenční tabulce.

   1. Klikněte kamkoliv do kontingenční tabulky, aby se zobrazila vybraná kontingenční tabulka aplikace Excel. V seznamu polí kontingenční tabulky , kde je rozbalená tabulka Discipline, najeďte myší na pole Discipline a napravo od něj se zobrazí šipka rozevíracího seznamu. Klikněte na rozevírací seznam, kliknutím na **(Vybrat vše)** odeberte všechny výběry, pak se posuňte dolů a vyberte Archery, Diving, Fencing, Figure Skating a Speed Skating. Klikněte na OK.
   2. Nebo v části Popisky řádků v kontingenční tabulce klikněte na rozevírací seznam vedle položky Popisky řádků, zrušte výběr všech položek kliknutím na (Vybrat vše) a pak postupně posunujte zobrazení dolů a vyberte Archery, Diving, Fencing, Figure Skating a Speed Skating. Klikněte na OK.

4. V polích kontingenční tabulky přetáhněte z tabulky Medals pole Medal do oblasti HODNOTY. Vzhledem k tomu, že hodnoty musí být číselné, Excel automaticky změní pole Medal na Počet z Medal.

5. V tabulce Medals znovu vyberte pole Medal a přetáhněte ho do oblasti FILTRY.

6. Vyfiltrujme kontingenční tabulku tak, aby zobrazovala jenom země nebo oblasti s víc než 90 medailemi celkem. Jak na to:

   1. V kontingenční tabulce klikněte na rozevírací šipku vpravo od Popisků sloupců.
   2. Vyberte Filtry hodnot a pak Větší než.
   3. Napište 90 do posledního pole (vpravo). Klikněte na OK.
      

Vaše kontingenční tabulka vypadá jako následující obrazovka.

S trochou úsilí jsme teď vytvořili kontingenční tabulku, která zahrnuje pole z tří různých tabulek. To, co tento úkol tak zjednodušilo, byly už předem vytvořené relace mezi tabulkami. Protože relace mezi tabulkami existovaly už ve zdrojové databázi a protože jste importovali všechny tabulky v jednom kroku, mohl Excel tyto tabulkové relace ve svém datovém modelu znovu vytvořit.

Ale co když data pocházejí z různých zdrojů nebo se budou importovat až později? Obvykle můžete relace s novými daty vytvořit na základě shodných sloupců. V dalším kroku importujete další tabulky a dozvíte se, jak se vytváří nové relace.

Import dat z tabulkového kalkulátoru

Teď naimportujme data z jiného zdroje, tentokrát z existujícího sešitu, a pak určíme vztahy mezi našimi stávajícími a novými daty. Relace umožňují analyzovat kolekce dat v Excelu a vytvářet z importovaných dat zajímavé a poutavé vizualizace.

Začneme vytvořením prázdného listu a pak naimportujeme data z excelového sešitu.

1. Vložte nový excelový list a pojmenujte ho Sports.

2. Přejděte do složky obsahující stažené ukázkové datové soubory a otevřete OlympicSports.xlsx.

3. Vyberte a zkopírujte data na Listu1. Když vyberete buňku s daty, třeba A1, můžete stisknutím Ctrl+A vybrat všechna sousední data. Zavřete sešit OlympicSports.xlsx.

4. Na listu Sports umístěte kurzor do buňky A1 a vložte data.

5. Ponechte data ještě zvýrazněná a stisknutím Ctrl+T je naformátujte jako tabulku. Data můžete jako tabulku naformátovat také na pásu karet výběrem možnosti DOMŮ > Formátovat jako tabulku. Protože data mají záhlaví, zaškrtněte v okně Vytvořit tabulku políčko Tabulka obsahuje záhlaví – jak je vidět na tomto obrázku.

   

   Naformátovat data jako tabulku má mnoho výhod. Tabulce můžete přiřadit název, aby se dala snadno identifikovat. Můžete taky vytvořit relace mezi tabulkami, což umožní zkoumat a analyzovat data v kontingenčních tabulkách, Power Pivotu a Power View.

6. Pojmenujte tabulku. V okně Vlastnosti NÁVRHU >tabulky najděte pole Název tabulky a napište Sport. Sešit vypadá jako následující obrazovka.
   

7. Uložte sešit.

Import dat zkopírováním a vložením

Teď když jsme naimportovali data z excelového sešitu, pojďme naimportovat data z tabulky, kterou najdeme na webové stránce, nebo z jakéhokoli jiného zdroje, ze kterého můžeme kopírovat a vkládat do Excelu. V následujícím postupu přidáte z tabulky města, která hostila olympijské hry.

1. Vložte nový excelový list a pojmenujte ho Hosts.
2. Vyberte a zkopírujte následující tabulku včetně záhlaví.

City	NOC_CountryRegion	Alpha-2 Code	Edice	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	SP	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 Excelu umístěte kurzor do buňky A1 na listu Hosts a vložte data.
2. Naformátujte data jako tabulku. Jak je popsáno dříve v tomto kurzu, stisknutím Ctrl + T naformátujete data jako tabulku nebo z HOME > Formátovat jako tabulku. Protože data mají záhlaví, zaškrtněte v okně Vytvořit tabulku políčko Tabulka obsahuje záhlaví.
3. Pojmenujte tabulku. V okně Vlastnosti NÁVRHU > TABULKY vyhledejte pole Název tabulky a zadejte Hostitelé.
4. Vyberte sloupec Edition a z karty DOMŮ ho naformátujte jako Číslo s 0 desetinných míst.
5. Uložte sešit. Váš sešit vypadá jako následující obrazovka.

Teď když máte excelový sešit s tabulkami, můžete mezi nimi vytvořit relace. Vytvoření relací mezi tabulkami získáte možnost kombinovat jejich data.

Vytvoření relace mezi importovanými daty

Pole v kontingenční tabulce pocházející z importovaných tabulek můžete začít používat okamžitě. Pokud Excel nedokáže určit, jak určité pole do kontingenční tabulky začlenit, je třeba vytvořit relaci s stávajícím datovým modelem. V následujících krocích se dozvíte, jak vytvořit relaci mezi daty, která jste naimportovali z různých zdrojů.

1. Na Listu1 v horní částipolí kontingenční tabulky klikněte naVše, aby se zobrazil úplný seznam dostupných tabulek, jak ukazuje následující obrazovka.
   

2. Posunujte seznam, dokud neuvidíte nové tabulky, kterou jste právě přidali.

3. Rozbalte Sports a vyberte pole Sport, aby se přidalo do kontingenční tabulky. Všimněte si, že Excel zobrazí výzvu k vytvoření relace, jak je vidět na následující obrazovce.
   
    
   Je to proto, že jste použili pole z tabulky, která není součástí základního datového modelu. Jedním ze způsobů, jak přidat tabulku do datového modelu, je vytvoření relace k tabulce, která už v datovém modelu je. Pokud chcete vytvořit relaci, musí jedna z tabulek obsahovat sloupec jedinečných, neopakujících se hodnot. V ukázkových datech obsahuje (z databáze importovaná) tabulka Disciplines pole s kódy sportů, nazvané SportID. Tytéž kódy sportů jsou přítomné jako pole i v excelových datech, která jsme naimportovali. Pusťme se do vytváření relace.

4. Klikněte na VYTVOŘIT… ve zvýrazněné oblasti polí kontingenční tabulky, čímž otevřete dialog Vytvořit relaci, jak je vidět na následující obrazovce.

   

5. V části Tabulka zvolte v rozevíracím seznamu položku Tabulka datového modelu: Disciplíny .

6. V poli Sloupec (cizí) zvolte SportID.

7. V části Související tabulka zvolte Tabulka datového modelu: Sport.

8. V poli Související sloupec (primární) zvolte SportID.

9. Klikněte na OK.

Kontingenční tabulka se změní, aby odrážela novou relaci. Ale kontingenční tabulka ještě nevypadá úplně dobře kvůli řazení polí v oblasti ŘÁDKY. Disciplína je podkategorií daného sportu, ale protože jsme v oblasti ŘÁDKY disciplínu zařadili nad sport, není uspořádání správné. Toto nežádoucí řazení vidíme na následující obrazovce.

1. Přesuňte v oblasti ŘÁDKY pole Sport nad pole Discipline. Tak je to mnohem lepší: jak je vidět na následující obrazovce, kontingenční tabulka teď zobrazuje data požadovaným způsobem.

   

Excel na pozadí sestavuje datový model, který je možné použít v celém sešitu, v libovolné kontingenční tabulce nebo grafu, v Power Pivotu nebo v sestavě Power View. Relace mezi tabulkami představují základ datového modelu a jsou tím, co určuje cesty pro navigaci a výpočty.

V dalším kurzu, Rozšíření relací datového modelu pomocí Excelu, Power Pivotu a jazyka DAX**, budete stavět na tom, co jste se tady naučili, a budete procházet kroky v rozšiřování datového modelu pomocí výkonného vizuálního doplňku Excelu s názvem Power Pivot. Naučíte se taky počítat sloupce v tabulce a používat počítaný sloupec k přidání jinak nesouvisející tabulky do datového modelu.

Kontrolní bod a kvíz

Shrnutí nových znalostí

Teď máte excelový sešit, který obsahuje kontingenční tabulku nabízející přístup k datům v několika tabulkách, z nichž některé jste naimportovali odděleně. Naučili jste se importovat z databáze, z jiného excelového sešitu a kopírováním a vkládáním dat do Excelu.

Aby tato data spolu fungovala, museli jste vytvořit relaci mezi tabulkami, kterou Excel využívá ke korelaci řádků. Taky jste se dozvěděli, že pro vytváření relací a vyhledávání souvisejících řádků má zásadní význam to, jestli jsou sloupce v jedné tabulce ve vzájemném vztahu s daty v druhé tabulce.

Teď jste připravení na další kurz v této řadě. Tady je odkaz:

Kurz: Rozšíření relací datového modelu pomocí Excelu, Power Pivotu a jazyka DAX

KVÍZ

Chcete zjistit, jak dobře si pamatujete, co jste se dozvěděli? Tady máte příležitost. Následující kvíz je zaměřený na funkce, možnosti a požadavky, se kterými jste se v tomto kurzu seznámili. Odpovědi najdete na konci stránky. Hodně štěstí!

Otázka 1: Proč je důležité převést importovaná data na tabulky?

A: Na tabulky je není nutné převádět, protože všechna importovaná data se na tabulky změní automaticky.

B: Pokud převedete importovaná data na tabulky, budou vyloučené z datového modelu. Jen když jsou vyloučené z datového modelu, budou dostupné v kontingenčních tabulkách, Power Pivotu a Power View.

C: Když importovaná data převedete na tabulky, půjdou zahrnout do datového modelu a budou pak dostupné pro kontingenční tabulky, Power Pivot a Power View.

D: Importovaná data na tabulky převést nejde.

Otázka 2: Které z následujících datových zdrojů můžete importovat do Excelu a zahrnout do datového modelu?

A: Accessové databáze a taky mnoho dalších databází.

B: Existující excelové soubory.

C: Cokoli, co můžete zkopírovat a vložit do Excelu a naformátovat jako tabulku, včetně tabulek dat na webech, dokumentů nebo čehokoli jiného, které se dají vložit do Excelu.

D: Všechny výše uvedené možnosti.

Otázka 3: Co se stane v kontingenční tabulce při změně pořadí polí ve čtyřech oblastech jejích polí?

A: Nic – po umístění polí do oblastí kontingenční tabulky nemůžete měnit jejich pořadí.

B: Formát kontingenční tabulky se změní tak, aby odrážel nové rozložení, aniž by to mělo vliv na podkladová data.

C: Formát kontingenční tabulky se změní tak, aby odrážel nové rozložení, a současně se trvale změní podkladová data.

D: Podkladová data se změní, takže vzniknou nové datové sady.

Otázka 4: Co je nutné pro vytvoření relace mezi tabulkami?

A: Ani jedna z tabulek nesmí mít žádný sloupec, který by obsahoval jedinečné, neopakující se hodnoty.

B: Jedna z tabulek nesmí být součástí excelového sešitu.

C: Sloupce nesmí být převedené na tabulky.

D: Žádná z výše uvedených možností není správná.

Odpovědi kvízu

1. Správná odpověď: C
2. Správná odpověď: D
3. Správná odpověď: B
4. Správná odpověď: D