Excel-tippek V.: Keresés a tartományokban
Megnézzük, hogyan válthatjuk ki kedvenc FKERES függvényünket, ha egy tömbnek nem az első oszlopában szeretnénk keresni.
Két komoly korlátja is van az "univerzális" FKERES függvénynek. Az egyik, hogy segítségével mindig csak egy tartomány vagy tömb első oszlopában tudunk keresni. A másik, hogy a visszaadott érték mindig ugyanannak a tartománynak vagy tömbnek ugyanazon vagy a tőle jobbra eső oszlopából származhat. Arról nem is beszélve, hogy ha esetleg be kell szúrnunk egy új oszlopot tartományunkba, akkor át kell írnunk az összes függvényt, mert az Excel az FKERES függvényekben nem korrigálja automatikusan az oszlopszámot.
De mit tehetünk akkor, ha a keresett értéktől balra lévő értékekre vagyunk kíváncsiak? Ha például a település nevét tudjuk (pl. Újfehértó), és az irányítószámot (4244) keressük, vagy ha azt szeretnénk tudni, hogy mi a neve annak a településnek, amiben pontosan százan élnek (Ispánk és Újszalonta) - anélkül, hogy korábbi táblázataink oszlopainak sorrendjét megváltoztat(hat)nánk? Jelenleg erre az Excelben nincs egyetlen függvény - viszont van kettő, amelyekkel kiválthatjuk az FKERES ezen hiányosságait. Azaz a probléma megoldásához ketté kell bontanunk a feladatot. Először egy adott tartományban vagy tömbben meg kell találunk a "keresési érték" sorát, majd ezt követően megnéznünk, hogy egy másik tartományban vagy tömbben milyen érték található ugyanabban a sorban.
Hol van?
Nézzük először az első feladatot: találjuk meg egy tartomány tetszőleges oszlopában a keresett számot, szöveget vagy dátumot. Példaként a Központi Statisztikai Hivatal Helységnévtárának letölthető adatait használjuk. Először is egy üres munkalap A oszlopába másoljuk át a helységek megnevezéseit, a B oszlopba pedig helyezzük melléjük a területnagyságot. Ezt követően két kis "segédtáblába" gyűjtsük ki az ország tíz legnagyobb és legkisebb területi értékét.
Ezt lusta módon megtehetnénk úgy is, hogy táblázatunkat a "Terület (hektár)" oszlop szerint rendezzük, majd kimásoljuk az első és az utolsó 10-10 sort. Azonban egyrészt így "lelőnénk a poént", másrészt sokkal elegánsabb megoldás a rendezgetés és másolás helyett a NAGY és a KICSI függvények használata. Ehhez a D2-től lefelé írjuk be a számokat 1-től 10-ig, majd egy sor kihagyással visszafelé 10-től 1-ig. Az első 1-es mellé, azaz az E2-es cellába írjuk be a következő képletet:
, és ezt húzzuk le egészen a 10-esig, vagyis E11-ig. Ezt ismételjük meg E13-tól, egy kicsi különbséggel, és ezúttal a NAGY helyett használjuk a KICSI függvényt: =KICSI($B$2:$B$3177;D13).
Miután ezzel megvagyunk, jöhet az első lépés: megtalálni az egyes értékek helyét a listában. Gyakorlatilag az Excelnek szó szerint feltesszük a kérdést, és a HOL.VAN nevű függvényt használjuk. A HOL.VAN függvény paraméterezése nagyon hasonló az FKERES-éhez, két apró eltéréssel. Először meg kell adnunk egy keresési értéket, ami esetünkben az E2-es cellába kikeresett legnagyobb területi érték lesz. Ezután jön a tartomány vagy tömb, amiben keresnünk kell, jelen esetben a területi adatokat tartalmazó $B$2:$B$3177 cellatartomány.
Viszont eltérően az FKERES-től ezt követően nem a visszaadandó érték oszlopa következik, hanem egyből az egyezés típusa, amit ezúttal pontos keresésre állítunk. Ez viszont megint csak eltér kissé az FKERES jól ismert HAMIS-ától, itt ugyanis 0-át kell megadnunk. Így az F2-be írandó képletünk a következőképpen néz ki: =HOL.VAN(E2;$B$2:$B$3177;0).