Detta är ett annat exempel på formelhastighet. Säg att du måste göra 12 kolumner i VLOOKUP. Du kan göra det snabbare genom att använda en MATCH och 12 INDEX-funktioner.
I följande bild måste du göra 12 VLOOKUP-funktioner för varje kontonummer. VLOOKUP är kraftfullt, men det tar mycket tid att göra beräkningar.
Dessutom måste formeln redigeras i varje cell när du kopierar över. Det tredje argumentet måste ändras från 2 till 3 för februari, sedan 4 för mars och så vidare.
En lösning är att lägga till en rad med kolumnnumren. Sedan kan det tredje argumentet i VLOOKUP peka på den här raden. Åtminstone kan du kopiera samma formel från B4 och klistra in till C4: M4 innan du kopierar hela uppsättningen.
Men här är ett mycket snabbare tillvägagångssätt. Lägg till en ny kolumn B med Var? som rubrik. Kolumn B innehåller en MATCH-funktion. Denna funktion liknar mycket VLOOKUP: Du letar efter värdet i A4 i kolumnen P4: P227. 0 i slutet är som False i slutet av VLOOKUP. Den anger att du vill ha en exakt matchning. Här är den stora skillnaden: MATCH returnerar där värdet hittas. Svaret från 208 säger att A308 är den 208: e cellen i intervallet P4: P227. Från ett omräkningstidsperspektiv är MATCH och VLOOKUP ungefär lika.
Jag kan höra vad du tänker. ”Vad är det bra att veta var något finns? Jag har aldrig låtit en chef ringa upp och fråga, "Vilken rad är det som går att få?" "
Medan människor sällan frågar vilken rad något är i, kan INDEX-funktionen använda den positionen. Följande formel säger att Excel ska returnera det 208: e objektet från Q4: Q227.
När du kopierar den här formeln flyttas värden matris över uppslagstabellen. För varje rad gör du en MATCH och 12 INDEX-funktioner. INDEX-funktionen är otroligt snabb jämfört med VLOOKUP. Hela uppsättningen formler beräknas 85% snabbare än 12 kolumner med VLOOKUP.
Kolla på video
- Säg att du måste göra 12 kolumner i VLOOKUP
- Använd försiktigt ett enda dollartecken före kolumnen med uppslagsvärdet
- Använd försiktigt fyra dollartecken för uppslagstabellen
- Du kodar fortfarande det tredje kolumnargumentet.
- En vanlig lösning är att lägga till en rad hjälparceller med kolumnnummer.
- En annan mindre effektiv lösning är att använda COLUMN (B2) inuti VLOOKUP-formeln.
- Men att göra 12 VLOOKUP för varje rad är mycket ineffektivt
- Lägg istället till en hjälpkolumn med rubriken WHERE och gör en enda matchning.
- MATCHEN tar lika lång tid som VLOOKUP för januari.
- Du kan sedan använda 12 INDEX-funktioner. Dessa är otroligt snabba jämfört med VLOOKUP.
- INDEX pekar på en enda kolumn med svar med $ före raderna.
- INDEX pekar på hjälpkolumnen med $ före kolumnen.
Videoutskrift
Lär dig Excel från podcast, avsnitt 2028 - Ersätt många VLOOKUPs med en MATCH!
Klicka på det "i" i det övre högra hörnet för att komma till spellistan, jag podcastar hela denna bok!
Hej, välkommen tillbaka till netcast, jag heter Bill Jelen! Tja, det är ett klassiskt problem, vi måste göra VLOOKUP en gång för varje månad, eller hur? Och du kan vara otroligt försiktig här när du trycker på F4 tre gånger för att låsa det ner till kolumnen och sedan trycka på F4 en gång i låset hela raden. Men när du kommer till den här punkten är, 2, FALSE att 2 är hårdkodad, och när du kopierar det över, måste du redigera 2 till en 3, eller hur? Nu är ett ineffektivt sätt att göra detta, ett sätt som jag inte gillar att använda kolumnen B1. Kolumn B1 är naturligtvis 2, men när du kopierar det över ser du att det kommer att ändras till kolumn C1, vilket är 3, men tänk på det här, det här räknar ständigt ut kolumnnumret om och om igen. Så vad jag ser människor gör och varför, du vet, föredrar mer än kolumnerna, är att vi Ctrl-drar det,placera siffrorna 2-13 där uppe i en hjälpcell, och sedan, när vi kommer till den här punkten, går vi upp och anger kolumnnumret. Tryck på F4 två gånger för att låsa den på raden,, FALSE och så vidare. Men även med den metoden är VLOOKUP otroligt ineffektivt, för det måste söka igenom alla dessa objekt här tills den hittar A308 och det är figuren B4. När den sedan flyttar över till C4 glömmer den att den bara gick och tittade, och den börjar om igen, okej. Så du har en av de långsammaste funktionerna i hela Excel, VLOOKUP, FALSE görs om och om och om igen för samma objekt.eftersom det måste gå igenom alla dessa objekt här tills den hittar A308 och det är figuren B4. När den sedan flyttar över till C4 glömmer den att den bara gick och tittade, och den börjar om igen, okej. Så du har en av de långsammaste funktionerna i hela Excel, VLOOKUP, FALSE görs om och om och om igen för samma objekt.eftersom det måste gå igenom alla dessa objekt här tills den hittar A308 och det är figuren B4. När den sedan flyttar över till C4 glömmer den att den bara gick och tittade, och den börjar om igen, okej. Så du har en av de långsammaste funktionerna i hela Excel, VLOOKUP, FALSE görs om och om och om igen för samma objekt.
Så här är det mycket, mycket snabbare sättet att gå, vi ska infoga en hjälparpelare, och den här hjälpkolumnen kallar jag det Var? Som i var heck är A308? Vi använder a = MATCH, letar efter A308 i den första raden i tabellen, trycker på F4 där,, 0 för en exakt matchning, okej, det säger oss att ”Hej, titta på det, det är i rad, 6, hur fantastiskt är det? ” Men när vi kopierar ner ser det att det är på olika platser hela tiden. Okej, nu tar den här matchen så länge som VLOOKUP i januari tar, där är de döda, men här är det fantastiska. Därifrån behöver vi aldrig göra en VLOOKUP för resten av raden, vi kan bara göra = INDEX, INDEX säger "Här är en rad svar." Jag ska gå till januari-cellerna, och jag kommer mycket försiktigt här trycka F4 två gånger så jag låser ner den till 4: 227,men Q får ändras när jag rör mig. Komma, och sedan vill det veta vilken rad, ja det kommer att vara svaret i B4, jag trycker på F4 tre gånger för att få $ före B, okej, kopiera det över.
Denna formel, dessa INDEX-formler, dessa 12 kommer att hända på mindre än den tid det skulle ta att göra VLOOKUP i februari, okej. Om vi lägger Charles Williams timer på det här kommer hela saken att beräkna ungefär 14% av tiden på 12 VLOOKUPs. Din chef vill inte se vart? Bra, dölj bara den kolumnen, allt fortsätter att fungera, okej, det här är ett vackert sätt att påskynda de 12 månaderna eller 52 veckorna med VLOOKUPs. Okej, det här tipset och så många fler tips finns i den här boken. Klicka på “i” i det övre högra hörnet där, du kan köpa boken, $ 10 e-bok, $ 25 för den tryckta boken, okej.
Så idag hade vi ett problem där 12 kolumner i VLOOKUP, du kan försiktigt sätta in $, men då måste det tredje argumentet fortfarande vara hårdkodat. Du kan använda kolumn (B2), jag är inte ett fan av det, för det finns hundratals rader * 12 kolumner där det beräknas om och om igen. Använd bara en hjälparcell i rad, sätt siffrorna 2-12 och peka på det, det är fortfarande ineffektivt, för VLOOKUP efter att det räknat ut i januari måste det börja tillbaka i början för februari. Så jag rekommenderar att du lägger till en kolumn med rubriken "Var?" och gör en enda MATCH där. Den MATCHEN tar lika lång tid som VLOOKUP för januari, men då tar de 12 INDEX-funktionerna mindre tid än VLOOKUP för februari, och du har trimmat en hel massa tid. Återigen, var försiktig med $ i INDEX-funktionen på båda platserna, en strax före raderna,och den andra före kolumnerna, en blandad referens i båda.
Hej, jag vill tacka dig för att du kom förbi, vi ses nästa gång för en ny netcast från!
Nedladdning fil
Ladda ner exempelfilen här: Podcast2028.xlsx
