Virdeeler vum Pivot vum Datemodell

Wann Dir eng Pivot-Tabelle an Excel baut, an der alleréischter Dialogbox, wou mir gefrot gi fir den initialen Beräich ze setzen an eng Plaz ze wielen fir de Pivot-Tabelle anzeféieren, gëtt et eng onopfälleg awer ganz wichteg Checkbox hei ënnen - Füügt dës Donnéeën an den Datemodell (Füügt dës Donnéeën zum Datemodell) an, e bësse méi héich, de Schalter Benotzt dëst Buch Daten Modell (Benotzt den Datemodell vun dësem Aarbechtsbuch):

Leider si vill Benotzer, déi laang Zäit mat Pivot Dëscher vertraut sinn an se erfollegräich an hirer Aarbecht benotzen, heiansdo net wierklech d'Bedeitung vun dësen Optiounen verstoen an se ni benotzen. An ëmsoss. No allem, eng Pivot-Tabelle fir den Data Model ze kreéieren gëtt eis e puer ganz wichteg Virdeeler am Verglach zum klassesche Excel Pivot-Tabelle.

Wéi och ëmmer, ier Dir dës "Bunnen" vun no berücksichtegen, loosst eis als éischt verstoen wat, tatsächlech, dësen Datemodell ass?

Wat ass en Datemodell

Daten Modell (ofgekierzt als MD oder DM = Data Model) ass e spezielle Gebitt an enger Excel Datei wou Dir tabulär Daten späichere kënnt - eng oder méi Dëscher verbonnen, wann Dir wëllt, mateneen. Tatsächlech ass dëst eng kleng Datebank (OLAP Cube) agebaut an engem Excel Aarbechtsbuch. Am Verglach mat der klassescher Späichere vun Daten a Form vu reegelméissegen (oder Smart) Dëscher op Blieder vun Excel selwer, huet den Data Model e puer bedeitend Virdeeler:

• Dëscher kënne bis zu 2 Milliarde Linnen, an en Excel Blat kann e bësse méi wéi 1 Millioun passen.
• Trotz der gigantescher Gréisst gëtt d'Veraarbechtung vun esou Dëscher (Filteren, Sortéierung, Berechnungen op hinnen, Bauresumé, asw.) ganz séier Vill méi séier wéi Excel selwer.
• Mat den Donnéeën am Model kënnt Dir zousätzlech (wann Dir wëllt, ganz komplex) Berechnungen benotzen gebaut-an DAX Sprooch.
  
• All Informatioun gelueden an den Data Model ass ganz staark kompriméiert mat engem speziellen agebauten Archiver an zimlech moderéiert d'Gréisst vun der ursprénglecher Excel Datei vergréissert.
  

De Modell gëtt geréiert a berechent duerch e speziellen Add-in a Microsoft Excel gebaut - powerpivotiwwer déi ech scho geschriwwen hunn. Fir et z'aktivéieren, op der Tab Entwéckler klickt COM Add-ins (Entwéckler - COM Add-ins) a kontrolléiert déi entspriechend Këscht:

Wann Tabs Entwéckler (Entwéckler)Dir gesitt et net op der Band, Dir kënnt et duerchschalten Datei - Optiounen - Ribbon Setup (Datei - Optiounen - Band personaliséieren). Wann Dir an der Fënster uewen an der Lëscht vun COM Add-ins net Power Pivot hutt, ass et net an Ärer Versioun vu Microsoft Office abegraff 🙁

Op der Power Pivot Tab déi erschéngt, gëtt et e grousse hellgrénge Knäppchen Gestioun (Management), klickt op déi d'Power Pivot Fënster uewen op Excel opmaacht, wou mir den Inhalt vum Datemodell vum aktuelle Buch gesinn:

Eng wichteg Notiz laanscht de Wee: en Excel Workbook kann nëmmen een Data Model enthalen.

Lued Dëscher an den Data Model

Fir Daten an de Model ze lueden, maache mir als éischt den Dësch an eng dynamesch "Smart" Tastatur Ofkiirzung Ctrl+T a gitt et e frëndlechen Numm op der Tab Builder (Design). Dëst ass en erfuerderleche Schrëtt.

Da kënnt Dir eng vun den dräi Methoden benotzen fir ze wielen:

• Press de Knäppchen Add to Model (Add to Data Model) Reiter powerpivot Reiter Home (Heem).
  
• Wiel vun Équipen Insert - PivotTable (Insert - Pivot Table) a schalt d'Checkbox un Füügt dës Donnéeën an den Datemodell (Füügt dës Donnéeën an den Datemodell). An dësem Fall, no den Daten, déi an de Model gelueden sinn, gëtt och direkt eng Pivot-Tabelle gebaut.
  
• Op der Advanced Tab Daten (Datum) klickt op de Knäppchen Vun Dësch / Gamme (Vum Dësch / Range)fir eisen Dësch an de Power Query Editor ze lueden. Dëse Wee ass de längsten, awer, wann Dir wëllt, hei kënnt Dir zousätzlech Datenreinigung, Redaktioun an all Zorte vu Transformatiounen ausféieren, an deenen Power Query ganz staark ass.

  Da ginn déi gekämmt Donnéeën op de Model eropgelueden duerch de Kommando Home - Zoumaachen a Lueden - Zoumaachen a Lueden an ... (Home — Close&Load — Close&load to...). An der Fënster déi opmaacht, wielt d'Optioun Schafen einfach eng Verbindung (Erstellt nëmmen eng Verbindung) an, am wichtegsten, e Kräizt setzen Füügt dës Donnéeën an den Datemodell (Füügt dës Donnéeën an den Datemodell).
  

Mir bauen e Resumé vum Datemodell

Fir e Resumé Data Model ze bauen, kënnt Dir eng vun dräi Approche benotzen:

• Press Knäppchen Resumé Dësch (Pivot Dësch) an der Power Pivot Fënster.
• Wielt Kommandoen an Excel Insert - PivotTable a schalt op Modus Benotzt dëst Buch Daten Modell (Insert - Pivot Table - Benotzt den Datemodell vun dësem Aarbechtsbuch).
• Wiel vun Équipen Insert - PivotTable (Insert - Pivot Table) a schalt d'Checkbox un Füügt dës Donnéeën an den Datemodell (Füügt dës Donnéeën an den Datemodell). Den aktuellen "Smart" Dësch gëtt an de Model gelueden an e Resumétabel gëtt fir de ganze Modell gebaut.

Elo datt mir erausfonnt hunn wéi d'Daten an den Data Model lueden an e Resumé doriwwer bauen, loosst eis d'Virdeeler an d'Virdeeler entdecken, déi dëst eis gëtt.

Virdeel 1: Relatiounen tëscht Dëscher ouni Formelen benotzen

E regelméissege Resumé kann nëmme mat Daten aus enger Quelltabel gebaut ginn. Wann Dir e puer vun hinnen hutt, zum Beispill Verkaf, Präislëscht, Clientsverzeechnes, Verträgregister, asw., da musst Dir als éischt Daten aus all Dëscher an een sammelen mat Funktiounen wéi VLOOKUP (VLOOKUP), INDEX (INDEX), MÉI ausgesat (MATCH), SUMMESLIMN (SUMIFS) an esou. Dëst ass laang, langweileg a fiert Ären Excel an e "Gedanke" mat enger grousser Quantitéit un Daten.

Am Fall vun engem Resumé vum Data Model ass alles vill méi einfach. Et ass genuch fir Relatiounen tëscht Dëscher eemol an der Power Pivot Fënster opzestellen - an et ass gemaach. Fir dëst ze maachen, op der Tab powerpivot dréckt op de Knäppchen Gestioun (Management) an dann an der Fënster déi erschéngt - de Knäppchen Chart View (Diagramm Vue). Et bleift fir gemeinsam (Schlëssel) Spaltennimm (Felder) tëscht Dëscher ze zéien fir Linken ze kreéieren:

Duerno, am Resumé fir den Data Model, kënnt Dir an de Resuméberäich (Reihen, Kolonnen, Filteren, Wäerter) all Felder vun all ähnlechen Dëscher werfen - alles gëtt verlinkt an automatesch berechent:

Virdeel 2: Zielt eenzegaarteg Wäerter

Eng regulär Pivot-Tabelle gëtt eis d'Méiglechkeet eng vun e puer agebaute Berechnungsfunktiounen ze wielen: Zomm, Moyenne, Zuel, Minimum, Maximum, asw. Zuel vun eenzegaartegen (net widderhuelend Wäerter). Mat senger Hëllef, zum Beispill, kënnt Dir einfach d'Zuel vun eenzegaartegen Artikele vu Wueren (Gamme) zielen, déi mir an all Stad verkafen.

Riets-klickt op d'Feld - Kommando Wäert Feld Optiounen an op der Tab Operatioun wielt Zuel vu verschiddenen Elementer (Ënnerscheet Zuel):

Virdeel 3: Benotzerdefinéiert DAX Formulen

Heiansdo musst Dir verschidde zousätzlech Berechnungen an Pivottabellen ausféieren. A reegelméissegen Zesummefaassungen gëtt dëst mat berechent Felder an Objeten gemaach, während d'Datemodell Zesummefaassung Moossnamen an enger spezieller DAX Sprooch benotzt (DAX = Data Analysis Expressions).

Fir eng Mooss ze kreéieren, wielt op der Tab powerpivot Kommando Moossnamen - Schafen Mooss (Mesures - New Mooss) oder klickt einfach op den Dësch an der Pivot Fields Lëscht a wielt Dobäizemaachen Moossnam (Mesure derbäisetzen) am Kontextmenü:

An der Fënster déi opmaacht, setze:

• Dësch Nummwou déi geschaf Mooss gespäichert ginn.
• Mooss Numm - all Numm Dir versteet fir déi nei Terrain.
• description - fakultativ.
• Formel - dat Wichtegst, well hei gi mir entweder manuell an, oder klickt op de Knäppchen fx a wielt eng DAX Funktioun aus der Lëscht, déi d'Resultat sollt berechnen wa mir dann eis Mooss an d'Wäertergebitt geheien.
• Am ënneschten Deel vun der Fënster kënnt Dir direkt d'Zuelenformat fir d'Mesure an der Lëscht setzen Kategorie.
  

D'DAX Sprooch ass net ëmmer einfach ze verstoen, well et net mat individuellen Wäerter funktionnéiert, mee mat ganze Spalten an Dëscher, also erfuerdert eng Restrukturéierung vum Denken no de klassesche Excel Formelen. Allerdéngs ass et derwäert, well d'Kraaft vu senge Fäegkeeten bei der Veraarbechtung vu grousse Quantitéiten un Daten schwéier ze iwwerschätzen ass.

Virdeel 4: Benotzerdefinéiert Feld Hierarchie

Dacks, wann Dir Standardberichter erstellt, musst Dir déiselwecht Kombinatioune vu Felder an Pivottabellen an enger bestëmmter Sequenz werfen, zum Beispill Joer-Véierel-Mount-Dag, oder Kategorie-Produkt, oder Land-Stad-Client etc. Am Data Model Resumé gëtt dëse Problem einfach geléist andeems Dir Ären eegene erstellt Hierarchien - personaliséiert Feldsets.

An der Power Pivot Fënster, schalt op den Diagrammmodus mam Knäppchen Chart View Reiter Home (Home - Diagram View), wielt mat Ctrl gewënschte Felder a riets-klickt op hinnen. De Kontextmenü enthält de Kommando Hierarchie erstellen (Hierarchie erstellen):

Déi erstallt Hierarchie kann ëmbenannt ginn a mat der Maus déi erfuerderlech Felder dran geschleeft ginn, sou datt se spéider an enger Bewegung an de Resumé geworf ginn:

Virdeel 5: Benotzerdefinéiert stencils

Weiderhin d'Iddi vum virege Paragraph, am Resumé vum Data Model, kënnt Dir och Är eege Sets vun Elementer fir all Feld erstellen. Zum Beispill, aus der ganzer Lëscht vu Stied kënnt Dir einfach e Set vun nëmmen deenen maachen, déi an Ärem Verantwortungsberäich sinn. Oder sammelen nëmmen Är Clienten, Är Wueren, asw an e spezielle Set.

Fir dëst ze maachen, op der Tab Pivot Dësch Analyse an der Dropdown-Lëscht Felder, Elementer a Sets et sinn entspriechend Kommandoen (Analyse - Fields, Items & Sets - Erstellt Set baséiert op Zeilen / Kolonnen Artikelen):

An der Fënster déi opmaacht, kënnt Dir selektiv d'Positioun vun all Element ewechhuelen, derbäisetzen oder änneren an de resultéierende Set ënner engem neien Numm späicheren:

All erstallt Sets ginn an der PivotTable Fields Panel an engem getrennten Dossier ugewisen, vu wou se fräi an d'Reihen a Kolonnen Beräicher vun all neie PivotTable gezunn kënne ginn:

Virdeel 6: Selektiv verstoppen Dëscher a Kolonnen

Och wann dëst e klengen, awer an e puer Fäll ganz agreabel Virdeel ass. Andeems Dir op de Feldnumm klickt oder op den Dësch Tab an der Power Pivot Fënster kënnt Dir de Kommando auswielen Verstoppen vum Client Toolkit (Verstoppt vu Client Tools):

Déi verstoppt Kolonn oder Tabell verschwannen aus der PivotTable Field List Pane. Et ass ganz bequem wann Dir e puer Hëllefskolonnen (zum Beispill berechent oder Saile mat Schlësselwäerter fir Bezéiungen ze kreéieren) oder souguer ganz Dëscher muss verstoppen.

Virdeel 7. Fortgeschratt Bueraarbechten

If you double-click on any cell in the value area in a regular pivot table, then Excel displays on a separate sheet a copy of the source data fragment that was involved in the calculation of this cell. This is a very handy thing, officially called Drill-down (in they usually say “fail”).

Am Data Model Resumé funktionnéiert dëst praktescht Tool méi subtil. Andeems Dir op enger Zelle steet mat dem Resultat dat eis interesséiert, kënnt Dir op d'Ikon klicken mat enger Lupe déi niewendrun opdaucht (et heescht Express Trends) a wielt dann all Feld an deem Dir interesséiert sidd an enger ähnlecher Tabell:

Duerno geet den aktuelle Wäert (Model = Explorer) an d'Filterberäich, an de Resumé gëtt vu Büroe gebaut:

Natierlech kann esou eng Prozedur vill Mol widderholl ginn, konsequent an Är Donnéeën an d'Richtung déi Dir interesséiert sidd.

Virdeel 8: Konvertéiert Pivot zu Cube Funktiounen

Wann Dir eng Zell am Resumé fir den Data Model wielt a wielt dann op der Tab Pivot Dësch Analyse Kommando OLAP Tools - Convertéieren op Formelen (Analyse - OLAP Tools - Convertéieren op Formelen), da gëtt de ganze Resumé automatesch op Formelen ëmgewandelt. Elo ginn d'Feldwäerter am Zeilekolonneberäich an d'Resultater am Wäertberäich aus dem Data Model zréckgewonnen mat de spezielle Würfelfunktiounen: CUBEVALUE an CUBEMEMBER:

Technesch heescht dat, datt mir elo net mat engem Resumé ze dinn hunn, mä mat e puer Zellen mat Formelen, also kënne mir einfach all Transformatioune mat eisem Rapport maachen, déi net am Resumé sinn, zum Beispill nei Reihen oder Kolonnen an d'Mëtt setzen vum Bericht, maacht zousätzlech Berechnungen am Resumé, arrangéiert se op all gewënschte Manéier, etc.

Zur selwechter Zäit bleift d'Verbindung mat de Quelldaten, natierlech, an an Zukunft ginn dës Formelen aktualiséiert wann d'Quellen änneren. D'Schéinheet!

• Plan-Fakt Analyse an engem Pivot Dësch mat Power Pivot a Power Query
• Pivot Dësch mat Multiline Header
• Erstellt eng Datebank an Excel mat Power Pivot