Vadovėlis: duomenų importavimas į "Excel" ir duomenų modelio kūrimas

Šio mokymo skyriai:

• Duomenų importavimas iš duomenų bazės

• Duomenų importavimas iš skaičiuoklės

• Duomenų importavimas kopijuojant ir įklijuojant

• Ryšio tarp importuotų duomenų kūrimas

• Kontrolinis taškas ir apklausa

Šio mokymo pabaigoje pateikiama apklausa, kurią atlikę galėsite pasitikrinti savo žinias.

Šioje mokymo sekoje naudojami duomenys, kurie apibūdina olimpinius medalius, žaidynes rengiančias šalis ir įvairius Olimpinių žaidynių sporto renginius. Siūlome peržiūrėti kiekvieną mokymo kursą nuosekliai. 

Duomenų importavimas iš duomenų bazės

Šį mokymo kursą pradedame atidarydami tuščią darbaknygę. Šiame skyriuje sužinosite, kaip prisijungti prie išorinio duomenų šaltinio ir importuoti duomenis į programą „Excel“ tolesnei analizei.

Pradėkime atsisiųsdami kai kuriuos duomenis iš interneto. Duomenys susiję su olimpiniais medaliais ir yra „Microsoft Access“ duomenų bazės formato.

1. Spustelėkite šiuos saitus ir atsisiųskite failus, kuriuos naudosime šios mokymo kursų serijos metu. Atsisiųskite kiekvieną iš keturių failų į lengvai pasiekiamą vietą, pvz., Atsisiuntimai arba Mano dokumentai, arba į naują sukurtą aplanką:
   > OlympicMedals.accdb Access" duomenų bazė
   " Excel" darbaknygės
   >OlympicSports.xlsx "Excel" darbaknygės
   >Population.xlsx "Excel" darbaknygės >DiscImage_table.xlsx

2. Programoje "Excel" atidarykite tuščią darbaknygę.

3. Spustelėkite Duomenų > Gauti duomenis > iš duomenų bazės > iš "Microsoft Access" duomenų bazės. Juostelė dinamiškai prisitaiko pagal darbaknygės plotį, todėl juostelės komandos gali atrodyti šiek tiek kitaip, nei pavaizduota šiame ekrane.
   
   

4. Pasirinkite atsisiųstą OlympicMedals.accdb failą ir spustelėkite Importuoti. Rodomas šis naršyklės langas, kuriame rodomos duomenų bazėje rastos lentelės. Duomenų bazės lentelės panašios į „Excel“ darbalapius ar lenteles. Pažymėkite langelį Pasirinkti kelias lenteles ir pažymėkite visas lenteles. Tada spustelėkite Įkelti > Įkelti į.
   
   

5. Rodomas langas Duomenų importavimas.

   Pastaba: Atkreipkite dėmesį į lango apačioje esantį žymės langelį, leidžiantį įtraukti šiuos duomenis į duomenų modelį, kaip parodyta šiame ekrane. Duomenų modelis sukuriamas automatiškai, kai vienu metu importuojate arba dirbate su dviem ar daugiau lentelių. Duomenų modelis integruoja lenteles, įgalinant išsamią analizę naudojant "PivotTable", Power Pivot ir "Power View". Importuojant lenteles iš duomenų bazės, esami duomenų bazės ryšiai tarp tų lentelių naudojami duomenų modeliui programoje "Excel" kurti. Duomenų modelis programoje "Excel" yra skaidrus, tačiau galite jį peržiūrėti ir modifikuoti tiesiogiai naudodami Power Pivot papildinį. Duomenų modelis toliau šiame mokyme aptariamas išsamiau.

   
   Pasirinkite "PivotTable" ataskaitos parinktį, kuri importuoja lenteles į "Excel" ir parengia "PivotTable" analizuoti importuotas lenteles, tada spustelėkite Gerai.
   
   

6. Importavus duomenis, iš importuotų lentelių sukuriama „PivotTable“.
   
   

Importavus duomenis į „Excel“ ir automatiškai sukūrus duomenų modelį, būsite pasirengę duomenis ištirti.

Duomenų analizė naudojant „PivotTable“

Tirti importuotuose duomenis yra paprasta naudojant „PivotTable“. „PivotTable“ vilkite laukus (panašius į „Excel“ stulpelius) iš lentelių (pvz., ką tik importuotų iš „Access“ duomenų bazės) į kitas „PivotTable“ sritis, kas nustatytumėte, kaip pateikti savo duomenis. „PivotTable“ turi keturias sritis: FILTRAI, STULPELIAI, EILUTĖS ir REIKŠMĖS.

Galite šiek tiek užtrukti, kol išmoksite nustatyti, į kurią sritį reikėtų nuvilkti lauką. Iš lentelių galite vilkti tiek laukų, kiek jums reikia, kol „PivotTable“ pateiks duomenis taip, kaip norite juos matyti. Galite laisvai eksperimentuoti vilkdami laukus į skirtingas „PivotTable“ sritis; laukų išdėstymas „PivotTable“ neturi įtakos juos esantiems duomenims.

Patyrinėkime „PivotTable“ esančius olimpinių medalių duomenis, grupuodami olimpinių medalininkus pagal sporto šaką, medalio tipą, atleto atstovaujamą šalį ar regioną.

1. Lange „PivotTable“ laukai išplėskite lentelę Medals spustelėdami šalia jos esančią rodyklę. Išplėstoje lentelėje NOC_CountryRegion Medals raskite lauką NOC_CountryRegion ir nuvilkite jį į sritį STULPELIAI. NOC (Nacionalinis olimpinis komitetas) yra šalies ar regiono organizacinis vienetas.

2. Tada lentelėje Disciplines vilkite sporto šaką į sritį EILUTĖS.

3. Filtruokite sporto šakas taip, kad būtų rodomos tik penkios: Archery, Diving, Fencing, Figure Skating ir Speed Skating. Tai galite padaryti iš vidaus srityje „PivotTable“ laukai arba naudodami „PivotTable“ filtrą Eilučių etiketės.

   1. Spustelėkite bet kurioje "PivotTable" vietoje, kad užtikrintumėte, kad pažymėta "Excel" "PivotTable". Sąraše "PivotTable" laukai, kuriame rungčių lentelė yra išplėsta, palaikykite pelės žymiklį virš lauko Discipline, o lauko dešinėje rodoma išplečiamoji rodyklė. Spustelėkite išplečiamąjį sąrašą, spustelėkite (Žymėti viską), kad pašalintumėte visus pasirinkimus, tada slinkite žemyn ir pasirinkite Archery, Diving, Fencing, Figure Skating ir Speed Skating. Spustelėkite Gerai.

   2. Arba „PivotTable“ dalyje Eilučių žymos spustelėkite šalia Eilučių žymos esančią išplečiamojo sąrašo rodyklę, tada – (Žymėti viską), kad pašalintumėte visus pasirinkimus, tada slinkite žemyn ir pasirinkite Archery, Diving, Fencing, Figure Skating ir Speed Skating. Spustelėkite Gerai.

4. Srities „PivotTable“ laukai lentelėje Medals, lauką Medal vilkite į sritį REIKŠMĖS. Laukas Reikšmės turi būti skaitinis, todėl „Excel“ automatiškai pakeičia lauką Medal į Count of Medal.

5. Lentelėje Medals dar kartą pasirinkite lauką Medal ir vilkite jį į sritį FILTRAI.

6. Išfiltruokite „PivotTable“, kad būtų rodomi tik tos šalys ir regionai, surinkę daugiau nei 90 medalių. Štai kaip tai padaryti:

   1. „PivotTable“ spustelėkite lauko Stulpelių žymos dešinėje esantį išplečiamąjį sąrašą.

   2. Pasirinkite Reikšmių filtrai ir Daugiau nei...

   3. Paskutiniame lauke (dešinėje) įveskite 90. Spustelėkite Gerai.
      

Jūsų „PivotTable“ turėtų atrodyti, kaip pavaizduota šiame ekrane.

Taip be didelių pastangų sukūrėte pradinę „PivotTable“, apimančią laukus iš keturių skirtingų lentelių. Ši užduotis tokia paprasta yra dėl iš anksto nustatytų lentelių ryšių. Šaltinio duomenų bazėje jau buvo lentelių ryšiai ir jūs importavote visas lenteles atlikdami vieną veiksmą, todėl „Excel“ gali iš naujo sukurti tuos ryšius duomenų modelyje.

Tačiau ką daryti, jei duomenys yra iš skirtingų šaltinių arba importuojami vėliau? Paprastai ryšius su naujais duomenimis galite sukurti pagal atitinkamus stulpelius. Kitas veiksmas: importuoti papildomas lenteles ir sužinoti, kaip sukurti naujų ryšių.

Duomenų importavimas iš skaičiuoklės

Dabar galime importuoti duomenis iš kito šaltinio, šį kartą iš esamos darbaknygės, tada nurodyti ryšius tarp turimų ir naujų duomenų. Pasitelkę ryšius programoje „Excel“ galite analizuoti duomenų rinkinius ir importuotų duomenų galite kurti įdomias bei įtraukiančias vizualizacijas.

Pirmiausia sukurkite tuščią darbalapį, tada importuokite duomenis iš „Excel“ darbaknygės.

1. Įterpkite naują „Excel“ darbalapį ir pavadinkite jį Sportas.

2. Pereikite į aplanką, kuriame yra atsisiųsti duomenų failų pavyzdžiai, ir atidarykite OlympicSports.xlsx.

3. Pažymėkite ir nukopijuokite duomenis 1lentelė. Jei pažymėsite langelį su duomenis, pvz., langelį A1, galite paspausti Ctrl + A ir pažymėti visus greta esančius duomenis. Uždarykite darbaknygę OlympicSports.xlsx.

4. Darbalapyje Sportas perkelkite žymeklį į langelį A1 ir įklijuokite duomenis.

5. Laikydami duomenis vis dar pažymėtus, paspauskite Ctrl + T, kad suformatuotumėte duomenis kaip lentelę. Galite suformatuoti duomenis kaip lentelę juostelėje pasirinkdami PAGRINDINIS > Formatuoti kaip lentelę. Duomenys turi antraščių, todėl rodomame lange Lentelės kūrimas pažymėkite Lentelė su antraštėmis, kaip pavaizduota čia.
   
   
   
   Duomenų formatavimas kaip lentelės turi daug pranašumų. Lentelei galima priskirti pavadinimą, kad būtų lengviau identifikuoti. Taip pat galite nustatyti ryšius tarp lentelių, taip įgalindami naršymą ir analizę naudojant „PivotTables“, „Power Pivot“ ir „Power View“.

6. Pavadinkite lentelę. Dalyje LENTELĖS DIZAINAS > Ypatybės raskite lauką Lentelės pavadinimas ir įveskite Sports. Darbaknygė pavaizduojama, kaip nurodyta toliau.
   

7. Įrašykite darbaknygę.

Duomenų importavimas kopijuojant ir įklijuojant

Dabar, kai jau importavome duomenis iš „Excel“ darbaknygės, importuokime duomenis iš lentelės, kurią radome tinklalapyje, arba iš bet kurio kito šaltinio, iš kurio galime kopijuoti ir įklijuoti į „Excel“. Atlikdami paskesnius veiksmus, į lentelę įtrauksite Olimpines žaidynes rengiančius miestus.

1. Įterpkite naują „Excel“ darbalapį ir pavadinkite jį Hosts.

2. Pasirinkite ir nukopijuokite šią lentelę, įskaitant lentelės antraštes.

1. Programos „Excel“ darbalapyje Hosts perkelkite žymeklį į langelį A1 ir įklijuokite duomenis.

2. Formatuokite duomenis kaip lentelę. Kaip aprašyta anksčiau šiame mokymo kurse, norėdami formatuoti duomenis kaip lentelę paspauskite Ctrl + T arba eikite į PAGRINDINIS > Formatuoti kaip lentelę. Duomenys turi antraštes, todėl pasirodžiusiame lange Lentelės kūrimas pažymėkite Lentelė su antraštėmis.

3. Pavadinkite lentelę. Dalyje LENTELĖS DIZAINAS > Ypatybės raskite lauką Lentelės pavadinimas ir įveskite Hosts.

4. Pasirinkite stulpelį Edition ir skirtuke PAGRINDINIS suformatuokite jį kaip Skaičius su 0 dešimtainių skilčių.

5. Įrašykite darbaknygę. Darbaknygė turėtų atrodyti, kaip pavaizduota šiame ekrane.

Dabar, kai „Excel“ darbaknygėje turite lenteles, galite sukurti jų ryšius. Kurdami lentelių ryšius galite įtraukti duomenis iš abiejų lentelių.

Ryšio tarp importuotų duomenų kūrimas

Galite iš karto pradėti naudoti „PivotTable“ laukus iš importuotų lentelių. Jei „Excel“ negali nustatyti, kaip įtraukti lauką į „PivotTable“, ryšius reikia nustatyti naudojant esamą duomenų modelį. Į šiuos veiksmus, jūs išmoksite kaip sukurti ryšį tarp duomenų importavote iš įvairių šaltinių.

1. Lapo1 "PivotTable" laukųviršuje spustelėkiteVisi, kad peržiūrėtumėte visą galimų lentelių sąrašą, kaip parodyta šiame ekrane.
   

2. Slinkite sąrašu ir pažiūrėkite, kokias naujas lenteles ką tik įtraukėte.

3. Išplėskite Sports ir pasirinkite Sports, norėdami įtraukti jį į „PivotTable“. Atminkite, kad „Excel“ įspėja apie sukurtą ryšį, kaip parodyta šiame ekrane.
   
    

   Šis pranešimas atsiranda, kai naudojate laukus iš lentelės, kuri nėra turimo duomenų modelio dalis. Vienas iš būdų įtraukti lentelę į duomenų modelį yra sukurti jo ryšį su lentele, kuri jau yra duomenų modelyje. Norint sukurti ryšį, vienoje iš lentelių turi būti unikalių, nesikartojančių reikšmių stulpelis. Duomenų pavyzdyje iš duomenų bazės importuotoje lentelėje Disciplines yra laukas su sporto šakų kodais – SportID. Tie patys sporto šakų kodai pateikiami kaip importuotų „Excel“ duomenų laukas. Sukurkite ryšį.

4. Spustelėkite KURTI... paryškintoje srityje „PivotTable“ laukai, kad atidarytumėte dialogo langą Ryšių kūrimas, kaip parodyta šiame ekrane.
   
   

5. Dalyje Lentelė išplečiamajame sąraše pasirinkite Duomenų modelio lentelė: rungčių.

6. Srityje Stulpelis (išorinis) pasirinkite SportID.

7. Dalyje Susijusi lentelė pasirinkite Duomenų modelio lentelė: Sportas.

8. Srityje Susijęs stulpelis (pagrindinis) pasirinkite SportID.

9. Spustelėkite Gerai.

„PivotTable“ pokyčiai atspindi naują ryšį. „PivotTable“ dar neatrodo visiškai užbaigta dėl laukų tvarkos srityje EILUTĖS. Discipline yra nurodytos sporto šakos antrinė kategorija, tačiau srityje EILUTĖS Discipline yra virš Sport, todėl ji nėra tinkamai sutvarkyta. Tolesniame ekrane pateikiama netinkamai sutvarkyta lentelė.

1. Srityje EILUTĖS perkelkite Sport virš Discipline. Taip daug geriau, o „PivotTable“ rodo duomenis taip, kaip norite juos matyti, kaip parodyta šiame ekrane.
   
   

Programa „Excel“ kuria duomenų modelį, kurį visoje darbaknygėje galima naudoti kuriant bet kurią „PivotTable“, „PivotCharts“, „Power Pivot“ arba „Power View“ ataskaitą. Lentelių ryšiai yra duomenų modelio pagrindas, kuris nustato naršymo ir skaičiavimo kelius.

Kitame mokyme Duomenų modelio ryšių išplėtimas naudojant "Excel", Power Pivotir DAX remiasi tuo, ką išmokote čia, ir padėsite išplėsti duomenų modelį naudodami galingą ir vaizdinį "Excel" papildinį, vadinamą Power Pivot. Taip pat sužinosite, kaip apskaičiuoti lentelės stulpelius ir naudoti tą apskaičiuojamąjį stulpelį, kad į duomenų modelį būtų galima įtraukti kitaip nesusijusią lentelę.

Kontrolinis taškas ir apklausa

Peržiūrėkite, ką išmokote

Dabar turite "Excel" darbaknygę, kurioje yra "PivotTable" prieiga prie kelių lentelių duomenų, iš kurių kelios importuojamos atskirai. Išmokote importuoti iš duomenų bazės, iš kitos "Excel" darbaknygės ir nukopijuoti bei įklijuoti į "Excel".

Kad duomenys veiktų kartu, reikėjo sukurti lentelių ryšius, kuriuos programa „Excel“ naudoja eilutėms susieti. Taip pat išmokote, kad kuriant ryšius ir ieškant susijusių eilučių yra svarbu turėti lentelės stulpelių, kurie siejasi su kitos lentelės duomenimis.

Esate pasiruošę pradėti kitą šios serijos mokymo kursą. Čia pateikiamas saitas:

Vadovėlis: duomenų modelio ryšių išplėtimas naudojant „Excel“, „Power Pivot“ ir DAX

APKLAUSA

Norite patikrinti, ar gerai atsimenate išmoktus dalykus? Štai galimybė tai padaryti. Šioje apklausoje pabrėžiamos funkcijos, galimybės arba reikalavimai, apie kuriuos sužinojote šio mokymo metu. Atsakymus rasite puslapio apačioje. Sėkmės!

1 klausimas: Kodėl svarbu importuotus duomenis konvertuoti į lenteles?

A: Nereikia konvertuoti duomenų į lenteles, nes visi importuojami duomenys automatiškai paverčiami į lenteles.

B: Jei konvertuosite importuotus duomenis į lenteles, jie nebus įtraukti iš duomenų modelį. Tik į duomenų modelį neįtraukti duomenys yra pasiekiami „PivotTables“, „Power Pivot“ ir „Power View“.

C: Kai konvertuosite importuotus duomenis į lenteles, juos bus galima įtraukti į duomenų modelį ir pasiekti per „PivotTable“, „Power Pivot“ ir „Power View“.

D: Importuotų duomenų negalima konvertuoti į lenteles.

2 klausimas: Kurį iš šių duomenų šaltinių galima importuoti į „Excel“ ir įtraukti į duomenų modelį?

A: „Access“ ir daugelis kitų duomenų bazių.

B: Esami „Excel“ failai.

C: Viskas, ką galite kopijuoti ir įklijuoti į „Excel“ ir formatuoti kaip lentelę, įskaitant duomenų lenteles svetainėse, dokumentus ar kitus elementus, kuriuos galima įterpti į programą „Excel“.

D: Visi išvardyti aukščiau

3 klausimas: Kas nutinka „PivotTable“, kai pakeičiate laukų tvarką keturiose „PivotTable“ srityse?

A: Nieko. Išdėsčius laukus „PivotTable“ srityse, jų tvarkos keisti negalima.

B: „PivotTable“ formatas pakeičiamas, kad atitiktų išdėstymą, tačiau esamų duomenų tai neveikia.

C: „PivotTable“ formatas pakeičiamas, kad atitiktų išdėstymą, tačiau visam laikui pakeičiami ir esami duomenys.

D: Esami duomenys pakeičiami, todėl gaunami nauji duomenų rinkiniai.

4 klausimas: Ko reikia kuriant ryšį tarp lentelių?

A: Visose lentelėse turi būti stulpelis, turintis unikalias, nesikartojančias reikšmes.

B: Viena lentelė turi būti ne „Excel“ darbaknygės dalis.

C: Stulpeliai negali būti konvertuojami į lenteles.

D: Nė vienas iš išvardytų atsakymų nėra teisingas.

Apklausos atsakymai

1. Teisingas atsakymas: C

2. Teisingas atsakymas: D

3. Teisingas atsakymas: B

4. Teisingas atsakymas: D