Dette er et andet eksempel på formelhastighed. Sig, at du skal udføre 12 kolonner med VLOOKUP. Du kan gøre det hurtigere ved hjælp af en MATCH og 12 INDEX-funktioner.
I den følgende figur skal du udføre 12 VLOOKUP-funktioner for hvert kontonummer. VLOOKUP er stærk, men det tager meget tid at foretage beregninger.
Plus, formlen skal redigeres i hver celle, når du kopierer på tværs. Det tredje argument skal ændres fra 2 til 3 for februar, derefter 4 for marts osv.
En løsning er at tilføje en række med søjlenumrene. Derefter kan det tredje argument i VLOOKUP pege på denne række. I det mindste kan du kopiere den samme formel fra B4 og indsætte til C4: M4, før du kopierer hele sættet ned.
Men her er en meget hurtigere tilgang. Tilføj en ny kolonne B med hvor? som overskrift. Kolonne B indeholder en MATCH-funktion. Denne funktion ligner meget VLOOKUP: Du leder efter værdien i A4 i kolonnen P4: P227. 0 i slutningen er som False i slutningen af VLOOKUP. Det specificerer, at du vil have et nøjagtigt match. Her er den store forskel: MATCH returnerer, hvor værdien findes. Svaret fra 208 siger, at A308 er den 208. celle i området P4: P227. Fra et genberegnet tidsperspektiv er MATCH og VLOOKUP omtrent lige store.
Jeg kan høre, hvad du tænker. “Hvad godt er det at vide, hvor noget er placeret? Jeg har aldrig haft en manager, der ringede op og spurgte: 'Hvilken række kan den modtages i?' "
Mens mennesker sjældent spørger, hvilken række noget er i, kan INDEX-funktionen bruge denne position. Følgende formel fortæller Excel at returnere det 208. element fra Q4: Q227.
Når du kopierer denne formel på tværs, bevæger arrayet af værdier sig over opslagstabellen. For hver række udfører du en MATCH og 12 INDEX-funktioner. INDEX-funktionen er utrolig hurtig sammenlignet med VLOOKUP. Hele sæt formler beregner 85% hurtigere end 12 kolonner med VLOOKUP.
Se video
- Sig, at du skal udføre 12 kolonner med VLOOKUP
- Brug forsigtigt et enkelt dollartegn før kolonnen med opslagsværdien
- Brug forsigtigt fire dollartegn til opslagstabellen
- Du koder stadig det tredje kolonneargument.
- En almindelig løsning er at tilføje en række hjælperceller med søjlenummeret.
- En anden mindre effektiv løsning er at bruge COLUMN (B2) inde i VLOOKUP-formlen.
- Men at lave 12 VLOOKUP for hver række er meget ineffektivt
- I stedet skal du tilføje en hjælpekolonne med overskriften WHERE og lave en enkelt kamp.
- MATCH tager så lang tid som VLOOKUP for januar.
- Du kan derefter bruge 12 INDEX-funktioner. Disse er utroligt hurtige sammenlignet med VLOOKUP.
- INDEX peger på en enkelt kolonne med svar med $ før rækkerne.
- INDEX peger på hjælpekolonnen med $ før kolonnen.
Videoudskrift
Lær Excel fra podcast, afsnit 2028 - Udskiftning af mange VLOOKUP'er med en MATCH!
Klik på det "i" i øverste højre hjørne for at komme til afspilningslisten, jeg podcaster hele denne bog!
Hej, velkommen tilbage til netcast, jeg er Bill Jelen! Nå, det er et klassisk problem, vi skal lave VLOOKUP en gang for hver måned, ikke? Og du kan være utrolig forsigtig her ved at trykke F4 3 gange for at låse den ned til søjlen og derefter trykke på F4 en gang låsen ned hele rækken. Men når du kommer til dette punkt, er 2, FALSK at 2 er hårdkodet, og når du kopierer det på tværs, bliver du nødt til at redigere 2 til en 3, ikke? Nu er en ineffektiv måde at gøre dette på, en måde, som jeg ikke kan lide, at bruge kolonnen B1. Kolonne B1 er naturligvis 2, men når du kopierer det på tværs, skal du se, at det vil ændre sig til kolonnen C1, som er 3, men tænk over dette, dette regner konstant ud kolonnetallet igen og igen. Så hvad jeg ser folk gøre, og hvorfor, ved du, foretrækker mere end kolonnerne, er, at vi Ctrl-trækker det,læg tallene 2-13 deroppe i en hjælpercelle, og så når vi kommer til dette punkt, går vi op og specificerer det kolonnenummer. Tryk på F4 2 gange for at låse den ned til rækken,, FALSE og så videre. Men selv med denne metode er VLOOKUP utrolig ineffektiv, fordi den skal søge gennem alle disse emner her, indtil den finder A308, og det er figuren B4. Når det derefter går over til C4, glemmer det, at det bare gik og så ud, og det starter forfra igen, okay. Så du har en af de langsomste funktioner i hele Excel, VLOOKUP, FALSE gøres igen og igen og igen for den samme vare.fordi det skal søge gennem alle disse emner her, indtil det finder A308, og det er figuren B4. Når det derefter går over til C4, glemmer det, at det bare gik og så ud, og det starter forfra igen, okay. Så du har en af de langsomste funktioner i hele Excel, VLOOKUP, FALSE gøres igen og igen og igen for den samme vare.fordi det skal søge gennem alle disse emner her, indtil det finder A308, og det er figuren B4. Når det derefter går over til C4, glemmer det, at det bare gik og så ud, og det starter forfra igen, okay. Så du har en af de langsomste funktioner i hele Excel, VLOOKUP, FALSE gøres igen og igen og igen for den samme vare.
Så her er den meget, meget hurtigere vej at gå, vi indsætter en hjælpekolonne, og denne hjælpekolonne kalder jeg det Hvor? Som i hvor dælen er A308? Vi bruger a = MATCH, kigger efter A308 i den første række i tabellen, trykker på F4 der,, 0 for et nøjagtigt match, okay, det fortæller os, at ”Hej, se på det, det er i række, 6, hvordan fantastisk er det? ” Men når vi kopierer ned, ser det, det er forskellige steder hele tiden. Okay, nu tager denne kamp, så længe januar VLOOKUP tager, der er de døde, men her er den fantastiske ting. Derfra behøver vi aldrig lave en VLOOKUP resten af rækken, vi kunne bare gøre = INDEX, INDEX siger "Her er en række svar." Jeg skal til januarcellerne, og jeg vil meget forsigtigt trykke her på F4 2 gange, så jeg låser den ned til 4: 227,men Q får lov til at ændre sig, når jeg bevæger mig. Komma, og så vil det vide, hvilken række, ja det bliver svaret i B4, jeg trykker på F4 3 gange for at få $ før B, okay, kopier det over.
Denne formel, disse INDEX-formler, disse 12 vil ske på mindre end den tid, det ville tage at gøre VLOOKUP i februar, okay. Hvis vi sætter Charles Williams-timer på dette, beregner det hele ca. 14% af tiden på 12 VLOOKUP'er. Din manager vil ikke se hvor? Fint, bare skjul den kolonne, alt fortsætter med at fungere, okay, dette er en smuk måde at fremskynde de 12 måneder eller de 52 uger af VLOOKUPs. Okay, dette tip og så mange flere tip findes i denne bog. Klik på “i” i øverste højre hjørne der, du kan købe bogen, $ 10 e-bog, $ 25 for den trykte bog, okay.
Så i dag havde vi et problem, hvor 12 kolonner af VLOOKUP, du kan omhyggeligt lægge $ i, men så skal det 3. argument stadig være hårdkodet. Du kan bruge kolonne (B2), det er jeg ikke fan af, for der er hundreder af rækker * 12 kolonner, hvor det beregnes igen og igen. Brug bare en hjælpercelle i træk, sæt tallene 2-12 og peg på det, det er dog stadig ineffektivt, fordi VLOOKUP, når det har fundet ud af januar, skal starte tilbage i starten til februar. Så jeg anbefaler at tilføje en kolonne med overskriften "Hvor?" og laver en enkelt MATCH der. Denne MATCH tager lige så lang tid som VLOOKUP i januar, men så tager de 12 INDEX-funktioner kortere tid end VLOOKUP i februar, og du har trimmet en hel masse tid. Igen skal du være forsigtig med $ i INDEX-funktionen begge steder, en lige før rækkerne,og den anden foran kolonnerne, en blandet reference i dem begge.
Hej, jeg vil gerne takke dig for at komme forbi, vi ses næste gang til endnu en netcast fra!
Download fil
Download eksempelfilen her: Podcast2028.xlsx
