Dette er et annet eksempel på hastighetsformel. Si at du må gjøre 12 kolonner med VLOOKUP. Du kan gjøre det raskere ved å bruke en MATCH og 12 INDEX-funksjoner.
I den følgende figuren må du utføre 12 VLOOKUP-funksjoner for hvert kontonummer. VLOOKUP er kraftig, men det tar mye tid å gjøre beregninger.
I tillegg må formelen redigeres i hver celle når du kopierer over. Det tredje argumentet må endres fra 2 til 3 for februar, deretter 4 for mars og så videre.
En løsning er å legge til en rad med kolonnetallene. Deretter kan det tredje argumentet til VLOOKUP peke på denne raden. I det minste kan du kopiere den samme formelen fra B4 og lime til C4: M4 før du kopierer hele settet ned.
Men her er en mye raskere tilnærming. Legg til en ny kolonne B med Hvor? som overskrift. Kolonne B inneholder en MATCH-funksjon. Denne funksjonen ligner veldig på VLOOKUP: Du ser etter verdien i A4 i kolonnen P4: P227. 0 på slutten er som False på slutten av VLOOKUP. Den spesifiserer at du vil ha en nøyaktig samsvar. Her er den store forskjellen: MATCH returnerer der verdien blir funnet. Svaret fra 208 sier at A308 er den 208. cellen i området P4: P227. Fra et beregningstidsperspektiv er MATCH og VLOOKUP omtrent like.
Jeg kan høre hva du tenker. “Hva er det bra å vite hvor noe ligger? Jeg har aldri hatt en leder som ringte opp og spurte: "Hvilken rad er det å motta i?" "
Mens mennesker sjelden spør hvilken rad noe er i, kan INDEX-funksjonen bruke den posisjonen. Følgende formel forteller Excel å returnere det 208. elementet fra Q4: Q227.
Når du kopierer denne formelen, beveger verdiene seg over oppslagstabellen. For hver rad gjør du en MATCH og 12 INDEX-funksjoner. INDEX-funksjonen er utrolig rask sammenlignet med VLOOKUP. Hele settet med formler vil beregne 85% raskere enn 12 kolonner med VLOOKUP.
Se på video
- Si at du må gjøre 12 kolonner med VLOOKUP
- Bruk forsiktig et enkelt dollartegn før kolonnen med oppslagsverdien
- Bruk forsiktig fire dollartegn for oppslagstabellen
- Du koder fortsatt argumentet for den tredje kolonnen.
- En vanlig løsning er å legge til en rad hjelperceller med kolonnenummeret.
- En annen mindre effektiv løsning er å bruke COLUMN (B2) i VLOOKUP-formelen.
- Men å gjøre 12 VLOOKUP for hver rad er veldig ineffektivt
- I stedet legger du til en hjelpekolonne med overskriften WHERE og gjør en enkelt kamp.
- KAMPEN tar like lang tid som VLOOKUP for januar.
- Deretter kan du bruke 12 INDEX-funksjoner. Disse er utrolig raske sammenlignet med VLOOKUP.
- INDEKSEN vil peke på en enkelt kolonne med svar med $ før radene.
- INDEKSEN peker på hjelpekolonnen med $ før kolonnen.
Videoutskrift
Lær Excel fra podcast, episode 2028 - Bytte ut mange VLOOKUP-er med en MATCH!
Klikk på “i” øverst til høyre for å komme til spillelisten, jeg podcaster hele boka!
Hei, velkommen tilbake til netcast, jeg er Bill Jelen! Vel, det er et klassisk problem, vi må gjøre VLOOKUP en gang for hver måned, ikke sant? Og du kan være utrolig forsiktig her med å trykke F4 tre ganger for å låse den ned til kolonnen, og deretter trykke F4 en gang låsen nedover hele raden. Men når du kommer til dette punktet, er 2, FALSE at 2 er hardkodet, og når du kopierer det over, må du redigere 2 til 3, ikke sant? Nå, en ineffektiv måte å gjøre dette på, en måte som jeg ikke liker, er å bruke kolonnen til B1. Kolonne B1 er selvfølgelig 2, men når du kopierer den over, se at den vil endre seg til kolonnen C1, som er 3, men tenk på dette, dette er å finne ut kolonnetallet igjen og igjen. Så det jeg ser folk gjør, og hvorfor, vet du, foretrekker mer enn kolonnene, er at vi Ctrl-drar det,legg tallene 2-13 der oppe i en hjelpercelle, og når vi kommer til dette punktet, går vi opp og spesifiserer det kolonnenummeret. Trykk på F4 to ganger for å låse den ned på raden,, FALSE og så videre. Men selv med den metoden er VLOOKUP utrolig ineffektiv, fordi den må søke gjennom alle disse elementene her til den finner A308 og det er figuren B4. Når den deretter går over til C4, glemmer den at den bare gikk og så ut, og den starter på nytt, ok. Så du har en av de tregeste funksjonene i hele Excel, VLOOKUP, FALSE gjøres om og om igjen for det samme elementet.fordi den må søke gjennom alle disse elementene her til den finner A308 og det er figuren B4. Når den deretter går over til C4, glemmer den at den bare gikk og så ut, og den starter på nytt, ok. Så du har en av de tregeste funksjonene i hele Excel, VLOOKUP, FALSE blir gjort om og om igjen for det samme elementet.fordi den må søke gjennom alle disse elementene her til den finner A308 og det er figuren B4. Når den deretter går over til C4, glemmer den at den bare gikk og så ut, og den starter på nytt, ok. Så du har en av de tregeste funksjonene i hele Excel, VLOOKUP, FALSE gjøres om og om igjen for det samme elementet.
Så her er den mye, raskere veien å gå, vi skal sette inn en hjelpekolonne, og denne hjelpekolonnen kaller jeg det Hvor? Som i hvor pokker er A308? Vi bruker a = MATCH, ser etter A308 i første rad i tabellen, trykker F4 der,, 0 for en nøyaktig samsvar, ok, det forteller oss at “Hei, se på det, det er i rad, 6, hvordan fantastisk er det? " Men når vi kopierer ned, ser det, det er på forskjellige steder hele tiden. Ok, nå tar denne kampen så lang tid som VLOOKUP i januar tar, der er de døde, men her er den fantastiske tingen. Derfra trenger vi aldri gjøre en VLOOKUP for resten av raden, vi kan bare gjøre = INDEX, INDEX sier "Her er en rekke svar." Jeg kommer til å gå til januar-cellene, og jeg skal veldig forsiktig trykke F4 to ganger så jeg låser den ned til 4: 227,men Q får lov til å endre seg når jeg beveger meg. Komma, og så vil det vite hvilken rad, vel det kommer til å være svaret i B4, jeg trykker på F4 tre ganger for å få $ før B, ok, kopier det over.
Denne formelen, disse INDEX-formlene, disse 12 vil skje på mindre enn den tiden det ville ta å gjøre VLOOKUP i februar, ok. Hvis vi legger Charles Williams timer på dette, vil hele greia beregne omtrent 14% av tiden på 12 VLOOKUPs. Lederen din vil ikke se hvor? Greit, bare skjul den kolonnen, alt fortsetter å fungere, ok, dette er en vakker måte å øke hastigheten på 12 måneder eller 52 uker med VLOOKUPs. OK, dette tipset og så mange flere tips er i denne boka. Klikk på “i” øverst til høyre der, du kan kjøpe boken, $ 10 e-bok, $ 25 for trykkboken, ok.
Så i dag hadde vi et problem der 12 kolonner med VLOOKUP, du kan sette $ forsiktig inn, men da må det tredje argumentet fortsatt være hardkodet. Du kan bruke kolonne (B2), jeg er ikke en fan av det, for det er hundrevis av rader * 12 kolonner hvor det beregnes om og om igjen. Bare bruk en hjelpercelle på rad, legg tallene 2-12 og pek på det, men det er fortsatt ineffektivt, fordi VLOOKUP etter at det har funnet ut januar, må det starte tilbake i begynnelsen av februar. Så jeg anbefaler å legge til en kolonne med overskriften "Hvor?" og gjør en enkelt KAMP der. Den MATCH tar like lang tid som VLOOKUP for januar, men da vil de 12 INDEX-funksjonene ta kortere tid enn VLOOKUP for februar, og du har trimmet en hel haug med tid. Igjen, vær forsiktig med $ i INDEX-funksjonen begge steder, en like før radene,og den andre før kolonnene, en blandet referanse i begge.
Hei, jeg vil takke deg for at du var innom, vi sees neste gang for nok en netcast fra!
Last ned fil
Last ned eksempelfilen her: Podcast2028.xlsx
