Az előző bejegyzésekben elmagyaráztam az INDEX formula alapjait, és mutattam egy hasznos és egyszerű dinamikus összegző formulát.
Ebben a cikkben megmutatom, hogyan tudjuk az INDEX-et a HOL.VAN (MATCH) segítségével felhasználni arra, hogy egy táblázat egyik oszlopában megkeressünk egy adatot, s eredményül egy másik oszlopban levő adatot adjunk vissza. Ezt általában az FKERES ((VLOOKUP) formulával szoktuk csinálni: a keresési értéket a tartomány első oszlopában megkeresi, majd a megadott számú oszlopban levő adattal tér vissza. A gyakorlatban azonban nem mindig az első oszlopban szeretnénk keresni, előfordul, hogy az adat, amit vissza szeretnénk kapni, a keresési oszloptól balra van. Az INDEX és HOL.VAN alkalmazásával könnyen túlléphetünk ezen a korlátozáson, sőt, a kalkulációt is gyorsabbá tehetjük.
Először tehát ismerkedjünk meg a HOL.VAN formulával. Az FKERES-hez nagyon hasonlóan működik: a keresési értéket megkeresi a kijelölt tartományban, és a tartományon belüli sorszámát adja vissza. Épp ezért a keresési tartomány mindig egy dimenziós, tehát vagy egy sor, vagy egy oszlop lehet csak.
Kis szintaktika és magyarázat:
Fontos, hogy mindig a tartományon belüli sorszámot adja vissza a formula, nem a munkalap bal oldalán látható sor számot!
Nézzünk egy pici példát. Keressük meg, hogy a kis táblázat szerint mennyi citromunk van. Először meghatározzuk a citrom sorszámát HOL.VAN-nal:
Majd ezt a sorszámot az INDEX formulában felhasználjuk arra, hogy a másik oszlopból ugyanezt a sorszámú elemet kiolvassuk:
Olyan, mintha az FKERES-t két részre bontanánk: a HOL.VAN végzi el a keresést, az INDEX pedig ennek megfelelően visszaadja az elemet a megadott oszlopból. A két formulát egymásba is ágyazhatod:
=INDEX( $C$2:$C$8 ; HOL.VAN(F3 ; $D$2:$D$8 ; 0) )
=INDEX( visszatérési tartomány ; HOL.VAN( keresési érték ; keresési tartomány ; egyezés típusa) )
S mivel az INDEX formulát már jól ismeritek, át tudjátok alakítani úgy, hogy vízszintesen működjön, a VKERES mintájára.
Milyen előnyei vannak az INDEX – HOL.VAN párosításnak az FKERES-sel szemben?
1. A keresési oszlop és a visszatérési oszlop bárhol lehet a munkalapon egymáshoz képest!
2. Mivel a keresési és a visszatérési oszlop külön van kijelölve, nincs probléma, ha pl. oszlopot szúrsz be / törölsz a táblázatból! (FKERES-t használva át kellene írni az oszlop számát, amit általában elfelejtünk…)
3. Gyorsítható a kalkuláció! A keresés nagyon erőforrás-igényes feladat. Ha több oszlopból is szükség van adatra (pl. dolgozói kódhoz vezetéknév, keresztnév, lakcím, stb.) ne csinálj minden oszlopra FKERES-t! Csak egyszer keresd meg a HOL.VAN-nal a sorszámot, s az INDEX-szel szedd ki az oszlopokból a megfelelő adatot. Tehát: a HOL.VAN-t tedd külön oszlopba, s a szükséges visszatérési adatokhoz csak az INDEX-et írd fel, mindig a HOL.VAN által visszaadott sorszámra, és a megfelelő visszatérési oszlopra hivatkozva. Ezzel egy csomó felesleges kereséstől megszabadítod a fájlodat.
Az INDEX – HOL.VAN hátránya?
Egy dolgot tudok itt megemlíteni, s valójában ez sem hátrány, inkább csak egy kis odafigyelést igényel. Nagyon fontos, hogy a keresési és a visszatérési tartomány szinkronban legyen egymással. Ugyanabban a sorban kell kezdődnie mindkettőnek, mert különben nem a megfelelő sorszámhoz tartozó értéket fogja visszaadni az INDEX.
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
1 ping