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 INDEX, 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, INDEX). Vektornak mondjuk, az egyoszlopos és az egysoros adattartományokat, amelyeket a program felülről lefelé, illetve balról jobbra haladva 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 vektor, se a mátrix 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 vizsgáljuk, 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 vektorban. 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 eredmé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, amelyik 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övekvő sorrendben kell állniuk. Ez a szabály egyaránt vonatkozik numerikus és karakteres adattípusokra is. A vektoroknak, értelemszerűen, azonos méretűnek kell lenniük, de tájolásuk eltérő is lehet.
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éplete: =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 vektorba 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ázhuszonegy á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 kategorizálást szöveg-keresésnél is megfigyelhetjük.
Az A4:A8 tartomány keresztneveket tartalmaz, ABC sorrendben. A munkalap első és második sorában a keresztnevekhez tartozó vezetéknevek é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éplete: =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íneztem. 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, keresé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-oszlopban az adatoknak emelkedő sorrendben kell állniuk. Nézzünk egy példát az FKERES első funkciójára.
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. Segítségünkre 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ő argumentuma 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ó határértékeit tartalmazó oszlop legyen. És a harmadik egy hármas szám, mert a mátrix harmadik oszlopa tartalmazza a sebesség-kategóriák elnevezéseit. 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!
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üggvé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 kategorizálásná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 eredményül.
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 cellá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ő argumentumával meghatározott adatot, a harmadik argumentumával meghatározott módon. Három módszer közül választhatunk: alulról közelí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özelítéses módszerek rendezést igényelnek.
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ő sorrendbe 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ő rendezé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 - Nagyobb”.
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 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 harmadik 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ípusá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 egynek (1) veszi. Az első argumentummal, zárójelek között, pontosvesszővel elválasztva, több tartományt is megadhatunk. Ebben az esetben 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 sorszámozás az első argumentum felsorolásának sorrendjét követi. Ha a negyedik argumentum hiányzik, akkor a függvény a tartomány sorszámát egynek (1) veszi.
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 argumentum-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 cellahivatkozásnak 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.
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ásodik 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ületé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 eredmé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 használhatjuk , 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.
margitfalvi.arpad@gmail.com
