Voordelen van draaien op gegevensmodel

Bij het bouwen van een draaitabel in Excel, in het allereerste dialoogvenster, waar we worden gevraagd om het initiële bereik in te stellen en een plaats te kiezen om de draaitabel in te voegen, is er een onopvallend maar zeer belangrijk selectievakje hieronder: Voeg deze gegevens toe aan het gegevensmodel (Voeg deze gegevens toe naar gegevensmodel) en, een beetje hoger, de schakelaar Gebruik het gegevensmodel van dit boek (Gebruik het gegevensmodel van deze werkmap):

Helaas begrijpen veel gebruikers die al lang bekend zijn met draaitabellen en deze met succes in hun werk gebruiken, soms niet echt de betekenis van deze opties en gebruiken ze nooit. En tevergeefs. Het maken van een draaitabel voor het gegevensmodel geeft ons immers een aantal zeer belangrijke voordelen ten opzichte van de klassieke Excel-draaitabel.

Laten we echter, voordat we deze "broodjes" van dichtbij bekijken, eerst begrijpen wat dit gegevensmodel in feite is?

Wat is een gegevensmodel?

Gegevensmodel (afgekort als MD of DM = Data Model) is een speciaal gebied in een Excel-bestand waar u tabelgegevens kunt opslaan – een of meer tabellen, indien gewenst, aan elkaar gekoppeld. In feite is dit een kleine database (OLAP-kubus) die is ingebed in een Excel-werkmap. Vergeleken met de klassieke opslag van gegevens in de vorm van gewone (of slimme) tabellen op werkbladen van Excel zelf, heeft het datamodel een aantal belangrijke voordelen:

• Tafels kunnen maximaal 2 miljard regels, en een Excel-blad kan iets meer dan 1 miljoen bevatten.
• Ondanks de gigantische omvang wordt de verwerking van dergelijke tabellen (filteren, sorteren, berekeningen erop, gebouwoverzicht, etc.) uitgevoerd erg snel Veel sneller dan Excel zelf.
• Met de gegevens in het Model kunt u aanvullende (desgewenst zeer complexe) berekeningen uitvoeren met behulp van ingebouwde DAX-taal.
  
• Alle informatie die in het gegevensmodel wordt geladen, is erg sterk gecomprimeerd met behulp van een speciale ingebouwde archiver en de grootte van het originele Excel-bestand enigszins vergroot.
  

Het model wordt beheerd en berekend door een speciale invoegtoepassing die is ingebouwd in Microsoft Excel - powerpivotwaarover ik al heb geschreven. Om het in te schakelen, op het tabblad ontwikkelaar Klik COM-invoegtoepassingen (Ontwikkelaar — COM-invoegtoepassingen) en vink het juiste vakje aan:

Als tabbladen ontwikkelaar (Ontwikkelaar)je kunt het niet zien op het lint, je kunt het aanzetten via Bestand – Opties – Lint instellen (Bestand — Opties — Lint aanpassen). Als u in het bovenstaande venster in de lijst met COM-invoegtoepassingen geen Power Pivot heeft, dan is deze niet opgenomen in uw versie van Microsoft Office 🙁

Op het Power Pivot-tabblad dat verschijnt, staat een grote lichtgroene knop Management (Beheren), klikken waarop het Power Pivot-venster bovenop Excel wordt geopend, waar we de inhoud van het gegevensmodel van het huidige boek zullen zien:

Een belangrijke opmerking onderweg: een Excel-werkmap kan slechts één gegevensmodel bevatten.

Laad tabellen in het gegevensmodel

Om gegevens in het model te laden, veranderen we eerst de tabel in een dynamische "slimme" sneltoets Ctrl+T en geef het een vriendelijke naam op het tabblad Constructeur (Ontwerp). Dit is een vereiste stap.

Dan kunt u een van de drie methoden gebruiken om uit te kiezen:

• Druk op de knop Toevoegen aan model (Toevoegen aan gegevensmodel) tab powerpivot tab Home (Home).
  
• Teams kiezen Invoegen – draaitabel (Invoegen — draaitabel) en schakel het selectievakje in Voeg deze gegevens toe aan het gegevensmodel (Voeg deze gegevens toe aan het gegevensmodel). In dit geval wordt, volgens de gegevens die in het model zijn geladen, ook meteen een draaitabel gebouwd.
  
• Op het tabblad Geavanceerd Data (Datum) klik op de knop Van tafel/bereik (Van tafel/bereik)om onze tabel in de Power Query-editor te laden. Dit pad is het langste, maar indien gewenst kun je hier extra data opschonen, bewerken en allerlei transformaties uitvoeren, waarbij Power Query erg sterk is.

  Vervolgens worden de gekamde gegevens geüpload naar het model met de opdracht Home — Sluiten en laden — Sluiten en laden in… (Home — Sluiten&Laden — Sluiten&Laden naar…). Selecteer in het geopende venster de optie Maak gewoon een verbinding (Alleen verbinding maken) en, belangrijker nog, een vinkje zetten Voeg deze gegevens toe aan het gegevensmodel (Voeg deze gegevens toe aan het gegevensmodel).
  

We bouwen een samenvatting van het gegevensmodel

Om een ​​samenvattend gegevensmodel te bouwen, kunt u drie benaderingen gebruiken:

• druk op de knop samenvattingstabel (draaitabel) in het Power Pivot-venster.
• Selecteer opdrachten in Excel Invoegen – draaitabel en overschakelen naar modus Gebruik het gegevensmodel van dit boek (Invoegen — Draaitabel — Gebruik het gegevensmodel van deze werkmap).
• Teams kiezen Invoegen – draaitabel (Invoegen — draaitabel) en schakel het selectievakje in Voeg deze gegevens toe aan het gegevensmodel (Voeg deze gegevens toe aan het gegevensmodel). De huidige "slimme" tabel wordt in het model geladen en er wordt een overzichtstabel gemaakt voor het hele model.

Nu we hebben ontdekt hoe we gegevens in het gegevensmodel kunnen laden en er een samenvatting van kunnen maken, gaan we de voordelen en voordelen ervan onderzoeken.

Voordeel 1: Relaties tussen tabellen zonder formules te gebruiken

Een reguliere samenvatting kan alleen worden gemaakt met gegevens uit één brontabel. Als u er meerdere hebt, bijvoorbeeld verkoop, prijslijst, klantenlijst, contractenregister, enz., dan moet u eerst gegevens van alle tabellen in één verzamelen met behulp van functies zoals VERT.ZOEKEN (VERT.ZOEKEN), INHOUDSOPGAVE (INHOUDSOPGAVE), MEER BLOOTGESTELD (WEDSTRIJD), SUMMESLIMN (SOMMEN) en dergelijke. Dit is lang, vervelend en drijft uw Excel in een "gedachte" met een grote hoeveelheid gegevens.

In het geval van een samenvatting van het datamodel is alles veel eenvoudiger. Het is voldoende om eenmaal in het Power Pivot-venster relaties tussen tabellen in te stellen - en het is klaar. Om dit te doen, op het tabblad powerpivot druk op de knop Management (Beheren) en dan in het venster dat verschijnt – de knop Grafiekweergave (Diagramweergave). Het blijft om gemeenschappelijke (sleutel) kolomnamen (velden) tussen tabellen te slepen om koppelingen te maken:

Daarna kunt u in de samenvatting voor het gegevensmodel in het overzichtsgebied (rijen, kolommen, filters, waarden) alle velden van alle gerelateerde tabellen toevoegen - alles wordt automatisch gekoppeld en berekend:

Voordeel 2: unieke waarden tellen

Een gewone draaitabel geeft ons de mogelijkheid om een ​​van de verschillende ingebouwde berekeningsfuncties te kiezen: som, gemiddelde, telling, minimum, maximum, enz. In de samenvatting van het gegevensmodel is een zeer nuttige functie toegevoegd aan deze standaardlijst om de aantal unieke (niet-herhalende waarden). Met zijn hulp kunt u bijvoorbeeld eenvoudig het aantal unieke goederen (assortiment) tellen dat we in elke stad verkopen.

Klik met de rechtermuisknop op het veld - commando Opties voor waardeveld en op het tabblad Werking Kies Aantal verschillende elementen (verschillende telling):

Voordeel 3: Aangepaste DAX-formules

Soms moet u verschillende aanvullende berekeningen uitvoeren in draaitabellen. In reguliere samenvattingen gebeurt dit met behulp van berekende velden en objecten, terwijl de datamodelsamenvatting metingen gebruikt in een speciale DAX-taal (DAX = Data Analysis Expressions).

Om een ​​meting aan te maken, selecteer op het tabblad powerpivot commando Maatregelen – Maatregel maken (Maatregelen — Nieuwe maatregel) of klik met de rechtermuisknop op de tabel in de lijst Draaivelden en selecteer Maatregel toevoegen (Voeg maat toe) in het contextmenu:

Stel in het geopende venster in:

• Tafel naamwaar de gemaakte maat wordt opgeslagen.
• Naam van de meting – elke naam die u begrijpt voor het nieuwe veld.
• Omschrijving - optioneel.
• Formule – het belangrijkste, want hier voeren we ofwel handmatig in, ofwel klikken we op de knop fx en selecteer een DAX-functie uit de lijst, die het resultaat zou moeten berekenen wanneer we onze maat vervolgens in het gebied Waarden gooien.
• In het onderste deel van het venster kunt u direct de getalnotatie instellen voor de maat in de lijst Categorie.
  

De DAX-taal is niet altijd gemakkelijk te begrijpen omdat het niet met individuele waarden werkt, maar met hele kolommen en tabellen, dwz dat het enige herstructurering van het denken vereist na de klassieke Excel-formules. Het is echter de moeite waard, omdat de kracht van zijn mogelijkheden bij het verwerken van grote hoeveelheden gegevens moeilijk te overschatten is.

Voordeel 4: Aangepaste veldhiërarchieën

Vaak moet u bij het maken van standaardrapporten bijvoorbeeld dezelfde combinaties van velden in draaitabellen in een bepaalde volgorde gooien Jaar-kwartaal-maand-dagof Categorie-Productof Land-Stad-Cliënt enz. In de samenvatting van het gegevensmodel kan dit probleem eenvoudig worden opgelost door uw eigen te maken hiërarchieën — aangepaste veldsets.

Schakel in het Power Pivot-venster naar de kaartmodus met de knop Grafiekweergave tab Home (Home — Diagramweergave), selecteer met Ctrl gewenste velden en klik er met de rechtermuisknop op. Het contextmenu bevat de opdracht Hiërarchie maken (Hiërarchie maken):

De gemaakte hiërarchie kan worden hernoemd en met de muis de vereiste velden erin worden gesleept, zodat ze later in één beweging in de samenvatting kunnen worden gegooid:

Voordeel 5: Aangepaste stencils

Als u het idee van de vorige paragraaf voortzet, kunt u in de samenvatting van het gegevensmodel ook uw eigen sets elementen voor elk veld maken. U kunt bijvoorbeeld uit de hele lijst met steden eenvoudig een set maken van alleen de steden die in uw verantwoordelijkheidsgebied liggen. Of verzamel alleen uw klanten, uw goederen, enz. in een speciale set.

Om dit te doen, op het tabblad Draaitabelanalyse in de vervolgkeuzelijst Velden, items en sets er zijn bijbehorende opdrachten (Analyseren — Velden, Items & Sets — Maak een set op basis van rij-/kolomitems):

In het geopende venster kunt u selectief elementen verwijderen, toevoegen of de positie wijzigen en de resulterende set opslaan onder een nieuwe naam:

Alle gemaakte sets worden weergegeven in het deelvenster Draaitabelvelden in een aparte map, van waaruit ze vrij kunnen worden gesleept naar de rijen en kolommen van een nieuwe draaitabel:

Voordeel 6: Tabellen en kolommen selectief verbergen

Hoewel dit in sommige gevallen een klein, maar zeer aangenaam voordeel is. Door met de rechtermuisknop op de veldnaam of op het tabeltabblad in het Power Pivot-venster te klikken, kunt u de opdracht selecteren: Verbergen voor Client Toolkit (Verbergen voor clienthulpprogramma's):

De verborgen kolom of tabel verdwijnt uit het deelvenster Lijst met draaitabelvelden. Het is erg handig als u enkele hulpkolommen (bijvoorbeeld berekende kolommen of kolommen met sleutelwaarden voor het maken van relaties) of zelfs hele tabellen voor de gebruiker wilt verbergen.

Voordeel 7. Geavanceerde drill-down

Als u dubbelklikt op een cel in het waardegebied in een gewone draaitabel, geeft Excel op een apart blad een kopie weer van het brongegevensfragment dat betrokken was bij de berekening van deze cel. Dit is een heel handig ding, officieel Drill-down genoemd (waarin ze meestal 'mislukt' zeggen).

In het Data Model-overzicht werkt deze handige tool subtieler. Door op een cel te gaan staan ​​met het resultaat dat ons interesseert, kun je op het icoontje klikken met een vergrootglas dat ernaast verschijnt (het heet Express trends) en selecteer vervolgens een veld waarin u geïnteresseerd bent in een gerelateerde tabel:

Daarna gaat de huidige waarde (Model = Explorer) naar het filtergebied en wordt de samenvatting gemaakt door kantoren:

Natuurlijk kan een dergelijke procedure vele malen worden herhaald, waarbij u consequent in uw gegevens duikt in de richting waarin u geïnteresseerd bent.

Voordeel 8: Pivot converteren naar kubusfuncties

Als u een cel in de samenvatting voor het gegevensmodel selecteert en vervolgens selecteert op het tabblad Draaitabelanalyse commando OLAP-hulpmiddelen – Converteren naar formules (Analyseren — OLAP Tools — Converteren naar formules), dan wordt het hele overzicht automatisch omgezet naar formules. Nu worden de veldwaarden in het rij-kolomgebied en de resultaten in het waardegebied opgehaald uit het Data Model met behulp van de speciale kubusfuncties: CUBEVALUE en CUBEMEMBER:

Technisch gezien betekent dit dat we nu niet te maken hebben met een samenvatting, maar met meerdere cellen met formules, dwz dat we met ons rapport gemakkelijk alle transformaties kunnen doen die niet beschikbaar zijn in de samenvatting, bijvoorbeeld nieuwe rijen of kolommen in het midden invoegen van het rapport, voer eventuele aanvullende berekeningen uit in de samenvatting, rangschik ze op elke gewenste manier, enz.

Tegelijkertijd blijft natuurlijk de koppeling met de brongegevens bestaan ​​en in de toekomst zullen deze formules worden bijgewerkt als de bronnen veranderen. De schoonheid!

• Plan-feitenanalyse in een draaitabel met Power Pivot en Power Query
• Draaitabel met koptekst met meerdere regels
• Maak een database in Excel met Power Pivot