Szöveg elemzése reguláris kifejezésekkel (RegExp) az Excelben

Az egyik legidőigényesebb és leginkább frusztráló feladat az Excelben szöveggel végzett munka során elemzése – az alfanumerikus „kását” komponensekre bontani, és a számunkra szükséges töredékeket kinyerni belőle. Például:

• az irányítószám kinyerése a címből (jó, ha az irányítószám mindig az elején van, de mi van, ha nem?)
• a számla számának és dátumának megtalálása a fizetés leírásából a bankszámlakivonaton
• TIN kinyerése a szerződő felek listáján szereplő vállalatok tarka leírásaiból
• autószámot vagy cikkszámot keress a leírásban stb.
  

Általában ilyenkor fél óra unalmas kézi szedés után kezdenek eszébe jutni a gondolatok, hogy valahogy automatizáljuk ezt a folyamatot (főleg, ha sok az adat). Számos megoldás létezik, és különböző bonyolultsági-hatékonysági fokokkal:

• Felhasználás beépített Excel szövegfunkciók szöveg keresése-kivágása-ragasztása: LEVSIMV (BAL), RIGHT (JOBB), PSTR (középső), STsEPIT (ÖSSZEKAPCSOL) és analógjai, KOMBÁJN (JOINTEXT), PONTOS (PONTOS) stb. Ez a módszer akkor jó, ha a szövegben világos logika van (például az index mindig a cím elején van). Ellenkező esetben a képletek sokkal bonyolultabbá válnak, és néha tömbképletekre is sor kerül, ami nagy táblákon nagymértékben lelassul.
• <p></p> mint a szöveghasonlósági operátor a Visual Basic-ből egy egyéni makrófüggvénybe csomagolva. Ez lehetővé teszi a rugalmasabb keresést helyettesítő karakterek (*, #,?, stb.) használatával. Sajnos ez az eszköz nem tudja kinyerni a kívánt részkarakterláncot a szövegből – csak ellenőrizze, hogy az benne van-e.

A fentieken kívül van egy másik megközelítés, amely nagyon jól ismert a professzionális programozók, webfejlesztők és más technikusok szűk köreiben – ez az szabályos kifejezések (Reguláris kifejezések = RegExp = "reguláris kifejezések" = "reguláris kifejezések"). Egyszerűen fogalmazva, A RegExp egy olyan nyelv, ahol speciális karakterek és szabályok segítségével megkeresik a szükséges részkarakterláncokat a szövegben, kivonják azokat vagy helyettesítik más szöveggel.. A reguláris kifejezések egy nagyon hatékony és gyönyörű eszköz, amely nagyságrenddel felülmúlja a szöveggel való munka minden más módját. Számos programozási nyelv (C#, PHP, Perl, JavaScript…) és szövegszerkesztő (Word, Notepad++…) támogatja a reguláris kifejezéseket.

A Microsoft Excel sajnos nem rendelkezik RegExp támogatással, de ez VBA-val könnyen megoldható. Nyissa meg a Visual Basic Editort a lapról fejlesztő (Fejlesztő) vagy billentyűparancsot más+F11. Ezután helyezze be az új modult a menün keresztül Beszúrás – Modul és másolja oda a következő makrófüggvény szövegét:

Nyilvános függvény RegExpExtract(Szöveg karakterláncként, minta karakterláncként, opcionális elem egész számként = 1) karakterláncként hiba esetén GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Minta regex.Global = True If regex.T (Szöveg) Akkor Set matches = regex.Execute(Text) RegExpExtract = matches.Elem(Elem - 1) Kilépési függvény vége If ErrHandl: RegExpExtract = CVERr(xlErrValue) Funkció vége  

Most már bezárhatjuk a Visual Basic Editort, és visszatérhetünk az Excelbe, hogy kipróbálhassuk új funkciónkat. A szintaxisa a következő:

=RegExpExtract( Txt ; Pattern ; Item )

ahol

• txt – egy cella a szöveggel, amelyet ellenőrizünk, és amelyből ki akarjuk kinyerni a szükséges részkarakterláncot
• minta – maszk (minta) a karakterlánc-kereséshez
• Tétel – a kinyerendő részkarakterlánc sorszáma, ha több van belőlük (ha nincs megadva, akkor az első előfordulás jelenik meg)

A legérdekesebb itt természetesen a Pattern – egy speciális karakterekből álló sablonsor a RegExp „nyelvén”, amely megadja, hogy pontosan mit és hol akarunk megtalálni. Íme a legalapvetőbbek a kezdéshez:

Ha bizonyos számú karaktert keresünk, például egy hatjegyű irányítószámot vagy mind a hárombetűs termékkódot, akkor mi segítünk kvantifikátorok or kvantifikátorok speciális kifejezések, amelyek meghatározzák a keresendő karakterek számát. A kvantorok az előtte álló karakterre vonatkoznak:

Most pedig térjünk át a legérdekesebb részre – a létrehozott funkció alkalmazásának elemzésére, valamint arra, hogy mit tanultunk a mintákról az élet gyakorlati példái alapján.

Számok kinyerése szövegből

Kezdésként elemezzünk egy egyszerű esetet - az alfanumerikus zabkását kell kivonni az első számból, például a szünetmentes tápegységek teljesítményéről az árlistából:

A reguláris kifejezés mögötti logika egyszerű: d bármely számjegyet és a kvantort jelent + azt mondja, hogy számuk legyen egy vagy több. A függvény előtti dupla mínuszra azért van szükség, hogy a kivont karaktereket „menet közben” a szám mint szövegből teljes számmá alakítsa.

irányítószám

Első pillantásra itt minden egyszerű – pontosan hat számjegyet keresünk egymás után. Speciális karaktert használunk d számjegyhez és kvantorhoz 6 {} a karakterek számához:

Előfordulhat azonban olyan helyzet, amikor a sorban az indextől balra egy másik nagy számsor van sorban (telefonszám, TIN, bankszámla stb.) Ekkor az alapszakaszunk kihúzza az első 6-ot. számjegyek belőle, azaz nem fog megfelelően működni:

Ennek elkerülése érdekében egy módosítót kell hozzáadnunk a reguláris kifejezésünk élei köré b egy szó végét jelenti. Ez egyértelművé teszi az Excel számára, hogy a szükséges töredéknek (indexnek) külön szónak kell lennie, és nem egy másik töredéknek (telefonszámnak) kell lennie:

WhatsApp/Viber

A probléma a telefonszámok szövegben való megtalálásával az, hogy nagyon sok lehetőség van számok írására – kötőjellel és anélkül, szóközzel, zárójelben lévő régiókóddal vagy anélkül stb. Ezért véleményem szerint egyszerűbb először törölje ki ezeket a karaktereket a forrásszövegből több beágyazott függvény segítségével HELYETTES (HELYETTES)úgy, hogy egyetlen egésszé tapadjon össze, majd egy primitív szabályossal d{11} húzzon ki 11 számjegyet egymás után:

ITN

Itt egy kicsit bonyolultabb a helyzet, mert a TIN (hazánkban) lehet 10 jegyű (jogi személyeknél) vagy 12 jegyű (magánszemélyeknél). Ha különösen nem talál hibát, akkor nagyon is meg lehet elégedni a szokásosval d{10,12}, de szigorúan véve kihúz minden számot 10-től 12 karakterig, azaz hibásan beírt 11 számjegyet. Helyesebb lenne két logikai VAGY operátor által összekapcsolt mintát használni | (függőleges sáv):

Felhívjuk figyelmét, hogy a lekérdezésben először 12 bites számokat keresünk, és csak ezután 10 bites számokat. Ha a reguláris kifejezésünket fordítva írjuk, akkor mindenkinél, még a hosszú 12 bites TIN-eknél is csak az első 10 karaktert fogja kihúzni. Ez azt jelenti, hogy az első feltétel aktiválása után a további ellenőrzés már nem történik meg:

Ez az alapvető különbség a kezelő között | szabványos Excel logikai függvényből OR (OR), ahol az argumentumok átrendezése nem változtat az eredményen.

Termék cikkszámai

Sok vállalatnál egyedi azonosítókat rendelnek az árukhoz és szolgáltatásokhoz – cikkekhez, SAP kódokhoz, cikkszámokhoz stb. Ha van logika a jelölésükben, akkor reguláris kifejezésekkel könnyen kihúzhatók bármilyen szövegből. Például, ha tudjuk, hogy cikkeink mindig három nagy angol betűből, egy kötőjelből és egy ezt követő háromjegyű számból állnak, akkor:

A sablon mögötti logika egyszerű. [AZ] – a latin ábécé bármely nagybetűjét jelenti. A következő kvantor 3 {} azt mondja, hogy számunkra fontos, hogy pontosan három ilyen betű legyen. A kötőjel után három számjegyet várunk, így a végén adjuk hozzá d{3}

Készpénzes összegek

Az előző bekezdéshez hasonlóan az árakat (költségek, ÁFA…) is kihúzhatja az áruleírásból. Ha például a pénzösszegeket kötőjellel jelöljük, akkor:

Mintás d kvantorral + bármilyen számot keres kötőjelig, és d{2} fillérekért (két számjegy) fog keresni utána.

Ha nem árakat, hanem áfát kell kivonni, akkor használhatja a RegExpExtract függvényünk harmadik opcionális argumentumát, amely a kivonandó elem sorszámát adja meg. És természetesen helyettesítheti a funkciót HELYETTES (HELYETTES) az eredményekben kötőjellel jelölje be a szabványos tizedeselválasztót, és adjon hozzá dupla mínuszt az elejére, hogy az Excel a talált áfát normál számként értelmezze:

Autó rendszámok

Ha nem vesz speciális járműveket, pótkocsikat és egyéb motorkerékpárokat, akkor a szabványos autószámot a „betű – három szám – két betű – régiókód” elv szerint elemzi. Sőt, a régiókód lehet 2 vagy 3 számjegyű, és csak a latin ábécéhez hasonló megjelenésű betűket használunk. Így a következő reguláris kifejezés segít számokat kinyerni a szövegből:

Time

Az idő ÓÓ:PP formátumú kivonásához a következő reguláris kifejezés alkalmas:

A vastagbél töredéke után [0-5]d, amint az könnyen kitalálható, bármilyen számot beállít a 00-59 tartományban. A zárójelben lévő kettőspont előtt két minta működik, amelyeket logikai VAGY (pipe) választ el egymástól:

• [0-1]d – tetszőleges szám a 00-19 tartományban
• 2[0-3] – tetszőleges szám a 20-23 tartományban

A kapott eredményhez használhatja a szabványos Excel funkciót is IDŐ (CSAPAT)a program számára érthető és további számításokra alkalmas időformátumra konvertálni.

Jelszó ellenőrzés

Tegyük fel, hogy ellenőriznünk kell a felhasználók által kitalált jelszavak listájának helyességét. Szabályaink szerint a jelszavak csak angol betűket (kis- vagy nagybetűket) és számokat tartalmazhatnak. Szóközök, aláhúzásjelek és egyéb írásjelek nem megengedettek.

Az ellenőrzés a következő egyszerű reguláris kifejezéssel szervezhető:

Valójában egy ilyen mintánál megköveteljük, hogy a kezdetek között (^) és vége ($) szövegünkben csak a szögletes zárójelben megadott halmazból voltak karakterek. Ha a jelszó hosszát is ellenőrizni kell (például legalább 6 karakter), akkor a kvantor + formában helyettesíthető a „hat vagy több” szóközzel {6,}:

Város címről

Tegyük fel, hogy ki kell húznunk a várost a címsorból. A normál program segít, kivonja a szöveget a „g”-ből. a következő vesszőhöz:

Nézzük meg közelebbről ezt a mintát.

Ha elolvasta a fenti szöveget, akkor már megértette, hogy a reguláris kifejezések egyes karakterei (pontok, csillagok, dollárjelek stb.) különleges jelentéssel bírnak. Ha meg kell keresni ezeket a karaktereket, akkor egy fordított perjel előzi meg őket (néha úgy hívják). árnyékolás). Ezért a „g” töredék keresésekor. reguláris kifejezéssel kell írnunk Úr. ha pluszt keresünk, akkor + elvisszük helyi falvakba ahol megismerkedhet az őslakosok kultúrájával; ...

Sablonunk következő két karaktere, a pont és a kvantor csillag tetszőleges számú karaktert, azaz tetszőleges városnevet jelöl.

A sablon végén vessző található, mert a „g”-ből keresünk szöveget. vesszőhöz. De lehet több vessző is a szövegben, nem? Nem csak a város után, hanem az utca, házak, stb. után is. Melyiken áll meg a kérésünk? Erre való a kérdőjel. Enélkül a reguláris kifejezésünk a lehető leghosszabb karakterláncot húzná ki:

A reguláris kifejezések tekintetében egy ilyen minta „mohó”. A helyzet javításához kérdőjelre van szükség – ez teszi a kvantort, amely után „fukar” áll –, és a lekérdezésünk csak a „g” utáni első számlálóvesszőig viszi a szöveget:

Fájlnév a teljes elérési útból

Egy másik nagyon gyakori helyzet az, hogy a fájl nevét a teljes elérési útból kinyerjük. Az űrlap egyszerű reguláris kifejezése segít itt:

A trükk itt az, hogy a keresés valójában az ellenkező irányban történik – a végétől az elejéig, mert a sablonunk végén a $, és mindent előtte keresünk, egészen az első jobb oldali fordított perjelig. A fordított perjel meg van szökve, mint a pont az előző példában.

PS

„A vége felé” szeretném tisztázni, hogy a fentiek mind egy kis részét képezik a reguláris kifejezések által kínált lehetőségeknek. Nagyon sok speciális karakter és szabály létezik a használatukra, és egész könyvek születtek ebben a témában (kezdésnek legalább ezt ajánlom). A reguláris kifejezések írása bizonyos értelemben szinte művészet. Szinte mindig egy kitalált reguláris kifejezés javítható vagy kiegészíthető, elegánsabbá téve, vagy szélesebb körű bemeneti adatokkal használható.

Mások reguláris kifejezéseinek elemzéséhez és elemzéséhez, vagy saját hibakereséséhez számos kényelmes online szolgáltatás áll rendelkezésre: RegEx101, RegExr több

Sajnos a klasszikus reguláris kifejezések nem minden funkciója támogatott a VBA-ban (például fordított keresés vagy POSIX osztályok), és működik cirill betűkkel, de úgy gondolom, hogy ami van, az első alkalommal elég a kedvedre.

Ha nem ismeri a témát, és van mit megosztania, hagyjon az alábbi megjegyzésekben az Excelben való munka során hasznos reguláris kifejezéseket. Egy elme jó, de két csizma egy pár!

• Szöveg cseréje és tisztítása a SUBSTITUTE funkcióval
• Latin karakterek keresése és kiemelése a szövegben
• Keresse meg a legközelebbi hasonló szöveget (Ivanov = Ivonov = Ivanof stb.)