Vi har været i stand til at oprette pivottabeller i Excel siden midten af 1990’erne. Lige siden har folk spurgt, hvordan man udtrækker data fra pivottabeller ved hjælp af formler. Selvom pivottabeller er rigtig gode til analyse, er de ikke altid de bedste til præsentation, og derfor er det nyttigt at udtrække data ved hjælp af en formel. Vi har i øjeblikket 3 muligheder for dette, standard cellereferencer, GETPIVOTDATA og KUBE-funktioner.

I denne artikel vil vi sammenligne disse tre metoder. Det er ikke en dybgåendeanalyse af disse tilgange, men har til formål at fremhæve forskellene, så du kan træffe de bedste beslutninger i dit scenarie.

Jeg anbefaler, at du downloader eksempelfilen til dette indlæg. Derefter kan du arbejde sammen med eksempler og se løsningen i aktion, og så vil filen være nyttig til fremtidig reference.

Eksempel data

Alle eksempler i denne artikel bruger det samme datasæt. Det er tilgængeligt i downloadfilen.

Fra dette datasæt har jeg oprettet en pivottabel:

Bemærk, at når du bruger KUBE-funktioner, kræver oprettelsen af pivottabellen et ekstra trin. Jeg vil vise dig dette i det relevante afsnit senere i artiklen.

Standardformler

Pivottabeller ses i Excels regneark, hvor hver celle i pivottabellen har sin egen cellereference. Derfor er den enkleste metode til at hente værdier fra en pivottabel at bruge standard cellereferencer i en formel.

Udtræk af data fra pivottabellen

Som allerede nævnt kan vi referere ethvert pivottabelelement ved hjælp af cellereference.

I skærmbilledet ovenfor er formlen i celle M3:

=G3

Denne formel udtrækker værdien 43 fra pivottabellen, som er summen af værdier for Anne i København.

Ændring af data

Lad os nu ændre vores kildedata. I dette scenario viser det sig, at Dorte ikke er i Korsør, men i Århus. Så i vores kildedata skal du ændre celler B5 og B9 fra Korsør til Århus, og derefter klikke på Data -> Opdater alle.

Se skærmbilledet ovenfor. Vores cellereference til G3 er bibeholdt. Men datapunktet i den celle har ændret sig, så vi peger nu på en tom celle. Summen af værdier for Anne i København er i celle F3, frem for G3.

Læren af cellereferencer er, at cellereferencer ikke ændres, når placeringen af de underliggende data ændres.

Bemærk

Vi kunne bruge en formel som INDEKS/SAMMENLIGN/SAMMENLIGN til at udføre et 2-dimensionelt opslag og sikre, at vi udtrækker de rigtige data til Anne i København. Dette eliminerer dog fordelen ved at bruge en pivottabel.

For at undgå dette problem kan vi bruge en af de andre udtrækningsmetoder.

GETPIVOTDATA funktion

GETPIVOTDATA er den funktion, de fleste af os tænker på, når de skal udtrække værdier fra en pivottabel.

Slå GETPIVOTDATA til/fra

Vi kan indstille Excel til automatisk at oprette GETPIVOTDATA-funktionen, når vi klikker på en pivottabelcelle i en formel. For at gøre dette skal du markere en hvilken som helst celle i en pivottabel og klikke på Analyser pivottabel -> Indstillinger -> Generer Hent PivotData for at sikre, at indstillingen er slået til.

Hver gang vi refererer til en pivottabelcelle, oprettes GETPIVOTDATA-formlen automatisk for os.

Udtræk af data fra pivottabellen

Lad os ændre vores kildedata tilbage til sin oprindelige tilstand og opdatere pivottabellen. Lad os derefter bruge GETPIVOTDATA til at udtrække celle G3 fra pivottabellen.

I skærmbilledet ovenfor indeholder celle M3 formlen:

=GETPIVOTDATA("Værdi";$E$1;"Navn";"Anne";"By";"København")

Når du klikker på celle G3, blev GETPIVOTDATA-funktionen automatisk oprettet for os. De enkelte elementer fordeler sig som følger

• =GETPIVOTDATA – funktionens navn
• “Værdi” – navnet på værdifeltet
• $E$1 – cellen øverst til venstre i pivottabellen
• “Navn” – navnet på det første pivotfelt
• “Anne” – det specifikke element fra det første pivotfelt
• “By” – navnet på det andet pivotfelt
• “København” – det specifikke element fra det andet pivotfelt

Hvis der er flere felter, kan vi fortsætte med at tilføje disse i par, ligesom “By” og “København” har været ovenfor.

Ændring af data

Lad os nu ændre vores kildedata igen fra Korsør til Århus og klikke på Data -> Opdater alle.

Selvom cellen, der indeholder datapunktet, er ændret fra G3 til F3, refererer vi ikke til en celle gennem GETPIVOTDATA. I stedet linker vi til Sum af Værdi for Anne i København; derfor bevares det returnerede resultat.

Ændring af GETPIVOTDATA-funktionen

Vi kan gøre GETPIVOTDATA mere fleksibel ved at bruge regnearksceller i stedet for hardkodede feltnavne.

Tilknytning af pivotfelter til celler

Lad os referere et element fra feltet By til en celle.

På skærmbilledet ovenfor er København indtastet i celle M5. GETPIVOTDATA er ændret til følgende:

=GETPIVOTDATA("Værdi";$E$1;"Navn";"Anne";"By";M5)

Formlen refererer til celle M5, som indeholder feltelementet.

Vi kan ændre dette til Køge eller Slagelse osv., og GETPIVOTDATA genberegnes automatisk.

Link af datafelter til celler

Så hvad sker der, hvis vi knytter datafeltet til en celle?

Åh! Det virker ikke. Celle M7 indeholder nu teksten Værdi, som er navnet på feltet i værdisektionen i pivottabellen.

Formlen i celle M3 er:

=GETPIVOTDATA(M7;$E$1;"Navn";"Anne";"By";M5)

Men alt er ikke tabt. Der er en spøjs løsning, hvor vi kan tilføje en tom tekststreng i starten eller slutningen af cellereferencen, og det vil fungere.

Hvis vi ændrer formlen i celle M3 til følgende, virker det:

=GETPIVOTDATA(M7&"";$E$1;"Navn";"Anne";"By";M5)

Prøv det selv; det virker!

KUBE-funktioner

Lad os starte dette afsnit med at gå tilbage til vores originale datasæt.

En pivottabel kan vise oplysninger, der er gemt i enten Pivot-cachen eller datamodellen. Dette er de to metoder til lagring af data til pivottabellen. Ved oprettelsen bruges Pivot Cache som standard, men ved at klikke på et enkelt afkrydsningsfelt kan vi i stedet bruge datamodellen.

Datamodellen er en mere moderne og effektiv motor til håndtering af data. Derfor bør vi prøve at bruge datamodellen, hvor vi kan.

KUBE-funktionerne er en gruppe funktioner, der kan udtrække data fra datamodellen. Da pivottabeller og formler kan bruge den samme kilde, skaber KUBE-funktionerne det tilsvarende ved at udtrække værdier fra en pivottabel.

Der er 7 KUBE-funktioner, men i denne artikel vil vi kun se på to: KUBEMEDLEM og KUBEVÆRDI.

Oprettelse af en pivottabel med datamodellen

Hvis vi planlægger at bruge KUBE-formler, skal vi oprette en pivottabel på den rigtige måde.

Vælg en celle i datasættet, og klik derefter på Indsæt -> Pivottabel.

Vælg afkrydsningsfeltet Føj disse data til data modellen i dialogboksen Opret pivottabel, og klik derefter på OK.

Det er det; der er kun et klik mere. Pivottabellen oprettes ved hjælp af datamodellen i stedet for Pivot-cachen.

Vi kan tilføje pivotfelter og værdifelter på den normale måde.

Konverter en pivottabel til formler

En pivottabel oprettet ud fra en datamodel kan konverteres til KUBE-formler.

Vælg en celle i pivottabellen, og klik derefter på Pivottabelanalyse -> OLAP-værktøjer -> Konverter til formler.

Hvis din pivottabel har et felt i sektionen Filtre, vises dialogboksen Konverter til formler. I dette eksempel har vi ikke brugt sektionen Filtre, så denne dialogboks vises ikke. Hvis det gør det for dit scenario, skal du vælge den ønskede indstilling og klikke på Konverter.

Pivottabellen konverteres nu til KUBE-formler. Der er ingen pivottabel længere!

Vi vil gennemgå, hvordan man hurtigt ændrer disse formler.

Bemærk:

Selvom formlerne er blevet oprettet ved at konvertere en pivottabel, kan vi skrive disse selv ligesom andre standardfunktioner, hvis vi lærer den syntaks, der kræves for KUBE-formler.

Ændring af data

Lad os nu ændre vores kildedata igen, fra Korsør til Århus, og klik derefter på Data -> Opdater alle.

Da vi ikke længere har en pivottabel, er det resultatet af formlerne, der opdateres. Da der ikke længere er et feltelement kaldet Korsør i vores datasæt, vises dette nu som #I/T.

Overbevis dig selv, at det virker. Ret et tal i kildedataene, og klik på Data -> Opdater alle; formelresultaterne opdateres.

Ændring af KUBE-funktioner

Transformationsprocessen til at ændre pivottabellen til formler bruger to forskellige funktioner: KUBEMEDLEM og KUBEVÆRDI. Vi tager et kig på begge hver for sig.

KUBEMEDLEM

KUBEMEDLEM-formlen bruges til dimensionerne/felterne.

Formlen i celle G2 er:

=KUBEMEDLEM("ThisWorkbookDataModel";"[Tabel1].[By].&[København]")

Denne formel opdeles som følger:

• =KUBEMEDLEM – funktionens navn
• “ThisWorkbookDataModel” – refererer til navnet på den datamodel, der bruges. Når du bruger denne metode, vil værdien altid være “ThisWorkbookDataModel”
• “[Tabel1].[By].&[København]” – dette er en MDX -sætning, der refererer til feltelementet i Købenahvn i feltet By fra en tabel kaldet Tabel1.

Formlen i celle E3 er:

=KUBEMEDLEM("ThisWorkbookDataModel";"[Tabel1].[Navn].&[Anne]")

Bemærk, at dette er en struktur, der meget ligner celle G2, som vi så ovenfor. Forskellen er, at vi refererer til feltelementet i Anne i feltet Navn fra en tabel kaldet Tabel1.

Formlen i celle E1 adskiller sig en smule:

=KUBEMEDLEM("ThisWorkbookDataModel";"[Measures].[Summen af Værdi]")

Celle E1 er ikke et felt/dimension der filtreres efter, men den beregning, der skal anvendes. Derfor har den et lidt andet format.

“[Measures].[Summen af Værdi]” – Det er syntaksen til henvisning til en implicit måling, der er oprettet i en pivottabel. Det første element i de firkantede parenteser vil altid være Measures med navnet på beregningen i de andre kantede parenteser.

For KUBE-funktioner er alt i dobbelte citationstegn en tekstværdi og kan kobles til en celle eller sammenkædes med andre tekststrenge.

KUBEVÆRDI

KUBEVÆRDI-formlen henter værdier fra datamodellen baseret på filterkonteksten, der er oprettet af de KUBEMEDLEM-funktioner, den er knyttet til.

Formlen i celle G3 er:

=KUBEVÆRDI("ThisWorkbookDataModel";$E$1;$E3;G$2)

Denne formel opdeles som følger:

• =KUBEVÆRDI – funktionens navn
• “ThisWorkbookDataModel” – refererer til navnet på den datamodel, der bruges
• $E$1;$E3;G$2 – disse er alle cellereferencer til de relevante KUBEMEDLEM-funktioner ovenfor

KUBEMEDLEM-funktionen indeholder de felter, der skal bruges, og KUBEVÆRDI henter værdien for denne filterkontekst.

Bemærk:

Det er muligt at ændre henvisningen til den beregning, der skal udføres, f.eks. Ved at ændre fra [Measure].[Summen af Værdi] til [Measure].[Maksimum af Værdi]. Der er dog en finurlighed at være opmærksom på.

Medmindre du skriver eksplicitte målinger ved hjælp af DAX i PowerPivot, skal alle implicitte beregninger oprettes i en pivottabel, før der henvises til det i en formel. Hvis vi ønskede at bruge [Measures].[Maksimum af Værdier], skulle vi først oprette en pivottabel med denne beregning inkluderet.

Når den er blevet oprettet, er den implicitte måling/beregning tilgængelig for CUBE -funktionerne.

Hvad er bedst?

Hvilken er den bedste løsning at bruge? Uheldigvis,…. det kommer an på.

Det bedste er i forhold til dit scenario. Da datamodellen er mere effektiv til at håndtere data end Pivot Cache, ville det altid være min foretrukne metode. Plus, der er den ekstra fordel, at det er et springbræt til at bruge PowerPivot-værktøjet i Excel.

Dette var en introduktion til forskellene mellem GETPIVOTDATA- og KUBE-funktionerne, men afslut ikke din rejse her, der er så meget mere at lære.

Jeg vil prøve at udarbejde lidt flere artikler om dette spændende emne.