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

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

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é modely v Excel 2013. Stejné datové modelování a funkce Power Pivotu zavedené v Excel 2013 se ale vztahují i na Excel 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í Excel, 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í:

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í. Dál platí, že v těchto kurzech používáme Excel 2013 s povoleným doplňkem Power Pivot. Další informace o Excel 2013 získáte kliknutím sem. Pokud si chcete přečíst pokyny k povolení doplňku 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 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 snadno přístupných umístění, jako je stahování nebo dokumenty ,nebo do nové složky, kterou vytvoříte:
    > OlympicMedals.accdb Access database
    >OlympicSports.xlsx Excel sešit
    > Population.xlsx Excel sešit
    >DiscImage_table.xlsx Excel sešit

  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šimněte si zaškrtávacího políčka v dolní části okna, které umožňuje přidat tato data do datového modelu, které je znázorněno na následující obrazovce. Datový model se vytvoří automaticky při současném importu nebo práci se dvěma nebo více tabulkami. 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 existující relace mezi těmito tabulkami používají k vytvoření datového modelu v Excel. Datový model je průhledný v Excel, ale můžete ho zobrazit a upravit přímo pomocí Power Pivot doplňku. Datový model je podrobněji popsán dále v tomto kurzu.


    Vyberte možnost Sestava kontingenční tabulky, která importuje tabulky do Excel a připraví kontingenční tabulku pro analýzu importovaných tabulek, 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 kontingenční tabulky, abyste zajistili, Excel je vybraná kontingenční tabulka. V seznamu Pole kontingenční tabulky, kde je rozbalená tabulka Disciplines, najeďte myší na 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.
      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ď naimportujte data z jiného zdroje, tentokrát z existujícího sešitu, a pak určete vztahy mezi našimi stávajícími daty a novými daty. Relace umožňují analyzovat kolekce dat v Excel a vytvářet zajímavé a působivé 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.


    Okno Vytvořit tabulku
    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.
    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

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. 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 listu List1v horní části pole kontingenční tabulky kliknutím na Vše zobrazíte úplný seznam 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šiřte relace datového modelu pomocí Excel 2013,Power Pivot a JAZYKA DAX, na základě toho, co jste se tady naučili, a projdete rozšířením datového modelu pomocí výkonného a vizuálního doplňku Excel s názvem Power Pivot. Dozvíte se také, jak vypočítat sloupce v tabulce a použít tento počítaný sloupec, aby se do datového modelu přidala jinak nesouvisející 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

Potřebujete další pomoc?

Rozšiřte své dovednosti s Office
Projít školení
Získejte nové funkce jako první
Připojte se k účastníkům programu Office Insiders

Byly tyto informace užitečné?

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

Děkujeme vám za váš názor! Pravděpodobně bude užitečné, když vás spojíme s některým z našich agentů podpory Office.

×