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

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ů. Stáhněte si každý ze čtyř souborů do umístění, které je snadno dostupné, například do složky Stažené soubory nebo Dokumenty, nebo do nové složky, kterou vytvoříte:
   > databáze
   OlympicMedals.accdb Access > 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 DATA > Načíst externí data > Z Accessu. Pás karet se dynamicky přizpůsobuje šířce sešitu, takže na něm zobrazené příkazy se můžou mírně odlišovat od těch na následujících obrazovkách. Na první obrazovce vidíme pás karet, když je sešit široký, zatímco druhý obrázek ukazuje sešit ve velikosti nastavené tak, aby zabíral jenom část obrazovky.
   
   
   
   
   
    

4. Vyberte stažený soubor OlympicMedals.accdb a klikněte na Otevřít. Objeví se následující okno Vybrat tabulku, které zobrazuje tabulky v databázi. Tabulky v databázi jsou podobné listům nebo tabulkám v Excelu. Zaškrtněte políčko Povolit výběr více tabulek a vyberte všechny tabulky. Pak klikněte na OK.
   
   

5. Zobrazí se okno Importovat data.

   : 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 znázorněno na následující obrazovce. Datový model se vytvoří automaticky při importu nebo práci 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 Pivot a Power View. Při importu tabulek z databáze se k vytvoření datového modelu v Excelu použijí existující databázové relace mezi těmito tabulkami. 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á importuje tabulky do Excelu a připraví kontingenční tabulku pro analýzu importovaných tabulek, 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. Kliknutím na libovolné místo v kontingenční tabulce zkontrolujte, že je vybraná excelová kontingenční tabulka. V seznamu Pole kontingenční tabulky , kde je rozbalená tabulka Disciplíny , najeďte myší na její pole Disciplína a vpravo od pole 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, posuňte se dolů a vyberte Lukostřelba, Potápění, Šermíř, Krasobruslení a Rychlobruslení. 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ď importujeme 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 daty a novými daty. Relace umožňují analyzovat kolekce dat v Excelu a vytvářet zajímavé a imerzivní vizualizace z importovaných dat.

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 i z pásu karet výběrem 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.
   
   
   
   Formátování dat jako tabulky 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, doplňku Power Pivot a nástroji Power View.

6. Pojmenujte tabulku. Na kartě NÁSTROJE TABULKY > NÁVRH > Vlastnosti najděte pole Název tabulky a napište Sports. 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í.

1. V Excelu umístěte kurzor do buňky A1 na listu Hosts a vložte data.

2. Naformátujte data jako tabulku. Naformátujte data jako tabulku stisknutím Ctrl+T nebo příkazem DOMŮ > Formátovat jako tabulku, jak jsme popsali dřív v tomto kurzu. Protože data mají záhlaví, zaškrtněte v okně Vytvořit tabulku políčko Tabulka obsahuje záhlaví.

3. Pojmenujte tabulku. Na kartě NÁSTROJE TABULKY > NÁVRH > Vlastnosti najděte pole Název tabulky a napište Hosts.

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 listu List1 v horní částipolí kontingenční tabulky klikněte naVše a zobrazte úplný seznam dostupných tabulek, jak je znázorněno na následující obrazovce.
   

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 poli Tabulka zvolte Disciplines z rozevíracího seznamu.

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

7. V poli Související tabulka zvolte Sports.

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 doplňku Power Pivot 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 Pivota DAX, budete stavět na tom, co jste se naučili tady, a krokujete rozšířením datového modelu pomocí výkonného a vizuálního doplňku Excelu s názvem Power Pivot. Naučíte se také počítat sloupce v tabulce a používat tento počítaný sloupec, aby bylo možné do datového modelu přidat jinak nesouvisející tabulku.

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, v doplňku Power Pivot a v 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