Folytatódik a SUMPRODUCT-kaland: kizárás egy SUM függvényből

2016. augusztus 06. - Office Guru

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:

sumo0.jpgA 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)

sumo1.jpgNa 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:

sumo2.jpgHiszen 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)

sumo3.jpg

sumo4.jpgEzutá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)

sumo5.jpgPersze 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.

A bejegyzés trackback címe:

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

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.

I_Isti 2016.08.08. 09:53:51

Ez egy nagyon érdekes, profi megoldás!

Viszont.
Mint nagyjából mindennel az excel univerzumban a következőt érzem vele problémának:
mondjuk kollégák vagyunk, és elmész két hét szabadságra. A táblát pedig amíg szabin vagy, átadod nekem, hogy a riportot frissítsem. A frissítés során lesz néhány sor, amelyet be kell szúrnom a Települések-összeg táblába, és néhány sor, amit változtatni kell a "nem összegzendő" listában, néhányat hozzá kell adni, néhányat el kell venni belőle.

Mondjuk ez a jelenlegi tevékenységem mellett ellátandó feladat, ezért nem sok időt szeretnék rá szánni, és ez a riport az általad kezelt excel-világnak csak egy lapja a 231-ből, mondjuk a 136-dik.

És ott állok, és nézem ezt a függvényt, hogy mi a bánatot csinál, és miért pont úgy csinálja, és a fenti feladatot hogyan tudom gyorsan úgy megoldani, hogy az eredmény helyes is legyen?

Én lazán a következőt csinálnám minden hasonló jellegű feladatnál (ha már excelt _kell_ használni, mert senki nincs a környéken, aki értené azt, hogy
select sum(összeg) as összeg
from "település_összeg"
where település in (select település_nev from "nem_összegzendők")

1) Mind a két táblát táblázatként formáznám (így ha hozzáadok egy új sort, akkor egyértelműen látszik, hogy a táblázathoz hozzáadásra került-e, és ha igen, akkor biztos lehetek abban is, hogy az abszolút cellahivatkozások a hivatkozó képletekben az új sort is tartalmazni fogják.
2) A "nem összegzendő" táblát kiegészíteném egy ellenőrző oszloppal (kb ez: =DARABTELI(Táblázat1[Település];Táblázat2[[#Ez a sor];[Nem összegzendő]]) - így ha valaki olyan sort szúr bele, ami nincs az összegzendő táblában,akkor egyből érzékelhetővé válik, mert ott a fv eredménye 0.)
3) A település-összeg táblát kiegészíteném két oszloppal: az egyik darabtelivel megadja, hogy szerepel-e a település neve a "nem összegzendő" táblában, a másik pedig ha az előző oszlop értéke 1, akkor az értéke legyen az összeg, egyébként 0.

Az eredmény szum-mal is megnézhető egyből, vagy akár kimutatással.
+1) a település összeg és a másik táblát is ki lehet egészíteni darabteli() függvénnyel, ami ellenőrzi, hogy egy településnév csak egyszer szerepelhessen a táblában.