“RIP VLOOKUP” – ezzel és hasonló címekkel lehet szerte az Interneten találkozni, mióta kijött a várva várt XKERES függvény. A VLOOKUP velünk volt már, mióta Excel létezik – de vajon tényleg leáldozott neki? Sokkal jobb az XKERES, mint az FKERES?
A cikk végén megtalálod a letölthető dokumentumot is, melyben az összes példa megtalálható!
Térjünk a lényegre!
- FKERES használata
- Amikor nem a legelső oszlop a keresési oszlop – INDEX-MATCH
- XKERES használata
- Miért NE használj XKERES-t?
Nyelvi percek
Szögezzük le gyorsan:
FKERES = VLOOKUP
XKERES = XLOOKUP
INDEX = INDEX
HOL.VAN = MATCH
És ugye emlékszel a vessző-pontosvessző témakörre?
Oké, akkor ezt tisztáztuk.
FKERES gyorstalpaló
Az egyik leggyakoribb igény, hogy egy táblázatból ki kell tudnunk keresni egy elemet. Erre találták fel az FKERES függvényt.
FKERES használata
=FKERES( Mit keresek ; Mely táblázat első oszlopában ; Mely másik oszloppal térek vissza ; Milyen keresési fajtát használok)
Vagyis
- Az alábbi táblázatban rá tudok keresni a sajtra, tésztára és a csokira, de nem tudok rákeresni az allergénekre vagy az árra.
- Az étel legyen identikus, azaz mindenből csak egy lehet. Nincs két sajt, nincs három tészta.
És akkor mondjuk a tészta ára így keresendő:
=FKERES("tészta",A2:C4,3,HAMIS)
Az utolsó paraméter jelentése: IGAZ vagy HAMIS?
IGAZ: közelítő keresés: a táblázat első oszlopában határértékek vannak, a keresési érték nem biztos, hogy megtalálható közöttük.
Például a távolság alapján el akarjuk dönteni, mivel induljunk el az útra.
A képlet:
=FKERES(B7,A2:B5,2,IGAZ)
HAMIS: pontos keresés: tipikusan szöveg adattípus van az első oszlopban. ID, névsor, vagy bármi, ami beazonosítást tesz lehetővé. Lásd: az első példa az ételallergénekkel.
Mi a baj az FKERES függvénnyel?
Az INDEX-MATCH önálló fogalom, szinte a “van trükkös megoldás” szinonímája. Amióta Excel létezik, az emberiség használja. Teljességgel érthetetlen, hogy
- Miért vártunk ennyit az XLOOKUP-ra?
- Hogyhogy csak az Excel csinálta meg eddig?
De komolyan. 1985 óta a VLOOKUP az egyik leggyakrabban használt függvény. Annyira fontos, hogy sok állásinterjúban a magas fokú Excel tudás ultimate indikátora. Amióta százezernél többen ismerik, közismert tény az is, hogy vegytiszta kiszúrás, hogy a keresési érték az első oszlopban kell, hogy legyen és punktum. Mert mi van ugye, ha nem az első oszlopon akarok keresni, de mondjuk az első oszlop értékével akarok visszatérni?
Példa:
Miben van glutén? Ugye? FKERES függvénnyel nemigen tudjuk meg.
Az IGAZÁN MENŐ excelesek persze erre is tudják az orvosságot: magabiztos mosollyal magyarázzák a vízautómata mellett, hogy
Ó, azt én megoldom INDEX-MATCH-el!
Bizony, nem véletlenül ez az első exceles bűvésztrükk, amit mindenki megtanul. Életmentő, nézzük is meg, hogy működik.
FKERES helyett INDEX-HOL.VAN
Annál is inkább meg kell említenünk, mert az XKERES még mindig nem elérhető mindenki számára. Naszóval.
Két része van:
- Az INDEX függvény megmondja egy táblázat adott pozíciójában lévő elemét. Mi van a megjelölt tartomány 3-dik sorának 4-dik cellájában?
Vagyis az INDEX függvény paraméterei:
=INDEX( Cellatartomány , Sor sorszáma, Oszlop sorszáma)
Ha csak egyetlen oszlopunk, vagy egyetlen sorunk van, a harmadik paraméter elhagyható, az Excel egyből jól érti majd. Lásd később.
- A MATCH függvény megmondja, hogy egy bizonyos érték hányadik cellában található a megjelölt cellatartományban?
Figyelem! A HOL.VAN függvénynek most nem használtam ki a harmadik paraméterét. Helyesen, hogy az FKERES pontos egyezését tudjuk szimulálni, így kellett volna írnom:
=HOL.VAN(B11,B2:B4,0)
- INDEX és MATCH együtt: A két függvényt pedig össze is tudjuk ollózni.
Előbb megkérdezzük, hogy hol van egy cellatartományon belül egy érték, és utána erre a pozícióra rámutatunk egy másik cellatartományon. Mondjuk hol a glutén? Na hát a második sorban. Jó, de miben van glutén? Hát abban a dologban, ami az első oszlop második sorában ül. Képlettel kifejezve:
Mi a baj az INDEX-MATCH trükkel?
Mármint azon kívül, hogy teljesen indokolatlanul régóta van velünk? A hátránya leginkább a hibakezelésben keresendő. Ami ugye nemigen van. Az FKERES-hez hasonlóan hibával tér vissza, amennyiben nem létező elemre keresünk rá.
A példában az “xxx” értékre kerestem rá. Az első két esetben a #HIÁNYZIK hibaüzenetet kaptuk, hiszen pontos egyezést kértünk mind az FKERES, mind a HOL.VAN függvény esetében. Még zavarba ejtőbb a helyzet akkor, ha a HOL.VAN függvény harmadik paraméterét nem adtuk meg, hiszen akkor az alapértelmezett 1 érték kerül előtérbe, azaz egy olyan közelítő keresés, ahol azt feltételezzük, hogy a hivatkozott tömb növekvő sorrend szerint van rendezve. Ebben az esetben visszatér a csokival, mint “legkisebb rossz” értékkel – hát, nem talált. (Persze lehet, hogy amúgy van a csokiban glutén, de a függvény nem ezért tért vissza vele. Az Excel végrehajt és nem értelmez.)
Igen, természetesen van lehetőségünk az egészet beágyazni még egy HAHIBA függvénybe is – de miért tennénk, ha feltalálták az XKERES függvényt?
XKERES, maga az új korszak
Az Excel történetében sok fontos változásnak lehettünk már szemtanúi, de a történelemkönyvekbe minden bizonnyal az XKERES előtti, és az XKERES korszakok fognak bekerülni.
Végre nem kell kettő, illetve hibakezelés esetén három függvényt egymásba ágyazni. Cserébe van egy új függvényünk egy csomó paraméterrel.
Az XKERES paraméterei
Az XKERES egy csomó paraméterrel érkezik. Szintaxisa:
=XKERES( Keresési érték ; Keresési tömb ; Visszatérési tömb ; Nem találtam teendő ; Egyezés típusa ; Keresés módja)
A fenti példában az utolsót nem is használtam ki, ilyen kis táblázat esetében nincs is jelentősége. A szintaktika szerint tehát ezeket kell megadnunk:
- KÖTELEZŐ Mit keresünk?
- KÖTELEZŐ Hol keressük?
- KÖTELEZŐ Melyik listából keressük ki a visszatérési értéket?
- Mit tegyünk, ha nem volt találat? (HAHIBA helyett beépített paraméter!)
- Pontos vagy közelítő egyezés? (egy kicsit máshogyan, mint az FKERES-nél megszoktuk)
- Bináris vagy szekvenciális, elölről vagy hátulról indul a keresés?
Én most ennél mélyebben azért nem megyek bele, mert csak a Microsoft leírását ismételgetném. Leggyakrabban az első öt paramétert fogod kitölteni, hiszen ez felel meg a korábbi FKERES megszokott paramétereinek – kibővítve a hibakezelés paraméterével.
Szóval a “gyári” leírás helyett hadd emeljek ki egy egyszerű tényt, amit tudtál, csak nem gondoltál:
Az XKERES nem összefüggő tartományokat is tud kezelni.
XKERES használata nem összefüggő tartományokra
A klasszikus INDEX-MATCH megoldáshoz hasonlóan az XKERES sem követeli meg, hogy a keresési és visszatérési tartomány egy összefüggő táblázat része legyen. Akár külön munkalapon is lehetnek, és egymáshoz képest elcsúsztatva is.
A lényeg, hogy a keresési és a visszatérési tartományok ugyanakkorák legyenek.
Az adatokat sorokba is rendezhetjük:
Amit az XKERES sajnos nem tud: több dimenziós tömbök kezelése. Az adatok egy sorban, vagy egy oszlopban vannak és punktum. Pedig adná magát, hogy ha már idáig eljutottak, ezt is tudja, de nem. Talán majd egy későbbi Excel verzióban.
Miért ne használj XKERES-t?
Mindezek ismeretében igen csábító örökre leszokni az FKERES-ről, és áttérni az XKERES függvényre. Csakhogy… Van egy-két szomorú tény.
❌ A régi Excelekben nincs XKERES
A legnagyobb probléma itthon egyszerűen az, hogy nem veheted egyértelműnek, hogy mindenki a legújabb Excelt használja. Sajnos kis hazánkban nem minden cég, nem minden otthon teheti meg, hogy 365-ös programcsomagot vásároljon magának, és bizony a régebbi verziókban nincs XKERES.
Ha a fájlt másnak is kell majd használnia, az XKERES egyértelműen kerülendő.
❌ Az alternatívákban nincs XKERES
Egy egész bejegyzéssorozatot szántam az Excel legjobb alternatíváinak. Mindegyiknek meg van a maga előnye és hátránya – az újítások késleltetett követése egyértelműen ezek közé tartozik. Sajnálom, de a legelterjedtebb Google Sheets sem implementálta még az XKERES függvényt. Pedig ott lett volna a nagy lehetőség elsőként bemutatni, szépen körbehaknizhatták volna vele a világot, de ez az olcsó marketing lehetőség elúszott.
Ha a fájlt Excel alternatívával megnyitva is kell majd használni, az XKERES egyértelműen kerülendő.
De amúgy igazából kb. ennyi. Amúgy jó cucc, addiktív, de nem káros. Használd egészséggel!
XKERES, FKERES és további függvények – hasznos jószágok ők, a Te üzletedet is felvillanyozhatják! Keress bátran egyedi üzleti megoldások ügyében, ha pedig tanulni szeretnél, akkor képzési portfóliómban érdemes szétnézned. Megtalálsz e-mailen vagy a jelentkezési űrlapon keresztül is. Szeretettel várlak továbbá például mindennapi exceles elakadásaid ügyében az Exceltudást mindenkinek! facebook csoportban is.
