KIMETSZ és szövegrészek cseréje: HELYETTE, CSERE, betűméret

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

Excel tanfolyam · 11.3 lecke

KIMETSZ és szövegrészek cseréje

„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

A lecke végére

  • KIMETSZ-szel és betűméret-függvényekkel egységesítesz;
  • HELYETTE-vel tartalom szerint cserélsz;
  • CSERE-vel pozíció szerint cserélsz;
  • megszámolod egy karakter előfordulásait;
  • ékezetmentes azonosítót állítasz elő.

Amit már tudsz

  • a KIMETSZ-t és a Keresés és cserét (7.6, 2.7);
  • a BAL, JOBB, KÖZÉP függvényt (11.1);
  • a HOSSZ-t és a SZÖVEG.KERES-t (11.2);
  • a függvények egymásba ágyazását (5.7).

Betűméret és szóközök

FüggvényHatásPélda (A2 = „ KISS anna ”)
KIMETSZ(szöveg)felesleges szóközök eltávolítása (7.6)„KISS anna”
NAGYBETŰS(szöveg)csupa nagybetű=NAGYBETŰS(KIMETSZ(A2)) → „KISS ANNA”
KISBETŰ(szöveg)csupa kisbetű=KISBETŰ(KIMETSZ(A2)) → „kiss anna”
TNÉV(szöveg)minden szó nagy kezdőbetűvel, a többi kisbetű=TNÉV(KIMETSZ(A2)) → „Kiss Anna”

🔮 Jósold meg!

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.

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.
FeladatKépletEredmé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

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.
FeladatKépletEredmé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.

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.

Kihívás: egységes névsor és azonosítók

🧽 Tisztítsd képletekkel!

  1. 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.
  2. A nevekből TNÉV és KIMETSZ segítségével készíts egységes alakot.
  3. 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!).
  4. Számold meg minden névben a szavak számát. Kinek van két utóneve?
  5. Á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.)
  6. Maszkold a telefonszámokat úgy, hogy csak az utolsó három számjegy látszódjon.

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(…;"")).
  • Tisztító képletlánc belülről kifelé; bonyolultabbnál segédoszlopok.

Ellenőrizd magad!

Mit ad a =HELYETTE("2026.10.18";".";"-")?
2026-10-18
Mit ad a =CSERE("alma";1;1;"A")?
Alma
Hogyan számolod meg a szavakat egy szóközökkel tagolt szövegben?
=HOSSZ(KIMETSZ(A2))-HOSSZ(HELYETTE(KIMETSZ(A2);" ";""))+1
👩‍🏫 Tanároknak

Kerettantervi kapcsolódás

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.

Időterv

Betűméret és szóközök, jóslás: 8 perc. HELYETTE: 8 perc. CSERE: 6 perc. Hasznos fogások: 6 perc. Kihívás: 13 perc. Hibavadászat: 4 perc.

Következő lecke: 11.4 Szövegek összefűzése és típusátalakítás

Az &, az ÖSSZEFŰZ és a SZÖVEG függvény: számok és dátumok szép, egységes formában egy mondatban.

HOSSZ, SZÖVEG.KERES és SZÖVEG.TALÁL: változó hosszúságú szövegrészek

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

Excel tanfolyam · 11.2 lecke

HOSSZ, SZÖVEG.KERES és SZÖVEG.TALÁL

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

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);
  • megvizsgálod, tartalmaz-e egy szöveg egy szót.

Amit már tudsz

  • a BAL, a JOBB és a KÖZÉP függvényt (11.1);
  • a helyettesítő karaktereket (9.6);
  • a SZÁM függvényt és a hibakezelést (7.6, 8.6);
  • a függvények egymásba ágyazását (5.7).

A három függvény

FüggvényJelentésPélda (A2 = Szabó Bence)Eredmény
HOSSZ(szöveg)a karakterek száma (szóközzel együtt)=HOSSZ(A2)11
SZÖVEG.KERES(keresett;szöveg;[kezdet])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.

Név szétbontása

  1. A szóköz helye (segédoszlop, B): =SZÖVEG.KERES(" ";A2) → 6.
  2. Vezetéknév: a szóköz előtti karakterek, vagyis eggyel kevesebb, mint a szóköz helye: =BAL(A2;B2-1) → Szabó.
  3. 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).
  4. 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.

További minták

FeladatKé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).

Kihívás: névsor és e-mailek

✂️ Bontsd szét az adatokat!

  1. 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).
  2. Segédoszloppal keresd meg a szóköz helyét, majd bontsd a nevet vezeték- és utónévre.
  3. Mi történik a bevezető szóközös névvel? Javítsd KIMETSZ-szel a forrásnál (7.6).
  4. Az e-mail-címből vágd ki a domaint és a felhasználói részt.
  5. Ellenőrizd: minden domain „iskola.hu”? Számold meg DARABTELI-vel, és emeld ki feltételes formázással az eltérőket.
  6. Bónusz: készíts egy „Monogram” oszlopot (például KA): a vezetéknév és az utónév első betűje.

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.

BAL, JOBB és KÖZÉP: szövegrészek kivágása az Excelben

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

Excel tanfolyam · 11.1 lecke

BAL, JOBB és KÖZÉP

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

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.

Amit már tudsz

  • a szövegösszefűzést az & jellel (4.1);
  • a szöveg és a szám különbségét, az ÉRTÉK függvényt (7.6);
  • a dátumot mint számot (3.7);
  • a véletlenszámokat (5.6).

A három függvény

FüggvényJelentésPélda (A2 = 9B-017)Eredmény
BAL(szöveg;[hány_karakter])a szöveg elejéről ennyi karakter=BAL(A2;1)9
JOBB(szöveg;[hány_karakter])a szöveg végéről ennyi karakter=JOBB(A2;3)017
KÖZÉP(szöveg;honnan;hány_karakter)a megadott sorszámú karaktertől ennyi karakter=KÖZÉP(A2;2;1)B
  • 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.

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ódRészKépletEredmény
9B-017évfolyam=BAL(A2;1)9
9B-017osztály betűje=KÖZÉP(A2;2;1)B
9B-017sorszá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.

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évC: UtónévD: Születési évFelhasználónévJelszó
KovácsPéter2009=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;"éé").

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”.

Kihívás: hálózati azonosítók

🔐 Felhasználónevek és tanulókódok

  1. 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ű!).
  2. Állítsd elő a felhasználónevet a 3. rész szabálya szerint.
  3. Á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.
  4. Bontsd a tanulókódot sorszámra (JOBB), és alakítsd számmá. Mennyi a legnagyobb sorszám (MAX)?
  5. 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.)
  6. 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?

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.

Mini projekt: árlista és rendelés összekapcsolása keresőfüggvényekkel

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

Excel tanfolyam · 10.9 mini projekt

Árlista és rendelés összekapcsolá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

A projekt végére

  • két munkalap adatait kapcsolod össze kód alapján;
  • jobbra és balra is keresel;
  • pontos és közelítő keresést kombinálsz;
  • célzottan kezeled az ismeretlen kódot;
  • ellenőrzött végösszeget számolsz kedvezménnyel.

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

  • 10.1 fogalmak; 10.2 FKERES; 10.3 közelítő keresés;
  • 10.5 HOL.VAN; 10.6 INDEX; 10.7 INDEX + HOL.VAN, XKERES;
  • 10.8 hiányzó találatok; valamint SZUMHA (9.2).

Az adatok

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ékB: KódC: KategóriaD: Ár (Ft)
Kakaós csigaK01pékáru290
PogácsaK02pékáru120
CsokoládéK03édesség350
ÁsványvízK04ital200
NarancsléK05ital300
GumicukorK06édesség250

Kedvezmény (Árlista lap, F1-től)

F: Részösszeg legalábbG: Kedvezmény
00%
20005%
500010%

Rendelés lap (A1-től)

A: KódB: Darab
K036
K0110
K048
K072
K0215
K054

Feldolgozás

  1. 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).
  2. 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).
  3. Kategória (D oszlop): bármelyik módszerrel.
  4. Tétel összege (F oszlop): =HA(SZÁM(E2);B2*E2;""), hogy az ismeretlen kódnál ne legyen hiba (8.6).
  5. 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ő.
  6. 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.
  7. Elemzés: kategóriánkénti összeg SZUMHA-val (9.2), és INDEX + HOL.VAN-nal a legnagyobb tétel terméke (10.6).

Ellenőrzés

Megoldások az ellenőrzéshez
KódDarabTermékKategóriaEgységárTétel
K036Csokoládéédesség3502100
K0110Kakaós csigapékáru2902900
K048Ásványvízital2001600
K072ismeretlen kódismeretlen kódismeretlen kód(üres)
K0215Pogácsapékáru1201800
K054Narancsléital3001200
  • 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).

Ö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ésAz á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ésRánéztem.A megoldásokkal egyezik.Határesetek és a kategóriaösszeg ellenőrzése is.

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?
HibaHelyesenLecke
rögzítetlen táblaÁrlista!$B$2:$D$5010.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 XKERES10.7
HAHIBA a HAHIÁNYZIK helyettHAHIÁNYZIK10.8
csökkenő sorrendű kedvezménytáblanövekvő sorrend: 0, 2000, 500010.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.

Hiányzó találatok és hibák kezelése a keresőfüggvényeknél

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

  • a hibaértékeket és a HAHIBA veszélyét (4.9, 8.6);
  • a szóközöket és a típusokat (7.6);
  • a keresőfüggvényeket (10.2–10.7);
  • a képletes feltételes formázást (8.8).

Az öt ok

OkTünetJavítás
1. Valóban hiányzika 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ésaz 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 karakterszemmel ugyanaz, mégsem egyezik („C02 ”)KIMETSZ, TISZTÍT (7.6)
4. Rögzítetlen táblaa felső sorok jók, lefelé egyre több a hibadollárjel a táblán (10.2)
5. Túl rövid tartományaz árlista végére felvett új termékeket nem találjaa tartomány bővítése (vagy Excel-tábla, 14.4)

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ésKépletHa HAMIS vagy eltér…
Tényleg egyezik a két cella?=A5=Árlista!A8van 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 keretetrö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ódszerKépletMit 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!";"")

Kihívás: a hibás rendelés kivizsgálása

🕵️ Derítsd ki, és javítsd!

  1. 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.
  2. Nézd meg, melyik sorban jelenik meg #HIÁNYZIK.
  3. Minden hibás sornál derítsd ki az okot a 2. rész vizsgálataival, és írd fel egy „Napló” oszlopba (7.10).
  4. Javítsd a 2–5. okot a forrásnál (típus, szóköz, tartomány, rögzítés).
  5. Az 1. okot (valóban hiányzó kód) kezeld HAHIÁNYZIK-kal (vagy XKERES-szel) „ismeretlen kód” üzenettel.
  6. 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.

INDEX és HOL.VAN együtt, valamint az XKERES

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

Excel tanfolyam · 10.7 lecke

INDEX és HOL.VAN együtt, valamint az XKERES

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

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;
  • kiválasztod a helyzethez illő keresőmódszert.

Amit már tudsz

  • az FKERES-t és korlátait (10.2);
  • a HOL.VAN-t (10.5);
  • az INDEX-et (10.6);
  • a függvények egymásba ágyazását (5.7).

A kereső páros

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.
FeladatKéplet (A: termék, B: kód, C: ár)
kódból ár (FKERES is tudná)=INDEX($C$2:$C$20;HOL.VAN(H1;$B$2:$B$20;0))
kódból terméknév – balra! (FKERES nem tudja)=INDEX($A$2:$A$20;HOL.VAN(H1;$B$2:$B$20;0))
a legdrágább termék neve=INDEX($A$2:$A$20;HOL.VAN(MAX($C$2:$C$20);$C$2:$C$20;0))

🔮 Jósold meg!

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).

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ó.

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])
FeladatKéplet
kódból terméknév (balra is)=XKERES(H1;$B$2:$B$20;$A$2:$A$20)
üzenet, ha nincs találat=XKERES(H1;$B$2:$B$20;$A$2:$A$20;"ismeretlen kód")
pontszámból jegy (határtábla)=XKERES(B2;$H$2:$H$6;$I$2:$I$6;;-1)
  • 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).

Melyiket válaszd?

FKERESINDEX + HOL.VANXKERES
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álatraHAHIBA-val (10.8)HAHIBA-val✅ beépítve
Sorra és oszlopra egyszerre❌✅✅ (egymásba ágyazva)
Minden Excel-verzióban✅✅❌ (2021-től)

Kihívás: egy feladat, három megoldás

🔁 Hasonlítsd össze a módszereket!

  1. 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.
  2. 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.
  3. Írd ki a kódhoz tartozó termék nevét is. Melyik módszerrel nem megy, és miért?
  4. Szúrj be egy új oszlopot a Kód és az Ár közé. Melyik képlet ad továbbra is helyes árat?
  5. Írj be nem létező kódot. Melyik ad beszédes üzenetet hibaérték helyett?
  6. 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.
  7. 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?

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.
  • Kétirányú keresés: INDEX(tábla;HOL.VAN(sor);HOL.VAN(oszlop)); közelítő: HOL.VAN(…;1).
  • 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.