Már több korábbi posztban is elmélkedtem a VLOOKUP függvény különféle lehetőségeiről, felhasználási módjairól, de még mielőtt azt hinnénk, hogy nem lehet több újat elmondani az egyik leggyakrabban használt Excel-funkcióról, jöjjön egy újabb poszt a függvényről néhány apró trükkel és információval.

Lássuk a következő kis táblát és mellette máris az első kérdésünket, avagy hogy tudjuk megtalálni az első, "Ü" betűt is tartalmazó településünket és a hozzá tartozó bevétel-információt:

Még mielőtt beágyazott függvényekben kezdenénk el gondolkodni, elég ha felidézzük a VLOOKUP joker-karakterei közül a "*"-ot, ami ugyebár tetszőleges hosszúságú szövegrészre utal, ergó a következő függvény fogja megadni a megoldásunkat:

Miután "*"-gal kezdjük a keresendő értékünk paraméterét, így az "Ü" bármelyik helyen állhat a szóban, de mivel nem azt a települést keressük, amelynek utolsó betűje "Ü", így a paramétert az "Ü" betű megadása után szintén egy "*"-gal zárjuk le.

Nézzünk is meg gyorsan egy másik, hasonló kérdést.

"*" ugyebár tetszőleges számú karakterre utal a szövegünkben, viszont előfordulhat olyan eset, amikor pontosan tudjuk, hogy hányadik helyen álló karaktert akarjuk megvizsgálni, ilyenkor jön a képbe a "?" joker-karakter, amely egy darab karaktert hivatott jelölni. Ergó, ha megnézzük a következő példát:

Látható, hogy miután a negyedik karakter esetében vizsgáljuk az "m" meglétét, így három kérdőjellel kezdődik a VLOOKUP első paramétere és mivel nem tudhatjuk, hogy utolsó karakter-e az "m", így csillaggal zárjuk a paramétert.

A fenti egyszerűbb kis trükkök után most jöjjön egy kis elmélkedés a VLOOKUP sebességéről, ugyanis aki dolgozott már igazán nagy adathalmazzal (sorok tízezreiről, oszlopok tucatjairól beszélünk), az tudja, hogy ez a függvény csak a kis mintapéldáim esetében ad azonnali választ, sokszor tapasztalhatunk az állapotsoron százalékban mérhető kalkulációs előrehaladást, ami esetenként több percig tartó várakozást jelent a felhasználónak.

És ezután még jön az a finomság, amikor esetleg valamit változtatunk az adathalmazban és indul újra az egész kalkuláció. A kérdés most az, hogy van-e bármilyen módszer ennek felgyorsítására, változtatására? Van pár trükk erre a célra, talán a legkönnyebben megvalósítható a bináris keresés használata.

Lineáris keresésről akkor beszélhetünk, ha egyszerűen "EXACT MATCH"-re, tehát pontos egyezésre keresünk a VLOOKUP függvényünkkel (utolsó paramétert FALSE-ra állítjuk), majd automatikus kitöltéssel akár több tízezer soron is bevetjük a függvényt. Ilyenkor az Excel szépen fogja magát és az első sortól kezdve az utolsóig végignézi az adathalmazt, hogy megtalálja az első egyezést (ha sok egyezésünk van, akkor már trükköket kell bevetnünk, hogy az x-edik egyezést adja vissza a függvény).

Ez egy egyszerű lineáris keresés:

Bináris keresésről akkor beszélhetünk, ha sorbarendezzük az adathalmazunkat a keresendő érték mentén, majd közelítő egyezésre keresünk a VLOOKUP utolsó paraméterének TRUE értékre állításával.

Tehát az előbbi példán, ha az azonosító alapján sorbarendezzük a céltáblánkat (ahol a településnév található), majd közelítő keresést hajtunk végre, akkor értelemszerűen gyorsabb lesz a függvény, mint lineáris keresés esetén.

De erre sokan felszisszenhetnek, hiszen ezzel iszonyú hibalehetőségeket teszünk a tábláinkba, kereséseinkbe. Így is lenne, ha egy az egyben közelítő keresést használnánk, de ehelyett be kell dobnunk egy trükköt:

Lineáris keresés:

=VLOOKUP(D4,$I$4:$J$11,2,FALSE)

Bináris keresés nem megbízható eredménnyel:

=VLOOKUP(D4,$I$4:$J$11,2,TRUE)

Bináris keresés megbízható eredménnyel:

=IF(VLOOKUP(D4,$I$4:$J$11,1,TRUE)<>D4,"N/A",VLOOKUP(D4,$I$4:$J$11,2,TRUE))

Mit is csinálunk? Az IF-be ágyazott első VLOOKUP azt nézi meg, hogy a közelítő keresés által a VLOOKUP-nak visszaadott érték megegyezik-e pontosan a keresett értékkel (tehát mindig a kiinduló oszlopban keres) és ha nem, akkor kiadja a megszokott N/A üzenetet, ellenkező esetben már nézi is a tényleges céloszlopot - azaz úgy tűnik, mintha ugyanazt megcsinálnánk két VLOOKUP és egy IF függvénnyel, amit a lineáris keresés pontos egyezőségi paraméterével tennénk, de van egy különbség:

A sebesség jelentősen jobb az IF-es bináris keresésben, mint a lineárisban.

A példán ez most nem jött vissza egyértelműen, de akinek lesz majd lehetősége, ideje, tegyen próbát ugyanezzel egy több tízezer soros keresésnél is - értelemszerűen persze validálja az eredményt, hogy meggyőződjön a sikerességéről.