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

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

Důležité :  Tento článek je strojově přeložený – přečtěte si toto upozornění. Anglickou verzi tohoto článku pro referenci najdete tady.

Přehled:    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, doplňku Power Pivot a Power View.

Poznámka : Tento článek popisuje datových modelech v Excelu 2013. Však stejné modelování dat a doplňku Power Pivot funkcích v Excelu 2013 použít k Excelu 2016.

V těchto kurzech se naučíte importovat a zkoumat data v Excelu, sestavit a zdokonalit datový model pomocí doplňku Power Pivot a vytvářet interaktivní sestavy v Power View, které se dají publikovat a sdílet a u kterých jde nastavit ochranu.

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

  1. Import dat do Excelu 2013 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. Vytváření skvělých sestav Power View – Část 1

  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.

Této řadě kurzů používá data popisující olympijských medaile hostingu země a různých olympijských sportovní akce. Měli byste že absolvovat každého kurzu v pořadí. Výukové programy pro taky pomocí Excelu 2013 s Power Pivot povolené. Další informace o Excelu 2013, klikněte sem. Pokyny k povolení Power Pivot, klikněte sem.


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 stahovat soubory, které budeme používat během této řadě kurzů. Stáhnout všechny čtyři soubory do umístění, do kterého je snadno přístupných osobám s postižením, například stahování nebo Dokumentynebo do nové složky vytvoříte:
    > databáze aplikace 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 2013 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.

    Import dat z Accessu

    Import dat z Accessu s malým pásem karet


  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.

    Okno Vybrat tabulku

  5. Zobrazí se okno Importovat data.

    Poznámka : V dolní části okna si všimněte zaškrtávacího políčka Přidat tahle data do datového modelu, jak je vidět na následující obrazovce. Datové modely se vytvoří automaticky, když současně importujete nejmíň dvě tabulky nebo s nimi pracujete. Datový model tyto tabulky integruje, čímž umožní jejich rozsáhlé analýzy pomocí kontingenčních tabulek, doplňku Power Pivot a Power View. Při importu tabulek z databáze se k vytvoření datového modelu v Excelu použijí stávající databázové relace mezi těmito tabulkami. Datový model je v Excelu přehledný, ale můžete ho zobrazit a upravovat přímo pomocí doplňku Power Pivot. Datový model si podrobněji probereme dál 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.

    Okno Importovat data

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

    Prázdná 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.

Čtyři oblasti polí kontingenční tabulky

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 kamkoli do excelové kontingenční tabulky, abyste měli jistotu, že je vybraná. V seznamu polí kontingenční tabulky, kde je rozbalená tabulka Disciplines, najeďte myší na její pole Discipline, aby se napravo od něho objevila rozevírací šipka. Klikněte na tento rozevírací seznam, 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.

    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.
      Okno Filtr hodnot

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

Aktualizovaná kontingenční tabulka

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ď naimportujeme data z jiného zdroje, tentokrát ze stávajícího sešitu. A pak definujeme relace mezi našimi stávajícími a novými daty. Relace umožňují analyzovat kolekce dat v Excelu a vytvářet z naimportovaných dat zajímavé a působivé 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 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.

    Okno Vytvořit tabulku

    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, 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.
    Pojmenování tabulky v Excelu

  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

Edition

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

roce 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

roce 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. 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.

Tabulka hostitelů

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 klikněte v horní částipolí kontingenční tabulky naVše, aby se zobrazil seznam všech dostupných tabulek, jak je vidět na následující obrazovce.
    Zobrazení všech dostupných tabulek kliknutím na Vše v polích kontingenční tabulky

  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.
    Výzva k vytvoření relace v polích kontingenční tabulky

    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.

    Okno Vytvořit relaci

  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.
Kontingenční tabulka s nežádoucím řazením

  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.

    Kontingenční tabulka s opraveným řazením

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 2013, doplňku Power Pivot a jazyka DAX, navážeme na to, co jste se naučili tady, a projdeme si rozšíření datového modelu pomocí výkonného, vizuálního doplňku pro Excel, který se jmenuje Power Pivot. Taky se naučíte nastavovat výpočty sloupců v tabulce a použít tyto počítané sloupce tak, aby se do datového modelu dala přidat jinak nepřidružená tabulka.

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:

Rozšíření relací datového modelu pomocí Excelu 2013, doplňku Power Pivot 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

Poznámky : Data a obrázky v této řadě kurzů jsou založené na následujících položkách:

  • Datová sada o olympijských hrách od Guardian News & Media Ltd.

  • Obrázky vlajek od CIA Factbook (cia.gov)

  • Údaje o obyvatelstvu od The World Bank (worldbank.org)

  • Piktogramy olympijských sportů od Thadius856 a Parutakupiu

Poznámka : Upozornění ke strojovému překladu: Tento článek přeložil počítačový systém bez zásahu člověka. Společnost Microsoft nabízí tyto strojové překlady proto, aby umožnila uživatelům, kteří nemluví anglicky, získat informace o produktech, službách a technologiích této společnosti. Protože je tento článek strojově přeložený, může obsahovat slovní, syntaktické nebo gramatické chyby.

Sdílení Facebook Facebook Twitter Twitter E-mail E-mail

Byly tyto informace užitečné?

Výborně! Je ještě něco dalšího, co byste nám chtěli dát vědět?

Jak bychom ho mohli vylepšit?

Děkujeme vám za zpětnou vazbu.

×