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 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ód | Kód (rejtett sorok nélkül) | Számítás |
|---|---|---|
| 1 | 101 | ÁTLAG |
| 2 | 102 | DARAB |
| 3 | 103 | DARAB2 |
| 4 | 104 | MAX |
| 5 | 105 | MIN |
| 9 | 109 | SZUM |
- 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ás | Mit hagy ki? |
|---|---|
| 5 | a rejtett sorokat |
| 6 | a hibaértékeket |
| 7 | a 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…).
⚠️ Ne a hibát rejtsd el!
A hibaértékek mellőzése kényelmes, de ugyanaz igaz rá, mint a HAHIBA-ra (8.6): a valódi hibákat is eltakarhatja. Előbb nézd meg, miért van hiba a cellákban.
A Részösszeg parancs
- 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!
- Kattints a listába → Adatok → Tagolás → Részösszeg.
- Állítsd be: Csoportosítási alap: Osztály; Függvény: Összeg (vagy Átlag, Darab…); Összegzendő oszlopok: például Kilogramm. OK.
- 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.
- A bal szélen megjelennek a tagolási gombok: 1 = csak a végösszeg, 2 = csoportösszegek, 3 = minden sor.
- 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
- Készíts egy kitalált, legalább 30 soros büfénaplót: Dátum, Termék, Darab, Bevétel (Ft).
- A lista alá írj három összeget:
SZUM,RÉSZÖSSZEG(9;…),RÉSZÖSSZEG(109;…). - Szűrd a listát egy termékre: melyik összeg változik? Rejts el kézzel két sort: most melyik?
- 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.
- 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! - 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?
- 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.
