Excel táblázatkezelés kezdőknek › 9.5 lecke
Excel tanfolyam · 9.5 lecke
Feltételek cellahivatkozással
Az osztályfőnök minden héten más kérdést tesz fel: „Hányan értek el legalább 40 pontot?”, a következő héten „legalább 45-öt”, máskor „a 9. a-ból hányan?”. Ha a feltétel a képletben van, minden kérdésnél át kell írni. Ha egy cellában, akkor csak a cellát. Ebben a leckében megtanulod a feltételt cellából venni, és egy kis „vezérlőpultot” építesz, amely azonnal válaszol bármelyik kérdésre.
- ⏱ kb. 45 perc
- 🎓 haladóbb
- 🎒 10. osztálytól
A lecke végére
- a feltételt egy cellából veszed;
- összehasonlító jelet fűzöl össze egy cellával;
- dátumot használsz feltételként;
- interaktív vezérlőpultot építesz bemeneti cellákkal;
- legördülő listát készítesz a bemeneti cellába.
Amit már tudsz
Feltétel cellából
Ha a feltétel egyenlőség (pontosan ez az érték), egyszerűen a cellára hivatkozol, idézőjel nélkül:
| H1 tartalma | Képlet | Jelentés |
|---|---|---|
| 9.b | =DARABTELI(B2:B60;$H$1) | a 9. b-sek száma |
| angol | =ÁTLAGHA(C2:C60;$H$1;D2:D60) | az angolosok átlaga |
| >=40 (a cellába írva) | =DARABTELI(D2:D60;$H$1) | a legalább 40 pontosak; a cellában álló teljes feltétel is működik |
💡 Miért dollárjel?
A feltételcella egy rögzített „bemenet” (4.3): ha a képletet másolod (például több eredménycellába), ne vándoroljon el. Ugyanígy a tartományokat is rögzítsd. A kategóriatáblában (9.1) viszont a feltételcella szándékosan relatív, mert soronként másik kategóriára mutat.
Jel és cella összefűzése
Ha a cellában csak a szám áll (H2 = 40), a műveleti jelet a képletben kell elé fűzni az & jellel (4.1):
| Feltétel | Képlet |
|---|---|
| legalább a határ | =DARABTELI(D2:D60;">="&$H$2) |
| a határ alatt | =DARABTELI(D2:D60;"<"&$H$2) |
| nem egyenlő a H1 osztállyal | =DARABTELI(B2:B60;"<>"&$H$1) |
| a H3 dátum után (H3-ban valódi dátum) | =DARABTELI(A2:A60;">"&$H$3) |
| két határ között (H2 és H4) | =DARABHATÖBB(D2:D60;">="&$H$2;D2:D60;"<="&$H$4) |
🔮 Jósold meg!
A H2 cellában 40 áll. Mit ad (a) =DARABTELI(D2:D60;">=H2") (b) =DARABTELI(D2:D60;">="&H2)?
Megoldás
(a) 0 (vagy értelmetlen eredmény): az idézőjelek között a „H2” csak két betű, nem cellahivatkozás. Az Excel a „H2” szöveghez hasonlít, és egyik pontszám sem „nagyobb vagy egyenlő” vele számként. (b) Helyes: az Excel előbb összefűzi a ">=" szöveget és a H2 értékét, így a feltétel ">=40" lesz. A szabály: a jel idézőjelben, a cella kívül, közöttük &.
✅ Dátumfeltételnél
A dátumot a legbiztosabb egy cellába írni (valódi dátumként, 3.7), és összefűzéssel hivatkozni rá, mint fent. A képletbe írt dátumszöveg (például ">2026.10.01") a gép területi beállításaitól függően értelmeződik. Egy képletben előállított dátumhoz a DÁTUM függvény jó (11.6): ">="&DÁTUM(2026;10;1).
Vezérlőpult
- A munkalap tetején (vagy egy külön lapon, 4.7) készíts bemeneti cellákat felirattal, kiemelve (Bevitel cellastílus, 4.3): Osztály (H1), Ponthatár (H2).
- Alatta eredménycellák: Létszám:
=DARABTELI($B$2:$B$60;$H$1); Legalább a határ:=DARABHATÖBB($B$2:$B$60;$H$1;$D$2:$D$60;">="&$H$2); Arány: a kettő hányadosa; Átlag:=HAHIBA(ÁTLAGHA($B$2:$B$60;$H$1;$D$2:$D$60);"nincs adat"). - Írd át a H1 vagy a H2 értékét: minden eredmény azonnal frissül.
- Legördülő lista a H1-be: jelöld ki a cellát → Adatok → Érvényesítés (Adatérvényesítés) → Engedélyezve: Lista → Forrás:
9.a;9.b;9.c(vagy egy tartomány, amelyben az osztálynevek állnak). Így a felhasználó nem gépelhet el egy osztálynevet.
💡 A „mi lenne, ha” elve
A vezérlőpult ugyanazt az elvet követi, mint a 4.7 paraméterlapja és a 4.10 bevásárlólistája: a kérdés (bemenet) és a számolás (képlet) külön van. Az érettségi feladatokban is gyakori, hogy egy megadott cellában változtatható érték alapján kell számolni.
Kihívás: az osztályfőnök vezérlőpultja
🎛️ Egy pult, sok kérdés
A 9.1 osztálystatisztikai listáján (vagy egy több osztályt tartalmazó listán) dolgozz.
- Készíts vezérlőpultot három bemeneti cellával: Nem (legördülő lista: lány; fiú), Nyelv (legördülő lista: angol; német), Ponthatár (szám).
- Eredmények: a kiválasztott nemű és nyelvű diákok létszáma; közülük hányan érték el a ponthatárt; az arányuk; az átlagpontszámuk (hiány esetén „nincs adat”).
- Adj hozzá egy negyedik bemenetet, „Legfeljebb ennyi mulasztás” néven, és vedd be a feltételek közé (SZUMHATÖBB, DARABHATÖBB, ÁTLAGHATÖBB).
- Próbálj ki legalább négy beállítást, és írd le az eredményeket egy kis táblázatba.
- Bónusz: egy cellában mondatként is írd ki az eredményt, például: „A németes lányok közül 5-en érték el a 40 pontot.” (& jel, 4.1.)
Hibavadászat
🐞 1. Peti szöveges cellája
Peti így írta a feltételt: =DARABTELI(D2:D60;"<H2"), hogy megszámolja a határ alatti dolgozatokat. Az eredmény mindig 0, bármit ír a H2-be.
Mi a megoldás?
Az idézőjelek között a H2 nem hivatkozás, hanem szöveg (2. rész). Helyesen: =DARABTELI(D2:D60;"<"&H2), és ha a képletet másolja, dollárjellel: "<"&$H$2.
🐞 2. Lili elcsúszott vezérlőpultja
Lili egy eredménycellában megírta a képletet: =DARABHATÖBB(B2:B60;H1;D2:D60;">="&H2), majd átmásolta a mellette lévő cellába, hogy egy másik határral is kipróbálja. Az új cella hibás vagy 0 eredményt ad.
Mit tegyen?
Dollárjelek nélkül másoláskor minden hivatkozás eggyel jobbra csúszott: a tartományok a C és az E oszlopra, a bemenetek az I1-re és az I2-re (4.4). Lili rögzítse a tartományokat és a bemeneti cellákat: =DARABHATÖBB($B$2:$B$60;$H$1;$D$2:$D$60;">="&$H$2). Ha szándékosan egy másik határral akar számolni, azt egy másik bemeneti cellára hivatkozva tegye.
Összefoglalás
- Egyenlőségnél a feltétel egyszerűen egy cella: =DARABTELI(…;$H$1).
- Összehasonlításnál a jel idézőjelben, a cella kívül: ">="&$H$2.
- Az idézőjel közötti cellanév csak szöveg, nem hivatkozás.
- Dátumfeltétel: valódi dátum egy cellában, összefűzéssel; vagy DÁTUM függvény.
- Vezérlőpult: bemeneti cellák (legördülő listával), rögzített hivatkozások, azonnal frissülő eredmények.
Ellenőrizd magad!
Hogyan számolod meg a H5 értékénél nagyobb számokat a C2:C40 tartományban?
=DARABTELI(C2:C40;">"&H5)
Miért nem működik a ">H5" feltétel?
Mert az idézőjelek között a H5 szöveg, nem cellahivatkozás.
Hol készítesz legördülő listát egy cellába?
Adatok → Érvényesítés → Engedélyezve: Lista.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (képletek és függvények paraméterezése, cellahivatkozások, adatbevitel ellenőrzése), modellezés és szimuláció („mi lenne, ha” vizsgálat); kapcsolódás a programozáshoz (paraméteres eljárások, bemenet és feldolgozás szétválasztása).
Módszertan
A lecke a feltételt paraméterré teszi, így a diák a „varázsszám nélkül” elvét (4.3) a feltételes összesítésre is átviszi. A jóslás a leggyakoribb szintaktikai hibát (cellanév idézőjelben) teszi tudatossá. A vezérlőpult egy kicsi, de valódi interaktív alkalmazás, amely a bemenet és a feldolgozás szétválasztását gyakoroltatja; a legördülő lista az adatbeviteli hibák megelőzésére is példa. Lili esete a hivatkozások rögzítésének fontosságát ismétli.
Időterv
Feltétel cellából: 6 perc. Jel és cella összefűzése és jóslás: 10 perc. Vezérlőpult: 10 perc. Kihívás: 15 perc. Hibavadászat: 4 perc.
Következő lecke: 9.6 Helyettesítő karakterek a feltételekben
A * és a ? a feltételekben: minden „K”-val kezdődő név, minden „robot” szót tartalmazó szakkör egy függvénnyel.
