mikor FKERES és mikor INDEX
2020-11-11    Excel 2019
fogalmak
Mármint, milyen helyzetben használhatjuk az FKERES-, és milyenben az INDEX függvényt. A kérdés indokolt, mert mindkét függvény egy táblázat meghatározott oszlopának meghatározott sorában álló cella adatát adja eredményül. Mondhatjuk őket akár testvéreknek is. Sőt újabb függvényekkel kiegészítve már egy családról is beszélhetünk. Az FKERES-nek van egy ikertestvére a VKERES, meg egy kisöccse, a KERES. A báty az IN­DEX, aki elválaszthatatlan egy távoli rokontól, a HOL.VAN-tól.
    A tárgyalt függvényeket munkaterületük alapján nevezhetjük vektor- (KERES, HOL.VAN) és mátrix-függvényeknek (FKERES, VKERES, IN­DEX). Vektornak mondjuk, az egyoszlopos és az egysoros adattartományokat, amelyeket a program felülről lefelé, illetve balról jobbra ha­lad­va dolgoz fel. A mátrix pedig egy többoszlopos és többsoros adattartomány. A hangsúly az adattartományon van. Tehát se a vek­tor, se a mát­rix nem tartalmazza az oszlop- és sorcímkéket.
    Hogy a címben feltett kérdésre válaszolni tudjunk össze kell hasonlítanunk a függvények tulajdonságait. A VKERES-t külön nem vizs­gál­juk, hiszen ő csak „tájolásában” tér el az FKERES-től.
keres
A függvény nevének megfelelően, az első argumentumával meghatározott adatot keresi a második argumentumával meghatározott vek­tor­ban. Ez a keresési-vektor. Megjegyzi a találat cellájának sorszámát és a harmadik argumentumával deklarált vektorban, vagyis az ered­mény-vektorban, a találat sorszámával azonos sorszámú cellában álló adatot adja eredményül.
    A keresés módja a következő: melyik az az adat, amelyik azonos a keresett adattal, vagy ha nincs ilyen, akkor melyik az az adat, ame­lyik közvetlenül a keresett adat előtt állna, ha a keresett adat szerepelne a keresési vektorban. A keresési-vektorban az adatoknak nö­vek­vő sor­rendben kell állniuk. Ez a szabály egyaránt vonatkozik numerikus és karakteres adattípusokra is. A vektoroknak, ér­te­lem­sze­rű­en, azo­nos mé­retűnek kell lenniük, de tájolásuk eltérő is lehet.
szám keresése a KERES függvény vektoros alkalmazásával
A képen látható példában a hallgatók felkészültségét kellett értékelnünk pontszámuk alapján. A függvény argumentumai: a keresendő pontszám, az alsó határértékek és a pontszám-kategóriák. A függvényben szereplő vektorok celláit szürkével színeztem. A C2 cella kép­le­te: =KERES( B2; $E$2:$E$6 ; $A$9:$E$9 ).
    Vegyük példának Linda értékelését: százhuszonnyolc pontja van. A keresési vektorban (E2:E6) nincs ilyen szám. Ha képzeletben a vek­tor­ba helyezzük, akkor a százhuszonegy és a száznegyvenegy között állna. Közvetlenül előtte, a vektor második cellájában, a száz­hu­szon­egy áll. Akkor Lindára az eredmény-vektor második cellájának tartalma vonatkozik! Éppen hogy.
    Ebben a feladatban a függvényt egy szám keresésére, akarom mondani, egy szám kategóriájának keresésére használtuk. A ka­te­go­ri­zá­lást szöveg-keresésnél is megfigyelhetjük.
szöveg keresése a KERES függvény vektoros alkalmazásával
Az A4:A8 tartomány keresztneveket tartalmaz, ABC sorrendben. A munkalap első és második sorában a keresztnevekhez tartozó ve­ze­ték­ne­vek és monogramok állnak, a keresztnevekkel azonos sorrendben.
    A feladatunk az volt, hogy ki kellett írattatnunk a C5:C7 tartományban a megadott keresztnevekhez tartozó monogramokat. A D5 cella kép­le­te: =KERES( C5 ; $A$4:$A$8 ; $A$2:$E$2 ). A függvényben szereplő vektorok (keresztnevek, monogramok) celláit a képen szürkével szí­nez­tem. Linda második a keresési vektorban, ezért a második monogramot kapja (DK). A keresztnevek között nem szereplő Márkó, viszont Linda monogramját kapta (FL), mert ha képzeletben a keresztnevek közé helyeznénk, akkor Linda állna közvetlenül előtte. Hogy Bence csak egy #HIÁNYZIK-ot kapott? Hát ő meg első lenne a keresztnevek között, így aztán egyetlen név sem állna előtte.
    Aztán, hogy ezt a szöveg-kategorizálást milyen probléma megoldására használhatjuk? Nem tudom, de biztos van ilyen probléma…
fkeres
Az FKERES kétfunkciós függvény. Első funkciója a KERES-ével azonos: kategorizálás. Tehát az ő esetében is van keresett adat, ke­re­sé­si- és eredmény-tartomány. De az FKERES-nél ezek a tartományok nem vektorok, hanem egy mátrix oszlopai.
    Első argumentuma a keresett adat, a második a mátrix, amelynek első oszlopában a keresés folyik, a harmadik pedig egy sorszám; a mátrix hányadik oszlopa tartalmazza az eredményeket. A függvény keresési metódusa azonos a KERES-ével, ezért a keresési-osz­lop­ban az adatoknak emelkedő sorrendben kell állniuk. Nézzünk egy példát az FKERES első funkciójára.
szám keresése az FKERES függvénnyel
A feladatunk az volt, hogy a jobb oldali kis táblázatban, sebességük alapján megadjuk, az adott napra jellemző széljárás nevét. Se­gít­sé­günk­re volt a bal oldali táblázat, amely a széljárások tulajdonságait tartalmazza. Az FKERES-sel dolgoztunk. A függvény első ar­gu­men­tu­ma a keresendő sebesség. A második a mátrix, amelyet úgy határoztunk meg, hogy első oszlopa a sebesség-kategóriák alsó ha­tár­ér­té­ke­it tar­tal­ma­zó oszlop legyen. És a harmadik egy hármas szám, mert a mátrix harmadik oszlopa tartalmazza a sebesség-kategóriák el­ne­ve­zé­se­it. Az I4 cella képlete: =FKERES( H4 ; $C$2:$E$14 ; 3 ). De, ha a KERES-t használjuk, akkor is ezt az eredményt kapjuk: =KERES( H4 ; $C$2:$C$14 ; $E$2:$E$14 ). De nézzünk egy szöveg „kategorizálást” is!
szöveg keresése az FKERES függvénnyel
Az F oszlopba ki kellett íratni az E oszlopban megadott keresztnevekhez tartozó monogramokat. Az FKERES függvénnyel dolgoztunk. A F3 cella képlete: =FKERES( E3 ; $B$2:$C$6 ; 2 ). Mindkét ábrán a mátrix celláit szürkével színeztem. Ha a műveletet elvégezzük a KERES függ­vénnyel is, akkor ugyanezeket az eredményeket kapjuk.
    Tehát megállapíthatjuk, hogy a két függvény, ha kategorizálásról van szó, teljesen egyformán működik és eredményeik is azonosok, de a KERES használhatóságát nem korlátozzák munka-területei pozíciójára vonatkozó előírások.
    És most nézzük az FKERES második funkcióját: adatpár ismeretlen elemének megállapítása. Ezt a működést a függvény negyedik, a ka­te­go­ri­zá­lás­nál elhagyható, argumentumának HAMIS értékével „kapcsolhatjuk be”.
    Az adatpár ismert elemét, amelyet a függvény első argumentumával deklarálunk, a mátrix első oszlopában keresi a program. Tehát nem a kategóriáját, hanem őt magát! Ezért ebben a funkciójában használva a függvényt a keresés oszlopában nincs rendezettségi elő­í­rás. Ha a program megtalálja a keresett adatot, akkor az eredmény-oszlop, a találatéval azonos sorszámú cellájának tartalmát adja ered­mé­nyül.
adatpárok egy táblázatban
A fenti táblázat banki lekötéseket tartalmaz. Állapítsuk meg, mennyi a lekötés a 0011-95-8 számú számlán: =FKERES( 11958 ; B2:E9 ; 4 ) » 8134000. Ha nincs ilyen számú számla, akkor a #HIÁNYZIK hibaértéket, ha van, de nincs lekötése, vagyis az eredmény-oszlopban a cel­lá­ja üres, akkor nullát kapunk eredményül.
    Használható-e a KERES függvény is az adatpár ismeretlen elemének megállapítására? Korlátozottan. Mert láttuk, hogy működését a kategorizálásra optimalizálták.
hol.van
A HOL.VAN munkaterülete egy vektor, amelyet második argumentumával deklarálunk. Ebben a vektorban keresi a függvény az első ar­gu­men­tu­má­val meghatározott adatot, a harmadik argumentumával meghatározott módon. Három módszer közül választhatunk: alulról kö­ze­lí­tés, felülről közelítés és egyezőség. A szükséges módszert a harmadik argumentum egy, mínusz egy és nulla értékével kérhetjük. A kö­ze­lí­té­ses módszerek rendezést igényelnek.
a HOL.VAN függvény keresési módszerei
A képen felül a tízes kerestük a szürkével színezett vektorban, az alulról közelítés módszerével. A vektor számait előzőleg emelkedő sor­rend­be rendeztük. Az F2 cella képlete: =HOL.VAN( 10 ; A1:E1 ; 1 ). Ezt a működést a harmadik argumentum névkiegészítős bevitelekor a lista „1 - Kisebb” tételével kérhetjük.
    A középső példában ismét a tízest kerestük a szürke tartományban, de most a felülről közelítés módszerével. Ezért a megelőző ren­de­zés itt a csökkenő sorrendű volt. F6: =HOL.VAN( 10 ; A5:E5 ; -1 ). Ennek az üzemmódnak a kapcsolója a névkiegészítőben a „-1 - Na­gyobb”.
    Az alsó feladatban a tizenhetest kerestük és pontos egyezőséget kértünk a függvénytől. Rendezni nem kellett a vektort. F10: =HOL.VAN( 17 ; A9:E9 ; 0 ). A névkiegészítős bevitelkor ezt a metódust a „0 - Pontos egyezés” tétellel kérhetjük.
a HOL.VAN függvény keresési módszerei
A fenti táblázat a függvény keresési-módjait foglalja össze. Talán érdemes még megemlíteni, hogy a HOL.VAN-t általában beágyazottan használják. Önállóan nagyon ritkán fordul elő.
index
Az INDEX függvény az első argumentumával meghatározott tartomány, második argumentumával megadott sorszámú sorában, és a har­ma­dik argumentumával meghatározott sorszámú oszlopában álló cella értékét vagy hivatkozását adja eredményül. Az eredmény tí­pu­sát a függvény képlet-környezete határozza meg. Ha a harmadik argumentum hiányzik, akkor a függvény az oszlop sorszámát egy­nek (1) veszi. Az első argumentummal, zárójelek között, pontosvesszővel elválasztva, több tartományt is megadhatunk. Ebben az eset­ben a függvény a negyedik argumentumával meghatározott sorszámú tartományból adja az adatot illetve a cella hivatkozását. A sor­szá­mo­zás az első ar­gu­men­tum felsorolásának sorrendjét követi. Ha a negyedik argumentum hiányzik, akkor a függvény a tartomány sor­szá­mát egynek (1) veszi.
példa a HA függvény tömbös kiértékelésére
Van három tartományunk (A1:C3, E1:G3 és I1:K3) és van három cellánk (B7, F7 és J7). Ez a hat objektum alkotja az INDEX függvény ar­gu­men­tum-listáját. Az első képletben a függvény a cella tartalmát adja vissza, a másodikban a hivatkozását: E3. És ennek a cel­la­hi­vat­ko­zás­nak a sor-számát írja ki a SOR függvény.
    Persze, amit a képen látunk az csak egy modell, az életben másként van. Az INDEX-et általában ugyanarra használjuk, mint amire az FKERES-t, a második funkciójában: adatpár ismeretlen elemének megállapítására. És ugyan úgy, mint az FKERES-nél az adatpár ismert elemét általában egy vektorban keressük, csak nem magával az INDEX-szel, hanem egy beágyazott HOL.VAN függvénnyel.
példa az INDEX függvény alkalmazására
A képen látható táblázat (A1:B10) neveket és születési dátumokat tartalmaz. Írassuk ki az első három legfiatalabb ember nevét az E4:E6 tartományban. Ez volt a feladtunk.
    Az A4 cella képletében az INDEX függvényt használtuk. A függvény első argumentuma a neveket tartalmazó vektor deklarációja. A má­so­dik egy kifejezés, amelyben a HOL.VAN megállapítja a NAGY függvény által szolgáltatott legkésőbbi dátum cellájának sorszámát a szü­le­té­si dátumok vektorában. Azután a név-vektor a találat cellájának sorszámával azonos sorszámú cellájának karakterláncát adja ered­mé­nyül.
válasz
Az FKERES és az INDEX tehát csak az adatpár ismeretlen elemének megállapításában alternatívája egymásnak. Csak az INDEX-et hasz­nál­hat­juk , ha az adatpár ismeretlen elemét tartalmazó oszlop a keresési-oszloptól balra áll és akkor, ha az adatpár „ismert elemének” nem az értékét, hanem valamely statisztikai tulajdonságát ismerjük. Például: ő a legnagyobb a rangsorban, ő a harmadik legkorábbi a dátumok kö­zött… A kategorizálásban, ahogy láttuk a KERES a király.
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com