Ismét csak egy olvasói kérdés fogja a mai poszt témáját képezni, ugyanis nemrégiben kaptam egy olyan levelet, amelyben egy kedves Excel-felhasználó arra volt kíváncsi, hogy lehet bizonyos függvényeket minden sheetre alkalmazni, ergó például, hogy lehet megkapni a legkisebb bevételt produkáló város nevét, ha a városok és a boltok külön-külön sheeten találhatóak, mint mondjuk ezen a képen:
A nehézséget nem az okozza, hogy megtaláljuk, melyik a legkisebb értékünk az összes sheeten, hiszen ahhoz elég egy MIN függvény és a sheetek halmaza, ahol keresgélünk, a mi esetünkben valahogy így:
=MIN(Eger:Budapest!C4:C8)
Tehát az Eger sheettől kezdve egészen a Budapest sheetig megnézzük a C4:C8 tartományokat és az összesből fogjuk visszakapni a legkisebb értéket - ha a sheeteken nem ugyanakkora a tartományunk mérete, akkor értelemszerűen külön sheetekre lebontva kell megadni, hol vizsgálódjunk.
A probléma valódi nehézsége akkor jön, amikor nem csak az értékre, hanem az értékhez tartozó megnevezésre vagy a sheet nevére vagyunk kíváncsiak, mert akkor már összetettebb függvényre, tömbfüggvényre van szükségünk.
Első lépésként, csak az egyszerűsítés érdekében, a Ribbonunk Formulas fülén található Name Manager segítségével hozzunk létre egy nevesített tartományt, amely a Cover sheetünkre sorban begépelt munkalap-nevekből áll:
Első lépésként egy INDIRECT formulára lesz szükségünk, ami lehetővé teszi, hogy a konkrét sheetek beírása nélkül meg tudjuk hivatkozni a nekünk szükséges tartományokat. Egy egyszerű példán:
=SUM(A1:A2)
értelemszerűen összeadja a két cella tartalmát, viszont
=SUM(INDIRECT("A"&H10&":A"&H11)
a H10 cellából kiveszi, hogy mi legyen szummánk első száma, tehát A hány, illetve H11-ből, hogy mi legyen a második szám, tehát A hány.
A mi konkrét példánknál maradva:
=INDIRECT("'" & Munkalapjaim & "'!C4:C8")
ami nagyjából ennek a tartománynak felel meg:
{"'Eger'!C4:C8";"'Miskolc'!C4:C8";"'Székesfehérvár'!C4:C8";"'Budapest'!C4:C8"}
Az első lépés ezzel kipipálva, hiszen megvan sheetjeink és tartományaink "tartománya". Most nézzük meg, hogy a legkevesebb bevétel száma (amit a fentebb már említett MIN függvénnyel szerezhetünk meg, tehát hivatkozhatjuk is a már kinyert értéket, de be is ágyazhatjuk a függvényt magát) melyik sheeten található, ehhez pedig használjunk egy COUNTIF formulát:
=COUNTIF(INDIRECT("'" & Munkalapjaim & "'!C4:C8"),F9)
Tömbfüggvényként futtatva, ez visszaadja nekünk, hogy mely sheetünk található az F9 cellába korábban már kinyert legkevesebb bevétel száma. Hiszen tudjuk, például a
=COUNTIF(A2:A100,"Szevasz")
visszaadja azon cellák számát, amelyek Szevasz értéket tartalmaznak a megadott tartományban, ergó a fenti példánk COUNTIF függvénye, megadja, hogy mely sheeten hány cellában van F9 értékünk.
Innen pedig mire van szükségünk? Megnézni, hogy melyik sheeten van egyes eredményünk, amihez tökéletes segítség a MATCH függvény, hiszen ahogy már írtam róla, ez a formula visszaadja egy megadott érték helyét egy megadott tartományban.
Azaz
=MATCH(1,A10:A20,0)
megadja, hogy az A10:A20 tartományban hányadik helyen található az egyes érték, nullával garantáljuk a teljes egyezést.
Ha a mi példánkat folytatjuk, akkor a következőre lesz szükségünk:
=MATCH(1,COUNTIF(INDIRECT("'" & Munkalapjaim & "'!C4:C8"), F9),0)
Ez megnézi, hogy hol található egyes érték az előbb, a COUNTIF segítségével létrehozott {0;1;0;0} tömbünkben és pontos egyezés esetén visszaadja annak helyét.
És már csak egyetlen egy dologra van szükségünk, hogy megkapjuk az eredményünket, ez pedig nem más, mint egy INDEX, amivel ki kell íratnunk a Munkalapjaim nevesített tartományból annak a városnak/sheetnévnek a nevét, amelyre az egyes érték, az előbb kinyert egyes érték vonatkozik, azaz:
=INDEX(Munkalapjaim, MATCH(1,COUNTIF(INDIRECT("'" & Munkalapjaim & "'!C4:C8"), F9), 0))
CTRL+SHIFT+ENTER-rel kell végrehajtanunk, hiszen tömbökről beszélünk, majd látni is fogjuk az eredményt, azaz melyik sheetünkön van a legkisebb érték.
Hiszen mit tesz az INDEX függvény? Egy megadott tartományban a sorszám és az oszlopszám alapján visszaadja az ott szereplő értéket, tehát
=INDEX(A2:B100,2,1)
Az A2:B100 tartomány második sorának első oszlopában szereplő értéket adja eredményül.
De a lényeg, hogy mostani feladványunkat megoldottuk, bőven felturbózhatjuk még ezt a tömbfüggvényt azzal, hogy akár kattintható linkké alakítjuk az eredményünket, de teljesen ugyanezzel a logikával a VLOOKUP is működővé varázsolható, azaz meg tudjuk azt is mondani, hogy mely város mely boltja termeli a legkisebb bevételt. De ez legyen inkább egy házi feladat mindenkinek, ha úgy gondoljátok, érdemes megosztani azt a megoldást is, jelezzétek!
