Voordele van Pivot by Data Model

Wanneer ons 'n spiltabel in Excel bou, in die heel eerste dialoogkassie, waar ons gevra word om die aanvanklike reeks te stel en 'n plek te kies om die spiltabel in te voeg, is daar 'n onopvallende maar baie belangrike merkblokkie hieronder - Voeg hierdie data by die Data Model (Voeg hierdie data by na datamodel) en, 'n bietjie hoër, die skakelaar Gebruik hierdie boek se datamodel (Gebruik Data Model van hierdie werkboek):

Ongelukkig verstaan ​​baie gebruikers wat al lankal met spilpunttabelle vertroud is en dit suksesvol in hul werk gebruik, soms nie regtig die betekenis van hierdie opsies nie en gebruik dit nooit. En tevergeefs. Die skep van 'n spiltabel vir die datamodel gee ons immers verskeie baie belangrike voordele in vergelyking met die klassieke Excel-spiltabel.

Maar, voordat ons hierdie "broodjies" van nader beskou, laat ons eers verstaan ​​wat, in werklikheid, hierdie Data Model is?

Wat is 'n datamodel

Datamodel (afgekort as MD of DM = Data Model) is 'n spesiale area binne 'n Excel-lêer waar jy tabeldata kan stoor – een of meer tabelle wat, indien verlang, aan mekaar gekoppel is. Trouens, dit is 'n klein databasis (OLAP-kubus) wat in 'n Excel-werkboek ingebed is. In vergelyking met die klassieke berging van data in die vorm van gereelde (of slim) tabelle op velle van Excel self, het die Data Model verskeie belangrike voordele:

• Tabelle kan tot 2 miljard lyne, en 'n Excel-blad kan 'n bietjie meer as 1 miljoen pas.
• Ten spyte van die reusagtige grootte, word die verwerking van sulke tabelle (filtrering, sortering, berekeninge daarop, bou-opsomming, ens.) uitgevoer baie vinnig Baie vinniger as Excel self.
• Met die data in die Model kan jy addisionele (indien verlang, baie komplekse) berekeninge uitvoer deur gebruik te maak van ingeboude DAX-taal.
  
• Alle inligting wat in die Data Model gelaai word, is baie sterk saamgepers met behulp van 'n spesiale ingeboude argiverer en verhoog die grootte van die oorspronklike Excel-lêer redelik matig.
  

Die model word bestuur en bereken deur 'n spesiale byvoeging wat in Microsoft Excel ingebou is - kragspilwaaroor ek reeds geskryf het. Om dit te aktiveer, op die blad ontwikkelaar kliek COM-byvoegings (Ontwikkelaar - COM-byvoegings) en merk die toepaslike blokkie:

As oortjies ontwikkelaar (Ontwikkelaar)jy kan dit nie op die lint sien nie, jy kan dit deur aanskakel Lêer – Opsies – Lintopstelling (Lêer - Opsies - Pas lint aan). As jy nie Power Pivot het in die venster wat hierbo in die lys van COM-byvoegings gewys word nie, is dit nie by jou weergawe van Microsoft Office ingesluit nie 🙁

Op die Power Pivot-oortjie wat verskyn, sal daar 'n groot liggroen knoppie wees bestuur (Bestuur), deur daarop te klik, sal die Power Pivot-venster bo-op Excel oopmaak, waar ons die inhoud van die datamodel van die huidige boek sal sien:

'n Belangrike nota langs die pad: 'n Excel-werkboek kan net een Data Model bevat.

Laai tabelle in die datamodel

Om data in die model te laai, verander ons eers die tabel in 'n dinamiese "slim" sleutelbordkortpad Ctrl+T en gee dit 'n vriendelike naam op die blad konstruktor (Ontwerp). Dit is 'n vereiste stap.

Dan kan jy enige van die drie metodes gebruik om van te kies:

• druk die knoppie Voeg by model (Voeg by datamodel) tab kragspil tab What is This (Tuis).
  
• Die keuse van spanne Voeg in – draaitabel (Voeg in – Spiltabel) en skakel die merkblokkie aan Voeg hierdie data by die Data Model (Voeg hierdie data by Data Model). In hierdie geval, volgens die data wat in die Model gelaai is, word 'n spiltabel ook onmiddellik gebou.
  
• Op die oortjie Gevorderd data (Datum) klik op die knoppie Van tabel/reeks (Vanaf tabel/reeks)om ons tabel in die Power Query-redigeerder te laai. Hierdie pad is die langste, maar as jy wil, kan jy hier bykomende data skoonmaak, redigering en allerhande transformasies uitvoer, waarin Power Query baie sterk is.

  Dan word die gekamde data na die Model opgelaai deur die opdrag Tuis — Maak toe en laai — Maak toe en laai in... (Tuis — Maak toe en laai — Maak toe en laai na...). Kies die opsie in die venster wat oopmaak Skep net 'n verbinding (Skep net verbinding) en, die belangrikste, maak 'n regmerkie Voeg hierdie data by die Data Model (Voeg hierdie data by Data Model).
  

Ons bou 'n opsomming van die Data Model

Om 'n opsommingsdatamodel te bou, kan jy enige van drie benaderings gebruik:

• Druk knoppie opsommingstabel (Swaaitabel) in die Power Pivot-venster.
• Kies opdragte in Excel Voeg in – draaitabel en skakel oor na modus Gebruik hierdie boek se datamodel (Voeg in — Spiltabel — Gebruik hierdie werkboek se datamodel).
• Die keuse van spanne Voeg in – draaitabel (Voeg in – Spiltabel) en skakel die merkblokkie aan Voeg hierdie data by die Data Model (Voeg hierdie data by Data Model). Die huidige "slim" tabel sal in die Model gelaai word en 'n opsommingstabel sal vir die hele Model gebou word.

Noudat ons uitgevind het hoe om data in die datamodel te laai en 'n opsomming daaroor te bou, kom ons ondersoek die voordele en voordele wat dit vir ons bied.

Voordeel 1: Verwantskappe tussen tabelle sonder om formules te gebruik

'n Gereelde opsomming kan slegs gebou word deur data uit een brontabel te gebruik. As jy verskeie van hulle het, byvoorbeeld verkope, pryslys, kliëntegids, register van kontrakte, ens., dan sal jy eers data van alle tabelle in een moet versamel met behulp van funksies soos VLOOKUP (VERSOEKING), INDEKS (INDEKS), MEER BLOOTSTELLING (MATCH), SUMMESLIMN (SUMIFS) en dies meer. Dit is lank, vervelig en dryf jou Excel in 'n "gedagte" met 'n groot hoeveelheid data.

In die geval van 'n opsomming van die Data Model is alles baie eenvoudiger. Dit is genoeg om verhoudings tussen tabelle een keer in die Power Pivot-venster op te stel – en dit is klaar. Om dit te doen, op die blad kragspil druk die knoppie bestuur (Bestuur) en dan in die venster wat verskyn – die knoppie Grafiese aansig (Diagramaansig). Dit bly om algemene (sleutel) kolomname (velde) tussen tabelle te sleep om skakels te skep:

Daarna, in die opsomming vir die datamodel, kan u enige velde van enige verwante tabelle in die opsommingsarea (rye, kolomme, filters, waardes) gooi - alles sal outomaties gekoppel en bereken word:

Voordeel 2: Tel unieke waardes

'n Gereelde spiltabel gee ons die geleentheid om een ​​van verskeie ingeboude berekeningsfunksies te kies: som, gemiddeld, telling, minimum, maksimum, ens. In die Data Model-opsomming word 'n baie nuttige funksie by hierdie standaardlys gevoeg om die aantal unieke (nie-herhalende waardes). Met sy hulp, byvoorbeeld, kan jy maklik tel die aantal unieke items van goedere (reeks) wat ons verkoop in elke stad.

Regskliek op die veld - opdrag Waarde veld opsies en op die blad Operasie Kies Aantal verskillende elemente (Distinct telling):

Voordeel 3: Pasgemaakte DAX-formules

Soms moet jy verskeie bykomende berekeninge in spilpunttabelle doen. In gereelde opsommings word dit gedoen met behulp van berekende velde en voorwerpe, terwyl die datamodel-opsomming maatstawwe in 'n spesiale DAX-taal (DAX = Data Analysis Expressions) gebruik.

Om 'n maatstaf te skep, kies op die oortjie kragspil Command Maatreëls – Skep maatstaf (Mate – Nuwe maatstaf) of klik net met die rechtermuisknop op die tabel in die Pivot Fields-lys en kies Voeg maat by (Voeg maat by) in die konteks kieslys:

Stel in die venster wat oopmaak:

• Tafelnaamwaar die geskepte maat gestoor sal word.
• Maat naam – enige naam wat jy vir die nuwe veld verstaan.
• Beskrywing - opsioneel.
• Formule – die belangrikste ding, want hier gaan ons óf handmatig in, óf klik op die knoppie fx en kies 'n DAX-funksie uit die lys, wat die resultaat moet bereken wanneer ons dan ons maat in die Waardes-area gooi.
• In die onderste deel van die venster kan jy dadelik die getalformaat vir die maat in die lys stel kategorie.
  

Die DAX-taal is nie altyd maklik om te verstaan ​​nie, want dit werk nie met individuele waardes nie, maar met hele kolomme en tabelle, dws vereis 'n mate van herstrukturering van denke na die klassieke Excel-formules. Dit is egter die moeite werd, want die krag van sy vermoëns in die verwerking van groot hoeveelhede data is moeilik om te oorskat.

Voordeel 4: Pasgemaakte veldhiërargieë

Dikwels, wanneer jy standaardverslae skep, moet jy dieselfde kombinasies van velde in spilpunttabelle in 'n gegewe volgorde gooi, byvoorbeeld Jaar-Kwartaal-Maand-Dag, of Kategorie-Produk, of Land-Stad-Kliënt ens. In die Data Model-opsomming word hierdie probleem maklik opgelos deur jou eie te skep hiërargieë - pasgemaakte veldstelle.

Skakel in die Power Pivot-venster na kaartmodus met die knoppie Grafiese aansig tab What is This (Tuis — Diagramaansig), kies met Ctrl gewenste velde en regskliek daarop. Die konteks kieslys sal die opdrag bevat Skep hiërargie (Skep hiërargie):

Die geskepte hiërargie kan hernoem word en met die muis die vereiste velde daarin gesleep word, sodat hulle later in een beweging in die opsomming gegooi kan word:

Voordeel 5: Pasgemaakte stensils

Deur die idee van die vorige paragraaf voort te sit, in die opsomming van die datamodel, kan u ook u eie stelle elemente vir elke veld skep. Byvoorbeeld, uit die hele lys van stede, kan jy maklik 'n stel maak van slegs dié wat in jou verantwoordelikheidsgebied is. Of versamel net jou kliënte, jou goedere, ens. in 'n spesiale stel.

Om dit te doen, op die blad Pivot tabel analise in die aftreklys Velde, items en stelle daar is ooreenstemmende opdragte (Analiseer - Fields, Ibepalings en stelle - Skep stel gebaseer op ry-/kolom-items):

In die venster wat oopmaak, kan jy selektief verwyder, byvoeg of die posisie van enige elemente verander en die resulterende stel onder 'n nuwe naam stoor:

Alle geskepte stelle sal in die PivotTable Fields-paneel in 'n aparte vouer vertoon word, vanwaar hulle vrylik na die rye en kolomareas van enige nuwe PivotTable gesleep kan word:

Voordeel 6: Versteek tabelle en kolomme selektief

Alhoewel dit in sommige gevalle 'n klein, maar baie aangename voordeel is. Deur met die rechtermuisknop op die veldnaam of op die tabeloortjie in die Power Pivot-venster te klik, kan jy die opdrag kies Versteek van Client Toolkit (Versteek vir kliëntnutsgoed):

Die versteekte kolom of tabel sal uit die PivotTable Field List-venster verdwyn. Dit is baie gerieflik as jy 'n paar hulpkolomme (byvoorbeeld berekende of kolomme met sleutelwaardes vir die skep van verhoudings) of selfs hele tabelle vir die gebruiker moet wegsteek.

Voordeel 7. Gevorderde dril-down

As jy dubbelklik op enige sel in die waardearea in 'n gewone spiltabel, dan vertoon Excel op 'n aparte vel 'n kopie van die brondatafragment wat by die berekening van hierdie sel betrokke was. Dit is 'n baie handige ding, amptelik genoem Drill-down (in hulle sê gewoonlik "misluk").

In die Data Model-opsomming werk hierdie handige hulpmiddel meer subtiel. Deur op enige sel te staan ​​met die resultaat wat ons interesseer, kan jy op die ikoon klik met 'n vergrootglas wat langsaan verskyn (dit word genoem Express Neigings) en kies dan enige veld waarin jy belangstel in enige verwante tabel:

Daarna sal die huidige waarde (Model = Explorer) in die filterarea gaan, en die opsomming sal deur kantore gebou word:

Natuurlik kan so 'n prosedure baie keer herhaal word, deur konsekwent in jou data te delf in die rigting waarin jy belangstel.

Voordeel 8: Skakel spilpunt na kubusfunksies om

As jy enige sel in die opsomming vir die Data Model kies en dan op die blad kies Pivot tabel analise Command OLAP-nutsgoed – Skakel om na formules (Analiseer - OLAP-nutsmiddels - Skakel om na formules), dan sal die hele opsomming outomaties na formules omgeskakel word. Nou sal die veldwaardes in die ry-kolom-area en die resultate in die waarde-area uit die Data Model gehaal word deur die spesiale kubusfunksies te gebruik: CUBEVALUE en CUBEMEMBER:

Tegnies beteken dit dat ons nou nie met 'n opsomming te doen het nie, maar met verskeie selle met formules, maw ons kan maklik enige transformasies met ons verslag doen wat nie in die opsomming beskikbaar is nie, byvoorbeeld, voeg nuwe rye of kolomme in die middel in. van die verslag, doen enige bykomende berekeninge binne die opsomming, rangskik dit op enige gewenste manier, ens.

Terselfdertyd bly die verband met die brondata natuurlik en in die toekoms sal hierdie formules bygewerk word wanneer die bronne verander. Die skoonheid!

• Planfeitontleding in 'n spilpunttabel met Power Pivot en Power Query
• Spiltabel met meerlynopskrif
• Skep 'n databasis in Excel met Power Pivot