SZUMHA: egy feltétel szerinti összeg az Excelben

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.

DARABTELI: egy feltétel szerinti darabszám az Excelben

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

Excel tanfolyam · 9.1 lecke

DARABTELI: egy feltétel szerinti darabszám

Hány 9. b-s van a listán? Hányan értek el legalább 40 pontot? Hányan nem adták be a dolgozatot? A 8.2-ben ezt egy HA-s segédoszloppal és egy SZUM-mal oldottuk meg. Kezdődik a 9. fejezet: a feltételes összesítő függvények mindezt egyetlen lépésben, segédoszlop nélkül elvégzik. Az első és a leggyakrabban használt közülük a DARABTELI.

  • ⏱ kb. 45 perc
  • 🎯 kezdőknek
  • 🎒 10. osztálytól

A lecke végére

  • DARABTELI-vel megszámolod a feltételnek megfelelő cellákat;
  • szöveges, számos és összehasonlító feltételt adsz meg;
  • megszámolod az üres és a nem üres cellákat;
  • arányt számolsz DARABTELI és DARAB2 segítségével;
  • a segédoszlopos megoldást egy függvénnyel váltod ki.

Amit már tudsz

  • a DARAB, DARAB2 és DARABÜRES függvényt (5.4);
  • az összehasonlító jeleket (8.1);
  • a HA-s segédoszlopot (8.2);
  • a szűrést (7.3).

A DARABTELI

DARABTELI(tartomány;feltétel): megszámolja, hogy a tartomány hány cellája felel meg a feltételnek.
KérdésSegédoszloppal (8.2)DARABTELI-vel
Hány 9. b-s van?=HA(B2="9.b";1;0) lefelé, majd =SZUM(…)=DARABTELI(B2:B31;"9.b")
  • A tartomány csak az adatokat tartalmazza, fejléc nélkül.
  • A feltétel egy érték vagy egy idézőjelbe tett összehasonlítás (2. rész).
  • A szöveges feltételnél a kis- és nagybetű nem számít: a "9.B" is megtalálja a „9.b”-t.

Feltételek

FeltételPéldaMit számol meg?
szöveg=DARABTELI(C2:C26;"angol")az „angol” értékű cellákat
szám=DARABTELI(D2:D26;50)a pontosan 50-et tartalmazó cellákat
összehasonlítás=DARABTELI(D2:D26;">=40")a legalább 40-et tartalmazókat
nem egyenlő=DARABTELI(B2:B26;"<>9.a")a nem „9.a” értékűeket (az üreseket is!)
logikai érték=DARABTELI(F2:F26;IGAZ)az IGAZ cellákat (8.1)
üres cella=DARABTELI(E2:E26;"")az üres cellákat (mint a DARABÜRES)
nem üres cella=DARABTELI(E2:E26;"<>")a kitöltött cellákat (mint a DARAB2)

🔮 Jósold meg!

Melyik képlet működik, és mit ad? (a) =DARABTELI(D2:D26;>=40) (b) =DARABTELI(D2:D26;">=40") (c) =DARABTELI(D2:D26;"=40")
Megoldás
(a) Nem működik: az Excel hibát jelez, mert a >=40 idézőjel nélkül nem érvényes argumentum. Az összehasonlító jelet a számmal együtt szövegként kell megadni. (b) Helyes: a legalább 40 pontosak száma. (c) Helyes: a pontosan 40 pontosak száma, ugyanaz, mintha csak 40-et írnál. Az egyenlőség jele elhagyható. Ökölszabály: ha a feltételben összehasonlító jel van, az egészet tedd idézőjelbe, és ne írj bele szóközt.

💡 Ha a határ egy cellában van

A határt is írhatod bemeneti cellába (például H1 = 40). Ilyenkor a jelet és a cellát össze kell fűzni: =DARABTELI(D2:D26;">="&$H$1). Erről a 9.5 leckében részletesen lesz szó.

Arány és ellenőrzés

  • Arány: a lányok aránya az osztályban: =DARABTELI(B2:B26;"lány")/DARAB2(A2:A26), százalék formátumban (4.8).
  • Kiegészítés: a fiúk száma vagy =DARABTELI(B2:B26;"fiú"), vagy =DARAB2(A2:A26)-DARABTELI(B2:B26;"lány"). Ha a kettő nem egyezik, a Nem oszlopban hibás (elírt, üres) adat van: ez jó adatminőség-ellenőrzés (7.5).
  • Szűréssel ellenőrizve: szűrd a listát a feltételre (7.3), és nézd meg az állapotsoron a rekordok számát: egyeznie kell a DARABTELI eredményével.

✅ Statisztikai táblázat

Ha több kategóriát számolsz (például osztályonként), írd a kategóriákat egy oszlopba (H2: 9.a, H3: 9.b, …), és a feltétel helyére hivatkozz rájuk: =DARABTELI($B$2:$B$31;H2). A tartomány rögzített (4.5), a feltétel lefelé másolva vándorol. Ellenőrzésként a darabszámok összege adja ki a teljes létszámot.

Kihívás: osztálystatisztika

📊 Válaszolj egy-egy DARABTELI-vel!

Készíts egy kitalált, 25 fős listát: Név (A), Nem (B: lány/fiú), Első idegen nyelv (C: angol/német), Évfolyamdolgozat (D: pontszám 0–100), Mulasztott órák (E). Két diáknál a dolgozat cellája legyen üres (nem írta meg).
  1. Hány lány és hány fiú van? Az összegük kiadja a létszámot?
  2. Hányan tanulnak angolt első idegen nyelvként?
  3. Hányan értek el legalább 50 pontot a dolgozaton? És hányan 20 pont alatt?
  4. Hányan nem írták meg a dolgozatot?
  5. Hányan nem mulasztottak egy órát sem? Hányan mulasztottak 20 óránál többet?
  6. Az osztály hány százaléka tanul németet?
  7. Készíts egy kis táblázatot nyelvenként (angol, német) a DARABTELI-vel, és ellenőrizd szűréssel.

Hibavadászat

🐞 1. Peti eggyel több diákja

Peti így számolta meg a nem 9. a-s diákokat: =DARABTELI(B1:B31;"<>9.a"). Az eredmény eggyel több a valóságosnál.
Mi a megoldás?
A tartomány a B1-ben kezdődik, vagyis az „Osztály” fejléc is benne van, és az is „nem 9.a”, ezért beszámolódik (1. rész). Peti a B2-től indítsa a tartományt. Figyeljen az üres cellákra is: a "<>9.a" az üreseket is megszámolja (2. rész).

🐞 2. Lili hiányzó 9. b-sei

Lili képlete: =DARABTELI(B2:B31;"9. b"). Az eredmény 0, pedig a listán tíz 9. b-s van, így írva: „9.b”.
Mit tegyen?
A „9. b” szóközzel és a „9.b” szóköz nélkül két különböző szöveg (7.6). Lili a feltételt pontosan úgy írja, ahogy az adatokban szerepel ("9.b"), vagy még jobb: egységesítse az adatokat, és a feltételt egy cellából vegye (3. rész).

Összefoglalás

  • =DARABTELI(tartomány;feltétel): a feltételnek megfelelő cellák száma.
  • Feltétel: szöveg ("9.b"), szám (50), összehasonlítás (">=40"), IGAZ, "" (üres), "<>" (nem üres).
  • Az összehasonlító jel a számmal együtt idézőjelben, szóköz nélkül.
  • Arány: DARABTELI / DARAB2; ellenőrzés szűréssel vagy a kategóriák összegével.
  • Kategóriatábla: rögzített tartomány, vándorló feltételcella.

Ellenőrizd magad!

Hogyan számolod meg a 30-nál nagyobb értékeket a C2:C20 tartományban?
=DARABTELI(C2:C20;">30")
Mit számol meg a =DARABTELI(A2:A20;"")?
Az üres cellákat.
Számít-e a kis- és nagybetű a DARABTELI szöveges feltételében?
Nem.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (statisztikai függvények, feltételtől függő számítások); kapcsolódás a matematikához (gyakoriság, relatív gyakoriság) és a programozáshoz (megszámlálás típusalgoritmus). Az NKP-okostankönyvre épülő emelt szóbeli tételek feltételes statisztikai feladatának (tanulók neme, nyelve, dolgozata, mulasztása) mintájára készült kitalált adatokkal.

Módszertan

A fejezet nyitó leckéje a 8.2 segédoszlopos módszerét egy függvénnyel váltja ki, így a diák látja a két megoldás egyenértékűségét. A jóslás a feltétel írásmódjának leggyakoribb hibáját (idézőjel nélküli összehasonlítás) dolgozza fel. A „kiegészítés” és a „szűréssel ellenőrzés” átvihető, adatminőséget is vizsgáló ellenőrzési szokásokat alakít ki. A kategóriatábla vegyes logikája (rögzített tartomány, vándorló feltétel) a 4.5 tudását alkalmazza.

Időterv

A DARABTELI: 6 perc. Feltételek és jóslás: 11 perc. Arány és ellenőrzés: 8 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.

Következő lecke: 9.2 SZUMHA: egy feltétel szerinti összeg

Mennyi a 9. b papírgyűjtése? Mennyit költöttünk ennivalóra? Feltételes összeg egy függvénnyel.

Mini projekt: pontszámok és minősítések – versenyértékelés Excelben

Excel táblázatkezelés kezdőknek › 8.9 mini projekt

Excel tanfolyam · 8.9 mini projekt

Pontszámok és minősítések

Lezajlott az iskolai matematikaverseny: négy feladat, feladatonként legfeljebb 25 pont. A szervezők szabályai szerint díjat a százalékos eredmény alapján adnak, a továbbjutáshoz minden feladatban el kell érni egy minimumot, a különdíjat pedig az kapja, aki valamelyik feladatot hibátlanul megoldotta. Akik nem adtak be minden feladatot, külön elbírálás alá esnek. Ebben a mini projektben a 8. fejezet minden eszközével elkészíted az értékelőtáblát.

  • ⏱ kb. 45 perc
  • 🧩 mini projekt
  • 🎒 9. osztálytól

A projekt végére

  • szöveges szabályokat pontos logikai képletekké alakítasz;
  • határtáblázatból, százalékosan minősítesz;
  • több feltételt kapcsolsz össze ÉS, VAGY, MIN, MAX segítségével;
  • célzottan kezeled a hiányos adatokat;
  • tesztsorokkal ellenőrzöd a képleteidet.

Ehhez használod a 8. fejezet tudását

  • 8.1 összehasonlítás; 8.2 HA; 8.3 ÉS, VAGY;
  • 8.4 egymásba ágyazás; 8.5 határok;
  • 8.6 hibakezelés; 8.7–8.8 feltételes formázás.

A szabályok

A verseny kitalált szabályzata (olvasd el figyelmesen, minden szó számít!):
SzabályTartalom
Teljes beadásAkinél valamelyik feladat helyén a „nem adta be” bejegyzés áll, annak az eredménye „hiányos”, és nem kap díjat.
DíjakA maximális pontszám (100) százalékában: legalább 85% arany, legalább 70% ezüst, legalább 55% bronz, legalább 40% oklevél; ez alatt nincs díj.
TovábbjutásTovábbjut, aki legalább 60 pontot ért el, és minden feladatból legalább 5 pontot kapott.
KülöndíjKülöndíjat kap, aki valamelyik feladatot hibátlanul (25 pont) oldotta meg.

💡 Először a szavak

Mielőtt képletet írsz, húzd alá a szabályokban a logikai kulcsszavakat: „legalább” (≥), „és” (ÉS / MIN), „valamelyik” (VAGY / MAX), „nem” (NEM). Minden szabályhoz írj egy mondatot a „Ha…, akkor…, különben…” mintára (8.2).

Felépítés

  1. Új munkafüzet verseny néven, két lappal: Beállítások és Eredmények. A Beállítások lapra: maximális pontszám (100), továbbjutási pontszám (60), feladatonkénti minimum (5), hibátlan feladat (25), és a díjak határtáblázata alsó határokkal, növekvő sorrendben: 0% nincs díj, 40% oklevél, 55% bronz, 70% ezüst, 85% arany (8.5).
  2. Az Eredmények lapon: Név, F1, F2, F3, F4 (legalább 12 kitalált versenyző; kettőnél valamelyik feladat helyén „nem adta be”), valamint a lenti három tesztsor (4. rész).
  3. Beadott: =DARAB(B2:E2), vagyis hány feladat helyén szám áll (5.4).
  4. Összesen: =HA(F2=4;SZUM(B2:E2);"hiányos"). (Miért nem HAHIBA? Mert a SZUM a szöveget hiba nélkül kihagyja, így a hiányos beadás észrevétlen maradna, 8.6.)
  5. Százalék: =HA(F2=4;G2/Beállítások!$B$1;""), százalék formátumban (4.7, 4.8).
  6. Díj: egymásba ágyazott HA a határtáblázat celláival, a legnagyobb határtól lefelé (8.4), és hiányos beadásnál üres: =HA(F2<4;"";HA(H2>=Beállítások!$B$10;"arany";HA(…)))). A cellahivatkozásokat a saját határtáblázatodhoz igazítsd!
  7. Továbbjut: =HA(ÉS(F2=4;G2>=Beállítások!$B$2;MIN(B2:E2)>=Beállítások!$B$3);"igen";"nem") (8.3).
  8. Különdíj: =HA(MAX(B2:E2)=Beállítások!$B$4;"különdíj";"") (8.3).

Kiemelés

  1. Jelöld ki a teljes eredménylistát (fejléc nélkül), és képletes feltételes formázással (8.8) emeld ki: az aranyérmesek teljes sorát halványarany kitöltéssel (=$I2="arany"), a hiányos beadásúakét szürkével (=$F2<4).
  2. A Továbbjut oszlopban ikonkészlettel vagy betűszínnel jelöld az „igen”-t (de ne csak színnel, 8.7).
  3. A Szabálykezelőben ellenőrizd a sorrendet: a szürke (hiányos) kiemelés legyen felül.

Tesztelés

Vedd fel a listába ezt a három tesztsort, és ellenőrizd, hogy a képleteid pontosan ezeket az eredményeket adják:
NévF1–F4ÖsszesenSzázalékDíjTovábbjutKülöndíj
Teszt A25, 22, 20, 188585%aranyigenkülöndíj
Teszt B15, 4, 24, 206363%bronznem (a 4 pont kevés)(üres)
Teszt C20, nem adta be, 15, 10hiányos(üres)(üres)nem(üres)

✅ Határesetek

Készíts még két tesztsort pontosan a határokon: egy 85 pontosat (arany kell legyen, nem ezüst!) és egy 60 pontosat, minden feladatban legalább 5 ponttal (továbbjut). Ha valamelyik rossz, nézd meg a > és a >= jeleket és a feltételek sorrendjét (8.4, 8.5).

💡 Mi lenne, ha…?

A szervezők szigorítanak: a továbbjutáshoz 65 pont kell, az aranyhoz 90%. Írd át a Beállítások lapon: minden eredmény és kiemelés frissül? Ha igen, a táblázatod „varázsszám” nélkül készült (4.3).

Önellenőrzés és értékelés

✅ Önellenőrző lista

  • Minden határ és paraméter a Beállítások lapon van, a képletek arra hivatkoznak.
  • A díj a legnagyobb határtól lefelé épül, a határtáblázat alsó határaival.
  • A hiányos beadást a DARAB függvény ismeri fel, nem a HAHIBA rejti el.
  • A továbbjutás ÉS-sel és MIN-nel, a különdíj MAX-szal (vagy VAGY-gyal) készült.
  • A három tesztsor és a két határeset a várt eredményt adja.
  • A kiemelések egész sorokra hatnak ($ az oszlop előtt), és rendezés után is helyesek.
  • A kiemelés nem csak színnel jelzi az eredményt.
Szempont⭐ Elindultam⭐⭐ Sikerült⭐⭐⭐ Profi vagyok
Logikai képletekVan néhány HA.Minden szabály helyes képlettel.MIN, MAX és ÉS, VAGY tudatos választással, egyszerű képletekkel.
ParaméterezésA határok a képletekben.Beállítások lap, abszolút hivatkozás.Határtáblázat; a „mi lenne, ha” kérdésekre azonnal válaszol.
EllenőrzésRánéztem.A tesztsorok jók.Határesetek is tesztelve, a hiányos beadás célzottan kezelve.

Hibavadászat: Peti versenyértékelése

🐞 Peti beszámolója

„A díjképletet a bronzzal kezdtem, és mindenki bronzot kapott, aki 55% fölött van. A továbbjutáshoz ezt írtam: =HA(60<=G2<=100;…). A különdíjnál ÉS-t használtam, mert »minden feladatnál nézni kell«. Az összeget HAHIBA-ba tettem, és a hiányos beadásúak is díjat kaptak. A szöveget idézőjel nélkül írtam: =HA(…;arany;…). A feltételes formázásban =I2="arany" szerepel, és csak az I oszlop lett színes. A 85-ös határnál > jelet használtam.”
Hány hibát találtál? Mit tanácsolnál Petinek?
HibaHelyesenLecke
a díjlánc a legkisebb határral kezdődika legnagyobb határtól lefelé8.4
matematikai kettős egyenlőtlenségÉS(…) vagy egyetlen >= feltétel8.3
ÉS a „valamelyik” helyettVAGY vagy MAX8.3
HAHIBA eltakarja a hiányos beadástDARAB-bal ellenőrizni, HA-val kezelni8.6
szöveg idézőjel nélkül"arany"8.2
dollárjel nélküli formázási képlet=$I2="arany"8.8
> a „legalább” határnál>=8.1, 8.5
Hét hibát találhattál. Ha mindet megtaláltad, a logikai függvények mestere vagy!

Mit tudsz már? A 8. fejezet összefoglalása

  • Összehasonlításokat írsz, és számolsz a logikai értékekkel.
  • HA-val, ÉS-sel, VAGY-gyal, NEM-mel és egymásba ágyazással pontos szabályokat valósítasz meg.
  • Hézag és átfedés nélküli kategóriákat és határtáblázatot készítesz.
  • Célzottan kezeled a várható hibákat, és nem rejted el a valódiakat.
  • Feltételes formázással, saját képlettel is, kiemeled a fontos adatokat.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (logikai függvények, feltételtől függő számítások, feltételes formázás, több munkalap), szöveges specifikáció formalizálása; kapcsolódás a programozáshoz (elágazások, tesztelés tesztesetekkel és határesetekkel). Az érettségi táblázatkezelési feladatok jellegzetes szerkezetét (szabályok szövegesen, eredmény képletekkel, kiemelés formázással) modellezi.

Módszertan

A mini projekt a 8. fejezet minden eszközét egy szöveges szabályzat formalizálásában alkalmazza. A „kulcsszavak aláhúzása” és a „Ha…, akkor…” mondatok a specifikáció pontos olvasását fejlesztik. A várt eredménnyel megadott tesztsorok és a határesetek a programozásból ismert tesztelési gyakorlatot hozzák be a táblázatkezelésbe. A HAHIBA helyett DARAB-bal végzett célzott hibakezelés a 8.6 tanulságát alkalmazza. Peti beszámolója leckénként egy tipikus hibát ismétel.

Időterv (45 perc)

A szabályok: 6 perc. Felépítés: 20 perc. Kiemelés: 7 perc. Tesztelés: 7 perc. Hibavadászat: 5 perc.

Következő fejezet: 9.1 DARABTELI: egy feltétel szerinti darabszám

Kezdődik a 9. fejezet: feltételes összesítés segédoszlop nélkül, egyetlen függvénnyel.

Feltételes formázás saját képlettel az Excelben

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

Excel tanfolyam · 8.8 lecke

Feltételes formázás saját képlettel

A beépített szabályok egy cella saját értékét vizsgálják. De mi van, ha azt szeretnéd, hogy a 40 pont alatti diákok teljes sora piros legyen? Vagy csak a 9. b-sek közül emelnéd ki a legjobbakat? Ilyenkor saját képletet írsz a feltételes formázásba. A kulcs ugyanaz, mint a 4.6-ban: a dollárjel helye dönti el, mit figyel a képlet.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 9. osztálytól

A lecke végére

  • saját képlettel készítesz feltételes formázási szabályt;
  • a képletet a kijelölés bal felső cellájára írod;
  • vegyes hivatkozással egész sorokat emelsz ki;
  • ÉS-sel, VAGY-gyal több feltételt figyelsz;
  • sávos sorokat, maximumot és lejárt dátumot jelölsz.

Amit már tudsz

  • a feltételes formázás alapjait (8.7);
  • a vegyes hivatkozást (4.6);
  • az ÉS és a VAGY függvényt (8.3);
  • a véletlenszámokat és a MARADÉK függvényt (5.6).

A képletes szabály

  1. Jelöld ki a formázandó tartományt a 2. sortól (fejléc nélkül), például a teljes listát: A2:D31. A kijelölés bal felső cellája (itt A2) az aktív cella.
  2. Kezdőlap → Feltételes formázás → Új szabály → A formázandó cellák kijelölése képlettel.
  3. Írd be a képletet úgy, mintha az aktív cellába (A2) írnád, és IGAZ vagy HAMIS eredményt adna: például =$C2<40 (a 2. sor pontszáma kisebb, mint 40).
  4. Formátum… gomb: válaszd ki a kitöltést, a betűszínt. OK, OK.
  5. Az Excel a képletet a kijelölés minden cellájára „lemásolja”, ugyanúgy, mint a kitöltőfogantyú (4.4): a dollárjel nélküli részek elmozdulnak, a dollárjelesek maradnak.

💡 Próbáld ki előbb a munkalapon!

Ha bizonytalan vagy, írd be a képletet egy üres oszlop 2. sorába (például =$C2<40), töltsd ki lefelé, és nézd meg, hol lesz IGAZ. Ha jó, másold be a szabályba, a segédoszlopot pedig töröld.

A dollárjel helye

🔮 Jósold meg!

Kijelölöd az A2:D31 tartományt (Név, Osztály, Pontszám, Jegy). A Pontszám a C oszlopban van. Mi lesz kiemelve az egyes képletekkel? (a) =$C2<40 (b) =C2<40 (c) =$C$2<40
Megoldás
(a) A 40 alatti pontszámú diákok teljes sora: az oszlop rögzített (mindig a C-t nézi), a sor pedig vándorol (minden sor a sajátját). (b) Kuszaság: az A oszlop a C-t, a B oszlop a D-t, a C oszlop az E-t nézi… mert az oszlop is elmozdul. (c) Vagy minden cella ki van emelve, vagy egy sem: mindegyik cella a C2-t, az első diák pontszámát vizsgálja. Egész sor kiemeléséhez tehát: dollár az oszlop elé, a sor elé nem (vegyes hivatkozás, 4.6).
CélHivatkozásPélda
egész sor egy oszlop alapján$C2=$C2<40
egész oszlop egy sor alapjánC$1=C$1="hétvége"
rögzített határ (bemeneti cella)$H$1=$C2<$H$1
csak a cella saját értékeC2 (ha csak a C oszlopot jelölöd ki)=C2<40

Hasznos minták

FeladatKéplet (aktív cella: A2)
9. b-s és legalább 40 pontos sorok=ÉS($B2="9.b";$C2>=40)
9. a-s vagy 9. b-s sorok=VAGY($B2="9.a";$B2="9.b")
minden második sor (sávozás)=MARADÉK(SOR();2)=0 (a SOR függvény a cella sorszámát adja)
a sor legnagyobb értéke (B2:E2 kijelölve, aktív: B2)=B2=MAX($B2:$E2)
lejárt határidő (a D oszlopban dátum)=$D2<MA() (a MA függvény a mai dátumot adja, 11.6)
hiányzó adat a sorban=DARABÜRES($A2:$D2)>0
véletlenszám 33 és 66 között (A1:A10 kijelölve)=ÉS(A1>=33;A1<=66)

✅ A szín követi az adatot

A képletes szabály a rendezés és a szűrés után is helyes marad, mert minden sor a saját adatait vizsgálja. Ez az, amire a 6.9-ben a lebegő nyíl nem volt képes, és amiért a „szín nem adat” elve (7.1) így teljesül: a szín az adatból következik, nem helyette áll.

Kihívás: kiemelések képlettel

🎲 1. Véletlenszámok sávja

  1. Az A1:A10 tartományba generálj tíz véletlen egész számot 1 és 100 között (5.6).
  2. Emeld ki képletes feltételes formázással azokat, amelyek 33 és 66 között vannak (a határokat is beleértve).
  3. Nyomd meg néhányszor az F9-et: a kiemelés mindig a jó számokat követi?
  4. Oldd meg ugyanezt a beépített „Között” szabállyal is. Mi a különbség?

📋 2. A pontszámlista teljes sorai

  1. A 8.4 pontszámlistáján jelöld ki a teljes listát (fejléc nélkül), és emeld ki halványpirossal azoknak a teljes sorát, akik a határ (bemeneti cella) alatt vannak.
  2. Egy második szabállyal emeld ki félkövérrel a 9. b-s, legalább 4-es jegyű diákok sorát (ÉS).
  3. Adj a listának sávos sorokat (minden második sor halványszürke).
  4. Rendezd a listát pontszám szerint (7.2): a kiemelések a helyes sorokkal mozognak?
  5. Nyisd meg a Szabálykezelőt, és állítsd be a sorrendet úgy, hogy a piros kiemelés a sávozás „felett” legyen.

Hibavadászat

🐞 1. Peti csúszó kiemelése

Peti a teljes listát (A2:D31) jelölte ki, és ezt a képletet írta: =C2<40. A Név oszlopban jó sorok lettek pirosak, a többi oszlopban viszont összevissza.
Mi a megoldás?
Dollárjel nélkül az oszlop is elmozdul: a B oszlop a D-t, a C az E-t vizsgálja (2. rész). Az oszlopot rögzíteni kell: =$C2<40. A Szabálykezelőben szerkessze a szabályt.

🐞 2. Lili eggyel elcsúszott sorai

Lili az A1:D31 tartományt jelölte ki (a fejléccel együtt), és a képletbe ezt írta: =$C2<40. A kiemelés mindig egy sorral a megfelelő diák fölé került.
Mit tegyen?
A képletnek a kijelölés bal felső cellájára kell vonatkoznia (1. rész). Mivel a kijelölés az A1-ben kezdődik, az első sor (A1) a C2-t vizsgálta, a második (A2) a C3-at, és így tovább. Lili jelölje ki a tartományt a 2. sortól (A2:D31), vagy ha az A1-től jelöl, a képlet legyen =$C1<40 (de akkor a fejléc is vizsgálódik). A legjobb: fejléc nélkül kijelölni.

Összefoglalás

  • Új szabály → A formázandó cellák kijelölése képlettel; a képlet IGAZ vagy HAMIS eredményt ad.
  • A képlet a kijelölés bal felső (aktív) cellájára vonatkozik, és a többire „lemásolódik”.
  • Egész sor: $C2 (oszlop rögzítve, sor vándorol); rögzített határ: $H$1.
  • ÉS, VAGY, MAX, MARADÉK(SOR();2), MA() a képletben is használható.
  • A képletes kiemelés rendezés és szűrés után is helyes marad.

Ellenőrizd magad!

Milyen hivatkozás kell egy egész sor kiemeléséhez a C oszlop alapján?
$C2 (vegyes hivatkozás: oszlop rögzítve).
Melyik cellára kell írni a képletet?
A kijelölés bal felső (aktív) cellájára.
Mit csinál a =MARADÉK(SOR();2)=0 szabály?
Minden második (páros) sort kiemel.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (feltételes formázás, logikai függvények, relatív, abszolút és vegyes hivatkozás). Az NKP-okostankönyvre épülő emelt szóbeli tétel feladatának („generáljon 10 véletlenszámot, és emelje ki képlettel, feltételes formázással a 33 és 66 közé esőket”) közvetlen gyakorlása; az érettségi feladatok gyakori eleme (sorok kiemelése több feltétel alapján).

Módszertan

A lecke a képletes feltételes formázást a már ismert hivatkozástípusokra (4.4–4.6) vezeti vissza: a jóslás a három hivatkozásváltozat hatását tapasztalati úton mutatja meg. A „próbáld ki előbb a munkalapon” tanács átvihető hibakeresési stratégia. Peti és Lili esete a két leggyakoribb hibát (hiányzó dollár, elcsúszott aktív cella) dolgozza fel. A rendezés utáni ellenőrzés a 6.9-ben és a 7.1-ben felvetett kérdést zárja le.

Időterv

A képletes szabály: 8 perc. A dollárjel helye és jóslás: 10 perc. Hasznos minták: 7 perc. Kihívás: 16 perc. Hibavadászat: 4 perc.

Következő lecke: 8.9 Mini projekt: pontszámok és minősítések

A 8. fejezet zárása: egy verseny értékelőtáblája logikai függvényekkel, hibakezeléssel és feltételes formázással.

Feltételes formázás alapjai az Excelben

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

Excel tanfolyam · 8.7 lecke

Feltételes formázás alapjai

Egy 30 soros pontszámlistában ki van 40 pont alatt? Ki a legjobb öt? Hol nőtt a legnagyobbat az eladás? Ha a cellák maguktól kiszíneződnek az értékük szerint, ezekre egy pillantással válaszolhatsz. A feltételes formázás pontosan ezt teszi, és ha az adatok változnak, a színek is velük változnak. Ez az a „kiemelés”, amelyet a 6.9-ben a lebegő nyíl helyett ígértünk.

  • ⏱ kb. 45 perc
  • 🎯 kezdőknek
  • 🎒 9. osztálytól

A lecke végére

  • kiemelési szabályt állítasz be értéktartományra;
  • adatsávot, színskálát és ikonkészletet használsz;
  • a határt bemeneti cellából veszed;
  • szerkeszted, rendezed és törlöd a szabályokat;
  • akadálymentesen, nem csak színnel emelsz ki.

Amit már tudsz

  • a cellák formázását (3.1);
  • az ismétlődő értékek kiemelését (7.5);
  • az összehasonlításokat (8.1);
  • miért nem jó a lebegő nyíl a táblázatban (6.9).

Kiemelési szabályok

  1. Jelöld ki a formázandó tartományt (például a pontszámokat, C2:C31), fejléc és összesítő sor nélkül.
  2. Kezdőlap → Stílusok → Feltételes formázás → Cellakiemelési szabályok → például Kisebb, mint…
  3. Írd be a határt (40), vagy kattints egy bemeneti cellára (például F1): ekkor a szabályba =$F$1 kerül, és ha a határ változik, a kiemelés is.
  4. Válassz formátumot (például Világospiros kitöltés sötétpiros szöveggel), vagy az Egyéni formátum lehetőséggel a sajátodat. OK.
CsoportSzabályokPélda
Cellakiemelési szabályokNagyobb, Kisebb, Között, Egyenlő, Szöveg tartalmazza, Dátum (például a múlt héten), Ismétlődő értékeka 40 pont alattiak pirosak; a „9.b”-t tartalmazók kékek
Felső/alsó szabályokFelső 10 elem, Felső 10%, Alsó 10 elem, Átlag felett, Átlag alatta legjobb 5 pontszám zöld (a 10 átírható 5-re)

Adatsáv, színskála, ikon

TípusMit mutat?Mikor jó?
Adatsávoka cellán belül egy vízszintes sáv, amelynek hossza az értékkel arányosgyors összehasonlítás a cellákban, „mini oszlopdiagram”
Színskálákaz értékek színátmenettel (például piros–sárga–zöld)sok érték eloszlása, például havi hőmérséklet-táblázat
Ikonkészleteknyilak, közlekedési lámpák, csillagok az érték nagysága szerintkevés kategória (jó, közepes, gyenge; nőtt, csökkent)

💡 Az ikonok határai

Az ikonkészletek alapesetben százalékos sávokat használnak (felső harmad, középső harmad…). Ha konkrét határok kellenek (például 70 pont felett zöld, 40 alatt piros), a Szabálykezelőben (3. rész) állítsd a típust Számra, és írd be az értékeket.

A Szabálykezelő

Kezdőlap → Feltételes formázás → Szabályok kezelése. Itt látod a kijelölés (vagy a teljes munkalap) összes szabályát:
  • Szabály szerkesztése: a határ, a formátum vagy a típus módosítása.
  • Érvényesség: melyik tartományra vonatkozik a szabály (például =$C$2:$C$31); itt bővíthető, ha a lista nő.
  • Sorrend: a fel-le nyilakkal; a felül álló szabály az erősebb.
  • Törlés: Szabály törlése; vagy a menüben: Szabályok törlése a kijelölt cellákból / a teljes munkalapról.

🔮 Jósold meg!

Két szabály vonatkozik a pontszámokra: A: „legalább 40 → világoszöld kitöltés”, B: „legalább 70 → sötétzöld kitöltés, félkövér betű”. Az A áll felül a listában. Milyen lesz egy 85 pontos cella?
Megoldás
Mindkét szabály teljesül. Ahol ugyanazt a tulajdonságot állítják (itt a kitöltés), ott a felül álló szabály győz: a kitöltés világoszöld lesz. Ahol nem ütköznek (a félkövér betű csak a B-ben van), ott mindkettő érvényesül: a 85 tehát világoszöld és félkövér. Ha azt szeretnéd, hogy a 70 felettiek sötétzöldek legyenek, a B szabályt mozgasd felülre a nyíllal.

Nem csak színnel

✅ Akadálymentes kiemelés

  • A színtévesztők (a fiúk körülbelül 8%-a) a pirosat és a zöldet nehezen különböztetik meg. Ne csak a szín hordozza az információt: használj ikont, félkövér betűt vagy egy külön szöveges oszlopot (például „Megfelelt / Nem felelt meg”, 8.2).
  • Nyomtatásban (fekete-fehérben) a színek is szürkévé válnak: ellenőrizd a nyomtatási képet (13. fejezet).
  • Mértékkel: ha minden cella színes, semmi sem tűnik ki.

Kihívás: a pontszámlista beszél

🎨 Kiemelések egy listán

  1. Vedd elő a 8.4-ben készült pontszám- és osztályzatlistát.
  2. A pontszámokon: a határ (bemeneti cella, például 40) alatti értékek világospirosak legyenek. Írd át a határt 45-re: frissül?
  3. A legjobb 3 pontszám legyen félkövér, zöld kitöltéssel (Felső 10 elem → 3).
  4. Az osztályzatoknál használj ikonkészletet három kategóriával, számhatárokkal: 4–5 zöld, 3 sárga, 1–2 piros (Szabálykezelő → Szám típus).
  5. Egy újabb oszlopban adatsávokkal mutasd a pontszámokat.
  6. Nyisd meg a Szabálykezelőt: hány szabály van? Próbáld ki a sorrend cseréjét, és figyeld meg a hatását.
  7. Ellenőrizd a nyomtatási képet fekete-fehérben: érthető marad a kiemelés?

Hibavadászat

🐞 1. Peti elavult határa

Peti a szabályba fix 40-et írt. A tanár a határt 45-re emelte, Peti a bemeneti cellában ezt át is írta, de a kiemelés továbbra is a 40 alattiakat jelöli.
Mi a megoldás?
A szabály a beírt számot használja, nem a bemeneti cellát. Peti a Szabálykezelőben szerkessze a szabályt, és a 40 helyére hivatkozzon a bemeneti cellára: =$F$1 (1. rész). Ugyanaz az elv, mint a képleteknél: ne írj „varázsszámot” (4.3).

🐞 2. Lili egyetlen „legjobbja”

Lili a Felső 10% szabályt az egész oszlopra tette, az alul lévő Összesen sorral együtt. Csak az Összesen cella lett zöld, egyetlen diák sem.
Mit tegyen?
Az összeg a legnagyobb érték a tartományban, ezért egyedül az került a felső 10%-ba (1. rész, 1. lépés). Lili a Szabálykezelőben szűkítse az Érvényesség tartományát csak a pontszámokra (fejléc és összesítő nélkül). Ugyanez a hiba a diagramoknál is előfordul (6.1).

Összefoglalás

  • Feltételes formázás: a cella formátuma az értékétől függ, és vele együtt változik.
  • Cellakiemelési és felső/alsó szabályok; adatsáv, színskála, ikonkészlet.
  • A határ bemeneti cellából (=$F$1), ne fix számként.
  • Szabálykezelő: szerkesztés, érvényesség, sorrend (felül az erősebb), törlés.
  • Ne csak színnel emelj ki; csak az adatokat jelöld ki, fejléc és összesítő nélkül.

Ellenőrizd magad!

Hol módosíthatod egy meglévő szabály határát?
Feltételes formázás → Szabályok kezelése → Szabály szerkesztése.
Melyik szabály érvényesül, ha két szabály ugyanazt a tulajdonságot állítja?
A listában felül álló.
Miért jobb a határt bemeneti cellába írni?
Mert így a határ módosításakor a kiemelés is automatikusan frissül.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (formázás, feltételtől függő megjelenítés), Adatok értelmezése és megjelenítése, digitális akadálymentesség (színtévesztés). Az NKP-okostankönyvre épülő emelt szóbeli tétel „Feltételes formázás alkalmazása” elemének előkészítése (a képletes változat: 8.8).

Módszertan

A lecke a feltételes formázást „élő” kiemelésként mutatja be, és visszautal a 6.9-re, ahol a lebegő nyíl hiányosságát már tapasztalták a diákok. A jóslás a szabályok sorrendjének és ütközésének kevéssé ismert logikáját fedezteti fel. A bemeneti cellás határ a 4.3 „varázsszám nélkül” elvét viszi át a formázásra. Az akadálymentességi rész a színtévesztők szempontját és a nyomtathatóságot is figyelembe veszi.

Időterv

Kiemelési szabályok: 9 perc. Adatsáv, színskála, ikon: 7 perc. A Szabálykezelő és jóslás: 9 perc. Nem csak színnel: 3 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 8.8 Feltételes formázás saját képlettel

Egész sorok kiemelése, több oszlopot figyelő feltételek: a feltételes formázás képlettel, vegyes hivatkozással.

Hibakezelés HAHIBA függvénnyel az Excelben

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

Excel tanfolyam · 8.6 lecke

Hibakezelés HAHIBA függvénnyel

Egy osztálynak még nincs dolgozata, ezért az átlaga helyén #ZÉRÓOSZTÓ! áll. A sportnapon valaki nem indult, és az eltérés oszlopában #ÉRTÉK! jelent meg. Ezek nem hibás képletek, hanem várható helyzetek, amelyeket illik szépen kezelni. A HAHIBA függvény a hibaérték helyett egy általad megadott értéket mutat. De vigyázat: ugyanilyen csendben a valódi hibáidat is eltüntetheti.

  • ⏱ kb. 45 perc
  • 🎓 haladóbb
  • 🎒 9. osztálytól

A lecke végére

  • használod a HAHIBA és a HAHIÁNYZIK függvényt;
  • eldöntöd, mikor várható és mikor valódi egy hiba;
  • beszédes üzenetet vagy üres cellát adsz a hibaérték helyett;
  • pontosabb alternatívaként HA-val és SZÁM-mal kezeled a hibát;
  • elkerülöd, hogy a HAHIBA elrejtse a valódi hibákat.

Amit már tudsz

  • a hibaértékeket és jelentésüket (4.9);
  • a sportnapi eltérés-csapdát (5.8);
  • a HA függvényt és az üres eredményt (8.2);
  • a SZÁM függvényt (7.6).

A HAHIBA

HAHIBA(érték;érték_ha_hiba): ha az első argumentum (általában egy képlet) hibaértéket ad, a második argumentumot mutatja; ha nem, az első eredményét.
KépletHa nincs hibaHa hiba van
=HAHIBA(B2/C2;"nincs adat")a hányados„nincs adat” (például ha C2 üres vagy 0)
=HAHIBA(ÁTLAG(C2:C9);"")az átlagüres cella (ha nincs egy szám sem)
=HAHIBA(ABS(D2-$H$2);"")az eltérésüres cella (ha a D2-ben „nem indult” szöveg áll)

💡 Testvérek

HAHIÁNYZIK(érték;érték_ha_hiányzik) csak a #HIÁNYZIK hibát kezeli, amelyet főleg a keresőfüggvények adnak, ha nem találnak valamit (10. fejezet); a többi hibát továbbra is mutatja. A HIBÁS(érték) függvény IGAZ-at ad, ha az érték bármilyen hibaérték: ez a HA feltételeként használható.

Várható hibák

A HAHIBA akkor jó választás, ha előre tudod, milyen helyzetben keletkezik a hiba, és az adat szempontjából teljesen rendben van:
  • Még nincs adat: egy osztálynak nincs dolgozata, ezért az átlag #ZÉRÓOSZTÓ!. Helyette: „nincs adat” vagy üres cella.
  • Szándékos szöveg egy számoszlopban: a sportnapon a „nem indult” bejegyzés miatt az eltérés #ÉRTÉK! (5.8). Helyette: üres cella, és így a MIN is működik.
  • Keresés sikertelen: egy kód nem szerepel a táblázatban (#HIÁNYZIK, 10.2). Helyette: „ismeretlen kód”.

✅ Üres cella vagy szöveg?

Ha a cellával tovább számolsz (átlag, MIN, MAX), az üres eredmény ("") a jobb, mert ezeket a függvények kihagyják. Ha az ember olvassa, egy beszédes szöveg („nincs adat”) a jobb. 0-t soha ne adj a hiba helyett, ha az nem valódi nulla (5.3).

A HAHIBA veszélye

🔮 Jósold meg!

Peti így írta az összegképletet: =HAHIBA(SZMU(B2:B9);0). Mit lát a cellában?
Megoldás
0-t, minden figyelmeztetés nélkül. A SZMU elírás (SZUM helyett), ezért a belső képlet #NÉV? hibát ad (4.9), a HAHIBA pedig ezt is „kezeli”, és a megadott 0-t mutatja. Így egy valódi hiba eltűnt, és Peti hibás összeggel dolgozik tovább. Ezért a HAHIBA-t mindig utoljára tedd a képletre, miután meggyőződtél róla, hogy a képlet hibaérték nélkül jól működik, és csak a várható hibák maradtak.

Pontosabb hibakezelés

Gyakran jobb, ha nem a hibát „takarod el”, hanem megelőzöd, a hiba okát vizsgálva a HA függvénnyel:
HelyzetHAHIBA-valPontosabban, HA-val
osztás nullával=HAHIBA(B2/C2;"")=HA(C2=0;"";B2/C2)
szöveg a számok közt=HAHIBA(ABS(D2-$H$2);"")=HA(SZÁM(D2);ABS(D2-$H$2);"")
nincs adat az átlaghoz=HAHIBA(ÁTLAG(C2:C9);"nincs adat")=HA(DARAB(C2:C9)=0;"nincs adat";ÁTLAG(C2:C9))
A HA-s változat csak a megnevezett helyzetet kezeli; ha valami más hiba történik (például egy elírt függvénynév), az továbbra is látszik.

Kihívás: rendet a hibák között

🛠️ 1. A sportnap ígérete

Az 5.8 mini projektben azt írtuk: „A 8. fejezetben a HA függvénnyel megoldod, hogy a képlet maga ismerje fel a hiányzó eredményt.” Itt az idő!
  1. Vedd elő a sportnapi táblázatot, és az iskolai csúcstól való eltérés oszlopában (ahol most #ÉRTÉK! vagy félrevezető szám van) írd át a képletet kétféleképpen: HAHIBA-val és HA(SZÁM(…))-mal.
  2. Melyik változat ad helyes eredményt az üres cellánál is? (Gondold végig: az üres cella nem hibát, hanem 0-t ad az ABS-ben!)
  3. Ellenőrizd: működik most a legkisebb eltérést kereső MIN?

📊 2. Osztályátlagok

  1. Készíts egy kis összesítőt négy osztállyal, ahol az egyik osztálynak még nincs pontszáma.
  2. Számold ki az osztályátlagokat segédoszloppal (8.2) vagy egyszerű tartományokkal, és kezeld a hiányzó osztályt „nincs adat” felirattal, először HAHIBA-val, aztán HA és DARAB párossal.
  3. Írj szándékosan egy elírt függvénynevet az egyik képletbe. Melyik változat mutatja meg a hibát, és melyik rejti el?

Hibavadászat

🐞 1. Peti nullás összege

Peti minden képletét HAHIBA-ba csomagolta, „hogy ne legyenek csúnya hibaüzenetek”. A végösszeg 0 lett, és nem érti, miért.
Mi a megoldás?
Valamelyik képletben valódi hiba van (például elírt függvénynév vagy hibás hivatkozás), amelyet a HAHIBA 0-ra cserélt (3. rész). Peti vegye le ideiglenesen a HAHIBA-kat, javítsa a valódi hibákat, és csak a várható hibákat kezelje HAHIBA-val vagy HA-val.

🐞 2. Lili lehúzott átlaga

Lili a hiányzó mérések miatti hibákat így kezelte: =HAHIBA(B2/C2;0). Az oszlop átlaga sokkal kisebb lett a valóságosnál.
Mit tegyen?
A 0 valódi számként beszámít az átlagba (5.3, 2. rész). Lili a hiba helyére üres eredményt tegyen: =HAHIBA(B2/C2;""), így az ÁTLAG kihagyja ezeket a cellákat.

Összefoglalás

  • =HAHIBA(érték;érték_ha_hiba): hibaérték helyett a megadott értéket mutatja.
  • HAHIÁNYZIK: csak a #HIÁNYZIK hibát kezeli; HIBÁS: IGAZ, ha hibaérték.
  • Várható hibához jó (nincs még adat, szándékos szöveg, sikertelen keresés).
  • A HAHIBA a valódi hibákat is elrejti: előbb tesztelj, utoljára csomagolj.
  • Pontosabb: HA-val a hiba okát vizsgálni (C2=0, SZÁM, DARAB=0); a hiba helyére "" vagy szöveg, ne 0.

Ellenőrizd magad!

Mit ad =HAHIBA(10/0;"nem értelmezhető")?
nem értelmezhető
Mit ad =HAHIBA(10/2;"nem értelmezhető")?
5
Miért veszélyes minden képletet HAHIBA-ba csomagolni?
Mert a valódi hibákat (elírás, rossz hivatkozás) is elrejti.
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (logikai függvények, hibakezelés); kapcsolódás a programozáshoz (kivételkezelés, try–except, és annak felelős használata). Az érettségi feladatokban gyakori elvárás, hogy hibaérték ne jelenjen meg az eredménycellákban.

Módszertan

A lecke a hibakezelést kétirányú döntésként tanítja: mikor várható a hiba, és mikor jelez valódi problémát. A jóslás a HAHIBA legfontosabb kockázatát (a valódi hibák elrejtése) teszi tapasztalattá, és a 4.9 hibakeresési tudására épít. A HA-s alternatíva a hiba okának vizsgálatára, a pontosabb, célzott hibakezelésre nevel. A kihívás lezárja az 5.8-ban nyitott kérdést, így a tananyag ívét is láthatóvá teszi.

Időterv

A HAHIBA: 6 perc. Várható hibák: 6 perc. A HAHIBA veszélye és jóslás: 7 perc. Pontosabb hibakezelés: 7 perc. Kihívás: 15 perc. Hibavadászat: 4 perc.

Következő lecke: 8.7 Feltételes formázás alapjai

A cellák maguktól színeződnek az értékük szerint: kiemelési szabályok, adatsávok, színskálák és ikonkészletek.