Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016
Amikor Excel haladó vagy Excel makró tréninget tartok, mindig felteszem a címben szereplő kérdést a résztvevőknek. A válaszok alapján úgy döntöttem, hogy megírom ezt a cikket. Ugyanis a felhasználók döntő többsége az FKERES utolsó paraméterének HAMIS, FALSE, Nulla értéket ad meg. Ezzel ugyebár azt kérjük, hogy pontos egyezőségre keressen. Ekkor, ha nem találja a keresett értéket, hibát fog visszaadni.
De nézzük a következő problémát!
A jobb oldali táblában szeretnénk kitölteni az értékeket. Segítségként a bal oldali táblát használjuk. Nagyon csábító, hogy ezt a feladatot a HA függvénnyel oldjuk meg:
=HA(ért_ár<12.000.000;2%;HA(ért_ár<30.000.000;2,5%;3%))
Ez nem rossz, csak nem az optimális megoldás. Ráadásul, ha a bal oldalon jóval több kategória lenne, akkor végeláthatatlanul sorakoznának az egymásba ágyazott HA függvények.
Megoldás FKERES (VLOOKUP) modellel
Lehet, hogy furcsán hangzik, de a hasonló problémákat FKERES modellel célszerű megoldani. Csupán két dolgot kell módosítani a szokásoshoz képest:
- A kategóriákat leíró táblát növekvő sorba kell rendezni a keresési kulcs oszlop alapján
- Az FKERES utolsó paraméterét 1-re, IGAZ-ra (TRUE-ra) állítjuk, vagy egyszerűen elhagyjuk, mert ez az alapértelmezett értéke.
Mi történik itt?
Az FKERES utolsó argumentuma a keresés típusát adja meg. A szokásos nulla vagy HAMIS úgynevezett lineéris keresést csinál, azaz a bal oldali tábla első oszlopának minden értékét összehasonlítja a keresési értékkel mindaddig, amíg meg nem találja, vagy a végére nem ér.
Tehát ebben az esetben a keresési lépések száma legrosszabb esetben megegyezik a keresési tábla sorainak számával.
Ha azonban az utolsó argumentumot elhagyjuk, vagy 1-re, IGAZ-ra állítjuk, akkor a keresési módszer megváltozik. Az FKERES átvált bináris vagy más néven logaritmikus keresésre. Ez a fajta keresés jelentősen gyorsabb, mint a másik, viszont csak rendezett (növekvő sorban lévő) értékeken működik helyesen.
Mi van akkor, ha nem talál pontos egyezést?
Számunkra most ez a lényeg. Ebben az esetben ugyanis nem hibaüzenetet kapunk, hanem a keresési értékhez legközelebb álló, de még nála kisebb számot.
Úgy is fogalmazhatunk, hogy visszaadja a keresési tábla első oszlopából a legnagyobb olyan értéket, ami még kisebb annál, mint amit keresünk.
Nézzünk egy másik példát, a szokásos teszt vagy dolgozat értékelést:
Itt a pontszámokat keressük a ponthatárok sorozatában. Ha valaki éppen határérték pontot kapott, akkor a pontos egyezés szerint egyértelmű, hogy hányas jár neki. Ha viszont a határértékek közé esik a pontszám, akkor a legközelebbi kisebbet tekinti találatnak. Jelen példában tehát a 43-hoz a 40, a 21-hez a 20, a 65-höz a 60, stb…
Megjegyzendő tehát
- Az FKERES utolsó argumentuma a keresési módot adja meg. Ha Nulla, akkor lineáris kereséssel, pontos egyezőséget keres.
- Ha az utolsó argumentumot elhagyjuk, vagy egyest adunk, akkor bináris (sokkal gyorsabb) kereséssel keres. A pontos egyezést így is megtalálja, de a nem pontos egyezésnél a legnagyobb kisebbet adja.
- Bináris keresésnél a keresési táblát rendezni kell a kulcs (első oszlop) szerint!
Kedves Imre,
Szeretném kérdezni, hogy a Power Query-ben van olyan megoldás, hogy két táblát összefűzök, és nem lineáris a megfelelés? mostanában többször jön ilyen megoldandó feladat, viszont ezt a részét csak excelben tudom, fkeres, utolsó argumentum 1-el.
Köszönöm előre is.Eta