Az FKERES függvény működése Excelben – Lépésről lépésre útmutató és példák

Az Excel táblázatkezelő program az üzleti élet, az oktatás és a mindennapi adatelemzés egyik alapvető eszköze. Számtalan funkciót kínál, amelyek közül az egyik leggyakrabban használt és sokoldalúbb a FKERES függvény. Ez a funkció lehetővé teszi, hogy egy adott érték alapján adatokat keressünk egy táblázatban, majd a megtalált sorból egy másik oszlopban lévő információt jelenítsünk meg.

A FKERES – angolul VLOOKUP – a “függőleges keresés” rövidítése. Elengedhetetlen eszköz mindazok számára, akik nagy mennyiségű adattal dolgoznak, és gyorsan, hatékonyan szeretnének specifikus információkat kinyerni.

Gondoljunk csak bele: egy hatalmas terméklistából szeretnénk kikeresni egy adott termék árát, vagy egy ügyféllistából az ügyfél telefonszámát a neve alapján. Ezekre a feladatokra kínál elegáns és automatizált megoldást az FKERES függvény.

Ez a cikk részletesen bemutatja az FKERES függvény működését, paramétereit, gyakori használati eseteit, buktatóit és haladó technikáit. Célunk, hogy teljes körű útmutatót nyújtsunk, amely segít Önnek mesterien elsajátítani ezt a kulcsfontosságú Excel funkciót.

Mi az az FKERES függvény és mire használható?

Az FKERES függvény az Excel egyik kereső és hivatkozó függvénye, amelynek célja, hogy egy adott értéket keressen egy táblázat vagy tartomány első oszlopában, majd a megtalált sorból egy megadott oszlopban lévő értéket adja vissza.

Képzeljen el egy adatbázist, ahol az első oszlopban egyedi azonosítók (pl. termékkódok, ügyfélszámok) találhatók, a további oszlopokban pedig az azonosítóhoz tartozó egyéb adatok (pl. termék neve, ára, leírása, ügyfél címe, telefonszáma). Az FKERES segítségével könnyedén lekérdezheti ezeket az adatokat.

A függvény különösen hasznos, ha két különböző táblázatot szeretnénk összekapcsolni egy közös azonosító alapján, vagy ha egy nagy adathalmazból kell gyorsan specifikus információkat kinyerni anélkül, hogy manuálisan keresgélnénk.

A mindennapi munkában ez jelentheti például egy számlázó programban a termék kódja alapján a termék nevének és egységárának automatikus beillesztését, vagy egy HR adatbázisban az alkalmazott azonosítója alapján a részlegének vagy beosztásának megkeresését.

Az FKERES függvény szintaxisa és paraméterei

Mielőtt belemerülnénk a gyakorlati példákba, értsük meg az FKERES függvény felépítését és az egyes paraméterek jelentését. A függvény szintaxisa a következő:

=FKERES(keresési_érték; tábla; oszlop_index; [tartomány_keresés])

Nézzük meg részletesen az egyes paramétereket.

Keresési_érték

Ez az az érték, amit keresni szeretne a táblázat első oszlopában. Lehet egy cellahivatkozás (pl. A2), egy konkrét szám (pl. 123), vagy egy szöveges sztring (pl. "alma"). Fontos, hogy ez az érték egyezzen azzal az adattípussal, amit a tábla első oszlopában keres.

Ha például számot keresünk, de a tábla első oszlopában szövegként vannak tárolva a számok, az hibát okozhat. Mindig ellenőrizzük az adattípusok egyezését a keresési_érték és a tábla első oszlopa között.

Tábla

Ez az a tartomány, amelyben az adatokat keresni szeretné. Fontos megjegyezni, hogy az FKERES függvény mindig a megadott táblázat első oszlopában keresi a keresési_érték-et. A tartományt általában abszolút hivatkozással (pl. $A$1:$D$100) adjuk meg, hogy a képlet másolásakor ne mozduljon el.

Ha a tábla relatív hivatkozással van megadva, és a képletet másoljuk, a tábla tartománya is elmozdulhat, ami hibás eredményekhez vezethet. Az abszolút hivatkozás használata biztosítja, hogy a keresési tartomány mindig ugyanaz maradjon.

Oszlop_index

Ez a szám adja meg, hogy a tábla melyik oszlopából szeretné az eredményt visszakapni. Az oszlopok számozása 1-től kezdődik, ahol az 1-es az a oszlop, amelyikben a keresési_érték-et találja. Ha például a 3-as oszlopban lévő adatot szeretné, akkor ide 3-at ír.

Ez a paraméter mindig egy pozitív egész szám. Ha 1-nél kisebb vagy a tábla oszlopainak számánál nagyobb számot adunk meg, az #ÉRTÉK! hibát eredményez. Az oszlopindex helyes megadása kulcsfontosságú a kívánt adat visszanyeréséhez.

Tartomány_keresés (opcionális)

Ez a paraméter határozza meg, hogy pontos vagy hozzávetőleges egyezést keres-e a függvény. Két lehetséges értéke van:

• IGAZ (TRUE) vagy elhagyva: Hozzávetőleges egyezést keres. Ebben az esetben a tábla első oszlopának növekvő sorrendben rendezettnek kell lennie. Ha nem talál pontos egyezést, a keresési_érték-nél kisebb, de ahhoz legközelebb eső értéket adja vissza. Ez például osztályzatok, jutalékok vagy ársávok meghatározásánál hasznos.
• HAMIS (FALSE): Pontos egyezést keres. Ez a leggyakrabban használt beállítás. Ha nem talál pontos egyezést, a függvény #HIÁNYZIK (N/A) hibát ad vissza. Ha pontosan tudjuk, mit keresünk, és az értéknek egyedinek kell lennie, ezt válasszuk.

A tartomány_keresés paraméter elhagyása esetén az Excel alapértelmezetten az IGAZ értéket veszi figyelembe, ami sok esetben nem a kívánt eredményt adja. Ezért javasolt mindig explicit módon megadni, hogy IGAZ vagy HAMIS.

A legtöbb esetben a pontos egyezés, vagyis a HAMIS paraméter használata a célravezető az FKERES függvény alkalmazásakor.

Lépésről lépésre példák az FKERES (pontos egyezés) használatára

Most, hogy ismerjük a szintaxist, nézzünk meg néhány gyakorlati példát a pontos egyezés (HAMIS) használatára.

Egyszerű termékkeresés

Tegyük fel, hogy van egy terméklistánk a munkalapunkon, és szeretnénk egy adott termékkód alapján megtalálni a termék nevét és árát.

Adataink az A1:C5 tartományban találhatók:

Termékkód	Termék neve	Ár
A101	Monitor	45000
B202	Billentyűzet	12000
C303	Egér	5000
D404	Webkamera	15000

Szeretnénk az A101 kódú termék nevét és árát lekérdezni. A G2 cellába beírjuk a keresési értéket: A101.

A termék nevének lekérdezéséhez (ami a tábla második oszlopában van) a H2 cellába a következő képletet írjuk:

=FKERES(G2; A2:C5; 2; HAMIS)

Ez a képlet az A101-et fogja keresni az A2:C5 tartomány első oszlopában. Amint megtalálja (az A2 cellában), visszaadja a második oszlopban lévő értéket, ami Monitor.

Az ár lekérdezéséhez (ami a tábla harmadik oszlopában van) a I2 cellába a következő képletet írjuk:

=FKERES(G2; A2:C5; 3; HAMIS)

Ez a képlet hasonlóan működik, de a harmadik oszlopból adja vissza az értéket, ami 45000.

Fontos, hogy a tábla tartományát (A2:C5) abszolút hivatkozással is megadhatjuk, ha a képletet lefelé szeretnénk másolni. Például $A$2:$C$5.

Ügyféladatok keresése

Legyen egy ügyféllistánk az alábbi adatokkal az A1:D6 tartományban:

Ügyfél ID	Név	Cím	Telefonszám
1001	Nagy Péter	Budapest	+36 20 123 4567
1002	Kovács Anna	Debrecen	+36 30 987 6543
1003	Kiss Gábor	Szeged	+36 70 555 1122
1004	Tóth Erika	Pécs	+36 20 222 3344
1005	Horváth Zoltán	Győr	+36 30 777 8899

Ha a G2 cellába beírjuk az 1003-as ügyfél ID-t, akkor a nevét, címét és telefonszámát szeretnénk lekérdezni.

A név (2. oszlop) lekérdezése a H2 cellába:

=FKERES(G2; A2:D6; 2; HAMIS) -> Eredmény: Kiss Gábor

A cím (3. oszlop) lekérdezése a I2 cellába:

=FKERES(G2; A2:D6; 3; HAMIS) -> Eredmény: Szeged

A telefonszám (4. oszlop) lekérdezése a J2 cellába:

=FKERES(G2; A2:D6; 4; HAMIS) -> Eredmény: +36 70 555 1122

Ezek az egyszerű példák jól illusztrálják az FKERES függvény alapvető működését pontos egyezés esetén. Mindig ügyeljünk arra, hogy a keresési_érték az első oszlopban található értékkel egyezzen.

Lépésről lépésre példák az FKERES (hozzávetőleges egyezés) használatára

Az IGAZ paraméter használata más típusú problémák megoldására alkalmas, mint a pontos egyezés. Itt nem egy konkrét értéket keresünk, hanem egy adott tartományba eső értéket.

KRITIKUS FONTOSSÁGÚ: Az IGAZ paraméter használatakor a tábla első oszlopának növekvő sorrendben rendezettnek kell lennie! Ha ez a feltétel nem teljesül, az eredmények hibásak vagy félrevezetőek lesznek.

Osztályzatok meghatározása pontszám alapján

Tegyük fel, hogy egy vizsgán elért pontszámok alapján szeretnénk osztályzatokat rendelni a diákokhoz. Az osztályzati határok a következők:

Pontszám alsó határ	Osztályzat
0	Elégtelen
50	Elégséges
65	Közepes
80	Jó
90	Jeles

Ez a tábla az A2:B6 tartományban található, és az első oszlop (Pontszám alsó határ) növekvő sorrendben van rendezve.

Ha egy diák 72 pontot ért el (ezt írjuk a D2 cellába), akkor a következő képlettel tudjuk lekérdezni az osztályzatát:

=FKERES(D2; A2:B6; 2; IGAZ)

Az Excel megkeresi a 72-t az első oszlopban. Mivel nincs pontos egyezés, megkeresi a 72-nél kisebb, de ahhoz legközelebb eső értéket, ami a 65. Ebből a sorból adja vissza a második oszlopban lévő értéket, ami Közepes.

Ha egy diák 95 pontot ért el, a képlet Jeles-t ad vissza, mivel a 90-es határba esik. Ha 40 pontot ért el, akkor Elégtelen-t, mivel a 0-ás határba esik.

Jutalékszámítás értékesítési szint alapján

Egy másik gyakori felhasználási terület a jutalékszámítás. Egy értékesítő jutaléka az eladási volumenétől függ.

Eladás (Ft) alsó határ	Jutalék (%)
0	0%
100000	5%
500000	7%
1000000	10%
2000000	12%

Ez a tábla az A2:B6 tartományban található, és az első oszlop (Eladás alsó határ) növekvő sorrendben van rendezve.

Ha egy értékesítő 750000 Ft értékben adott el (ezt írjuk a D2 cellába), akkor a jutalék százalékát a következő képlettel kapjuk meg:

=FKERES(D2; A2:B6; 2; IGAZ)

Az Excel a 750000-hez a 500000-es alsó határt találja meg, és visszaadja a 7%-ot. Ha 1500000 Ft volt az eladás, akkor a 10%-ot kapjuk.

Az IGAZ paraméter rendkívül hasznos, de a rendezett adatokra vonatkozó követelményt soha nem szabad figyelmen kívül hagyni, különben a függvény teljesen téves eredményeket adhat.

Gyakori hibák és azok elhárítása az FKERES függvény használatakor

Az FKERES függvény erős eszköz, de mint minden komplex funkció, számos buktatóval járhat, amelyek hibás eredményekhez vagy hibakódokhoz vezethetnek. Ismerjük meg a leggyakoribb hibákat és azok megoldását.

A #HIÁNYZIK (N/A) hiba

Ez valószínűleg a leggyakoribb hiba, amivel az FKERES használatakor találkozhatunk. Akkor jelenik meg, ha a keresési_érték-et a függvény nem találja meg a tábla első oszlopában, és a tartomány_keresés paraméter HAMIS-ra van állítva (pontos egyezés).

Okok:

• Az érték tényleg hiányzik: A legegyszerűbb ok, hogy a keresett érték egyszerűen nincs benne a táblázatban.
• Elgépelés vagy eltérő formátum: A keresési értékben vagy a táblázatban lévő értékben apró eltérés van (pl. extra szóköz, kis- és nagybetű eltérés, számként tárolt szöveg vs. szám).
• Adattípus eltérés: A keresési érték számszerű, de a tábla első oszlopában szövegként van formázva, vagy fordítva. Például az “123” (szöveg) nem egyezik meg az 123 (szám) értékkel.

Megoldások:

• Ellenőrizze az adatokat: Győződjön meg róla, hogy a keresett érték pontosan megegyezik a táblázatban lévővel. Használja a SZÓKÖZ.TÖRLÉS függvényt a felesleges szóközök eltávolítására.
• Ellenőrizze az adattípusokat: Győződjön meg róla, hogy a keresési_érték és a tábla első oszlopában lévő értékek adattípusa megegyezik. Szükség esetén konvertálja azokat (pl. ÉRTÉK függvénnyel a szöveges számokat számmá).
• Hibakezelés HAHIBA vagy HAHIÁNYZIK függvénnyel: Ha a hiányzó érték elfogadható forgatókönyv, használja a HAHIBA vagy HAHIÁNYZIK függvényeket, hogy egyedi üzenetet vagy üres cellát jelenítsen meg a hiba helyett.

Példa HAHIBA használatára:

=HAHIBA(FKERES(G2; A2:C5; 2; HAMIS); "Nincs találat")

Ez a képlet, ha az FKERES #HIÁNYZIK hibát adna, akkor a “Nincs találat” szöveget írja ki helyette.

Keresési érték nem az első oszlopban

Ez egy alapvető korlátja az FKERES függvénynek: mindig a tábla tartományának első oszlopában keres. Ha a keresett érték egy másik oszlopban van, az FKERES nem fogja megtalálni.

Ok:

• A keresési_érték a tábla azon oszlopában található, ami nem az első.

Megoldás:

• Rendezze át a táblázatot: Ha lehetséges, rendezze át a táblázat oszlopait úgy, hogy a keresési érték az első oszlopba kerüljön.
• Használjon alternatív függvényeket: A INDEX és HOL.VAN függvények kombinációja (vagy az XKERES, ha elérhető) megoldja ezt a problémát, mivel ezek lehetővé teszik a keresési oszlop és az eredmény oszlopának független megadását. Erről később részletesebben is szó lesz.

Oszlop beszúrása vagy törlése

Ha a táblázatban, amelyre az FKERES hivatkozik, oszlopokat szúrunk be vagy törlünk, az oszlop_index paraméter elavulttá válhat, és hibás eredményeket adhat.

Ok:

• Az oszlop_index fix érték (pl. 3), és a táblázat szerkezete megváltozott.

Megoldás:

• Dinamikus oszlop_index használata: Használja a HOL.VAN függvényt az oszlop_index dinamikus meghatározására. Ez rugalmasabbá teszi a képletet a táblázat szerkezetének változásával szemben. Ezt is részletesen bemutatjuk a haladó technikák között.

Abszolút és relatív hivatkozások

Amikor az FKERES képletet másoljuk más cellákba, a tábla tartományának hivatkozása elmozdulhat, ha nem abszolút hivatkozással adjuk meg.

Ok:

• A tábla tartománya relatív hivatkozással van megadva (pl. A2:C5 a $A$2:$C$5 helyett).

Megoldás:

• Használjon abszolút hivatkozást: Mindig használjon abszolút hivatkozást ($ jelekkel) a tábla tartományára, ha a képletet másolni fogja. Például $A$2:$C$5. A F4 billentyűvel könnyen válthat a hivatkozástípusok között.

Adatok rendezése az IGAZ paraméter használatakor

Mint már említettük, ha a tartomány_keresés paraméter IGAZ, az első oszlopnak növekvő sorrendben rendezettnek kell lennie. Ennek elmulasztása teljesen téves eredményekhez vezet.

Ok:

• A tábla első oszlopa nincs növekvő sorrendbe rendezve.

Megoldás:

• Rendezze az adatokat: Mielőtt az FKERES függvényt használná IGAZ paraméterrel, győződjön meg róla, hogy a tábla első oszlopa növekvő sorrendben van rendezve.

Ezen hibák ismerete és a megfelelő megoldások alkalmazása jelentősen növeli az FKERES függvény hatékony és hibamentes használatát.

Haladó technikák és tippek az FKERES függvény használatához

Az FKERES függvény alapvető használatán túl léteznek olyan haladó technikák, amelyek még sokoldalúbbá és robusztusabbá teszik ezt a funkciót. Ezekkel a módszerekkel komplexebb problémákat is megoldhatunk, és ellenállóbbá tehetjük a képleteinket az adatváltozásokkal szemben.

Helyettesítő karakterek (*, ?) használata

Az FKERES függvény képes helyettesítő karakterekkel is dolgozni, ha a tartomány_keresés paraméter HAMIS-ra van állítva. Ez akkor hasznos, ha a keresési_érték nem pontosan ismert, vagy csak egy részét tudjuk az értéknek.

• Csillag (*): Bármilyen számú karaktert helyettesít. Például "alma*" megtalálja az “alma”, “almás”, “almaszósz” szavakat.
• Kérdőjel (?): Egyetlen karaktert helyettesít. Például "a?ma" megtalálja az “apma”, “arma” szavakat.

Példa: Ha van egy terméklistánk, és csak annyit tudunk, hogy a termék neve “monitor” szóval kezdődik, és szeretnénk megtalálni az árát.

=FKERES("monitor*"; A2:C5; 3; HAMIS)

Ez a képlet megkeresi az első olyan terméket az A2:C5 tartomány első oszlopában, amely “monitor” szóval kezdődik, és visszaadja az árát. Fontos, hogy csak az első találatot adja vissza.

A helyettesítő karakterekkel történő keresés jelentősen növeli az FKERES függvény rugalmasságát, különösen részleges adatok esetén.

Dinamikus oszlopindex – FKERES és HOL.VAN kombinációja

Ahogy korábban említettük, az oszlop_index paraméter fix értékének megadása problémás lehet, ha a táblázat oszlopainak sorrendje megváltozik. Ezt a problémát a HOL.VAN függvény segítségével orvosolhatjuk.

A HOL.VAN függvény (angolul MATCH) megkeresi egy adott érték pozícióját egy tartományban, és annak sorszámát adja vissza. Szintaxisa:

=HOL.VAN(keresési_érték; keresési_tömb; [egyezési_típus])

• keresési_érték: Amit keresünk (pl. az oszlopfejléc szövege).
• keresési_tömb: Az a sor vagy oszlop, amiben keresünk (pl. a táblázat fejléc sora).
• egyezési_típus: 0 a pontos egyezéshez (ez a leggyakoribb).

Példa: Adott egy terméklistánk az A1:C5 tartományban, a fejléc az A1:C1-ben van (Termékkód, Termék neve, Ár). Szeretnénk az A101 kódú termék árát lekérdezni.

A G2 cellában van a keresett termékkód: A101. Az H1 cellában van a keresett oszlop neve: Ár.

A képlet a H2 cellába:

=FKERES(G2; A2:C5; HOL.VAN(H1; A1:C1; 0); HAMIS)

Ebben a képletben a HOL.VAN(H1; A1:C1; 0) rész megkeresi az “Ár” szöveget az A1:C1 tartományban, és visszaadja a pozícióját (ami jelen esetben 3). Ez a 3 lesz az FKERES oszlop_index paramétere. Így, ha az “Ár” oszlop helye megváltozik, a képlet automatikusan alkalmazkodik.

Több feltétel szerinti keresés (segédoszlop használatával)

Az FKERES függvény alapvetően csak egy feltétel (a tábla első oszlopa) alapján tud keresni. Ha több feltételnek kell megfelelnie az eredménynek (pl. termékkód ÉS szín alapján), akkor segédoszlopra van szükségünk.

Példa: Egy terméklistán, ahol a termékkódok ismétlődhetnek, de a termékkód és szín kombinációja egyedi.

Termékkód	Szín	Méret	Ár
P101	Piros	S	2000
P101	Kék	M	2200
P102	Piros	L	3000

Szeretnénk a P101 kódú, Kék színű termék árát lekérdezni.

1. Hozzon létre egy segédoszlopot: A forrástáblázatban (pl. az A oszlop elé vagy az E oszlopba) hozzon létre egy új oszlopot, amely összefűzi a keresési feltételeket. Például az E2 cellába írja be: =A2&B2. Ezt másolja le az összes sorra. Az eredmény: P101Piros, P101Kék, stb.

2. Használja az FKERES függvényt: Most már az összefűzött értéket keresheti az új segédoszlopban.

Ha a keresési értékek a G2 (P101) és H2 (Kék) cellákban vannak, akkor a segédoszlopot feltételezve a tábla első oszlopában (pl. E oszlop), az ár lekérdezéséhez a I2 cellába írja:

=FKERES(G2&H2; E2:H4; 4; HAMIS)

Itt az E2:H4 a kibővített táblázat, ahol az E oszlop a segédoszlop, és a 4 az ár oszlopának indexe a kibővített táblában. Eredmény: 2200.

FKERES több munkalapon keresztül

Az FKERES függvény nem csak egy munkalapon belül, hanem több munkalap között is képes adatokat keresni. Ehhez egyszerűen hivatkozzon a tábla paraméterben a másik munkalapra.

Példa: Van egy Termékek nevű munkalapunk, és egy Rendelések nevű munkalapunk. A Rendelések lapon szeretnénk lekérdezni a termék nevét a Termékek lapról.

A Termékek lapon az adataink az A1:C100 tartományban vannak.

A Rendelések lap B2 cellájában van a keresendő termékkód. A C2 cellába írjuk a képletet:

=FKERES(B2; Termékek!$A$1:$C$100; 2; HAMIS)

Ez a képlet a Rendelések lap B2 cellájában lévő értéket keresi a Termékek nevű munkalap A1:C100 tartományának első oszlopában, és visszaadja a második oszlopban lévő értéket.

Ez a technika lehetővé teszi, hogy adatokkal teli, jól strukturált Excel fájlokat hozzunk létre, ahol az egyes információtípusok külön lapokon vannak tárolva, de mégis könnyen összekapcsolhatók.

Az FKERES korlátai és alternatívái

Bár az FKERES függvény rendkívül hasznos, vannak bizonyos korlátai, amelyek miatt néha más megoldásokhoz kell folyamodnunk. Ezeknek az alternatíváknak az ismerete elengedhetetlen a haladó Excel felhasználók számára.

Miért nem mindig a legjobb választás az FKERES?

Az FKERES néhány alapvető korlátja:

• Csak jobbra tud keresni: Az FKERES csak a tábla első oszlopában tudja megkeresni a keresési_érték-et, és csak attól jobbra lévő oszlopokból tud eredményt visszaadni. Ha az eredmény oszlopa a keresési oszloptól balra van, az FKERES nem használható.
• Oszlop beszúrás/törlés érzékeny: Ahogy már említettük, a fix oszlop_index miatt a táblázat szerkezetének változása (oszlopok beszúrása vagy törlése) hibás eredményekhez vezethet, ha nem használunk dinamikus indexet.
• Csak az első találatot adja vissza: Ha több egyező érték is van a tábla első oszlopában, az FKERES mindig csak az elsőt adja vissza. Nincs beépített módja a többszörös találatok kezelésére.
• Lassabb nagy adathalmazok esetén: Nagyon nagy táblázatok (több tízezer vagy százezer sor) esetén az FKERES lassabbá teheti a számolást, különösen, ha sok függvényt használunk.

Ezek a korlátok vezettek a rugalmasabb alternatívák kifejlesztéséhez és elterjedéséhez.

INDEX és HOL.VAN függvények kombinációja

Az INDEX és HOL.VAN függvények kombinációja az FKERES egyik legerősebb és legrugalmasabb alternatívája. Számos előnnyel rendelkezik:

• Balra is képes keresni: A HOL.VAN megkeresi a keresési érték pozícióját egy tetszőleges oszlopban, az INDEX pedig visszaadja a megadott pozíción lévő értéket egy másik tetszőleges oszlopból. Így a keresési oszlop és az eredmény oszlopa egymáshoz képest bárhol elhelyezkedhet.
• Nem érzékeny az oszlop beszúrásra/törlésre: Mivel az INDEX és HOL.VAN külön-külön hivatkozik az oszlopokra, a táblázat szerkezetének változása kevésbé befolyásolja a képletet (feltéve, hogy a hivatkozott oszlopok nem mozdulnak el).
• Rugalmasabb: Képes több feltétel szerinti keresésre is segédoszlop nélkül, tömbképletekkel (bár ez bonyolultabb).

Az INDEX függvény szintaxisa:

=INDEX(tömb; sor_szám; [oszlop_szám])

• tömb: Az a tartomány, ahonnan az eredményt vissza szeretné kapni (csak az eredmény oszlopa, vagy az egész tábla).
• sor_szám: A sor sorszáma a tömbön belül.
• oszlop_szám: Az oszlop sorszáma a tömbön belül (opcionális, ha a tömb csak egy oszlop).

Példa az INDEX+HOL.VAN kombinációra:

Tegyük fel, hogy van egy terméklistánk az A1:D5 tartományban:

Termék ID	Ár	Termék neve	Kategória
P001	12000	Monitor	Elektronika
P002	5000	Egér	Elektronika
P003	8000	Billentyűzet	Elektronika

Szeretnénk az P002 Termék ID alapján lekérdezni a Termék nevét. Itt a Termék ID az első oszlopban van, a Termék neve pedig a harmadikban. Az FKERES működne.

De mi van, ha a Termék ID a második oszlopban lenne, és a Termék neve az elsőben? Az FKERES nem tudna balra keresni.

Az INDEX+HOL.VAN megoldás:

A G2 cellába beírjuk a keresett Termék ID-t: P002.

A H2 cellába a képlet:

=INDEX(C2:C5; HOL.VAN(G2; A2:A5; 0))

Magyarázat:

• HOL.VAN(G2; A2:A5; 0): Megkeresi a P002 érték pozícióját az A2:A5 tartományban (ami a Termék ID oszlopa). Ez 2-t ad vissza, mert a P002 a második sorban van.
• INDEX(C2:C5; 2): Az INDEX függvény a C2:C5 tartományból (ami a Termék neve oszlopa) a második elemet adja vissza, ami Egér.

Ez a kombináció a modern Excelben sok esetben előnyösebb, mint az FKERES, különösen ha rugalmasságra van szükség.

XKERES függvény (modern alternatíva)

Az Excel 365 és Excel 2019 verzióiban bevezetésre került az XKERES függvény (angolul XLOOKUP), amely az FKERES és a INDEX+HOL.VAN kombinációjának továbbfejlesztett utódja. Sok szempontból felülmúlja elődeit.

Az XKERES előnyei:

• Balra is keres: Nincs korlátozva az első oszlopra, képes balra is keresni.
• Egyszerűbb szintaxis: Sokkal intuitívabb és rövidebb a képlete.
• Rugalmasabb egyezési módok: Pontos egyezés (alapértelmezett), hozzávetőleges egyezés (kisebb vagy nagyobb), helyettesítő karakteres egyezés.
• Keresési módok: Képes alulról felfelé vagy felülről lefelé keresni, bináris keresést is támogat.
• Beépített hibakezelés: Van egy opcionális paramétere, amellyel megadhatjuk, mit jelenítsen meg, ha nincs találat.

Az XKERES függvény szintaxisa:

=XKERES(keresési_érték; keresési_tömb; visszaadási_tömb; [ha_nincs_találat]; [egyezési_mód]; [keresési_mód])

Példa az XKERES használatára (ugyanaz a terméklista):

=XKERES(G2; A2:A5; C2:C5; "Nincs ilyen termék")

Magyarázat:

• G2: Keresési érték (P002).
• A2:A5: Keresési tömb (Termék ID oszlopa).
• C2:C5: Visszaadási tömb (Termék neve oszlopa).
• "Nincs ilyen termék": Ha nincs találat, ezt a szöveget írja ki.

Az XKERES funkcióval a fenti példa is Egér eredményt adna, sokkal egyszerűbb képlettel és beépített hibakezeléssel. Ha elérhető, erősen ajánlott az XKERES használata az FKERES helyett.

DKERES függvény

A DKERES függvény (angolul HLOOKUP) az FKERES vízszintes megfelelője. A DKERES egy adott értéket keres egy táblázat vagy tartomány első sorában, majd a megtalált oszlopból egy megadott sorban lévő értéket ad vissza.

Ez akkor hasznos, ha az adatai nem oszlopokban, hanem sorokban vannak rendezve, és vízszintesen kell keresnie. A szintaxisa nagyon hasonló az FKERES-hez, csak a sorok és oszlopok szerepe cserélődik fel.

=DKERES(keresési_érték; tábla; sor_index; [tartomány_keresés])

A DKERES is hasonló korlátokkal rendelkezik, mint az FKERES (pl. csak lefelé tud keresni az első sorhoz képest, érzékeny a sorok beszúrására/törlésére).

Gyakorlati példák és esettanulmányok az FKERES használatára

Az FKERES függvény ereje igazán a valós életben, különböző adatelemzési feladatok során mutatkozik meg. Nézzünk meg néhány esettanulmányt, amelyek bemutatják, hogyan alkalmazható ez a funkció hatékonyan.

Készletnyilvántartás és árkalkuláció

Egy kiskereskedelmi vállalkozásnak folyamatosan nyomon kell követnie a készletét és a termékek árát. Az adatok két külön lapon vannak:

• Termékek lap: Tartalmazza a Termékkódot, Termék nevét és Egységárát.
• Készlet lap: Tartalmazza a Termékkódot és a Készleten lévő mennyiséget.

Cél: A Készlet lapon szeretnénk látni a termékek nevét és egységárát a Termékek lapról.

Termékek lap (A1:C5):

Termékkód	Termék neve	Egységár
TV001	Samsung TV	150000
HP002	HP Laptop	220000
SO003	Sony Hangfal	50000
LG004	LG Monitor	80000

Készlet lap (A1:C5):

Termékkód	Mennyiség	Termék neve (FKERES-sel)	Egységár (FKERES-sel)
TV001	10
HP002	5
SO003	8
LG004	12

A Készlet lap C2 cellájába a Termék nevéhez:

=FKERES(A2; Termékek!$A$2:$C$5; 2; HAMIS)

A Készlet lap D2 cellájába az Egységárhoz:

=FKERES(A2; Termékek!$A$2:$C$5; 3; HAMIS)

Ezeket a képleteket lemásolva az összes sorra, a Készlet lap automatikusan feltöltődik a terméknevekkel és árakkal, lehetővé téve a gyors készletérték számítást.

Számlázás és megrendelés feldolgozása

Egy számlázó rendszerben gyakran előfordul, hogy egy termékkód alapján kell lekérdezni a termék adatait. Képzeljük el, hogy van egy megrendelési űrlapunk, ahol a felhasználó beírja a termékkódot, és az Excel automatikusan kitölti a termék nevét és egységárát.

Terméklista lap (A1:C5): (ugyanaz, mint az előző példában)

Termékkód	Termék neve	Egységár
TV001	Samsung TV	150000
HP002	HP Laptop	220000
SO003	Sony Hangfal	50000
LG004	LG Monitor	80000

Megrendelés lap (A1:E5):

Termékkód	Termék neve (FKERES-sel)	Egységár (FKERES-sel)	Mennyiség	Összesen
HP002			2
TV001			1
SO003			3

A Megrendelés lap B2 cellájába a Termék nevéhez:

=HAHIBA(FKERES(A2; Terméklista!$A$2:$C$5; 2; HAMIS); "Érvénytelen kód")

A Megrendelés lap C2 cellájába az Egységárhoz:

=HAHIBA(FKERES(A2; Terméklista!$A$2:$C$5; 3; HAMIS); 0)

Az E2 cellába az Összesen értékhez:

=D2*C2

Ezek a képletek lehetővé teszik, hogy a termékkód beírása után automatikusan megjelenjenek az adatok, és kiszámolódjon az összeg, jelentősen felgyorsítva a számlázási folyamatot.

Diákok eredményeinek kezelése

Egy oktatási intézményben a diákok pontszámai alapján szeretnénk meghatározni az osztályzatukat és azonosítani a diák adatait.

Diák adatok lap (A1:C5):

Diák ID	Név	Osztály
D001	Kiss Anna	10A
D002	Nagy Bence	10B
D003	Kovács Éva	10A
D004	Tóth Gergő	10B

Osztályzati szintek lap (A1:B6):

Pontszám alsó határ	Osztályzat
0	Elégtelen
50	Elégséges
65	Közepes
80	Jó
90	Jeles

Eredmények lap (A1:E5):

Diák ID	Elért pontszám	Név (FKERES-sel)	Osztály (FKERES-sel)	Osztályzat (FKERES-sel)
D002	78
D001	92
D003	60

Az Eredmények lap C2 cellájába a Névhez:

=FKERES(A2; 'Diák adatok'!$A$2:$C$5; 2; HAMIS)

Az Eredmények lap D2 cellájába az Osztályhoz:

=FKERES(A2; 'Diák adatok'!$A$2:$C$5; 3; HAMIS)

Az Eredmények lap E2 cellájába az Osztályzathoz (itt használjuk az IGAZ paramétert):

=FKERES(B2; 'Osztályzati szintek'!$A$2:$B$6; 2; IGAZ)

Ez a kombináció lehetővé teszi a diákok adatainak és osztályzatainak automatikus megjelenítését a pontszámok alapján, ami nagyban megkönnyíti az adminisztrációt.

Legjobb gyakorlatok és tippek a hatékony FKERES használathoz

Az FKERES függvény hatékony és problémamentes használatához érdemes néhány bevált gyakorlatot követni. Ezek a tippek segítenek elkerülni a gyakori hibákat, és optimalizálni a képletek teljesítményét.

Adatok előkészítése

A jól strukturált és tiszta adatok alapvető fontosságúak az FKERES sikeres működéséhez.

• Tisztítsa meg az adatokat: Távolítsa el a felesleges szóközöket (SZÓKÖZ.TÖRLÉS), ellenőrizze a helyesírást, és győződjön meg róla, hogy az adatok egységes formátumúak.
• Ellenőrizze az adattípusokat: Győződjön meg róla, hogy a keresési érték és a tábla első oszlopában lévő értékek adattípusa megegyezik (pl. ne legyen szám szövegként tárolva, ha számot keres).
• Rendezze az adatokat IGAZ paraméter esetén: Ha hozzávetőleges egyezést használ, feltétlenül rendezze a tábla első oszlopát növekvő sorrendben.
• Egyedi azonosítók: Ha lehetséges, használjon egyedi azonosítókat (pl. termékkódok, ügyfélszámok) a kereséshez, hogy elkerülje a téves találatokat.

Hibakezelés

A hibák elkerülhetetlenek, de kezelhetők. A HAHIBA vagy HAHIÁNYZIK függvények használata kulcsfontosságú a felhasználóbarát és stabil munkafüzetek létrehozásához.

• HAHIBA: Ez a függvény bármilyen típusú Excel hibát (#HIÁNYZIK, #OSZT0!, #ÉRTÉK! stb.) képes kezelni.
• HAHIÁNYZIK: Ez a függvény kifejezetten a #HIÁNYZIK (N/A) hibák kezelésére szolgál, ami az FKERES leggyakoribb hibája. Specifikusabb, mint a HAHIBA.

Példa: =HAHIÁNYZIK(FKERES(A2; $D$2:$F$100; 2; HAMIS); "Nincs adat")

Olvashatóság és karbantarthatóság

A képletek és a munkafüzet hosszú távú fenntarthatósága érdekében:

• Nevesített tartományok használata: A táblázat tartományának megadása helyett (pl. $A$2:$C$100) nevezze el a tartományt (pl. TermékAdatok). Ez sokkal olvashatóbbá teszi a képletet: =FKERES(A2; TermékAdatok; 2; HAMIS). A nevesített tartományok ráadásul automatikusan bővülhetnek, ha táblázatként formázza őket.
• Dinamikus oszlopindex: Használja a HOL.VAN függvényt az oszlop_index dinamikus meghatározására, hogy a képlet rugalmasabb legyen a táblázat szerkezetének változásával szemben.
• Kommentek: Ha komplex képleteket használ, adjon hozzá kommenteket a cellákhoz, magyarázva a logika mögött.

Teljesítmény optimalizálása nagy adathalmazok esetén

Nagy mennyiségű adat (több tízezer sor) esetén az FKERES függvény lassíthatja a munkafüzetet. Néhány tipp a teljesítmény javítására:

• Csak a szükséges tartományt hivatkozza: Ne hivatkozzon feleslegesen nagy tartományokra (pl. A:C helyett A2:C1000), ha a táblázatnak van vége.
• Konvertálja értékekké: Ha az FKERES eredménye statikus lesz, vagy már nem kell dinamikusan frissülnie, másolja be az eredményeket értékekként. Jelölje ki a cellákat, másolja, majd illessze be “Értékek” opcióval.
• Rendezze az adatokat IGAZ paraméterrel: Ha hozzávetőleges egyezést használ, és a tábla rendezett, az IGAZ paraméterrel történő keresés gyorsabb lehet, mint a HAMIS paraméterrel történő pontos egyezés (bináris keresést használ).
• Használjon alternatívákat: Nagyon nagy adathalmazok esetén fontolja meg az XKERES (ha elérhető), INDEX+HOL.VAN, vagy akár a Power Query használatát, amelyek hatékonyabbak lehetnek.

Az FKERES függvény mesteri elsajátítása rendkívül értékes készség minden Excel felhasználó számára. A fenti útmutató és tippek segítségével Ön is magabiztosan és hatékonyan alkalmazhatja ezt a sokoldalú funkciót a mindennapi munkájában.