Excel táblázatkezelés kezdőknek › 10.8 lecke
Excel tanfolyam · 10.8 lecke
Hiányzó találatok és hibák kezelése
A rendelésben öt sorban #HIÁNYZIK áll, és emiatt a végösszeg is hibás. Ilyenkor két dolgot kell tenned, ebben a sorrendben: először kideríteni, miért nincs találat, és csak utána eldönteni, hogyan jelenjen meg a hiány. Ebben a leckében megtanulod a hiányzó találatok öt leggyakoribb okát felismerni, és a valóban hiányzó adatokat úgy kezelni, hogy a valódi hibák közben ne tűnjenek el.
- ⏱ kb. 45 perc
- 🎓 haladóbb
- 🎒 10. osztálytól
A lecke végére
- felismered a #HIÁNYZIK öt leggyakoribb okát;
- lépésenként felderíted egy hiányzó találat okát;
- a valóban hiányzó adatot beszédes üzenettel kezeled;
- a HAHIÁNYZIK-ot választod a HAHIBA helyett;
- kiemeled az ismeretlen kódokat, és ellenőrzöd a végösszeget.
Amit már tudsz
Az öt ok
| Ok | Tünet | Javítás |
|---|---|---|
| 1. Valóban hiányzik | a kód tényleg nincs az árlistában (új termék, elírás a rendelésben) | pótolni az árlistában, vagy javítani a rendelést; ha szándékos, kezelni (3. rész) |
| 2. Típuseltérés | az egyik oldalon szám, a másikon szövegként tárolt szám (zöld háromszög, balra igazítás) | egységesíteni a típust (2.8, 7.6) |
| 3. Szóköz vagy rejtett karakter | szemmel ugyanaz, mégsem egyezik („C02 ”) | KIMETSZ, TISZTÍT (7.6) |
| 4. Rögzítetlen tábla | a felső sorok jók, lefelé egyre több a hiba | dollárjel a táblán (10.2) |
| 5. Túl rövid tartomány | az árlista végére felvett új termékeket nem találja | a tartomány bővítése (vagy Excel-tábla, 14.4) |
⚠️ A fordított hiba: van találat, de rossz
Ha az FKERES negyedik argumentuma elmarad (10.2), vagy a HOL.VAN-é (10.5), a közelítő egyezés nem ad #HIÁNYZIK-ot, hanem egy rossz sort. Ez még alattomosabb, mert nincs hibaüzenet. Ezért pontos kereséshez mindig írd ki a HAMIS-t vagy a 0-t.
Felderítés
Ha egy kódhoz nincs találat, de szerinted kellene lennie, egy segédcellában vizsgáld meg lépésről lépésre (a példában a rendelés A5 cellája, az árlistában a kód az A8-ban szemmel látható):
| Kérdés | Képlet | Ha HAMIS vagy eltér… |
|---|---|---|
| Tényleg egyezik a két cella? | =A5=Árlista!A8 | van köztük eltérés |
| Ugyanolyan hosszúak? | =HOSSZ(A5) és =HOSSZ(Árlista!A8) | szóköz vagy rejtett karakter (3. ok) |
| Mindkettő szám vagy mindkettő szöveg? | =SZÁM(A5) és =SZÁM(Árlista!A8) | típuseltérés (2. ok) |
| Benne van a keresési tartományban? | kattints a képletre, és nézd meg a színes keretet | rögzítetlen vagy rövid tábla (4–5. ok) |
Kezelés
Ha a hiány valódi (a kód tényleg nincs az árlistában), a hibaérték helyett beszédes üzenet jelenjen meg:
| Módszer | Képlet | Mit kezel? |
|---|---|---|
| HAHIÁNYZIK (ajánlott) | =HAHIÁNYZIK(FKERES(A2;Árlista!$A$2:$C$50;3;HAMIS);"ismeretlen kód") | csak a #HIÁNYZIK hibát |
| HAHIBA | =HAHIBA(FKERES(…);"ismeretlen kód") | minden hibát, a valódiakat is (8.6) |
| XKERES | =XKERES(A2;Árlista!$A$2:$A$50;Árlista!$C$2:$C$50;"ismeretlen kód") | csak a hiányzó találatot (beépítve) |
🔮 Jósold meg!
Peti az árlista C oszlopából akart árat, de véletlenül 4-es oszlopszámot írt egy háromoszlopos táblára. Mit lát a cellában (a) =HAHIBA(FKERES(A2;$F$2:$H$20;4;HAMIS);"ismeretlen kód") (b) =HAHIÁNYZIK(FKERES(A2;$F$2:$H$20;4;HAMIS);"ismeretlen kód") esetén?
Megoldás
(a) Minden sorban „ismeretlen kód” áll, pedig a kódok léteznek. A rossz oszlopszám valójában #HIV! hibát ad (10.2), de a HAHIBA ezt is eltakarja, és félrevezető üzenetet mutat. (b) #HIV! látszik: a HAHIÁNYZIK csak a hiányzó találatot kezeli, a képlet valódi hibáját megmutatja. Ezért keresőfüggvényeknél a HAHIÁNYZIK a jobb választás.
Kiemelés és ellenőrzés
- Ismeretlen kódok kiemelése: jelöld ki a rendelés kódoszlopát (A2:A30), és képletes feltételes formázással (8.8) emeld ki narancssárgával:
=DARABTELI(Árlista!$A$2:$A$50;$A2)=0. Így már a kód beírásakor látszik, ha nincs az árlistában. - Hány ismeretlen van?
=DARABTELI(D2:D30;"ismeretlen kód") - Figyelmeztetés a végösszeg mellett:
=HA(DARABTELI(D2:D30;"ismeretlen kód")>0;"Figyelem: a végösszeg hiányos!";"")
⚠️ A szöveg csendben kimarad az összegből
Ha az ár helyén „ismeretlen kód” szöveg áll, a SZUM egyszerűen kihagyja (5.2), és a végösszeg kisebb lesz, hibaüzenet nélkül. A beszédes üzenet önmagában nem elég: a végösszeg mellett jelezd, hogy hiányzó adat van benne (fent), vagy amíg van ismeretlen kód, ne is számolj végösszeget.
Kihívás: a hibás rendelés kivizsgálása
🕵️ Derítsd ki, és javítsd!
- Vedd elő a 10.2-ben készült rendelést és árlistát. „Rontsd el” szándékosan öt sorát, mindegyiket másképp: (1) nem létező kód; (2) számként tárolt kód a szövegesek között (vagy fordítva); (3) szóköz a kód végén; (4) egy új termék az árlista végén a táblán kívül; (5) egy sor, ahol a tábla nincs rögzítve.
- Nézd meg, melyik sorban jelenik meg
#HIÁNYZIK. - Minden hibás sornál derítsd ki az okot a 2. rész vizsgálataival, és írd fel egy „Napló” oszlopba (7.10).
- Javítsd a 2–5. okot a forrásnál (típus, szóköz, tartomány, rögzítés).
- Az 1. okot (valóban hiányzó kód) kezeld HAHIÁNYZIK-kal (vagy XKERES-szel) „ismeretlen kód” üzenettel.
- Emeld ki feltételes formázással az ismeretlen kódokat, és tegyél figyelmeztetést a végösszeg mellé.
Hibavadászat
🐞 1. Peti „mindenki ismeretlen” listája
Peti az összes keresést HAHIBA-ba tette. Egy oszlopbeszúrás után a rendelés minden sorában „ismeretlen kód” áll, pedig egyetlen kód sem változott.
Mi a megoldás?
Az oszlopbeszúrás miatt az FKERES oszlopszáma rossz lett (10.2), és a képlet valódi hibát ad, amelyet a HAHIBA „ismeretlen kód”-nak álcázott (3. rész). Peti javítsa az oszlopszámot (vagy számolja HOL.VAN-nal, 10.5, vagy használjon INDEX + HOL.VAN-t, 10.7), és cserélje a HAHIBA-t HAHIÁNYZIK-ra.
🐞 2. Lili túl kicsi végösszege
Lili szépen kezelte a hiányzó kódokat („ismeretlen kód”), de a rendelés végösszege 3200 Ft-tal kevesebb lett, mint amit a pénztárban fizettek, és senki nem vette észre.
Mit tegyen?
A SZUM kihagyta az „ismeretlen kód” szövegű sorokat, ezért a végösszegből hiányzik az áruk (4. rész). Lili számolja meg az ismeretlen kódokat, és tegyen figyelmeztetést a végösszeg mellé; utána pótolja a hiányzó termékeket az árlistában.
Összefoglalás
- #HIÁNYZIK okai: valóban hiányzik, típuseltérés, szóköz, rögzítetlen tábla, rövid tartomány.
- Előbb derítsd ki az okot (=A5=…, HOSSZ, SZÁM), és a forrást javítsd.
- A valódi hiányt HAHIÁNYZIK-kal vagy az XKERES negyedik argumentumával kezeld; a HAHIBA a valódi hibákat is eltakarja.
- Az ismeretlen kódokat emeld ki feltételes formázással.
- A szöveges üzenet kimarad a SZUM-ból: jelezd a végösszeg mellett.
Ellenőrizd magad!
Mit kezel a HAHIÁNYZIK, és mit nem?
Csak a #HIÁNYZIK hibát; a többi hibát (például #HIV!, #NÉV?) megmutatja.
Hogyan ellenőrzöd, hogy két, szemre egyforma kód tényleg egyezik-e?
=A5=Árlista!A8, illetve a HOSSZ függvénnyel.
Miért veszélyes az „ismeretlen kód” szöveg a végösszegnél?
Mert a SZUM kihagyja, és a végösszeg hibaüzenet nélkül kisebb lesz.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények, hibakezelés, feltételes formázás), adatminőség; kapcsolódás a programozáshoz (hibakeresés, a hiba okának szisztematikus felderítése, célzott kivételkezelés).
Módszertan
A lecke a hibakezelést két lépésre bontja: előbb diagnózis, utána kezelés. Az okok táblázata és a felderítő képletek szisztematikus hibakeresési eljárást adnak, amely a 7. fejezet adattisztítását alkalmazza. A jóslás a HAHIBA és a HAHIÁNYZIK különbségét a 8.6 tanulságára építve mutatja meg. Lili esete a „csendes” adatvesztésre (szöveg a SZUM-ban) hívja fel a figyelmet, amely a felelős adatkezelés egyik alapkérdése.
Időterv
Az öt ok: 7 perc. Felderítés: 7 perc. Kezelés és jóslás: 8 perc. Kiemelés és ellenőrzés: 5 perc. Kihívás: 14 perc. Hibavadászat: 4 perc.
Következő lecke: 10.9 Mini projekt: árlista és rendelés összekapcsolása
A 10. fejezet zárása: egy iskolai büférendelés teljes feldolgozása keresőfüggvényekkel.
