Telefonszámunk: 1-472-0679

Összesítés szűrt és rejtett tartományokban

2015-02-14 - horvimi - Megtekintések száma: 4,185 - Kategória: Függvények
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013

Ha egy tartományban bizonyos oszlopokban összesítő műveleteket szeretnénk végezni, (szumma, Átlag, Max, Min, Darab, stb), de a sorokat szűrjük, vagy sorokat elrejtünk, akkor a hagyományos összesítő  függvények nem lesznek jók, mert azok az eredménybe beleszámítják az éppen nem látható sorokat is.

A RÉSZÖSSZEG – SUBTOTAL függvény

A RÉSZÖSSZEG vagy angol nevén a SUBTOTAL függvény képes arra, hogy figyelmen kívül hagyja az éppen nem látható sorokat. Ezen belül különbséget tehetünk a szűréssel eltüntetett sorok, és az egyéb módon, pl. csoportosítással vagy kézzel elrejtett sorok számítása között.

=RÉSZÖSSZEG(függvénykód;Tartomány)

A függvénykód argumentummal adhatjuk meg, hogy milyen összesítő függvényt alkalmazzon, a második argumentumként megadott tartományon. A teljes listát olvassátok el a függvény súgójában, vagy a Neten, itt most csak néhányat sorolok fel.

 

Függvénykód
(a rejtett értékek is)
Függvénykód
(a rejtett értékek kihagyása)
Függvény
1 101 ÁTLAG
2 102 DARAB
3 103 DARAB2
4 104 MAX
5 105 MIN
9 109 SZUM

A táblázatot úgy kell értelmezni, hogy a százzal kezdődő függvénykódok az összegzésbe nem veszik bele az elrejtett sorokat.

Nem számít elrejtésnek az autoszűrő, emiatt ha autoszűrővel szűrt tartományt összesítünk, akkor mindkét verzió ugyanazt az eredményt fogja adni.

Lássuk a példát

Részösszeg-példa

Az alap táblázat Név oszlopában megszámlálunk a DARAB2( ) függvénnyel, és a fizetés oszlopban pedig összesíteni akarunk a SZUM( ) függvénnyel.

Szűrés

Látható, hogy szűrés nélkül mindhárom változat ugyanazt az eredményt adja. Nézzük meg, hogy mi lesz, ha leszűrjük csak  a Férfiakra:

Részösszeg-szűrés

Az első összesítő sorban a sima SZUM továbbra is mind a 13 sort összegzi, és ugyanezt teszi a DARAB2 is. A Részösszeg a normál és a százas függvénykódokkal egyformán működik, azaz csak a látható sorokat összegzi.

Elrejtés

Végül lássuk mi történik, ha elrejtünk néhány sort. Most a csoportosítás funkciót használtam, hogy elrejtsem a 8-11. sorokat:

Részösszeg-elrejtés

Így a normál és a százas függvények eltérő eredményt adtak, mert az elrejtett sorokat csak a százas függvények hagyják figyelmen kívül.

A Résszösszegek elhelyezése és a tartomány

Ha a táblázatunk több sort tartalmaz, mint amennyi a képernyőn elfér, akkor javasolt a részösszegzést inkább a tartomány fölé elhelyezni, és így azonnal, görgetés nélkül látjuk a szűrés eredményét.

További infók

A RÉSZÖSSZEG függvény a megadott tartományban lévő esetleges korábbi RÉSZÖSSZEG függvényeket is figyelmen kívül hagyja.

Ha a RÉSZÖSSZEG sort mégis a tartomány aljára tesszük, akkor előtte hagyjunk egy üres sort, különben a szűrő bekapcsolásakor a Részösszeg sor eltűnhet.

Ugyanerre a feladatra használható az ÖSSZESÍT ( AGGREAGATE ) függvény. Erről készült már korábban egy kis összeállítás.

És lássuk a Videót

  • A RÉSZÖSSZEG függvény munka közben

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