Fordeler med Pivot by Data Model

Når du bygger en pivottabell i Excel, i den aller første dialogboksen, hvor vi blir bedt om å angi startområdet og velge et sted å sette inn pivottabellen, er det en usynlig, men veldig viktig avmerkingsboks nedenfor – Legg til disse dataene i datamodellen (Legg til disse dataene til datamodell) og, litt høyere, bryteren Bruk denne bokens datamodell (Bruk datamodellen til denne arbeidsboken):

Dessverre er det mange brukere som har vært kjent med pivottabeller i lang tid og som lykkes med å bruke dem i arbeidet, noen ganger ikke helt forstår betydningen av disse alternativene og bruker dem aldri. Og forgjeves. Å lage en pivottabell for datamodellen gir oss tross alt flere svært viktige fordeler sammenlignet med den klassiske Excel-pivottabellen.

Men før vi vurderer disse "bollene" på nært hold, la oss først forstå hva denne datamodellen faktisk er?

Hva er en datamodell

Datamodell (forkortet MD eller DM = Data Model) er et spesielt område inne i en Excel-fil hvor du kan lagre tabelldata – en eller flere tabeller koblet til hverandre om ønskelig. Faktisk er dette en liten database (OLAP-kube) innebygd i en Excel-arbeidsbok. Sammenlignet med den klassiske lagringen av data i form av vanlige (eller smarte) tabeller på selve Excel-ark, har datamodellen flere betydelige fordeler:

• Tabeller kan være opp til 2 milliarder linjer, og et Excel-ark kan passe litt mer enn 1 million.
• Til tross for den gigantiske størrelsen, utføres behandlingen av slike tabeller (filtrering, sortering, beregninger på dem, bygningssammendrag, etc.) veldig fort Mye raskere enn Excel selv.
• Med dataene i modellen kan du utføre ytterligere (om ønskelig, svært komplekse) beregninger vha innebygd DAX-språk.
  
• All informasjon som lastes inn i datamodellen er veldig sterkt komprimert ved hjelp av en spesiell innebygd arkiver og ganske moderat øker størrelsen på den originale Excel-filen.
  

Modellen administreres og beregnes av et spesielt tillegg innebygd i Microsoft Excel – Powerpivotsom jeg allerede har skrevet om. For å aktivere det, på fanen utvikler klikk COM-tillegg (Utvikler – COM-tillegg) og merk av i den aktuelle boksen:

Hvis faner utvikler (Utvikler)du kan ikke se det på båndet, du kan slå det på gjennom Fil – Alternativer – Båndoppsett (Fil — Alternativer — Tilpass bånd). Hvis du i vinduet vist ovenfor i listen over COM-tillegg ikke har Power Pivot, er det ikke inkludert i din versjon av Microsoft Office 🙁

På Power Pivot-fanen som vises, vil det være en stor lysegrønn knapp Administrasjon (Få til), ved å klikke på som vil åpne Power Pivot-vinduet på toppen av Excel, hvor vi vil se innholdet i datamodellen til gjeldende bok:

En viktig merknad på veien: en Excel-arbeidsbok kan bare inneholde én datamodell.

Last inn tabeller i datamodellen

For å laste inn data i modellen, gjør vi først tabellen til en dynamisk "smart" tastatursnarvei Ctrl+T og gi den et vennlig navn på fanen Constructor (Design). Dette er et nødvendig trinn.

Deretter kan du bruke en av de tre metodene å velge mellom:

• trykk på knappen Legg til modell (Legg til datamodell) tab Powerpivot tab Hjemprodukt (Hjem).
  
• Å velge lag Sett inn – Pivottabell (Sett inn – pivottabell) og slå på avmerkingsboksen Legg til disse dataene i datamodellen (Legg til disse dataene i datamodellen). I dette tilfellet, i henhold til dataene som er lastet inn i modellen, bygges det også umiddelbart en pivottabell.
  
• I kategorien Avansert Data (Dato) klikk på knappen Fra Tabell/Rekkevidde (Fra tabell/område)for å laste tabellen vår inn i Power Query-redigeringsprogrammet. Denne banen er den lengste, men om ønskelig kan du her utføre ytterligere datarensing, redigering og alle slags transformasjoner, der Power Query er veldig sterk.

  Deretter lastes de kjemmede dataene opp til modellen med kommandoen Hjem — Lukk og last — Lukk og last inn... (Hjem — Lukk&last — Lukk&last til...). Velg alternativet i vinduet som åpnes Bare opprette en forbindelse (Bare opprette forbindelse) og viktigst av alt, sett en hake Legg til disse dataene i datamodellen (Legg til disse dataene i datamodellen).
  

Vi bygger et sammendrag av datamodellen

For å bygge en sammendragsdatamodell kan du bruke en av tre tilnærminger:

• trykk på knappen oppsummeringstabell (pivottabell) i Power Pivot-vinduet.
• Velg kommandoer i Excel Sett inn – Pivottabell og bytt til modus Bruk denne bokens datamodell (Sett inn – Pivottabell – Bruk denne arbeidsbokens datamodell).
• Å velge lag Sett inn – Pivottabell (Sett inn – pivottabell) og slå på avmerkingsboksen Legg til disse dataene i datamodellen (Legg til disse dataene i datamodellen). Den nåværende "smarte" tabellen vil bli lastet inn i modellen og en oppsummeringstabell vil bli bygget for hele modellen.

Nå som vi har funnet ut hvordan vi laster inn data i datamodellen og bygger et sammendrag på den, la oss utforske fordelene og fordelene dette gir oss.

Fordel 1: Forhold mellom tabeller uten bruk av formler

Et vanlig sammendrag kan bare bygges ved å bruke data fra én kildetabell. Hvis du har flere av dem, for eksempel salg, prisliste, kundekartotek, kontraktsregister, etc., så må du først samle data fra alle tabeller til én ved hjelp av funksjoner som VLOOKUP (VISNING), INDEKS (INDEKS), MER EKSPONERT (KAMP), SUMMESLIMN (SUMIFS) o.l. Dette er langt, kjedelig og driver Excel til en "tanke" med en stor mengde data.

Når det gjelder et sammendrag av datamodellen, er alt mye enklere. Det er nok å sette opp relasjoner mellom tabeller én gang i Power Pivot-vinduet – og det er gjort. For å gjøre dette, på fanen Powerpivot trykk på knappen Administrasjon (Få til) og deretter i vinduet som vises – knappen Kartvisning (Diagramvisning). Det gjenstår å dra vanlige (nøkkel) kolonnenavn (felt) mellom tabeller for å lage koblinger:

Etter det, i sammendraget for datamodellen, kan du kaste i sammendragsområdet (rader, kolonner, filtre, verdier) alle felt fra alle relaterte tabeller - alt vil bli koblet og beregnet automatisk:

Fordel 2: Tell unike verdier

En vanlig pivottabell gir oss muligheten til å velge en av flere innebygde beregningsfunksjoner: sum, gjennomsnitt, antall, minimum, maksimum osv. I datamodellsammendraget er det lagt til en svært nyttig funksjon i denne standardlisten for å telle antall unike (ikke-repeterende verdier). Med dens hjelp kan du for eksempel enkelt telle antall unike varer (utvalg) som vi selger i hver by.

Høyreklikk på feltet – kommando Alternativer for verdifelt og på fanen Operasjon Velg Antall forskjellige elementer (Distinkt antall):

Fordel 3: Egendefinerte DAX-formler

Noen ganger må du utføre ulike tilleggsberegninger i pivottabeller. I vanlige oppsummeringer gjøres dette ved hjelp av beregnede felt og objekter, mens datamodelloppsummeringen bruker mål på et spesielt DAX-språk (DAX = Data Analysis Expressions).

For å opprette et mål, velg på fanen Powerpivot Kommando Mål – Lag mål (Tiltak — Nytt mål) eller bare høyreklikk på tabellen i Pivot Fields-listen og velg Legg til mål (Legg til mål) i kontekstmenyen:

I vinduet som åpnes, sett:

• Tabellnavnhvor det opprettede tiltaket vil bli lagret.
• Målnavn – et hvilket som helst navn du forstår for det nye feltet.
• Beskrivelse – valgfritt.
• Formel – det viktigste, for her går vi enten inn manuelt, eller klikker på knappen fx og velg en DAX-funksjon fra listen, som skal beregne resultatet når vi deretter kaster målet vårt inn i verdiområdet.
• I den nedre delen av vinduet kan du umiddelbart sette tallformatet for målet i listen Kategori.
  

DAX-språket er ikke alltid lett å forstå fordi det ikke opererer med individuelle verdier, men med hele kolonner og tabeller, dvs. krever noe omstrukturering av tenkningen etter de klassiske Excel-formlene. Imidlertid er det verdt det, fordi kraften til dens evner til å behandle store mengder data er vanskelig å overvurdere.

Fordel 4: Egendefinerte felthierarkier

Når du oppretter standardrapporter, må du ofte kaste de samme kombinasjonene av felt inn i pivottabeller i en gitt rekkefølge, for eksempel År-kvartal-måned-dageller Kategori-Produkteller Land-by-klient osv. I datamodellsammendraget løses dette problemet enkelt ved å lage din egen hierarkier - tilpassede feltsett.

I Power Pivot-vinduet bytter du til kartmodus med knappen Kartvisning tab Hjemprodukt (Hjem – diagramvisning), velg med Ctrl ønskede felt og høyreklikk på dem. Kontekstmenyen vil inneholde kommandoen Lag hierarki (Lag hierarki):

Det opprettede hierarkiet kan gis nytt navn og dras inn i det med musen de nødvendige feltene, slik at de senere i en bevegelse kan kastes inn i sammendraget:

Fordel 5: Egendefinerte sjablonger

Ved å fortsette ideen til forrige avsnitt, i sammendraget av datamodellen, kan du også lage dine egne sett med elementer for hvert felt. For eksempel, fra hele listen over byer, kan du enkelt lage et sett med bare de som er i ditt ansvarsområde. Eller samle kun dine kunder, dine varer osv. i et spesielt sett.

For å gjøre dette, på fanen Pivottabellanalyse i rullegardinlisten Felt, elementer og sett det er tilsvarende kommandoer (Analyser - Fields, Ivilkår og sett — Lag sett basert på rad-/kolonneelementer):

I vinduet som åpnes kan du selektivt fjerne, legge til eller endre plasseringen til alle elementer og lagre det resulterende settet under et nytt navn:

Alle opprettede sett vil bli vist i PivotTable Fields-panelet i en egen mappe, hvorfra de fritt kan dras til rad- og kolonneområdene i enhver ny pivottabell:

Fordel 6: Skjul tabeller og kolonner selektivt

Selv om dette er en liten, men veldig hyggelig fordel i noen tilfeller. Ved å høyreklikke på feltnavnet eller på tabellfanen i Power Pivot-vinduet kan du velge kommandoen Skjul fra Client Toolkit (Skjul fra klientverktøy):

Den skjulte kolonnen eller tabellen forsvinner fra ruten PivotTable Field List. Det er veldig praktisk hvis du trenger å skjule noen hjelpekolonner for brukeren (for eksempel beregnede eller kolonner med nøkkelverdier for å lage relasjoner) eller til og med hele tabeller.

Fordel 7. Avansert drill-down

Hvis du dobbeltklikker på en celle i verdiområdet i en vanlig pivottabell, viser Excel på et eget ark en kopi av kildedatafragmentet som var involvert i beregningen av denne cellen. Dette er en veldig hendig ting, offisielt kalt Drill-down (på de vanligvis sier de "fail").

I datamodellsammendraget fungerer dette hendige verktøyet mer subtilt. Ved å stå på en hvilken som helst celle med resultatet som interesserer oss, kan du klikke på ikonet med et forstørrelsesglass som dukker opp ved siden av (det kalles Ekspresstrender) og velg deretter et felt du er interessert i i en relatert tabell:

Etter det vil gjeldende verdi (Model = Explorer) gå inn i filterområdet, og sammendraget vil bli bygget av kontorer:

Selvfølgelig kan en slik prosedyre gjentas mange ganger, konsekvent dykke ned i dataene dine i den retningen du er interessert i.

Fordel 8: Konverter pivot til kubefunksjoner

Hvis du velger en celle i sammendraget for datamodellen og deretter velger på fanen Pivottabellanalyse Kommando OLAP-verktøy – Konverter til formler (Analyser - OLAP-verktøy - Konverter til formler), så blir hele sammendraget automatisk konvertert til formler. Nå vil feltverdiene i rad-kolonneområdet og resultatene i verdiområdet hentes fra datamodellen ved å bruke de spesielle kubefunksjonene: CUBEVALUE og CUBEMEMBER:

Teknisk sett betyr dette at vi nå ikke har å gjøre med et sammendrag, men med flere celler med formler, dvs. vi kan enkelt gjøre eventuelle transformasjoner med rapporten vår som ikke er tilgjengelig i sammendraget, for eksempel sette inn nye rader eller kolonner i midten av rapporten, gjør eventuelle tilleggsberegninger inne i sammendraget, ordne dem på ønsket måte, osv.

Samtidig forblir selvfølgelig sammenhengen med kildedataene og i fremtiden vil disse formlene bli oppdatert når kildene endres. Skjønnheten!

• Planfaktaanalyse i en pivottabell med Power Pivot og Power Query
• Pivottabell med flerlinjeoverskrift
• Lag en database i Excel ved hjelp av Power Pivot