Az FKERES függvény turbózása: Így hozhatsz létre ciklusos keresést Excel VBA programozással

Amikor az Excel funkcióiról esik szó, az **FKERES** (VLOOKUP) az egyik leggyakrabban emlegetett név. Sokak számára ez az első „haladó” Excel funkció, amivel találkoznak, és amely jelentősen felgyorsítja a mindennapi munkát. Azonban, mint minden eszköznek, az **FKERES**-nek is vannak korlátai. Mi történik, ha nem csupán az első találatot szeretnénk megtalálni, hanem az összes releváns adatot, vagy ha éppen egy komplex függőségi láncot kellene feltérképeznünk, ami akár önmagába is visszatérhet? Ilyenkor lép színre az **Excel VBA programozás**, amely turbófeltöltőt ad az **FKERES** alapötletéhez, és lehetővé teszi a valódi **ciklusos keresés** megvalósítását.

### Az FKERES alapvető korlátai és a felmerülő igény

Az **FKERES** egy zseniális eszköz, ha egy adott értékhez tartozó információt szeretnénk lekérni egy táblázatból. Megkeresi a megadott értéket a táblázat *első* oszlopában, és amikor megtalálja, visszaadja a hozzá tartozó értéket egy másik, általunk megadott oszlopból. Egyszerű, gyors és hatékony… de csak az első találatig. Mi van, ha a keresett érték többször is szerepel a táblázatban? Az **FKERES** kíméletlenül csak az elsőt adja vissza, a többit figyelmen kívül hagyja. 🤷‍♀️

Ez a korlátozás különösen problémássá válhat az **adatelemzés** és **adatkezelés** során, amikor:
* Egy ügyfélnek több megrendelése is van, és mindegyikre szükségünk van.
* Egy terméknek több beszállítója van, és mindegyiket látni akarjuk.
* Projektfeladatok közötti függőségeket vizsgálunk, ahol egy feladat több előfeltételre is épülhet, vagy több másik feladat előfeltétele.
* Egy összetett termék (gyártási darabjegyzék – BOM) alkotóelemeit szeretnénk listázni, melyek maguk is összetett részegységek.

Ezekben az esetekben már nem elegendő az **FKERES** egyetlen találata. Egy olyan megoldásra van szükség, amely képes végigpásztázni az egész adathalmazt, az összes releváns információt összegyűjteni, sőt, akár logikai láncolatokat is követni. Itt jön képbe az **Excel VBA programozás**, mint a végső, testreszabható megoldás.

### Mi is az a ciklusos keresés és miért fontos?

A **ciklusos keresés** tágabb értelemben azt jelenti, hogy egy keresési folyamat során nem elégszünk meg az első találattal, hanem szisztematikusan végigmegyünk az összes lehetséges elemen, és minden relevantnak ítélt találatot feldolgozunk. A „ciklusos” jelző azonban ennél mélyebbre is mutathat: gyakran utal arra, hogy a keresési folyamat során a talált elemek valamilyen módon visszahatnak a további keresésre, vagy egy hierarchikus, esetleg körkörös függőségi láncot kell felderítenünk. Például:
* **Összes egyezés:** Egy egyszerűbb megközelítés szerint a **ciklusos keresés** az összes olyan sort megtalálja, ahol a keresési kritérium teljesül. Ez a legegyszerűbb kiterjesztése az **FKERES**-nek.
* **Függőségi láncok:** Egy komplexebb forgatókönyvben az egyik keresési találat lesz a következő keresés kiindulópontja. Gondoljunk egy projekttervre, ahol A feladat előfeltétele B, B-é C, és C-é D. Ha meg akarjuk tudni, mi kell D-hez, az egy egyszerű lánc. De mi van, ha D-nek közvetetten szüksége van A-ra is? Vagy egy szervezeti hierarchiában egy vezető beosztottja egy másik részleg vezetőjének beosztottja is? Ezek igazi **ciklusos keresés** kihívások.

Az ilyen típusú keresések automatizálása elengedhetetlen a modern **adatelemzés** és **automatizálás** terén. Jelentősen csökkenti a manuális munkát, minimalizálja a hibalehetőségeket és felgyorsítja a döntéshozatalt. ✨

### Belépő a VBA világába: A turbófeltöltő bekapcsolása

Az Excel beépített **VBA programozás** felülete (Visual Basic for Applications) egy rendkívül erőteljes eszköz, amely lehetővé teszi, hogy saját **makrókat** és **függvényeket** írjunk, amelyek meghaladják a standard Excel funkciók képességeit. A **VBA kód** írása eleinte ijesztőnek tűnhet, de az alapok elsajátítása után rájövünk, hogy a lehetőségek tárháza végtelen.

**Első lépések a VBA-val:**
1. **Fejlesztőeszközök lap aktiválása:** Ha még nem látod a „Fejlesztőeszközök” fület az Excel menüszalagon, kapcsold be! Fájl > Beállítások > Szalag testreszabása, majd jelöld be a „Fejlesztőeszközök” négyzetet. ✅
2. **VBA szerkesztő megnyitása:** Kattints a „Fejlesztőeszközök” lapon a „Visual Basic” gombra, vagy használd az Alt + F11 billentyűkombinációt. Megnyílik a VBA szerkesztő (VBE).
3. **Modul beszúrása:** A VBE-ben kattints a Beszúrás > Modul menüpontra. Ide írjuk a **VBA kódunkat**.

### A ciklusos keresés VBA kóddal: Lépésről lépésre

Most pedig nézzük, hogyan hozhatunk létre egy **VBA makrót**, amely az **FKERES** korlátait túllépve képes megtalálni az összes találatot, és ezt a logikát kiterjesztve egyfajta „ciklusos” megközelítést alkalmazni. A példa kedvéért tételezzük fel, hogy van egy „Adatok” nevű munkalapunk, ahol az A oszlopban keresünk egy értéket, és a B oszlopból szeretnénk lekérni a hozzá tartozó információkat. Az eredményeket egy „Találatok” nevű új munkalapra írjuk.

„`vba
Sub CiklusosKereses()
‘ Deklaráljuk a változókat
Dim KeresettErtek As Variant
Dim KeresesiTartomany As Range
Dim Cella As Range
Dim TalalatokMunkalap As Worksheet
Dim SorSzamlalo As Long
Dim AdatMunkalap As Worksheet

‘ ✨ Fejlesztési tipp: Kezdeti beállítások, hibakezelés ✨
On Error GoTo HibaKezeles
Application.ScreenUpdating = False ‘ Képernyőfrissítés kikapcsolása a gyorsaság érdekében

‘ Adatlap beállítása
Set AdatMunkalap = ThisWorkbook.Sheets(„Adatok”)

‘ Keresett érték bekérése a felhasználótól
KeresettErtek = InputBox(„Kérlek, add meg a keresett értéket:”, „Ciklusos Keresés”)

‘ Ellenőrizzük, hogy a felhasználó megadott-e értéket
If KeresettErtek = „” Then
MsgBox „Nem adtál meg keresendő értéket. A művelet megszakítva.”, vbCritical
GoTo Befejezes
End If

‘ Beállítjuk a keresési tartományt (itt feltételezzük, hogy az „Adatok” lap A oszlopában keresünk)
‘ Az UtolsoSor változó segít dinamikusan meghatározni a tartományt
Dim UtolsoSor As Long
UtolsoSor = AdatMunkalap.Cells(Rows.Count, „A”).End(xlUp).Row
Set KeresesiTartomany = AdatMunkalap.Range(„A1:A” & UtolsoSor)

‘ Találatok munkalapjának előkészítése
‘ Ellenőrizzük, létezik-e már a „Találatok” lap, ha igen, töröljük a tartalmát
On Error Resume Next ‘ Hibakezelés bekapcsolása a lap ellenőrzéséhez
Set TalalatokMunkalap = ThisWorkbook.Sheets(„Találatok”)
On Error GoTo HibaKezeles ‘ Hibakezelés visszaállítása

If Not TalalatokMunkalap Is Nothing Then
TalalatokMunkalap.Cells.ClearContents ‘ Töröljük a régi találatokat
Else
Set TalalatokMunkalap = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
TalalatokMunkalap.Name = „Találatok”
End If

‘ Fejléc írása a Találatok lapra
TalalatokMunkalap.Cells(1, 1).Value = „Keresett érték”
TalalatokMunkalap.Cells(1, 2).Value = „Talált érték (A oszlop)”
TalalatokMunkalap.Cells(1, 3).Value = „Hozzá tartozó érték (B oszlop)”
TalalatokMunkalap.Cells(1, 4).Value = „Sor száma”
SorSzamlalo = 2 ‘ A találatok írását a 2. sorból kezdjük

‘ Végigmegyünk a keresési tartományon ciklussal
For Each Cella In KeresesiTartomany
‘ Feltétel: Ha a cella értéke megegyezik a keresett értékkel
If Cella.Value = KeresettErtek Then
‘ 💡 Tipp: Itt tároljuk el a találatokat 💡
TalalatokMunkalap.Cells(SorSzamlalo, 1).Value = KeresettErtek
TalalatokMunkalap.Cells(SorSzamlalo, 2).Value = Cella.Value ‘ Ahol találtuk
TalalatokMunkalap.Cells(SorSzamlalo, 3).Value = Cella.Offset(0, 1).Value ‘ A hozzá tartozó érték a B oszlopból
TalalatokMunkalap.Cells(SorSzamlalo, 4).Value = Cella.Row ‘ A sor száma
SorSzamlalo = SorSzamlalo + 1 ‘ Következő sorra ugrunk
End If
Next Cella

‘ Ha nincs találat
If SorSzamlalo = 2 Then
TalalatokMunkalap.Cells(SorSzamlalo, 1).Value = „Nincs találat a(z) ‘” & KeresettErtek & „‘ értékre.”
End If

‘ Oszlopszélességek automatikus igazítása a jobb olvashatóság érdekében
TalalatokMunkalap.Columns(„A:D”).AutoFit

MsgBox „A ciklusos keresés befejeződött. Az eredmények a ‘Találatok’ munkalapon találhatók.”, vbInformation

Befejezes:
Application.ScreenUpdating = True ‘ Képernyőfrissítés visszakapcsolása
Exit Sub

HibaKezeles:
MsgBox „Hiba történt a makró futtatása során: ” & Err.Description, vbCritical
GoTo Befejezes
End Sub
„`

#### A Kód Magyarázata Részletesen:

1. **Változók deklarálása (`Dim … As …`):**
* `KeresettErtek`: A felhasználó által megadott érték, amit keresni fogunk.
* `KeresesiTartomany`: Az a tartomány, ahol a keresést végezzük (pl. az `Adatok` lap `A` oszlopa).
* `Cella`: Egyetlen cellát reprezentál a `KeresesiTartomany`-ban, amint a ciklus végigmegy rajta.
* `TalalatokMunkalap`: A munkalap, ahová az eredményeket írjuk.
* `SorSzamlalo`: Segít nyomon követni, melyik sorba írjuk a következő találatot a `TalalatokMunkalap`-on.
* `AdatMunkalap`: A munkalap, ahol a keresendő adatok vannak.

2. **Hibakezelés és optimalizálás:**
* `On Error GoTo HibaKezeles`: Egy általános hibakezelő blokk, ami egy `HibaKezeles` címkéhez ugrik, ha valamilyen hiba merül fel.
* `Application.ScreenUpdating = False`: Ez egy **optimalizálás**. Kikapcsolja a képernyő frissítését, amíg a makró fut, ami jelentősen felgyorsíthatja a műveletet, különösen nagy adatmennyiségek esetén. A makró végén vissza kell kapcsolni.

3. **Adatlap és keresett érték bekérése:**
* `Set AdatMunkalap = ThisWorkbook.Sheets(„Adatok”)`: A makró feltételezi, hogy van egy „Adatok” nevű lap.
* `KeresettErtek = InputBox(…)`: Felugró ablakban kérjük be a felhasználótól a keresendő értéket.
* Ellenőrizzük, hogy a felhasználó megadott-e értéket.

4. **Keresési tartomány beállítása:**
* `UtolsoSor = AdatMunkalap.Cells(Rows.Count, „A”).End(xlUp).Row`: Dinamikusan meghatározza az „A” oszlop utolsó kitöltött sorát, így a makró bármilyen méretű adathalmazon működik.
* `Set KeresesiTartomany = AdatMunkalap.Range(„A1:A” & UtolsoSor)`: Beállítja a teljes „A” oszlopot (az elsőtől az utolsó kitöltött sorig) keresési tartományként.

5. **Találatok munkalapjának előkészítése:**
* A **VBA kód** ellenőrzi, létezik-e már „Találatok” nevű lap. Ha igen, törli annak tartalmát, hogy tiszta lappal indulhasson. Ha nem, létrehozza a lapot.
* Fejléceket ír az első sorba, hogy az eredmények áttekinthetőek legyenek.

6. **A ciklus: A valódi „ciklusos keresés” magja:**
* `For Each Cella In KeresesiTartomany`: Ez a **ciklus** megy végig a `KeresesiTartomany` *összes* celláján, egyenként. Ez az, ami az **FKERES**-től különbözik, mivel nem áll meg az első találatnál.
* `If Cella.Value = KeresettErtek Then`: Minden egyes cella értékét összehasonlítja a `KeresettErtek`-kel.
* Ha van egyezés, akkor a `TalalatokMunkalap`-ra írja ki:
* A keresett értéket.
* Ahol megtalálta (az `A` oszlopból).
* A hozzá tartozó értéket (a `B` oszlopból a `Cella.Offset(0, 1).Value` segítségével, ami az aktuális cellától 0 sorral eltolva, 1 oszloppal jobbra lévő cellát jelenti).
* A sor számát.
* `SorSzamlalo = SorSzamlalo + 1`: Növeli a sor számlálót, hogy a következő találat a következő sorba kerüljön.

7. **Találat hiányának kezelése:**
* Ha a `SorSzamlalo` értéke még mindig 2 (azaz csak a fejléceket írtuk ki, és nem találtunk egyezést), akkor egy üzenetet ír ki, hogy nincs találat.

8. **Formázás és üzenet:**
* `TalalatokMunkalap.Columns(„A:D”).AutoFit`: Automatikusan beállítja az oszlopszélességeket.
* `MsgBox …`: Tájékoztatja a felhasználót a művelet befejezéséről.

9. **Tisztítás (`Befejezes` és `HibaKezeles`):**
* `Application.ScreenUpdating = True`: Visszakapcsolja a képernyő frissítését.
* `Exit Sub`: Kilép a szubrutinból.
* A `HibaKezeles` blokk kezeli az esetleges futásidejű hibákat, és egy értelmes üzenetet jelenít meg.

### A „ciklusos” dimenzió kiterjesztése

Az imént bemutatott **makró** nagyszerűen használható az **FKERES** helyett, ha minden egyezést meg szeretnénk találni. De mi van, ha valóban egy **ciklusos keresés**-re van szükségünk, ahol egy találat további kereséseket indít?

Képzeljük el, hogy egy termék darabjegyzékét (Bill of Materials – BOM) akarjuk feltérképezni. Egy `Adatok` lapon van egy listánk, ahol az első oszlopban a „Szülő komponens”, a másodikban pedig a „Gyermek komponens” található.
`Adatok` lap:
| Szülő Komponens | Gyermek Komponens |
| :————– | :—————- |
| A | B |
| A | C |
| B | D |
| C | E |
| E | F |
| D | G |
| G | H |
| H | A | 📚 **Szakértői vélemény:** „Évekig küzdöttem az Excel beépített függvényeivel, amikor komplexebb, több dimenziós adatokkal kellett dolgoznom. Emlékszem, egyszer egy több száz termékből álló gyártási darabjegyzék (BOM) feldolgozásánál akadtam el, ahol minden terméknek lehetett több alkatrésze, és azoknak is alkatrészei. Az FKERES csak az első szintet hozta, a nested függőségek manuális kibogozása pedig napokig tartott. Amikor először írtam egy **VBA makrót**, ami képes volt rekurzívan végigmenni ezen a hierarchián, az olyan volt, mintha egy új dimenziót nyitottam volna meg az **adatelemzés** terén. Nem csak időt spóroltam, de olyan összefüggésekre is rávilágítottam, amik korábban rejtve maradtak. Egy ilyen **VBA kód** írása, még ha eleinte kihívás is, hosszú távon megtérülő befektetés a hatékonyságba.”

### Teljesítmény és optimalizálás ⚠️

Nagy adathalmazok esetén a **VBA makrók** futási ideje kulcsfontosságú lehet. Néhány tipp a **VBA kód** optimalizálásához:
* **`Application.ScreenUpdating = False`:** Ahogy a fenti példában is látható, ez az egyik legfontosabb lépés.
* **`Application.Calculation = xlCalculationManual`:** Ha sok képlet van a lapon, a képletek újraszámolásának kikapcsolása szintén gyorsít. A makró végén vissza kell kapcsolni: `Application.Calculation = xlCalculationAutomatic`.
* **Objektumváltozók használata:** `Set AdatMunkalap = ThisWorkbook.Sheets(„Adatok”)` – Gyorsabb, mint minden alkalommal `Sheets(„Adatok”)` írni.
* **`For Each Cella In Tartomany` vs. `For i = 1 To UtolsoSor`:** Általában a `For Each` elegánsabb, de néha a `For i` ciklus indexeléssel (pl. `Cells(i, 1)`) gyorsabb lehet.
* **Adatok tömbbe olvasása:** A leggyorsabb módszer, ha nagy adathalmazzal dolgozunk. Ahelyett, hogy egyenként olvasnánk a cellákat, beolvassuk a teljes tartományt egy **VBA** tömbbe, ott végezzük el a számításokat, majd a kész eredményt egyetlen lépésben írjuk vissza a munkalapra. Ez drasztikusan csökkenti az Excel és a **VBA** közötti kommunikáció idejét.

### Összefoglalás és Következtetés

Az **FKERES** függvény kétségtelenül a mindennapi Excel használat elengedhetetlen része, de amikor a szükség úgy hozza, hogy túl kell lépnünk az első találat korlátain, vagy komplex, **ciklusos keresés**-eket kell megvalósítanunk, az **Excel VBA programozás** adja a kezünkbe a kulcsot. A fenti példa bemutatta, hogyan lehet egy egyszerű, de rendkívül hasznos **makrót** létrehozni, ami az **FKERES**-t turbózva az összes találatot kilistázza. Innen már csak egy lépés, hogy a logikát továbbfejlesztve valódi függőségi láncokat, hierarchiákat térképezzünk fel, és ezzel soha nem látott hatékonyságot érjünk el az **adatelemzés** és **automatizálás** terén. Ne félj belevágni a **VBA kód** írásába; a befektetett idő és energia sokszorosan megtérül majd a jövőben! 💡