az FKERES függvény bemutatása
2019-02-11    Excel 2019
Mi ez az F betű a függvény nevében, Tanító bácsi? A függőleges szó rövidítése, Pistike! Van neki egy testvére is, a VKERES. V, mint víz­szin­tes. Arról nem is beszélve, hogy mindkettőjüknek rokona a KERES. Az egész család keres, de mit, Tanító bácsi? Kapcsolódó ada­tot, Pistike! Megmagyarázom.
    Van egy adatunk. Nevezzük viszonyítási alapnak! Azután van egy tartományunk, amelynek meghatározott oszlopában áll a vi­szo­nyí­tá­si alaphoz kapcsolódó adat. Ezt az adatot tudjuk az FKERES függvénnyel kiíratni. A kapcsolódó adat sorát a függvény, a tartomány első osz­lo­pá­nak vizsgálatával, határozza meg. Felülről lefelé haladva egyesével összehasonlítja az oszlopban álló adatokat a vi­szo­nyí­tá­si alappal.
    Az összehasonlításhoz a függvény a kisebb egyenlő (<=) vagy az egyenlő (=) relációt tudja alkalmazni. Alapból a kisebb egyenlő re­lá­ci­ót kapjuk, az egyenlőt kérni kell. Ebből következően, a szokásos működés szám adattípusú viszonyítási alapot feltételez. Több ta­lá­lat e­se­tén a legnagyobb szám (<=), illetve az első azonos adat (=) sora lesz a meghatározó az eredmény szempontjából. És még egy fon­tos meg­kö­tés: a kisebb egyenlő reláció alkalmazása esetén, a tartomány első oszlopában a számoknak növekvő sorrendben kell állniuk.
    És most nézzük mindezt a gyakorlatban! Először a szokásos működéssel ismerkedjünk meg! A hallgatók dolgozatot írtak, a javítás már megtörtént. Az értékelés a mi feladatunk, amelynek a „ponthatárok” nevű adatbázis-táblázat alapján kell készülnie.
az FKERES függvény, negyedik argumentum nélkül
A függvény első argumentuma a viszonyítási alap. A mi esetünkben ez Ambrus Regina pontszáma: B2. A függvény második ar­gu­men­tu­ma a tartomány. Nálunk a ponthatárok nevű adatbázis táblázat: FKERES( B2 ; ponthatárok ; …). Ha nem hoztunk létre adatbázis-táb­lá­za­tot, akkor: FKERES( B2 ; $E$2:$H$6 ; … ). Abszolút hivatkozás és az oszlop-neveket nem adjuk meg, csak a színezett területet! A függ­vény harmadik argumentuma az eredmény-oszlop sorszáma a tartományban, balról-jobbra haladva, az első szám az egyes (1). Tehát a C2-es cella képlete: FKERES( B2 ; ponthatárok ; 4 ).
    A függvénynek először azt a legnagyobb számot kell meghatároznia az alsó határ oszlopban, amelyik kisebb vagy egyenlő 240-nél. Ez a szám a 232. A kapcsolódó adatot tehát a 232-t tartalmazó sor negyedik cellája tartalmazza. Kedves Regina! Sajnos ez csak egy kö­ze­pes! Az FKERES a #HIÁNYZIK hibaértéket adja eredményül, ha a viszonyítási alap szöveg adattípusú vagy ha az első oszlop szá­mai nem nö­vek­vő sorrendben követik egymás.
    És most nézzünk egy példát a módosított működésre, másszóval az egyenlő reláció használatára. Van egy ügyfél-listánk és egy „adó­sok” nevű adatbázis-táblázatunk. A tartozásokat át akarjuk vezetni az ügyfél-listára. A kép hátterében az utóbbit, előterében az adósok adat­bá­zis-táblázatot látjuk.
az FKERES függvény, negyedik argumentumnal
A függvény működését a negyedik, elhagyható, argumentummal módosíthatjuk: az egyenlőség reláció alkalmazását a HAMIS logikai ér­ték­kel írhatjuk elő. A viszonyítási alap az ügyfelek kódja, a tartomány az adósok adatbázis-táblázat és az eredmény-oszlop sorszáma a 3-as lesz. A C2-as cella képlete, akkor így néz ki: FKERES( A2 ; adósok ; 3 ; HAMIS ).
    Jaj-jaj, Tanító bácsi! Tessék segíteni! Rengeteg #HIÁNYZIK-ot kaptam! Semmi baj Pistike! A cellában álló hibaérték most azt jelenti, hogy az adott ügyfél nem szerepel az adós-listán. Nagyon megijedtem, Tanító bácsi! De így meg olyan béna ez a táblázat! A két­ar­gu­men­tu­mos HAHIBA függvénnyel orvosolhatjuk a problémát, Pistike! A függvény első argumentumával a végrehajtandó műveletet dek­la­rál­juk, a második argumentummal pedig alternatív műveletet írhatunk elő, arra az esetre, ha az első argumentum kiértékelése hibát ered­mé­nyez­ne. A C2-as cella végleges képlete így alakul: HAHIBA( FKERES( A2 ; adósok ; 3 ; HAMIS ) ; ”” ). Ezek szerint, nulla hosszú­sá­gú szöveget ad­tunk meg eredménynek, ha az FKERES hibát eredményezne.
FKERES függvény a HAHIBA függvénnyel kiegészítve
A képen balra az első-, jobbra a második, javított kiadást látjuk. A negyedik argumentum tehát elhagyható, ha a függvényt a szokásos re­lá­ci­ó­val kívánjuk használni, de ha képlettel határozzuk meg az alkalmazandó relációt, akkor a szokásos működés értéke IGAZ. Az alábbi táb­lá­zat­ban összefoglaltam a függvény legfontosabb tulajdonságait.
az FKERES függvény szokásos és módosított működése
Valami szöget ütött a fejembe, Tanító bácsi! Hogyan lehet az FKERES-t vízszintesen másolni? Hiszen az eredmény-oszlop deklarációja egy szám. Látom, figyelsz Pistike! Megmagyarázom! Fogalmazzuk meg a problémát egy konkrét feladaton! Készlet-értéket akarunk szá­mí­ta­ni, egy termék-lista és egy készlet-lista („készlet” adatbázis-táblázat, készlet-lista lap) segítségével. Így néznek ki a táblázatok.
az FKERES függvény vízszintes másolása
Az előtérben álló táblázatban dolgozunk. A C2-es cellában az első termék budaörsi készletének értékét kell kiszámítani: FKERES( $A2 ; készlet ; 4 ; HAMIS ) * $B2. Gondolva a képlet vízszintes másolására a vonalkód és az ár oszlopát rögzítettem. Igen ám, de mi lesz a függ­vény harmadik argumentumával: Budaörs esetében 4 volt, de Soroksárnál már 5, Fehérvárnál már 6 és így tovább. Ezt a problémát csak képlettel tudjuk megoldani.
    Az argumentum nélküli OSZLOP függvény a képletet tartalmazó oszlop számát adja eredményül. A számozás az A oszloptól indul, amely­nek száma az egyes. A budaörsi készlet-értéket kiszámoló képlet a C, azaz a 3-as oszlopban áll, a hozzá tartozó darabszám a kész­let-lista nevű munkalap 4. oszlopában (D) található. Soroksár esetén a két oszlop-szám 4-es és 5-ös, Fehérvár 5-ös és 6-os… Te­hát a kész­let és készlet-érték oszlopai között mindig egy a különbség. A C2-es cella végleges képlete, akkor így alakul: FKERES( $A2 ; készlet ; OSZLOP() + 1 ; HAMIS ).
    A fenti problémát még az oszlopok tényleges számozásával is megoldhatjuk. Beszúrunk egy sort az adatbázis-táblázat fölé, meg­szá­moz­zuk az oszlopokat és az FKERES harmadik argumentumában ezeknek az oszlop-számoknak a celláira hivatkozunk majd. De vi­gyáz­zunk, mert a függőleges másolás miatt, a hivatkozás sorát rögzíteni kell. A képen felül a beszúrt oszlop-számok sorát, alul pedig a C2-es cella új képletét látjuk.
az FKERES függvény vízszintes másolása
Létezik egy másik, nagyon gyakori FKERES-probléma, amelynek megoldását ismernünk kell! Lássunk erre is egy példát! A mun­ka­tár­sak órabérét három tulajdonság értékei alapján kell meghatároznunk: kategória (első, második, harmadik), státusz (A, B, C) és szint (1, 2, 3). Munkánkat egy segéd-táblázat segíti, amely a három tulajdonság értékeinek összes variációját tartalmazza, a hozzájuk tartozó óra­bé­rek­kel együtt. Lássuk a két táblázatot!
az FKERES használata több tulajdonság keresésekor
Hát igen! Nehéz elképzelni, hogy itt az FKERES-sel boldogulni tudunk! A probléma az, hogy ez a függvény csak egyetlen adatot tud ke­res­ni és azt is csak egyetlen oszlopban. Az első akadályt egy összefűzéssel el tudjuk hárítani: C2 & D2 & E2, de hogy tudunk a segéd-táblában három oszlopból egyet csinálni? Hát azt is összefűzéssel! Beszúrunk egy oszlopot az órabér elé, és a három oszlop értékeit összefűzzük: H2 & I2 & J2.
az FKERES használata több tulajdonság keresésekor
Ahogy a képen látjuk, a mind és az órabér oszlopokkal adatbázis-táblázatot hoztam létre, csak a szokás kedvéért. Neve „pontszámok”. A Füleki Piroska órabérét kiszámító képlet tehát így alakul: FKERES( C2 & D2 & E2 ; órabérek ; 2 ; HAMIS ). 3 800 Ft. Na kedves Pi­ros­ka, meg van elégedve?
    Adieu kedves Gyerekek! Mi az, hogy ágyő, Tanító bácsi! Hát a VKERES hol marad? Azt feladom házi feladatnak, Pistike! Ami az egyik­nél sor a másiknál oszlop, ami az egyiknél oszlop a másiknál sor…
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com