Har du ikke Power Pivot? Gjør ikke noe. Det meste av Power Pivot er innebygd i Excel 2013 og enda mer i Excel 2016. I dag er vårt tips fra Ash å koble sammen tabeller i en pivottabell.
Hver onsdag i syv uker har jeg et av favorittipsene fra Ash Sharma. Ash er produktsjef i Excel-teamet. Teamet hans gir deg svingbord og mange andre gode ting. I dag er Ashs favorittfunksjon å bli med i flere datasett ved hjelp av Relationships og Data Model.
Si at IT-avdelingen gir deg datasettet vist i kolonne A: D. Det er felt for kunde og marked. Du må kombinere visse markeder i regioner. Hver kunde tilhører en sektor. Region og sektor er ikke i de opprinnelige dataene, men du har oppslagstabeller for å gi denne informasjonen.
Normalt vil du flate dataene ved å bruke VLOOKUP for å trekke data fra de oransje og gule tabellene til den blå tabellen. Men siden nøkkelfeltet ikke er på venstre side av hver tabell, må du enten bytte til INDEKS og MATCH, eller omorganisere oppslagstabellene.
Fra og med Excel 2013 kan du la oppslagstabellene være der og kombinere dem i selve pivottabellrapporten.
For at denne teknikken skal fungere, må alle tre tabellene være formatert som en tabell. Velg en celle i hvert datasett og velg Hjem, Format som tabell eller trykk Ctrl + T. De tre tabellene vil i utgangspunktet hete Tabell 1, Tabell 2 og Tabell 3. Jeg bruker Tabellverktøy-designfanen på båndet og gir hvert bord nytt navn. Jeg endrer også fargen på hvert bord. I dette eksemplet kalles den blå tabellen Data. Det oransje bordet er RegionTable. Det gule bordet er SectorTable.
Merk
Noen vil fortelle deg at du bør bruke nerdete navn som Fact, TblSector og TblRegion. Hvis noen plager deg slik, er det bare å stjele lommebeskytteren og gi beskjed om at du foretrekker navn på engelsk.
For å gi nytt navn til en tabell, skriv inn et nytt navn i boksen på venstre side av kategorien Design for tabellverktøy. Tabellnavn skal ikke ha mellomrom.
Når de tre tabellene er definert, går du til Data-fanen og klikker på Relasjoner.
Klikk Ny i dialogboksen Administrer forhold. I dialogboksen Opprett forhold, spesifiser at datatabellens kundefelt er relatert til Sektorbordets kundefelt. Klikk OK.
Definer et nytt forhold mellom Markedsfeltet i feltene Data og RegionTable. Når du har definert begge relasjonene, vil du se dem i dialogboksen Administrer forhold.
Gratulerer: du har nettopp bygd en datamodell i arbeidsboken din. Det er på tide å bygge et pivottabell.
Velg den tomme cellen der du vil at pivottabellen skal vises. Som standard velger dialogboksen Opprett pivottabell Bruk denne arbeidsbokens datamodell. Pivottabellplasseringen vil som standard være cellen du valgte. Klikk OK.
Pivottabellfeltlisten viser alle tre tabellene. Bruk trekanten til venstre for en tabell for å utvide tabellnavnet for å vise deg feltene.
Utvid datatabellen. Velg Inntektsfelt. Den flyttes automatisk til Verdier-området. Utvid SectorTable. Velg Sektor-feltet. Den vil flytte til radområdet. Utvid RegionTable. Dra Region-feltet til Kolonner-området. Du vil nå ha en pivottabell som oppsummerer data fra de tre tabellene.
Merk
I hver bok jeg har skrevet før i dag, bruker jeg en annen teknikk for å lage denne rapporten. Etter å ha definert de tre tabellene, velger jeg celle A1 og Sett inn, pivottabell. Jeg merker av for Legg til disse dataene i datamodellen. I listen Pivottabellfelt velger du Alt øverst på listen. Velg felt for rapporten, og definer deretter forholdet etter faktum. Teknikken beskrevet ovenfor virker jevnere og innebærer faktisk en liten planlegging fremover. Folk som bruker Option Explicit i VBA-koden deres, vil definitivt like denne metoden.
Forholdene i datamodellen gjør at Excel føles mer som Access eller SQL Server, men med all godheten til Excel.
Jeg elsker å spørre Excel-teamet om deres favorittfunksjoner. Hver onsdag vil jeg dele et av svarene deres. Takk til Ash Sharma som leverte denne ideen.
Excel-tanken om dagen
Jeg har bedt mine Excel Master-venner om deres råd om Excel. Dagens tanke å tenke på:
"Ikke slå opp hvis du er i et forhold"
John Michaloudis
