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

Excel tanfolyam · 12.4 lecke · emelt szint

AB.SZUM és AB.ÁTLAG

A 12.3-ban egy egyszerű feltétellel (Osztály = 9.b) számoltunk. Az adatbázisfüggvények ereje azonban az összetett feltételekben van: több mező egyszerre, intervallum ugyanazon a mezőn, szöveg eleje vagy pontos egyezés, és olyan határ, amely egy bemeneti cellából jön. Ebben a leckében az AB.SZUM és az AB.ÁTLAG függvénnyel gyakorolod a feltételtartomány minden fajtáját, a 12.3 ellenőrizhető adatsorán.

  • ⏱ kb. 45 perc
  • 🎓 emelt szint
  • 🎒 11. osztálytól

A lecke végére

  • több mezőre vonatkozó ÉS feltételt adsz meg;
  • intervallumot adsz meg ugyanazon a mezőn;
  • szöveges feltételt adsz meg „kezdete” és pontos egyezéssel;
  • a feltételtartományba bemeneti cellából veszed a határt;
  • megismered a számított feltételt.

Amit már tudsz

  • az AB függvények szerkezetét és a példalistát (12.3);
  • a feltételtartomány szabályait (7.4);
  • a helyettesítő karaktereket (9.6);
  • a feltétel összefűzését cellával (9.5).

💡 A kiinduló adatok

A 12.3 listája: A1:D9 (Név, Osztály, Nem, Pontszám), elnevezve adatok néven. A pontszámok: Kiss Anna 42, Nagy Bence 38, Tóth Lili 45, Szabó Máté 29, Varga Zsófi 50, Kovács Péter 47, Horváth Dóra 33, Molnár Ádám 41 (összesen 325). A feltételtartományokat a lista mellé, az F oszloptól írd.

Több mező: ÉS

A 9. b-s lányok: két mezőnév egymás mellett, alattuk egy sorban a két feltétel (egy sor = ÉS, 7.4):

OsztályNem
9.blány

  • =AB.SZUM(adatok;"Pontszám";F1:G2) → 95 (Tóth Lili 45 + Varga Zsófi 50)
  • =AB.ÁTLAG(adatok;"Pontszám";F1:G2) → 47,5

Intervallum ugyanazon a mezőn

A 40 és 50 pont közöttiek (a határokat is beleértve): ugyanaz a mezőnév kétszer, egymás mellett, alattuk egy sorban a két határ:

PontszámPontszám
>=40<=50

  • =AB.SZUM(adatok;"Pontszám";I1:J2) → 225 (42 + 45 + 50 + 47 + 41)
  • =AB.ÁTLAG(adatok;"Pontszám";I1:J2) → 45

Szöveges feltételek

🔮 Jósold meg!

A feltételtartomány:

Név
K
Mennyi az AB.SZUM(adatok;"Pontszám";…)?

Megoldás

89: Kiss Anna (42) és Kovács Péter (47). A feltételtartományban a szöveges feltétel „ezzel kezdődik” jelentésű (7.4), ezért a „K” minden K-val kezdődő nevet megtalál. Ha a név pontosan „K” lenne, és csak azt keresnéd, pontos egyezés kellene.

Feltétel a Név alattJelentésAB.SZUM
KK-val kezdődik89
="=Kiss Anna" (a cellában így látszik: =Kiss Anna)pontosan egyezik42
*a„a”-ra végződik (9.6)75 (Kiss Anna 42 + Horváth Dóra 33)
<>9.b az Osztály alattnem 9. b-s151 (325 − 174)

💡 Miért fontos a pontos egyezés?

Ha egy osztály neve „9.b”, egy másiké „9.bny” (nyelvi előkészítő), a 9.b feltétel mindkettőt megtalálja, mert mindkettő „9.b”-vel kezdődik. Ilyenkor a pontos egyezés (="=9.b") a biztos.

Feltétel cellából

  • Határ bemeneti cellából: a H1 cellában a ponthatár (például 45). A feltételtartomány Pontszám alatti cellájába ne >=45-öt írj, hanem egy képletet: =">="&$H$1 (9.5). A cellában „>=45” jelenik meg, és ha a H1 változik, a feltétel is. Eredmény 45-nél: AB.SZUM = 142 (45 + 50 + 47), AB.ÁTLAG ≈ 47,33.
  • Legördülő lista a feltételben: az Osztály alatti feltételcellába adatérvényesítési listát tehetsz (9.5): így a felhasználó egy kattintással választhat osztályt, és az AB függvények azonnal frissülnek.

🎓 Kitekintés: számított feltétel

Néha a feltétel nem egy érték, hanem egy számítás: „az átlagnál többet elérők”. Ilyenkor a feltételtartomány fejléce ne egyezzen egyik mezőnévvel sem (például „Átlag felett”, vagy üres), alatta pedig egy képlet álljon, amely a lista első adatsorára hivatkozik relatívan: =D2>ÁTLAG($D$2:$D$9). Az Excel ezt minden rekordra kiértékeli. Eredmény: AB.SZUM = 225 (az átlag 40,625). Ez haladó technika; ha bizonytalan vagy, használj segédoszlopot (8.2).

Kihívás: feltételtartományok gyakorlása

🧮 Minden kérdéshez egy feltételtartomány

A 12.3 listáján (adatok) készíts minden kérdéshez külön feltételtartományt, és számold ki az AB.SZUM és az AB.ÁTLAG értékét. Utána ellenőrizd a megoldásokkal.

  1. A 9. a-s fiúk.
  2. A lányok közül azok, akik 40 pontnál többet értek el.
  3. A fiúk közül azok, akik 30 és 45 pont között teljesítettek (a határokat is beleértve).
  4. Egy bemeneti cellában (H1) állítsd a ponthatárt 40-re, egy legördülő listában (H2) az osztályt 9.b-re, és a feltételtartomány mindkettőt cellából vegye. Mennyi az eredmény? Változtasd meg a H2-t 9.c-re!
  5. Bónusz: számított feltétellel azok, akik az átlagnál kevesebbet értek el.
Megoldások
  1. Szabó Máté: összeg 29, átlag 29.
  2. Kiss Anna, Tóth Lili, Varga Zsófi: összeg 137, átlag ≈ 45,67.
  3. Nagy Bence, Molnár Ádám: összeg 79, átlag 39,5.
  4. 9. b, legalább 40: Tóth Lili, Varga Zsófi, Molnár Ádám: összeg 136, átlag ≈ 45,33. 9. c, legalább 40: Kovács Péter: 47.
  5. Az átlag (40,625) alattiak: Nagy Bence, Szabó Máté, Horváth Dóra: összeg 100, átlag ≈ 33,33.

Hibavadászat

🐞 1. Peti „mindenki” intervalluma

Peti a 40 és 50 közöttieket így kereste: a Pontszám alatt az első sorban >=40, a második sorban <=50. Az AB.SZUM 325 lett, minden versenyző pontszámának összege.

Mi a megoldás?

A két sor VAGY kapcsolatot jelent (7.4): „legalább 40 VAGY legfeljebb 50”, ami minden számra igaz. Az ÉS-hez a mezőnevet kétszer kell egymás mellé írni, és a két határt egy sorba (2. rész).

🐞 2. Lili nulla eredménye

Lili a feltételtartományba a Pontszám alá ezt gépelte: >=H1. Az AB.SZUM 0 lett.

Mit tegyen?

A „>=H1” szövegként került a cellába: az Excel a pontszámokat a „H1” szöveghez hasonlította (9.5). A határt képlettel kell összefűzni: =">="&$H$1 (4. rész). Ellenőrzés: a cellában „>=40” (a H1 aktuális értékével) jelenjen meg.

Összefoglalás

  • Több mező ÉS: mezőnevek egymás mellett, feltételek egy sorban.
  • Intervallum: ugyanaz a mezőnév kétszer, a két határ egy sorban.
  • Szöveges feltétel: „ezzel kezdődik”; pontos egyezés: ="=szöveg"; helyettesítő karakter: *, ?.
  • Határ cellából: =">="&$H$1 a feltételtartományban.
  • Számított feltétel: nem egyező fejléc, képlet az első adatsorra hivatkozva.

Ellenőrizd magad!

Hogyan adod meg, hogy a pontszám 30 és 40 között legyen?

Pontszám | Pontszám fejléc egymás mellett, alattuk egy sorban: >=30 | <=40.

Mit talál meg a „Sz” feltétel a Név mezőben?

Minden „Sz”-szel kezdődő nevet (például Szabó Máté).

Mit kell a feltételtartományba írni, hogy a határ a H1 cellából jöjjön?

=">="&$H$1 (vagy a megfelelő összehasonlító jellel).

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Adatbázis-kezelés táblázatkezelővel (összetett feltételek, összesítés). Az emelt szintű érettségi adatbázisfüggvényes követelményének és az NKP-tétel „AB függvények használata” elemének részletes gyakorlása; kapcsolódás az SQL WHERE feltételeihez (AND, BETWEEN, LIKE).

Módszertan

A lecke a feltételtartomány minden jellemző formáját (több mező, intervallum, szöveg, cellából vett határ, számított feltétel) egy állandó, kis adatsoron gyakoroltatja, így minden eredmény fejben is ellenőrizhető. A jóslás a szöveges feltétel „kezdete” jelentését mutatja meg, a hibavadászat a két leggyakoribb hibát (intervallum két sorban, cellanév szövegként). A számított feltétel kitekintésként szerepel, a haladóbb tanulóknak.

Időterv

Több mező: 6 perc. Intervallum: 6 perc. Szöveges feltételek és jóslás: 8 perc. Feltétel cellából, kitekintés: 8 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 12.5 AB.DARAB, AB.DARAB2, AB.MIN és AB.MAX

Darabszám, legkisebb és legnagyobb érték feltétellel, és az AB.MEZŐ, amely egyetlen rekord adatát adja vissza.