Undvik VLOOKUP med hjälp av datamodellen. Så du har två tabeller som måste kopplas till VLOOKUP innan du kan göra en pivottabell. Om du har Excel 2013 eller senare på en Windows-dator kan du nu göra det enkelt och enkelt.
Säg att du har en datamängd med produkt-, kund- och försäljningsinformation.
IT-avdelningen glömde att lägga in sektor där. Här är en uppslagstabell som kartlägger kund till sektor. Dags för en VLOOKUP, eller hur?
Det finns ingen anledning att göra VLOOKUP för att gå med i dessa datauppsättningar om du har Excel 2013 eller Excel 2016. Båda dessa versioner av Excel har införlivat Power Pivot-motorn i Excel-kärnan. (Du kan också göra detta med Power Pivot-tillägget för Excel 2010, men det finns några extra steg.)
Använd både Hem, Format som tabell i både den ursprungliga datamängden och uppslagstabellen. På fliken Tabellverktyg byter du namn på tabellen från Tabell 1 till något meningsfullt. Jag har använt Data och sektorer.
Välj en cell i datatabellen. Välj Infoga, pivottabell. Från och med Excel 2013 finns det en extra ruta Lägg till dessa data i datamodellen som du ska välja innan du klickar på OK.
Listan Pivottabellfält visas med fälten från datatabellen. Välj Intäkter. Eftersom du använder datamodellen visas en ny rad högst upp i listan som erbjuder Aktiv eller Alla. Klicka på Alla.
Överraskande erbjuder listan Pivottabellfält alla andra tabeller i arbetsboken. Detta är banbrytande. Du har inte gjort en VLOOKUP än. Expandera Sektortabellen och välj Sektor. Två saker händer för att varna dig för att det finns ett problem.
Först visas pivottabellen med samma nummer i alla celler.
Den mer subtila varningen kanske är att en gul ruta visas högst upp i listan Pivottabellfält som indikerar att du måste skapa en relation. Välj Skapa. (Om du är i Excel 2010 eller 2016, lycka till med Auto-Detect.)
I dialogrutan Skapa förhållande har du fyra rullgardinsmenyer. Välj data under tabell, kund under kolumn (utländsk) och sektorer under relaterad tabell. Power Pivot fyller automatiskt i matchande kolumn under Relaterad kolumn (Primär). Klicka på OK.
Den resulterande pivottabellen är en sammanblandning av originaldata och uppslagstabellen. Inga VLOOKUPs krävs.
Kolla på video
- Från och med Excel 2013 erbjuder dialogrutan Pivottabell datamodellen
- Detta är kodordet för Power Pivot Engine
- För att använda datamodellen gör du en Ctrl + T-tabell från varje tabell i arbetsboken
- Bygg en pivottabell från första tabellen
- Ändra från Aktiv till Alla i fältlistan för pivottabell
- Välj ett fält från uppslagstabellen
- Antingen skapa relationen eller Auto-Detect
- Auto-Detect var inte där 2013
- Tack till Colin Michael och Alejandro Quiceno för att de föreslog Power Pivot i allmänhet.
Videoutskrift
Lär dig Excel från podcast, avsnitt 2014 - Eliminera VLOOKUP!
Podcasting av hela denna bok, klicka på “i” längst upp till höger för spellistan!
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen, detta heter faktiskt Eliminera VLOOKUP med datamodellen! Nu ber jag om ursäkt, det här är Excel 2013 och nyare. Om du är tillbaka i Excel 2010 måste du ladda ner Power Pivot-tillägget, vilket naturligtvis är gratis tillbaka 2010. Så vad vi har här är att vi har vår huvuddata, det finns ett kundfält här, och sedan har jag en liten tabell som kartlägger kund till sektor, jag måste skapa totala intäkter efter sektor, eller hur? Det här är en VLOOKUP, gör bara en VLOOKUP, men hej, tack vare Excel 2013 behöver vi inte göra en VLOOKUP! Jag gjorde båda dessa till en tabell, och på Tabellverktyg, Design, döper jag om tabellerna, jag kallar den här sektorerna och jag kallar den här Data, för att göra den till en tabell, välj bara en cell, tryck Ctrl + T. Så om vi har några rubriker och några siffror, när du trycker på Ctrl + T,de frågar ”Var finns data för din tabell?”, Min tabell har rubriker, och sedan kallar de det Table3, du kallar det något annat. Okej, det är så jag skapade dessa två bord, jag ska bli av med den här tabellen, okej.
Så för att det här tricket ska fungera måste all data leva i tabeller. Vi går till fliken Infoga, välj pivottabell, och här nere längst ner, lägg till dessa data i datamodellen. Det här låter väldigt oskyldigt, eller hur? Det finns inget som blinkar som säger "Hej, det låter dig göra fantastiska saker!" Och vad de säger här, vad de försöker att inte säga är att - Åh, förresten, varje kopia av Excel 2013 har Power Pivot-motorn bakom sig. Du vet, om du är i Office 365 betalar du $ 10 per månad, och de vill att du ska betala $ 12 eller $ 15 per månad för att få Power Pivot, de extra två eller fem dollar. Tja, hej, shh, berätta inte, du har faktiskt det mesta av Power Pivot redan i Excel 2013. Okej, så jag klickar på OK, tar lite längre tid att ladda datamodellen, okej, men det är OK, och rätt över här,i pivottabellfälten har jag en lista över alla fälten. Så jag vill verkligen visa intäkter, men vad som är annorlunda är här med Active and All. När jag väljer Alla får jag alla tabeller i arbetsboken. Okej, så jag går till sektorerna och jag sa att jag vill placera sektor i raderna. Nu kommer initialt rapporten att vara fel, se 6,7 miljoner hela vägen ner, och den här gula varningen här kommer att säga att du måste skapa en relation.och den här gula varningen här kommer att säga att du måste skapa en relation.och den här gula varningen här kommer att säga att du måste skapa en relation.
Okej nu, 2010 med Power Pivot, skulle det bara, det erbjöd AutoDetect, 2013 tog de AutoDetect ut, och 2016 tog de tillbaka AutoDetect, okej? Jag borde visa dig hur CREATE ser ut, men när jag klickar på den här CREATE-knappen, åh ja, det är det, okej, bra. Så från vår första tabelldata har jag ett fält som heter Kund, från de relaterade tabellsektorerna har jag ett fält som heter Kund och sedan klickar du på OK, okej. Men låt mig bara visa dig hur cool AutoDetect är, om du råkar vara 2016, där, de tänkte på det, hur fantastiskt är det, eller hur? Du behöver inte oroa dig för VLOOKUP, och komma faller i slutet, om VLOOKUP gör huvudet ont kommer du att älska datamodellen. Tog de två borden, sammanfogade dem, du vet, som Access skulle göra, antar jag, och skapade ett pivottabell, helt fantastiskt.Så kontrollera datamodellen nästa gång du måste göra en VLOOKUP mellan två tabeller. Nåväl detta och alla de andra 40 tipsen finns i boken. Klicka på det "i" i det övre högra hörnet. Du kan köpa boken, ha en fullständig korsreferens till hela serien av videor, hela augusti, hela september, helvete, vi kanske till och med över i oktober för att få det hela gjort.
Okej, sammanfatta idag: från och med Excel 2013 erbjuder dialogrutan Pivottabell något som kallas datamodellen, det är kodordet för Power Pivot-motorn. Innan du skapar dina pivottabeller, gör Ctrl + T för att skapa en tabell från varje arbetsbok, jag tog extra tid att namnge var och en. Bygg en pivottabell från den första tabellen och gå upp till toppen i fältlistan och byt från Aktiv till Alla. Välj ett fält från uppslagstabellen, så varnar det dig att du antingen måste skapa en relation, eller AutoDetect, 2013, du måste klicka på CREATE. Men det är vad, 4 klick för att skapa det, 5 om du räknar OK-knappen, så riktigt, riktigt lätt att göra.
Okej, Colin, Michael och Alejandro Quiceno föreslog Power Pivot i allmänhet för böckerna, tack vare dem, tack till dig för att du stannade, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2014.xlsx
