Az alig ismert vagy éppen titkos Excel-funkciókról szóló posztban nem igazán akartam szóba hozni, nem is azért, mert annyira elterjedt lenne a használata, hanem inkább azért, mert úgy gondoltam, megérdemel egy külön posztot a hihetetlen lehetőségeket rejtő SUMPRODUCT függvény, amelynek bemutatása után ki is térek arra, hogy pontosan mi mindenre lehet használni.
Ha bemegyünk az Excelünkbe és egyszerűen meghívjuk a függvényt, akkor már a leírásból szépen ki tudjuk találni, hogy mire való: két vagy több tömb elemeit szorozza majd adja össze, azaz ahogy a lenti példán is látható, a két tömb egymással egy sorban lévő elemeit összeszorozza majd az eredményeket szummázza. Eddig tehát ezzel nem is lenne gond, itt jön inkább a kérdés, hogy erre mi szükség van, hiszen ezt a feladatot ez a formula nélkül is simán meg lehet oldani.
Mi van akkor, ha kicsit komplikálom az előbbi példát, és az első oszlopban most már nem számok, hanem mondjuk megyék fognak szerepelni (mondjuk ahol egy cég boltjai működnek) és azt szeretném megtudni, hogy mondjuk Csongrád megyében pontosan az adott bolt mekkora forgalmat generált egy adott periódus alatt. Még jómagam is egyértelműen az egyszerűbbnek tűnő, jobban ismert SUMIF megoldás mellett tenném le a voksot, de ne adjuk fel, tartsunk még ki a SUMPRODUCT mellett, hogy meglássuk valódi értékét!
Szóval ezt a feladványt SUMPRODUCT segítségével elvileg a következőképpen oldhatjuk meg - gondolnánk elsőre:
=SUMPRODUCT((A2:A11="Csongrád"),B2:B11)
Azaz fogjuk az első oszlopunkat és mint tömb meghivatkozzuk, majd az egyenlővé tétellel arra utasítjuk a függvényt, hogy a Csongrád értéket tartalmazó cellákat keresse meg, majd az ezekhez tartozó értékeket a B2:B11 tömbünkból összesítse.
De hát az eredmény nulla lett! Miközben Csongrádnál 24 a szumma összeg, mégis miért nem jó a függvényem? Itt kell megtanulnunk valami olyat, amit máshol is könnyen hasznosíthatunk, azaz a kötőjel funkcióját az Excelben.
A kötőjel (illetve a dupla kötőjel) Boolean (IGAZ,HAMIS - TRUE, FALSE) értékeket alakít egyesekké és nullákká, sőt, mivel a program a kötőjelet "tagadásként" is értelmezi, még az előjelet is megfordítja, ergó egy kötőjellel egy TRUE értékből -1 lenne (a nulla nem változik), azaz még egy kötőjelre van szükségünk, hogy megkapjuk a vágyott egyesünket. Azaz erre van szükségünk:
=SUMPRODUCT(--(A2:A11="Csongrád"),B2:B11)
Hiszen első körben azért nem működött jól a formula, mert ugyan megtalálta a Csongrád értékeket, de ott a cella TRUE értéket vett fel, azzal pedig hiába szorozgatta a nyolcasainkat - a két kötőjellel viszont meg is van a kívánt eredményünk.
A SUMPRODUCT ereje szerintem abban rejlik, hogy kvázi iszonyú sok tömböt képes kezelni egyszerre (gyengesége meg abban, hogy ezen tömböknek azonos méretűnek kell lenniük), amit kapásból látni is fogunk, ha kibővítjük fenti példánkat egy újabb oszloppal (de ez lehetne akár 10 újabb oszlop is) és arra leszünk kíváncsiak, hogy Zala megye keleti részén mennyi volt az eladások értéke.
Innen pedig már roppant egyszerű a sztori, hiszen A2:A11="Zala" valamint B2:B11="Kelet" és a C2:C11 tömböket kell összeolvasztanunk egy SUMPRODUCT-ba, értelemszerűen dupla kötőjel használatával:
=SUMPRODUCT(--(A2:A11="Zala"),--(B2:B11="Kelet"),C2:C11)
És done! Mintha egy negyven oszlopból álló listában filterezgetnénk oszloponként, hogy megkapjuk a vágyott szummát, csak ezt most belevághatjuk egyetlen függvénybe - nem mondom, hogy gyorsabb, de hogy szép megoldás, az biztos. Barátkozzunk egy kicsit most ezzel a függvénnyel, ugyanis még vissza fogok térni rá egy emeltszintű "SUMPRODUCT kiképzés" alkalmával.
