Kollégák kérdéséről mindig szívesen írok bejegyzést, mert ezek valódi, gyakorlatban felmerülő példák, nem csak önmagáért való képlet magyarázatok. Ma egy elég komplex tervező fájlunk kapcsán merült fel a kérdés: ha az értékesítő kollégák egy kis két dimenziós táblázat alapján vannak termékcsoportokhoz és területekhez rendelve, akkor hogyan tudjuk ebből a táblából kivenni, hogy egy adott termékcsoportért és területért melyik értékesítő felelős.
Tehát ilyen a táblázat:
Ebből kellene kiszednünk, hogy pl. Szegeden ki a felelős a Kozmetika termékekért.
FKERES-re gondolnánk, dehát melyik oszlopot írjuk bele?
Nos, szoktam mondani, hogy INDEX formulával majdnem mindent meg lehet oldani, főleg, ha valami keresős jellegű feladatról van szó. Tehát most is bátran tippeljünk inkább az INDEX-re, ez lesz a legegyszerűbb megoldás kulcsa! (Ha még nem ismered ezt a formulát, kezdd itt!)
Az INDEX pont azt tudja, amire most szükségünk van: egy két dimenziós táblából a sor és az oszlop száma alapján kiszedi a „metszet” elemet. Tehát úgy működne a fenti esetben, hogy Szeged a 4. sorban van, a Kozmetika a 2. oszlopban, vagyis így néz ki a formula a tábla ; sor száma ; oszlop száma paramétersorral:
=INDEX( $C$3:$H$11 ; 4 ; 2 )
És az eredmény Szegedi K. Szuper, egy jó eredményünk van. Akkor most ki kellene találni, honnan szedjük elő a sor és oszlop számot. 🙂
Ebben a HOL.VAN (MATCH) lesz segítségünkre, ami az INDEX formula tökéletes kiszolgálója. Itt írtam róla.
=HOL.VAN( „Szeged” ; $B$3:$B$11 ; 0 )
=MATCH( „Szeged” ; $B$3:$B$11 ; 0 )
Ez a formula megadja, hogy hányadik helyen található Szeged a város oszlopban. Az eredmény 4.
A HOL.VAN egy sorban vagy egy oszlopban (szigorúan egy dimenzióban!) tud keresni, tehát ugyanígy egy HOL.VAN-t írunk a termékcsoport sorra, ami megadja a pozíciót (jelen esetben az oszlop számot):
=HOL.VAN( „Kozmetika” ; $C$2:$H$2 ; 0 )
=MATCH( „Kozmetika” ; $C$2:$H$2 ; 0 )
Az eredmény 2.
Nincs más hátra, mint ezeket a darabkákat összerakni egy szép formulába, a megfelelő hivatkozásokkal!
=INDEX( $C$3:$H$11 ; HOL.VAN($B15;$B$3:$B$11;0) ; HOL.VAN($C15;$C$2:$H$2;0) )
=INDEX( $C$3:$H$11 ; MATCH($B15;$B$3:$B$11;0) ; MATCH($C15;$C$2:$H$2;0) )
Az INDEX első paramétere a tábla „belseje”, az egyik HOL.VAN a fejlécre, a másik a sorfejlécre hivatkozik. Fontos, hogy összhangban legyenek a tartományok, nehogy elcsússzanak egymástól a koordináták!
Remélem, hasznosnak találjátok ezt az egyszerű, segédcellák nélküli megoldást!
Kérdezz tőlünk Excel segítő csoportunkban vagy kövesd az Adatkertészetet a Facebookon!
1 ping