Kiaknázatlan kincsek rejtőznek Excelünk mélyén - szorzunk, összeadunk egy óriási listában

Egyetlen függvény segítségével - a SUMPRODUCT történet első része

2015. szeptember 13. - Office Guru

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.

hetvenegy.jpgMi 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!

hetvenketto.jpg

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)

hetvenharom.jpgHiszen 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.

hetvennegy.jpg

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)

hetvenot.jpgÉ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.

A bejegyzés trackback címe:

https://officeguru.blog.hu/api/trackback/id/tr67786212

Kommentek:

A hozzászólások a vonatkozó jogszabályok  értelmében felhasználói tartalomnak minősülnek, értük a szolgáltatás technikai  üzemeltetője semmilyen felelősséget nem vállal, azokat nem ellenőrzi. Kifogás esetén forduljon a blog szerkesztőjéhez. Részletek a  Felhasználási feltételekben és az adatvédelmi tájékoztatóban.

Hmmm... 2015.09.14. 21:08:08

Ravasz, trükkös:-) Még pofásabb - ekszelszerűbb;-) adatkezelésű - lenne, ha még a szűrésre használt "Zala" ill. a "Kelet" is egy-egy cellába lenne letéve, és a képlet azokat hivatkozná.
Viszont van ennek a feladatnak egy igazi "iparos" megoldása adatbázisfüggvénnyel. Ráadásul abban szinte tetszőleges, képlettel leírható szűrőfeltétel is megadható (ezt kevesen tudják, már nekem is utána kéne nézni;-) Én nem szeretem a trükkös megoldásokat, mert nagyon megnehezítik az olvasást / áttekintést (1 hónap múlva: "mija a franczkarikát akartam én itt ezzel?")

©________ 2015.09.14. 21:10:10

Csini.
Bar en az utolso pelda eseten pivot tablahoz nyulnek reflexbol.

Office Guru 2015.09.14. 22:01:14

@Hmmm...: Teljesen jogos hozzászólás, amivel nem lehet nem egyetérteni - de néha azért annyira jó gondolkodni "outside the box" :)

Macskond 2015.09.14. 22:16:41

@Hmmm...: HAha.. Ezer éve én is így kezdtem hogy tele voltam trükkös programmal... komment az minek, tudom miért irtam a programot... még a szakdolim egy része is egy trükkre volt kihegyezve.
Aztán jött a gyakorlat...és mikor először kellett belemódosítani 3-4 év után a trükkös programba...szép lassan elkezdtem leszokni a trükkökről.

Mára inkább olvasható progit írok, vagy ha mégis trükközök, akkor jól megkommentelem hogy mit csináltam...

Azért ez a -- trükk még tud jól jönni, megjegyeztem ! :-)

Schrobi 2015.09.14. 23:11:32

Bocsánat, de a 2 tömb között a függvényben nem sima, hanem pontosvessző kell... Gondolom más eleve így csinálta, aki Excel zseni, de ha valaki mégsem, akkor érdemes azzal próbálkozni, mert amúgy nem működik...

Void Bunkoid 2015.09.14. 23:15:31

A duplakötőjel okos használatáért pirospont, amúgy valóban, mint páran megírták, ennél jóval több oszlopos szűrögetéseknél egyszerűbb inkább adatbázist használni, vagy ha ragaszkodunk az excelhez, akkor pivottáblát (a kilométer hosszú függvényt neccesebb módosítgatni :)).

alfredaurel 2015.09.15. 09:29:43

@Schrobi: Hello, az hogy pontosvesszo vagy sima vesszo azat a szamitogep beallitasa hatarozza meg: Control Panel/Region and Language/Additional Settings/List Separator