„Pivot by Data Model“ pranašumai

Kuriant suvestinę lentelę „Excel“, pačiame pirmame dialogo lange, kuriame mūsų prašoma nustatyti pradinį diapazoną ir pasirinkti vietą, kur įterpti suvestinę lentelę, apačioje yra nepastebimas, bet labai svarbus žymimasis langelis – Pridėkite šiuos duomenis prie duomenų modelio (Pridėkite šiuos duomenis prie duomenų modelio) ir, šiek tiek aukščiau, jungiklis Naudokite šios knygos duomenų modelį (Naudokite šios darbaknygės duomenų modelį):

Deja, daugelis vartotojų, kurie jau seniai yra susipažinę su pivot lentelėmis ir sėkmingai jas naudoja savo darbe, kartais nelabai supranta šių parinkčių prasmės ir niekada jomis nesinaudoja. Ir veltui. Galų gale, duomenų modelio suvestinės lentelės sukūrimas suteikia mums keletą labai svarbių pranašumų, palyginti su klasikine „Excel“ suvestinės lentele.

Tačiau prieš svarstydami šias „bandeles“ iš arti, pirmiausia supraskime, kas iš tikrųjų yra šis duomenų modelis?

Kas yra duomenų modelis

Duomenų modelis (sutrumpintai kaip MD arba DM = duomenų modelis) yra speciali sritis Excel faile, kurioje galite saugoti lentelių duomenis – vieną ar daugiau lentelių, susietų, jei pageidaujate, viena su kita. Tiesą sakant, tai yra nedidelė duomenų bazė (OLAP kubas), įterpta į „Excel“ darbaknygę. Palyginti su klasikiniu duomenų saugojimu įprastų (arba išmaniųjų) lentelių pavidalu pačiame „Excel“ lapuose, duomenų modelis turi keletą reikšmingų pranašumų:

• Lentelės gali būti iki 2 milijardai eilučių, o Excel lape telpa kiek daugiau nei 1 mln.
• Nepaisant milžiniško dydžio, tokių lentelių apdorojimas (filtravimas, rūšiavimas, skaičiavimai ant jų, pastato suvestinė ir kt.) yra atliekamas. labai greitai Daug greičiau nei pati Excel.
• Naudodamiesi modelio duomenimis, galite atlikti papildomus (jei norite, labai sudėtingus) skaičiavimus integruota DAX kalba.
  
• Visa informacija, įkelta į duomenų modelį, yra labai stipriai suspaustas naudojant specialų įmontuotą archyvatorių ir gana saikingai padidina pradinio Excel failo dydį.
  

Modelis valdomas ir apskaičiuojamas naudojant specialų priedą, integruotą į Microsoft Excel – „PowerPivot“apie kurią jau rašiau. Norėdami jį įjungti, skirtuke ryškalas spragtelėjimas COM priedai (Kūrėjas – COM priedai) ir pažymėkite atitinkamą langelį:

Jei skirtukai ryškalas (Programuotojas)ant juostelės jo nesimato, galima perjungti Failas – Parinktys – Juostos sąranka (Failas – Parinktys – Tinkinti juostelę). Jei aukščiau esančiame lange COM priedų sąraše neturite Power Pivot, vadinasi, jis neįtrauktas į jūsų Microsoft Office versiją 🙁

Pasirodžiusiame skirtuke Power Pivot bus didelis šviesiai žalias mygtukas valdymas (Tvarkyti), kurį spustelėjus atsidarys „Excel“ viršuje esantis „Power Pivot“ langas, kuriame pamatysime dabartinės knygos duomenų modelio turinį:

Svarbi pastaba: „Excel“ darbaknygėje gali būti tik vienas duomenų modelis.

Įkelkite lenteles į duomenų modelį

Norėdami įkelti duomenis į modelį, pirmiausia paverčiame lentelę dinamišku „išmaniuoju“ sparčiuoju klavišu "Ctrl"+T ir suteikite jam draugišką pavadinimą skirtuke Konstruktorius (Dizainas). Tai būtinas žingsnis.

Tada galite pasirinkti vieną iš trijų būdų:

• Paspausk mygtuką Pridėti prie modelio (Pridėti prie duomenų modelio) kortelė „PowerPivot“ kortelė Pagrindinis (Namai).
  
• Komandų pasirinkimas Įterpimas – PivotTable (Įterpti – Suvestinė lentelė) ir įjunkite žymimąjį laukelį Pridėkite šiuos duomenis prie duomenų modelio (Pridėkite šiuos duomenis prie duomenų modelio). Šiuo atveju pagal į Modelį įkeltus duomenis iš karto sukuriama ir suvestinė lentelė.
  
• Skirtuke Išplėstinė Duomenys (Data) spustelėkite mygtuką Iš lentelės/diapazono (Iš lentelės / diapazono)norėdami įkelti lentelę į Power Query redaktorių. Šis kelias yra ilgiausias, tačiau, jei pageidaujama, čia galima atlikti papildomą duomenų valymą, redagavimą ir visokias transformacijas, kuriose Power Query yra labai stipri.

  Tada sušukuoti duomenys komanda įkeliami į Modelį Pagrindinis puslapis – Uždaryti ir įkelti – Uždaryti ir įkelti… (Pagrindinis - Uždaryti ir įkelti - Uždaryti ir įkelti į...). Atsidariusiame lange pasirinkite parinktį Tiesiog sukurkite ryšį (Tik sukurti ryšį) ir, svarbiausia, pažymėkite varnelę Pridėkite šiuos duomenis prie duomenų modelio (Pridėkite šiuos duomenis prie duomenų modelio).
  

Sudarome duomenų modelio santrauką

Norėdami sukurti suvestinį duomenų modelį, galite naudoti bet kurį iš trijų būdų:

• spauskite mygtuką suvestinė lentelė (Suvestinės lentelės) Power Pivot lange.
• Pasirinkite komandas Excel Įterpimas – PivotTable ir perjunkite į režimą Naudokite šios knygos duomenų modelį (Įterpti – Suvestinė lentelė – naudokite šios darbaknygės duomenų modelį).
• Komandų pasirinkimas Įterpimas – PivotTable (Įterpti – Suvestinė lentelė) ir įjunkite žymimąjį laukelį Pridėkite šiuos duomenis prie duomenų modelio (Pridėkite šiuos duomenis prie duomenų modelio). Dabartinė „išmanioji“ lentelė bus įkelta į modelį ir bus sukurta viso modelio suvestinė.

Dabar, kai išsiaiškinome, kaip įkelti duomenis į duomenų modelį ir sudaryti jo santrauką, panagrinėkime jo teikiamą naudą ir pranašumus.

1 pranašumas: ryšiai tarp lentelių nenaudojant formulių

Įprastą suvestinę galima sudaryti tik naudojant duomenis iš vienos šaltinio lentelės. Jei turite keletą iš jų, pavyzdžiui, pardavimai, kainoraštis, klientų katalogas, sutarčių registras ir kt., pirmiausia turėsite surinkti duomenis iš visų lentelių į vieną naudodami tokias funkcijas kaip VLOOKUP (ŽIŪRĖTI), INDEKSAS (INDEKSAS), DAUGIAU ATSKIRTA (MATCH), SUMMESLIMN (SUMIFS) ir panašiai. Tai ilgas, varginantis ir skatina jūsų „Excel“ į „mintį“ su dideliu duomenų kiekiu.

Duomenų modelio santraukos atveju viskas yra daug paprasčiau. Pakanka vieną kartą „Power Pivot“ lange nustatyti ryšius tarp lentelių – ir viskas. Norėdami tai padaryti, skirtuke „PowerPivot“ Paspausk mygtuką valdymas (Tvarkyti) o tada pasirodžiusiame lange – mygtukas Diagramos vaizdas (Diagramos vaizdas). Belieka vilkti bendruosius (raktų) stulpelių pavadinimus (laukus) tarp lentelių, kad būtų sukurtos nuorodos:

Po to duomenų modelio suvestinėje į suvestinės sritį (eilutes, stulpelius, filtrus, reikšmes) galite įmesti bet kokius laukus iš bet kokių susijusių lentelių – viskas bus susieta ir apskaičiuojama automatiškai:

2 pranašumas: suskaičiuokite unikalias reikšmes

Įprasta suvestinė lentelė suteikia mums galimybę pasirinkti vieną iš kelių integruotų skaičiavimo funkcijų: suma, vidurkis, skaičius, minimumas, didžiausias ir tt Duomenų modelio suvestinėje į šį standartinį sąrašą įtraukta labai naudinga funkcija, skirta skaičiuoti unikalių (nepasikartojančių reikšmių) skaičius. Jos pagalba, pavyzdžiui, nesunkiai suskaičiuosite, kiek unikalių prekių (asortimento) parduodame kiekviename mieste.

Dešiniuoju pelės mygtuku spustelėkite lauką - komanda Vertės lauko parinktys ir skirtuke Operacija Pasirinkti Įvairių elementų skaičius (Skirtas skaičius):

3 pranašumas: tinkintos DAX formulės

Kartais jūs turite atlikti įvairius papildomus skaičiavimus suvestinėse lentelėse. Įprastose suvestinėse tai daroma naudojant apskaičiuotus laukus ir objektus, o duomenų modelio suvestinėje naudojami matai specialia DAX kalba (DAX = Data Analysis Expressions).

Norėdami sukurti matą, pasirinkite skirtuke „PowerPivot“ Komanda Priemonės – sukurkite priemonę (Priemonės – nauja priemonė) arba tiesiog dešiniuoju pelės mygtuku spustelėkite lentelę „Pivot Fields“ sąraše ir pasirinkite Pridėkite matą (Pridėti matą) kontekstiniame meniu:

Atsidariusiame lange nustatykite:

• Lentelės pavadinimaskur bus saugomas sukurtas matas.
• Mato pavadinimas – bet koks jums suprantamas naujo lauko pavadinimas.
• Aprašymas – neprivaloma.
• Formulė – svarbiausia, nes čia arba įvedame rankiniu būdu, arba paspaudžiame mygtuką fx ir iš sąrašo pasirinkite DAX funkciją, kuri turėtų apskaičiuoti rezultatą, kai tada įmessime savo matą į reikšmių sritį.
• Apatinėje lango dalyje galite iš karto nustatyti sąrašo mato skaičių formatą Kategorija.
  

DAX kalbą ne visada lengva suprasti, nes ji veikia ne su atskiromis reikšmėmis, o su visais stulpeliais ir lentelėmis, ty reikalauja tam tikro mąstymo pertvarkos po klasikinių Excel formulių. Tačiau tai verta, nes sunku pervertinti jo galimybes apdoroti didelius duomenų kiekius.

4 pranašumas: tinkintos laukų hierarchijos

Dažnai kurdami standartines ataskaitas turite įmesti tas pačias laukų kombinacijas į suvestinės lenteles tam tikra seka, pvz. Metai-ketvirtis-mėnuo-dienaarba Kategorija-Produktasarba Šalis-Miestas-Klientas tt Duomenų modelio suvestinėje šią problemą nesunku išspręsti susikūrus savo hierarchijos - pasirinktiniai lauko rinkiniai.

Power Pivot lange mygtuku perjunkite į diagramos režimą Diagramos vaizdas kortelė Pagrindinis (Pagrindinis – diagramos rodinys), pasirinkite su "Ctrl" norimus laukus ir dešiniuoju pelės mygtuku spustelėkite juos. Kontekstiniame meniu bus komanda Sukurkite hierarchiją (Sukurti hierarchiją):

Sukurtą hierarchiją galima pervardyti ir nuvilkti į ją su pele reikiamus laukus, kad vėliau vienu judesiu juos būtų galima įmesti į suvestinę:

5 pranašumas: pasirinktiniai trafaretai

Tęsdami ankstesnės pastraipos idėją, duomenų modelio santraukoje taip pat galite sukurti savo kiekvieno lauko elementų rinkinius. Pavyzdžiui, iš viso miestų sąrašo galite lengvai sudaryti tik tų, kurie yra jūsų atsakomybės srityje, rinkinį. Arba į specialų rinkinį surinkite tik savo klientus, savo prekes ir pan.

Norėdami tai padaryti, skirtuke Suvestinės lentelės analizė išskleidžiamajame sąraše Laukai, elementai ir rinkiniai yra atitinkamos komandos (Analizuoti – Fields, Ielementai ir rinkiniai – sukurkite rinkinį pagal eilučių / stulpelių elementus):

Atsidariusiame lange galite pasirinktinai pašalinti, pridėti arba pakeisti bet kokių elementų padėtį ir išsaugoti gautą rinkinį nauju pavadinimu:

Visi sukurti rinkiniai bus rodomi „PivotTable Fields“ skydelyje atskirame aplanke, iš kurio juos galima laisvai vilkti į bet kurios naujos „PivotTable“ eilučių ir stulpelių sritis:

6 pranašumas: pasirinktinai slėpkite lenteles ir stulpelius

Nors tai nedidelis, bet kai kuriais atvejais labai malonus privalumas. Dešiniuoju pelės mygtuku spustelėję lauko pavadinimą arba lentelės skirtuką Power Pivot lange, galite pasirinkti komandą Slėpti iš Kliento įrankių rinkinio (Paslėpti nuo kliento įrankių):

Paslėptas stulpelis arba lentelė išnyks iš „PivotTable“ laukų sąrašo srities. Tai labai patogu, jei jums reikia paslėpti nuo vartotojo kai kuriuos pagalbinius stulpelius (pavyzdžiui, apskaičiuotus arba stulpelius su pagrindinėmis reikšmėmis ryšiams kurti) ar net visas lenteles.

Nauda 7. Išplėstinis gręžimas

Jei įprastoje suvestinės lentelės verčių srityje dukart spustelėsite bet kurį langelį, tada „Excel“ atskirame lape parodys šaltinio duomenų fragmento, kuris buvo įtrauktas į šio langelio skaičiavimą, kopiją. Tai labai patogus dalykas, oficialiai vadinamas „Drill-down“ (dažniausiai sakoma „nepavyko“).

Duomenų modelio suvestinėje šis patogus įrankis veikia subtiliau. Atsistoję ant bet kurios ląstelės su mus dominančiu rezultatu, galite spustelėti piktogramą su didinamuoju stiklu, kuris pasirodo šalia jo (ji vadinama Išreikškite tendencijas) ir bet kurioje susijusioje lentelėje pasirinkite bet kurį jus dominantį lauką:

Po to dabartinė reikšmė (modelis = Explorer) pateks į filtro sritį, o santrauką sudarys biurai:

Žinoma, tokią procedūrą galima kartoti daug kartų, nuosekliai gilinantis į savo duomenis jus dominančia kryptimi.

8 pranašumas: konvertuokite „Pivot“ į kubo funkcijas

Jei pasirinksite bet kurį langelį duomenų modelio suvestinėje ir tada pasirinkite skirtuke Suvestinės lentelės analizė Komanda OLAP įrankiai – konvertuoti į formules (Analizė – OLAP įrankiai – konvertuoti į formules), tada visa suvestinė bus automatiškai konvertuojama į formules. Dabar laukų reikšmės eilutės-stulpelio srityje ir rezultatai reikšmių srityje bus gauti iš duomenų modelio naudojant specialias kubo funkcijas: CUBEVALUE ir CUBEMEMBER:

Techniškai tai reiškia, kad dabar mes susiduriame ne su santrauka, o su keliais langeliais su formulėmis, ty su savo ataskaita galime lengvai atlikti bet kokias transformacijas, kurių nėra suvestinėje, pavyzdžiui, į vidurį įterpti naujas eilutes ar stulpelius. ataskaitoje, atlikite papildomus skaičiavimus suvestinėje, sutvarkykite juos norimu būdu ir pan.

Tuo pačiu ryšys su pirminiais duomenimis, žinoma, išlieka ir ateityje šios formulės bus atnaujinamos pasikeitus šaltiniams. Grožis!

• Plano ir faktų analizė suvestinėje lentelėje su Power Pivot ir Power Query
• Suvestinė lentelė su kelių eilučių antrašte
• Sukurkite duomenų bazę programoje „Excel“ naudodami „Power Pivot“.