Õppetükk: andmete importimine Excelisse ja andmemudeli loomine

Abstraktne: See on sarja esimene õppetükk, mis on mõeldud selleks, et saaksite tuttavaks ja mugavaks Exceli ning selle sisseehitatud andmeülevaate- ja analüüsifunktsioonide kasutamisega. Nendes õppeteemades tutvustatakse seda, kuidas saate Excelis uue töövihiku luua ja seda viimistleda ning seejärel Power View’ abil põneva interaktiivse aruande koostada. Õppetükkide eesmärk on tutvustada Microsofti ärianalüüsifunktsioone ja -võimalusi Excelis, PivotTable-liigendtabelites, Power Pivotis ja Power View's. 

Nendes õppeteemades saate teada, kuidas Excelis andmeid importida ja uurida, Power Pivoti abil andmemudelit koostada ja piiritleda ning Power View's interaktiivseid aruandeid luua, mida saate avaldada, kaitsta ja teistega jagada.

See sari sisaldab järgmisi õppeteemasid:

1. Andmete importimine Excel 2016 ja andmemudeli loomine
2. Andmemudeli seoste laiendamine Exceli, Power Pivoti ja DAX-i abil
3. Kaardipõhiste Power View’ aruannete loomine
4. Interneti-andmete kaasamine ja Power View’ aruannete vaikeväärtuste seadmine
5. Power Pivoti spikker
6. Vaimustavate Power View’ aruannete loomine – 2. osa

Selles õppeteemas alustatakse tühja Exceli tabeliga.

Õppeteema sisaldab järgmisi peatükke.

• Andmete importimine andmebaasist
• Andmete importimine arvutustabelist
• Andmete importimine kopeerimist ja kleepimist kasutades
• Seose loomine imporditud andmete vahel
• Kontrollpunkt ja test

Õppeteema lõpus on test, mille abil saate õpitut kontrollida.

Õpetuste seerias kasutatakse näidisena olümpiamedalite, korraldajariikide ja olümpiaaladega seotud andmeid. Soovitame kõik õppeteemad samas järjekorras läbida. 

Andmete importimine andmebaasist

Alustame seda õppeteemat tühja töövihikuga. Selles jaotises tutvustatakse seda, kuidas saate välise andmeallikaga ühenduse luua ja sealt andmed analüüsimiseks Excelisse importida.

Alustuseks laadime Internetist alla mõned andmed. Need Microsoft Accessi andmebaasifailid sisaldavad andmeid olümpiamedalite kohta.

1. Õppeteemade sarja jaoks vajalike õppefailide allalaadimiseks klõpsake järgmisi linke. Laadige kõik neli faili hõlpsalt juurdepääsetavasse kohta (nt Allalaaditud failid või Minu dokumendid) või uude loodud kausta.
   > Accessi andmebaas OlympicMedals.accdb
   > OlympicSports.xlsx Exceli töövihik
   > Population.xlsx Exceli töövihik
   > DiscImage_table.xlsx Exceli töövihik

2. Avage Excelis tühi töövihik.

3. Klõpsake käsku Andmete > toomine > andmebaasist > Microsoft Accessi andmebaasist. Lint kohandub dünaamiliselt vastavalt töövihiku laiusele, nii et lindi käsud võivad välja näha veidi teistsugused kui järgmisel kuvatõmmisel.

   

4. Valige allalaaditud fail OlympicMedals.accdb ja klõpsake nuppu Impordi. Kuvatakse järgmine aken Navigaator, kus kuvatakse andmebaasist leitud tabelid. Andmebaasi tabelid sarnanevad Exceli töövihikute ja tabelitega. Märkige ruut Vali mitu tabelit ja valige kõik tabelid. Seejärel klõpsake nuppu Laadi laadimisalus>.

   

5. Kuvatakse aken Andmete importimine.

   Märkus.

   Pange tähele akna allservas asuvat märkeruutud, mis võimaldab teil lisada need andmed andmemudelisse, nagu on näidatud järgmisel kuvatõmmisel. Andmemudel luuakse automaatselt, kui impordite või töötate korraga kahe või enama tabeliga. Andmemudel integreerib tabelid, võimaldades põhjalikku analüüsi PivotTable-liigendtabelite, Power Pivoti ja Power View' abil. Tabelite importimisel andmebaasist kasutatakse Excelis andmemudeli loomiseks nende tabelite olemasolevaid andmebaasiseoseid. Andmemudel on Excelis läbipaistev, kuid saate seda otse Power Pivoti lisandmooduli abil vaadata ja muuta. Selles õppeteemas käsitletakse andmemudelit üksikasjalikumalt.

   Valige suvand PivotTable-aruanne, mille puhul imporditakse tabelid Excelisse ja valmistatakse imporditud tabelite analüüsimiseks ette PivotTable-liigendtabel, ning seejärel klõpsake nuppu OK.

   

6. Pärast andmete importimist luuakse imporditud tabelite põhjal PivotTable-liigendtabel.

   

Kui andmed on Excelisse imporditud ja nende põhjal automaatne andmemudel loodud, saate hakata andmeid uurima.

Andmete uurimine PivotTable-liigendtabeli abil

Imporditud andmete uurimine on PivotTable-liigendtabeli abil lihtne. PivotTable-liigendtabelis saate tabelite (nt Accessi andmebaasist imporditud tabelite) väljad (sarnaselt Exceli veergudega) lohistada PivotTable-liigendtabeli eri aladele , et kohandada nende esitusviisi. PivotTable-liigendtabelil on neli ala: FILTRID, VEERUD, READ ja VÄÄRTUSED.

Ilmselt on vaja paar korda katsetada, et leida igale väljale sobiv ala. Aladele saate lohistada nii mitu välja, kui soovite, et PivotTable-liigendtabelis kuvataks andmed teile sobival viisil. Lohistage välju julgelt PivotTable-liigendtabeli eri aladele – see ei muuda aluseks olevaid andmeid.

Uurime PivotTable-liigendtabelis olümpiamedalitega seotud andmeid, alustades medalivõitjate loendist, mis on korraldatud distsipliini, medalitüübi ja sportlase riigi või regiooni alusel.

1. Paani PivotTable-liigendtabeli väljad tabeli Medals (Medalid) laiendamiseks klõpsake selle kõrval asuvat noolt. Otsige laiendatud tabelist Medals (Medalid) väärtus NOC_CountryRegion (ROK_RiikRegioon) ja lohistage see alale VEERUD. NOC ehk National Olympic Commitees (rahvuslikud olümpiakomiteed) on riikide organisatsioonilised üksused.

2. Seejärel lohistage tabelist Disciplines (Distsipliinid) väärtus Discipline (Distsipliin) alale READ.

3. Filtreerime tabelit Disciplines (Distsipliinid) nii, et kuvataks ainult viis spordiala: Archery, Diving, Fencing, Figure Skating ja Speed Skating (vibulaskmine, sukeldumine, vehklemine, iluuisutamine ja kiiruisutamine). Saate seda teha paanil PivotTable-liigendtabeli väljad või otse PivotTable-liigendtabeli päises Reasildid asuva filtri abil.

   1. Exceli PivotTable-liigendtabeli valimiseks klõpsake PivotTable-liigendtabelis suvalist kohta. Liikuge loendis PivotTable-liigendtabeli väljad , kus tabel Disciplines (Distsipliinid) on laiendatud , kursoriga üle välja Discipline (Distsipliin) ja väljast paremal kuvatakse rippnool. Klõpsake ripploendit, klõpsake kõigi valikute eemaldamiseks nuppu **(Vali kõik)**, liikuge kerides allapoole ja valige Vibulaskmine, Sukeldumine, Vehklemine, Iluuisutamine ja Kiiruisutamine. Klõpsake nuppu OK.
   2. Või klõpsake PivotTable-liigendtabeli jaotises Reasildid päise Reasildid kõrval kuvatavat ripploendi noolt, klõpsake kõikide valikute eemaldamiseks käsku (Vali kõik) ja valige väärtused Archery, Diving, Fencing, Figure Skating ja Speed Skating (vibulaskmine, sukeldumine, vehklemine, iluuisutamine ja kiiruisutamine). Klõpsake nuppu OK.

4. Lohistage paanil PivotTable-liigendtabeli väljad tabelist Medals (Medalid) väli Medal alale VÄÄRTUSED. Kuna väärtused peavad olema arvulised, muudab Excel kirje Medal kirjeks Count of Medal (Medalite arv).

5. Valige tabelis Medals (Medalid) uuesti väli Medal ja lohistage alale FILTRID.

6. Filtreerime PivotTable-liigendtabeli andmeid nii, et kuvataks ainult need riigid/regioonid, mis on võitnud kokku üle 90 medali. Selleks tehke järgmist.

   1. Klõpsake PivotTable-liigendtabeli välja Veerusildid ripploendi noolt.
   2. Valige Väärtusefiltrid ja valige väärtus Suurem kui….
   3. Tippige viimasele (kõige parempoolsemale) väljale väärtus 90. Klõpsake nuppu OK.
      

PivotTable-liigendtabel näeb välja nii nagu järgmisel kuvatõmmisel.

Olete vähese vaevaga loonud PivotTable-liigendtabeli, mis sisaldab kolme eri tabeli välju. Toimingu tegi lihtsaks asjaolu, et tabelite vahel olid seosed juba olemas. Kuna tabeliseosed olid lähteandmebaasis olemas ja te saite kõik tabelid ühe toimingu raames importida, sai Excel need tabeliseosed andmemudelis uuesti luua.

Mida aga teha siis, kui andmed pärinevad eri allikatest või on imporditud hiljem? Üldjuhul saate omavahel ühilduvate veergude põhjal luua uued seosed. Järgmises peatükis selgitame seda, kuidas importida täiendavaid tabeleid ja luua uusi seoseid.

Andmete importimine arvutustabelist

Nüüd selgitame olemasoleva töövihiku näitel seda, kuidas importida andmeid teistest allikatest ning luua olemasolevate ja uute andmete vahel seoseid. Seoste abil saate analüüsida Exceli andmekogusid ning imporditud andmete põhjal luua põnevaid ja immersiivseid visualiseeringuid.

Alustame tühja töölehe loomisega ja seejärel impordime andmed Exceli töövihikust.

1. Lisage uus tööleht ja pange sellele nimeks Spordialad.

2. Otsige sirvides üles kaust, kuhu salvestasite allalaaditud näidisandmefailid, ja avage fail OlympicSports.xlsx.

3. Valige ja kopeerige andmed lehelt Leht1. Kui valite andmeid sisaldava lahtri (nt lahtri A1), saate klahvikombinatsiooni Ctrl+A abil valida kõik külgnevad andmelahtrid. Sulgege töövihik OlympicSports.xlsx.

4. Viige kursor töölehel Spordialad lahtrisse A1 ja kleepige andmed.

5. Andmete vormindamiseks tabelina jätke andmed esile tõstetuks ja vajutage klahvikombinatsiooni Ctrl+T. Andmeid saate tabelina vormindada ka lindil, kui valite HOME Format as Table (Koduvorming > tabelina). Kuna andmed sisaldavad päiseid, märkige aknas Tabelina vormindamine ruut Minu tabelil on päised, nagu on näidatud allpool.

   

   Tabelina vormindamine pakub palju eeliseid. Tabeli hilisema tuvastamise hõlbustamiseks saate tabelile määrata nime. Samuti saate luua tabelitevahelisi seoseid, mis võimaldavad uurida ja analüüsida PivotTable-liigendtabelites, Power Pivotis ja Power View's.

6. Pange tabelile nimi. Otsige jaotises TABLE DESIGN Properties (TABELI KUJUNDUSe > atribuudid) üles väli Table Name (Tabeli nimi ) ja tippige Sports (Spordialad). Töövihik näeb välja selline, nagu on näidatud järgmisel kuvatõmmisel.
   

7. Salvestage töövihik.

Andmete importimine kopeerimist ja kleepimist kasutades

Nüüd kui oleme näidanud seda, kuidas importida andmeid Exceli töövihikust, selgitame ka seda, kuidas importida andmeid veebilehe tabelist või mõnest muust allikast, mille andmed saab kopeerida ja Excelisse kleepida. Järgmiste juhiste järgi saate tabelist lisada olümpiamängude korraldajariigid.

1. Lisage uus Exceli tööleht ja pange sellele nimeks Korraldajariigid.
2. Valige ja kopeerige järgmine tabel koos päistega.

City (Linn)	NOC_CountryRegion (ROK_RiikRegioon)	Alpha-2 Code (Alpha-2 maakood)	Väljaanne	Season (Aastaaeg)
Melbourne / Stockholm	AUS	AS	1956	Suvi
Sydney	AUS	AS	2000	Suvi
Innsbruck	AUT	AT	1964	Talv
Innsbruck	AUT	AT	1976	Talv
Antwerpen	BEL	BE	1920	Suvi
Antwerpen	BEL	BE	1920	Talv
Montreal	CAN	CA	1976	Suvi
Lake Placid	CAN	CA	1980	Talv
Calgary	CAN	CA	1988	Talv
St. Moritz	SUI	SZ	1928	Talv
St. Moritz	SUI	SZ	1948	Talv
Peking	CHN	CH	2008	Suvi
Berliin	GER	GM	1936	Suvi
Garmisch-Partenkirchen	GER	GM	1936	Talv
Barcelona	ESP	SP	1992	Suvi
Helsingi	FIN	FI	1952	Suvi
Pariis	FRA	FR	1900	Suvi
Pariis	FRA	FR	1924	Suvi
Chamonix	FRA	FR	1924	Talv
Grenoble	FRA	FR	1968	Talv
Albertville	FRA	FR	1992	Talv
London	GBR	UK	1908	Suvi
London	GBR	UK	1908	Talv
London	GBR	UK	1948	Suvi
München	GER	DE	1972	Suvi
Ateena	GRC	GR	2004	Suvi
Cortina d'Ampezzo	ITA	IT	1956	Talv
Rooma	ITA	IT	1960	Suvi
Torino	ITA	IT	2006	Talv
Tokyo	JPN	JA	1964	Suvi
Sapporo	JPN	JA	1972	Talv
Nagano	JPN	JA	1998	Talv
Soul	KOR	KS	1988	Suvi
Mehhiko	MEX	MX	1968	Suvi
Amsterdam	NED	NL	1928	Suvi
Oslo	NOR	NO	1952	Talv
Lillehammer	NOR	NO	1994	Talv
Stockholm	SWE	SW	1912	Suvi
St Louis	USA	US	1904	Suvi
Los Angeles	USA	US	1932	Suvi
Lake Placid	USA	US	1932	Talv
Squaw Valley	USA	US	1960	Talv
Moskva	URS	RU	1980	Suvi
Los Angeles	USA	US	1984	Suvi
Atlanta	USA	US	1996	Suvi
Salt Lake City	USA	US	2002	Talv
Sarajevo	YUG	YU	1984	Talv

1. Viige kursor Exceli töölehe Korraldajariigid lahtrisse A1 ja kleepige andmed.
2. Vormindage andmed tabelina. Nagu selles õppeteemas eespool kirjeldatud, vajutage andmete tabelina vormindamiseks klahvikombinatsiooni Ctrl + T või funktsiooni HOME Format as Table (KODU > vorming tabelina). Kuna andmed sisaldavad päiseid, märkige kuvatavas aknas Tabeli vormindamine ruut Minu tabelil on päised
3. Pange tabelile nimi. Otsige jaotises TABLE DESIGN Properties (TABELI KUJUNDUSe > atribuudid ) üles väli Table Name (Tabeli nimi ) ja tippige "Hosts" (Korraldajariigid).
4. Valige veerg Aasta ja valige menüüs AVALEHT vormingusuvand Arv ilma komakohtadeta.
5. Salvestage töövihik. Teie töövihiku ilme on selline, nagu on näidatud järgmisel kuvatõmmisel.

Kui Exceli töövihik sisaldab tabeleid, saate nende tabelite vahel luua seosed. Tabelitevaheliste seoste abil on võimalik koondada kahe tabeli andmeid.

Seose loomine imporditud andmete vahel

Saate imporditud tabelite väljad PivotTable-liigendtabelis kohe kasutusele võtta. Kui Excel ei saa määratleda, kuidas tabeli välja PivotTable-liigendtabelisse kaasata, peab looma seose olemasoleva andmemudeliga. Järgmised juhised selgitavad seda, kuidas luua teistest allikatest imporditud andmete vahel seos.

1. Saadaolevate tabelite täieliku loendi kuvamiseks klõpsake lehe 1PivotTable-liigendtabeli väljade ülaosas nuppuKõik , nagu on näidatud järgmisel kuvatõmmisel.
   

2. Liikuge loendis tabeliteni, mille äsja lisasite.

3. Laiendage tabelit Spordialad ja PivotTable-liigendtabelisse lisamiseks valige Sport (Spordiala). Excel palub teil luua seose, nagu on näidatud järgmisel kuvatõmmisel.
   
    
   See teatis kuvatakse seetõttu, et kasutate välju tabelist, mis ei kuulu aluseks olevasse andmemudelisse. Üks võimalus tabel andmemudelisse lisada on luua seos tabeliga, mis on andmemudelis juba olemas. Seose loomiseks peab ühes tabelis olema unikaalsete kordumatute väärtustega veerg. Näidisandmetena andmebaasist alla laaditud tabelis Disciplines (Distsipliinid) on spordialade koodide väli SportID (SpordialaID). Need spordialade koodid on imporditud Exceli andmetes esindatud väljana. Loome selle seose.

4. Klõpsake käsku LOO... paani PivotTable-liigendtabeli väljad esile tõstetud väljal, et avada dialoogiboks Seose loomine, nagu on näidatud järgmisel kuvatõmmisel.

   

5. Valige jaotises Tabel ripploendist Andmemudelitabel: Distsipliinid .

6. Valige jaotises Veerg (väline) väärtus SportID(SpordialaID).

7. Valige seotud tabelisAndmemudelitabel: Sport.

8. Valige jaotises Seostuv veerg (primaarne) väärtus SportID (SpordialaID).

9. Klõpsake nuppu OK.

PivotTable-liigendtabel muutub vastavalt uuele seosele. Kuid PivotTable-liigendtabel ei ole veel päris valmis, sest ala READ väljad on vales järjekorras. Väärtus Discipline (Distsipliin) on väärtuse Sports (Spordiala) alamkategooria, kuid selles tabelis on väljad valesti korraldatud, sest alal READ on väärtused Discipline (Distsipliin) väärtuste Sports (Spordiala) kohal. Soovimatu järjestus näeb välja nii, nagu on näidatud järgmisel kuvatõmmisel.

1. Teisaldage alal READ väärtus Sport (Spordiala) väärtuse Discipline (Distsipliin) kohale. See järjestus on palju parem ja PivotTable-liigendtabelis kuvatakse andmed soovitud kujul, nagu on näidatud järgmisel kuvatõmmisel.

   

Taustal loob Excel andmemudeli, mida saab kasutada kogu töövihikus, mis tahes PivotTable-liigendtabelis, PivotChart-liigenddiagrammis, Power Pivotis või power View' aruandes. Andmemudeli aluseks on tabelitevahelised seosed, mis määravad kindlaks navigeerimis- ja arvutusteed.

Järgmises õppeteemas Andmemudeli seoste laiendamine Exceli, Power Pivot** ja DAX** abil saate tugineda siin õpitule ning laiendada andmemudelit võimsa ja visuaalse Exceli lisandmooduli Power Pivot abil. Samuti saate teada, kuidas arvutada tabelis veerge ja kasutada seda arvutatud veergu nii, et muidu seostamata tabeli saaks teie andmemudelisse lisada.

Kontrollpunkt ja test

Vaadake õpitu üle

Olete loonud Exceli töövihiku, kus on mitme eraldi imporditud tabeli sisu koondav PivotTable-liigendtabel. Õppisite, kuidas importida andmeid andmebaasist või mõnest muust Exceli töövihikust ning kuidas kopeerida ja kleepida andmeid Excelisse.

Saite teada, kuidas andmete üheskoos tööle panemiseks luua tabeliseos, mille põhjal viib Excel read vastavusse. Lisaks saite teada, et tabeliseoste loomiseks ja seostatud ridade otsimiseks peavad tabeli veerud ühilduma teise tabeli veergudega.

Olete valmis tutvuma selle sarja järgmise õppeteemaga. Klõpsake järgmist linki:

Õppetükk: andmemudeli seoste laiendamine Exceli, Power Pivoti ja DAX-i abil

TEST

Kas soovite kontrollida, kui hästi on õpitu teile meelde jäänud? Siin saate seda teha. Selles testis keskendutakse funktsioonidele, võimalustele ja nõuetele, mida selles õppeteemas tutvustati. Lehe allservas on kirjas õiged vastused. Palju edu!

1. küsimus miks peab imporditud andmed tabeliteks teisendama?

A. Andmeid ei pea tabeliteks teisendama, sest imporditud andmed teisendatakse automaatselt tabeliteks.

B. Imporditud andmete tabeliteks teisendamisel jäetakse need andmemudelist välja. Ainult siis, kui need on andmemudelist välja jäetud, on need saadaval PivotTable-liigendtabelites, Power Pivotis ja Power View's.

C. Imporditud andmete tabeliteks teisendamisel saab need kaasata andmemudelisse ning need saab teha kättesaadavaks PivotTable-liigendtabelitele, Power Pivotile ja Power View'le.

D. Imporditud andmeid ei saa tabeliteks teisendada.

2. küsimus millistest loetletud andmeallikatest saab andmeid Excelisse importida ja andmemudelisse kaasata?

A. Accessi andmebaasidest ja paljudest teistest andmebaasidest.

B. Olemasolevatest Exceli failidest.

C. Kõikidest andmeallikatest, mille andmeid saab kopeerida ja Excelisse kleepida ning tabelina vormindada (sh veebisaitidel ja dokumentides olevad tabelid ja muud andmed, mida saab Excelisse kleepida).

D. Kõik ülaltoodud variandid

3. küsimus mis juhtub PivotTable-liigendtabelis siis, kui PivotTable-liigendtabeli alad ümber korraldada?

A. Midagi ei juhtu – PivotTable-liigendtabeli alasid ei saa ümber korraldada.

B. PivotTable-liigendtabeli vormingut muudetakse, kuid aluseks olevad andmed jäävad samaks.

C. PivotTable-liigendtabeli vormingut ja aluseks olevaid andmeid muudetakse jäädavalt.

D. Aluseks olevaid andmeid muudetakse ja luuakse uued andmehulgad.

4. küsimus milline nõudmine peab olema täidetud, et luua tabelite vahel seos?

A. Kummaski tabelis ei tohi olla unikaalseid kordumatuid väärtusi sisaldavaid veerge.

B. Üks tabel ei tohi olla Exceli töövihiku osa.

C. Veerud ei tohi olla tabeliteks teisendatud.

D. Kõik variandid on valed.

Õiged vastused

1. Õige vastus: C
2. Õige vastus: D
3. Õige vastus: B
4. Õige vastus: D