1111 Budapest, Bertalan Lajos utca 7-9., BME L épület
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.
-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.)
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.
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.
- 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.
