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.
Ha most megnyomjuk az [Enter] vagy a [Tab] billentyűt, akkor elsőre az 1067-et kapjuk, ami teljesen korrekt megoldás: a HOL.VAN függvény megkereste, hol van a B oszlopban, pontosabban a $B$2:$B$3177 tartományban a 48 798-es érték, és miután ez a szám a B1068-as cellában található, valóban az a megadott tartomány 1067. sora.
Mi van a sorban?
Miután már tudjuk, hogy a keresett érték a településnagyságok tartományának hányadik sorában található, nincs más dolgunk, mint kivenni a településnevek tartományának ugyanannyiadik sorának elemét. Pontosan erre a célra szolgál az INDEX függvény, aminek két különböző alakja van, a hivatkozásos és a tömbös - nekünk most ez utóbbira lesz szükségünk.
Az INDEX függvény tömbös alakja ugyanis egy tartomány vagy tömb azon elemének értékét adja vissza, amelyet a sorszám és oszlopszám mint index meghatároz. Így nincs más dolgunk, mint megadnunk a településnevek tartományát, valamint az imént kikeresett sorszámot (ha tartományunknak csak egy oszlopa van, akkor az oszlopszámot el is hagyhatjuk):
, és már látjuk is, hogy melyik a legnagyobb területű magyarországi település (Hódmezővásárhely). Nagyon figyeljünk oda arra, hogy az INDEX függvénynél megadott tartomány mindig ugyanabban a sorban kezdődjön, mint a HOL.VAN-nál használt tartomány - hacsak nincs valami speciális oka annak, hogy szándékosan más sorból akarjuk kivenni az adatokat. Természetesen a három függvényt egymásba is ágyazhatjuk, a köztes értékeket nem kell "munkacellákban" tárolnunk.
Vigyázat, csalunk!
Ha képleteinket lehúzzuk egészen a 22. sorig - persze odafigyelve arra, hogy a 13. sortól a NAGY helyett a KICSI-t kell használnunk -, máris láthatjuk Magyarország 10-10 legnagyobb és legkisebb területű településének neveit. Ám ha most a területi adatokat kicseréljük a lakónépességre, akkor nem kis bosszúságunkra a legkisebb településeknél több ismétlődést is látunk. Ennek az az oka, hogy a KICSI függvény, miután növekvő sorrendbe állította a lakónépesség számokat, a 14-et megtalálja a lista 3. és 4., a 17-et az 5. és 6., a 26-ot pedig a 9. és a 10., sőt, a 11. és a 12. helyen, a HOL.VAN azonban mindig csak az első előfordulásokig jut el.
Ezt a problémát a legegyszerűbben egy kis csalással tudjuk megoldani: a lakónépesség-számba "elrejtünk" egy sorszámot. Egy "vegyes hivatkozásos" DARABTELI függvénnyel megnézzük, hogy az adott szám hányadszor fordul elő a listában, ebből levonunk egyet, elosztjuk százzal, végül az egészet hozzáadjuk az eredeti értékhez:
Ha most ezt a képletet lehúzzuk egészen a végéig, azaz a 3177. sorig - és meghagyjuk az alapértelmezett formázást -, akkor azt látjuk, hogy a számok többsége változatlan marad, ám például Aggtelek 572-ből 572,01 lesz, köszönhetően annak, hogy a listában nem sokkal fölötte levő Acsádon szintén 572-en laknak. Egyébként kicsit meglepő, de csak a települések alig több mint 40 százalékának, egész pontosan 1292-nek van "egyedi lakónépesség-száma", a többi 1884-hez kis kiegészítést kell fűznünk ahhoz, hogy egyedi értékeket kaphassunk.
Ha most annyit módosítunk képleteinken, hogy a NAGY, KICSI és a HOL.VAN függvények ne a B, hanem a C jelű oszlop adataival dolgozzanak, vagy még egyszerűbben és gyorsabban a C oszlopban lévő számokkal felülírjuk a B-ben lévőket (Irányított beillesztés/Értéket), akkor a legkisebb települések listájában az egyik Csérből máris Megyer, az egyik Debrétéből Felsőszenterzsébet, míg az egyik Tornabarakonyból Tornakápolna lesz. Vagyis immáron a helyes adatokat látjuk táblázatunkban.
HOL.VAN pontosan vagy közelítőleg?
Az FKERES-hez hasonlóan a HOL.VAN függvény esetében is megadhatjuk, hogy a keresés pontos egyezésre történjen-e vagy "közelítőleg". A függvény negyedik, opcionális paramétere határozza meg a működés mikéntjét. Ha nem adunk meg semmit, vagy 1-et állítunk be, akkor a HOL.VAN azt a legnagyobb értéket keresi meg a - kötelezően növekvő sorrendbe rendezett - tartományon vagy tömbön belül, amely egyenlő vagy kisebb, mint a keresési érték, hasonlóképpen az FKERES IGAZ-ra állított keresési módjához.
Amennyiben - a leckében többször is mutatott módon - 0-t adunk meg egyezési típusnak, akkor a HOL.VAN az első olyan értéket keresi meg, amely egyenlő a keresési értékkel, mint amikor az FKERES "tartományban keres" paraméterét HAMIS-ra állítjuk. Végül az FKERES-hez képest extra lehetőségként a HOL.VAN egyezési típusának -1-et is megadhatunk, ilyenkor a függvény azt a legkisebb értéket keresi meg a - kötelezően csökkenő sorrendbe rendezett - tartományon belül, amely egyenlő vagy nagyobb, mint a keresési érték.
- 01: Mire jó az irányítószám?
- 02: Feltételes összegek és átlagok
- 03: Kategóriák és esetszámok
- 04: Minimum és maximum értékek
- 05: Keresés a tartományokban
- 06: Automatikusan bővülő grafikonok
- 07: Évek, hónapok, napok
- 08: Formás értékek I.
- 09: Formás értékek II.
- 10: Formázások feltételekkel I.
- 11: Formázások feltételekkel II.
- 12: Új diagramtípusok
