Përfitimet e Pivot sipas modelit të të dhënave

Kur ndërtoni një tabelë kryesore në Excel, në kutinë e parë të dialogut, ku na kërkohet të vendosim diapazonin fillestar dhe të zgjedhim një vend për të futur tabelën kryesore, ka një kuti kontrolli që nuk bie në sy, por shumë e rëndësishme më poshtë - Shtoni këto të dhëna në Modelin e të Dhënave (Shto këto të dhëna në modelin e të dhënave) dhe, pak më lart, çelësi Përdorni modelin e të dhënave të këtij libri (Përdor modelin e të dhënave të këtij libri pune):

Fatkeqësisht, shumë përdorues që janë njohur me tabelat kryesore për një kohë të gjatë dhe i përdorin ato me sukses në punën e tyre, ndonjëherë nuk e kuptojnë vërtet kuptimin e këtyre opsioneve dhe nuk i përdorin kurrë ato. Dhe më kot. Në fund të fundit, krijimi i një tabele përmbledhëse për Modelin e të Dhënave na jep disa avantazhe shumë të rëndësishme në krahasim me tabelën kryesore të Excel-it.

Sidoqoftë, përpara se t'i shqyrtojmë nga afër këto "simite", le të kuptojmë së pari se çfarë është, në fakt, ky Model i të Dhënave?

Çfarë është një model i të dhënave

Modeli i të dhënave (shkurtuar si MD ose DM = Modeli i të Dhënave) është një zonë e veçantë brenda një skedari Excel ku mund të ruani të dhëna tabelare - një ose më shumë tabela të lidhura, nëse dëshironi, me njëra-tjetrën. Në fakt, kjo është një bazë e vogël e të dhënave (kub OLAP) i ngulitur brenda një libri pune Excel. Krahasuar me ruajtjen klasike të të dhënave në formën e tabelave të rregullta (ose inteligjente) në fletët e vetë Excel-it, Modeli i të Dhënave ka disa përparësi domethënëse:

• Tabelat mund të jenë deri në 2 miliardë rreshta, dhe një fletë Excel mund të përshtatet pak më shumë se 1 milion.
• Pavarësisht përmasave gjigante, përpunimi i tabelave të tilla (filtrim, renditje, llogaritje mbi to, përmbledhje ndërtimi etj.) kryhet. shumë shpejt Shumë më shpejt se vetë Excel.
• Me të dhënat në Model, mund të kryeni llogaritje shtesë (nëse dëshironi, shumë komplekse) duke përdorur gjuhë e integruar DAX.
  
• I gjithë informacioni i ngarkuar në Modelin e të Dhënave është shumë i ngjeshur fort duke përdorur një arkivues të veçantë të integruar dhe rrit në mënyrë të moderuar madhësinë e skedarit origjinal Excel.
  

Modeli menaxhohet dhe llogaritet nga një shtesë e veçantë e integruar në Microsoft Excel - powerpivotpër të cilën kam shkruar tashmë. Për ta aktivizuar, në skedën zhvillues kliko Shtesat COM (Zhvilluesi - Shtesat COM) dhe kontrolloni kutinë e duhur:

Nëse skedat zhvillues (Zhvilluesi)nuk mund ta shihni në shirit, mund ta ndizni Skedari – Opsionet – Konfigurimi i shiritit (Skedar - Opsione - Personalizo shiritin). Nëse në dritaren e treguar më sipër në listën e shtesave COM nuk keni Power Pivot, atëherë nuk përfshihet në versionin tuaj të Microsoft Office 🙁

Në skedën Power Pivot që shfaqet, do të ketë një buton të madh jeshil të lehtë drejtuesit (Menaxho), duke klikuar mbi të cilën do të hapet dritarja Power Pivot në krye të Excel, ku do të shohim përmbajtjen e Modelit të të Dhënave të librit aktual:

Një shënim i rëndësishëm gjatë rrugës: një libër pune në Excel mund të përmbajë vetëm një model të të dhënave.

Ngarko tabelat në Modelin e të Dhënave

Për të ngarkuar të dhënat në Model, fillimisht ne e kthejmë tabelën në një shkurtore dinamike "të zgjuar" të tastierës Ctrl+T dhe i jepni një emër miqësor në skedë konstruktor (Dizajn). Ky është një hap i kërkuar.

Pastaj mund të përdorni ndonjë nga tre metodat për të zgjedhur:

• Shtyp butonin Shto në Model (Shto në modelin e të dhënave) tab powerpivot tab Fillimi (Shtëpi).
  
• Zgjedhja e ekipeve Insert – PivotTable (Fut - Tabela kryesore) dhe aktivizoni kutinë e zgjedhjes Shtoni këto të dhëna në Modelin e të Dhënave (Shto këto të dhëna në Modelin e të Dhënave). Në këtë rast, sipas të dhënave të ngarkuara në Model, ndërtohet menjëherë një tabelë kryesore.
  
• Në skedën Avancuar Data (Data) klikoni në butonin Nga tabela/gama (Nga tabela/gama)për të ngarkuar tabelën tonë në redaktorin e Power Query. Kjo rrugë është më e gjata, por, nëse dëshironi, këtu mund të kryeni pastrim shtesë të të dhënave, redaktim dhe të gjitha llojet e transformimeve, në të cilat Power Query është shumë i fortë.

  Më pas të dhënat e krehura ngarkohen në Model me anë të komandës Faqja kryesore — Mbyll dhe ngarko — Mbylle dhe ngarko në… (Shtëpia - Mbyll&Ngarko - Mbylle&Ngarko për…). Në dritaren që hapet, zgjidhni opsionin Thjesht krijoni një lidhje (Vetëm krijoni lidhje) dhe, më e rëndësishmja, vendosni një tik-tak Shtoni këto të dhëna në Modelin e të Dhënave (Shto këto të dhëna në Modelin e të Dhënave).
  

Ne ndërtojmë një përmbledhje të Modelit të të Dhënave

Për të ndërtuar një model përmbledhës të të dhënave, mund të përdorni një nga tre qasjet:

• Shtyp butonin tabelë përmbledhëse (Tabela strumbullar) në dritaren Power Pivot.
• Zgjidhni komandat në Excel Insert – PivotTable dhe kaloni në modalitet Përdorni modelin e të dhënave të këtij libri (Fut — Tabela kryesore — Përdor modelin e të dhënave të këtij libri pune).
• Zgjedhja e ekipeve Insert – PivotTable (Fut - Tabela kryesore) dhe aktivizoni kutinë e zgjedhjes Shtoni këto të dhëna në Modelin e të Dhënave (Shto këto të dhëna në Modelin e të Dhënave). Tabela aktuale "e zgjuar" do të ngarkohet në Model dhe një tabelë përmbledhëse do të ndërtohet për të gjithë Modelin.

Tani që kemi kuptuar se si të ngarkojmë të dhënat në Modelin e të Dhënave dhe të ndërtojmë një përmbledhje mbi të, le të shqyrtojmë përfitimet dhe avantazhet që na jep kjo.

Përfitimi 1: Marrëdhëniet midis tabelave pa përdorur formula

Një përmbledhje e rregullt mund të ndërtohet vetëm duke përdorur të dhëna nga një tabelë burimore. Nëse keni disa prej tyre, për shembull, shitjet, listën e çmimeve, drejtorinë e klientëve, regjistrin e kontratave, etj., atëherë së pari do t'ju duhet të mbledhni të dhëna nga të gjitha tabelat në një duke përdorur funksione të tilla si VLOOKUP (VLOOKUP), INDEKSI (INDEX), ME TE EKSPOZUAR (ndeshje), SUMMESLIMN (SUMIFS) dhe të ngjashme. Kjo është e gjatë, e lodhshme dhe e shtyn Excel-in tuaj në një "mendim" me një sasi të madhe të dhënash.

Në rastin e një përmbledhjeje të Modelit të të Dhënave, gjithçka është shumë më e thjeshtë. Mjafton të konfiguroni marrëdhëniet midis tabelave një herë në dritaren e Power Pivot – dhe është bërë. Për ta bërë këtë, në skedën powerpivot Shtyp butonin drejtuesit (Menaxho) dhe më pas në dritaren që shfaqet – butoni Pamja e grafikut (Pamje e diagramit). Mbetet të tërhiqni emrat e kolonave (fushat) të përbashkëta (çelësat) midis tabelave për të krijuar lidhje:

Pas kësaj, në përmbledhjen për Modelin e të Dhënave, mund të hidhni në zonën e përmbledhjes (rreshta, kolona, ​​filtra, vlera) çdo fushë nga çdo tabelë përkatëse - gjithçka do të lidhet dhe do të llogaritet automatikisht:

Përfitimi 2: Numëroni vlerat unike

Një tabelë e rregullt rrotulluese na jep mundësinë të zgjedhim një nga disa funksione llogaritëse të integruara: shuma, mesatarja, numërimi, minimumi, maksimumi, etj. Në përmbledhjen e Modelit të të Dhënave, kësaj liste standarde i shtohet një funksion shumë i dobishëm për të numëruar numri i vlerave unike (që nuk përsëriten). Me ndihmën e tij, për shembull, mund të numëroni lehtësisht numrin e artikujve unikë të mallrave (gamë) që ne shesim në çdo qytet.

Klikoni me të djathtën në fushë - komandë Opsionet e fushës së vlerës dhe në skedën operacion Zgjedh Numri i elementeve të ndryshëm (Numërim i veçantë):

Përfitimi 3: Formulat e personalizuara DAX

Ndonjëherë ju duhet të kryeni llogaritje të ndryshme shtesë në tabelat kryesore. Në përmbledhjet e rregullta, kjo bëhet duke përdorur fusha dhe objekte të llogaritura, ndërsa përmbledhja e modelit të të dhënave përdor masa në një gjuhë të veçantë DAX (DAX = Shprehjet e Analizës së të Dhënave).

Për të krijuar një masë, zgjidhni në skedën powerpivot Komandë Masat – Krijo masë (Masat - Masa e re) ose thjesht klikoni me të djathtën mbi tabelën në listën Pivot Fields dhe zgjidhni Shtoni masën (Shto masën) në menunë e kontekstit:

Në dritaren që hapet, vendosni:

• Emri i tabelësku do të ruhet masa e krijuar.
• Emri i masës – çdo emër që kuptoni për fushën e re.
• Përshkrim – opsionale.
• Formulë – gjëja më e rëndësishme, sepse këtu ose futemi manualisht, ose klikojmë në butonin fx dhe zgjidhni një funksion DAX nga lista, i cili duhet të llogarisë rezultatin kur më pas hedhim masën tonë në zonën e Vlerave.
• Në pjesën e poshtme të dritares, mund të vendosni menjëherë formatin e numrave për masën në listë Kategoria.
  

Gjuha DAX nuk është gjithmonë e lehtë për t'u kuptuar sepse funksionon jo me vlera individuale, por me kolona dhe tabela të tëra, dmth kërkon një ristrukturim të të menduarit sipas formulave klasike të Excel-it. Sidoqoftë, ia vlen, sepse fuqia e aftësive të saj në përpunimin e sasive të mëdha të të dhënave është e vështirë të mbivlerësohet.

Përfitimi 4: Hierarkitë e personalizuara të fushave

Shpesh, kur krijoni raporte standarde, duhet të hidhni të njëjtat kombinime fushash në tabelat kryesore në një sekuencë të caktuar, për shembull Viti-Treemuaj-Ditëose Kategoria-Produktose Vend-Qytet-Klient etj. Në përmbledhjen e Modelit të të Dhënave, ky problem zgjidhet lehtësisht duke krijuar tuajin hierarkitë - grupe të personalizuara të fushave.

Në dritaren Power Pivot, kaloni në modalitetin e grafikut me butonin Pamja e grafikut tab Fillimi (Faqja kryesore - Pamja e diagramit), zgjidhni me Ctrl fushat e dëshiruara dhe kliko me të djathtën mbi to. Menyja e kontekstit do të përmbajë komandën Krijo hierarki (Krijo hierarki):

Hierarkia e krijuar mund të riemërohet dhe të tërhiqet në të me miun fushat e kërkuara, në mënyrë që më vonë në një lëvizje të mund të hidhen në përmbledhje:

Përfitimi 5: Stencilat me porosi

Duke vazhduar idenë e paragrafit të mëparshëm, në përmbledhjen e Modelit të të Dhënave, ju gjithashtu mund të krijoni grupet tuaja të elementeve për secilën fushë. Për shembull, nga e gjithë lista e qyteteve, lehtë mund të bëni një grup vetëm të atyre që janë në zonën tuaj të përgjegjësisë. Ose mblidhni vetëm klientët tuaj, mallrat tuaja, etj. në një grup të veçantë.

Për ta bërë këtë, në skedën Analiza e tabelës strumbullare në listën rënëse Fushat, artikujt dhe grupet ka komanda përkatëse (Analizo - Fushat, Items & Sets — Krijo grup bazuar në artikujt e rreshtit/kolonave):

Në dritaren që hapet, mund të hiqni, shtoni ose ndryshoni në mënyrë selektive pozicionin e çdo elementi dhe të ruani grupin që rezulton me një emër të ri:

Të gjitha grupet e krijuara do të shfaqen në panelin PivotTable Fields në një dosje të veçantë, nga ku mund të tërhiqen lirisht në zonat e rreshtave dhe kolonave të çdo Table të re Pivot:

Përfitimi 6: Fshihni në mënyrë selektive tabelat dhe kolonat

Edhe pse ky është një avantazh i vogël, por shumë i këndshëm në disa raste. Duke klikuar me të djathtën në emrin e fushës ose në skedën e tabelës në dritaren e Power Pivot, mund të zgjidhni komandën Fshih nga Klienti i veglave (Fshih nga Veglat e Klientit):

Kolona ose tabela e fshehur do të zhduket nga paneli i Listës së Fushave të PivotTable. Është shumë i përshtatshëm nëse duhet të fshehni nga përdoruesi disa kolona ndihmëse (për shembull, të llogaritura ose kolona me vlera kryesore për krijimin e marrëdhënieve) ose edhe tabela të tëra.

Përfitimi 7. Stërvitje e avancuar

Nëse klikoni dy herë në çdo qelizë në zonën e vlerave në një tabelë të zakonshme kryesore, atëherë Excel shfaq në një fletë të veçantë një kopje të fragmentit të të dhënave burimore që ishte përfshirë në llogaritjen e kësaj qelize. Kjo është një gjë shumë e dobishme, e quajtur zyrtarisht Drill-down (nëse zakonisht thonë "dështon").

Në përmbledhjen e Modelit të të Dhënave, ky mjet i dobishëm funksionon më delikate. Duke qëndruar në çdo qelizë me rezultatin që na intereson, mund të klikoni në ikonën me një xham zmadhues që shfaqet pranë saj (quhet Trendet Express) dhe më pas zgjidhni çdo fushë që ju intereson në çdo tabelë përkatëse:

Pas kësaj, vlera aktuale (Model = Explorer) do të shkojë në zonën e filtrit dhe përmbledhja do të ndërtohet nga zyrat:

Sigurisht, një procedurë e tillë mund të përsëritet shumë herë, duke u zhytur vazhdimisht në të dhënat tuaja në drejtimin që ju intereson.

Përfitimi 8: Konvertoni Funksionet e Pivot në Kub

Nëse zgjidhni ndonjë qelizë në përmbledhjen për Modelin e të Dhënave dhe më pas zgjidhni në skedën Analiza e tabelës strumbullare Komandë Mjetet OLAP - Konvertoni në formula (Analizo - Mjetet OLAP - Konverto në formula), atëherë e gjithë përmbledhja do të konvertohet automatikisht në formula. Tani vlerat e fushës në zonën rresht-kolona dhe rezultatet në zonën e vlerave do të merren nga Modeli i të Dhënave duke përdorur funksionet e veçanta të kubit: CUBEVALUE dhe CUBEMEMBER:

Teknikisht, kjo do të thotë që tani nuk kemi të bëjmë me një përmbledhje, por me disa qeliza me formula, dmth. ne mund të bëjmë lehtësisht çdo transformim me raportin tonë që nuk është i disponueshëm në përmbledhje, për shembull, të fusim rreshta ose kolona të reja në mes. të raportit, bëni ndonjë përllogaritje shtesë brenda përmbledhjes, rregulloni ato në çdo mënyrë të dëshiruar, etj.

Në të njëjtën kohë, lidhja me të dhënat burimore, natyrisht, mbetet dhe në të ardhmen këto formula do të përditësohen kur të ndryshojnë burimet. Bukuria!

• Analiza plan-fakt në një tabelë kryesore me Power Pivot dhe Power Query
• Tabela rrotulluese me kokën me shumë rreshta
• Krijoni një bazë të dhënash në Excel duke përdorur Power Pivot