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
Betűméret és szóközök
| Függvény | Hatás | Pé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.
| 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.
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.
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!
- 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.
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.
