Dit is een ander voorbeeld van een formule. Stel dat u 12 kolommen met VERT.ZOEKEN moet doen. U kunt het sneller maken door één MATCH- en 12 INDEX-functies te gebruiken.
In de volgende afbeelding moet u 12 VERT.ZOEKEN-functies uitvoeren voor elk accountnummer. VERT.ZOEKEN is krachtig, maar het kost veel tijd om berekeningen uit te voeren.
Bovendien moet de formule in elke cel worden bewerkt terwijl u eroverheen kopieert. Het derde argument moet worden gewijzigd van 2 naar 3 voor februari, dan 4 voor maart, enzovoort.
Een oplossing is om een rij met de kolomnummers toe te voegen. Vervolgens kan het derde argument van VERT.ZOEKEN naar deze rij verwijzen. U kunt tenminste dezelfde formule uit B4 kopiëren en in C4: M4 plakken voordat u de hele set kopieert.
Maar hier is een veel snellere aanpak. Voeg een nieuwe kolom B toe met Waar? als titel. Kolom B bevat een MATCH-functie. Deze functie lijkt sterk op VERT.ZOEKEN: u zoekt de waarde in A4 in de kolom P4: P227. De 0 aan het einde is als de False aan het einde van VERT.ZOEKEN. Het geeft aan dat u een exacte overeenkomst wilt. Hier is het grote verschil: MATCH geeft als resultaat waar de waarde is gevonden. Het antwoord van 208 zegt dat A308 de 208e cel is in het bereik P4: P227. Vanuit een herberekend tijdsperspectief zijn MATCH en VERT.ZOEKEN ongeveer gelijk.
Ik kan horen wat je denkt. “Wat heb je eraan om te weten waar iets zich bevindt? Ik heb nog nooit een manager laten bellen en vragen: 'In welke rij is die vordering te ontvangen?' "
Hoewel mensen zelden vragen in welke rij iets staat, kan de INDEX-functie die positie gebruiken. De volgende formule vertelt Excel om het 208e item uit Q4 te retourneren: Q227.
Terwijl u deze formule kopieert, wordt de reeks waarden door de opzoektabel verplaatst. Voor elke rij voer je één MATCH- en 12 INDEX-functies uit. De INDEX-functie is ongelooflijk snel in vergelijking met VERT.ZOEKEN. De volledige set formules wordt 85% sneller berekend dan 12 kolommen met VERT.ZOEKEN.
Bekijk video
- Stel dat u 12 kolommen met VERT.ZOEKEN moet doen
- Gebruik voorzichtig een enkel dollarteken vóór de kolom met de opzoekwaarde
- Gebruik voorzichtig vier dollartekens voor de opzoektabel
- U bent nog steeds bezig met het hardcoderen van het argument in de derde kolom.
- Een veel voorkomende oplossing is om een rij hulpcellen toe te voegen met het kolomnummer.
- Een andere, minder efficiënte oplossing is om KOLOM (B2) te gebruiken in de VERT.ZOEKEN-formule.
- Maar 12 VERT.ZOEKEN doen voor elke rij is erg inefficiënt
- Voeg in plaats daarvan een hulpkolom toe met de kop WAAR en doe een enkele wedstrijd.
- De MATCH duurt zo lang als de VERT.ZOEKEN voor januari.
- U kunt dan 12 INDEX-functies gebruiken. Deze zijn ongelooflijk snel in vergelijking met VERT.ZOEKEN.
- De INDEX verwijst naar een enkele kolom met antwoorden met $ voor de rijen.
- De INDEX verwijst naar de hulpkolom met een $ voor de kolom.
Videotranscriptie
Leer Excel van podcast, aflevering 2028 - Veel VERT.ZOEKEN vervangen door één MATCH!
Klik op die "i" in de rechterbovenhoek om naar de afspeellijst te gaan, ik podcast dit hele boek!
Hé, welkom terug bij de netcast, ik ben Bill Jelen! Het is een klassiek probleem, we moeten elke maand VERT.ZOEKEN doen, toch? En je kunt hier ongelooflijk voorzichtig zijn door 3 keer op F4 te drukken om dat naar de kolom te vergrendelen en vervolgens eenmaal op F4 te drukken om de hele rij te vergrendelen. Maar als je op dit punt komt, is de, 2, ONWAAR dat 2 hard gecodeerd is, en terwijl je dat kopieert, zul je de 2 naar een 3 moeten bewerken, toch? Een inefficiënte manier om dit te doen, een manier die ik niet leuk vind, is door de kolom B1 te gebruiken. Kolom B1 is natuurlijk 2, maar als je dat kopieert, zie je dat het verandert in kolom C1, wat 3 is, maar denk hier eens over na, dit is constant het kolomnummer steeds opnieuw uitzoeken. Dus wat ik mensen zie doen en waarom, weet je, meer de voorkeur geven dan de kolommen, is dat we dat Ctrl-slepen,zet de nummers 2-13 daarboven in een helpercel, en als we op dit punt komen, gaan we omhoog en specificeren dat kolomnummer. Druk 2 keer op F4 om het vast te zetten op de rij,, FALSE enzovoort. Maar zelfs met die methode is VERT.ZOEKEN ongelooflijk inefficiënt, omdat het hier door al deze items moet zoeken totdat het A308 vindt en dat is het cijfer B4. Als het dan naar C4 gaat, vergeet het dat het gewoon ging kijken, en het begint helemaal opnieuw, oké. Dus je hebt een van de langzaamste functies in heel Excel, de VERT.ZOEKEN, ONWAAR wordt steeds opnieuw gedaan voor hetzelfde item.omdat het hier door al deze items moet zoeken totdat het A308 vindt en dat is het cijfer B4. Als het dan naar C4 gaat, vergeet het dat het gewoon ging kijken, en het begint helemaal opnieuw, oké. Dus je hebt een van de langzaamste functies in heel Excel, de VERT.ZOEKEN, ONWAAR wordt steeds opnieuw gedaan voor hetzelfde item.omdat het hier door al deze items moet zoeken totdat het A308 vindt en dat is het cijfer B4. Als het dan naar C4 gaat, vergeet het dat het gewoon ging kijken, en het begint helemaal opnieuw, oké. Dus je hebt een van de langzaamste functies in heel Excel, de VERT.ZOEKEN, ONWAAR wordt steeds opnieuw gedaan voor hetzelfde item.
Dus hier is de veel, veel snellere manier om te gaan, we gaan een hulpkolom invoegen, en deze hulpkolom noem ik het Waar? Zoals waar is de A308? We gebruiken a = MATCH, zoek naar A308 in de eerste rij van de tabel, druk daar op F4,, 0 voor een exacte match, oké, het vertelt ons dat "Hé, kijk daar eens naar, het staat in rij 6, hoe geweldig is dat? " Maar als we het kopiëren, zie, het is de hele tijd op verschillende plaatsen. Oké, deze wedstrijd duurt nu net zo lang als de VLOOKUP van januari, daar zijn ze zelfs dood, maar hier is het verbazingwekkende. Van daaruit hoeven we nooit een VERT.ZOEKEN uit te voeren voor de rest van de rij, we zouden gewoon kunnen doen = INDEX, INDEX zegt "Hier is een reeks antwoorden." Ik ga naar de januari-cellen, en ik ga hier heel voorzichtig 2 keer op F4 drukken, dus ik vergrendel het op 4: 227,maar de Q mag veranderen als ik beweeg. Komma, en dan wil het weten welke rij, nou dat wordt het antwoord in B4, ik druk drie keer op F4 om de $ voor de B te krijgen, oké, kopieer dat naar voren.
Deze formule, deze INDEX-formules, deze 12 zullen gebeuren in minder dan de tijd die nodig is om de VERT.ZOEKEN in februari uit te voeren, oké. Als we de Charles Williams-timer hierop zetten, berekent dit hele ding ongeveer 14% van de tijd van 12 VERT.ZOEKEN. Uw manager wil de Waar? Goed, verberg gewoon die kolom, alles blijft werken, oké, dit is een prachtige manier om de 12 maanden of 52 weken van VERT.ZOEKEN te versnellen. Oké, deze tip, en nog veel meer tips, staan in dit boek. Klik daar op de "i" in de rechterbovenhoek, je kunt het boek kopen, $ 10 e-book, $ 25 voor het gedrukte boek, oké.
Dus vandaag hadden we een probleem waarbij 12 kolommen VERT.ZOEKEN, je voorzichtig de $ erin kunt plaatsen, maar dan moet dat 3e argument nog steeds hard gecodeerd worden. Je zou kolom (B2) kunnen gebruiken, daar ben ik geen fan van, want er zijn honderden rijen * 12 kolommen waar dat steeds opnieuw wordt berekend. Gebruik gewoon een helpercel op een rij, plaats de nummers 2-12 en wijs ernaar, het is echter nog steeds inefficiënt, omdat VERT.ZOEKEN nadat het januari heeft berekend, opnieuw moet beginnen in februari. Daarom raad ik aan een kolom toe te voegen met de kop 'Waar?' en daar een enkele wedstrijd doen. Die MATCH duurt net zo lang als VERT.ZOEKEN voor januari, maar dan zullen de 12 INDEX-functies minder tijd kosten dan VERT.ZOEKEN voor februari, en je hebt een heleboel tijd ingekort. Nogmaals, voorzichtig met de $ in de INDEX-functie op beide plaatsen, een net voor de rijen,en de andere voor de kolommen, een gemengde verwijzing in beide.
Hé, ik wil je bedanken voor het langskomen, we zien je de volgende keer voor nog een netcast van!
Download bestand
Download het voorbeeldbestand hier: Podcast2028.xlsx
