Az adatok keresése nagyon gyakori feladat a táblázatokkal történő munka során. Az FKERES függvényt már ismerjük, segítségével könnyen és gyorsan vethetjük össze táblázatainkat. De bármennyire is belopta magát a szívünkbe, azért vannak korlátai. Emlékezzünk csak vissza, melyek is azok a korlátok?

A Tábla mezőhöz megadott tartomány oszlopainak a sorrendje nem lehet tetszőleges. Az kell legyen az első, amiben keresünk, és az attól jobbra eső oszlopokból jeleníthetjük meg az eredményt. Természetesen ha a táblázat szerkezete nem megfelelő, azért általában át tudjuk alakítani úgy, hogy jó legyen.

De azért vannak más lehetőségek is, például az INDEX és a HOL.VAN függvények. Ezt a két függvényt általában egymásba ágyazva szoktuk használni, és így együtt tökéletes keresőként működnek. Nézzük meg, hogyan!

Nyissuk ki a Hitelek kezelése munkafüzetet!

A táblázat már ismerős lehet, bizonyos oszlopaiba már számoltunk. Most a munkafüzet Ügyféllista és Fontos ügyfelek munkalapjára lesz szükségünk.

A feladatunk az lesz, hogy a Fontos ügyfelek munkalapon szereplő táblázat üres oszlopait feltöltsük a megfelelő adatokkal. Olyan képleteket kell gyártanunk, amelyek a megadott azonosító alapján kikeresik a megfelelő nevet, a nettó bért és az ügylet típusát.

Kezdjük a névvel! Tehát vesszük az első azonosítót, ezt keressük a másik táblázat C oszlopában. Eredményül a hozzá tartozó nevet várjuk a B oszlopból. Az FKERES függvényt most azért nem használhatjuk a meg­ol­dás­hoz, mert a C oszlopban keresünk és az eredményt tar­tal­ma­zó oszlop tőle balra esik.

Próbáljuk megoldani a feladatot az INDEX-HOL.VAN függvénypárossal! Nézzük meg először a függvények működését.

Az INDEX függvénynek meg kell adnunk egy összefüggő cellatartományt, ami tartalmazza azt az oszlopot, amiből az eredményt várjuk. Ezen kívül megadunk egy sor- és egy oszlopszámot, ami konkrétan meghatározza a keresett cellát.

Jelen esetben a 4-es azonosítóhoz tartozó név a 4. sorban van a kijelölt tartományon belül, és az oszlop, amiből az eredményt várjuk ugyanitt az 1. oszlop.

Kereséskor azt, hogy melyik oszlopból várjuk az eredményt, azt tudjuk, de hogy melyik sorban van a keresett érték, azt nem. Magát a keresést a HOL.VAN függvény végzi el.

A függvénynek meg kell adnunk egyrészt a keresett értéket, azt az oszlopot, amiben ezt keressük, illetve meg kell határoznunk a keresés módját, ami lehet pontos keresés vagy közelítő. Ebben az esetben természetesen pontosan keresünk.

Kezdjünk hozzá a feladat megoldásához, nézzük először a neveket!

Első lépésben szúrjuk be az INDEX függvényt a Fontos ügyfelek munkalap B2-es cellájába!

INDEX függvényből kétféle is van, nekünk az első típusra van szükségünk.

Töltsük ki a függvényargumentumokat! A Tömb az a cellatartomány lesz, ahonnan az eredményt várjuk. Kijelölhetnénk az Ügyféllista munkalap teljes táblázatát is, de felesleges. Elég az az egy oszlop, ami a neveket tartalmazza: Ügyféllista!B2:B26. Ezzel összefüggésben tudjuk az Oszlop_Számot is, ezzel határozzuk meg azt, hogy a Tömbhöz kijelölt cellatartománynak hányadik oszlopából várjuk az eredményt. Mivel egyetlen egy oszlopról van szó, ide vagy 1-et írunk, vagy egyszerűen üresen hagyjuk.

Folytassuk a Sor_szám mezővel! Itt kéne meg­mon­da­nunk, hogy a Tömb hányadik sorában van a szükséges értéket tartalmazó cella. De ezt mi még nem tudjuk.

Ide kell beágyaznunk a HOL.VAN függvényt, ami megkeresi a szükséges értéket.

Amit keresünk az az első azonosító a Fontos ügyfelek munkalapról: A2. Ezt az azonosítót a másik táblázat C oszlopában keressük, vagyis a Tábla mezőhöz ezt jelöljük ki: Ügyféllista!C2:C26. És mivel pontos keresést végzünk, az Egyezés_típus 0.

Már majdnem készen vagyunk, már csak a dollárjeleket kell beállítanunk, hiszen a képletet le fogjuk másolni. Ha jól belegondolunk, igazából csak egyetlen egy cellahivatkozás csúszhat, méghozzá a HOL.VAN függvénynek megadott Keresési_érték. Az összes többi cellahivatkozást rögzítsük le! (INDEX függvényben a Tömb, HOL.VAN füg­gvény­ben a Tábla)

Ha megvagyunk, kattintsunk a Kész gombra és másoljuk le a képletet a 6. sorig! Láthatjuk, hogy a képlet minden egyes azonosítóhoz tartozó nevet megjelenített.

Folytassuk a többi adat megjelenítésével, jöhet a nettó bér! A nettó bér az eredeti táblázatban az azonosítóktól jobbra esik, így az FKERES függvényt is használhatnánk, de nem ezt fogjuk tenni. Gyakorlásképpen most is az INDEX-HOL.VAN függvénypárossal oldjuk meg a feladatot.

Álljunk a C2-es cellába és az INDEX függvénnyel kezdjük. A két típus közül válasszuk az elsőt!

A Tömb most a béreket tartalmazó D oszlop lesz, innen várjuk az eredményt. Már most gondoljunk arra is, hogy a képletet majd le fogjuk másolni, tehát ezeket a cellahivatkozásokat rögzítsük le: Ügyféllista!$D$2:$D$26. Az Oszlop_szám 1, de akár üresen is hagyhatjuk ezt a mezőt, hiszen a Tömbhöz egy oszlopot jelöltünk ki.

Következhet a HOL.VAN függvény beágyazása a Sor_szám mezőbe.

A Keresési_érték most is az első azonosító, a Tábla, ahol ezt keressük, az eredeti táblázat C oszlopa, és mivel pontos keresést végzünk az Egyezés_típus 0. Ne felejtsük el a Tábla mezőhöz kijelölt tartományt rögzíteni!

Ha minden argumentumot megadtunk, kattintsunk a Kész gombra és másoljuk le a képletet!

Utolsó lépésben jelenítsük meg az ügylet típusát is! Ezt is ugyanúgy oldjuk meg, mint az előző feladatokat. Szúrjuk be az INDEX függvényt a D2-es cellába!

Mivel a megfelelő ügylet típust szeretnénk megjeleníteni, a Tömb az eredeti táblázat F oszlopa, rögzítve: Ügyféllista!$F$2:$F$26. Az Oszlop_Szám 1 vagy elhagyjuk.

Ezután ágyazzuk be a HOL.VAN függvényt a Sor_szám mezőbe! A Keresési_érték még mindig az azonosító, a Tábla az eredeti táblázat C oszlopa, ezt ne felejtsük el lerögzíteni, és a Tar­to­mány­ban_keres mező értéke 0, mivel pontosan ke­re­sünk.

Ha mindent megadtunk, kattintsunk a Kész gombra és másoljuk le a képletet!

A HOL.VAN függvény is alkalmas olyan jellegű ka­te­go­ri­zá­lás­ra, mint az FKERES. Sőt a HOL.VAN függ­vény két irányból is tudja közelíteni a keresett ér­té­ket, ennek megfelelően -1-et vagy 1-et kell írnunk az Egyezés_típus mezőbe. Figyeljünk arra, hogy ha a függvény felülről közelít (-1), akkor az in­ter­val­lu­mok felső határára van szükségünk csökkenő sorrendben.

Az adatok keresését általában függőleges irányban végezzük, de néha előfordulhat, hogy vízszintes keresésre van szükség. A vízszintes irányú keresésre használhatjuk a VKERES függvényt, ami pont úgy működik, mint az FKERES, de a korlátai is ugyanazok.

Az INDEX és HOL.VAN függvényeket eddig csak függőleges keresésre használtuk, de alkalmasak arra is, hogy vízszintesen keressünk. A keresés irányát tulajdonképpen az határozza meg, hogy a HOL.VAN függvény Tábla mezőjéhez egy oszlopot vagy egy sort jelölünk ki.

Nyissuk ki a Valuták munkafüzetet!

Két munkalapunk van. Az elsőn különböző valuták árfolyamát látjuk 6-féle bank esetén. A másik munkalapon ugyanezeket a valutákat látjuk, ugyanabban a sorrendben.

A feladatunk a következő. A második munkalap B oszlopába először is meg kell jelenítenünk az egyes valuták esetén a legkisebb értéket. Ez nem lesz túl nehéz, egy egyszerű MIN függvénnyel meg tudjuk oldani. A C oszlopba pedig azt szeretnénk látni, hogy az a bizonyos legkisebb érték a B oszlopban pon­to­san melyik bank ajánlata. Ez már nehezebb ügy. Tu­laj­don­kép­pen meg kell keresnünk, hogy az az adott érték melyik bankhoz tartozik az első munkalapon.

Kezdjük a legkisebb érték kiszámításával. A Legkedvezőbb munkalap B2-es cellájába szúrjunk be egy MIN függvényt és adjuk meg a Valuták bankonként munkalap B2:G2 tartományát.

Másoljuk le a képletet a táblázat aljáig, így megkaptuk minden egyes valuta esetén a legkisebb értéket.

Mellé a C oszlopba jelenítsük meg, hogy a kapott értékek melyik bankhoz tartoznak. Most jön az INDEX és a HOL.VAN függvénypáros.

A HOL.VAN függvény kikeresi, hogy az adott érték melyik oszlopban van, az INDEX pedig visszaadja az adott oszlop első sorában szereplő feliratot vagyis a megfelelő bank nevét. Kezdjünk hozzá a feladat megoldásához! A C2-es cellába szúrjuk be az INDEX függvény első típusát. A Tömbhöz kijelöljük azokat a cellákat, ahonnan az e­red­ményt várjuk, ez az első munkalap B1:G1 tartománya, ami a bankok nevét tartalmazza. Mivel vízszintes irányú ke­re­sést végzünk a Sor-, illetve Oszlop_szám mezők közül most a Sor_számot tudjuk, hiszen a tömbhöz kijelölt tar­to­mány első sorából várjuk az eredményt. A B1:G1 tartományt ne felejtsük el rögzíteni, ez másoláskor nem csúszhat el, az eredményt mindig innen várjuk.

Következhet a HOL.VAN függvény beágyazása. Kattintsunk az Oszlop_szám mezőbe és szúrjuk be a függvényt!

A Keresési_érték a B2-es cellában szereplő érték lesz, vagyis az adott valuta legkisebb értéke, amit a MIN függvénnyel számoltunk ki. Ezt rögzítenünk nem kell, hiszen másoláskor mindig a következő értéket kell figyelembe vennie a függvénynek.

A Tábla mezőhöz az eredeti táblázat első sorát kell kijelölnünk:'Valuták bankonként'!B2:G2, mivel a keresett értéket a függvénynek mindig az adott valuta adatai között kell keresnie. Ezt sem rögzítjük, ennek is csúsznia kell másoláskor.

Az utolsó mező értéke 0, hiszen pontos keresést végzünk.

Ha készen vagyunk, másoljuk le a képletet a táblázat aljáig!

Térjünk vissza egy kicsit a hiteleket tartalmazó táblázathoz! A feladatunk ugyanaz lenne, mint korábban, vagyis a Fontos ügyfelek munkalapon jelenítsük meg az azonosítókhoz tartozó többi adatot!

De most próbáljuk meg úgy kialakítani a képletet, hogy ne csak lefelé tudjuk másolni, hanem jobbra is. Tehát nem csak az azonosítókat kell megkeresnünk, hanem az adott oszlop feliratát is.

A feladat megoldásához most is az INDEX és a HOL.VAN függvényeket használjuk. A különbség annyi lesz, hogy az oszlopszámot is egy HOL.VAN függvény fogja meghatározni.

Kezdjünk hozzá a feladat megoldásához, szúrjuk be az INDEX függvényt! A Tömb most legyen az egész táblázat: A:K. Ez másolás közben nem csúszhat, rögzítsük!

A Sor_szám mezőbe ágyazzuk be a HOL.VAN függvényt úgy, ahogy korábban is. Vigyázzunk a $ jelekre, az azonosítót tartalmazó cella oszlopazonosítóját rögzítenünk kell, illetve az eredeti táblázat C oszlopát is. Ez a HOL.VAN függvény adja vissza, hogy az adott azonosító melyik sorban van.

A következő lépésben az Oszlop_számhoz is ágyazzunk be egy HOL.VAN függvényt. Ez adja vissza, hogy az adott oszlop felirata az A:K oszlopokon belül hányadik oszlopban szerepel. A Keresési_érték a B1, itt a sorazonosítót rögzítjük, a Tábla az eredeti táblázat első sora, ahol a feliratokat keressük. Ezt rögzítsük. Az Egyezés_típus itt is 0.

Az INDEX függvénynek van egy olyan változata is, melynek segítségével nem csak egy táblázatban kereshetünk, hanem több egymástól független tartományban is. Nyissuk ki az Almák eladási mennyisége nevű munkafüzetet!

A munkalapon 4 különböző táblázatot láthatunk, amelyek 4-féle alma eladási mennyiségeit tartalmazzák városonként 3 évre.

A B19-es cellába jelenítsük meg, hogy a megadott almából mennyit adtak el a kiválasztott városban és évben. Tehát először is meg kell keresnünk, hogy az adott fajtájú alma melyik táblázat a 4 közül, és annak melyik sora és oszlopa tartalmazza a várt értéket. A feladat megoldásához az INDEX függvény másik változatát fogjuk használni, ahol nem csak sor- és oszlopszám adható meg, hanem az is, hogy a végeredményt melyik táblázatból várjuk (Terület_szám).

Kezdjünk hozzá a feladat megoldásához, szúrjuk be az INDEX függvényt a B19-es cellába!

Elsőként a Hivatkozás mezőbe meg kell adnunk a négy táblázatot. Ezek egymástól független tar­to­má­nyok, ki­je­lö­lé­skor használjuk a Ctrl billentyűt! Ha a ki­je­lö­lés­sel megvagyunk, akkor ezt a részt tegyük zá­ró­je­lek közé, különben hibaüzenetet kapunk vég­e­red­mény­ként.

Ehhez kapcsolódóan kell megadnunk a Te­rü­let_szá­mot, amivel azt határozzuk meg, hogy a kijelölt tartományok közül melyikben kell keresnünk. Ide egy egyszerű sorszámot vár az Excel. Az almafajták táblázatához tartozó sorszámokat az M1:N4 tar­to­mány­ban tároljuk. A B16-os cellában szereplő al­ma­faj­ta alapján egy FKERES függvénnyel ki­ke­res­sük, hogy az melyik sorszámú táblázat.

Tehát a Terület_szám mezőbe ágyazzuk be az FKERES függvényt. A Keresési_érték a B16-os cellában szereplő almafajta, ahol keresünk és ahonnan várjuk az eredményt, az az almafajtákat és a sorszámokat tartalmazó táblázat: M1:N4 és ennek a 2. oszlopából várjuk az eredményt.

Lépjünk vissza az INDEX függvény paneljére és foglalkozzunk a Sor_szám mezővel. Ide a megadott táblázatból annak a sornak a sorszáma kell, amelyben a B17-es cellában szereplő város van. Hogy ez melyik sor lesz, azt egy HOL.VAN függvény segítségével számoljuk ki. Tehát ágyazzuk be a Sor_szám mezőbe!

A Keresési_érték a B17-es cella lesz, ebben van a keresett város neve. A Táblához kerül az az oszlop, amiben a megadott várost keressük. Mivel ez a tartomány mind a négy táblázat esetén egyforma, mindegy melyiket adjuk meg, legyen az első. Az Egyezés_típus  pedig 0, hiszen pontos keresést végzünk.

Ezután lépjünk vissza az INDEX függvény paneljére és nézzük az Oszlop_számot!

Most már csak azt kell megadnunk, hogy a keresett érték melyik oszlopban szerepel. Ez attól függ, hogy a B18-as cellába melyik évet adtuk meg. Ezt szintén egy HOL.VAN függvénnyel oldjuk meg. A különbség az előzőhöz képest csak annyi, hogy most vízszintes irányú keresést végzünk, vagyis megnézzük, hogy az adott évszám az első sorban hányadik oszlopban szerepel.

Ágyazzuk be a függvényt az Oszlop_szám mezőbe!

A Keresési_érték a B18-as cella, amiben az adott évszám szerepel. A Tábla az évszámokat tartalmazó sor, ez szintén egyforma mind a négy táblázat esetén, jelöljük ki mondjuk az elsőt! Az Egyezés_típus itt is 0.

Lépjünk vissza az INDEX függvény paneljére, itt láthatjuk a kereső függvények által visszaadott sorszámokat. Kattintsunk a Kész gombra, megkaptuk a keresett értéket.

Figyeljünk oda a Hivatkozás mezőbe megadott tartományok sorrendjére. A megadás sorrendje határozza meg az egyes területek sorszámát.

A Hivatkozás mezőbe megadott tartományok nem szerepelhetnek külön munkalapokon.