Har du ikke Power Pivot? Det betyder ikke noget. Det meste af Power Pivot er indbygget i Excel 2013 og endnu mere i Excel 2016. I dag er vores tip fra Ash at forbinde tabeller i en pivottabel.
Hver onsdag i syv uger præsenterer jeg et af yndlingstipene fra Ash Sharma. Ash er produktchef i Excel-teamet. Hans team giver dig drejeborde og mange andre gode ting. I dag er Ashs yndlingsfunktion at slutte sig til flere datasæt ved hjælp af Relationships og datamodellen.
Sig, at din it-afdeling giver dig datasættet vist i kolonne A: D. Der er felter til kunde og marked. Du skal kombinere bestemte markeder i regioner. Hver kunde tilhører en sektor. Region og sektor er ikke i de originale data, men du har opslagstabeller for at give disse oplysninger.
Normalt ville du flade dataene ved at bruge VLOOKUP til at trække data fra de orange og gule tabeller ind i den blå tabel. Men da nøglefeltet ikke er i venstre side af hver tabel, skal du enten skifte til INDEX og MATCH eller omarrangere opslagstabellerne.
Fra og med Excel 2013 kan du lade opslagstabellerne være, hvor de er, og kombinere dem i selve pivottabellen.
For at denne teknik kan fungere, skal alle tre tabeller være formateret som en tabel. Vælg en celle i hvert datasæt, og vælg Hjem, Formatér som tabel, eller tryk på Ctrl + T. De tre tabeller kaldes oprindeligt Tabel1, Tabel2 og Tabel3. Jeg bruger fanen Design af tabelværktøjer i båndet og omdøber hver tabel om. Jeg ændrer også farven på hver tabel. I dette eksempel kaldes den blå tabel Data. Det orange bord er RegionTable. Det gule bord er Sektortabel.
Bemærk
Nogle vil fortælle dig, at du skal bruge nørdige navne som Fact, TblSector og TblRegion. Hvis nogen besværer dig sådan her, skal du bare stjæle deres lommebeskytter og fortælle dem, at du foretrækker navne, der lyder på engelsk.
For at omdøbe en tabel skal du skrive et nyt navn i feltet til venstre på fanen Design af tabelværktøjer. Tabelnavne bør ikke have mellemrum.
Når de tre tabeller er defineret, skal du gå til fanen Data og klikke på Forhold.
Klik på Ny i dialogboksen Administrer forhold. I dialogboksen Opret forhold skal du angive, at datatabellens kundefelt er relateret til Sektortabelens kundefelt. Klik på OK.
Definer et andet nyt forhold mellem Marked-feltet i felterne Data og RegionTable. Når du har defineret begge forhold, vil du se dem i dialogboksen Administrer forhold.
Tillykke: du har lige bygget en datamodel i din projektmappe. Det er tid til at oprette et pivottabel.
Vælg den tomme celle, hvor din pivottabel skal vises. Som standard vil dialogboksen Opret pivottabel vælge Brug denne projektmappes datamodel. Pivottabelplaceringen vil som standard være den celle, du valgte. Klik på OK.
Listen over pivottabelfelter viser alle tre tabeller. Brug trekanten til venstre for en tabel for at udvide tabelnavnet for at vise dig felterne.
Udvid datatabellen. Vælg indtægtsfeltet. Den flytter automatisk til området Værdier. Udvid SectorTable. Vælg feltet Sektor. Det flytter til rækkeområdet. Udvid RegionTable. Træk feltet Region til området Kolonner. Du får nu en pivottabel, der opsummerer data fra de tre tabeller.
Bemærk
I hver bog, som jeg har skrevet før i dag, bruger jeg en anden teknik til at opbygge denne rapport. Efter at have defineret de tre tabeller vælger jeg celle A1 og Indsæt, drejetabel. Jeg markerer afkrydsningsfeltet for Tilføj disse data til datamodellen. På listen Pivottabelfelter skal du vælge Alt øverst på listen. Vælg felter til rapporten, og definer derefter forholdet efter kendsgerningen. Den ovenfor beskrevne teknik virker glattere og involverer faktisk en lille smule planlægning fremad. De mennesker, der bruger Option Explicit i deres VBA-kode, vil bestemt gerne denne metode.
Forholdene i datamodellen får Excel til at føle sig mere som Access eller SQL Server, men med al godhed i Excel.
Jeg elsker at bede Excel-teamet om deres yndlingsfunktioner. Hver onsdag deler jeg et af deres svar. Tak til Ash Sharma for at levere denne idé.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke at tænke over:
"Slå ikke op, hvis du er i et forhold"
John Michaloudis
