Undgå VLOOKUP ved hjælp af datamodellen. Så du har to tabeller, der skal sammenføjes med VLOOKUP, før du kan lave en pivottabel. Hvis du har Excel 2013 eller nyere på en Windows-pc, kan du nu gøre dette enkelt og nemt.
Sig, at du har et datasæt med produkt-, kunde- og salgsoplysninger.
IT-afdelingen glemte at sætte sektor derinde. Her er en opslagstabel, der kortlægger kunde til sektor. Tid til en VLOOKUP, ikke?
Det er ikke nødvendigt at gøre VLOOKUP'er for at deltage i disse datasæt, hvis du har Excel 2013 eller Excel 2016. Begge disse versioner af Excel har indarbejdet Power Pivot-motoren i kernen i Excel. (Du kan også gøre dette ved hjælp af Power Pivot-tilføjelsesprogrammet til Excel 2010, men der er et par ekstra trin.)
Brug både Hjem, Format som tabel i både det originale datasæt og opslagstabellen. Omdøb tabellen fra tabel1 til fanen Tabelværktøjer til noget meningsfuldt. Jeg har brugt data og sektorer.
Vælg en celle i datatabellen. Vælg Indsæt, drejetabel. Fra og med Excel 2013 er der et ekstra felt Tilføj disse data til datamodellen, som du skal vælge, før du klikker på OK.
Listen med pivottabelfelter vises med felterne fra datatabellen. Vælg indtægter. Fordi du bruger datamodellen, vises en ny linje øverst på listen, der tilbyder Aktiv eller Alle. Klik på Alle.
Overraskende nok indeholder listen PivotTable Fields alle de andre tabeller i projektmappen. Dette er banebrydende. Du har ikke lavet en VLOOKUP endnu. Udvid tabellen Sektorer, og vælg Sektor. To ting sker for at advare dig om, at der er et problem.
For det første vises pivottabellen med det samme nummer i alle cellerne.
Måske er den mere subtile advarsel, at der vises en gul boks øverst på listen Pivottabelfelter, der indikerer, at du skal oprette et forhold. Vælg Opret. (Hvis du er i Excel 2010 eller 2016, skal du være heldig med Auto-Detect.)
I dialogboksen Opret forhold har du fire rullemenuer. Vælg data under tabel, kunde under kolonne (udenlandsk) og sektorer under relateret tabel. Power Pivot udfylder automatisk den matchende kolonne under den relaterede kolonne (primær). Klik på OK.
Den resulterende pivottabel er en sammenblanding af de originale data og opslagstabellen. Ingen VLOOKUP'er krævet.
Se video
- Fra og med Excel 2013 tilbyder dialogboksen Pivottabel datamodellen
- Dette er kodeordet for Power Pivot Engine
- For at bruge datamodellen skal du oprette en Ctrl + T-tabel fra hver tabel i projektmappen
- Byg en pivottabel fra den første tabel
- Skift fra Aktiv til Alle på feltlisten for pivottabel
- Vælg et felt fra opslagstabellen
- Enten opret forholdet eller Auto-Detect
- Auto-Detect var ikke der i 2013
- Tak til Colin Michael og Alejandro Quiceno for at foreslå Power Pivot generelt.
Videoudskrift
Lær Excel fra podcast, episode 2014 - Fjern VLOOKUP!
Podcasting af hele denne bog, klik på "i" i øverste højre hjørne for afspilningslisten!
Hej, velkommen tilbage til netcast, jeg er Bill Jelen, dette kaldes faktisk Eliminer VLOOKUP med datamodellen! Nu undskylder jeg, dette er Excel 2013 og nyere. Hvis du er tilbage i Excel 2010, skal du downloade Power Pivot-tilføjelsesprogrammet, som selvfølgelig er gratis tilbage i 2010. Så hvad vi har her er, at vi har vores hoveddatasæt, der er et kundefelt her, og så har jeg en lille tabel, der kortlægger kunde til sektor, jeg skal skabe samlede indtægter efter sektor, ikke? Dette er en VLOOKUP, bare gør en VLOOKUP, men hej takket være Excel 2013 behøver vi ikke lave en VLOOKUP! Jeg lavede begge disse til en tabel, og på tabelværktøjerne, design, omdøber jeg tabellerne, jeg kalder denne sektor, og jeg kalder den ene data for at gøre den til en tabel skal du bare vælge en celle, trykke på Ctrl + T. Så hvis vi har nogle overskrifter og nogle tal, når du trykker på Ctrl + T,de spørger ”Hvor er dataene til din tabel?”, Min tabel har overskrifter, og så kalder de det Table3, du kalder det noget andet. Okay, sådan oprettede jeg disse to borde, jeg skal slippe af med denne tabel, okay.
Så for at dette trick kan fungere, skal alle dataene leve i tabeller. Vi går til fanen Indsæt, vælg Pivottabel, og lige her nederst, tilføj disse data til datamodellen. Det lyder meget uskadeligt, ikke? Der er intet som blinkende punkt, der siger "Hej, det giver dig mulighed for at gøre fantastiske ting!" Og hvad de siger her, hvad de prøver ikke at sige er, at - Åh, forresten, hver kopi af Excel 2013 har Power Pivot-motoren bag sig. Du ved, hvis du er i Office 365, betaler du $ 10 om måneden, og de vil have dig til at betale $ 12 eller $ 15 om måneden for at få Power Pivot, de ekstra to eller fem dollars. Nå, hej, shh, fortæl det ikke, du har faktisk det meste af Power Pivot allerede i Excel 2013. Okay, så jeg klikker på OK, tager lidt længere tid at indlæse datamodellen, okay, men det er OK, og lige over her,i PivotTable-felterne har jeg en liste over alle felterne. Så jeg vil bestemt vise indtægter, men hvad der er anderledes, er her oppe med Active and All. Når jeg vælger Alt, får jeg alle tabellerne i projektmappen. Okay, så jeg går til sektorerne, og jeg sagde, at jeg vil sætte sektor i Rows-området. Nu oprindeligt vil rapporten være forkert, se 6,7 millioner helt ned, og denne gule advarsel herover vil sige, at du er nødt til at skabe et forhold.og denne gule advarsel herover vil sige, at du er nødt til at skabe et forhold.og denne gule advarsel herover vil sige, at du er nødt til at skabe et forhold.
Okay, i 2010 med Power Pivot, ville det bare, det tilbød AutoDetect, i 2013 tog de AutoDetect ud, og i 2016 bragte de AutoDetect tilbage, okay? Jeg skulle vise dig, hvordan CREATE ser ud, men når jeg klikker på denne CREATE-knap, åh ja, det er det, okay, godt. Så fra vores første tabeldata har jeg et felt kaldet kunde, fra de relaterede tabel sektorer har jeg et felt kaldet kunde, og derefter klikker du på OK, okay. Men lad mig bare vise dig, hvor sej AutoDetect er, hvis du tilfældigvis er i 2016, der regnede de det ud, hvor fantastisk er det, ikke? Du behøver ikke bekymre dig om VLOOKUP, og kommaet falder i slutningen, hvis VLOOKUP gør dit hoved ondt, vil du elske datamodellen. Tog disse to borde, sluttede dem sammen, du ved, som Access ville gøre, antager jeg, og skabte et Pivot-bord, helt fantastisk.Så tjek datamodellen næste gang du skal lave en VLOOKUP mellem to tabeller. Nå dette og alle de andre 40 tip findes i bogen. Klik på det "i" øverst til højre. Du kan købe bogen, have en komplet krydshenvisning til hele denne serie af videoer, hele august, hele september, helvede, vi kan endda overføre til oktober for at få det hele gjort.
Okay, resumé i dag: Fra og med Excel 2013 tilbyder Pivot Table-dialogen noget, der hedder datamodellen, det er kodeordet for Power Pivot-motoren. Inden du opretter dine pivottabeller, skal du gøre Ctrl + T for at lave en tabel fra hver projektmappe, jeg tog den ekstra tid til at navngive hver enkelt. Byg en pivottabel fra den første tabel, og gå derefter op til toppen i feltlisten, og skift fra Aktiv til Alle. Vælg et felt fra opslagstabellen, og så advarer det dig om, at du enten skal oprette et forhold eller AutoDetect i 2013 skal du klikke på Opret. Men det er hvad, 4 klik for at oprette det, 5 hvis du tæller OK-knappen, så virkelig, virkelig let at gøre.
Okay, Colin, Michael og Alejandro Quiceno foreslog Power Pivot generelt til bøgerne, takket være dem, tak til dig for at du kom forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2014.xlsx
