Duomenų modelio kūrimas programoje „Excel“

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 lentelės duomenis, naudojamus "PivotTable" ir "PivotChart". Duomenų modelis vizualizuojamas kaip lentelių rinkinys laukų sąraše, o dažniausiai jo niekada nežinote.

Kad galėtumėte pradėti dirbti su duomenų modeliu, turite gauti tam tikrų duomenų. Norėdami tai padaryti, naudosime funkciją "Get & Transform" ("Power Query"),todėl galbūt norėsite grįžti ir peržiūrėti vaizdo įrašą arba vadovautis mūsų mokymo vadovu apie "Get & Transform" ir "Power Pivot".

Kur yra „Power Pivot“?

Excel 2016 & „Excel“, skirta „Microsoft 365“ – "Power Pivot" yra įtraukta į juostelę.
"Excel 2013" – "Power Pivot" yra Office Professional Plus "Excel 2013" leidimo dalis, tačiau nėra įgalinta pagal numatytuosius parametrus. Sužinokite daugiau apie "Power Pivot" papildinio, skirto "Excel 2013", paleidimą.
"Excel 2010 " – atsisiųskite "Power Pivot" papildinį, tada įdiekite "Power Pivot" papildinį.

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

Excel 2016 & „Excel“, skirta „Microsoft 365“ – gauti & transformaciją ("Power Query") buvo integruota su "Excel" skirtuke Duomenys.
"Excel 2013" – "Power Query" yra papildinys, įtrauktas į "Excel", tačiau jį reikia suaktyvinti. Eikite į Failų > parinktys > Papildiniai, tada srities apačioje esančiame išplečiamajame sąraše Tvarkyti pasirinkite COM papildiniai > Eiti. Patikrinkite "Microsoft" "Power Query", skirtą "Excel", tada Gerai, kad ją suaktyvintumėte. Į juostelę bus įtrauktas "Power Query" skirtukas.
"Excel 2010" – atsisiųskite ir įdiekite "Power Query" papildinį.. Suaktyvinus, į juostelę bus įtrauktas "Power Query" skirtukas.

Darbo pradžia

Pirmiausia turite gauti tam tikrų duomenų.

Excel 2016 ir „Excel“, skirta „Microsoft 365“ naudokite Duomenų > Gauti & Transformuoti duomenis > Gauti duomenis, kad importuotumėte duomenis iš įvairių išorinių duomenų šaltinių, pvz., teksto failo, "Excel" darbaknygės, svetainės, "Microsoft Access", "SQL Server" ar kitos sąryšinės duomenų bazės, kurioje yra kelios susijusios lentelės.

"Excel 2013" ir "Excel 2010" eikite į "Power Query" > Gauti išorinius duomenis ir pasirinkite duomenų šaltinį.
Bus rodomas „Excel“ raginimas pasirinkti lentelę. Jei norite gauti kelias lenteles iš to paties duomenų šaltinio, pažymėkite parinktį Įgalinti kelių lentelių pasirinkimą . Kai pasirenkate kelias lenteles, "Excel" automatiškai sukuria duomenų modelį.

Pastaba: Šiuose pavyzdžiuose naudojame "Excel" darbaknygę su išgalvota mokinio informacija apie pamokas ir įvertinimus. Galite atsisiųsti mūsų studentų duomenų modelio darbaknygės pavyzdį ir sekti. Taip pat galite atsisiųsti versiją su baigtu duomenų modeliu..
Pasirinkite vieną ar kelias lenteles, tada spustelėkite Įkelti.

Jei norite redaguoti šaltinio duomenis, galite pasirinkti parinktį Redaguoti . Daugiau informacijos žr.: Užklausų rengyklė ("Power Query") įvadas.

Dabar turite duomenų modelį, kuriame yra visos importuotos lentelės, ir jie bus rodomi "PivotTable" laukų sąraše.

Pastabos:

Modeliai paprastai kuriami netiesiogiai, kai į „Excel“ vienu metu importuojate dvi ar daugiau lentelių.
Modeliai sukuriami aiškiai, kai naudojate Power Pivot papildinį duomenims importuoti. Papildinio modelis pateikiamas kaip skirtukų maketas, panašus į "Excel", kur kiekviename skirtuke yra lentelės duomenų. Žr. Duomenų gavimas naudojant "Power Pivot" papildinį, kad sužinotumėte duomenų importavimo naudojant "SQL Server" duomenų bazę pagrindus.
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ų modelio dydį, ieškokite Atmintį taupaus duomenų modelio kūrimas naudojant "Excel" ir "Power Pivot".
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 > Valdyti. Jei matote darbalapio duomenis, tuomet modelis yra. Žr.: Sužinokite, kurie duomenų šaltiniai naudojami darbaknygės duomenų modelyje , kad sužinotumėte daugiau.

Lentelių ryšių kūrimas

Kitas veiksmas – sukurti ryšius tarp lentelių, kad galėtumėte gauti duomenis iš bet kurios iš jų. Kiekvienoje lentelėje turi būti pirminis raktas arba unikalus lauko identifikatorius, pvz., studento ID arba klasės numeris. Paprasčiausias būdas yra nuvilkti tuos laukus, kad juos sujungtumėte "Power Pivot" diagramos rodinyje.

Eikite į "Power Pivot" > valdyti.
Skirtuke Pagrindinis pasirinkite Diagramos rodinys.
Bus rodomos visos importuotos lentelės, todėl galite skirti šiek tiek laiko, kad pakeistumėte jų dydį, atsižvelgiant į tai, kiek laukų yra kiekvienoje lentelėje.
Tada vilkite pirminio rakto lauką iš vienos lentelės į kitą. Toliau pateiktas pavyzdys yra mūsų studentų 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 į lentelės Įvertinimai lauką Studento ID.
- tbl_Semesters | Semestro ID > tbl_Grades | Semestras
- tbl_Classes | Klasės numeris > tbl_Grades | Klasės numeris
Pastabos:
- Norint sukurti ryšį, laukų pavadinimai neprivalo būti tokie patys, 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.: Ryšiai tarp duomenų modelio lentelių .
- Jungtys tik rodo, kad yra ryšys tarp lentelių. Jie iš tikrųjų nerodys, kurie laukai yra susieti tarpusavyje. Norėdami peržiūrėti saitus, eikite į "Power Pivot" > valdyti > dizaino > ryšių > ryšių valdymas. Programoje "Excel" galite eiti į Duomenų > ryšiai.

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

"Excel" darbaknygėje gali būti tik vienas duomenų modelis, bet tame modelyje gali būti kelios lentelės, kurias galima pakartotinai naudoti visoje darbaknygėje. Į esamą duomenų modelį galite bet kada įtraukti daugiau lentelių.

Dalyje Power Pivoteikite į Valdyti.
Skirtuke Pagrindinis pasirinkite PivotTable.
Pasirinkite, kur norite padėti "PivotTable": naują darbalapį arba dabartinę vietą.
Spustelėkite Gerai ir "Excel" įtrauks tuščią "PivotTable" su dešinėje rodoma sritimi Laukų sąrašas.

Tada sukurkite "PivotTable" arba sukurkite "Pivot" diagramą. Jei jau sukūrėte ryšius tarp lentelių, galite naudoti bet kuriuos jų laukus "PivotTable". Jau sukūrėme ryšius studentų duomenų modelio darbaknygės pavyzdyje.

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

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

Pradėkite pažymėdami bet kurį duomenų langelį, kurį norite įtraukti į modelį. Tai gali būti bet koks duomenų diapazonas, tačiau duomenys, suformatuoti kaip "Excel" lentelė , yra tinkamiausi.
Duomenis įtraukite vienu iš toliau nurodytų būdų.
Spustelėkite Power Pivot > Įtraukti į duomenų modelį.
Spustelėkite Įterpimas > PivotTable ir dialogo lange Kurti „PivotTable“ pažymėkite Įtraukti šiuos duomenis į duomenų modelį.

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

Duomenų įtraukimas į Power Pivot lentelę

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