1111 Budapest, Bertalan Lajos utca 7-9., BME L épület

Excel VII. – HOL.VAN és XHOL.VAN

MATCH HOL.VAN

Egy rövidebb kihagyást követően idén is folytatjuk Exceles sorozatunkat, amelyben lépésről-lépésre próbáljuk bemutatni azokat a technikákat, tippeket és trükköket, mellyel logisztikai vonatkozású feladatainkat könnyebben tudjuk ellátni. Utolsó bejegyzésünkben egy pareto elemzést végeztünk, ezzel lezárva a legalapvetőbb függvények ismétlését. A következő bekezdésekben a HOL.VAN és XHOL.VAN függvények kerülnek bemutatásra, első körben csak röviden megnézzük működésüket. Később ezekre építve mutatunk be majd példákat.

HOL.VAN függvény

A fentnevezett függvény (angolul MATCH) megkeres egy értéket egy tartományban (sorban vagy oszlopban) és visszaadja annak helyzetét. Például van egy oszlopunk, amely a következő értékeket tartalmazza: 25, 12, 34, 8. Ha a függvény segítségével szeretnénk megkeresni ebben a tartományban a 12-es értéket, akkor a függvény értéke 2 lesz. (Hiszen a 2. elem a 12 az oszlopban.)

Szintaxisa a következő:

=HOL.VAN(keresési_érték;tábla;[egyezés_típusa])

Jól látszik, hogy a függvénynek 3 paramétere van, amelyekből az utolsó – a szögletes zárójel alapján – opcionális. A keresési értéket keressük a tábla tartományban. (Az előző példában a keresési érték volt a 12, a négy darab számjegyet tartalmazó oszlop pedig a tábla.) Az egyezés típusa háromféle lehet:

0 (pontos egyezés)

A függvény csakis akkor ad vissza értelmes eredményt, amennyiben a keresett érték megtalálható a keresési táblában. Ahogy az ábra bal oldalán is látszik, ha a keresett érték többször is szerepel ebben tartományban, akkor az első találat helyzetét kapjuk meg. Amennyiben nincs találat, úgy a függvény #HIÁNYZIK hibát eredményez. Fontos megjegyezni, hogy egy tartományban nem csak számokra kereshetünk rá, hanem szövegekre is. Szintén használhatóak ún. „Joker” karakterek, amelyekkel helyettesíthetünk a keresési érték esetén egy (?) vagy több (*) karaktert. Az ábra jobb oldalán erre mutatunk egy példát, a „bi” kifejezés utáni * tetszőleges számú karaktert helyettesít. Ha a *-ot kicserélnénk ?-re, akkor nem kapnánk helyes eredményt, hiszen a tábla nem tartalmaz olyan 3 karakteres szót, amely első kettő betűje b és i.

Excel HOL.VAN MATCH

-1 (nagyobb)

Első lépésben csökkenő sorrendbe kell rendezni az adatsort, azt a táblát (sort vagy oszlopot) amelyben az értéket keressük. Ebben az esetben nem kell pontosan meghatározni a keresett értéket, a függvény az első olyan szám helyzetét adja vissza, amely a lehető legkisebb a csökkenő sorrendben, de még nagyobb, mint a keresett érték. (Ld. az ábra bal oldala.)

1 (kisebb)

Első lépésben növekvő sorrendbe kell rendezni az adatsort, azt a táblát (sort vagy oszlopot) amelyben az értéket keressük. A függvény az első olyan szám helyzetét adja vissza, amely a lehető legnagyobb a növekvő sorrendben, de még kisebb, mint a keresett érték. (Ld. az ábra jobb oldala.)

Excel HOL.VAN MATCH

XHOL.VAN függvény

Office 365 előfizetéssel rendelkező felhasználók részére 2019. őszével elérhetővé vált az XHOL.VAN függvény (angolul XMATCH). Funkciója alapvetően azonos a régi HOL.VAN függvénnyel, visszaadja a keresett érték relatív helyzetét egy táblában (tartományban). Szintaxisa:

=XHOL.VAN(keresési_érték;keresési_tömb;[egyeztetési_mód];[keresési_mód])

Az első két paraméter kötelező: milyen értéket és hol keresünk.

Egyeztetési mód

Az „egyeztetési mód” hasonló a HOL.VAN függvény „egyezés típusa” paraméterrel, azonban nem kell sorbarendezni a keresési táblát használatukhoz:

  • 0: pontos egyezés, működése nem változott
  • -1: pontos egyezés vagy következő kisebb elem
  • 1: pontos egyezés vagy következő nagyobb elem
  • 2: helyettesítő karakterek használata (azonos az előzőleg bemutatott „Joker” karakterekkel

A lenti példákban a 14-es szám a keresett érték, a fenti képletben a harmadik paraméter -1, a lenti képletben pedig 1 értéket vett fel. Emiatt az első eredmény a 14-es számhoz legközelebb álló legkisebb szám sorszámát adja meg (fentről-lefele keresve), ami a 4. sorban lévő 13. A második eredmény a 14-eshez legközelebb eső nagyobb szám sorszámát adja (fentről-lefelé keresve), ami a 8. helyen álló 20-as szám.

Excel XHOL.VAN XMATCH

Keresési mód

A negyedik paraméter szintén újdonság, négy lehetséges értéket használhatunk, melyek közül a legtöbben az első kettőt fogják használni:

  • 1: szekvenciális keresés
  • -1: szekvenciális keresés visszafelé

A lenti példában továbbra is a 14-es számot vagy az ahhoz legközelebb eső kisebb érték helyzetét keressük (tehát a harmadik paraméter -1). A bal oldali esetben a függvény negyedik paramétere 1, jobb oldali esetben pedig -1. Jól látszik, hogy utóbbi eredménye a 4-es helyett 7, ugyanis lentről-felfelé keresve az alsó 13-as áll legközelebb a 14-es keresett értékhez.

Excel XHOL.VAN XMATCH

  • 2: bináris keresés, amennyiben az adatsort növekvő sorrendbe rendeztük
  • -2: bináris keresés, amennyiben az adatsort csökkenő sorrendbe rendeztük

Ezen paramétereket akkor érdemes használni, amennyiben óriási adatmennyiségben kell megtalálni egy értéket. Lényege, hogy a függvény először megnézi a keresési tábla középső elemét. Ha az eltér a keresett értéktől, akkor kizárja a tartomány azon felét a keresésből, amelyben nem lehet benne a keresett értékünk. Így folyamatosan felezi a tartomány hosszát, ezzel pedig drasztikusan lerövidíti a keresési időt.

Az idei első bejegyzésben mélyebben is megismerhettük a HOL.VAN és az új XHOL.VAN függvény használatát. Következő bejegyzésben az INDEX függvényt nézzük meg közelebbről, majd elkezdjük az eddig tanultakat kombinálni.

Összefoglalás
HOL.VAN XHOL.VAN függvények
Cikk címe
HOL.VAN XHOL.VAN függvények
Leírás
Idén is folytatjuk Exceles blogsorozatunkat. Heti bejegyzésünkben a HOL.VAN és XHOL.VAN Excel függvényeket mutatjuk be nektek.
Szerző
Kiadó
Innolog Solutions
Kiadó logó
Tetszett az oldal? Oszd meg másokkal is!
English