A mostani poszt remélhetőleg sokak (köztük jómagam) számára is tanulságos írás lesz, hiszen egy olyan feladatra keresem a megoldást, amely rendszeresen szembejöhet velünk a mindennapokban (legalábbis az első része). Adott a következő roppant egyszerű Excel-tábla:

Már ránézésre egyértelmű az is, hogy mi itt a kérdés, simán keressük meg a megadott városhoz tartozó bevételi értéket a segédtáblában. Én most egyféle megoldást fogok felvázolni, de hozzászólásokban jöhet minden más javaslat is, hiszen ez tipikusan az a kérdés, ahol legalább féltucat függvényalapú megoldást el bírok képzelni (az egyebekről ne is beszéljünk).

Az én megoldásom (mivel számokról van csak szó) a SUMIF függvény (de természetesen használható bővített verziója, a SUMIFS is), amely az első paramétereként megadott tartományban összesíti a második paramétereként megadott kritériumnak megfelelő értékeket - harmadik, nem kötelező paraméter pedig az összeadandó értékek tartományának külön meghatározása lehet.

Jelen példánknál ez azt jelenti, hogy a tábla lesz az első paraméter, a második paraméter a városunk neve, a harmadik paraméter pedig az első számértéket tartalmazó sortól kezdődően a táblánk megint:

És íme, az eredmény:

Na de mi is ezzel a probléma? Hát elég egyértelmű: kizárólag számokra használható, tehát ha mondjuk nem numerikus értékeink is vannak, akkor ez így már nem lesz jó - lásd a következő módosított példa:

De még mielőtt azonban erre is bemutatok egy lehetséges megoldást, hangsúlyozni kell, hogy egy ilyen jellegű probléma már nem mindennapos, az pedig, hogy formulával próbáljuk megoldani, még annál is ritkább, hiszen előbb alakítjuk "normál" módon kezelhető táblázattá vagy fordulunk VBA-hoz, minthogy függvények egybeágyazásán gondoljunk. Ettől függetlenül jó kihívás a gondolkodni vágyó elme számára egy ilyen probléma - lássuk is, hogy én mire jutottam.

Az én logikám az INDEX függvényen alapul, azon a függvényen, amely az első paramétereként megadott tartományban a második paramétereként megadott sor és a harmadik paramétereként megadott oszlop metszeti cellájának értékét adja vissza.

Azaz ha meg tudjuk határozni, hogy az adott város melyik sorban, illetve melyik oszlopban található, akkor már csak az INDEX formulába kell ezeket ágyaznunk és kész is a válasz.

Szóval adott az első kérdés: hogy határozzuk meg, hogy "Esztergom" értékünk melyik sorban található?

Én szokás szerint a SUMPRODUCT függvényhez nyúlok, annak is egy feltétellel kibővített verziójához. Ez a feltétel nagyjából annyiból fog állni, hogy az A2:C7 (a táblánk) összes értékét megvizsgáljuk, hogy valamelyik érték egyezik-e a megadott városunk nevével - azaz eredményként rengeteg nullát és egy egyest várunk, ott ahol van egyezés.

(A2:C7=G3)

Ezt a tömböt pedig szimplán összeszorozzuk egy, a ROW függvény által meghatározott, hivatkozásokat tartalmazó másik tömbbel, amely a táblánk összes sorát tartalmazza:

ROW(A2:C7)

A szorzás eredményeként lesz egy tömbünk, amelyben nullák és egy olyan szám szerepel, szóval ha ezt összegezzük egy SUMPRODUCT segítségével, akkor megkapjuk, hogy hányadik sorban szerepel az adott városunk:

Innen már nyílegyenes út vezet az oszlopszám meghatározásához, elég ismernünk a COLUMN függvényt, ami teljesen ugyanazt tudja, mint a ROW, csak oszlopokra, azaz oszlopok hivatkozásait adja vissza, tehát egy megadott celláról megmondja, hogy az hányadik oszlopban szerepel.

Teljesen ugyanazt kell csinálnunk tehát, mint a sor meghatározásánál, annyi kivétellel, hogy az első tömbünket a COLUMN által meghatározott, hivatkozásokat tartalmazó másik tömbbel szorozzuk össze:

Ezzel pedig az egész történetnek a végére is jutottunk, hiszen ezt a két függvényt kell csak beágyaznunk egy INDEX formulába, amelynek első paramétere értelemszerűen a táblánk, második paramétere a SUMPRODUCT és ROW függvények felhasználásával készült sorszám, a harmadik pedig a COLUMN száma:

=INDEX(A2:C7,SUMPRODUCT((A2:C7=G3)*ROW(A2:C7)),SUMPRODUCT((A2:C7=G3)*(COLUMN(A2:C7))))

Works like charm!