Kaip sukurti ryšius tarp kelių lentelių naudojant „Excel“ duomenų modelį

Kaip sukurti ryšius tarp kelių lentelių naudojant „Excel“ duomenų modelį

„Excel“ yra galingas duomenų analizės ir vėlesnio automatizavimo įrankis tvarkant didelius duomenų rinkinius. Galite praleisti daug laiko analizuodami daugybę duomenų naudodami VLOOKUP, INDEX-MATCH, SUMIF ir kt.





„Excel“ duomenų modelio dėka galite sutaupyti brangaus laiko naudodami automatines duomenų ataskaitas. Sužinokite, kaip lengvai galite priskirti ryšį tarp dviejų lentelių naudodami duomenų modelį ir tokio ryšio iliustraciją suvestinėje lentelėje kitame skyriuje.



Pagrindiniai reikalavimai

Jums reikės „Power Pivot“ ir „Power Query“ („Get & Transform“), kad kurdami atliktumėte kelias užduotis „Excel“ Duomenų modelis. Štai kaip galite gauti šias funkcijas „Excel“ darbaknygėje:





Kaip gauti „Power Pivot“

1. „Excel 2010“: Turite atsisiųsti „Power Pivot“ priedą iš „Microsoft“ ir tada įdiekite ją savo „Excel“ programai savo kompiuteryje.

2. „Excel 2013“: „Excel 2013“ „Office Professional Plus“ leidime yra „Power Pivot“. Tačiau prieš pradėdami naudoti, turite jį suaktyvinti. Štai kaip:



  1. Spustelėkite Failas ant Juostelė „Excel“ darbaknygėje.
  2. Tada spustelėkite Galimybės atidaryti „Excel“ parinktys .
  3. Dabar spustelėkite Papildiniai .
  4. Pasirinkite COM priedai spustelėdami išskleidžiamąjį meniu Tvarkyti dėžė.
  5. Spustelėkite Eik ir pažymėkite žymės langelį „Microsoft Power Pivot“, skirta „Excel“ .

3. „Excel 2016“ ir naujesnės versijos: „Power Pivot“ meniu rasite Juostelė .

Susijęs: Kaip pridėti kūrėjo skirtuką prie juostelės „Microsoft Word“ ir „Excel“



Kaip gauti „Power Query“ (gauti ir transformuoti)

1. „Excel 2010“: „Power Query“ priedą galite atsisiųsti iš „Microsoft“ . Po įdiegimo, „Power Query“ pasirodys ant Juostelė .

2. „Excel 2013“: Turite suaktyvinti „Power Query“ atlikdami tuos pačius veiksmus, kuriuos ką tik atlikote, kad „Power Pivot“ veiktų „Excel 2013“.



3. „Excel 2016“ ir naujesnės versijos: „Power Query“ („Get & Transform“) galite rasti apsilankę Duomenys skirtuką „Excel“ Juostelė .

Sukurkite duomenų modelį importuodami duomenis į „Excel“ darbaknygę

Šioje pamokoje galite gauti iš anksto suformatuotų pavyzdinių duomenų iš „Microsoft“:

parsisiųsti : Pavyzdinis studentas duomenis (tik duomenys) | Pavyzdinis studentas duomenis (pilnas modelis)

Galite importuoti duomenų bazę su keliomis susijusiomis lentelėmis iš daugelio šaltinių, pvz., „Excel“ darbaknygių, „Microsoft Access“, svetainių, SQL serverio ir kt. Tada turite suformatuoti duomenų rinkinį, kad „Excel“ galėtų jį naudoti. Štai veiksmai, kuriuos galite išbandyti:

1. „Excel 2016“ ir vėlesniuose leidimuose spustelėkite Duomenys skirtuką ir pasirinkite Nauja užklausa .

2. Rasite kelis būdus importuoti duomenis iš išorinių ar vidinių šaltinių. Pasirinkite tą, kuris tau tinka.

3. Jei naudojate „Excel 2013“ leidimą, spustelėkite „Power Query“ ant Juostelė ir tada pasirinkite Gaukite išorinius duomenis pasirinkti importuojamus duomenis.

4. Pamatysite Navigatorius langelį, kuriame turite pasirinkti, kurias lenteles norite importuoti. Spustelėkite žymės langelį Pasirinkite kelis elementus pasirinkti keletą lentelių importui.

5. Spustelėkite Įkelti užbaigti importo procesą.

6. Naudodama šias lenteles „Excel“ sukurs jums duomenų modelį. Lentelės stulpelių antraštes galite pamatyti skiltyje „PivotTable“ laukai sąrašus.

Taip pat galite naudoti „Power Pivot“ funkcijas, pvz., Apskaičiuotus stulpelius, KPI, hierarchijas, apskaičiuotus laukus ir filtruotus duomenų rinkinius iš „Excel“ duomenų modelio. Norėdami tai padaryti, turėsite sukurti duomenų modelį iš vienos lentelės. Galite išbandyti šiuos veiksmus:

1. Suformatuokite duomenis lentelės modeliu, pažymėdami visas langelius, kuriuose yra duomenų, ir spustelėkite „Ctrl“+T. .

2. Dabar pasirinkite visą lentelę ir spustelėkite „Power Pivot“ skirtuką ant Juostelė .

3. Iš Lentelės skyriuje, spustelėkite Pridėti prie duomenų modelio .

„Excel“ sukurs lentelės ryšius tarp susijusių duomenų iš duomenų modelio. Tam importuotose lentelėse turėtų būti pirminių ir užsienio raktų santykiai.

„Excel“ naudoja ryšių informaciją iš importuotos lentelės kaip pagrindą, kad sukurtų ryšius tarp duomenų modelio lentelių.

Susijęs: Kaip sukurti „Kas, jei“ analizę „Microsoft Excel“

Sukurkite ryšius tarp duomenų modelio lentelių

Dabar, kai „Excel“ darbaknygėje turite duomenų modelį, turėsite apibrėžti ryšius tarp lentelių, kad sukurtumėte prasmingas ataskaitas. Prie kiekvienos lentelės turite priskirti unikalų lauko identifikatorių arba pirminį raktą, pvz., Semestro ID, klasės numerį, studento ID ir kt.

„Power Pivot“ diagramos rodinio funkcija leis vilkti ir mesti tuos laukus, kad sukurtumėte ryšį. Norėdami sukurti lentelės nuorodas „Excel“ duomenų modelyje, atlikite šiuos veiksmus:

1. Ant Juostelė „Excel“ darbaknygėje spustelėkite „Power Pivot“ Meniu.

2. Dabar spustelėkite Tvarkyti viduje konors Duomenų modelis skyrius. Pamatysite „Power Pivot“ redaktorius, kaip parodyta žemiau:

3. Spustelėkite Diagramos vaizdas mygtuką, esantį Peržiūrėti „Power Pivot“ skyriuje Namai skirtuką. Pamatysite lentelių stulpelių antraštes, sugrupuotas pagal lentelės pavadinimą.

4. Dabar galėsite nuvilkti unikalų lauko identifikatorių iš vienos lentelės į kitą. Toliau pateikiama santykių schema tarp keturių „Excel“ duomenų modelio lentelių:

Toliau aprašomas ryšys tarp lentelių:

  • Stalo studentai | Mokinio pažymėjimas prie stalo Įvertinimai | Studento pažymėjimas
  • Lentelės semestrai | Semestro ID prie lentelės Įvertinimai | Semestras
  • Lentelių klasės | Klasės numeris iki lentelės Įvertinimai | Klasės ID

5. Ryšius galite sukurti pasirinkę porą unikalių verčių stulpelių. Jei yra dublikatų, pamatysite šią klaidą:

6. Jūs pastebėsite Žvaigždė (*) iš vienos pusės ir Vienas (1) kita vertus, Diagramos santykių rodinyje. Tai apibrėžia, kad tarp lentelių egzistuoja ryšys „vienas su daugeliu“.

7. „Power Pivot“ redaktoriuje spustelėkite Dizainas skirtuką, tada pasirinkite Tvarkyti santykius žinoti, kurie laukai užmezga ryšius.

Sukurkite „PivotTable“ naudodami „Excel“ duomenų modelį

Dabar galite sukurti „PivotTable“ arba „PivotChart“, kad vizualizuotumėte savo duomenis iš „Excel“ duomenų modelio. „Excel“ darbaknygėje gali būti tik vienas duomenų modelis, tačiau galite ir toliau atnaujinti lenteles.

Susijęs: Kas yra duomenų gavyba ir ar tai neteisėta?

Kadangi duomenys laikui bėgant keičiasi, galite ir toliau naudoti tą patį modelį ir sutaupyti laiko dirbdami su tuo pačiu duomenų rinkiniu. Pastebėsite daugiau laiko taupymo dirbdami su tūkstančių eilučių ir stulpelių duomenimis. Norėdami sukurti „PivotTable“ ataskaitą, atlikite šiuos veiksmus:

1. „Power Pivot“ redaktoriuje spustelėkite Namai skirtuką.

2. Ant Juostelė , spustelėkite Suvestinės lentelės .

3. Pasirinkite bet kuris tarp naujo darbalapio arba esamo darbalapio.

kaip ištrinti „Hotmail“ paskyrą

4. Pasirinkite Gerai . „Excel“ pridės Suvestinės lentelės kad parodys Laukų sąrašas sritis dešinėje.

Toliau pateikiamas holistinis suvestinės lentelės vaizdas, sukurtas naudojant „Excel“ duomenų modelį šioje pamokoje naudojamiems mokinių duomenų pavyzdžiams. Taip pat galite sukurti profesionalias suvestines lenteles ar diagramas iš didelių duomenų naudodami „Excel“ duomenų modelio įrankį.

Paverskite sudėtingus duomenų rinkinius į paprastas ataskaitas naudodami „Excel“ duomenų modelį

„Excel“ duomenų modelis naudoja ryšių tarp lentelių kūrimo pranašumus, kad būtų sukurtos reikšmingos suvestinės lentelės ar diagramos duomenų ataskaitų teikimo tikslais.

Galite nuolat atnaujinti esamą darbaknygę ir skelbti atnaujintų duomenų ataskaitas. Jums nereikia redaguoti formulių ar investuoti laiko slinkdami tūkstančius stulpelių ir eilučių kiekvieną kartą, kai atnaujinami šaltinio duomenys.

Dalintis Dalintis „Tweet“ Paštu Kaip sukurti „Pivot“ lentelę „Excel“

Sužinokite, kaip „Excel“ sukurti suvestines lenteles ir kaip jomis pasinaudoti norint gauti norimą informaciją.

Skaityti toliau Susijusios temos
  • Produktyvumas
  • Skaičiuoklės patarimai
  • „Microsoft Excel“
  • „Microsoft Office“ patarimai
  • Duomenų analizė
Apie autorių Tamal Das(Paskelbta 100 straipsnių)

Tamalas yra laisvai samdomas rašytojas „MakeUseOf“. Įgijęs didelę patirtį technologijų, finansų ir verslo procesų srityje ankstesniame darbe IT konsultacijų įmonėje, prieš 3 metus jis priėmė rašymą kaip visą darbo dieną dirbančią profesiją. Nors nerašo apie produktyvumą ir naujausias technologijų naujienas, jis mėgsta žaisti „Splinter Cell“ ir iš naujo žiūrėti „Netflix“/ „Prime Video“.

Daugiau iš Tamal Das

Prenumeruokite mūsų naujienlaiškį

Prisijunkite prie mūsų naujienlaiškio, kad gautumėte techninių patarimų, apžvalgų, nemokamų el. Knygų ir išskirtinių pasiūlymų!

Norėdami užsiprenumeruoti, spustelėkite čia