Telefonszámunk: 1-472-0679

Feltételes összegzés és megszámlálás két dimenzióban

2013-09-27 - horvimi - Megtekintések száma: 7,376 - Kategória: Függvények, Képletek, Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

Az anyagban megmutatom, hogy lehet képlettel megoldani azt a feladatot, amikor a sor, illetve oszlop azonosítók alapján egyszerre kellene összesítést vagy megszámlálást csinálni. Volt egy korábbi anyag, ahol 2D keresést mutattam, de ez más.

Íme a probléma

2D-osszegzes-feladat

Tehát ugyanazok az értékek ismétlődgetnek a fejlécben, mint az első oszlopban. Nekünk pedig az alsó, összesített tábálázatot kell összehozni

Hű, ez nagyon érdekes. nem tudom, hogy mennyire gyakori probléma, de Nekem annyira megtetszett, hogy azt éreztem, ezt mindenképpen fel kell tenni az Excel Bázisra.

PIVOT?

Mivel ismétlődő fejléc elemek vannak, PIVOT-al nem lehet megcsinálni. Legalább is Én nem találtam rá megoldást. ha lenne valaki, aki IGEN, akkor commentben ossza már meg velünk!

MACRO?

Igen, makróval meg lehet csinálni. Egymásba ágyazott ciklusokkal. Már ha érted mire gondolok. Ez nagyobb táblázat esetén meglehetősen lassú, ráadásul tudni kell programozni, és a VBA nyelv ismerete is szükséges.

A megoldás – Képlet – SUM vagy SUMPRODUCT

Nem gondolnád, Igaz? Nézzük a feladatot konkretizálva az ‘A‘ kategóriára: Összesíteni kell az adattartomány azon elemeit, ahol a fejlécben ‘A‘ van és a sor elején is ‘A‘ van. Mivel az egyik feltétel vízszintesen van, a SUMIFS (Darabhatöbb) függvény nem jön számításba. Tömbökkel kell dolgoznunk, így esélyes, hogy tömbképlet lesz a megoldás.

Általánosan kb. így néz ki:

=ÖSSZEG((fejléc="A")  *  (Első oszlop="A") * (adatok))

A fenti kép alapján konkrétan, még mindig csak az “A” kategóriára

=SUMPRODUCT(($B$3:$B$12="A")*($C$2:$J$2="A")*($C$3:$J$12))

A SUMPRODUCT (SZORZATÖSSZEG) remek függvény. Kiválón alkalmas hasonló módon több feltétel szerinti összegzésre. Amíg nem volt 2007-es Excel, és nem nem volt SUMIFS (DARABHATÖBB), addig így lehetett csak hasonló problémákat megoldani.

Hogy működik?

Hát, ezt írásban meglehetősen hosszadalmas lenne elmagyarázni. Inkább nézd meg a videót. Abban elmondom, hogy mi a működés alapja, és megnézzük az összegzésen kívül a megszámlálást is.

Gyakorló munkafüzet: 2D-felt-osszegzes-megszamlalas

  • Két dimenziós összesítés

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