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

  • a DARABTELI függvényt és feltételeit (9.1);
  • a SZUM-ot (5.2);
  • a kiadási naplót (3.9);
  • a szűrés és a SZUM viszonyát (7.3).

Két alak

AlakMikor?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:

CellaKépletMagyará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ó

  1. 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).
  2. Készíts kategóriatáblát SZUMHA-val (3. rész), és ellenőrizd a végösszeggel.
  3. Mennyit költöttél az 1000 Ft-nál drágább tételekre (az egyoszlopos alak)?
  4. Hány tétel volt kategóriánként (DARABTELI), és mennyi volt egy tétel átlagos ára kategóriánként (SZUMHA / DARABTELI)?
  5. Készíts a kategóriák összegéből kördiagramot (6.4).

♻️ 2. Papírgyűjtés naplóként

  1. 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.
  2. Számold ki SZUMHA-val osztályonként a gyűjtött mennyiséget, és rendezd sorba (7.2).
  3. 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.