A különböző többdimenziós adatokat tartalmazó rendszerekből sokszor úgy hívunk le adatokat, hogy a különböző dimenziók nem egymást követő oszlopokban helyezkednek el, hanem egy mátrixot hozunk létre. Ez a legtöbb esetben az adatok átláthatósága miatt egy nagyon jó döntés, de nehézkessé teszik a különböző feltételek szerinti összegzést. Hogyan készítsünk tehát többdimenziós összegzést?
Nézzünk erre egy példát!
A feladatban használt fájlt innen tudod letölteni.
Többdimenziós összegzés
Ebben az esetben „csak” 4 dimenzióval dolgozunk: a hónap, számla, ország és kereskedő. A lekért adat így például az adott dimenziókhoz tartozó EUR összeg és lentebbi feltételek alapján szeretnénk egy összesítést végezni.
A SZUMHATÖBB függvény ebben az esetben nem használható, mivel egyszerre kell sorokban és oszlopokban is szereplő feltételeket is vizsgálni, és az összegzendő adatok is két dimenzióban helyezkednek el.
Az ilyen esetekre segítségül hívhatjuk a SZORZATÖSSZEG függvényt és amennyiben vannak már ismereteink a tömbfüggvényekről, akkor nincs is nehéz dolgunk.
De először nézzünk egy egyszerűbb példát!
A SZORZATÖSSZEG függvény működését már egy korábbi blogposztban olvashattad, de annyit elevenítsünk fel róla, hogy ő egyszerűen úgy működik, hogy a megadott tömbök elemeit először összeszorozza, majd a kapott eredményeket adja össze.
Az első példánkban ezt fogjuk felhasználni egy feltételes összegzéshez.
Többdimenzionális összegzés – egyszerű példa
Itt annyi lenne a kérdés, hogy a Kereskedő 1-hez tartozó elemeket adjuk össze.
A SZORZATÖSSZEG függvény egyik tömbje a C oszlopban szereplő elemek lesznek, mivel őket szeretnénk összeadni.
A másik argumentumban pedig kihasználjuk az Excelnek azt a tulajdonságát, hogy egy logikai teszt eredményeképpen vagy IGAZ-at, vagy HAMIS-at ad eredményül, amelyek „átváltva” számokká 1-et (IGAZ) vagy 0-át (HAMIS) jelentenek.
Így az első argumentumban megvizsgáljuk, hogy az adott feltétel (B oszlop eleme egyenlő-e „Kereskedő 1”-el) IGAZ-e. Amennyiben igaz, úgy 1-et kapunk, amit megszorozva a C oszloppal a C oszlop értéke marad, és amennyiben hamis, úgy 0-át kapunk, amit a C oszloppal szorozva 0-át kapunk eredményül.
Hogy is néz ki ez a gyakorlatban?
Először a feltétel vizsgálata:
Amit kibontva a következő tömböt kapjuk
Ezeket pedig számmá a legegyszerűbben úgy alakíthatjuk, ha a tömböt 1-el szorozzuk:
Azaz:
Ezt a tömböt pedig már összeszorozhatjuk a C oszlop elemeivel, és a következő eredmény kapjuk:
Mivel a SZORZATÖSSZEG függvény már alapvetően képes a tömböket kezelni, ezért a CTRL+SHIFT+ENTER lezárásra sincsen szükségünk ebben az esetben.
Többdimenziós összegzés négy dimenzió alapján
A négy dimenziót használó példánkban pedig ugyanúgy járhatunk el, azzal a könnyítéssel, hogy igazából nincs szükség az 1-el való szorzásra, mivel szorzást végezhetünk már a logikai értékek között is, és úgy már alapból egy számot kapunk vissza. Így a feltételek vizsgálata között már szorzást fogunk eleve végezni, így egyszerűsítve a képletünket.
A példa a logikai értékek közti műveltekre:
A feladatunk megoldása így a következő:
=SZORZATÖSSZEG((C10:N10=A32)*(C11:N11=A33)*(A12:A26=A34)*(B12:B26=A35)*C12:N26)
Az utolsó elem tartalmazza magukat a számokat, mint egy kétdimenziós tömböt, és ha kibontjuk az első négy feltétel vizsgálatát, akkor is jól látszik, hogy azokból is egy kétdimenziós tömb jön létre, ami 0-ákat és 1-eseket tartalmaz.
Egyeseket csak azokban az esetekben kapunk, amikor a 4 feltétel teljesül, így összeszorozva a teljes mátrixban lévő elemekkel már csak a 40-et és a 69-et fogja a SZORZATÖSSZEG összeadni, azaz megkapjuk a kívánt eredményt, a 109-et.
Remélem számodra is hasznos lesz ennek a függvénynek a használata, kérlek, oszd meg másokkal is.
