Számított feltétel szerinti összesítések

Power BI Desktop alapok Hamarosan Megnézem Két Online délelőtt PowerQuery bevezető Hamarosan Megnézem Két online délelőtt Videó Megnézem - Excel makró alapok - Power BI alapok - Excel PIVOT mester Tanfolyamok - PowerQuery alapok

A feltétel szerinti összegzés (Sumif/Szumha), illetve a megszámlálás (Countif/Darabteli) függvényeket legtöbben ismerik. Sőt, az excel 2007-től ezek több feltételt is használni képes nagy-testvérei is egyre inkább bekerülnek a köztudatba. (SumIfs/Sumhatöbb, Countifs/Darabhatöbb). Ezekkel a függvényekkel remek riportokat lehet készíteni, de azért van néhány korlát, amiket nem tudnak átlépni.

Az oktatóanyagban ezt a táblázatot használjuk példaként.

Érdekesebb kérdések ehhez a táblázathoz

• A nyereséges sorok száma
• Az „Északi” nyereségesek száma
• Az „Északi” nyereségesek összbevétele
• Északiak és Déliek együttes bevétele vagy nyeresége vagy vesztesége.

Természetesen lehetne egy „Nyereség” nevű számított oszlopot tenni a végére, de most ezt szándékosan nem tesszük.

Mi a gond az ismert feltételes összesítő függvényekkel?

• A feltételek csak konkrét értékek lehetnek (konstans, cellacím vagy számítás), de olyat már nem tudnak, hogy csak azokat a sorokat vegyék figyelembe, ahol pl. a ‘B’ oszlopban nagyobb érték van, mint a ‘C’ oszlopban
• A feltételeket csak ÉS kapcsolatba tudják hozni, azaz egy következő feltétel tovább szűkíti az előző feltéteknek megfelelő sorok számát. A feltételeket nem képesek VAGY kapcsolatba tenni.

Akkor mi a megoldás?

• Egyik lehetőség, hogy egy külön oszlopba elvégezzük a vizsgálatot, majd ezt az oszlopot értékeljük ki. Ha pl. a nyereséges sorok számát keressük, akkor az utolsó oszlopba írhatunk egy olyan képletet, ami nullát ad eredményül, ha nincs nyereség, különben pedig egyet. Ezen oszlop összesítésével megkapjuk a nyereséges cégek számát. Ez két lépés. Egyik a segédoszlop elkészítése, és  a második az összegzés.

A segédoszlop képlete lehetne egy HA() függvény, de Én ezt javaslom beírni, majd lehúzni az oszlopra:

=--(B2>C2)

A zárójelben lévő kifejezés TRUE/FALSE eredményt ad, a dupla mínusz jel pedig ezt alakítja át 0/1 értékekre.

• Második lehetőség, hogy Adatbázis függvényt próbálunk bevetni, és a kritérium tartományt olyan számítással adjuk meg, ami a táblázat első sorában értékel ki. Az egész kritériumtartományt elnevezzük „Criteria” néven.

A függvény pedig a következő:

=DCOUNTA($A$1:$D$13;"Kateg";Criteria)

=AB.DARAB2($A$1:$D$13;"Kateg";Criteria)

És újra a SUMPRODUCT (Szorzatösszeg)

• Harmadik lehetőség, hogy az egészet egy képlettel oldjuk meg.

=SUMPRODUCT(--(B2:B13>C2:C13))
=SZORZATÖSSZEG(--(B2:B13>C2:C13))

Ez a képlet belül a ‘B’ és a ‘C’ oszlopot hasonlítja össze, és egy olyan tömböt ad eredményül, aminek annyi eleme van, ahány sora a két megadott tartománynak, de ott van benne TRUE, ahol a B>C, különben FALSE. A dupla mínusz jel a logikai értékeket 0/1 értékekké alakítja. Végül a SUMPRODUCT összeadja, az eredmény a tömbben szereplő egyesek összege, azaz a nyereséges sorok száma lesz.

Letölthető munkafüzet: szamitott-felt-szerinti-osszesitesek

A kapcsolódó videóban látható a megoldás menete, és a többi kérdésre is választ kapunk