Razčlenite besedilo z regularnimi izrazi (RegExp) v Excelu

Ena izmed najbolj zamudnih in frustrirajočih nalog pri delu z besedilom v Excelu je razčlenjevanje – razčleniti alfanumerično "kašo" na komponente in iz nje izluščiti fragmente, ki jih potrebujemo. Na primer:

• črpanje poštne številke iz naslova (dobro je, če je poštna številka vedno na začetku, kaj pa če ni?)
• iskanje številke in datuma računa iz opisa plačila na bančnem izpisku
• črpanje TIN iz pestrih opisov podjetij na seznamu nasprotnih strank
• iskanje številke avtomobila ali številke artikla v opisu itd.
  

Običajno se v takih primerih po pol ure turobnega ročnega brskanja po besedilu začnejo pojavljati misli, da bi nekako avtomatizirali ta proces (še posebej, če je podatkov veliko). Obstaja več rešitev z različnimi stopnjami kompleksnosti in učinkovitosti:

• Uporaba vgrajene besedilne funkcije Excel za iskanje-izrezovanje-lepljenje besedila: LEVSIMV (LEVO), PRAVICA (PRAV), PSTR (sredina), STsEPIT (ZDRUŽI) in njegovi analogi, KOMBIN (SKUPNO BESEDILO), Točno (TOČNO) itd. Ta metoda je dobra, če je v besedilu jasna logika (na primer, indeks je vedno na začetku naslova). V nasprotnem primeru se formule precej zakomplicirajo in včasih pride celo do matričnih formul, kar se pri velikih tabelah močno upočasni.
• Uporaba kot operator podobnosti besedila iz Visual Basica, zavitega v makro funkcijo po meri. To vam omogoča, da izvedete bolj prilagodljivo iskanje z uporabo nadomestnih znakov (*, #,?, itd.). Na žalost to orodje ne more izvleči želenega podniza iz besedila – preverite samo, ali je v njem.

Poleg naštetega obstaja še en pristop, ki je zelo dobro znan v ozkih krogih profesionalnih programerjev, spletnih razvijalcev in drugih tehnikov – to je pravilni izrazi (Regularni izrazi = RegExp = “regexps” = “regulars”). Enostavno povedano, RegExp je jezik, kjer se posebni znaki in pravila uporabljajo za iskanje potrebnih podnizov v besedilu, njihovo ekstrahiranje ali zamenjavo z drugim besedilom. Regularni izrazi so zelo zmogljivo in lepo orodje, ki za red velikosti presega vse druge načine dela z besedilom. Številni programski jeziki (C#, PHP, Perl, JavaScript…) in urejevalniki besedil (Word, Notepad++…) podpirajo regularne izraze.

Microsoft Excel žal nima takojšnje podpore za RegExp, vendar je to mogoče enostavno popraviti z VBA. Odprite urejevalnik Visual Basic na zavihku razvijalec (Razvijalec) ali bližnjico na tipkovnici druga+F11. Nato vstavite nov modul skozi meni Vstavi – Modul in tja kopirajte besedilo naslednje funkcije makra:

Javna funkcija RegExpExtract(besedilo kot niz, vzorec kot niz, izbirni element kot celo število = 1) kot niz ob napaki GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = vzorec regex.Global = True If regex.Test (Besedilo) Potem Set matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Zdaj lahko zapremo urejevalnik Visual Basic in se vrnemo v Excel, da preizkusimo našo novo funkcijo. Njegova sintaksa je naslednja:

=RegExpExtract( Txt ; vzorec ; element )

Kje

• txt – celica z besedilom, ki ga preverjamo in iz katerega želimo izluščiti podniz, ki ga potrebujemo
• vzorec – maska ​​(vzorec) za iskanje podniza
• Postavka – zaporedna številka podniza, ki ga je treba ekstrahirati, če jih je več (če ni podana, se izpiše prva pojavitev)

Najbolj zanimiv pri tem je seveda Pattern – predlogni niz posebnih znakov “v jeziku” RegExp, ki določa, kaj točno in kje želimo najti. Tu so najosnovnejši za začetek:

Če iščemo določeno število znakov, na primer šestmestno poštno številko ali vse tričrkovne kode izdelkov, potem priskočimo na pomoč. količniki or količniki so posebni izrazi, ki določajo število znakov za iskanje. Kvantifikatorji se uporabijo za znak, ki je pred njim:

Zdaj pa preidimo na najbolj zanimiv del – analizo uporabe ustvarjene funkcije in tega, kar smo o vzorcih izvedeli na praktičnih primerih iz življenja.

Izvleček številk iz besedila

Za začetek analizirajmo preprost primer - iz alfanumerične kaše morate izvleči prvo številko, na primer moč brezprekinitvenih napajalnikov s cenika:

Logika za regularnim izrazom je preprosta: d pomeni poljubno števko in kvantifikator + pravi, da mora biti njihovo število ena ali več. Dvojni minus pred funkcijo je potreben za "sprotno" pretvorbo ekstrahiranih znakov v polno število iz števila kot besedila.

Poštna številka

Na prvi pogled je tukaj vse preprosto - iščemo točno šest števk v vrsti. Uporabljamo poseben znak d za števko in kvantifikator 6 {} za število znakov:

Možna pa je situacija, ko je levo od indeksa v vrstici še en velik niz številk v vrsti (telefonska številka, TIN, bančni račun itd.). Potem bo naša redna sezona izločila prvih 6 števk iz njega, tj. ne bo delovalo pravilno:

Da se to ne bi zgodilo, moramo okoli robov našega regularnega izraza dodati modifikator b ki označuje konec besede. Tako bo Excelu jasno, da mora biti fragment (indeks), ki ga potrebujemo, ločena beseda in ne del drugega fragmenta (telefonske številke):

Telefon

Težava pri iskanju telefonske številke v besedilu je, da obstaja veliko možnosti za pisanje številk – z in brez vezajev, s presledki, z ali brez regionalne kode v oklepaju itd. Zato je po mojem mnenju lažje najprej počistite vse te znake iz izvornega besedila z več ugnezdenimi funkcijami NAMESTITEV (ZAMENJAVA)tako da se zlepi v enotno celoto, nato pa s primitivnim rednim d{11} izvlecite 11 števk v vrsti:

ITN

Tukaj je malo bolj zapleteno, saj je TIN (pri nas) lahko 10-mestna (za pravne osebe) ali 12-mestna (za fizične osebe). Če ne najdete posebne napake, potem je povsem mogoče biti zadovoljen z rednim d{10,12}, vendar bo, strogo gledano, izvlekel vse številke od 10 do 12 znakov, torej in napačno vnesenih 11 števk. Bolj pravilno bi bilo uporabiti dva vzorca, povezana z logičnim operatorjem ALI | (navpična vrstica):

Upoštevajte, da v poizvedbi najprej iščemo 12-bitna števila in šele nato 10-bitna števila. Če naš regularni izraz zapišemo obratno, potem bo za vse, tudi za dolge 12-bitne številke TIN, izvlekel samo prvih 10 znakov. To pomeni, da se po sprožitvi prvega pogoja nadaljnje preverjanje ne izvaja več:

To je temeljna razlika med operaterjem | iz standardne excelove logične funkcije OR (OR), kjer preurejanje argumentov ne spremeni rezultata.

Šifre izdelkov

V mnogih podjetjih so edinstveni identifikatorji dodeljeni blagu in storitvam – artiklom, kodam SAP, SKU-jem itd. Če je v njihovem zapisu logika, jih je mogoče zlahka izvleči iz katerega koli besedila z uporabo regularnih izrazov. Na primer, če vemo, da so naši članki vedno sestavljeni iz treh velikih angleških črk, vezaja in naslednje trimestne številke, potem:

Logika predloge je preprosta. [AZ] – pomeni vse velike črke latinske abecede. Naslednji kvantifikator 3 {} pravi, da je za nas pomembno, da so takšne črke točno tri. Za vezajem čakamo na tri števke, zato dodajamo na koncu d{3}

Gotovinski zneski

Na podoben način kot v prejšnjem odstavku lahko iz opisa blaga izvlečete tudi cene (stroški, DDV …). Če so denarni zneski na primer označeni z vezajem, potem:

Vzorec d s kvantifikatorjem + išče poljubno število do vezaja in d{2} poišče penije (dvomestno).

Če ne želite izvleči cen, ampak DDV, potem lahko uporabite tretji izbirni argument naše funkcije RegExpExtract, ki določa redno številko elementa, ki ga želite izvleči. In seveda lahko zamenjate funkcijo NAMESTITEV (ZAMENJAVA) v rezultatih vstavite vezaj na standardno decimalno ločilo in na začetku dodajte dvojni minus, da Excel najdeni DDV interpretira kot običajno številko:

Številke avtomobilskih tablic

Če ne vzamete posebnih vozil, prikolic in drugih motornih koles, se standardna številka avtomobila razčleni po načelu "črka - tri številke - dve črki - koda regije". Poleg tega je regionalna koda lahko 2- ali 3-mestna, kot črke pa se uporabljajo le tiste, ki so po videzu podobne latinični abecedi. Tako nam bo naslednji regularni izraz pomagal izluščiti številke iz besedila:

čas

Za ekstrahiranje časa v formatu HH:MM je primeren naslednji regularni izraz:

Po fragmentu debelega črevesa [0-5]d, kot je enostavno ugotoviti, nastavi poljubno število v območju 00-59. Pred dvopičjem v oklepaju delujeta dva vzorca, ločena z logičnim ALI (cev):

• [0-1]d – katero koli število v območju 00-19
• 2[0-3] – katero koli število v območju 20-23

Za dobljeni rezultat lahko dodatno uporabite standardno funkcijo Excel ČAS (EKIPA)pretvoriti v časovni format, ki je programu razumljiv in primeren za nadaljnje izračune.

Preverjanje gesla

Recimo, da moramo preveriti pravilnost seznama gesel, ki so si jih izmislili uporabniki. Po naših pravilih lahko gesla vsebujejo samo angleške črke (male ali velike) in številke. Presledki, podčrtaji in druga ločila niso dovoljeni.

Preverjanje je mogoče organizirati z naslednjim preprostim regularnim izrazom:

Pravzaprav s takim vzorcem zahtevamo, da med začetkom (^) in konec ($) v našem besedilu so bili samo znaki iz nabora, navedenega v oglatih oklepajih. Če morate preveriti tudi dolžino gesla (na primer vsaj 6 znakov), potem kvantifikator + lahko nadomestite z intervalom "šest ali več" v obrazcu {6,}:

Mesto od naslova

Recimo, da moramo potegniti mesto iz naslovne vrstice. Pomagal vam bo običajni program, ki bo izvlekel besedilo iz "g." na naslednjo vejico:

Oglejmo si ta vzorec pobližje.

Če ste prebrali zgornje besedilo, ste že razumeli, da imajo nekateri znaki v regularnih izrazih (pike, zvezdice, znaki za dolar itd.) poseben pomen. Če morate poiskati te znake same, potem je pred njimi poševnica nazaj (včasih imenovana zaščita). Zato pri iskanju fragmenta "g." pisati moramo v regularnem izrazu G. če iščemo plus, torej + in tako naprej

Naslednja dva znaka v naši predlogi, pika in kvantifikator zvezdica, pomenita poljubno število poljubnih znakov, tj. poljubno ime mesta.

Na koncu predloge je vejica, ker iščemo besedilo iz "g." do vejice. V besedilu pa je lahko več vejic, kajne? Ne samo po mestu, ampak tudi po ulici, hiši itd. Na kateri od njih se bo naša zahteva ustavila? Temu je namenjen vprašaj. Brez tega bi naš regularni izraz izvlekel najdaljši možni niz:

V smislu regularnih izrazov je tak vzorec "požrešen". Da bi popravili situacijo, je potreben vprašaj – zaradi tega je kvantifikator, za katerim stoji, »skop« – in naša poizvedba prevzame besedilo samo do prve protivejice za »g.«:

Ime datoteke iz celotne poti

Druga zelo pogosta situacija je ekstrahiranje imena datoteke iz celotne poti. Tu bo v pomoč preprost regularni izraz obrazca:

Trik je v tem, da se iskanje pravzaprav odvija v nasprotni smeri – od konca proti začetku, ker je na koncu naše predloge $, in iščemo vse pred njim do prve poševnice nazaj z desne. Poševnica nazaj je ubežna, tako kot pika v prejšnjem primeru.

PS

»Proti koncu« želim pojasniti, da je vse zgoraj našteto majhen del vseh možnosti, ki jih ponujajo regularni izrazi. Posebnih znakov in pravil za njihovo uporabo je ogromno, na to temo so napisane cele knjige (za začetek priporočam vsaj tole). Na nek način je pisanje stalnih izrazov skoraj umetnost. Skoraj vedno je mogoče izumljeni regularni izraz izboljšati ali dopolniti, zaradi česar je bolj eleganten ali zmožen delati s širšim obsegom vhodnih podatkov.

Za analizo in razčlenjevanje regularnih izrazov drugih ljudi ali odpravljanje napak v lastnih je na voljo več priročnih spletnih storitev: RegEx101, RegExr in še več

Na žalost v VBA niso podprte vse funkcije klasičnih regularnih izrazov (na primer povratno iskanje ali razredi POSIX) in lahko delujejo s cirilico, vendar menim, da je to, kar je tam, dovolj za prvič, da vas zadovolji.

Če niste novi v tej temi in imate nekaj za deliti, v spodnjih komentarjih pustite regularne izraze, uporabne pri delu v Excelu. Enoumnost je dobra, a dva škornja sta par!

• Zamenjava in čiščenje besedila s funkcijo SUBSTITUTE
• Iskanje in označevanje latiničnih znakov v besedilu
• Poiščite najbližje podobno besedilo (Ivanov = Ivonov = Ivanof itd.)