Az Excel egyik leggyakoribb függvénye az FKERES függvény, vagy angolul VLOOKUP függvény. Biztosan ismered, szereted a korlátaival együtt. A hiányosságait próbálja orvosolni az XKERES függvény vagy angolul az XLOOKUP függvény. Az XKERES függvényről az XLOOKUP függvény (XKERES függvény) bejegyzésünkben olvashatsz bővebben.

Amit viszont egyik függvénnyel sem tudsz megoldani, akkor azt megpróbálhatod egy felhasználói függvénnyel. (UDF, azaz User Defined Function). A UDF-t a VBE (Visual Basic Editor) felületen lehet elkészíteni.

Arról, hogy mi az EXCEL VBA, az Excel VBA programozás – Ismertető bejegyzésünkben olvashatsz bővebben! Ebben a bejegyzésben két egyedi keresés létrehozását tudod elolvasni:

  • fkeresx, amikor egy árlistában kell megkeresni egy termék harmadik legjobb árát
  • fkeres2t, amikor ugyanazt a terméket több helyen is meg lehet vásárolni, és meg kell keresni, hogy adott helyen mennyibe kerül a termék

Nézzük is meg ezeket és a hozzájuk tartozó VBA Kódokat!

A feladatban használt fájl innen letöltheted: VLOOKUP VBA

VLOOKUP VBA – x-edik találat

Feltételezem, hogy a VOOKUP alaptulajdonságait ismered. Ha még új neked a függvény, olvasd el FKERES függvény – Hogyan használjam? című bejegyzésünket.

Az első oszlopban keres, és az első találat sorát (növekvő sorba rendezettnek kell lennie) ütközteti a megadott oszloppal, és adja eredményül.

Ezzel szemben az megírt UDF-ünk nem az első találat sorával, hanem a megadott x-dik találat sorát ütközteti a megadott oszloppal és adja eredményül. Ennek az egyedi függvénynek fkeresx lesz a neve, persze Te adhatsz neki más nevet is.

Argumentumai: fkeresx(KeresésiÉrték; Tartomány; HányadikOszlop; HányadikTalálat), a HányadikTalálat opcionális, ha nincs megadva, akkor az alapértelmezett az 1, ahogy az alábbi képen láthatod.

VLOOKUP VBA

A Visual Basic fejlesztő felületét (VBE) az ALT+F11 billentyűparanccsal érheted el. Adj hozzá egy új modult (angolul: module) a fájlhoz. Részletes leírást  az Irányított beillesztés VBA kóddal – Értékek és képletek nevű bejegyzésben olvashatsz.

Ebbe az üres modulba kell bemásolni ezt a kódot:

VLOOKUP VBA – x-edik találat VBA kód

Mindez VBA kóddal így néz ki:

Copy to Clipboard
UDF VBA

A függvényhez részletes magyarázatot a minta Excel fájlban, a VBA kódban találsz.

Érdekesség, hogy ez a függvény tud a keresési oszlop előtti oszlopokban is találatot adni, ez esetben az oszlopszám argumentumban negatív számot kell megadni. Erre is találsz példát a mintafájlban.

VLOOKUP VBA – Két tartományban keres

Másik UDF először a tartomány1-en belül megkeresi a keresési érték1-ket, megjegyzi ezeket a sorokat ütközteti a Tartomány2-vel és keresi a keresési érték2-t. A megtalált sorral ütközteti a Tartomány3-al, és azt adja eredményül. Ennél a függvénynél célszerű mindhárom tartományt úgy megadni, hogy csak egy oszlopa legyen.

Ennek az egyedi függvénynek fkeres2t lesz a neve, persze Te adhatsz neki más nevet is.

Argumentumai: fkeres2t(KeresésiÉrték1; Tartomány1; KeresésiÉrték2; Tartomány2; Tartomány3)

VLOOKUP VBA UDF

Az előző kód alá be lehet másolni ezt a kódot is, de egy új modulba is másolhatod. Ennek UDF-nek a magyarázatát szintén a letölthető Excel fájlban, a modulban találod.

VLOOKUP VBA – Két tartományban keres – VBA kód

Fontos megjegyezni, hogy ezek az egyedi függvények nem biztos, hogy 100%-an jól működnek minden esetben, hiszen nem biztos, hogy minden eset le lett tesztelve.

Copy to Clipboard

UDF használata és formai követelményei

A UDF-ek formai követelményei:

  • Function paranccsal kell kezdődjön
  • Azután a függvény nevét kell megadni (szóköz nem megengedett)
  • A zárójelben kell deklarálni az argumentumokat, azaz a függvények bemenő adatait
  • End Function-el kell befejezni

A UDF-eket (ha jól csináltál mindent) egy „=” jel beírása után fel fogja ajánlani az Excel. Más módon is rá tudsz keresni. A jól ismert „fx” gombra kattintva előugrik a függvény beszúrása ablak, és ott a „felhasználói” függvény kategóriát kiválasztva, láthatóvá vállnak az egyedi függvényeid. Kiválasztod a megfelelőt, és akkor már úgy jelenik meg, mint ahogy feljebb már látható.

VLOOKUP VBA

Remélem számodra is hasznos volt ez a blogposzt. Kérlek oszd meg másokkal is!

Van 3 INGYENES Excel VBA oktató videónk, mellyel azonnal el is kezdheted a tanulást.

Ne halogasd, kérd a 3 videót most!