Töltse le az alábbi fájlt: xlookup_gyakorlas.xslx és oldja meg a bennük lévő feladatokat. Megoldás
Bevezető
A feladatok megoldása során végig az XLOOKUP (XKERES) függvényt kell használni.
A függény paraméterezése:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XKERES(keresett_érték; keresési_tömb; visszaadott_tömb; [ha_nincs_találat]; [illesztési_mód]; [keresési_mód])
A szögletes zárójelekkel jelölt opcionális paraméterek nélkül a függvény az alábbi műveletet végzi el: a lookup_value értéket megkeresi a lookup_array tartományban és megállapítja, hogy hányadik helyen szerepel (pl. 5.), végül pedig visszaadja a return_array-nak a megfelelő indexű elemét.
A mellékelt képen lévő példában egy adatbázisunk van országnév-országkód párokról. Szeretnénk lekérdezni egy adott országhoz tartozó országkódot. A lekérdezendő ország nevét az E3 cella tartalmazza, ez lesz a lookup_value. Ezt az adatot az országnevek listájában keressük, az az a B3:B5 tartományban (lookup_array). Mivel Magyarország a listában az 1. helyen szerepel, ezért az országkódok listájából is az 1. értéket szeretnénk visszakapni. Tehát a visszatérési tartomány, azaz a return_array a C3:C5 lesz.
Amennyiben nincs megadva az [if_not_found] paraméter és a függvény nem találja a keresett értéket, úgy a #N/A hibaüzenetet fogjuk kapni. Egyébként pedig megadható, hogy mit írjon ki a program ebben az esetben a hibaüzenet helyett. A fenti példának egy lehetséges kiegészítése:
=XLOOKUP(E3;B3:B5;C3:C5;"nincs adat")
A [match_mode] paraméterrel megadhatjuk, hogy a keresett érték és a megtalált érték között milyen egyezést várunk. Ehhez a 0,-1,1,2,3 számokat használhatjuk, a program felajánlja őket automatikusan és a jelentésüket is megmagyarázza. Ennek a paraméternek az alapértelmezett értéke 0, amely a pontos egyezést jelenti. A további módokról a későbbiekben lesz szó.
A [search_mode] paraméterrel a keresési algoritmust adhatjuk meg pl. elölről hátra keressen. Ennek a paraméternek a megadására csak ritka esetben van szükség.
TIPP: Az opcionális paramétereket sorban kell megadnunk, tehát ha a [search_mode] paramétert meg szeretnénk adni, akkor az előtte lévő kettőt is meg kell adni.
Feladatok
A feladatok mindegyikében egy lekérdezést kell végezni. A lekérdezett érték a világoszölddel jelölt cellában szerepel, a képletet pedig a sárga cellába kell írni. Miután sikerült megtalálni a megfelelő képletet, érdemes tesztelni más bemeneteket is.
1. Hány éves a Kapitány?
A feladat megoldása a fenti példához hasonló. A lookup_value a világoszölddel jelölt cella, a lookup_array a neveket tartalmazó tartomány, a return_array pedig az, ami az életkorokat tartalmazza.
2. Osztályozás
Adott egy segédtáblázat ponthatárokkal és a hozzá tartozó jegyekkel. Az előző feladattal ellentétben a ponthatárok esetén a keresett pontszámot csak ritkán fogjuk megtalálni a segédtáblázatban. Ilyen esetben a [match_mode] opcionális paramétert is meg kell adnunk. Lehetséges értékek:
- 0 - Exact match: pontos egyezés
- -1 - Exact match or next smaller item: a két megtalált érték közül a kisebbet veszi
- 1 - Exact match or next larger item: a két megtalált érték közül a nagyobbat veszi
- 2 - Wildcard character match: ún. wildcard karaktereket is figyelembevesz (pl. ha szótöredékre keresnénk)
- 3 - Regex match: ún. regex karakterek segítségével kereshetünk mintázatokra (pl. három betű után következzen három számjegy)
A helyes érték kiválasztásához meg kell figyelnünk, hogy a táblázat milyen logika szerint készült el. Mivel 40 ponttól van 2-es és 60-tól már 3-as, ezért 55 ponthoz még a 2-es jegy tartozik. Ez azt jelenti, hogy a -1 értéket kell választanunk.
A [match_mode] megadásához az [if_not_found] paramétert is meg kell adnunk. Tetszőleges érték megadható.
3. Szótár
Az első feladattal megegyezik a megoldás, a különbség abban rejlik, hogy itt vízszintesek megadott tartományok.
4. Kő-papír-olló
A feladatban egy kő-papír-olló játékhoz tartozó kiértékelő táblázatban kell megkeresnünk a játék végeredményét a játékosok lépése alapján. Először egy XLOOKUP függvénnyel kiválasztjuk, hogy a táblázat melyik sorát kell figyelembe venni, amit az "Én" játékos lépése határoz meg: a lookup_array a játékos lehetséges választásait tartalmazza (oszlop), az return_array pedig az összes lehetséges eredményt (2D tartomány):
=XLOOKUP(H41;B43:B45;C43:E45)
Figyeljük meg, hogy a képlet végeredménye nem egy érték, hanem egy sor! Ebben a sorban kell megkeresnünk a végeredményt, az alapján, hogy az "Ellenfél" mit lépett. Tehát a fenti képlet lesz az új keresésünk visszatérési tartománya. A keresett érték az "Ellenfél lépése", a keresési tartomány pedig ellenfél lehetséges lépései:
=XLOOKUP(H42;C42:E42;XLOOKUP(H41;B43:B45;C43:E45))
5. ZH eredmények
Adottak a ponthatárok (a 2-es feladattal megegyeznek) és adottak a diákok pontszámai, a feladat a hozzájuk tartozó jegy meghatározása. Csak a sárga cellát szabad módosítani.
Amennyiben a többi cella is módosítható lenne, úgy a 2-es feladatban megadott képletet lehetne újra alkalmazni és lefelé másolni. Megadható azonban a függvénynek több keresési érték is, ebben az esetben tehát a lookup_array lehet a teljes pontszámokat tartalmazó tartomány: B52:B63. A többi tekintetben a feladat megegyezik a 2. feladattal.
TIPP: Bizonyos képletek nem csak azt a cellát töltik ki, amelyekben a képlet szerepel, hanem szomszédosakat is. Ezek az ún. dinamikus képletek. Onnan ismerhetjük fel, hogy egy cellában dinamikus képlet van, hogy a cellára kattintva egy téglalap megjelenik, amely jelzi, hogy meddig terjed ki a dinamikus adat.