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

  • a feltételes összesítő függvényeket (9.1–9.4);
  • a szövegösszefűzést az & jellel (4.1);
  • a bemeneti cellát és a dollárjelet (4.3, 4.5);
  • a dátumot mint számot (3.7).

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 tartalmaKépletJelenté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ételKé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

  1. 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).
  2. 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").
  3. Írd át a H1 vagy a H2 értékét: minden eredmény azonnal frissül.
  4. 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.

  1. 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).
  2. 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”).
  3. 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).
  4. Próbálj ki legalább négy beállítást, és írd le az eredményeket egy kis táblázatba.
  5. 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.