VLOOKUP függvény VBA-val továbbfejlesztve

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 függvény VBA-val továbbfejlesztve

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.

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

Function fkeresx(ByVal KeresésiÉrték As Variant, ByVal Tartomány As Range, ByVal HányadikOszlop As Integer, Optional ByVal HányadikTalálat As Integer)
Dim SorokSzáma As Double
Dim OszlopokSzáma As Double
Dim AktuálisSor As Double
Dim TaláltSor As Double
Dim Találat As Double
Dim Hibás As Boolean

fkeresx = CVErr(xlErrNA)
Hibás = True

AktuálisSor = 1
TaláltSor = 0
Találat = 0
If HányadikTalálat = 0 Then HányadikTalálat = 1
SorokSzáma = Tartomány.Rows.Count
OszlopokSzáma = Tartomány.Columns.Count

If HányadikOszlop > OszlopokSzáma Then
    fkeresx = CVErr(xlErrRef)
Else
    Do
        If Tartomány.Cells(AktuálisSor, 1) = KeresésiÉrték.Value Then
            TaláltSor = AktuálisSor
            Találat = Találat + 1
            Hibás = False
            If HányadikTalálat <= Találat Then Exit Do
            Hibás = True
            TaláltSor = 0
        End If
        AktuálisSor = AktuálisSor + 1
    Loop Until ((HányadikTalálat = Találat) Or (AktuálisSor > SorokSzáma))
End If

If Hibás = False Then fkeresx = Tartomány.Cells(TaláltSor, HányadikOszlop)
End Function

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)

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

Function fkeres2t(ByVal KeresésiÉrték1 As Variant, ByVal Tartomány1 As Range, ByVal KeresésiÉrték2 As Variant, ByVal Tartomány2 As Range, ByVal Tartomány3 As Range)

Dim SorokSzáma As Double
Dim ESor1, Sor1, ESor2, Sor2, ESor3, Sor3 As Double
Dim OszlopokSzáma As Integer
Dim AktuálisSor As Integer
Dim TaláltSor As Integer
Dim Találat As Integer
Dim TaláltMinSor As Double
Dim TaláltMaxSor As Double

Sor1 = Tartomány1.Rows.Count
ESor1 = Tartomány1.Row
Sor2 = Tartomány2.Rows.Count
ESor2 = Tartomány2.Row
Sor3 = Tartomány3.Rows.Count
ESor3 = Tartomány3.Row

fkeres2t = CVErr(xlErrNA)

If ESor1 <> ESor2 Or ESor2 <> ESor3 Or Sor1 <> Sor2 Or Sor2 <> Sor3 Then
    fkeres2t = CVErr(xlErrValue)
Exit Function

TaláltMinSor = 0
TaláltMaxSor = 0

TaláltMinSor = WorksheetFunction.Match(KeresésiÉrték1, Tartomány1, 0)
TaláltMaxSor = WorksheetFunction.Match(KeresésiÉrték1, Tartomány1, 1)
TaláltSor = WorksheetFunction.Match(KeresésiÉrték2, Range(Cells(TaláltMinSor, Tartomány2.Column), Cells(TaláltMaxSor, Tartomány2.Column)), 0)

fkeres2t = Cells(TaláltMinSor + TaláltSor - 1, Tartomány3.Column).Value
End Function

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ó.

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

VLOOKUP függvény VBA-val továbbfejlesztve