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

Excel tanfolyam · 13.7 lecke

Képletek, hivatkozások és szélsőértékek ellenőrzése

A táblázat kész, a nyomtatási kép rendben, a PDF elkészült. De biztosan jók a számok? Egy beírt szám egy képletoszlop közepén, egy elcsúszott hivatkozás, egy hiányzó adat, amelyből csendben „részvétel” minősítés lett: ezek nem okoznak hibaüzenetet, csak rossz eredményt. Ebben a leckében a 4.9 hibakeresési eszközeit egy átadás előtti ellenőrzési eljárássá bővítjük, és megtanulod a képleteket „kínzó” szélsőértékekkel próbára tenni.

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

A lecke végére

  • megjeleníted és átnézed a munkalap képleteit;
  • megtalálod a következetlen képleteket és a beírt számokat;
  • elődökkel és utódokkal követed a hivatkozásokat;
  • kontrollösszegekkel ellenőrzöd az eredményeket;
  • szélsőértékekkel teszteled a képleteidet.

Amit már tudsz

  • a hibaértékeket és az elődök mutatását (4.9);
  • a határértékek tesztelését (8.5);
  • a Képletkiértékelőt (12.7);
  • a kereszttábla belső ellenőrzését (12.8).

Képletek átnézése

  • Képletek megjelenítése: Képletek → Képletvizsgálat → Képletek megjelenítése (vagy Ctrl + `, a gravis billentyű). Az értékek helyett a képletek látszanak, így egy oszlopban azonnal feltűnik, ha valamelyik cella eltér a többitől. Ugyanígy kapcsolod vissza.
  • Következetlen képlet: ha egy képlet eltér a szomszédos képletek mintájától, az Excel a cella bal felső sarkában zöld háromszöggel jelzi. A cella melletti figyelmeztető ikonra kattintva látod az okot („Ez a képlet eltér a környező képletektől”).
  • Beírt számok keresése: egy képletoszlopban a „beégetett” szám a leggyakoribb rejtett hiba. Jelöld ki az oszlopot → Kezdőlap → Keresés és kijelölés → Irányított kijelölés… → Állandók → csak a Számok. Az Excel kijelöli a nem képletes számokat: egy képletoszlopban ott nem lehetne egy sem.
  • Hibaellenőrzés: Képletek → Képletvizsgálat → Hibaellenőrzés: végiglépked a munkalap hibát tartalmazó vagy gyanús cellái között.

Hivatkozások követése

EszközMit mutat?Mire jó?
Elődök mutatása (4.9)kék nyilakkal: melyik cellákból számol a kijelölt képletjó tartományra hivatkozik-e egy összegzés
Utódok mutatásamelyik képletek használják a kijelölt cellátmi romlik el, ha egy bemeneti cellát (például a ponthatárt) átírsz vagy törölsz
Nyilak eltávolításaeltünteti a nyilakata vizsgálat végén
Névkezelő (12.1)a nevek hivatkozásaia nevek ugyanakkora tartományra mutatnak-e (12.2)

💡 Utód nélküli bemenet

Ha egy bemeneti cellának (például hatar, afa) nincs utódja, egyetlen képlet sem használja: vagy fölösleges, vagy valahol a képletbe „varázsszám” került helyette (4.3). Ez egy gyors, de hatékony ellenőrzés.

Kontrollösszegek

Egy jó munkafüzetben az eredmények „összeillenek”. Készíts egy kis ellenőrző területet, amely IGAZ vagy HAMIS értékkel jelzi, rendben vannak-e:

EllenőrzésKéplet (például a 12.9 munkafüzetén)
a kereszttábla végösszege = a teljes összeg=S5=SZUM(osszes)
a kategóriák darabszáma = a versenyzők számaa négy DARABTELI-eredmény (arany, ezüst, bronz, részvétel) egy-egy cellában, alattuk: =SZUM(U2:U5)=DARAB2(nev)
nincs hibaérték a listában=SZORZATÖSSZEG(--HIBA.E(A2:J11))=0 (a HIBA.E minden hibaértékre IGAZ-at ad, a -- ezt 1-gyé alakítja, a SZORZATÖSSZEG összeadja)
a kereszttábla oszlopösszegei együtt = az egész=SZUM(Q5:R5)=SZUM(osszes) (a lány és a fiú oszlop összege)

✅ Józan ész

A legegyszerűbb ellenőrzés: nagyságrend. Egy osztály átlaga nem lehet nagyobb a maximális pontszámnál, egy részösszeg nem lehet nagyobb az egésznél, egy darabszám nem lehet törtszám, egy életkor nem lehet negatív. Ha valamelyik mégis, ott hiba van.

Szélsőérték-tesztek

Egy képlet a „szokásos” adatokon általában jól működik. A hibák a szélein bújnak meg: a határokon, a nullánál, a hiányzó és a hibás adatoknál. Készíts egy kis próbatáblát a lista mellé (vagy egy másolaton), írd be a próbaértékeket és a várt eredményt, majd hasonlítsd össze a képlet eredményével (8.1).

Példa: a 12.9 minősítő képlete: =FKERES(F2;hatarok;2;IGAZ), a határtábla: 0 részvétel, 35 bronz, 42 ezüst, 48 arany.

PróbaértékMiért ez?Várt eredményA képlet eredménye
0a legkisebb lehetséges értékrészvételrészvétel ✅
34 / 35a bronz határa alatt és pontosan rajtarészvétel / bronzrészvétel / bronz ✅
41 / 42az ezüst határabronz / ezüstbronz / ezüst ✅
48 / 50az arany határa és a maximumarany / aranyarany / arany ✅
−1lehetetlen értékhibajelzés#HIÁNYZIK (10.3)
(üres)hiányzó adatüres vagy „hiányos”? (jóslás)
xszöveg szám helyetthibajelzés#HIÁNYZIK

🔮 Jósold meg!

Egy versenyző 2. fordulós pontszáma még nincs beírva (az E cella üres). Mit mutat az Összesen (=D2+E2) és a minősítés, ha az 1. fordulóban 30 pontot ért el?

Megoldás

Az Összesen 30 (az üres cellát az Excel 0-nak veszi), a minősítés „részvétel”, hibaüzenet nélkül. Ez rejtett hiba: úgy tűnik, mintha a versenyző gyengén szerepelt volna, pedig csak hiányzik az adata. Javítás: =HA(DARAB(D2:E2)<2;"hiányos";D2+E2) az Összesenben, és a minősítésnél =HA(SZÁM(F2);FKERES(F2;hatarok;2;IGAZ);"–"). A hiányzó adatot jelezni kell, nem nullának venni.

💡 Mit érdemes kipróbálni?

A legkisebb és a legnagyobb lehetséges értéket; minden határt alatta, rajta és fölötte; a nullát; az üres cellát; a szöveget; a negatív számot; és ha van, a dátumot (11.5). A programozásban ezt tesztesetnek nevezik: a jó teszteset az, amelyik megtalál egy hibát.

Kihívás: átadás előtti ellenőrzés

🔎 Ellenőrizd a 12.9 munkafüzetet!

  1. Kapcsold be a Képletek megjelenítését, és nézd át oszloponként: minden sorban ugyanaz a minta?
  2. Az Irányított kijelöléssel keress beírt számokat az Összesen, a Javulás és a Helyezés oszlopban.
  3. Jelöld ki a hatarok tábla egyik celláját, és mutasd az utódait. Mely képletek függnek tőle?
  4. Készíts egy ellenőrző területet három kontrollösszeggel (3. rész). Mindhárom IGAZ?
  5. Egy másolaton készíts próbatáblát a minősítés és a továbbjutás képletéhez a 4. rész szerint, és javítsd a hiányzó adatok kezelését.
  6. Írj egy rövid „ellenőrzési jegyzőkönyvet” a munkafüzet egy külön lapjára: mit vizsgáltál, mit találtál, mit javítottál (7.10 naplójának mintájára).

Hibavadászat

🐞 1. Peti beégetett száma

Peti a „Javulás” oszlopban az egyik sorba, ahol a képlet hibát adott, „gyorsan beírta” a 3-at. Utána módosította a versenyző pontszámait, de a javulás 3 maradt.

Mi a megoldás?

A beírt szám nem képlet, nem frissül (1. rész). Az Irányított kijelölés → Állandók megtalálja. Peti írja vissza a képletet (a fölötte lévő cellából kitöltve), és derítse ki, miért adott hibát (például szöveg volt a pontszám helyén, 7.6).

🐞 2. Lili „gyenge” versenyzője

Lili listáján egy versenyző „részvétel” minősítést kapott 14 ponttal, pedig a tanár szerint jól szerepelt. Kiderült: a második fordulós eredménye még nem volt beírva.

Mit tegyen?

Az üres cella 0-nak számított (4. rész, jóslás). Lili kezelje a hiányzó adatot a képletekben („hiányos”), és emelje ki feltételes formázással a hiányos sorokat (8.8). Egy szélsőérték-teszt az üres cellával előre megmutatta volna a hibát.

Összefoglalás

  • Képletek megjelenítése (Ctrl + `): az oszlopok mintája átnézhető; zöld háromszög: következetlen képlet.
  • Irányított kijelölés → Állandók: megtalálja a képletoszlopba beírt számokat.
  • Elődök és utódok: honnan számol egy képlet, és mi függ egy bemeneti cellától.
  • Kontrollösszegek és józan ész: az eredményeknek össze kell illeniük.
  • Szélsőérték-tesztek: 0, határértékek, maximum, üres cella, szöveg, negatív szám; a hiányzó adatot jelezni kell.

Ellenőrizd magad!

Hogyan kapcsolod be a képletek megjelenítését?

Képletek → Képletvizsgálat → Képletek megjelenítése, vagy Ctrl + `.

Mit vesz az Excel egy üres cellának egy összeadásban?

0-nak.

Milyen értékeket próbálsz ki egy 40 pontos határnál?

39-et, 40-et és 41-et (alatta, rajta, fölötte).

👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (képletek ellenőrzése, hibakeresés), adatminőség és az eredmények kritikus értékelése; kapcsolódás a programozáshoz (tesztelés, tesztesetek, határérték-analízis, „defenzív” programozás a hiányzó adatok kezelésével).

Módszertan

A lecke a 4.9 eszközeit egy átadás előtti ellenőrzési eljárássá szervezi: képletek → hivatkozások → kontrollösszegek → szélsőértékek. A próbatábla a programozás tesztelési szemléletét (határérték-analízis) viszi át a táblázatkezelésbe. A jóslás a hiányzó adat csendes nullázásának veszélyét mutatja meg, amely a felelős adatkezelés egyik alapkérdése. Az ellenőrzési jegyzőkönyv a reflektív munkát és a dokumentálást fejleszti.

Időterv

Képletek átnézése: 8 perc. Hivatkozások követése: 6 perc. Kontrollösszegek: 6 perc. Szélsőérték-tesztek és jóslás: 10 perc. Kihívás: 12 perc. Hibavadászat: 3 perc.

Következő lecke: 13.8 Mini projekt: nyomtatható költségkimutatás

A 13. fejezet zárása: egy iskolai sportnap költségkimutatása, ellenőrizve, nyomtatásra és PDF-re készen.