Excel táblázatkezelés kezdőknek › 9.7 lecke

Excel tanfolyam · 9.7 lecke

Részösszegek és szűrt adatok

A 7.3-ban megláttuk: ha leszűröd a listát, a SZUM továbbra is az összes sort adja össze. Mi van, ha éppen azt szeretnéd, hogy az összeg a szűréshez igazodjon? A RÉSZÖSSZEG függvény csak a látható sorokkal számol. Ebben a leckében megismered, és mellette az Adatok lap Részösszeg parancsát is, amely egy listát csoportonként összesít, akár egy kattintással.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 10. osztálytól

A lecke végére

  • RÉSZÖSSZEG-gel csak a látható sorokat összesíted;
  • a függvénykódok közül a megfelelőt választod;
  • megkülönbözteted a kiszűrt és a kézzel elrejtett sorokat;
  • ÖSSZESÍT-tel a hibaértékeket is mellőzöd;
  • a Részösszeg paranccsal csoportonként összesítesz.

Amit már tudsz

  • a szűrést és a SZUM viselkedését szűrt listán (7.3);
  • a rendezést (7.2);
  • a sorok elrejtését (2.5);
  • a SZUMHA-t (9.2).

A RÉSZÖSSZEG

RÉSZÖSSZEG(függv_szám;hiv1;…): az első argumentum egy kód, amely megmondja, milyen számítás kell; a második a tartomány.

KódKód (rejtett sorok nélkül)Számítás
1101ÁTLAG
2102DARAB
3103DARAB2
4104MAX
5105MIN
9109SZUM
  • A kiszűrt sorokat a RÉSZÖSSZEG mindig kihagyja, akár 9-es, akár 109-es kódot használsz.
  • Ha egy tartományban más RÉSZÖSSZEG képletek is vannak, azokat nem számolja bele: így nem keletkezik kétszeres összeg.
  • Gyorsan: ha egy szűrt lista alá az AutoSzum gombbal (5.2) összeget szúrsz be, az Excel magától RÉSZÖSSZEG(9;…) képletet ír.

Kiszűrt vagy elrejtett?

🔮 Jósold meg!

A büfélista bevétel oszlopa (D2:D100) összesen 48 200 Ft. (a) Leszűröd a listát a „csokoládé” termékre (bevétel: 6300 Ft). (b) Szűrés nélkül kézzel elrejtesz néhány sort (jobb kattintás → Elrejtés), amelyek bevétele 5000 Ft. Mit mutat a két esetben a SZUM(D2:D100), a RÉSZÖSSZEG(9;D2:D100) és a RÉSZÖSSZEG(109;D2:D100)?

Megoldás

(a) Szűrés: SZUM: 48 200 (mindent összead); RÉSZÖSSZEG(9): 6300; RÉSZÖSSZEG(109): 6300. A kiszűrt sorokat mindkét kód kihagyja. (b) Kézi elrejtés: SZUM: 48 200; RÉSZÖSSZEG(9): 48 200 (a kézzel elrejtett sorokat a 9-es kód beleszámolja!); RÉSZÖSSZEG(109): 43 200 (a 100 feletti kód a kézzel elrejtett sorokat is kihagyja). Ha azt szeretnéd, hogy az összeg mindig csak a látható sorokat mutassa, a 109 a biztos választás.

Az ÖSSZESÍT

Az ÖSSZESÍT(függv_szám;beállítások;tartomány) a RÉSZÖSSZEG „nagytestvére”: több számítást tud (például NAGY, KICSI, MEDIÁN), és a beállítások argumentummal azt is megadhatod, mit hagyjon ki:

BeállításMit hagy ki?
5a rejtett sorokat
6a hibaértékeket
7a rejtett sorokat és a hibaértékeket

Például: =ÖSSZESÍT(9;6;D2:D100) összeadja a bevételeket akkor is, ha néhány cellában #ÉRTÉK! hiba van (a SZUM ilyenkor maga is hibát adna). A függvényszámok a SZUM esetén ugyanazok, mint a RÉSZÖSSZEG-nél (9 = SZUM, 1 = ÁTLAG, 4 = MAX…).

A Részösszeg parancs

  1. Rendezd a listát a csoportosító mező szerint (például Osztály, 7.2). Rendezés nélkül minden „váltásnál” új csoport kezdődik!
  2. Kattints a listába → Adatok → Tagolás → Részösszeg.
  3. Állítsd be: Csoportosítási alap: Osztály; Függvény: Összeg (vagy Átlag, Darab…); Összegzendő oszlopok: például Kilogramm. OK.
  4. Az Excel minden csoport után egy „… Összeg” sort szúr be (RÉSZÖSSZEG képlettel), a végére pedig egy „Végösszeg” sort.
  5. A bal szélen megjelennek a tagolási gombok: 1 = csak a végösszeg, 2 = csoportösszegek, 3 = minden sor.
  6. Eltávolítás: ugyanebben az ablakban az Összes eltávolítása gomb.

💡 Részösszeg parancs vagy SZUMHA?

A Részösszeg parancs gyors áttekintést ad egy rendezett listán, de beleírja a sorokat a listába. Ha az összesítésnek a lista mellett, külön kell állnia, vagy a lista gyakran változik, a SZUMHA-s kategóriatábla (9.2) vagy a kimutatás (14.6) a jobb választás.

Kihívás: a büfélista többféle összesítése

🧾 Szűrés, rejtés, csoportosítás

  1. Készíts egy kitalált, legalább 30 soros büfénaplót: Dátum, Termék, Darab, Bevétel (Ft).
  2. A lista alá írj három összeget: SZUM, RÉSZÖSSZEG(9;…), RÉSZÖSSZEG(109;…).
  3. Szűrd a listát egy termékre: melyik összeg változik? Rejts el kézzel két sort: most melyik?
  4. Számold ki RÉSZÖSSZEG-gel a látható tételek átlagát (1 vagy 101) és darabszámát (2 vagy 102) is.
  5. Az egyik bevételcellába írj szándékosan hibás képletet (például =1/0). Mit mutat a SZUM, és mit az ÖSSZESÍT(9;6;…)? Utána javítsd a hibát!
  6. Másolatot készíts a lapról, rendezd a listát Termék szerint, és használd a Részösszeg parancsot a bevételek termékenkénti összegére. Kattints a 2-es tagolási gombra: mi látszik?
  7. Hasonlítsd össze az eredményt egy SZUMHA-s kategóriatáblával (9.2): ugyanazok az összegek?

Hibavadászat

🐞 1. Peti végtelen részösszegei

Peti a büfénaplón a Részösszeg parancsot használta termékenként, de a lista nem volt rendezve. Az eredmény: majdnem minden sor után egy újabb „… Összeg” sor.

Mi a megoldás?

A Részösszeg parancs minden olyan helyen új csoportot kezd, ahol a csoportosító mező értéke megváltozik (4. rész, 1. lépés). Peti távolítsa el a részösszegeket (Összes eltávolítása), rendezze a listát Termék szerint (7.2), és csak utána futtassa újra a parancsot.

🐞 2. Lili kétszeres végösszege

Lili a Részösszeg parancs után a Bevétel oszlop aljára még egy saját =SZUM(D2:D45) képletet írt. A kapott összeg pontosan kétszerese a valódinak.

Mit tegyen?

A SZUM a csoportösszeg-sorokat is összeadja, így minden tétel kétszer kerül bele: egyszer önmagában, egyszer a csoportösszegben. A RÉSZÖSSZEG viszont kihagyja a többi RÉSZÖSSZEG-et (1. rész): Lili használja a =RÉSZÖSSZEG(9;D2:D45) képletet, vagy egyszerűen a parancs által létrehozott Végösszeg sort.

Összefoglalás

  • RÉSZÖSSZEG(kód;tartomány): csak a látható (nem kiszűrt) sorokkal számol.
  • Kódok: 9 = SZUM, 1 = ÁTLAG, 2 = DARAB, 3 = DARAB2, 4 = MAX, 5 = MIN; +100: a kézzel elrejtett sorokat is kihagyja.
  • A RÉSZÖSSZEG nem számolja kétszer a többi RÉSZÖSSZEG-et; szűrt listán az AutoSzum magától ezt szúrja be.
  • ÖSSZESÍT: a hibaértékeket is mellőzheti (6, 7) – óvatosan.
  • Adatok → Részösszeg: rendezés után, csoportonkénti összeg, tagolási szintek.

Ellenőrizd magad!

Melyik kód adja a látható sorok átlagát, a kézzel elrejtetteket is kihagyva?

101

Mit kell tenni a Részösszeg parancs előtt?

Rendezni a listát a csoportosító mező szerint.

Miért kétszeres a SZUM egy részösszegekkel tagolt oszlopon?

Mert a csoportösszeg-sorokat is összeadja.

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés és Adatbázis-kezelés táblázatkezelővel (szűrés, csoportosítás, összesítés), nagy adathalmazok kezelése; előkészíti az adatbázis-kezelés csoportosító lekérdezéseit (GROUP BY).

Módszertan

A lecke lezárja a 7.3-ban nyitott kérdést (a SZUM szűrés utáni viselkedése). A jóslás a kiszűrt és a kézzel elrejtett sorok különbségét, valamint a kódok (9 és 109) szerepét számszerű példán mutatja meg. A Részösszeg parancs és a SZUMHA-s kategóriatábla összevetése a megfelelő eszköz kiválasztására nevel. Lili esete a kétszeres számolás veszélyét és a RÉSZÖSSZEG „önkizáró” tulajdonságát teszi tudatossá.

Időterv

A RÉSZÖSSZEG: 7 perc. Kiszűrt vagy elrejtett és jóslás: 8 perc. Az ÖSSZESÍT: 5 perc. A Részösszeg parancs: 8 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 9.8 Mini projekt: büféforgalom elemzése

A 9. fejezet zárása: egy havi büfénapló elemzése feltételes összesítéssel, vezérlőpulttal és diagrammal.