Anotacija: Tai pirmasis mokymo kursas iš serijos, skirtos supažindinti jus "Excel" ir įdėtosiomis duomenų pridėjimo ir analizės funkcijomis. Šie mokymo kursai išmokys kurti ir tobulinti „Excel“ darbaknygę nuo pradžių, kurti duomenų modelį, tada – neįtikėtinas interaktyviąsias ataskaitas naudojant „Power View“. Šie mokymo kursai skirti atskleisti "„Microsoft“ Business Intelligence" funkcijas ir galimybes, naudojamas "Excel", "PivotTables", "Power Pivot" ir "Power View".
Šiuose mokymuose sužinosite, kaip importuoti ir naršyti duomenis programoje "Excel", kurti ir tikslinti duomenų modelį naudojant "Power Pivot" bei interaktyvias ataskaitas naudojant "Power View", kurias galima publikuoti, saugoti ir bendrinti.
Šios sekos mokymai:
- Duomenų importavimas į "Excel 2016" ir duomenų modelio kūrimas
- Duomenų modelio ryšių išplėtimas naudojant "Excel", "Power Pivot" ir DAX
- Susiejimu pagrįstų „Power View“ ataskaitų kūrimas
- Interneto duomenų įtraukimas ir numatytųjų „Power View“ ataskaitos parametrų nustatymas
- „Power Pivot“ žinynas
- Puikių „Power View“ ataskaitų kūrimas – 2 dalis
Šiame mokymo kurse pradėkite atidarydami tuščią „Excel“ darbaknygę.
Š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.
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 , Mano dokumentai, arba į savo naujai sukurtą aplanką:
> OlympicMedals.accdb "Access" duomenų bazė
> OlympicSports.xlsx "Excel" darbaknygę
> Population.xlsx "Excel" darbaknygę
> DiscImage_table.xlsx "Excel" darbaknygęProgramoje "Excel" atidarykite tuščią darbaknygę.
Spustelėkite Duomenys > Gauti duomenis > iš duomenų bazės > iš "Microsoft Access" duomenų bazės. Juostelės dinamiškai pritaikoma pagal darbaknygės plotį, todėl juostelėje esančios komandos gali atrodyti šiek tiek skirtingai nei pavaizduotame ekrane.
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 žymės langelį Pasirinkite kelias lenteles ir pasirinkite visas lenteles. Tada spustelėkite Įkelti > į.
Rodomas langas Duomenų importavimas.
Pastaba
Atkreipkite dėmesį į žymės langelį lango apačioje, kuris leidžia jums įtraukti šiuos duomenis į duomenų modelį, kuris pavaizduotas šiame ekrane. Duomenų modelis sukuriamas automatiškai, kai vienu metu importuojate dvi ar daugiau lentelių arba su jomis dirbate. Duomenų modelis integruoja lenteles, todėl galima atlikti 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 kuriant duomenų modelį programoje "Excel". Duomenų modelis programoje "Excel" yra skaidrus, bet jį galite peržiūrėti ir modifikuoti tiesiogiai, naudodami "Power Pivot" papildinį. Duomenų modelis išsamiau aptariamas toliau šiame mokymo kurse.
Pasirinkite parinktį „PivotTable“ ataskaita, kuri importuoja lenteles į „Excel“ ir parengia „PivotTable“ analizuoti importuotas lenteles, tada spustelėkite Gerai.
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ą.
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.
Tada lentelėje Disciplines vilkite sporto šaką į sritį EILUTĖS.
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.
- Spustelėkite bet kurioje "PivotTable" vietoje, kad įsitikintumėte, jog pasirinkta "Excel" "PivotTable". " PivotTable" laukų sąraše, kuriame išplėsta lentelė Disciplines , palaikykite žymiklį virš lauko Discipline ir lauko dešinėje pateikiama išplečiamoji rodyklė. Spustelėkite išplečiamąjį sąrašą, spustelėkite **(Pasirinkti viską)**, kad pašalintumėte visus pasirinkimus, tada slinkite žemyn ir pasirinkite Archery, Diving, Fencing, Figure Skating ir Speed Skating. Spustelėkite Gerai.
- 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.
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.
Lentelėje Medals dar kartą pasirinkite lauką Medal ir vilkite jį į sritį FILTRAI.
Išfiltruokite „PivotTable“, kad būtų rodomi tik tos šalys ir regionai, surinkę daugiau nei 90 medalių. Štai kaip tai padaryti:
- „PivotTable“ spustelėkite lauko Stulpelių žymos dešinėje esantį išplečiamąjį sąrašą.
- Pasirinkite Reikšmių filtrai ir Daugiau nei...
- 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.
Įterpkite naują „Excel“ darbalapį ir pavadinkite jį Sportas.
Pereikite į aplanką, kuriame yra atsisiųsti duomenų failų pavyzdžiai, ir atidarykite OlympicSports.xlsx.
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.
Darbalapyje Sportas perkelkite žymeklį į langelį A1 ir įklijuokite duomenis.
Laikydami duomenis vis dar pažymėtus, paspauskite Ctrl + T, kad suformatuotumėte duomenis kaip lentelę. Taip pat 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 teikia 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".
Pavadinkite lentelę. Dalyje LENTELĖS DIZAINAS > Ypatybės raskite lauką Lentelės pavadinimas ir įveskite Sports. Darbaknygė pavaizduojama, kaip nurodyta toliau.
Į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.
- Įterpkite naują „Excel“ darbalapį ir pavadinkite jį Hosts.
- Pasirinkite ir nukopijuokite šią lentelę, įskaitant lentelės antraštes.
- Programos „Excel“ darbalapyje Hosts perkelkite žymeklį į langelį A1 ir įklijuokite duomenis.
- Formatuokite duomenis kaip lentelę. Kaip aprašyta anksčiau šioje pamokoje, paspauskite Ctrl + T, kad formatuotumėte duomenis kaip lentelę, arba iš HOME > Formatuoti kaip lentelę. Duomenys turi antraštes, todėl pasirodžiusiame lange Lentelės kūrimas pažymėkite Lentelė su antraštėmis.
- Pavadinkite lentelę. Dalyje LENTELĖS DIZAINAS > Ypatybės raskite lauką Lentelės pavadinimas ir įveskite Hosts.
- Pasirinkite stulpelį Edition ir skirtuke PAGRINDINIS suformatuokite jį kaip Skaičius su 0 dešimtainių skilčių.
- Į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ų.
Lapas1, "PivotTable" laukų viršuje, spustelėkiteVisi, kad peržiūrėtumėte visą galimų lentelių sąrašą, kaip pavaizduota šiame ekrane.
Slinkite sąrašu ir pažiūrėkite, kokias naujas lenteles ką tik įtraukėte.
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šį.Spustelėkite KURTI... paryškintoje srityje „PivotTable“ laukai, kad atidarytumėte dialogo langą Ryšių kūrimas, kaip parodyta šiame ekrane.
Dalyje Lentelė išplečiamajame sąraše pasirinkite Duomenų modelio lentelė: Disciplinos .
Srityje Stulpelis (išorinis) pasirinkite SportID.
Dalyje Susijusi lentelė pasirinkite Duomenų modelio lentelė: Sportas.
Srityje Susijęs stulpelis (pagrindinis) pasirinkite SportID.
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ė.
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", "PivotChart", "Power Pivot" arba bet kurią "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 Pivot"** ir DAX** remsitės tuo, ką čia išmokote, ir išbandysite duomenų modelio išplėtimą naudodami galingą ir vizualų "Excel" papildinį "Power Pivot". Taip pat sužinosite, kaip apskaičiuoti lentelės stulpelius ir naudoti tą apskaičiuotą 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", pasiekianti duomenis keliose lentelėse, iš kurių kelias importavote atskirai. Išmokote importuoti iš duomenų bazės, iš kitos "Excel" darbaknygės, kopijuoti duomenis ir įklijuoti juos į "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
- Teisingas atsakymas: C
- Teisingas atsakymas: D
- Teisingas atsakymas: B
- Teisingas atsakymas: D
Pastaba
Šios mokymų sekos duomenys ir atvaizdai pagrįsti:
- „Olympics Dataset“ © „Guardian News & Media Ltd.“
- Vėliavų vaizdai iš „CIA Factbook“ (cia.gov)
- Populiacijos duomenys iš „The World Bank“ (worldbank.org)
- Olimpinių žaidynių sporto šakų piktogramas sukūrė Thadius856 ir Parutakupiu