ö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 minket 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 oszlopában álló, számokat összeadja. A csoport-összegek a „részösszegek”, a teljes szám-oszlop összege pedig a „végösszeg”. A csoportosí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 akarom, 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 rendeznünk kell a táblázatot a csoportosító oszlop adatai alapján!
É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 csoportosító oszlopot a Csoportosító alap, míg a statisztikai oszlopot az Összegzendő oszlopok mezőben kell kiválasztanunk. A statisztikai 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 vezérlőjére majd visszatérünk, de most lássuk a feldolgozott táblázatot!
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 megjelení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 besegíthetü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övetkeztethetü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 ellipszisek, 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 fogalmazva, az oszlopok csoportosítás és a rendezés szerinti rangsora legyen azonos!
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áblázatban 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 megjelenítjük a modul 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.
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öbbszintes rendezéssel elő kell készíteni a művelethez. Az oszlopok csoportosítási és rendezési rangsorának azonosnak kell lennie! Az első munkamenetet követő munkamenetekben a Részösszegek lecserélése vezérlő kijelöletlen, mert így deklaráljuk az újabb csoportosí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” oszlop 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 erre 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 darabszámok és az előlegek összegét alakzatok szerint.
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 szempont 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 oszlopot, 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 nevé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övidítésével is. Akkor először vegyük számba az összes azonosítási lehetőséget!
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éplettel képezzük az azonosítókat!
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örtö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 argumentuma 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összeges táblázat jó áttekinthetősége elsődleges szempont, ezért a létrehozott oszlopok álljanak mindig a táblázat elején! Lássunk egy példát éves, és azon belül negyedéves részösszeges táblázatra!
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állí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észletek megjelení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 visszakapcsolni, a nem profik meg egyszerűen az Adatok, Tagolás, Részletek megjelenítése paranccsal tudnak operálni.
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űvelet 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állítani.
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! - mondja Pista 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-összegeket 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…
margitfalvi.arpad@gmail.com
