FKeres helyett használj XKeres függvényt!
A Micrsoft Excel új, Xkeres függvénye nem csak megbízhatóbb, de egyszerűbb alternatívája lehet az Fkeres / Vkeres, valamint az Index+Hol.Van függvényeknek is.
A Microsoft Excelt rendszeresen használók gyakran találkoznak a megoldandó problémával, amikor kapnak egy listát egy kollégától, amihez bizonyos adatokat csak egy másik listában találhatnak meg.
Például:
- Kapsz egy névlistát, amelyekhez egyéb adatokat (címeket, cégnevet, eladási számokat) kell párosítanod
- Szeretnéd megnézni, hogy a kilistázott cikkekhez mekkora készlet tartozik, és az aktuális készletet a raktáros kolléga egy saját Excel fájlban tartja számon
A fentiekre egyébként hosszú távú megoldásként javasoljuk egy integrált vállalatirányítási rendszer bevezetését, de ha jelenleg nem áll rendelkezésedre az erőforrás, idő, vagy csak egy ad-hoc igényre keresel megoldást, akkor olvass tovább, ugyanis egy friss Excelen kívül nem lesz szükséged másra.
Egy kis történelem (FKeres, Index+Hol.Van)
A Microsoft első megoldása a problémára az FKeres és a VKeres függvények voltak. Ezekkel a függvényekkel megadtunk egy értéket, majd egy oszlopot/sort, amelyben az értéket kerestük, majd pedig, ha megtalálta az Excel az őt tartalmazó sort, akkor megmondhattuk, hogy a kijelölt oszlophoz képest jobbra, hányadik oszlopot szeretnénk értékként visszakapni:
Az FKeresnek azonban megvannak a maga korlátai:
- Igazán stabilan csak névsorba rendezett oszlopban tudott keresni
- A visszaadott értéket tartalmazó oszlopnak a keresett értéket tartalmazó oszloptól jobbra kellett lennie
- Nagyobb mennyiségű (>1000) adat esetében nem volt megbízható
Közelítő egyezéses keresésénél hibás eredményeket kaphattunk, a beállítások hiánya miatt.
Erre volt kerülőmegoldás az Index és a Hol.Van függvények kombinációja, amely látható módon már a helyes megoldást adta vissza. Ha megmutatom, hogyan lehetett a Fenti Fkeres függvényt kiváltani, akkor azonnal láthatod, hogy ezzel mi volt a probléma:
=INDEX(A1:B7;HOL.VAN(D2;A1:A7;0);2)
- Egymásba ágyazott függvényekre volt szükségünk, így bonyolultabb és lassabb volt a használata
- Itt is hiányosak a “nem pontos” keresések beállításai
Ismerd meg az XKeres függvényt, hogy soha többet ne kelljen FKeres-t használnod
XKeres függvény paraméterei:
- Keresési_érték
Az az adat (pl.: név, cikkszám), amelyikhez információt szeretnél párosítani a másik táblából - Keresési_tömb
Az a tartomány, amelyen belül a keresést szeretnéd elvégezni – tartalmaznia kell a keresett értékeket tartalmazó oszlopot/sort - Visszaadandó_tömb
Ennek a tartománynak az értékét fogja eredményül visszaadni a függvény - Ha_nincs_találat
Ezt az értéket adja vissza a függvény, ha nem találja az Keresési_értéket a Keresési_tömbben - Egyezési_mód
Megadja, hogy mi számít találatnak- 0 = pontosan megegyező találat
- -1 = pontos egyezés, vagy a következő kisebb elem (számsorrendet, vagy névsort figyelembe véve)
- 1 = pontos egyezés, vagy a következő nagyobb elem (számsorrendet, vagy névsort figyelembe véve)
- 2 = helyettesítő karakter egyezés, amelynél használhatjuk a *-ot egy bármilyen hosszúságú szövegrész megadására, vagy a ?-et egy ismeretlen karakter helyének jelölésére (példát lásd a lenti ábrán)
Példa az XKeres használatára
A FELADAT
Egy kollégánktól kaptunk egy ügyféllistát, akiknek csomagot/levelet szeretnénk küldeni, de a hozzájuk tartozó címek nem szerepelnek benne, ezért szeretnénk a nevekhez címeket párosítani a lehető leggyorsabban.
A névlistán kívül, szerencsére van egy másik Excel táblánk, amely tartalmazza az összes ügyfelünket címekkel együtt.
1) Táblázattá alakítás
A kedvezményezettek tábláját táblázattá alakítjuk. Ehhez az egyik adatot tartalmazó cellába kattintás után megnyomjuk a CTRL+R gomb használatával. Ennek előnye, hogy ha az új oszlop tetejébe beírjuk a képletet, automatikusan frissíti az alatta lévő sorokat is, ráadásul a későbbiekben szűrhetünk is adatink között. Ez a lépés nem kötelező, de akkor az elkészült függvényt másolni kell az alatta lévő cellákba is.
2) Függvény beírása
Ezután beírjuk a végleges függvényt a B2-es cellába, ami a következő lesz:
=Xkeres(A2;Adatok!C:C;Adatok!H:H;”Nincs cím”)
- A2 – Ez lesz a keresett kifejezés, azaz a név. A függvény legörgetésével, ez dinamikusan változni fog, minden sorban az adott nevet fogja kijelölni
- Adatok!C:C – Ez a segédtáblánkban a neveket tartalmazó oszlop. Segíteni fog nekünk összepárosítani a kedvezményezettek sorait, a másik táblázat, azonos nevekhez tartozó soraival. Itt érdemes az egész oszlopot kijelölni, így a függvény legörgetésével nem tolódik lefele a keresési terület sem. Ha nem szeretnétek az egész oszlopot kijelölni, akkor a tartomány kijelölése után nyomjátok meg az F4 billentyűt, így az Excel $-ket fog beszúrni a képlettel, amivel rögzítitek a tartományt.
- Adatok!H:H – Példánkban az Adatok tábla, irányítószámokat tartalmazó oszlopa. Ebből az oszlopból fogja a függvény a megfelelő értéket visszaadni nekünk az adott cellában. Itt is érdemes a teljes oszlopot kijelölni, vagy használjuk a $-ket a tartomány rögzítésére
- ”Nincs cím” – a függvény negyedik paramétere opcionális. Ezt az értéket fogja visszaadni a függvény akkor, ha nem találja meg az adott nevet az adatok táblában
3) Néhány keresési eredmény ellenőrzése / Hibajavítás
Összegyűjtöttük a leggyakoribb hibákat, amiket érdemes megnézned, ha a függvényed nem hozza ki a megfelelő értékeket, vagy túlságosan sok nevet nem talált meg:
- Az Adatok táblában és a Kedvezményezettek táblában nem azonosak a nevek összetételei.
Vezetéknév és keresztnév fordítva szerepel, A kézzel írt nevekben elütés van, kötőjelek eltérő használata, vagy az ékezetes betűk különböző verzióinak használata (a kis és nagybetűket a keresés nem különbözteti meg, így ezekkel nem kell foglalkoznod)
Próbálj meg elütésektől mentes listát szerezni, illetve ha látsz egy visszatérő hibát, akkor CTRL+H-t megnyomva, cserével egyesítheted az eltérő karaktereket: pl. „é” és „è”. - Szóközök a nevek után
Ha függvényekkel olvasztod össze az elő- és utóneveket, gyakran előfordul, hogy extra szóközök kerülnek a nevek közé, vagy éppen mögé. Ezektől megszabadulhatsz például egy szövegfüggvénnyel: =Ha(JOBB(A2)=” „;BAL(A2;HOSSZ(A2)-1);A2)
Ez a függvény megnézi az A2-es cellát, és levágja az utolsó karaktert, amennyiben az szóköz
Ha továbbra sem működne a függvényed, kérdezz bátran kommentben, vagy keress minket a marketing@multisoft.hu e-mail címen.
Ha pedig érdekelnek további oktató jellegű tartalmak a Microsoft temrékeivel kapcsolatban, iratkozz fel közösségi média felületeinkre, és kövesd a MultiBlogot!
