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.