Telefonszámunk: 1-472-0679

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

2013-10-31 - horvimi - Megtekintések száma: 2,254 - Kategória: Függvények, Képletek, Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

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.

mintatabla

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

adatbazis-fuggveny-szamitott-feltetel
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

  • Számított feltétel szerinti összegzés

Vélemény, hozzászólás?