Duomenų modelio kūrimas programoje „Excel“

Taikoma

„Excel“, skirta „Microsoft 365“ „Excel 2024“ Excel 2021

Duomenų modelis leidžia integruoti duomenis iš kelių lentelių, efektyviai kuriant sąryšinį duomenų šaltinį "Excel" darbaknygėje. Programoje "Excel" duomenų modeliai naudojami skaidriai, pateikiant lentelių duomenis, naudojamus "PivotTable" ir "PivotChart". Duomenų modelis vizualizuojamas kaip lentelių rinkinys laukų sąraše ir dažniausiai su juo dirbate "PivotTable" laukų sąraše ir galite jo nepastebėti.

Prieš pradėdami dirbti su duomenų modeliu, turite gauti tam tikrų duomenų. Šiuo tikslu naudosime „Power Query“ funkciją "Gauti & transformuoti", todėl galbūt norėsite atsitraukti ir peržiūrėti vaizdo įrašą arba pasinaudoti mūsų mokymosi vadovu "Gauti & transformuoti" ir "Power Pivot". Duomenys turėtų būti lentelėse (ne tik langelių diapazonuose), kad juos būtų galima įkelti ir tinkamai susieti.

Būtinosios sąlygos

Kur yra „Power Pivot“?

„Excel“, skirta „Microsoft 365“ – "Power Pivot" įtraukta į juostelę.

Kur yra "Get & Transform" („Power Query“)?

„Excel“, skirta „Microsoft 365“ – Gauti & transformavimą („Power Query“) integruotas su "Excel" skirtuke Duomenys.

Darbo pradžia

Pirmiausia turite gauti tam tikrų duomenų.

Sukurkite naują darbaknygę arba atidarykite darbaknygę, kurioje nėra duomenų.
„Excel“, skirta „Microsoft 365“ juostelėje pasirinkite skirtuką Duomenys. Skyriuje Duomenų gavimas & keitimas pasirinkite Gauti duomenis, kad importuotumėte duomenis iš bet kokio skaičiaus išorinių duomenų šaltinių, pvz., teksto failo, "Excel" darbaknygės, svetainės, "Microsoft Access" „SQL Server“ ar kitos reliacinės duomenų bazės, kurioje yra kelios susietos lentelės.
Bus rodomas "Excel" raginimas pasirinkti vieną ar kelias lenteles. Jei norite gauti kelias lenteles iš to paties duomenų šaltinio, pažymėkite žymės langelį Pasirinkti kelis elementus .
1. Pasirinkite Transformuoti. Pasirinkus kelias lenteles, "Excel" automatiškai sukuria duomenų modelį. Daugiau informacijos žr.: Užklausos kūrimas, įkėlimas arba redagavimas programoje "Excel" ("„Power Query“").
  
  Pastaba
  
  Šiuose pavyzdžiuose naudojame "Excel" darbaknygę su išgalvota studentų informacija apie klases ir pažymius. Galite atsisiųsti mūsų studentų duomenų modelio darbaknygės pavyzdį ir sekti. Taip pat galite atsisiųsti versiją su užbaigtu duomenų modeliu.
Dabar turite duomenų modelį, kuriame yra visos importuotos lentelės ir jos bus rodomos " PivotTable" laukų sąraše.

Pastaba

Modeliai paprastai kuriami netiesiogiai, kai į „Excel“ vienu metu importuojate dvi ar daugiau lentelių.
Modeliai kuriami aiškiai, kai duomenims importuoti naudojate "Power Pivot" papildinį. Papildinyje modelis pateikiamas skirtukų makete, panašiai kaip "Excel", kur kiekviename skirtuke yra lentelės duomenys. Žr. Duomenų gavimas naudojant "Power Pivot" papildinį, kad sužinotumėte duomenų importavimo pagrindus naudojant "„SQL Server“" duomenų bazę.
Modelį gali sudaryti viena lentelė. Norėdami sukurti modelį, pagrįstą tik viena lentele, pasirinkite lentelę ir " Power Pivot" spustelėkite Įtraukti į duomenų modelį . Šiuos veiksmus galite atlikti norėdami naudoti "Power Pivot" funkcijas, pvz., filtruotus duomenų rinkinius, apskaičiuojamuosius stulpelius, apskaičiuotuosius laukus, KPI ir hierarchijas.
Lentelių ryšiai gali būti kuriami automatiškai, jeigu importuojate susijusias lenteles, turinčias pirminių ir išorinio rakto ryšių. Paprastai „Excel“ gali naudoti importuotą ryšių informaciją kaip duomenų modelio lentelių ryšių pagrindą.
Patarimų, kaip sumažinti duomenų modelį, rasite skyriuje Atmintį taupančio duomenų modelio kūrimas naudojant "Excel" ir "PowerPivot".
Daugiau informacijos žr. Vadovėlis: duomenų importavimas į "Excel" ir duomenų modelio kūrimas.

Patarimas

Kaip sužinoti, ar darbaknygėje yra duomenų modelis? Eikite į "Power Pivot>Manage". Jei matote į darbalapį panašius duomenis, vadinasi, modelis yra. Žr.: Sužinokite, kurie duomenų šaltiniai naudojami darbaknygės duomenų modelyje , kad sužinotumėte daugiau.

Lentelių ryšių kūrimas

Kitas žingsnis yra sukurti ryšius tarp lentelių, kad galėtumėte gauti duomenis iš bet kurios iš jų. Kiekviena lentelė turi turėti pirminį raktą arba unikalų lauko identifikatorių, pvz., studento ID arba klasės numerį. Paprasčiausias būdas yra nuvilkti šiuos laukus, kad sujungtumėte juos "PowerPivot" diagramos rodinyje.

Eikite į "Power Pivot>Manage".
On the Home tab, select Diagram View.
Bus rodomos visos importuotos lentelės, todėl galbūt norėsite šiek tiek laiko pakeisti jų dydį, atsižvelgdami į tai, kiek laukų kiekvienoje turime.
Tada vilkite pirminio rakto lauką iš vienos lentelės į kitą. Toliau pateiktas pavyzdys yra mūsų mokinių lentelių diagramos rodinys:

Sukūrėme šiuos saitus:
- tbl_Students | Studento ID > tbl_Grades | Studento ID
  Kitaip tariant, nuvilkite lauką Studento ID iš lentelės Studentai į lauką Studento ID, esantį lentelėje Įvertinimai.
- tbl_Semesters | Semestro ID > tbl_Grades | Semestras
- tbl_Classes | Klasės numeris > tbl_Grades | Klasės numeris
Pastaba
- Norint sukurti ryšį, laukų pavadinimai nebūtinai turi sutapti, tačiau jie turi būti to paties duomenų tipo.
- Diagramos rodinio jungčių vienoje pusėje yra "1", o kitoje – "*". Tai reiškia, kad tarp lentelių yra ryšys "vienas su daugeliu" ir tai lemia, kaip duomenys naudojami "PivotTable". Norėdami sužinoti daugiau, žr. Lentelių ryšiai duomenų modelyje .
- Jungtys tik nurodo, kad tarp lentelių yra ryšys. Jie iš tikrųjų nerodys, kurie laukai yra susieti vienas su kitu. Norėdami peržiūrėti saitus, eikite į "Power Pivot>"Dizaino>ryšių>valdymasryšių>valdymas. Programoje "Excel" galite pereiti į duomenų>ryšius.

Duomenų modelio naudojimas kuriant "PivotTable" arba "PivotChart"

"Excel" darbaknygėje gali būti tik vienas duomenų modelis, tačiau tame modelyje gali būti kelios lentelės, kurias galima pakartotinai naudoti visoje darbaknygėje. Bet kuriuo metu į esamą duomenų modelį galite įtraukti daugiau lentelių.

"Power Pivot" eikite į Tvarkymas.
Skirtuke Pagrindinis pasirinkite "PivotTable".
Pasirinkite, kur norite padėti "PivotTable": naują darbalapį ar dabartinę vietą.
Spustelėkite Gerai ir "Excel" įtrauks tuščią "PivotTable", kurios dešinėje bus rodoma sritis Laukų sąrašas.

Tada sukurkite "PivotTable" arba "PivotChart". Jei jau esate sukūrę ryšius tarp lentelių, "PivotTable" galite naudoti bet kurį jų lauką. Jau sukūrėme ryšius studento duomenų modelio pavyzdinėje darbaknygėje.

Esamų nesusijusių duomenų įtraukimas į duomenų modelį

Tarkime, kad importavote arba nukopijavote daug duomenų, kuriuos norite naudoti modelyje, tačiau neįtraukėte jų į duomenų modelį. Įtraukti naujų duomenų į modelį yra lengviau nei manote.

Pradėkite pažymėdami bet kurį duomenų, kuriuos norite įtraukti į modelį, langelį. Tai gali būti bet koks duomenų intervalas, bet geriausiai tinka duomenys, suformatuoti kaip "Excel" lentelė .
Duomenis įtraukite vienu iš toliau nurodytų būdų.
Spustelėkite "Power Pivot>"Įtraukti į duomenų modelį.
Spustelėkite Įterpti"PivotTable", tada pažymėkite Įtraukti> šiuos duomenis į duomenų modelį dialogo lange Kurti "PivotTable".

Intervalas arba lentelė dabar įtraukiami į modelį kaip susieta lentelė. Norėdami daugiau sužinoti apie darbą su susietomis modelio lentelėmis, žr. Duomenų įtraukimas į "Power Pivot" naudojant "Excel" susietas lenteles.

Duomenų įtraukimas į "PowerPivot" lentelę

Naudojant "Power Pivot" negalima įtraukti eilutės į lentelę tiesiogiai įrašant tekstą naujoje eilutėje, kaip tai galite padaryti "Excel" darbalapyje. Tačiau eilutes galite įtraukti nukopijuodami ir įklijuodami arba atnaujindami šaltinio duomenis ir atnaujindami "Power Pivot" modelį.