A következő posztban egy Exceles, feltételek alapján történő összegzés problémakörét fogjuk körbejárni egy lehetséges megoldás alapján és remélhetőleg ez sokak számára segítség lehet majd, hiszen a most felvázolandó kérdéssel sokan-sokszor szembesülhetnek a legegyszerűbb napi Exceles feladatok során is.
Adott a következő két táblázat, amely komolyabb leírás nélkül is magáért beszél:
A célunk egyetlen képlettel annak elérése, hogy a Nem összegzendő táblázatban szereplő települések nélkül összegezzük a teljes táblánk értékeit. Ez tipikusan az a probléma, amelyre rengeteg megoldás létezik, nagyon favágó módszerrel készíthetünk például egy hatalmas, IF és SUM függvények egymásba ágyazásából álló formulát, de nyomhatunk egy PIVOT-Slicer megoldást is, kissé manuálisabb SUMIF felhasználás is szóba jöhet, de még akár a DSUM formulával is próbálkozhatunk. Ettől függetlenül a mostani megoldásban maradunk két függvénynél, az egyik az egyértelműen szükséges SUM, a másik pedig az Excel sokat megélt, titkos szuperfegyvere, a SUMPRODUCT.
Ennek a válasznak a kulcsa abban a logikában rejlik, hogy nem kizárni akarunk bizonyos településeket az összegzésünkből, hanem a teljes szummánkból vonjuk ki a Nem összegzendő települések értékének szummáját. Azaz a formulánk első része kérdés nélkül:
=SUM($E$3:$E$17)
Na de hogyan összesítsük a kizárandó településeink értékeit? Ehhez hívjuk segítségül a COUNTIFS függvényt első körben, amely egy megadott tartományban megadja, hogy hány, az adott feltételnek megfelelő cellánk van, de ellentétben a COUNTIF formulával, ez több feltétel és tartomány elemzésére is képes.
Például a következő képlet megadja, hogy összegeink között mennyi 200-nál kisebb és 195-nél nagyobb érték található:
=COUNTIFS(E3:E17,"<200",E3:E17,">195")
Ha most ezt a mi táblánkra átvetítjük és beleerőltetjük egy cellába, hogy számolja meg településeink között hány olyan van, ami Nem összegzendő, akkor értelemszerűen a lenti képen látható függvénynek nulla lesz az eredménye:
Hiszen nincs olyan cella, ami megfelelne mindhárom feltételnek. De még ne temessük a COUNTIFS-t, hiszen ezt a nulla szám ellenére még felhasználhatjuk egy SUMPRODUCT egyik paramétereként, mert azt már az itteni tucatnyi poszt után tudhatjuk, hogy ez a rendkívül sokoldalú tömbfüggvény (itt bővebben róla) összehasonlítást és ez alapján történő összegzést is lehetővé tesz.
Azaz egyszerűen megfogjuk az előző képen felvázolt COUNTIFS függvényünket, beágyazzuk egy SUMPRODUCT-ba, mint a formula első paramétereként meghivatkozott tömb, majd a formula második paramétereként meghivatkozzuk az összegeinket a teljes táblában, így pedig szépen a SUMPRODUCT azt fogja nekünk kiszámolni, hogy mennyi a COUNTIFS függvényrészben meghatározott feltételeknek megfelelő cellákhoz tartozó összeg.
=SUMPRODUCT(COUNTIFS(H4:H6,$D$3:$D$17),$E$3:$E$17)
Ezután meg már csak ki kell vonnunk a legelső SUM-ból a az előbbi SUMPRODUCT-ot és meg is kapjuk az eredményünket:
=SUM($E$3:$E$17)-SUMPRODUCT(COUNTIFS($H$4:$H$6,$D$3:$D$17),$E$3:$E$17)
Persze ezt a hibák elkerülése érdekében átalakíthatjuk elnevezett tartományokra is, sokkal jobban szétszedhetjük és nagyobb táblákra optimalizálhatjuk a képletet, de alapvetően kiindulópontnak tökéletes. De jöhetnek kommentben más megoldások is, hiszen nekem sem a fenti volt az első, ami eszembe jutott ennél a kérdésnél, másrészt nem is biztos, hogy ez a legegyszerűbb válasz.
