Telefonszámunk: 1-472-0679

Az első nagybetű pozíciója egy cellában

2016-10-02 - horvimi - Kategória: Tömbképletek
Hivatkozott Excel verzio: Excel 2007 Excel 2010 Excel 2013 Excel 2016

Adott egy cella vagy egy oszlop. Arra vagyunk kíváncsiak, hogy hol van az első nagybetű. Meglehetősen speciális eset, de végül mégis úgy döntöttem, hogy írok róla, mert ebből a megoldásból következően, kisebb módosításokkal más feladatok is megoldhatók.

elso-nagybetu.adatok

Tehát nem tudjuk előre, hogy melyik nagybetű, és nem tudjuk, hogy hol. Sőt, azt sem tudjuk, hogy van-e nagybetű egyáltalán. Melyik nagybetűt keressük?

És van még egy probléma: Hogyan teszünk különbséget nagybetű és kisbetű között?

A keresőfüggvény

Ha nem egy, hanem az összes nagybetűt keressük, az megoldás lehetne. A kereséshez olyan függvényre van szükség, ami megkülönbözteti a kis és nagybetűket.

Ez a függvény a SZÖVEG.TALÁL. Angol neve FIND.

=SZÖVEG.TALÁL(mit_keres; miben_keresi; [honnan_keresi])

Az utolsó argumentum nem kötelező. Ha elhagyjuk, akkor az elejétől keres. Ha megtalálja a keresett szöveget, akkor megadja a pozícióját. Ha nem találja meg, akkor hibát ad.

Hogy használjuk a mi problémánkhoz

=MIN(HAHIBA(SZÖVEG.TALÁL({"A";"Á";"B";"C";"D";"E";"É";"F";"G";"H";"I";"Í";"J";"K";"L";"M";"N";"O";"Ó";"Ö";"Ő";"P";"Q";"R";"S";"T";"U";"Ú";"Ü";"Ű";"V";" W";"X";"Y";"Z"};C3);100))

Ez egy tömbképlet, Ezért Ctrl+Shift+Enter-rel kell lezárni!

Hogyan működik?

A példában a C3 cellában van az a szöveg, amiben keressük az első nagybetűt. A SZÖVEG.TALÁL függvény keresi az összes nagybetűt, amelyek egy tömbben vannak felsorolva, a C3 cellában.

Ennek a keresésnek az eredménye egy olyan tömb, ahol hiba lesz azoknál a betűknél, amelyek nincsenek benne, és a pozíció száma, ha benne van.

hibatomb

Azt látjuk, hogy az „R” helyén van egy 8-as, az „Y” helyén pedig van egy 6-os. Tehát a C3 8.-dik karaktere „R” és a 6.-dik karaktere „Y”.

A HAHIBA (IFERROR) függvény az #ÉRTÉK üzeneteket cseréli egy nagy számra.

Lényeges, hogy ez a szám nagyobb legyen a C3-ban lévő szöveg hosszánál. ha egy egész oszlopra használjuk  a képletet, akkor az oszlopban előfordulható leghoszabb szöveg hosszánál legyen nagyobb. Itt most Én 100-at választottam.

Ezek után az előbbi tömb így néz ki:

hibatomb-iferror

A hibák helyén ott a 100-as érték.

Utolsó lépésként a MIN függvénnyel kivesszük ennek a tömbnek a legkisebb értékét, ami tehát megadja az első nagybetű pozícióját. Jelen példában ez a 6-os lesz.

Mi van, ha a betű kell, és nem a pozíciója?

Mivel a pozíciója már megvan, csak ki kell vennünk az eredeti szövegből az adott pozícióból 1 db karaktert. Ez a KÖZÉP (MID) függvénnyel tehető meg. A képlet ebben az esetben így alakul:

=KÖZÉP(A2;MIN(HAHIBA(SZÖVEG.TALÁL({"A";"Á";"B";"C";"D";"E";"É";"F";"G";"H";"I";"Í";"J";"K";"L";"M";"N";"O";"Ó";"Ö";"Ő";"P";"Q";"R";"S";"T";"U";"Ú";"Ü";"Ű";"V";" W";"X";"Y";"Z"};A2);100));1)

Mi van akkor, ha nincs nagybetű?

Akkor a képlet azt a bizonyos nagy számot fogja visszaadni, ami a mi példánkban a 100.

Mi van, ha mást keresünk?

A kapcsos zárójelben felsorolt karakterek természetesen bármi egyéb sorozatra cserélhetők, sőt, nem is kell feltétlenül egy karakteresnek lenniük.

A keresendők lehetnek tartományban is

A kapcsos zárójelben lévő tömbkonstans helyett a képletben hivatkozhatunk

  • Egy oszlopos tartományra,
  • Névtartományra
  • Táblázat oszlopára

Letölthető munkafüzet

Elso-nagybetu

Lássuk ezt működés közben is

1 Comment
  1. Ha a hibakezelést úgy oldjuk meg, hogy a betűket hozzáadjuk a végéhez, lásd:

    =MIN(SZÖVEG.TALÁL({„A”;”Á”;”B”;”C”;”D”;”E”;”É”;”F”;”G”;”H”;”I”;”Í”;”J”;”K”;”L”;”M”;”N”;”O”;”Ó”;”Ö”;”Ő”;
    „P”;”Q”;”R”;”S”;”T”;”U”;”Ú”;”Ü”;”Ű”;”V”;”W”;”X”;”Y”;”Z”};A2&”AÁBCDEÉFGHIÍJKLMNOÓÖŐPQRSTUÚÜŰVWXYZ”))

    akkor már nem tömbképlet és simán is le lehet zárni…
    A „nem talált” visszatérési érték meg nyilván a hossz+1 lesz…

Vélemény, hozzászólás?