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.