keresés és csere képlettel

2023-04-17    Excel 2021
a cikk tartalma
A keresés és csere modul használhatósága, mint minden speciális eszközé, korlátozott. Nem tudunk minden keresést vagy módosítást egyetlen karakterlánccal deklarálni! Gondoljunk bele: még egy egyszerű cellahivatkozást sem alkalmazhatunk!
Összetett kereséshez és cseréhez már szövegkezelő függvények kellenek. Persze ez képlet-szerkesztéssel jár és azt nem mindenki szereti… A „képletes” módszer, természetéből fakadóan, nem az eredeti tartalmat módosítja, mint a keresés és csere modul, hanem egy új karakterláncot hoz létre. Tehát a művelet nem is csere, inkább feldolgozás.
Ebben a cikkben csak azokkal a szövegkezelő függvényekkel foglalkozunk, amelyek szorosan vagy lazán, de valamennyire a keresés és csere témakörhöz kapcsolódnak.
karakterlánc keresése
Első lépésben ellenőriznünk kell tartalmazza e egyáltalán a vizsgált cella a feldolgozandó karakterláncot. Persze előtte tisztáznunk kell, mit szeretnénk: (1) a feldolgozandó karakterlánc legyen-e azonos a cella tartalmával vagy (2) elegendő, ha csak része annak.
Az első eset meglétéről meggyőződhetünk egy összehasonlítással vagy az AZONOS függvénnyel.
mintával azonos cellatartalmak keresése képlettel
A képen látható feladatban azt kellett ellenőriznünk, hogy a C oszlop celláiban valóban az azonos sorban álló vezeték és utónevek lettek-e összefűzve.
Karakterláncokat a szövegösszefűző operátorral (&) vagy az ÖSSZEFŰZ függvénnyel egyesíthetünk. A program az ampersand, amelyet közönségesen „és” jelnek nevezünk, két oldalán álló-, illetve az ÖSSZEFŰZ argumentumaiban megadott karakterláncokat egyesíti. Természetesen nem csak idézőjelek között álló konstansokkal, hanem karakterláncot eredményező kifejezésekkel is dolgozhatunk. A példában három karakterláncot adtunk meg.
karakterláncok és karakterláncokat eredményező kifejezések
Kettőt hivatkozásként (vezetéknév, utónév), egyet konstansként (szóköz). Az AZONOS függvény két karakterláncot hasonlít össze a kis- és nagybetűket megkülönböztetve. Az összehasonlítás viszont nem „kisbetű-nagybetű érzékeny”. Ezért eltérőek a D és a C oszlop eredményei. Természetesen a függvény argumentumai is lehetnek kifejezések! Csak kiértékelésük karakterláncot eredményezzen.
De ez a feladat nem is igazi keresés, mert az igazi keresés ilyen…
adott karakterlánc keresése
Keresd meg az A oszlopban azokat a cellákat, amelyek tartalma azonos a C1-es cella tartalmával! Oké! Akkor, a B1-es cella képlete =A1=$C$1 lesz. Vagy =AZONOS(A1;$C$1). És lehet másolni! De, ha már itt tartunk: mi lenne, ha nem egy, hanem három szín előfordulásait kerestetnénk?
lista elemeinek keresése tömbképlettel
Az AZONOS függvény második argumentuma a három keresett szín rögzített tartománya. A képletet tömbösen kell kiértékeltetnünk, tehát a Shift+Ctrl+Enter billentyűparanccsal fejezzük be a képlet szerkesztését. Ezt jelzik a kapcsos zárójelek.
Az AZONOS(A1;$C$1:$C$3) képletrész kiértékelése három HAMIS logikai értéke eredményez (A1=$C$1, A1=$C$2, A1=$C$3). A VAGY függvény akkor ad IGAZ eredményt, ha valamelyik argumentuma (itt csak egy argumentuma van, de az egy három elemű tömb) IGAZ. Hát ez a barna (A1) esetében nem jön össze.
Ha a keresett karakterlánc nem egyenlő a cella tartalmával, hanem csak része annak, akkor a SZÖVEG.KERES és a SZÖVEG.TALÁL függvényeket kell használnunk. A két függvény argumentum-listája azonos: (1) keresendő karakterlánc, (2) melyik cellában történjen a keresés, (3) a cella hányadik karakterétől kezdődjön a keresés. A harmadik argumentum elhagyható. Szokásos értéke, ha nem adjuk meg: 1. A kis- és nagybetűket a SZÖVEG.KERES nem különbözteti meg, a SZÖVEG.TALÁL viszont megkülönbözteti őket. A keresendő karakterlánc argumentumban joker karakter is állhat: kérdőjel, amely egyetlen tetszőleges karaktert, vagy a csillag, amely nem meghatározott számú, tetszőleges karaktert helyettesít. Ha a kérdőjel és a csillag nem helyettesítő funkcióban szerepel az argumentumban, akkor hullámvonal (~) karakter kell eléjük írni.
SZÖVEG.KERES és SZÖVEG.TALÁL függvények
A kép bal oldalán a SZÖVEG.KERES, jobb oldalán a SZÖVEG.TALÁL függvény szolgáltatta eredményeket látjuk: számok és az #ÉRTÉK! hiba. Ha szám, akkor a cella tartalmazza a keresett karakterláncot, ha #ÉRTÉK!, akkor nem tartalmazza. De mit mutat a szám? Azt, hogy a találat cellájának karakterláncában hányadik karaktertől kezdődik a keresett karakterlánc. A karakterek sorszámozása egyessel kezdődik és balról jobbra halad. Ha a harmadik argumentum nem 1, akkor az első vizsgált karakter kapja az egyes sorszámot. Előfordulhat hogy a keresett karakterlánc többször is előfordul a vizsgált cellában, de a tárgyalt függvények csak az első előfordulással foglalkoznak.
Ha logikai értékkel akarjuk a találatokat jelezni, akkor ki kell egészítenünk a képletünket…
Stalálatok jelzése logikai értékkel
… a SZÁM függvénnyel, amely logikai értéket ad eredményül. Ha egyetlen argumentumának kiértékelése numerikus adattípust eredményez, akkor IGAZ-t, ha nem, akkor HAMIS-t. Ezt a megoldást kell alkalmaznunk az előbb bemutatott VAGY függvényes, listás kereséshez, illetve ha műveletet akarunk a találathoz rendelni.
keresés és HA függvény
A keresés és csere modullal nem csak a cella látható tartalmában, de a képletében is kutathatunk. Vajon a képletes módszerrel is megtehetjük ezt. Igen. A KÉPLETSZÖVEG függvény segítségével, amely szövegként adja vissza egy cella képletét.
keresés a képletben a KÉPLETSZÖVEG függvénnyel
Felül a B oszlop képleteiben kerestem a D1-es cella tartalmát. Alul, pedig megjelenítettem ezeket a képleteket.
felesleges szóközök eltávolítása
Hogy lehet egy szóköz felesleges? Hiszen konkrét feladatot tölt be: elválasztja a szavakat! Benne is van a nevében! Ez igaz, de ha úgy tesszük fel a kérdést, hogy milyen helyzetben felesleges, akkor már tudunk válaszolni. A karakterlánc elején és a végén. Sőt a szavak között is, ha nem csak egy van belőle! És miért a keresés és csere témakörben beszélünk róla? Azért mert számos esetben végrehajtása csere műveletet is igényel. Elmesélem miért.
A karaktereket minden számítástechnikai program, így az Excel is, egy kódolási rendszer segítségével azonosítja. Ebben a rendszerben minden karakterhez egy szám tartozik. Az összerendeléseket a kódtábla tartalmazza. A program a kódtábla alapján állapítja meg az inputként érkező számhoz tartozó karaktert. Az Excelben le is kérdezhetjük ezt a számot, sőt a szám alapján még képezni is tudjuk a karaktert.
KÓD függvény, KARAKTER függvény
Tehát egy karakter számát a KÓD, a számhoz tartozó karaktert pedig a KARAKTER függvénnyel képezhetjük. A Wordből már tudhatjuk, hogy vannak nem látható karakterek is. Ezeket ott szöveg-tördelő karaktereknek neveztük, de az informatikában általában vezérlő karaktereknek hívják őket.
Amikor karakterekkel végzünk műveletet, akkor nem csak a felesleges szóközöktől, de a vezérlő karakterektől is meg kell tisztítanunk a karakterláncot. A vezérlő karaktereket a TISZTÍT függvénnyel távolíthatjuk el a cellából.
TISZTÍT függvény
És, tessék mondani, itt mit tisztított a TISZTÍT függvény? Eltávolította a karakterláncból a felhasználói sortörést (Alt+Enter). Tisztítás után jöhet a felesleges szóközök eltávolítása a KIMETSZ függvénnyel! A függvény törli a karakterlánc előtt és után álló, valamint a karakterlánc belsejében egymás után álló második, harmadik és minden további szóközt.
KIMETSZ függvény
Hát ez nem sikerült valami fényesen: kilenc cellából csak háromból tűntek el a felesleges szóközök (B2, B3, B7). Mi ennek az oka? Ahogy a Wordben, itt az Excelben is kétfajta szóközt különböztetünk meg, a közönségeset és a nem-törhetőt. Az előbbi kódja 32, az utóbbié 160.
szóköz és nem törhető szóköz a cellában
A négy számos cella között két képletes cella áll. Mindkét képletet tartalmazó cellához automatikus sortörést állítottam be (Cellaformázás, Igazítás, Sortöréssel több sorba). Tovább már nem kell magyarázni, a kép önmagáért beszél.
Visszatérve a KIMETSZ függvényre, az a probléma vele, hogy csak a 32-es kódú szóközt törli, ezért minden 160-ast le kell cserélni 32-esre. Ezt a HELYETTE függvénnyel tehetjük meg. Argumentum-listája négy elemből áll. (1) Tartalmazó karakterlánc: a törlendő karakterláncot tartalmazó karakterlánc. (2) Törlendő karakterlánc: A tartalmazó karakterlánc eltávolítandó része vagy részei. (3) Beillesztendő karakterlánc: a törölt karakterlánc helyére elhelyezendő karakterlánc. (4) A törlendő karakterlánc hányadik előfordulását kívánjuk lecserélni a tartalmazó karakterláncban. A számozás balról jobbra halad. Nem kötelező argumentum. Ha nincs megadva, akkor az összeset.
HELYETTE függvény
csere függvények
Nincs olyan sok. Összesen kettő. Az előbb ismertetett HELYETTE és a CSERE. A CSERE argumentum-listája is négyelemű. (1) A karakterlánc, amelyben a csere történik. (2) Az első törlendő karakter sorszáma a karakterláncban. A sorszámozás egyessel kezdődik és balról jobbra tart. (3) A törlésre kerülő, egymást követő karakterek darabszáma. (4) A törölt karakterek helyére beszúrandó karakterlánc.
CSERE függvény
A fenti képen városok kódjának módosítását látjuk. Az eredeti kód (A oszlop) ötödik, hatodik és hetedik karakterét le kellett cserélni a régió két karakterére.
Hasonlítsuk össze a két függvényt! A két függvény azonos abban, hogy argumentumaikkal deklarálnunk kell (1) a feldolgozandó karakterláncot (2) a törlendő karakterláncot és (3) a beillesztendő karakterláncot. Vagy ha valakinek így nem tetszik, akkor a feldolgozandó szöveget, a törlendő szöveget, és a beillesztendő szöveget.
a HELYETTE és a CSERE függvények összehasonlítása
A HELYETTE esetében a törlés és a beillesztés csak akkor valósul meg, ha a feldolgozandó karakterlánc tartalmazza a törlendő karakterláncot. A CSERE esetében nincs semmilyen előzetes vizsgálat, a törlés és a beillesztés mindenképpen megtörténik.
A törlendő karakterláncot a HELYETTE esetében egy mintával deklaráljuk: Ezt a szöveget keresd! Mindegy, hogy hol áll a feldolgozandó karakterláncban, a lényeg, hogy ez a karakterlánc legyen! A CSERE esetében is a törlendő karakterlánc a feldolgozandó karakterlánc egy szakasza, de a szakasz karaktereit a függvény nem vizsgálja.
A CSERE mindig csak egyet cserél, a feldolgozandó karakterlánc adott pozíciótól kezdődő és meghatározott számú karakterből álló szakaszát. A HELYETTE viszont, a szokásos működésében, a törlendő karakterlánc összes előfordulását lecseréli.
Mindkét függvény csak töröl ha a beillesztendő karakterlánc nulla hosszúságú szöveg (””). Amit hívnak még üres karakterláncnak is.
Szabadság, Testvériség, Egyenlőség!
margitfalvi.arpad@gmail.com