Vadnica: uvoz podatkov v Excel in ustvarjanje podatkovnega modela

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 spodnje povezave za prenos datotek, ki jih uporabljamo v tem nizu vadnic. Prenesite vse od štirih datotek na mesto, ki je preprosto dostopno, na primer Prenosi ali Moji dokumenti, ali v novo mapo, ki jo ustvarite:
   > olympicMedals.accdb Access database
   > 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 PODATKI > Pridobi zunanje podatke > Iz Accessa. Trak se dinamično prilagodi glede na dolžino vašega delovnega zvezka, tako da so ukazi morda malce drugače prikazani kot na spodnjih slikah. Na prvi sliki je prikazan trak, ko je delovni zvezek širok, na drugi pa delovni zvezek, katerega velikost je bila spremenjena, tako da zasede le del zaslona.
   
   
   
   
   
    

4. Izberite preneseno datoteko »OlympicMedals.accdb« in kliknite Odpri. Prikaže se okno »Izbira tabele«, ki prikazuje tabele v zbirki podatkov. Tabele v zbirki podatkov so podobne delovnim listom ali tabelam v Excelu. Potrdite polje Omogoči izbor več tabel in potrdite polja vseh tabel. Nato kliknite V redu.
   
   

5. Prikaže se okno za uvoz podatkov.

   Opomba: Opazili boste potrditveno polje na dnu okna, ki vam omogoča dodajanje teh podatkov v podatkovni model, prikazano na spodnji sliki. Podatkovni model se ustvari samodejno, ko hkrati uvozite ali delate z dvema ali več tabelami. Podatkovni model združi tabele in tako omogoči obsežno analizo z vrtilnimi tabelami, Power Pivot in dodatkom Power View. Ko uvozite tabele iz zbirke podatkov, so obstoječe relacije zbirk podatkov med temi tabelami uporabljene za ustvarjanje podatkovnega modela v Excelu. Podatkovni model je v Excelu prosojen, vendar si ga lahko ogledate in spremenite neposredno Power Pivot dodatkom. V tej vadnici bomo o podatkovnem modelu podrobneje predstavili podatkovni model.

   
   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

Raziskovanje uvoženih podatkov je preprosto z vrtilno tabelo. V vrtilni tabeli povlečete polja (podobna stolpcem v Excelu) iz tabel (kot tabele, ki ste jih pravkar uvozili iz Accessove zbirke podatkov) v različna območja vrtilne tabele, da prilagodite način predstavitve 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 kjer koli v vrtilni tabeli, da zagotovite, da je izbrana Excelova vrtilna tabela. Na seznamu Polja vrtilne tabele , kjer je tabela Disciplines razširjena, premaknite kazalec miške nad polje Discipline in na desni strani polja se prikaže puščica spustnega seznama. Kliknite spustni meni, kliknite (Izberi vse), da odstranite vse izbore, nato pa se pomaknite navzdol in izberite lokostrelstvo, potapljanje, sabliranje, umetnostno drsanje in hitrostno drsanje. Kliknite V redu.

   2. Ali pa v razdelku Oznake vrstic vrtilne tabele kliknite spustni meni ob možnosti Oznake vrstic v vrtilni tabeli, kliknite (Izberi vse), da odstranite vse izbore, nato pa 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

Uvozimo podatke iz drugega vira, tokrat iz obstoječega delovnega zvezka, nato pa določimo relacije med obstoječimi podatki in novimi podatki. Z relacijami lahko analizirate zbirke podatkov v Excelu in ustvarite zanimive in potopne ponazoritve 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 na List1. Če izberete celico s podatki, na primer celico A1, lahko pritisnete Ctrl + A, da izberete vse sosednje podatke. Zaprite delovni OlympicSports.xlsx zvezka.

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 kot tabelo tudi na traku, in sicer tako, da izberete OSNOVNO > Oblikuj kot tabelo. Ker podatki vključujejo glave, v prikazanem oknu Ustvari tabelo izberite Tabela ima glave, kot je prikazano spodaj.
   
   
   
   Oblikovanje podatkov v obliki tabele ima številne prednosti. Tabeli lahko dodelite ime, s čimer jo boste lažje prepoznali. Med tabelami lahko ustvarite tudi relacije in tako omogočite raziskovanje in analizo vsebine v vrtilnih tabelah, dodatku Power Pivot ter funkciji Power View.

6. Poimenujte tabelo. Na zavihku ORODJA ZA TABELE > NAČRT > Lastnosti poiščite polje Ime tabele in vnesite Sports. 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.

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 > Oblikuj kot tabelo. Podatki imajo glave, zato v prikazanem oknu Ustvari tabelo izberite Tabela ima glave.

3. Poimenujte tabelo. Na zavihku ORODJA ZA TABELE > NAČRT > Lastnosti 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. V preglednici List1 na vrhu razdelka Polja vrtilne tabele kliknite Vse, da prikažete seznam tabel, ki so na voljo, kot je prikazano na sliki 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. Za polje Tabela s spustnega seznama izberite Discipline.

6. Za polje Stolpec (tuji) izberite SportID.

7. Za polje Povezana tabela izberite Sports.

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 boste z razširitvijo relacij podatkovnega modela z Excelom, programomPower Pivot in dax gradili na tem, kar ste se naučili tukaj, in se naučili razširiti podatkovni model z zmogljivimi in vizualnimi Excelovimi dodatki, imenovanimi Power Pivot. Naučili se boste tudi izračunati stolpce v tabeli in uporabiti izračunani stolpec tako, da podatkovnem modelu lahko dodate sicer nepovezane tabele.

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 ali 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 ter 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