Excel táblázatkezelés kezdőknek › 9.2 lecke
Excel tanfolyam · 9.2 lecke
SZUMHA: egy feltétel szerinti összeg
Mennyit költöttél a hónapban ennivalóra? Hány kilót gyűjtött a 9. b? Mennyi volt a büfé csokoládéeladása? A SZUM mindent összead, a SZUMHA csak azokat az értékeket, amelyek mellett a feltétel teljesül. Ez az a függvény, amely a 7.3-ban a szűrés után „nem változó” SZUM helyett a jó választ adja.
- ⏱ kb. 45 perc
- 🎯 kezdőknek
- 🎒 10. osztálytól
A lecke végére
- a SZUMHA mindkét alakját használod;
- megkülönbözteted a feltételtartományt és az összegtartományt;
- kategóriánkénti összesítő táblázatot készítesz;
- ellenőrzöd az összesítést a végösszeggel;
- elkerülöd az elcsúszott tartományokat.
Amit már tudsz
Két alak
| Alak | Mikor? | Példa |
|---|---|---|
SZUMHA(tartomány;feltétel) | a feltétel ugyanarra az oszlopra vonatkozik, amelyet összeadsz | =SZUMHA(D2:D40;">=1000"): az 1000 Ft-os vagy nagyobb tételek összege |
SZUMHA(feltétel_tartomány;feltétel;összeg_tartomány) | a feltétel egy másik oszlopban van, mint az összegzendő érték | =SZUMHA(C2:C40;"ennivaló";D2:D40): az „ennivaló” kategóriájú tételek összege |
Mondd ki szavakkal (4.1): „Add össze a D oszlop azon értékeit, amelyek sorában a C oszlopban »ennivaló« áll.” A feltétel ugyanúgy írható, mint a DARABTELI-nél (9.1): szöveg, szám, ">=40", "<>egyéb".
💡 Párban a DARABTELI-vel
A két függvény gyakran együtt jár: =SZUMHA(C2:C40;"ennivaló";D2:D40)/DARABTELI(C2:C40;"ennivaló") az ennivalóra fordított tételek átlaga. (Erre egy saját függvény is van: ÁTLAGHA, 9.3.)
Tartományok párban
A feltételtartomány és az összegtartomány soronként párban áll: a C2 feltétele a D2-re vonatkozik, a C3-é a D3-ra, és így tovább. Ezért a két tartománynak ugyanabban a sorban kell kezdődnie, és ugyanolyan hosszúnak kell lennie.
🔮 Jósold meg!
Lili képlete: =SZUMHA(C2:C40;"ennivaló";D3:D41). A feltételtartomány a 2., az összegtartomány a 3. sorban kezdődik. Lesz hibaüzenet? Jó lesz az eredmény?
Megoldás
Hibaüzenet nem lesz, de az eredmény rossz: minden „ennivaló” sor helyett az alatta lévő sor összegét adja össze, mert a C2-höz a D3, a C3-hoz a D4 tartozik. Az ilyen hiba alattomos, mert a szám valószerűnek tűnik. Ellenőrizd a tartományokat a szerkesztőlécen (a színes keretek egymás mellett kezdődnek-e, 4.1), vagy szűréssel (3. rész).
Kategóriatábla
A kiadási napló kategóriáit egy oszlopba írva (G2: ennivaló, G3: mozi, G4: könyv, G5: egyéb) egyetlen képlet tölti ki a teljes összesítőt:
| Cella | Képlet | Magyarázat |
|---|---|---|
| H2 (lefelé másolva) | =SZUMHA($C$2:$C$40;G2;$D$2:$D$40) | a tartományok rögzítve, a feltétel (G2, G3…) vándorol (4.5) |
| H6 | =SZUM(H2:H5) | a kategóriák összege |
| H7 (ellenőrzés) | =H6=SZUM($D$2:$D$40) | IGAZ, ha minden tétel valamelyik kategóriába került (8.1) |
✅ Ha az ellenőrzés HAMIS
Valamelyik tétel kategóriája el van írva (például „ennivalo”, „Ennivaló ” szóközzel, 7.6), vagy hiányzik, és ezért egyik sorba sem került. Szűrd a Kategória oszlopot, és nézd meg, milyen értékek vannak benne: ott lesz a hiba.
Kihívás: hová megy a pénz, ki gyűjtött többet?
💰 1. Kiadási napló
- Vedd elő a 3.9-ben készült kiadási naplót (vagy készíts egy kitalált, 25 tételes listát: Dátum, Tétel, Kategória, Összeg).
- Készíts kategóriatáblát SZUMHA-val (3. rész), és ellenőrizd a végösszeggel.
- Mennyit költöttél az 1000 Ft-nál drágább tételekre (az egyoszlopos alak)?
- Hány tétel volt kategóriánként (DARABTELI), és mennyi volt egy tétel átlagos ára kategóriánként (SZUMHA / DARABTELI)?
- Készíts a kategóriák összegéből kördiagramot (6.4).
♻️ 2. Papírgyűjtés naplóként
- A papírgyűjtés adatait most „napló” formában rögzítsd: minden leadás egy sor (Dátum, Osztály, Kilogramm), összesen legalább 20 sor, az osztályok vegyesen.
- Számold ki SZUMHA-val osztályonként a gyűjtött mennyiséget, és rendezd sorba (7.2).
- Hasonlítsd össze az 5.2-es táblázattal: melyik szerkezet a jobb, ha minden nap újabb leadások jönnek? (Tipp: a napló egy szabályos adatlista, 7.1.)
Hibavadászat
🐞 1. Peti felcserélt argumentumai
Peti képlete: =SZUMHA(D2:D40;"ennivaló";C2:C40). Az eredmény 0.
Mi a megoldás?
Peti felcserélte a tartományokat: a feltételt az Összeg oszlopban kereste (ahol nincs „ennivaló”), és a Kategória oszlopot adta volna össze (ahol szövegek vannak). A sorrend: először ahol a feltételt keresed, aztán a feltétel, végül amit összeadsz: =SZUMHA(C2:C40;"ennivaló";D2:D40).
🐞 2. Lili hiányzó forintjai
Lili importált listájában az ennivaló összege a SZUMHA szerint 3200 Ft, pedig szemmel összeadva legalább 5000 Ft. A hiányzó tételek összegei balra igazodnak.
Mit tegyen?
A balra igazított összegek szövegként vannak tárolva (2.8), és a SZUMHA, mint a SZUM, kihagyja őket. Lili alakítsa számmá az Összeg oszlopot (7.6), és az eredmény helyes lesz. Ellenőrzésként a DARAB és a DARAB2 eltérése is jelzi a gondot (5.4).
Összefoglalás
- =SZUMHA(tartomány;feltétel): az ugyanabban az oszlopban feltételnek megfelelő értékek összege.
- =SZUMHA(feltétel_tartomány;feltétel;összeg_tartomány): a feltétel egy másik oszlopban.
- A két tartomány ugyanabban a sorban kezdődjön, és ugyanolyan hosszú legyen.
- Kategóriatábla: rögzített tartományok, vándorló feltételcella; ellenőrzés a végösszeggel.
- A szövegként tárolt számokat a SZUMHA kihagyja.
Ellenőrizd magad!
Mi a SZUMHA harmadik argumentuma?
Az összegtartomány: azok az értékek, amelyeket összead.
Hogyan adod össze a B2:B30 azon értékeit, amelyek 100-nál kisebbek?
=SZUMHA(B2:B30;"<100")
Miért veszélyes, ha a két tartomány nem ugyanabban a sorban kezdődik?
Mert a sorok elcsúsznak egymáshoz képest, és hibaüzenet nélkül rossz eredmény születik.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (feltételtől függő számítások, statisztikai függvények), adatok csoportosítása és elemzése; kapcsolódás a programozáshoz (összegzés típusalgoritmus feltétellel) és a pénzügyi tudatossághoz (kiadások kategóriánként).
Módszertan
A lecke a SZUMHA két alakját a mondatba foglalt jelentésen keresztül tanítja. A jóslás a leggyakoribb, hibaüzenet nélküli hibát (elcsúszott tartomány) teszi tudatossá. A kategóriatábla és az IGAZ/HAMIS ellenőrzés átvihető önellenőrzési szokást ad, amely egyben adatminőségi hibákat is felfed (7.6). A papírgyűjtési napló a „széles” és a „hosszú” (napló) adatszerkezet összevetésével előkészíti a kimutatásokat (14.6).
Időterv
Két alak: 8 perc. Tartományok párban és jóslás: 7 perc. Kategóriatábla: 8 perc. Kihívás: 18 perc. Hibavadászat: 4 perc.
Következő lecke: 9.3 ÁTLAGHA: feltételes átlag
A 9. b átlaga, az angolosok átlagpontszáma: feltételes átlag egy függvénnyel, és mi történik, ha nincs egy megfelelő érték sem.
