Az Excel az egyik leghasznosabb eszköz az adatfeldolgozásban, de néha olyan adatokkal találkozunk, amelyek nem pont úgy állnak rendelkezésre, ahogyan szeretnénk. Az egyik leggyakoribb kihívás a szöveges cellákban lévő adatok rendezése, különösen, ha számokat kell kinyernünk összetett szövegmezőkből. Képzeljük el, hogy számlákra, termékkódokra vagy ügyfélszámokra vonatkozó információkat kapunk, amelyek „textuális köntösbe” vannak csomagolva, például: „Rendelés_szám_(12345)_dátum_2023-10-26” vagy „ID: [67890] – Termék A”. Hogyan tudjuk ezeket a számokat kinyerni a szövegből hatékonyan, anélkül, hogy manuálisan kellene átírnunk őket? A válasz az Excel függvények kombinálásában rejlik.
Ebben az átfogó útmutatóban lépésről lépésre bemutatjuk, hogyan használhatjuk az Excel beépített függvényeit arra, hogy a számok kinyerése a szövegből, adott karakterek között lévő értékek esetén is gyerekjáték legyen. Nem csak a végeredményt mutatjuk meg, hanem részletesen elmagyarázzuk az egyes függvények működését, és tippeket adunk a robusztus, hibatűrő megoldások létrehozásához. Készüljön fel, mert az adatkinyerés ezen módja jelentősen felgyorsíthatja a munkáját!
Miért van szükség a számok kinyerésére szövegből?
Az adatok gyakran strukturálatlan formában érkeznek. Gondoljunk csak a weboldalakról kimásolt adatokra, log fájlokra, vagy akár manuálisan beírt megjegyzésekre. Ezekben az esetekben a fontos numerikus információk, mint például mennyiségek, azonosítók, árak vagy méretek, gyakran beágyazódnak más szöveges leírásokba. Az Excel alapértelmezetten nem tudja egyből felhasználni ezeket a számokat matematikai műveletekhez, grafikonok készítéséhez, vagy szűréshez, amíg szöveges formátumban vannak. Ezért elengedhetetlen, hogy képesek legyünk azonosítani, kinyerni és numerikus formátumra alakítani őket.
Az alapvető Excel függvények a számkinyeréshez
Ahhoz, hogy számokat tudjunk kinyerni adott karakterek (például zárójelek, kötőjelek, dupla pontok) között, több függvényt kell kombinálnunk. Lássuk a legfontosabbakat:
-
SZÖVEG.KERES (SEARCH / FIND):
Ez a függvény segít megtalálni egy adott karakter vagy szövegrész pozícióját egy szövegen belül. Két hasonló függvény van, a SZÖVEG.KERES és a SZÖVEG.KERES. Pontosabban: a
SZÖVEG.KERESnem különbözteti meg a kis- és nagybetűket, míg aSZÖVEG.KERES.PONTOSAN(FIND) igen. A legtöbb esetben aSZÖVEG.KERESa rugalmasabb választás, mert nem kell aggódnunk a betűk mérete miatt. Szintaxisa:SZÖVEG.KERES(mit_keres; hol_keres; [kezdő_pozíció])mit_keres: Az a karakter vagy szöveg, amit keresünk (pl. „(„).hol_keres: Az a szöveg, amelyben keresünk (pl. a cella, ahol a teljes szöveg van).kezdő_pozíció(opcionális): Ettől a karaktertől kezdve keres. Hasznos, ha több azonos karakter is szerepel, és a második vagy későbbi előfordulást keressük.
A függvény a talált karakter első előfordulásának pozícióját adja vissza számként.
-
KÖZÉP (MID):
Ez a függvény lehetővé teszi, hogy egy adott szövegből kivágjunk egy részt, egy megadott kezdőponttól és egy megadott hosszal. Ez lesz a „szív” függvénye a megoldásunknak.
KÖZÉP(szöveg; kezdő_szám; karakterek_száma)szöveg: Az a szöveg, amelyből kivágunk.kezdő_szám: Az a pozíciószám, ahol a kivágás kezdődik. Ezt általában aSZÖVEG.KERESfüggvény fogja szolgáltatni.karakterek_száma: Hány karaktert vágjunk ki a kezdőponttól. Ezt úgy kell kiszámolnunk, hogy a lezáró karakter pozíciójából kivonjuk a kezdő karakter pozícióját, majd levonunk 1-et, ha a kezdő és záró karaktereket nem akarjuk a végeredményben látni.
-
HOSSZ (LEN):
Egyszerű, de elengedhetetlen függvény, amely egy szöveges sztring hosszát adja vissza, azaz hány karaktert tartalmaz.
HOSSZ(szöveg)Ez gyakran hasznos a
KÖZÉPfüggvényben, ha a sztring végéig akarunk kivágni. -
ÉRTÉK (VALUE):
Az
ÉRTÉKfüggvény kulcsfontosságú, mivel aKÖZÉPmindig szöveges eredményt ad vissza, még akkor is, ha a kivágott rész számokból áll. AzÉRTÉKfüggvény átalakítja a szöveges számot valódi számmá, így azzal számításokat végezhetünk. Szintaxisa:ÉRTÉK(szöveg) -
HAHIBA (IFERROR):
A robusztusság érdekében mindig érdemes használni a
HAHIBAfüggvényt. Mi történik, ha a keresett karakterek nem találhatók meg a szövegben? ASZÖVEG.KEREShibát ad vissza (#ÉRTÉK!), ami továbbterjed a képletben. AHAHIBAsegítségével kezelhetjük ezeket a helyzeteket, például üres cellát vagy nullát írva ki eredményként.HAHIBA(érték; hiba_esetén_érték)érték: Az a képlet, amit ki akarunk értékelni.hiba_esetén_érték: Az az érték, amit akkor ad vissza, ha azértékhibát eredményez.
Lépésről lépésre: Szám kinyerése zárójelek közül
Vegyünk egy konkrét példát. Tegyük fel, hogy az „A1” cellában a következő szöveg található:
"Termékazonosító (12345) raktár_XY"
Célunk az 12345 szám kinyerése.
1. lépés: A kezdő karakter pozíciójának megtalálása
A szám a nyitó zárójel ( után kezdődik. Használjuk a SZÖVEG.KERES függvényt:
=SZÖVEG.KERES("(";A1)
Ez az eredmény 17 lesz, mivel a „(” a 17. karakter a sztringben. Ezt az értéket fogjuk használni a KÖZÉP függvény kezdő_szám argumentumaként, de mivel a szám _utána_ kezdődik, hozzá kell adnunk 1-et.
2. lépés: A záró karakter pozíciójának megtalálása
A szám a záró zárójel ) előtt ér véget. Ismét a SZÖVEG.KERES:
=SZÖVEG.KERES(")";A1)
Ez az eredmény 23 lesz. Ez a lezáró zárójel pozíciója.
3. lépés: A kivágandó szöveg hosszának kiszámítása
A kivágandó szám hossza (azaz hány karaktert kell kivágnunk) a záró zárójel pozíciója mínusz a nyitó zárójel pozíciója mínusz 1 (mivel magát a zárójelet nem akarjuk látni). Képletben:
(Záró_zárójel_pozíciója) - (Nyitó_zárójel_pozíciója + 1)
Jelen példában: 23 - (17 + 1) = 23 - 18 = 5. Ez az 5 karakter a 12345.
4. lépés: A szám kivágása a KÖZÉP függvénnyel
Most már minden adatunk megvan a KÖZÉP függvényhez:
KÖZÉP(szöveg; kezdő_szám; karakterek_száma)
Helyettesítsük be a talált értékeket a fenti lépésekből:
=KÖZÉP(A1; SZÖVEG.KERES("(";A1)+1; SZÖVEG.KERES(")";A1) - (SZÖVEG.KERES("(";A1)+1) )
Vagy egyszerűbben: a záró karakter pozíciójából kivonjuk a nyitó karakter pozícióját, majd levonunk 1-et:
=KÖZÉP(A1; SZÖVEG.KERES("(";A1)+1; SZÖVEG.KERES(")";A1)-SZÖVEG.KERES("(";A1)-1)
Ez a képlet eredményül a „12345” szöveget adja. Ez még mindig szöveg.
5. lépés: A szöveg számra alakítása
Az előző lépés eredményét beágyazzuk az ÉRTÉK függvénybe:
=ÉRTÉK(KÖZÉP(A1; SZÖVEG.KERES("(";A1)+1; SZÖVEG.KERES(")";A1)-SZÖVEG.KERES("(";A1)-1))
Ez a végső képlet adja vissza az 12345 számot, amellyel már számolhatunk.
6. lépés (Opcionális, de ajánlott): Hibakezelés a HAHIBA függvénnyel
Mi történik, ha az A1 cellában „Nincs azonosító” szerepel, azaz nincsenek zárójelek? A képlet hibát (#ÉRTÉK!) adna. Ezt elkerülhetjük a HAHIBA függvénnyel:
=HAHIBA(ÉRTÉK(KÖZÉP(A1; SZÖVEG.KERES("(";A1)+1; SZÖVEG.KERES(")";A1)-SZÖVEG.KERES("(";A1)-1)); "")
Ebben az esetben, ha a zárójelek nem találhatók meg, a cella üresen marad (vagy 0-t is megadhatunk a "" helyett).
További komplex esetek és tippek
1. Különböző elválasztó karakterek használata
Ugyanez a logika alkalmazható bármilyen elválasztó karakterpárra, legyen az [], {}, < >, ID: és -, vagy akár egyedi szöveges markerek. Egyszerűen cserélje ki a "(" és ")" karaktereket a megfelelő nyitó és záró karaktereire.
Például, ha "Ár: [99.99] EUR" és a [99.99]-et szeretnénk kinyerni:
=HAHIBA(ÉRTÉK(KÖZÉP(A1; SZÖVEG.KERES("[";A1)+1; SZÖVEG.KERES("]";A1)-SZÖVEG.KERES("[";A1)-1)); "")
Figyelem! A tizedesvessző/pont használata regionális beállításoktól függ. Az ÉRTÉK függvény kezeli a rendszer beállításait, de ha a szövegben lévő szám formátuma eltér a rendszerétől (pl. pont helyett vessző vagy fordítva), akkor szükség lehet a HELYETTE (SUBSTITUTE) függvényre is az átalakításhoz.
Például, ha az A1 cellában „Ár: [99.99] EUR” van, de a rendszerünk tizedesvesszőt használ, a HELYETTE segítségével pontot alakíthatunk át vesszővé a kivágott szövegben, mielőtt az ÉRTÉK függvénybe kerül:
=HAHIBA(ÉRTÉK(HELYETTE(KÖZÉP(A1; SZÖVEG.KERES("[";A1)+1; SZÖVEG.KERES("]";A1)-SZÖVEG.KERES("[";A1)-1); "."; ",")); "")
2. Kezelje, ha a sztringben több azonos karakter van
Ha például a következő sztringünk van: "Kód: A123 (Rendelés: 456) Tétel: X" és a 456-ot akarjuk kinyerni, de több zárójel is van.
A SZÖVEG.KERES függvény harmadik argumentumát, a kezdő_pozíció-t használhatjuk arra, hogy megmondjuk, hol kezdje a keresést.
Tegyük fel, hogy az első zárójelpár azonosítót jelöl, a második pedig a rendelési számot. A második nyitó zárójel pozícióját úgy találjuk meg, hogy az első zárójel után kezdjük a keresést.
Első_nyitó = SZÖVEG.KERES("(";A1)
Második_nyitó = SZÖVEG.KERES("(";A1; Első_nyitó + 1)
És a lezáró zárójelet is az első nyitó zárójel után keressük:
Lezáró = SZÖVEG.KERES(")";A1; Második_nyitó + 1)
Ezekkel a segítő lépésekkel már könnyen felépíthetjük a végső képletet.
3. A szám körül lévő extra szóközök eltávolítása
Néha a kinyert szám körül felesleges szóközök lehetnek (pl. ” 123 „). Bár az ÉRTÉK függvény általában kezeli ezeket, a SZÓKÖZÖK (TRIM) függvény használata biztosítja, hogy a kinyert szöveg tiszta legyen, mielőtt számmá alakítjuk:
=ÉRTÉK(SZÓKÖZÖK(KÖZÉP(...)))
Mikor nem elegendőek a függvények?
Bár az Excel függvények rendkívül erősek, vannak olyan esetek, amikor a képletek bonyolultsága már nem hatékony vagy praktikus. Ezek közé tartoznak:
- Nagyon komplex minták: Ha a kinyerni kívánt szám nem követ egy szabályos „karakterek között” mintát, hanem nagyon változatos, esetleg több szám is szerepel a cellában, és meghatározott logikával kell kiválasztani a megfelelőt.
- Rendszeres feladatok nagy adatmennyiségen: Ha több ezer soron kell elvégezni ezt a műveletet, a képletek lassíthatják a munkafüzetet.
- Reguláris kifejezésekre (Regex) van szükség: Az Excel alapértelmezetten nem támogatja a reguláris kifejezéseket, amelyek sokkal rugalmasabbak összetett szövegminták felismerésére. Ebben az esetben a VBA (Visual Basic for Applications) vagy a Power Query lehet a megoldás.
- „Szövegből oszlopokba” funkció: Egyszerűbb esetekben, ha az elválasztó karakterek konzisztensek, a „Szövegből oszlopokba” (Text to Columns) funkció is szóba jöhet, de az inkább az adat felosztására, mint a beágyazott számok kinyerésére szolgál.
Összefoglalás
Az Excelben számok kinyerése szövegből adott karakterek között egy alapvető, mégis rendkívül hasznos készség, amely jelentősen javíthatja az adatfeldolgozási hatékonyságot. A SZÖVEG.KERES, KÖZÉP, ÉRTÉK, HOSSZ és HAHIBA függvények okos kombinálásával könnyedén automatizálhatjuk ezt a feladatot. Ne féljen kísérletezni, és alkalmazza a tanultakat saját adataihoz! Az Excel tippek és trükkök elsajátítása folyamatos tanulás, de a befektetett idő megtérül a gyorsabb és pontosabb munkavégzésben.
Reméljük, hogy ez az útmutató segít Önnek abban, hogy magabiztosabban kezelje a szöveges adatokban rejlő numerikus információkat. Sok sikert a gyakorláshoz!