Vadnica: uvažanje podatkov v Excel in ustvarjanje podatkovnega modela

Povzetek: To je prva vadnica v nizu, namenjena za lažji začetek uporabe Excela, povezovanje njegovih vgrajenih podatkov in za funkcije analize. Sodelujete v teh vadnicah in ustvarili boste ter natančneje določili Excelov delovni zvezek, oblikovali podatkovni model in nato ustvarili interaktivna poročila s funkcijo Power View. Vadnice so oblikovane na način, ki prikazuje Microsoftove funkcije in zmogljivosti za poslovno obveščanje v Excelu, vrtilnih tabelah, dodatku Power Pivot in funkciji Power View. 

V teh vadnicah se boste naučili uvažati in raziskovati podatke v Excelu, sestaviti in natančneje določiti podatkovni model z dodatkom Power Pivot ter ustvariti interaktivna poročila s funkcijo Power View, ki jih lahko objavite, zaščitite in daste v skupno rabo.

Spodaj si oglejte vadnice tega niza:

1. Uvažanje podatkov v Excel 2016 in ustvarjanje podatkovnega modela
2. Razširitev relacij podatkovnega modela s programom Excel, dodatkom Power Pivot in jezikom DAX
3. Ustvarjanje poročil »Power View« na osnovi zemljevidov
4. Vključevanje internetnih podatkov in nastavitev privzetih poročil »Power View«
5. Pomoč za Power Pivot
6. Ustvarjanje neverjetnih poročil »Power View« – 2. del

V tej vadnici bomo začeli s praznim Excelovim delovnim zvezkom.

Razdelki, predstavljeni v tej vadnici, so:

• Uvoz podatkov iz zbirke podatkov
• Uvoz podatkov iz preglednice
• Uvažanje podatkov z ukazoma »Kopiraj« in »Prilepi«
• Ustvarjanje relacije uvoženih podatkov
• Točka preverjanja in kviz

Na koncu vadnice se lahko udeležite kviza in tako preverite svoje znanje.

Za ta niz vadnic smo uporabili podatke, ki opisujejo število olimpijskih medalj, države gostiteljice in različne športne dogodke na olimpijskih igrah. Predlagamo, da si vadnice ogledate v pravilnem vrstnem redu. 

Uvoz podatkov iz zbirke podatkov

To vadnico bomo začeli s praznim delovnim zvezkom. Cilj tega razdelka je, da zvezek povežete z zunanjim virom podatkov in uvozite podatke v Excel za nadaljnjo analizo.

Najprej prenesimo podatke iz interneta. Podatki navajajo število osvojenih olimpijskih medalj in predstavljajo Microsoft Accessovo zbirko podatkov.

1. Kliknite te povezave za prenos datotek, ki smo jih uporabili v tej vadnici. Prenesite vsako od štirih datotek na mesto, ki je preprosto dostopno, kot so prenosi ali Moji dokumenti, ali v novo mapo, ki jo ustvarite:
   > OlympicMedals.accdb Accessova zbirka podatkov
   > OlympicSports.xlsx Excelov delovni zvezek
   > Population.xlsx Excelov delovni zvezek
   > DiscImage_table.xlsx Excelov delovni zvezek

2. V Excelu odprite prazen delovni zvezek.

3. Kliknite »Pridobivanje > podatkov > iz zbirke podatkov iz Microsoft > Accessove zbirke podatkov«. Trak se dinamično prilagodi glede na dolžino vašega delovnega zvezka, tako da so ukazi morda malce drugače prikazani kot na spodnji sliki.

   

4. Izberite preneseno datoteko »OlympicMedals.accdb« in kliknite »Uvozi«. Prikaže se okno »Krmar«, ki prikazuje tabele v zbirki podatkov. Tabele v zbirki podatkov so podobne delovnim listom ali tabelam v Excelu. Potrdite polje » Izberi več tabel « in izberite vse tabele. Nato kliknite »Naloži > v«.

   

5. Prikaže se okno za uvoz podatkov.

   Opomba

   Bodite pozorni na potrditveno polje na dnu okna, ki omogoča dodajanje teh podatkov v podatkovni model, prikazan na spodnji sliki. Podatkovni model se ustvari samodejno, ko istočasno uvozite tabele ali delate z njimi. Tabele integrirate s podatkovnim modelom, kar omogoča obsežnejšo analizo z vrtilnimi tabelami, dodatkom Power Pivot in funkcijo Power View. Ko uvozite tabele iz zbirke podatkov, se obstoječe relacije zbirke podatkov med temi tabelami uporabijo za ustvarjanje podatkovnega modela v Excelu. Podatkovni model je v Excelu pregleden, vendar si ga lahko ogledate in ga spremenite neposredno z dodatkom Power Pivot. Podatkovni model je podrobneje opisan v nadaljevanju te vadnice.

   Izberite možnost Poročilo vrtilne tabele, s katero uvozite tabele v Excel in pripravite vrtilno tabelo za analizo uvoženih tabel, nato pa kliknite V redu.

   

6. Ko so podatki uvoženi, se iz uvoženih tabel ustvari vrtilna tabela.

   

Podatki so bili uvoženi v Excel, podatkovni model je bil samodejno ustvarjen, vi pa ste pripravljeni, da začnete raziskovati podatke.

Raziskovanje podatkov z vrtilno tabelo

Z vrtilno tabelo lahko preprosto raziskujete uvožene podatke. V vrtilni tabeli povlecite polja (podobna stolpcem v Excelu) iz tabel (kot so tabele, ki ste jih pravkar uvozili iz Accessove zbirke podatkov) v različna območja vrtilne tabele, da prilagodite način prikaza podatkov. Vrtilna tabela ima štiri območja: FILTRI, STOLPCI, VRSTICE in VREDNOSTI.

Morda se boste morali malce poigrati, preden boste ugotovili, v katero območje je treba povleči polje. Iz tabel povlecite tolikšno število polj, dokler vrtilna tabela ne bo predstavljala vaših podatkov na želeni način. Polja poskusite povleči v različna območja vrtilne tabele; pri razvrščanju polj vrtilne tabele ne vplivate na temeljne podatke.

V vrtilni tabeli si podrobneje oglejmo podatke o osvojenih olimpijskih medaljah. Začeli bomo s športniki, ki so osvojili medaljo in so razvrščeni glede na disciplino, barvo medalje in državo ali regijo.

1. V razdelku Polja vrtilne tabele razširite tabelo Medals tako, da kliknete puščico poleg tabele. V razširjeni tabeli Medals poiščite polje »NOC_CountryRegion« in ga povlecite v območje STOLPCI. NOC je kratica za Mednarodni olimpijski komite, ki za države ali regije predstavlja organizatorja.

2. Nato iz tabele Disciplines povlecite polje »Discipline« v območje VRSTICE.

3. Discipline filtrirajte, da bodo prikazano le pet športov: lokostrelstvo, potapljanje, sabljanje, umetnostno drsanje in hitrostno drsanje. To lahko naredite v območju Polja vrtilne tabele ali pa v filtru Oznake vrstic vrtilne tabele.

   1. Kliknite kamor koli v vrtilno tabelo, da zagotovite, da je izbrana Excelova vrtilna tabela. Na seznamu polj vrtilne tabele , kjer je tabela Discipline razširjena, premaknite kazalec miške nad polje Discipline in na desni strani polja se prikaže puščica spustnega menija. Kliknite spustni meni, kliknite **(Izberi vse)**, da odstranite vse izbrane možnosti, nato se pomaknite navzdol in izberite lokostrelstvo, potapljanje, sabljanje, umetnostno drsanje in hitrostno drsanje. Kliknite V redu.
   2. Ali pa v razdelku » Oznake vrstic « v vrtilni tabeli kliknite spustni meni ob možnosti »Oznake vrstic « v vrtilni tabeli, kliknite (Izberi vse), da odstranite vse izbore, nato se pomaknite navzdol in izberite lokostrelstvo, potapljanje, sabljanje, umetnostno drsanje in hitrostno drsanje. Kliknite V redu.

4. V razdelku Polja vrtilne tabele iz tabele Medals povlecite polje »Medal« v območje VREDNOSTI. Vrednosti morajo številske, zato Excel možnost »Medal« spremeni v Count of Medal.

5. V tabeli Medals znova izberite »Medal« in polje povlecite v območje FILTRI.

6. Filtrirajmo vrtilno tabelo in prikažimo le tiste države ali regije, ki so osvojile več kot 90 medalj. To naredimo tako:

   1. V vrtilni tabeli kliknite spustni seznam desno od vrstice Oznake stolpcev.
   2. Izberite Filtri vrednosti in nato še Večje od ...
   3. V zadnje polje na desni vnesite število 90. Kliknite V redu.
      

Vaša vrtilna tabela je videti tako:

Z nekaj truda ste ustvarili osnovno vrtilno tabelo, ki vključuje polja iz treh različnih tabel. Preprostost tega opravila so omogočile vnaprej obstoječe relacije med tabelami. Ker so relacije med tabelami obstajale v zbirki podatkov vira in ste vse tabele uvozili z eno operacijo, je Excel lahko znova ustvaril te relacije tabele v podatkovnem modelu.

Kaj pa, če vaši podatki izvirajo iz različnih virov ali so uvoženi pozneje? Po navadi lahko ustvarite relacije z novimi podatki na osnovi ujemajočih se stolpcev. V naslednjem koraku boste uvozili dodatne tabele in se naučili ustvarjati nove relacije.

Uvoz podatkov iz preglednice

Zdaj uvozimo podatke iz drugega vira, tokrat iz obstoječega delovnega zvezka, nato pa določimo odnose med obstoječimi in novimi podatki. Relacije omogočajo analizo zbirk podatkov v Excelu in ustvarjanje zanimivih in poglobljenih ponazoritev iz podatkov, ki jih uvozite.

Najprej ustvarimo prazen delovni list in nato uvozimo podatke iz Excelovega delovnega zvezka.

1. Vstavite nov Excelov delovni list in ga poimenujte Sports.

2. Poiščite mapo, v kateri so prenesene vzorčne podatkovne datoteke, in odprite datoteko OlympicSports.xlsx.

3. Izberite in kopirajte podatke v List1. Če izberete celico s podatki, na primer celico A1, lahko pritisnete Ctrl + A, da izberete vse sosednje podatke. Zaprite delovni zvezek OlympicSports.xlsx.

4. Na delovnem listu Sports postavite kazalec v celico A1 in prilepite podatke.

5. Označite podatke in pritisnite Ctrl + T, da oblikujete podatke kot tabelo. Podatke lahko oblikujete tudi kot tabelo na traku, tako da izberete HOME > Format as Table. Ker podatki vključujejo glave, v prikazanem oknu Ustvari tabelo izberite Tabela ima glave, kot je prikazano spodaj.

   

   Oblikovanje podatkov kot tabelo ima veliko prednosti. Tabeli lahko dodelite ime, s čimer jo boste lažje prepoznali. Vzpostavite lahko tudi relacije med tabelami, tako da omogočite raziskovanje in analizo v vrtilnih tabelah, dodatku Power Pivot in funkciji Power View.

6. Poimenujte tabelo. V razdelku Lastnosti TABLE DESIGN >poiščite polje Ime tabele in vnesite Šport. Delovni zvezek je podoben spodnji sliki.
   

7. Shranite delovni zvezek.

Uvažanje podatkov z ukazoma »Kopiraj« in »Prilepi«

Podatke smo že uvozili iz Excelovega delovnega zvezka, zadaj pa jih uvozimo iz tabele, ki smo jo našli na spletni strani, ali drugega vira, s katerega lahko kopiramo vsebino in jo prilepimo v Excel. V naslednjih korakih boste iz tabele dodali mesta, ki so gostovala olimpijske igre.

1. Vstavite nov Excelov delovni list in ga imenujte Hosts.
2. Označite in kopirajte to tabelo, vključno z glavami tabele.

City	NOC_CountryRegion	Alpha-2 Code	Izdaja	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
Atene	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 postavite kazalec v celico A1 delovnega lista Hosts in prilepite podatke.
2. Podatke oblikujte kot tabelo. Kot smo že omenili, podatke oblikujete s kombinacijo tipk Ctrl + T ali na zavihku »OSNOVNO« na zavihku » > Oblikuj kot tabelo«. Podatki imajo glave, zato v prikazanem oknu Ustvari tabelo izberite Tabela ima glave.
3. Poimenujte tabelo. V lastnostih načrta tabele > poiščite polje »Ime tabele« in vnesite »Hosts«.
4. Izberite stolpec »Leto« in ga na zavihku OSNOVNO oblikujte kot Število z 0 decimalnimi mesti.
5. Shranite delovni zvezek. Vaš delovni zvezek je videti tako:

Zdaj, ko imate Excelovo tabelo s tabelami, lahko ustvarite relacije med njimi. Z relacijami med tabelami lahko primerjate podatke dveh tabel.

Ustvarjanje relacije uvoženih podatkov

Polja vrtilne tabele, ki ste jih uvozili iz tabel, lahko začnete uporabljati takoj. Če Excel ne uspe določiti načina za vključevanje polja v vrtilno tabelo, morate vzpostaviti relacijo z obstoječim podatkovnim modelom. V naslednjih korakih boste izvedeli, kako ustvarjati relacije med podatki, ki ste jih uvozili iz različnih virov.

1. Na Listu1 na vrhupolj vrtilne tabele kliknite »Vse«, če si želite ogledati celoten seznam razpoložljivih tabel, kot je prikazano spodaj.
   

2. Pomaknite se po seznamu navzdol in prikažite nove tabele, ki ste jih pravkar dodali.

3. Razširite polje Sports in izberite Sport, da ga dodate v vrtilno tabelo. Excel vas pozove, da ustvarite relacijo, kot je prikazano na spodnji sliki.
   
    
   To obvestilo se prikaže, ker ste uporabili polja iz tabele, ki ni del temeljnega podatkovnega modela. Tabelo lahko dodate v podatkovni model tako, da ustvarite relacijo s tabelo, ki je že vključena v podatkovni model. Če želite ustvariti relacijo, mora ena od tabel vključevati stolpec z enoličnimi vrednostmi, ki se ne ponavljajo. V vzorčnih podatkih ima tabela Disciplines, ki ste jo uvozili iz zbirke podatkov, polje s kodami športov, imenovano »SportID«. Iste kode športov so prisotne v polju Excelovih podakov, ki smo jih uvozili. Ustvarimo relacijo.

4. V označenem območju Polja vrtilne tabele kliknite USTVARI ..., da odprete pogovorno okno Ustvari relacijo, kot je prikazano na spodnji sliki.

   

5. V tabeli na spustnem seznamu izberite Tabela podatkovnega modela: Discipline.

6. Za polje Stolpec (tuji) izberite SportID.

7. V tabeli v relaciji izberite Tabela podatkovnega modela: Športi.

8. Za polje Povezani stolpec (primarni) izberite SportID.

9. Kliknite V redu.

Vrtilna tabela se spremeni, da prikaže novo relacijo. Toda urejanja vrtilne tabele še nismo dokončali, saj želimo spremeniti vrstni red v območju VRSTICE. Discipline predstavljajo podkategorijo športa, toda polje »Discipline« ni pravilno razvrščeno, ker smo v območju VRSTICE to polje uvrstili nad polje »Sport«. Oglejte si spodnjo sliko, ki prikazuje ta neželeni vrstni red.

1. V območju VRSTICE polje »Sport« pomaknite nad polje »Discipline«. To je veliko bolje, saj vrtilna tabela podatke prikazuje na želeni način, kot je prikazano spodaj.

   

V ozadju Excel sestavlja podatkovni model, ki ga je mogoče uporabljati v celotnem delovnem zvezku v kateri koli vrtilni tabeli, dodatku Power Pivot ali poljubnem poročilu Power View. Relacije tabel predstavljajo osnovno podatkovnega modela in določajo poti krmarjenja in izračunov.

V naslednji vadnici z naslovom »Razširitev relacij podatkovnega modela z Excelom, dodatkom Power Pivot** in jezikom DAX** boste nadgradili svoje znanje tukaj in dodatno razširili podatkovni model z uporabo zmogljivega in vizualnega Excelovega dodatka, imenovanega Power Pivot. Naučili se boste tudi izračunavati stolpce v tabeli in uporabiti ta izračunani stolpec za dodajanje tabele, ki bi bila sicer nepovezana, v podatkovni model.

Točka preverjanja in kviz

Preverite svoje znanje

Ustvarili ste Excelov delovni zvezek z vrtilno tabelo, ki dostopa do podatkov v več tabelah; nekatere od teh tabel ste uvozili ločeno. Naučili ste se uvoziti podatke iz zbirke podatkov, drugega Excelovega delovnega zvezka in prek funkcije kopiranja in lepljenja v Excel.

Želeli ste povezati podatke, zato ste ustvarili relacijo tabele, s katero je Excel povezal vrstice. Med drugim ste izvedeli tudi, da je za ustvarjanje relacij in iskanje povezanih vrstic pomembno, da imate stolpce v eni tabeli, ki povezujejo podatke v drugi.

Lahko nadaljujete z naslednjo vadnico tega niza. Kliknite povezavo:

Vadnica – razširitev relacij podatkovnega modela s programom Excel, dodatkom Power Pivot in jezikom DAX

KVIZ

Ali želite preveriti svoje znanje? Izkoristite priložnost. Sodelujte v kvizu in preverite funkcije, zmogljivosti ali zahteve, ki ste se jih ogledali v tej vadnici. Odgovori so prikazani na dnu strani. Srečno!

1. vprašanje: Zakaj je pomembno, da uvožene podatke pretvorim v tabele?

A: Podatkov vam ni treba pretvoriti v tabele, saj se uvoženi podatki samodejno spremenijo v tabele.

B: Če uvožene podatke pretvorite v tabele, ti ne bodo vključeni v podatkovni model. Če niso vključeni v podatkovni model, si podatke lahko ogledate v vrtilnih tabelah, dodatku Power Pivot in funkciji Power View.

C: Če uvožene podatke pretvorite v tabele, jih lahko vključite v podatkovni model in jih boste lahko uporabili v vrtilnih tabelah, dodatku Power Pivot in funkciji Power View.

D: Uvoženih podatkov ni mogoče pretvoriti v tabele.

2. vprašanje: Katerega od naštetih virov podatkov lahko uvozite v Excel in ga vključite v podatkovni model?

A: Accessove zbirke podatkov kot tudi številne druge zbirke podatkov.

B: Obstoječe Excelove datoteke.

C: Podatke, ki jih lahko kopirate in prilepite v Excel ter jih oblikujete kot tabele, vključno s podatkovnimi tabelami na spletnih mestih, dokumenti ali drugo vsebino, ki jo lahko prilepite v Excel.

D: Vse zgornje možnosti.

3. vprašanje: Kaj se zgodi, če v vrtilni tabeli spremenite vrstni red polj štirih območij »Polja vrtilne tabele«.

A: Nič – ko polja enkrat vstavite v območja »Polja vrtilne tabele«, njihovega vrstnega reda ni mogoče več spremeniti.

B: Oblika vrtilne tabele se spremeni, da odraža novo postavitev, temeljni podatki pa ostanejo nespremenjeni.

C: Oblika vrtilne tabele se spremeni, da odraža novo postavitev, temeljni podatki pa se spremenijo za stalno.

D: Temeljni podatki se spremenijo, kar se odraža v novih naborih podatkov.

4. vprašanje: Kaj potrebujemo pri ustvarjanju relacije med tabelami?

A: V nobeni od tabel ne sme biti stolpcev z enoličnimi vrednostmi, ki se ne ponavljajo.

B: Ena od tabel ne sme biti del Excelovega delovnega zvezka.

C: Stolpci ne smejo biti pretvorjeni v tabele.

D: Nič od naštetega ni pravilno.

Odgovori na zastavljena vprašanja

1. Pravilen odgovor: C
2. Pravilen odgovor: D
3. Pravilen odgovor: B
4. Pravilen odgovor: D