„KISS ANNA”, „kiss anna”, „Kiss Anna ”: ugyanaz a név háromféleképpen. Egy telefonszám hol szóközökkel, hol kötőjelekkel szerepel. Egy angol forrásban pont a tizedesjel. A 7.6-ban a Keresés és cserével és a KIMETSZ-szel már tisztítottunk; most képletekkel tesszük ugyanezt, amelyek új adatoknál is maguktól működnek. Megismered a HELYETTE és a CSERE függvényt, és a betűméretet átalakító függvényeket is.
⏱ kb. 45 perc
🎓 haladóbb
🎒 10. osztálytól
Kép helye (16:9)Két oszlop egymás mellett: bal oldalon „piszkos” adatok („ KISS anna ”, „+36 30 123 4567”, „3.5”), jobb oldalon a megtisztított változatok („Kiss Anna”, „+36301234567”, 3,5); a szerkesztőlécen =TNÉV(KIMETSZ(A2)).Javasolt alternatív szöveg: „Szövegtisztítás képletekkel”
A lecke végére
KIMETSZ-szel és betűméret-függvényekkel egységesítesz;
Mit ad a =TNÉV("KISS ANNA-MÁRIA") és a =TNÉV("o'brien de la cruz")?
Megoldás
„Kiss Anna-Mária”: a TNÉV minden olyan betűt nagybetűsít, amely nem betű után áll, így a kötőjel utánit is. „O'Brien De La Cruz”: az aposztróf után is nagybetű lesz, és a „de la” is nagy kezdőbetűt kap, pedig ezek általában kisbetűsek. A TNÉV jó kiindulás, de nem ismeri a nyelvi szabályokat: különleges nevek után ellenőrizd szemmel.
2
HELYETTE
HELYETTE(szöveg;régi_szöveg;új_szöveg;[melyiket]): a régi részletet az újra cseréli, tartalma alapján. Ha a negyedik argumentumot elhagyod, minden előfordulást cserél; ha megadod (például 2), csak azt az előfordulást.
Feladat
Képlet
Eredmény
szóközök eltávolítása a telefonszámból
=HELYETTE(A2;" ";"")
+36 30 123 4567 → +36301234567
tizedespont cseréje, számmá alakítás (7.6)
=ÉRTÉK(HELYETTE(A2;".";","))
„3.5” → 3,5
egységes osztályírás
=HELYETTE(A2;" ";"")
„9. b” → „9.b”
csak a második kötőjel cseréje
=HELYETTE(A2;"-";"/";2)
KON-2026-0412 → KON-2026/0412
⚠️ A HELYETTE betűérzékeny
A HELYETTE megkülönbözteti a kis- és nagybetűt: a =HELYETTE(A2;"utca";"u.") az „Utca” szót nem cseréli. Ha mindkettőt cserélni kell, előbb egységesítsd a betűméretet, vagy ágyazz egymásba két HELYETTE-t.
3
CSERE
CSERE(szöveg;honnan;hány_karakter;új_szöveg): a megadott pozíciótól ennyi karaktert cserél le az új szövegre, függetlenül attól, mi áll ott.
Feladat
Képlet
Eredmény
telefonszám maszkolása (adatvédelem)
=CSERE(A2;6;4;"****")
+36301234567 → +3630****567
az évszám cseréje egy kódban
=CSERE(A2;5;4;"2027")
KON-2026-0412 → KON-2027-0412
beszúrás (0 karakter cseréje)
=CSERE(A2;3;0;"-")
9B017 → 9B-017
💡 HELYETTE vagy CSERE?
Ha tudod, mit akarsz cserélni (egy szót, egy karaktert), a HELYETTE kell. Ha tudod, hol (hányadik karaktertől, hány karaktert), a CSERE.
4
Hasznos fogások
Hány szóköz van a szövegben? Hasonlítsd össze a szöveg hosszát a szóközök nélküli hosszal: =HOSSZ(A2)-HOSSZ(HELYETTE(A2;" ";"")). A „Kovács Anna Mária” esetén 2. A szavak száma (KIMETSZ után) ennél eggyel több: =HOSSZ(KIMETSZ(A2))-HOSSZ(HELYETTE(KIMETSZ(A2);" ";""))+1 → 3.
Ugyanez bármely karakterre: hány „a” betű van egy szövegben? =HOSSZ(A2)-HOSSZ(HELYETTE(KISBETŰ(A2);"a";"")).
Ékezetmentes azonosító (a 11.1 bónuszkérdésére): több HELYETTE egymásba ágyazva, a kisbetűs változaton: =HELYETTE(HELYETTE(HELYETTE(HELYETTE(KISBETŰ(A2);"á";"a");"é";"e");"ö";"o");"ő";"o"). A teljes változathoz az í, ó, ú, ü, ű cseréje is kell (összesen 9 HELYETTE). Hosszú, de egyszer kell megírni, utána lefelé tölthető.
✅ Tisztító „futószalag”
A tisztítás lépései egy képletbe láncolhatók, belülről kifelé olvasva (5.7): =TNÉV(KIMETSZ(HELYETTE(A2;KARAKTER(160);" "))): előbb a nem törhető szóközt cseréli (7.6), aztán eltávolítja a felesleges szóközöket, végül egységesíti a betűméretet. Ha bonyolult, készíts belőle több segédoszlopot, és ellenőrizd lépésenként.
5
Kihívás: egységes névsor és azonosítók
🧽 Tisztítsd képletekkel!
Vedd elő a 7.10 kérdőíves adatait vagy egy saját „piszkos” névsort: vegyes betűméret, felesleges szóközök, „9b”, „9. b”, „9.B” osztályírás.
A nevekből TNÉV és KIMETSZ segítségével készíts egységes alakot.
Az osztályokat egységesítsd egy képlettel: szóköz eltávolítása, kisbetűssé alakítás, és ha nincs pont a szám után, szúrd be (tipp: =HA(SZÁM(SZÖVEG.KERES(".";B2));…;CSERE(…;2;0;".")), a 10. évfolyamnál gondold végig a pozíciót!).
Számold meg minden névben a szavak számát. Kinek van két utóneve?
Állíts elő ékezetmentes, kisbetűs felhasználónevet: vezetéknév és utónév ponttal összefűzve (például kovacs.peter). (11.2 a szétbontáshoz.)
Maszkold a telefonszámokat úgy, hogy csak az utolsó három számjegy látszódjon.
6
Hibavadászat
🐞 1. Peti szétesett címei
Peti a „Fő utca 12.” típusú címekben az „utca” szót „u.”-ra akarta cserélni: =CSERE(A2;4;4;"u."). A „Fő utca 12.” jó lett, a „Rákóczi utca 5.” viszont „Ráku. utca 5.” lett.
Mi a megoldás?
A CSERE pozíció szerint cserél (3. rész), az „utca” szó viszont minden címben máshol van. Tartalom szerinti cseréhez a HELYETTE kell: =HELYETTE(A2;"utca";"u.").
🐞 2. Lili megmaradt „Utcái”
Lili a =HELYETTE(A2;"utca";"u.") képletet használta. A legtöbb cím jó lett, de ahol nagybetűvel írták („Kossuth Utca 3.”), ott nem változott semmi.
Mit tegyen?
A HELYETTE betűérzékeny (2. rész). Lili ágyazzon egymásba két cserét: =HELYETTE(HELYETTE(A2;"utca";"u.");"Utca";"u."), vagy előbb egységesítse a betűméretet (de a TNÉV az „u.”-t is „U.”-ra alakítaná: gondold végig a sorrendet!).
Összefoglalás
KIMETSZ, NAGYBETŰS, KISBETŰ, TNÉV: egységes szóköz és betűméret.
HELYETTE(szöveg;régi;új;[melyiket]): tartalom szerinti csere, betűérzékeny.
CSERE(szöveg;honnan;hány;új): pozíció szerinti csere; 0 karakterrel beszúrás.
Előfordulások száma: HOSSZ − HOSSZ(HELYETTE(…;"")).
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (szövegkezelés), adatok tisztítása és egységesítése; kapcsolódás a programozáshoz (karakterlánc-műveletek: csere, beszúrás, kis- és nagybetű), az adatvédelemhez (maszkolás) és a magyar nyelv sajátosságaihoz (ékezetek).
Módszertan
A lecke a 7.6 kézi tisztítását képletes, újrafelhasználható megoldásokká fejleszti. A HELYETTE és a CSERE párhuzamos bemutatása a „mit vagy hol?” döntésre épít. A jóslás a TNÉV korlátait (nyelvi szabályok) mutatja meg. A HOSSZ − HOSSZ(HELYETTE) fogás egy elegáns megszámlálási ötlet, amely a programozói gondolkodást fejleszti. A hibavadászat a két függvény összekeverését és a betűérzékenységet dolgozza fel.
A „Kiss Anna” és a „Szabó Bence” névben a vezetéknév más hosszú, ezért a BAL függvénynek nem adhatsz fix darabszámot. Viszont mindkettőben egy szóköz választja el a két nevet. Ha tudod, hányadik karakter a szóköz, és milyen hosszú a teljes név, bármelyik részt ki tudod vágni. Ebben a leckében három függvény segít: a HOSSZ megméri a szöveget, a SZÖVEG.KERES és a SZÖVEG.TALÁL pedig megkeresi benne egy részlet helyét.
⏱ kb. 45 perc
🎓 haladóbb
🎒 10. osztálytól
Kép helye (16:9)A „Szabó Bence” karakterei 1–11 sorszámmal, a 6. pozíción lévő szóköz kiemelve; alatta: SZÖVEG.KERES(" ";A2) = 6, HOSSZ(A2) = 11, BAL(A2;6-1) = „Szabó”, JOBB(A2;11-6) = „Bence”.Javasolt alternatív szöveg: „Név szétbontása a szóköz helye alapján”
A lecke végére
megméred egy szöveg hosszát;
megkeresed egy karakter vagy szövegrész helyét;
megkülönbözteted a SZÖVEG.KERES-t és a SZÖVEG.TALÁL-t;
változó hosszúságú részeket vágsz ki (név, domain);
a keresett részlet első előfordulásának helye; a kis- és nagybetű nem számít, helyettesítő karakter használható (9.6)
=SZÖVEG.KERES(" ";A2)
6
SZÖVEG.TALÁL(keresett;szöveg;[kezdet])
ugyanez, de a kis- és nagybetű számít, helyettesítő karakter nincs
=SZÖVEG.TALÁL("b";A2)
4 (a „b” kisbetű a „Szabó”-ban)
A harmadik argumentum (kezdet) megadja, hányadik karaktertől keressen: így a második előfordulás is megtalálható (3. rész).
Ha a keresett részlet nincs a szövegben, mindkét függvény #ÉRTÉK! hibát ad (4.9).
A különbség egy példán:=SZÖVEG.KERES("B";A2) → 4, mert a kis- és nagybetűt nem különbözteti meg, így a „Szabó” kisbetűs b-je is egyezik. =SZÖVEG.TALÁL("B";A2) → 7, mert csak a „Bence” nagy B-je felel meg.
2
Név szétbontása
A szóköz helye (segédoszlop, B): =SZÖVEG.KERES(" ";A2) → 6.
Vezetéknév: a szóköz előtti karakterek, vagyis eggyel kevesebb, mint a szóköz helye: =BAL(A2;B2-1) → Szabó.
Utónév: a szóköz utáni karakterek: a teljes hossz mínusz a szóköz helye: =JOBB(A2;HOSSZ(A2)-B2) → Bence. Vagy: =KÖZÉP(A2;B2+1;100) (a 100 „bőven elég”, a KÖZÉP nem megy túl a szöveg végén).
Ha jól működik, a segédoszlopot be is építheted: =BAL(A2;SZÖVEG.KERES(" ";A2)-1).
🔮 Jósold meg!
Mit ad a vezetéknév és az utónév képlete (a) „Kovács Anna Mária” (b) „Cher” (egyetlen szó) esetén?
Megoldás
(a) Vezetéknév: „Kovács”, utónév: „Anna Mária”. A SZÖVEG.KERES az első szóközt találja meg (7. hely), a JOBB pedig minden utána lévő karaktert visszaad (17 − 7 = 10 karakter). Két utónévnél ez általában jó is. (b)#ÉRTÉK! mindkettőben, mert nincs szóköz. Kezelés: =HAHIBA(BAL(A2;SZÖVEG.KERES(" ";A2)-1);A2) → ha nincs szóköz, az egész név a vezetéknév (8.6). Előtte érdemes KIMETSZ-szel (7.6, 11.3) eltávolítani a felesleges szóközöket, különben egy elején álló szóköz mindent elront.
3
További minták
Feladat
Képlet
e-mail-cím domainje (anna@iskola.hu → iskola.hu)
=KÖZÉP(A2;SZÖVEG.KERES("@";A2)+1;100)
az e-mail-cím felhasználói része
=BAL(A2;SZÖVEG.KERES("@";A2)-1)
tartalmazza-e a „robot” szót? (IGAZ/HAMIS)
=SZÁM(SZÖVEG.KERES("robot";A2))
a második kötőjel helye (KON-2026-0412)
=SZÖVEG.KERES("-";A2;SZÖVEG.KERES("-";A2)+1) → 9
a kód utolsó része a második kötőjel után
=KÖZÉP(A2;9+1;100) (a 9 helyett a fenti képlet)
💡 „Tartalmazza” két módon
A 9.6-ban a =DARABTELI(A2;"*robot*")>0 képlettel vizsgáltuk ugyanezt. A SZÁM(SZÖVEG.KERES(…)) ugyanazt adja, de a SZÖVEG.TALÁL-lal kis- és nagybetű-érzékeny változat is készíthető. Mindkettő használható a HA feltételeként vagy feltételes formázásban (8.8).
4
Kihívás: névsor és e-mailek
✂️ Bontsd szét az adatokat!
Készíts egy kitalált listát nyolc diákkal egy oszlopban („Vezetéknév Utónév”), köztük legyen egy két utónevű és egy, akinek a neve elején szóköz van. Mellé egy e-mail-cím oszlopot (például kiss.anna@iskola.hu).
Segédoszloppal keresd meg a szóköz helyét, majd bontsd a nevet vezeték- és utónévre.
Mi történik a bevezető szóközös névvel? Javítsd KIMETSZ-szel a forrásnál (7.6).
Az e-mail-címből vágd ki a domaint és a felhasználói részt.
Ellenőrizd: minden domain „iskola.hu”? Számold meg DARABTELI-vel, és emeld ki feltételes formázással az eltérőket.
Bónusz: készíts egy „Monogram” oszlopot (például KA): a vezetéknév és az utónév első betűje.
5
Hibavadászat
🐞 1. Peti szóközös vezetéknevei
Peti képlete: =BAL(A2;SZÖVEG.KERES(" ";A2)). A vezetéknevek jónak tűnnek, de az FKERES (10.2) nem találja meg őket egy másik táblában.
Mi a megoldás?
A BAL a szóköz helyéig vágott, vagyis a szóközt is benne hagyta: „Szabó ” (2. rész). A „Szabó ” és a „Szabó” két különböző szöveg (7.6). Helyesen: =BAL(A2;SZÖVEG.KERES(" ";A2)-1). Ellenőrzés: a HOSSZ függvény eggyel többet mutat a vártnál.
🐞 2. Lili hiányzó találata
Lili a „Nagy Anna” névben az „anna” részletet kereste: =SZÖVEG.TALÁL("anna";A2). Az eredmény #ÉRTÉK!.
Mit tegyen?
A SZÖVEG.TALÁL megkülönbözteti a kis- és nagybetűt (1. rész), a névben pedig „Anna” áll nagy A-val. Ha a betűméret nem számít, használja a SZÖVEG.KERES-t: =SZÖVEG.KERES("anna";A2) → 6.
Összefoglalás
HOSSZ: a karakterek száma; SZÖVEG.KERES és SZÖVEG.TALÁL: egy részlet helye.
SZÖVEG.KERES: kis- és nagybetű nem számít, helyettesítő karakter használható; SZÖVEG.TALÁL: betűérzékeny.
Nincs találat: #ÉRTÉK!; a harmadik argumentummal a következő előfordulás is kereshető.
Név szétbontása: BAL(…;hely−1) és JOBB(…;HOSSZ−hely) vagy KÖZÉP(…;hely+1;100).
Tartalmazás: SZÁM(SZÖVEG.KERES(…)).
Ellenőrizd magad!
Mit ad a =HOSSZ("Győr")?
4
Mit ad a =SZÖVEG.KERES("@";"lili@iskola.hu")?
5
Miért kell a -1 a vezetéknév képletében?
Hogy a szóköz már ne kerüljön bele.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (szövegkezelés, adatok bevitele különböző forrásokból); kapcsolódás a programozáshoz (karakterlánc keresése, index, részlánc). Az NKP-okostankönyvre épülő emelt szóbeli tétel SZÖVEG.KERES elemét gyakoroltatja.
Módszertan
A lecke a változó hosszúságú szövegrészeket a „hol van a határoló karakter?” kérdésre vezeti vissza, segédoszlopos, lépésenkénti felépítéssel. A jóslás a két utónevet és a szóköz nélküli nevet mint határeseteket vizsgálja, és a hibakezeléshez (8.6) kapcsol. Peti esete a gyakori „eggyel elcsúszott” hibát (off-by-one) mutatja meg, amely a programozásból is ismerős.
Időterv
A három függvény: 9 perc. Név szétbontása és jóslás: 11 perc. További minták: 6 perc. Kihívás: 15 perc. Hibavadászat: 4 perc.
Következő lecke: 11.3 KIMETSZ és szövegrészek cseréje
Szóközök, egységes írásmód, kis- és nagybetűk: a HELYETTE, a CSERE és a betűméret-függvények.
Egy tanulókód (9B-017) három információt rejt: az évfolyamot, az osztályt és a sorszámot. Egy cikkszám elején a kategória kódja áll. Egy iskolai rendszer a nevekből és a születési évből állítja elő a felhasználóneveket. Kezdődik a 11. fejezet: a szöveg-, dátum- és időfüggvények. Az első lépés: egy szövegből egy darabot kivágni, elölről, hátulról vagy a közepéből.
⏱ kb. 45 perc
🎯 kezdőknek
🎒 10. osztálytól
Kép helye (16:9)A „9B-017” tanulókód karakterei hat dobozban, alattuk 1–6 sorszámmal; színes zárójelek: BAL(A2;1) → „9”, KÖZÉP(A2;2;1) → „B”, JOBB(A2;3) → „017”.Javasolt alternatív szöveg: „Tanulókód darabolása BAL, KÖZÉP és JOBB függvénnyel”
A lecke végére
BAL-lal és JOBB-bal levágod egy szöveg elejét, végét;
KÖZÉP-pel kivágod a közepét;
kódokat bontasz részekre;
nevekből és számokból új azonosítót állítasz elő;
tudod, hogy az eredmény szöveg, és mikor kell átalakítani.
A karakterek számozása 1-től kezdődik; a szóköz, a kötőjel és az írásjelek is karakternek számítanak.
Az ékezetes betű egy karakter („Ő” = 1).
Ha a darabszámot elhagyod, a BAL és a JOBB egy karaktert ad: =BAL(A2) → 9.
Ha több karaktert kérsz, mint amennyi van, az egész szöveget kapod hibaüzenet nélkül.
2
Kódok darabolása
Ha egy kód rögzített szerkezetű (minden rész ugyanolyan hosszú és ugyanott van), a három függvénnyel bármelyik része kivágható:
Kód
Rész
Képlet
Eredmény
9B-017
évfolyam
=BAL(A2;1)
9
9B-017
osztály betűje
=KÖZÉP(A2;2;1)
B
9B-017
sorszám
=JOBB(A2;3)
017
KON-2026-0412
év
=KÖZÉP(A3;5;4)
2026
💡 Ha a részek hossza változik
„Kiss Anna” és „Szabó Bence”: a vezetéknév hossza nem állandó, ezért a BAL-nak nem tudsz fix darabszámot adni. Ilyenkor meg kell keresni a szóköz helyét (SZÖVEG.KERES, 11.2), és abból kiszámolni a hosszt.
3
Azonosító előállítása
Egy (kitalált) iskolai hálózatban az új diák felhasználóneve: a vezetéknév és az utónév első két betűje, utána a születési év utolsó két jegye. A jelszó: az utónév utolsó három karaktere és egy véletlen háromjegyű szám.
B: Vezetéknév
C: Utónév
D: Születési év
Felhasználónév
Jelszó
Kovács
Péter
2009
=BAL(B2;2)&BAL(C2;2)&JOBB(D2;2) → KoPé09
=JOBB(C2;3)&VÉLETLEN.KÖZÖTT(100;999) → ter483
🔮 Jósold meg!
A D2 cellában most nem az évszám (2009), hanem a teljes születési dátum áll: 2009.05.12. Mit ad a =JOBB(D2;2)?
Megoldás
„45”, nem „09”! A dátum a háttérben egy sorszám (3.7): 2009.05.12 = 39945. A JOBB ennek a számnak a szöveges alakjából veszi az utolsó két karaktert, nem a megjelenített dátumból. Előbb az évet kell kinyerni: =JOBB(ÉV(D2);2) → „09” (az ÉV függvény: 11.6), vagy a SZÖVEG függvénnyel formázva (11.4): =SZÖVEG(D2;"éé").
4
Szöveg az eredmény
A BAL, a JOBB és a KÖZÉP eredménye mindig szöveg, akkor is, ha számjegyekből áll („017”, „2026”). Balra igazodik, és a SZUM kihagyja (5.2).
Ha számként kell tovább számolni vele: =ÉRTÉK(JOBB(A2;3)) → 17, vagy =JOBB(A2;3)*1 (7.6).
Ha a vezető nullát meg kell tartani (például azonosítóként), maradjon szöveg: a „017” így marad „017”.
5
Kihívás: hálózati azonosítók
🔐 Felhasználónevek és tanulókódok
Készíts egy kitalált listát nyolc új diákról: Vezetéknév, Utónév, Születési év (szám), Tanulókód (például 9B-017, 10A-005 – figyelj: a 10. évfolyamnál az évfolyam két jegyű!).
Állítsd elő a felhasználónevet a 3. rész szabálya szerint.
Állítsd elő a jelszót: az utónév utolsó három karaktere és egy véletlen háromjegyű szám. Rögzítsd értékként (2.3), hogy ne változzon az F9-re.
Bontsd a tanulókódot sorszámra (JOBB), és alakítsd számmá. Mennyi a legnagyobb sorszám (MAX)?
Gondold végig: miért nem működik a BAL(…;1) az évfolyamra a 10A-005 kódnál? Milyen kódszerkezet lenne jobb? (Tipp: mindig két jegyű évfolyam, például 09B-017.)
Bónusz: a felhasználónév legyen csupa kisbetűs (KISBETŰ függvény: =KISBETŰ(…)). Mi a véleményed az ékezetes betűkről egy felhasználónévben?
6
Hibavadászat
🐞 1. Peti furcsa osztálybetűje
Peti a „9B-017” kódból az osztály betűjét akarta kivágni: =KÖZÉP(A2;1;2). Az eredmény „9B” lett.
Mi a megoldás?
A KÖZÉP második argumentuma a kezdőpozíció, a harmadik a darabszám (1. rész). Peti az 1. karaktertől kért 2 karaktert. A betű a 2. pozíción van, és 1 karakter hosszú: =KÖZÉP(A2;2;1).
🐞 2. Lili eltűnt nullái
Lili a tanulókódok sorszámát =ÉRTÉK(JOBB(A2;3)) képlettel vágta ki, és a „017” helyett 17 lett. Az iskolai rendszer viszont háromjegyű sorszámot vár.
Mit tegyen?
Az ÉRTÉK számmá alakít, és a számnak nincs vezető nullája (2.8, 4. rész). Ha azonosítóként kell, Lili hagyja szövegként: =JOBB(A2;3) → „017”. Ha számolni is kell vele, és utána újra háromjegyű alakra van szükség, a SZÖVEG függvény segít: =SZÖVEG(17;"000") → „017” (11.4).
Összefoglalás
BAL(szöveg;n): az első n karakter; JOBB(szöveg;n): az utolsó n karakter.
KÖZÉP(szöveg;honnan;hány): a megadott pozíciótól ennyi karakter.
A karakterek számozása 1-től indul; a szóköz és az írásjel is karakter.
Az eredmény mindig szöveg; számmá ÉRTÉK-kel vagy *1-gyel alakítható, de a vezető nulla elvész.
Dátumon előbb ÉV/HÓNAP/NAP vagy SZÖVEG kell, mert a dátum a háttérben sorszám.
Ellenőrizd magad!
Mit ad a =BAL("Excel";2)?
Ex
Mit ad a =KÖZÉP("Budapest";5;4)?
pest
Milyen típusú a =JOBB("KON-2026";4) eredménye?
Szöveg ("2026").
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (szövegkezelés, szövegkezelő függvények), adatok feldolgozása különböző forrásokból; kapcsolódás a programozáshoz (karakterlánc, részlánc, indexelés) és az információbiztonsághoz (felhasználónév, jelszó). Az NKP-okostankönyvre épülő emelt szóbeli tétel szövegkezelő feladatának (felhasználónév a nevek első két betűjéből és a születési év utolsó két jegyéből, jelszó az utónév utolsó három karakteréből és egy véletlen számból) mintájára készült.
Módszertan
A fejezet nyitó leckéje a karakterek sorszámozását vizuálisan mutatja be, a programozás karakterlánc-indexeléséhez kapcsolva. A jóslás a dátum belső tárolásának egy meglepő következményét fedezteti fel (3.7), és előre mutat az ÉV függvényre (11.6). A kódszerkezetről szóló kérdés (10A-005) az adattervezés szempontját erősíti. Lili esete a szám és a szöveg különbségét (2.8) ismétli.
Időterv
A három függvény: 8 perc. Kódok darabolása: 7 perc. Azonosító előállítása és jóslás: 9 perc. Szöveg az eredmény: 4 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.
Következő lecke: 11.2 HOSSZ, SZÖVEG.KERES és SZÖVEG.TALÁL
Hol van a szóköz? Milyen hosszú a szöveg? Változó hosszúságú részek kivágása.
A 9. b osztály közös büférendelést ad le az osztálykirándulásra. A rendelési lapon csak a termékkódok és a darabszámok szerepelnek, minden más (a termék neve, a kategóriája, az ára) a büfé árlistájában van. A büfé mennyiségi kedvezményt is ad. A feladatod, hogy keresőfüggvényekkel teljes, ellenőrzött rendelési összesítőt készíts, amely akkor is helyesen működik, ha a rendelésben ismeretlen kód szerepel.
⏱ kb. 45 perc
🧩 mini projekt
🎒 10. osztálytól
Kép helye (16:9)Két munkalap füle: „Árlista” és „Rendelés”. A Rendelés lapon: Kód, Darab, Termék, Kategória, Egységár, Tétel összege oszlopok; az egyik sorban narancssárgán kiemelt „ismeretlen kód”; alul Részösszeg, Kedvezmény (10%), Fizetendő és egy figyelmeztető sor.Javasolt alternatív szöveg: „Rendelési összesítő keresőfüggvényekkel”
Készíts egy munkafüzetet két lappal (kitalált adatok). Figyeld meg: az árlistában a Termék a Kód előtt (balra) áll!
Árlista lap (A1-től)
A: Termék
B: Kód
C: Kategória
D: Ár (Ft)
Kakaós csiga
K01
pékáru
290
Pogácsa
K02
pékáru
120
Csokoládé
K03
édesség
350
Ásványvíz
K04
ital
200
Narancslé
K05
ital
300
Gumicukor
K06
édesség
250
Kedvezmény (Árlista lap, F1-től)
F: Részösszeg legalább
G: Kedvezmény
0
0%
2000
5%
5000
10%
Rendelés lap (A1-től)
A: Kód
B: Darab
K03
6
K01
10
K04
8
K07
2
K02
15
K05
4
2
Feldolgozás
Egységár (Rendelés, E oszlop) FKERES-sel: a kód a B oszlopban, az ár tőle jobbra, ezért a tábla Árlista!$B$2:$D$50, oszlopszám 3, pontos egyezés. Ismeretlen kódnál „ismeretlen kód” (HAHIÁNYZIK, 10.8).
Termék (C oszlop): a név a kód bal oldalán van, ezért FKERES-sel nem megy. INDEX + HOL.VAN: =HAHIÁNYZIK(INDEX(Árlista!$A$2:$A$50;HOL.VAN(A2;Árlista!$B$2:$B$50;0));"ismeretlen kód"), vagy XKERES-sel (10.7).
Kategória (D oszlop): bármelyik módszerrel.
Tétel összege (F oszlop): =HA(SZÁM(E2);B2*E2;""), hogy az ismeretlen kódnál ne legyen hiba (8.6).
Alatta: Részösszeg (SZUM), Kedvezmény % közelítő kereséssel a kedvezménytáblából (FKERES IGAZ-zal, vagy INDEX + HOL.VAN 1-gyel, 10.3), Kedvezmény Ft, Fizetendő.
Ellenőrzés: az ismeretlen kódok száma (DARABTELI), figyelmeztetés a fizetendő mellett, ha nem 0 (10.8), és feltételes formázás az ismeretlen kódokra.
Elemzés: kategóriánkénti összeg SZUMHA-val (9.2), és INDEX + HOL.VAN-nal a legnagyobb tétel terméke (10.6).
3
Ellenőrzés
Megoldások az ellenőrzéshez
Kód
Darab
Termék
Kategória
Egységár
Tétel
K03
6
Csokoládé
édesség
350
2100
K01
10
Kakaós csiga
pékáru
290
2900
K04
8
Ásványvíz
ital
200
1600
K07
2
ismeretlen kód
ismeretlen kód
ismeretlen kód
(üres)
K02
15
Pogácsa
pékáru
120
1800
K05
4
Narancslé
ital
300
1200
Részösszeg: 9600 Ft; kedvezmény: 10% (a 9600 eléri az 5000-et) = 960 Ft; fizetendő: 8640 Ft.
Ismeretlen kódok száma: 1 → a figyelmeztetés megjelenik.
Kategóriák: pékáru 4700 Ft, ital 2800 Ft, édesség 2100 Ft (összesen 9600 Ft: egyezik a részösszeggel).
Legnagyobb tétel: Kakaós csiga, 2900 Ft.
Utolsó lépés: a büfé közli, hogy a K07 a „Kifli” (pékáru, 80 Ft). Vedd fel az árlista végére: a rendelés magától frissül → részösszeg 9760 Ft, kedvezmény 976 Ft, fizetendő 8784 Ft, ismeretlen kód: 0, a figyelmeztetés eltűnik. (Ha nem frissül, a keresési tartomány túl rövid: 10.8, 5. ok.)
💡 Próbáld ki a határokat is!
Írd át a darabszámokat úgy, hogy a részösszeg pontosan 5000 Ft legyen: 10% kedvezményt kell kapnod („legalább”). 4999 Ft-nál 5%-ot. Ha nem így van, nézd meg a kedvezménytábla alsó határait és a közelítő keresés beállítását (10.3).
4
Önellenőrzés és értékelés
✅ Önellenőrző lista
Az egységár, a termék és a kategória minden ismert kódnál helyes, a táblák rögzítettek.
A terméknevet balra kereséssel (INDEX + HOL.VAN vagy XKERES) kaptam meg.
Minden pontos keresésben szerepel a HAMIS vagy a 0 (XKERES-nél ez az alapérték).
Az ismeretlen kódot HAHIÁNYZIK-kal (vagy XKERES-szel) kezeltem, nem HAHIBA-val.
A tétel összege ismeretlen kódnál üres, nem hiba.
A kedvezmény közelítő kereséssel jön, a határokon is helyes.
A figyelmeztetés jelzi, ha a végösszeg hiányos.
A K07 felvétele után minden magától frissült.
Szempont
⭐ Elindultam
⭐⭐ Sikerült
⭐⭐⭐ Profi vagyok
Keresés
Az ár megvan.
Ár, név, kategória, kedvezmény.
Balra és közelítő keresés is, rögzített, bővíthető tartományokkal.
Hibakezelés
#HIÁNYZIK látszik.
„ismeretlen kód” üzenet.
Célzott kezelés, kiemelés, figyelmeztetés a végösszegnél.
Ellenőrzés
Ránéztem.
A megoldásokkal egyezik.
Határesetek és a kategóriaösszeg ellenőrzése is.
5
Hibavadászat: Peti rendelési összesítője
🐞 Peti beszámolója
„Az árat =FKERES(A2;Árlista!B2:D7;3) képlettel kerestem ki, és lemásoltam. A termék nevét =FKERES(A2;Árlista!$A$2:$D$7;1;HAMIS) képlettel próbáltam, de nem működött. Az ismeretlen kódot HAHIBA-val oldottam meg. A kedvezménytáblát így írtam: 5000 → 10%, 2000 → 5%, 0 → 0%. A tétel összegében =B2*E2 áll. A K07 felvétele után semmi nem változott.”
Hány hibát találtál? Mit tanácsolnál Petinek?
Hiba
Helyesen
Lecke
rögzítetlen tábla
Árlista!$B$2:$D$50
10.2
hiányzó HAMIS (közelítő keresés)
…;3;HAMIS)
10.2
FKERES balra (a név a kód előtt van)
INDEX + HOL.VAN vagy XKERES
10.7
HAHIBA a HAHIÁNYZIK helyett
HAHIÁNYZIK
10.8
csökkenő sorrendű kedvezménytábla
növekvő sorrend: 0, 2000, 5000
10.3
tétel összege hibát ad ismeretlen kódnál
=HA(SZÁM(E2);B2*E2;"")
8.6
túl rövid tartomány (B2:D7)
bővebb tartomány (például B2:D50)
10.8
Hét hibát találhattál. Ha mindet megtaláltad, a keresőfüggvények mestere vagy!
Mit tudsz már? A 10. fejezet összefoglalása
Két táblát kapcsolsz össze egy kulcs alapján, pontos és közelítő egyezéssel.
FKERES-t és VKERES-t használsz, és ismered a korlátaikat.
HOL.VAN-nal helyet, INDEX-szel értéket keresel; együtt bármelyik irányban.
XKERES-sel egyszerűbben keresel, ha a verzió engedi.
Felderíted és célzottan kezeled a hiányzó találatokat.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények, több munkalap, hibakezelés), Adatbázis-kezelés (táblák kapcsolata kulcs alapján, mint egy lekérdezés). Az NKP-okostankönyvre épülő emelt szóbeli tétel keresőfüggvényes elemének komplex alkalmazása; az érettségi táblázatkezelési feladatainak jellegzetes szerkezete (több lap, segédtábla, kedvezménysáv).
Módszertan
A mini projekt adatszerkezete tudatosan úgy készült, hogy a diáknak mindhárom keresési helyzettel (jobbra, balra, közelítő) találkoznia kelljen. Az ismeretlen kód és a K07 utólagos felvétele a dinamikus, bővíthető megoldás értékét mutatja meg. A megadott megoldások és a határesetek önálló ellenőrzést tesznek lehetővé. Peti beszámolója a fejezet hét tipikus hibáját ismétli.
Időterv (45 perc)
Az adatok: 7 perc. Feldolgozás: 22 perc. Ellenőrzés és határok: 7 perc. Hibavadászat: 6 perc. Önellenőrzés: 3 perc.
Következő fejezet: 11.1 BAL, JOBB és KÖZÉP
Kezdődik a 11. fejezet: szöveg-, dátum- és időfüggvények az importált adatok feldolgozásához.
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
Kép helye (16:9)Egy rendelési lista: az Ár oszlopban néhány #HIÁNYZIK, mellette egy javított oszlop „ismeretlen kód” szöveggel, a kód cellák narancssárga kiemeléssel; alul „Ismeretlen kódok száma: 2” és egy figyelmeztető cella a végösszeg mellett.Javasolt alternatív szöveg: „Hiányzó keresési találatok kezelése”
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.
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.
2
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)
3
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:
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.
4
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.
5
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é.
6
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.
Az FKERES csak jobbra tud visszaadni, és ha egy oszlopot beszúrsz, elcsúszik az oszlopszáma. A HOL.VAN megmondja, hányadik sorban van a keresett érték, az INDEX pedig kiolvassa, mi van egy oszlop ugyanazon sorában. Együtt egy rugalmas kereső párost alkotnak, amely bármelyik irányban keres, akár sorra és oszlopra egyszerre. Az újabb Excel-verziókban mindezt egyetlen függvény, az XKERES is tudja: a lecke végére mindkettőt használni fogod.
⏱ kb. 45 perc
🎓 haladóbb
🎒 10. osztálytól
Kép helye (16:9)Egy árlista, amelyben a Termék az A oszlopban, a Kód a B oszlopban áll; a keresett kód (C07) a B oszlopban kiemelve, egy nyíl balra mutat a termék nevére; a szerkesztőlécen =INDEX($A$2:$A$20;HOL.VAN(H1;$B$2:$B$20;0)) színes részekkel, alatta az XKERES-es változat.Javasolt alternatív szöveg: „INDEX és HOL.VAN: keresés balra”
A lecke végére
INDEX és HOL.VAN párral keresel bármelyik irányban;
sorra és oszlopra egyszerre keresel;
közelítő keresést végzel határtáblával;
XKERES-sel keresel, üzenettel a hiányzó találatra;
=INDEX($C$2:$C$20;HOL.VAN(A2;$A$2:$A$20;0))zöld: amiből választunk (visszaadott oszlop) · sárga: amit keresünk · kék: ahol keresünk (kulcsoszlop)
Mondd ki szavakkal: „Keresd meg az A2 értékét a kulcsoszlopban (HOL.VAN → hányadik sor), és add vissza a visszaadott oszlop ugyanannyiadik elemét (INDEX).” A két oszlop egymástól független: lehet egymás mellett, távol, akár más munkalapon is, és a visszaadott oszlop balra is lehet a kulcsoszloptól.
Peti képlete: =INDEX($A$3:$A$21;HOL.VAN(H1;$B$2:$B$20;0)). Az INDEX tartománya a 3., a HOL.VAN tartománya a 2. sorban kezdődik. A keresett kód a B5-ben van. Melyik termék nevét kapja?
Megoldás
A HOL.VAN eredménye 4 (B2 = 1., B3 = 2., B4 = 3., B5 = 4.). Az INDEX a saját tartományának 4. elemét adja: A3 = 1., A4 = 2., A5 = 3., A6 = 4. → az A6, vagyis a következő sor termékét, hibaüzenet nélkül. A két tartománynak ugyanabban a sorban kell kezdődnie és ugyanolyan hosszúnak kell lennie (mint a SZUMHA-nál, 9.2).
2
Kétirányú és közelítő keresés
Sorra és oszlopra egyszerre: két HOL.VAN, az egyik a sort, a másik az oszlopot adja. Az órarendben (sorok: órák, oszlopok: napok): =INDEX($B$2:$F$8;HOL.VAN($H$2;$A$2:$A$8;0);HOL.VAN($H$1;$B$1:$F$1;0)), ahol H1 a nap neve, H2 az óra (az A oszlopban az órák sorszáma áll).
Közelítő keresés határtáblával (10.3, 10.5): =INDEX($I$2:$I$6;HOL.VAN(B2;$H$2:$H$6;1)) → a pontszámhoz tartozó jegy. Ez az NKP-feladatokban is szereplő „INDEX…HOL.VAN” megoldás.
✅ Miért szeretik a profik?
Az INDEX + HOL.VAN minden Excel-verzióban (és a LibreOffice Calcban is) működik, bármelyik irányban keres, és ha oszlopot szúrsz be, nem csúszik el, mert nincs benne beírt oszlopszám. Hátránya, hogy hosszabb és elsőre nehezebben olvasható.
3
Az XKERES
Az újabb Excel-verziókban (Excel 2021, 2024 és Microsoft 365; az Excel 2016-ban és 2019-ben nem érhető el) az XKERES egyetlen függvényben adja mindezt:XKERES(keresett_érték;keresési_tömb;visszaadott_tömb;[ha_nincs_találat];[illesztési_mód];[keresési_mód])
Az alapértelmezés a pontos egyezés: nem kell HAMIS-t vagy 0-t írni (a leggyakoribb FKERES-hiba így nem fordulhat elő).
Illesztési mód: 0 = pontos (alap); −1 = pontos, vagy ha nincs, a következő kisebb (ez a határtáblás sávkeresés, a tábla rendezése nélkül is működik); 1 = pontos, vagy a következő nagyobb; 2 = helyettesítő karakterekkel (9.6).
Keresési mód: 1 = elölről (alap); −1 = hátulról (például a legutolsó előfordulás).
⚠️ Ellenőrizd a verziót!
Ha egy XKERES-t tartalmazó fájlt régebbi Excelben nyitsz meg, vagy egy régebbi Excelben próbálod beírni, #NÉV? hibát kapsz (4.9). Érettségin a vizsgán használt program dönt: ha nincs benne XKERES, az FKERES vagy az INDEX + HOL.VAN a biztos választás.
4
Melyiket válaszd?
FKERES
INDEX + HOL.VAN
XKERES
Keresés balra
❌
✅
✅
Oszlopbeszúrás után is jó
❌ (beírt oszlopszám)
✅
✅
Pontos egyezés alapból
❌ (HAMIS kell)
❌ (0 kell)
✅
Üzenet hiányzó találatra
HAHIBA-val (10.8)
HAHIBA-val
✅ beépítve
Sorra és oszlopra egyszerre
❌
✅
✅ (egymásba ágyazva)
Minden Excel-verzióban
✅
✅
❌ (2021-től)
5
Kihívás: egy feladat, három megoldás
🔁 Hasonlítsd össze a módszereket!
Készíts egy árlistát úgy, hogy a Termék az A oszlopban, a Kód a B-ben, az Ár a C-ben legyen.
Egy keresőcellába (H1) írj egy kódot. Írd ki a hozzá tartozó árat háromféleképpen: FKERES-sel, INDEX + HOL.VAN-nal és (ha a verziód engedi) XKERES-sel.
Írd ki a kódhoz tartozó termék nevét is. Melyik módszerrel nem megy, és miért?
Szúrj be egy új oszlopot a Kód és az Ár közé. Melyik képlet ad továbbra is helyes árat?
Írj be nem létező kódot. Melyik ad beszédes üzenetet hibaérték helyett?
Kétirányú keresés: az órarendben INDEX-szel és két HOL.VAN-nal írd ki a kiválasztott nap kiválasztott óráját.
Határtábla: a 10.3 pontszámlistáján a jegyet INDEX + HOL.VAN (1-es egyezés) és XKERES (−1-es illesztés) képlettel is számold ki. Ugyanazt adják, mint a közelítő FKERES?
6
Hibavadászat
🐞 1. Peti szomszéd terméke
Peti képlete: =INDEX($A$3:$A$21;HOL.VAN(H1;$B$2:$B$20;0)). Minden kódhoz a következő sor termékének nevét kapja.
Mi a megoldás?
A két tartomány nem ugyanabban a sorban kezdődik (1. rész, jóslás). Peti igazítsa össze: =INDEX($A$2:$A$20;HOL.VAN(H1;$B$2:$B$20;0)). Ellenőrzésként kattintson a képletre: a szerkesztőlécen a két színes keret ugyanabban a sorban kezdődik és végződik.
🐞 2. Lili #NÉV? hibája
Lili az iskolai gépen megnyitotta az otthon készített fájlját. Minden XKERES-es cellában #NÉV? áll.
Mit tegyen?
Az iskolai gépen régebbi Excel fut (például 2016 vagy 2019), amely nem ismeri az XKERES függvényt (3. rész). Lili írja át a képleteket INDEX + HOL.VAN-ra vagy FKERES-re, amelyek minden verzióban működnek. Tanulság: ha egy fájlt más gépen is használni fognak (vagy vizsgán), előre derítsd ki, milyen verzió fut rajta.
Összefoglalás
=INDEX(visszaadott_oszlop;HOL.VAN(keresett;kulcsoszlop;0)): bármelyik irányban keres.
A két tartomány ugyanabban a sorban kezdődjön, ugyanolyan hosszú legyen.
XKERES: pontos egyezés alapból, üzenet hiányzó találatra, −1-es illesztés sávokhoz; csak Excel 2021-től.
Válassz a helyzet szerint: régi fájl, vizsgagép, irány, karbantarthatóság.
Ellenőrizd magad!
Miért tud az INDEX + HOL.VAN balra keresni?
Mert a kulcsoszlop és a visszaadott oszlop két külön tartomány, bármelyik oldalon lehetnek.
Mi az XKERES alapértelmezett illesztési módja?
Pontos egyezés (0).
Melyik módszer működik biztosan egy Excel 2016-os gépen: XKERES vagy INDEX + HOL.VAN?
Az INDEX + HOL.VAN.
👩🏫 Tanároknak
Kerettantervi kapcsolódás
NAT 2020, Digitális kultúra, 9–11. évfolyam: Táblázatkezelés (keresőfüggvények, egymásba ágyazott függvények), digitális eszközök tudatos választása (szoftververziók). Az NKP-okostankönyvre épülő emelt szóbeli tétel keresőfüggvényes elemét („=INDEX…HOL.VAN vagy FKERES vagy XKERES”) teljes egészében lefedi.
Módszertan
A lecke a 10.5 és a 10.6 két „fél” függvényét egy egésszé rakja össze, és mondatba foglalva teszi érthetővé. A jóslás a leggyakoribb hibát (eltérő kezdősorú tartományok) mutatja meg. Az XKERES a Microsoft hivatalos magyar súgója szerinti argumentumnevekkel és illesztési módokkal szerepel, a verziófüggőség hangsúlyozásával. A „három megoldás egy feladatra” kihívás és az összevető táblázat a módszerválasztás tudatosságát fejleszti.
Időterv
A kereső páros és jóslás: 10 perc. Kétirányú és közelítő keresés: 7 perc. Az XKERES: 9 perc. Melyiket válaszd: 3 perc. Kihívás: 12 perc. Hibavadászat: 4 perc.
Következő lecke: 10.8 Hiányzó találatok és hibák kezelése
#HIÁNYZIK helyett beszédes üzenet, és a hiányzó találatok okainak felderítése.
A legjobb felhasználói élmény érdekében technológiákat, például sütiket használunk, hogy tároljuk és/vagy hozzáférjünk az eszköz információihoz. Ezek elfogadása lehetővé teszi az oldalon belüli viselkedésed vagy egyedi azonosítók adatainak feldolgozását. Ha nem fogadod el vagy vonod vissza a hozzájárulásodat, bizonyos funkciók korlátozottak lehetnek.
Funkcionális
Always active
A technikai tárolás vagy hozzáférés szigorúan szükséges az előfizető vagy felhasználó által kifejezetten kért konkrét szolgáltatás igénybevételének lehetővé tételéhez, vagy kizárólag az elektronikus hírközlő hálózaton keresztül történő kommunikáció továbbításához.
Preferences
The technical storage or access is necessary for the legitimate purpose of storing preferences that are not requested by the subscriber or user.
Statisztika
A technikai tárolás vagy hozzáférés kizárólag statisztikai célokra történik.The technical storage or access that is used exclusively for anonymous statistical purposes. Without a subpoena, voluntary compliance on the part of your Internet Service Provider, or additional records from a third party, information stored or retrieved for this purpose alone cannot usually be used to identify you.
Marketing
A technikai tárolás vagy hozzáférés szükséges a felhasználói profilok létrehozásához reklámküldés céljából, vagy a felhasználók követéséhez egy vagy több weboldalon marketing célokból.