összeadás az Excelben - csoportok
2020-10-07    Excel 2019
Ebben a cikkben a „Részösszegek” statisztikai eszközről mesélek. A nevével ellentétben, ez a modul sem csak összeadni tud, de min­ket most más funkciói nem érdekelnek.
    A modul egy táblázat sorait csoportosítja egyik oszlopának adatai alapján, majd az egy csoportba került, a táblázat egy másik osz­lo­pá­ban álló, számokat összeadja. A csoport-összegek a „részösszegek”, a teljes szám-oszlop összege pedig a „végösszeg”. A cso­por­to­sí­tás alapjául szolgáló oszlop neve „csoportosító oszlop”, míg az összeadandó számokat tartalmazóé „statisztikai oszlop”. A modul a rész- és végösszegeket a táblázatban, feliratozva helyezi el.
    Nézzük ezt a gyakorlatban! Van egy táblázatom, amely különböző anyagú alakzatok dátumát és darabszámát tartalmazza. Tudni aka­rom, hány darab van az egyes alakzatokból, anyaguktól és dátumuktól függetlenül. Mielőtt megjelenítenénk a modul parancstábláját ren­dez­nünk kell a táblázatot a csoportosító oszlop adatai alapján!
a táblázat rendezése a csoportosító oszlop adatai alapján a részösszegek beszúrása előtt
És csak ezután jöhet: Adatok, Tagolás, Részösszeg! Ügyeljünk arra, hogy a rendezést követően az aktív cella maradjon a táblázatban, mert ha nem, a parancstábla helyett hibaüzenetet kapunk!
a Részösszegek parancstábla
A csoportosító oszlopot a Csoportosító alap, míg a statisztikai oszlopot az Összegzendő oszlopok mezőben kell kiválasztanunk. A sta­tisz­ti­kai műveletet, a mi esetünkben az összeadást, a Melyik függvénnyel lista Összeg elemével kérhetjük. A parancstábla többi ve­zér­lő­jé­re majd visszatérünk, de most lássuk a feldolgozott táblázatot!
a részösszegekkel tagolt táblázat, a megjelenítést szabályzó vezérlőkkel
A csoport-képzés illetve a beszúrt részösszegek rontják az adatok áttekinthetőségét, ezt ellensúlyozandó a modul lehetőséget teremt a meg­je­le­ní­tés egyszerűsítésére. A munkalap bal felső sarkán elhelyezett vezérlőkkel szabályozhatjuk az adatok részletezettségét: [1] csak a végösszeg sora látszik, [2] a részösszegek sorai és a végösszeg sora látszik, [3] a teljes rész- és végösszeges táblázat látszik. A kereten elhelyezett +/- vezérlőkkel az adott csoport sorait tudjuk elrejteni illetve megjeleníteni. Persze az áttekinthetőség javításában mi is be­se­gít­he­tünk egy kicsit. Én például szürke háttérszínnel és fehér belső szegéllyel különítettem el az alap-adatokat az összegektől.
    Az Összesítés parancstábla Csoportosító alap vezérlője egy lista, amelyen csak egyetlen oszlopot tudunk kiválasztani. Ebből arra kö­vet­kez­tet­he­tünk, hogy a modul nem képes többszintű csoportosításra. Egy munkamenetben valóban nem, de kettőben már igen.
    Nézzük hogyan! Például, nem csak az azonos alakzatok (ellipszisek) darabszámára, de azon belül az azonos anyagúak (fém el­lip­szi­sek, papír ellipszisek, üveg ellipszisek) darabszámára is kíváncsiak vagyunk! Természetesen a többszintű csoportosítás többoszlopos rendezést igényel! És a rendező oszlopok hierarchiájának meg kell egyeznie a csoportosító mezők hierarchiájával! Vagy másként fo­gal­maz­va, az osz­lo­pok csoportosítás és a rendezés szerinti rangsora legyen azonos!
többoszlopos csoportosítást megelőző többoszlopos rendezés
A képen a többoszlopos rendezés parancstáblás végrehajtását látjuk. De lehet így is: belekattintunk az „anyag” oszlopba és rendezés A-tól Z-ig, azután belekattintunk az „alakzat” oszlopba és megint rendezés A-tól Z-ig. Tehát a kattingatós többoszlopos rendezés a táb­lá­zat­ban visszafelé halad!
    Megjelenítjük a modul parancstábláját és beállítunk egy alakzatok szerinti csoport- és egy darabszám szerinti részösszeg-képzést. Ezt egyszer már megcsináltuk, de most a többszintű rendezés miatt, más a sorok sorrendje a táblázatban! OK. Majd ismét meg­je­le­nít­jük a mo­dul parancstábláját. Beállítjuk az anyag szerinti csoportosítást és töröljük a pipát a Részösszegek lecserélése jelölőnégyzetből! És újra OK.
többoszlopos csoportosítás
Tehát, a többoszlopos csoportosítás végrehajtása csak több munkamenetben lehetséges. Az első munkamenet előtt a táblázatot több­szin­tes rendezéssel elő kell készíteni a művelethez. Az oszlopok csoportosítási és rendezési rangsorának azonosnak kell lennie! Az el­ső mun­ka­me­ne­tet követő munkamenetekben a Részösszegek lecserélése vezérlő kijelöletlen, mert így deklaráljuk az újabb cso­por­to­sí­tó oszlopot.
    Az oszlopok csoportosítás szerinti hierarchiával azonos sorrendje a táblázatban nem előírás, de az adatok jó áttekinthetősége miatt erő­sen ajánlott! Magyarul, ha anyag szerint és azon belül alakzat szerint kívánjuk a darabszámok összegét megállapítani, akkor az „anyag” osz­lop előzze meg a táblázatban az „alakzat” oszlopot!
    A modul parancstábláján az Összegzendő oszlopok jelölőnégyzetes vezérlővel több statisztikai mezőt is deklarálhatunk. Nézzünk er­re is egy példát! Az eredeti táblázatunkból töröltem az „anyag” oszlopot és felvettem helyette az „előleg”-et. Tudni szeretnénk a da­rab­szá­mok és az előlegek összegét alakzatok szerint.
részösszegek több statisztikai oszlop esetén
Mindez nagyon szép! De hol van a dátum-egységek szerinti csoportosítás lehetősége? Mert az szinte a legfontosabb csoportosítási szem­pont a mindennapokban. Hát az sehol! De egy kis plusz munkával megteremthetjük a lehetőségét. Létre kell hoznunk egy szá­mí­tott osz­lo­pot, amely tartalmazza a csoportosító dátum-egység adatait. Például, ha évek szerinti részösszegeket akarunk, akkor létre kell hoznunk egy „évek” oszlopot, az =ÉV( dátum ) képlettel.
    A fenti képlet az évek sorszámát, más szóval indexét adja eredményül. De más dátum-egységet nem csak a sorszámával, hanem a ne­vé­vel is szokás azonosítani. Például, az év hónapjait vagy a hét napjait. Sőt a neves dátum-egység azonosítás történhet a név rö­vi­dí­té­sé­vel is. Akkor először vegyük számba az összes azonosítási lehetőséget!
csoportosító dátum-egységek és azonosítóik
Az azonosító oszlopban álló adatok tizennégy dátum-csoportosítási lehetőséget jelentenek a részösszeges táblázatban! Nézzük milyen kép­let­tel képezzük az azonosítókat!
csoportosító dátum-egységek értékeinek kiszámítása
A HÉT.SZÁMA függvény 21-es második argumentuma az EU szabvány szerinti számolást deklarálja: az év első hete az év első csü­tör­tö­két tartalmazó hét. A hét napjainak sorszámozása egyessel (1) kezdődik és ezt a hétfő kapja, ha a HÉT.NAPJA függvény második ar­gu­men­tu­ma 2. Többszintű dátum-csoportosítást is alkalmazhatunk, de akkor létre kell hoznunk az összes dátum-egység oszlopot. A rész­össze­ges táb­lá­zat jó áttekinthetősége elsődleges szempont, ezért a létrehozott oszlopok álljanak mindig a táb­lá­zat elején! Lássunk egy példát éves, és azon belül negyedéves részösszeges táblázatra!
részösszegek dátum-egységek szerinti csoportosításban
Hogy hol álljanak a rész- és végösszegek: az összegzendő adatok alatt vagy felett? Ízlés dolga. A szokásos beállításokkal az adatok alatt. De ha nem így akarjuk, akkor töröljük a pipát az Összeg az adatok alatt jelölőnégyzetből!
    Ha adatvédelmi okokból a részletek nem nyilvánosak, akkor az adatok részletezettségét szabályzó vezérlőket elrejthetjük a Be­ál­lí­tá­sok, Speciális, E munkalap megjelenítési beállításai, Tagoló szimbólumok megjelenítése… jelölőnégyzettel. Ne felejtsük el, hogy a rész­le­tek meg­je­le­ní­té­sé­nek teljes tiltása csak a lapvédelemmel együtt lehetséges, mert hiába rejtjük el a vezérlőket, a profik tudják, hol kell őket vissza­kap­csol­ni, a nem profik meg egyszerűen az Adatok, Tagolás, Részletek megjelenítése paranccsal tudnak operálni.
a megjelenítés részletezettségét szabályzó vezérlők elrejtése
Az aktív táblázat csoportosításait, rész- és végösszegeit a modul parancstáblájának Az összes eltávolítása vezérlőjével törölhetjük. A mű­ve­let a rendezett táblázatot adja vissza. Az eredeti sorrendet csak a rendezés előtt létrehozott sor-számozó segédoszloppal tudjuk vissza­ál­lí­ta­ni.
    A profikban felmerülhet a kérdés, ezeket a műveleteket miért nem a program Pivot táblázat eszközével végeztettjük el, hiszen annak a modulnak a szolgáltatás-készlete sokkal szélesebb és összetettebb, mint az Részösszegek modulé. Számos oka van annak! - mond­ja Pis­ta bácsi. Mert éppen az összetettsége miatt a Pivot táblázat használatának elsajátítása nem kis teljesítmény. Mert a csoport-össze­ge­ket a forrás-táblázatban kívánom elhelyezni és nem egy külön táblázatban. Mert ágyúval nem lövünk verébre…
  ismertető letöltése pdf-ben   munkafüzet letöltése
margitfalvi.arpad@gmail.com