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. Be to, šiuose mokymo kursuose naudojama „Excel 2013“, kurioje įjungta „Power Pivot“. Daugiau informacijos apie "Excel 2013" spustelėkite čia. Nurodymus, kaip įjungti „Power Pivot“, rasite spustelėję čia.

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ą aplanką, kurį sukuriate:
   > OlympicMedals.accdb Access" duomenų bazė
   >OlympicSports.xlsx Excel darbaknygė
   > Population.xlsx Excel darbaknygė
   >DiscImage_table.xlsx Excel darbaknygė

2. Programoje „Excel 2013“ atidarykite tuščią darbaknygę.

3. Spustelėkite DUOMENYS > Gauti išorinių duomenų > Iš „Access“. Juostelės dinamiškai pritaikoma pagal darbaknygės plotį, todėl juostelėje esančios komandos gali atrodyti šiek tiek skirtingai nei tolesniuose ekranai. Pirmame ekrane rodoma juostelė, kai darbaknygė yra plati, antrame atvaizde rodoma darbaknygė, užimanti tik dalį ekrano.
   
   
   
   
   
    

4. Pasirinkite atsisiųstą OlympicMedals.accdb failą ir spustelėkite Atidaryti. Pasirodžiusiame lange Lentelės pasirinkimas rodomos duomenų bazėje rastos lenteles. Duomenų bazės lentelės panašios į „Excel“ darbalapius ar lenteles. Pažymėkite žymės langelį Įgalinti kelių lentelių pasirinkimą ir pasirinkite visas lentelės. Spustelėkite Gerai.
   
   

5. Rodomas langas Duomenų importavimas.

   Pastaba: Atkreipkite dėmesį į žymės langelį lango apačioje, kuris leidžia įtraukti šiuos duomenis į duomenų modelį,rodomą šiame ekrane. Duomenų modelis sukuriamas automatiškai, kai vienu metu importuojate arba dirbate su dviem ar daugiau lentelių. Duomenų modelis integruoja lenteles, kad būtų galima atlikti išsamią analizę naudojant "PivotTable", " Power Pivot " ir "Power View". Kai importuojate lenteles iš duomenų bazės, esami duomenų bazės ryšiai tarp šių lentelių naudojami kuriant duomenų modelį Excel. Duomenų modelis yra skaidrus Excel, bet galite jį peržiūrėti ir modifikuoti tiesiogiai naudodami Power Pivot papildinį. Toliau šiame mokymo vadove išsamiau aptariamas duomenų modelis.

   
   Pasirinkite "PivotTable" ataskaitos parinktį, kuri importuoja lenteles į "Excel" ir paruošia "PivotTable" importuotoms lentelėms analizuoti, 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 Excel pasirinkta "PivotTable". Sąraše "PivotTable" laukai, kuriame išplėsta lentelė Disciplines, laikykite žymiklį virš lauko Disciplina ir lauko dešinėje rodoma išplečiamojo sąrašo 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ę. Eikite į LENTELĖS ĮRANKIAI > DIZAINAS > Ypatybės ir raskite lauką Lentelės pavadinimas ir įveskite Sportas. 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ę. Eikite į LENTELĖS ĮRANKIAI > DIZAINAS > Ypatybės ir 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. 1 lape, "PivotTable" laukųviršuje spustelėkite Visi, kad būtų rodomas visas galimų lentelių sąrašas, kaip parodyta toliau pateiktame 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. Srities Lentelė išplečiamajame sąraše pasirinkite Disciplines.

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

7. Srityje Susijusi lentelė pasirinkite Sports.

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 mokymo vadove išplėskite duomenų modelio ryšius naudodami "Excel 2013", "Power Pivot " ir DAX, kuriate tai, ką čia išmokote, ir tęskite duomenų modelio išplėtimą 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 kitaip nesusijusią lentelę būtų galima įtraukti į duomenų modelį.

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 importavote atskirai. Išmokote importuoti iš duomenų bazės, iš kitos Excel darbaknygės ir nuo duomenų kopijavimo ir įklijimo į 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:

Duomenų modelio ryšių išplėtimas naudojant „Excel 2013“, „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