Telefonszámunk: 1-472-0679

XKERES függvény – a szerethető keresés 5 in 1

2020-09-30 - horvimi - Megtekintések száma: 186 - Kategória: Függvények
Hivatkozott Excel verzio: Office365

Az FKERES (VLOOKUP) több sebből vérzik, mégis az egyik leggyakrabban használt függvény. Az elmúlt években több cikket is írtam a témában, ezek közül kiemelném a kiváltásra használható INDEX/HOL.VAN párost, és az új, felgyorsított FKERES bejelentését. A gyenge pontok erősítésére a Microsoft kijött egy új, fejlesztett kereső függvénnyel, ami a keresztségben az XKERES (XLOOKUP) nevet kapta.

Lényeges információ, hogy az XKERES függvény  kizárólag az OFFICE 365-ben érhető el további régóta várt függvények társaságában.

Az FKERES gyenge pontjai

  • Csak a keresési tábla első oszlopában tud keresni, és csak tőle jobbra lévő oszlopból tudunk kérni információt
  • Az oszlopindex argumentum konstans, ezért érzékeny a keresési táblázat szerkezetének (oszlopsorrendjének) változására
  • Ha több oszlopból is kérünk vissza adatot, akkor mindannyiszor kerestetni kell a kulcsot
  • Előbbi miatt nagy táblázatoknál jelentős lassulást eredményezhet

Az XKERES lényegi működése

Az XKERES tulajdonképpen úgy működik, mint a HOL.VAN – INDEX páros, azaz előbb megkeresi egy oszlopban a keresendő elemet, majd egy másikból visszaadja az ugyanannyiadikat. Emiatt tud jobbról és balról is visszaadni eredményt.

eredmény_balrólVAGY eredmény_jobbról

Az XKERES újdonságai – 5 in 1

  • Balról is ad eredményt
  • Nem érzékeny az oszlopok elmozdulására
  • Beépített hibakezelés (Nem kell HAHIBA)
  • Fentről lefelé és lentről felfelé is tud keresni, ezáltal az első és az utolsó előfordulást is kérhetjük
  • Vízszintesen is működik (Nem kell VKERES)

Lényeges bónusz, hogy az egyezés típus alapértelmezése a pontos egyezőség, tehát ha nem adjuk meg azt az argumentumot, akkor nullának tekinti.

Nem pontos egyezőségnél kereshetjük a legközelebbi kisebbet vagy nagyobbat, ráadásul ebben az esetben a keresési táblának nem kell rendezettnek lennie!!!

Az XKERES paraméterezése

XKERES (
        keresett_érték; 
        keresési_tömb; 
        eredmény_tömb; 
        [ha_nincs_találat]; 
        [illesztési_mód]; 
        [keresési_mód]
        )
Keresett érték Amit keresünk
Keresési tömb 1 Dimenziós tömb, vízszintes vagy függőleges, ebben keres
Eredmény tömb 1 vagy 2 Dimenziós tömb,a keresési tömbbel azonos magasságú vagy szélességű, ebből adja vissza a találatot a megfelelő sorból vagy oszlopból
Ha nincs találat [nem kötelező] Mi legyen, ha nem talált
Illesztési mód  [nem kötelező] 0 = pontos egyezés (alap), -1 = egyenlő vagy legközelebbi kisebb, 1 = egyenlő vagy legközelebbi nagyobb, 2 = Joker illesztés
Keresési mód  [nem kötelező] 1 = lineáris, elölről hátra keres (alap), -1 = lineáris, hátulról előre keres, 2 = bináris keresés növekvő rendezettség, -2 = bináris keresés csökkenő rendezettség

 Alap működés

XKERES-alapműködés

Mit keres, Hol keresse, Honnan adja vissza az ugyanannyiadik elemet, KÉSZ.

Nem kell egyezés típus, alapból lineáris keresést csinál, azaz pontos egyezőséget keres. Ha nem találja a keresett értéket, akkor a szokásos #HIÁNYZIK üzenetet adja.

Hibakezelés

XKERES-hibakezelés

Beleépítették a hibakezelést, nem kell HAHIBA (IFERROR) függvénybe ágyazni, külön paraméterben megadhatjuk, hogy mi legyen hiba esetén. Ide írhatunk konstans értéket vagy akár egy másik képletet.

Közelítő keresés

XKERES-közelítő

Jól látható, hogy a jobb oldali keresési tábla nincs rendezett állapotban, a bal oldali táblában mégis jók az eredmények. Az XKERES ötödik argumentumával megadhatjuk, hogy a legközelebbi kisebbet vagy nagyobbat kérjük vissza, ha nem talál pontos egyezést.

XKERES-közelítő-argumentumok

Keresési irány

XKERES-keresési-irány-példa

A megadott kódú termék első és utolsó előfordulásához is könnyedén megkaphatjuk a kívánt értéket. Ehhez csupán a hatodik argumentumot kell megfelelően beállítani a fenti táblázat alapján.

XKERES-keresési-irány-argumentumok

Mi van, ha több tételt is át akarunk hozni?

Ez esetben reflexszerűen egymás után több XKERES-t használnánk. Ezt lehet ugyan, de akkor töbször keresnénk meg ugyanazt az elemet. Ha ez nem okoz sebesség problémákat (nagy keresési táblák esetén), akkor csak nyugodtan.

De az igazán optimális megoldás a keresés és az indexelés szétválasztása, azaz egy HOL.VAN vagy esetleg egy XHOL.VAN (mert ilyen is van ám!), majd utána néhány INDEX használata lenne.

Összefoglalás

A cikkben szereplő leírás és példák bevezető jellegűek, a cél az volt, hogy bemutassam ezt az új és remek függvényt. A benne rejlő potenciál távolabbra mutat, amit majd a további cikkekből lehet megtudni, például

  • A találathoz tartozó egész sort/oszlopot vagy annak egy részét is vissza lehet kérni
  • Kérhetünk több, nem összefüggő tételt is, így nem kell többszöri XKERES
  • Lehet 2D keresést csinálni egymásba ágyazott XKERES-t használva

Lássuk az alapokat Videón is

 

Vélemény, hozzászólás?