Unngå VLOOKUP ved bruk av datamodellen. Så du har to bord som må slås sammen med VLOOKUP før du kan gjøre et pivottabell. Hvis du har Excel 2013 eller nyere på en Windows-PC, kan du nå gjøre dette enkelt og enkelt.
Si at du har et datasett med produkt-, kunde- og salgsinformasjon.
IT-avdelingen glemte å sette sektor der inne. Her er en oppslagstabell som kartlegger kunde til sektor. På tide med en VLOOKUP, ikke sant?
Det er ikke nødvendig å gjøre VLOOKUP for å bli med i disse datasettene hvis du har Excel 2013 eller Excel 2016. Begge disse versjonene av Excel har innlemmet Power Pivot-motoren i kjernen i Excel. (Du kan også gjøre dette ved hjelp av Power Pivot-tillegget for Excel 2010, men det er noen få ekstra trinn.)
Bruk både Hjem, Format som tabell i både det originale datasettet og oppslagstabellen. I Tabellverktøy-fanen kan du endre navnet på tabellen fra Tabell 1 til noe meningsfylt. Jeg har brukt data og sektorer.
Velg en celle i datatabellen. Velg Sett inn, pivottabell. Fra og med Excel 2013 er det en ekstra rute Legg til disse dataene i datamodellen som du bør velge før du klikker OK.
Pivottabellfeltlisten vises med feltene fra datatabellen. Velg Inntekt. Fordi du bruker datamodellen, vises en ny linje øverst på listen som tilbyr Aktiv eller Alle. Klikk på Alle.
Overraskende nok har PivotTable Fields-listen alle de andre tabellene i arbeidsboken. Dette er banebrytende. Du har ikke gjort en VLOOKUP ennå. Utvid sektorer-tabellen og velg sektor. To ting skjer for å advare deg om at det er et problem.
Først vises pivottabellen med samme nummer i alle cellene.
Kanskje den mer subtile advarselen er at en gul boks vises øverst i PivotTable Fields-listen som indikerer at du trenger å opprette et forhold. Velg Opprett. (Hvis du er i Excel 2010 eller 2016, ta lykken med Auto-Detect.)
I dialogboksen Opprett forhold har du fire rullegardinmenyer. Velg data under tabell, kunde under kolonne (utenlandsk) og sektorer under relatert tabell. Power Pivot vil automatisk fylle ut den matchende kolonnen under Relatert kolonne (Primær). Klikk OK.
Den resulterende pivottabellen er en blanding av de opprinnelige dataene og oppslagstabellen. Ingen VLOOKUPs kreves.
Se på video
- Fra og med Excel 2013, tilbyr dialogboksen Pivottabell datamodellen
- Dette er kodeordet for Power Pivot Engine
- For å bruke datamodellen lager du en Ctrl + T-tabell fra hver tabell i arbeidsboken
- Bygg et pivottabell fra første bord
- I feltlisten for pivottabeller bytter du fra Aktiv til Alle
- Velg et felt fra oppslagstabellen
- Enten opprett forholdet eller Auto-Detect
- Auto-Detect var ikke der i 2013
- Takk til Colin Michael og Alejandro Quiceno for å foreslå Power Pivot generelt.
Videoutskrift
Lær Excel fra podcast, episode 2014 - Eliminer VLOOKUP!
Podcasting av hele boka, klikk på “i” øverst til høyre for spillelisten!
Hei, velkommen tilbake til netcast, jeg er Bill Jelen, dette heter faktisk Eliminate VLOOKUP med datamodellen! Nå beklager jeg, dette er Excel 2013 og nyere. Hvis du er tilbake i Excel 2010, må du laste ned Power Pivot-tillegget, som selvfølgelig er gratis tilbake i 2010. Så det vi har her er at vi har vår hoveddatasett, det er et kundefelt her, og så har jeg et lite bord som kartlegger kunde til sektor, jeg må skape totale inntekter etter sektor, ikke sant? Dette er en VLOOKUP, bare gjør en VLOOKUP, men hei, takket være Excel 2013, trenger vi ikke å gjøre en VLOOKUP! Jeg lagde begge disse til en tabell, og på Tabellverktøyet, Design, omdøper jeg bordene, jeg kaller denne Sektorer, og jeg kaller denne Data, for å gjøre den til en tabell, velg bare en celle, trykk Ctrl + T. Så hvis vi har noen overskrifter og noen tall, når du trykker på Ctrl + T,de spør ”Hvor er dataene for tabellen din?”, Tabellen min har overskrifter, og så kaller de den Table3, du kaller den noe annet. Ok, det var slik jeg opprettet de to bordene, jeg skal bli kvitt dette bordet, ok.
Så for at dette trikset skal fungere, må all data leve i tabeller. Vi går til Sett inn-fanen, velger Pivottabell, og rett nede her nederst, legg til disse dataene i datamodellen. Dette høres veldig uskadelig ut, ikke sant? Det er ingenting som blinkende punkt som sier "Hei, det lar deg gjøre fantastiske ting!" Og det de sier her, det de prøver å ikke si er at - Åh, forresten, hver kopi av Excel 2013 har Power Pivot-motoren bak seg. Du vet, hvis du er i Office 365, betaler du $ 10 i måneden, og de vil at du skal betale $ 12 eller $ 15 i måneden for å få Power Pivot, de ekstra to eller fem dollarene. Vel, hei, shh, ikke fortell, du har faktisk det meste av Power Pivot allerede i Excel 2013. OK, så jeg klikker OK, tar litt lengre tid å laste inn datamodellen, ok, men det er OK, og rett over her,i PivotTable-feltene har jeg en liste over alle feltene. Så jeg vil absolutt vise Revenue, men det som er annerledes er her oppe med Active and All. Når jeg velger Alle får jeg alle tabellene i arbeidsboken. Ok, så jeg går til sektorene, og jeg sa at jeg vil sette sektor i Rows-området. Nå, i utgangspunktet, kommer rapporten til å være feil, se 6,7 millioner helt ned, og denne gule advarselen her vil si at du må opprette et forhold.og denne gule advarselen her vil si at du må opprette et forhold.og denne gule advarselen her vil si at du må opprette et forhold.
Greit nå, i 2010 med Power Pivot, ville det bare, det tilbød AutoDetect, i 2013 tok de AutoDetect ut, og i 2016 tok de AutoDetect tilbake, ok? Jeg burde vise deg hvordan CREATE ser ut, men når jeg klikker på denne CREATE-knappen, å ja, det er det, ok, bra. Så fra vår første tabelldata har jeg et felt som heter Kunde, fra de relaterte tabell sektorene, jeg har et felt som heter Kunde, og deretter klikker du OK, ok. Men la meg bare vise deg hvor kult AutoDetect er, hvis du tilfeldigvis er i 2016, fant de det ut, hvor fantastisk er det, ikke sant? Du trenger ikke å bekymre deg for VLOOKUP, og kommaet faller på slutten. Hvis VLOOKUP gjør hodet vondt, kommer du til å elske datamodellen. Tok de to bordene, slo dem sammen, vet du, som Access ville gjort, antar jeg, og laget et pivottabell, helt fantastisk.Så sjekk datamodellen neste gang du må gjøre en VLOOKUP mellom to tabeller. Vel, dette og alle de andre 40 tipsene er i boka. Klikk på "i" øverst til høyre. Du kan kjøpe boken, ha en fullstendig kryssreferanse til hele denne serien av videoer, hele august, hele september, pokker, vi kan til og med overføre til oktober for å få det hele gjort.
Greit, oppsummering i dag: Fra og med Excel 2013 tilbyr Pivot Table-dialogen noe som heter Data Model, det er kodeordet for Power Pivot-motoren. Før du oppretter pivottabellene dine, gjør Ctrl + T for å lage en tabell fra hver arbeidsbok, jeg tok ekstra tid til å navngi hver enkelt. Bygg en pivottabell fra den første tabellen, og gå opp til toppen i feltlisten og bytt fra Aktiv til Alle. Velg et felt fra oppslagstabellen, og så vil det advare deg om at du enten må opprette et forhold, eller AutoDetect, i 2013, du må klikke OPPRETT. Men det er hva, 4 klikk for å lage det, 5 hvis du teller OK-knappen, så veldig, veldig enkelt å gjøre.
Greit, Colin, Michael og Alejandro Quiceno foreslo Power Pivot generelt for bøkene, takk til dem, takk til deg for at du var innom, vi sees neste gang for en ny netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2014.xlsx
