Analizatu testua adierazpen erregularrekin (RegExp) Excel-en

Excel-en testuarekin lan egitean denbora asko eta frustragarrienetako bat da analizatuz – “porridge” alfanumerikoa osagaietan analizatzea eta hortik behar ditugun zatiak ateratzea. Adibidez:

• posta-kodea helbidetik ateratzea (ona da posta-kodea beti hasieran badago, baina zer gertatzen da ez bada?)
• fakturaren zenbakia eta data bankuko laburpenean ordainketaren deskribapenetik aurkitzea
• TIN-a ateratzea kontraparteen zerrendako enpresen deskribapen bitxietatik
• bilatu auto-zenbakia edo artikulu-zenbakia deskribapenean, etab.
  

Normalean, horrelako kasuetan, testua eskuz jaso ordu erdi latz egin ondoren, prozesu hau automatizatzeko gogoetak etortzen hasten dira nolabait (batez ere datu asko badaude). Hainbat irtenbide daude eta konplexutasun-eraginkortasun maila ezberdinekin:

• Erabili Excel testu-funtzio integratuak testua bilatu-moztu-kola: LEVSIMV (EZKERRA), RIGHT (ESKUMENA), PSTR (erdialdea), STsEPIT (KATENATU) eta bere analogoak, UZTARTZEKO (TESTU BATU), EXACT (ZEHATZA) etab. Metodo hau ona da testuan logika argia badago (adibidez, aurkibidea beti helbidearen hasieran dago). Bestela, formulak askoz ere korapilatsuagoak izaten dira eta, batzuetan, array-formuletara ere iristen da, eta horrek asko moteltzen du mahai handietan.
• erabiliz testuaren antzekotasun operadorea bezala Visual Basic-etik makro funtzio pertsonalizatu batean bilduta. Honi esker, bilaketa malguagoa inplementatu dezakezu komodin karaktereak erabiliz (*, #,?, etab.) Zoritxarrez, tresna honek ezin du testutik nahi duzun azpikatea atera; egiaztatu bakarrik bertan dagoen ala ez.

Aurrekoaz gain, programatzaile profesionalen, web garatzaileen eta beste teknologia batzuen zirkulu estuetan oso ezaguna den beste ikuspegi bat dago. ohiko adierazpenak (Adierazpen erregularrak = RegExp = “regexps” = “erregularrak”). Besterik gabe, RegExp hizkuntza bat da, non karaktere eta arau bereziak erabiltzen diren testuan beharrezko azpikateak bilatzeko, ateratzeko edo beste testu batzuekin ordezkatzeko.. Adierazpen erregularrak oso tresna indartsu eta ederra dira, testua lantzeko beste modu guztiak magnitude-ordena batez gainditzen dituena. Programazio-lengoaia askok (C#, PHP, Perl, JavaScript...) eta testu-editoreek (Word, Notepad++...) adierazpen erregularrak onartzen dituzte.

Microsoft Excel-ek, zoritxarrez, ez du RegExp euskarria kutxatik kanpo, baina hori erraz konpondu daiteke VBArekin. Ireki Visual Basic editorea fitxatik developer (Garatzailea) edo teklatuko lasterbidea Alt+F11. Ondoren, sartu modulu berria menuan Txertatu – Modulua eta kopiatu bertan makro funtzio honen testua:

Funtzio publikoa RegExpExtract(Testua Kate gisa, Eredua Kate gisa, Aukerako elementua Integer gisa = 1) String gisa Errore batean GoTo ErrHandl Ezarri regex = CreateObject("VBScript.RegExp") regex.Pattern = Patroia regex.Global = Egia bada regex.Test (Testua) Ondoren, ezarri partidak = regex.Execute(Testua) RegExpExtract = bat datozenak.Item (Item - 1) Irten funtzioa Amaiera ErrHandl bada: RegExpExtract = CVErr (xlErrValue) Amaiera funtzioa  

Orain Visual Basic editorea itxi eta Excel-era itzuli dezakegu gure eginbide berria probatzeko. Bere sintaxia honakoa da:

=RegExpExtract (Txt; Eredua; Elementua)

non

• txt – Egiaztatzen ari garen eta behar dugun azpikatea atera nahi dugun testua duen gelaxka bat
• eredua – azpikateen bilaketarako maskara (eredua).
• Item – Erauzi beharreko azpikatearen sekuentzia-zenbakia, horietako batzuk badira (zehazten ez bada, lehenengo agerraldia bistaratuko da)

Hemen interesgarriena, noski, Pattern da - RegExp-en "hizkuntzan" karaktere berezien kate txantiloia, zeinak zehazki zer eta non aurkitu nahi dugun zehazten duena. Hona hemen hasteko oinarrizkoenak:

Karaktere kopuru jakin bat bilatzen ari bagara, adibidez, sei zifrako posta-kode bat edo hiru hizkiko produktu-kode guztiak, orduan salbatzera gatoz. zenbatzaile or zenbatzaile Bilatu beharreko karaktere kopurua zehazten duten esamolde bereziak dira. Aurretik datorren karaktereari kuantifikatzaileak aplikatzen zaizkio:

Orain pasa gaitezen zatirik interesgarrienera: sortutako funtzioaren aplikazioaren analisia eta bizitzako adibide praktikoen ereduei buruz ikasi genuena.

Testutik zenbakiak ateratzea

Hasteko, azter dezagun kasu sinple bat: lehen zenbakia atera behar duzu porridge alfanumerikotik, adibidez, etenik gabeko hornikuntzaren potentzia prezioen zerrendatik:

Adierazpen erregularraren atzean dagoen logika sinplea da: d edozein zifra esan nahi du, eta zenbatzailea + dio haien kopurua bat edo gehiago izan behar dela. Funtzioaren aurrean minus bikoitza behar da "hegan" ateratako karaktereak zenbaki-testutik zenbaki oso batean bihurtzeko.

KP

Lehen begiratuan, dena erraza da hemen - zehazki sei digitu bilatzen ari gara jarraian. Pertsonaia berezi bat erabiltzen dugu d zifrarako eta zenbatzailerako 6 {} karaktere kopuruagatik:

Hala ere, egoera bat posible da lerroko indizearen ezkerraldean beste zenbaki multzo handi bat dagoenean jarraian (telefono zenbakia, TIN, banku-kontua, etab.) Orduan gure denboraldi erregularrak lehenengo 6ak aterako ditu. bertatik datozen digituak, hau da, ez du behar bezala funtzionatuko:

Hori gerta ez dadin, modifikatzaile bat gehitu behar dugu gure adierazpen erregularraren ertzetan b hitz baten amaiera adieraziz. Horrek argi utziko dio Excel-i behar dugun zatia (indizea) hitz bereizia izan behar dela, eta ez beste zati baten (telefono-zenbakia):

Telefonoa

Testuan telefono-zenbaki bat aurkitzeko arazoa da zenbakiak idazteko aukera asko daudela: marratxoekin eta gabe, zuriunez, parentesi artean eskualde-kode batekin edo gabe, etab. Horregatik, nire ustez, errazagoa da lehenik eta behin, garbitu karaktere horiek guztiak sorburuko testutik habiaratu diren hainbat funtzio erabiliz ORDUTEGIA (ORDENAK)beraz, osotasun bakar batean itsasten da, eta gero erregular primitibo batekin d{11} atera 11 zifra jarraian:

ITN

Hemen pixka bat konplikatuagoa da, TIN (Gure Herrian) 10 digitukoa (pertson juridikoentzat) edo 12 zifrakoa (pertsonalentzat) izan daitekeelako. Batez ere akatsik aurkitzen ez baduzu, oso posible da ohikoarekin asetzea d{10,12}, baina, hertsiki esanda, 10 eta 12 karaktere arteko zenbaki guztiak aterako ditu, hau da, 11 zifra oker sartuta. Zuzenagoa litzateke EDO operadore logiko batek konektatutako bi eredu erabiltzea | (barra bertikala):

Kontuan izan kontsultan lehenik 12 biteko zenbakiak bilatzen ditugula, eta gero bakarrik 10 biteko zenbakiak. Gure adierazpen erregularra alderantziz idazten badugu, guztiontzat aterako da, baita 12 biteko TIN luzeak ere, lehenengo 10 karaktereak bakarrik. Hau da, lehen baldintza abiarazi ondoren, egiaztapen gehiago ez da egiten:

Hau da operadorearen arteko funtsezko aldea | Excel funtzio logiko estandar batetik OR (OR), non argumentuak berrantolatzeak ez duen emaitza aldatzen.

Produktuen SKUak

Enpresa askotan, identifikatzaile bakarrak esleitzen zaizkie ondasunei eta zerbitzuei: artikuluak, SAP kodeak, SKUak, etab. Haien idazkeran logikarik badago, edozein testutik erraz atera daitezke adierazpen erregularrak erabiliz. Adibidez, badakigu gure artikuluak ingelesezko hiru letra larriz, marratxo bat eta ondorengo hiru zifrako zenbakiz osatuta daudela beti, orduan:

Txantiloiaren atzean dagoen logika sinplea da. [AZ] – latindar alfabetoko edozein letra larriz esan nahi du. Hurrengo zenbatzailea 3 {} dio guretzat garrantzitsua dela halako hiru letrak egotea. Marratxoaren ondoren, hiru zifren zain gaude, beraz, amaieran gehitzen dugu d{3}

Diru-kopuruak

Aurreko paragrafoaren antzera, salgaien deskribapenetik prezioak ere atera ditzakezu (kostuak, BEZa...). Diru-kopuruak, adibidez, marratxo batekin adierazten badira, orduan:

Eredua d zenbatzailearekin + edozein zenbaki bilatzen du marratxo bat arte, eta d{2} zentimoak (bi zifra) bilatuko ditu ondoren.

Prezioak ez, BEZa baizik atera behar badituzu, orduan gure RegExpExtract funtzioaren hirugarren aukerako argumentua erabil dezakezu, erauzi beharreko elementuaren zenbaki ordinala zehazten duena. Eta, noski, funtzioa ordeztu dezakezu ORDUTEGIA (ORDENAK) emaitzetan, marratxoa bereizle hamartar estandarrari eta gehitu minus bikoitza hasieran, Excel-ek aurkitutako BEZa zenbaki normal gisa interpreta dezan:

Kotxeen plakaren zenbakiak

Ibilgailu bereziak, atoiak eta beste motozikleta batzuk hartzen ez badituzu, orduan autoaren zenbakia estandarra "letra - hiru zenbaki - bi letra - eskualdeko kodea" printzipioaren arabera analizatzen da. Gainera, eskualde-kodea 2 edo 3 zifrakoa izan daiteke, eta latin-alfabetoaren itxuraz antzekoak direnak bakarrik erabiltzen dira letra gisa. Horrela, honako adierazpen erregular honek testutik zenbakiak ateratzen lagunduko digu:

Ordua

Ordua HH:MM formatuan ateratzeko, honako adierazpen erregular hau egokia da:

Kolone zatiaren ondoren [0-5]d, irudikatzeko erraza denez, 00-59 bitarteko edozein zenbaki ezartzen du. Parentesi arteko bi puntuen aurretik, bi eredu funtzionatzen dute, OR logiko batek (hodia) bereizita:

• [0-1]d – 00-19 bitarteko edozein zenbaki
• 2[0-3] – 20-23 bitarteko edozein zenbaki

Lortutako emaitzari, gainera, Excel funtzio estandarra aplika diezaiokezu TIME (TALDEA)programarako ulergarria den eta kalkulu gehiago egiteko egokia den denbora formatuan bihurtzeko.

Pasahitza egiaztatzea

Demagun erabiltzaileek asmatutako pasahitzen zerrenda zuzena dela egiaztatu behar dugula. Gure arauen arabera, pasahitzek ingelesezko letrak (maiuskulak edo xeheak) eta zenbakiak izan ditzakete. Ez dira onartzen zuriuneak, azpimarrak eta bestelako puntuazio-ikurrak.

Egiaztapena honako adierazpen erregular sinple hau erabiliz antola daiteke:

Izan ere, halako eredu batekin hasiera artean (^) eta amaiera ($) gure testuan kortxete artean emandako multzoko karaktereak baino ez zeuden. Pasahitzaren luzera ere egiaztatu behar baduzu (adibidez, gutxienez 6 karaktere), orduan zenbatzailea + formularioan "sei edo gehiago" tartearekin ordezka daiteke {6,}:

Helbidetik hiria

Demagun hiria helbide barratik atera behar dugula. Ohiko programak lagunduko du, testua "g"-tik ateratzen. hurrengo komara:

Ikus diezaiogun eredu honi hurbilagotik.

Goiko testua irakurri baduzu, jadanik ulertu zenuen esamolde erregularetako karaktere batzuek (puntuak, izartxoak, dolarearen zeinuak, etab.) esanahi berezia dutela. Karaktere hauek beraiek bilatu behar badituzu, atzera barra bat jarriko dute aurretik (batzuetan deitzen da estali). Hori dela eta, "g" zatia bilatzean. adierazpen erregularrean idatzi behar dugu Mr. plus baten bila bagabiltza, orduan + eta abar.

Gure txantiloiaren hurrengo bi karaktereek, puntua eta izartxo kuantifikatzailea, edozein karaktereren kopurua adierazten dute, hau da, edozein hiri-izena.

Txantiloiaren amaieran koma bat dago, "g"-ko testua bilatzen ari garelako. koma batera. Baina testuan hainbat koma egon daitezke, ezta? Hiriaren ondotik ez ezik, kalearen, etxeen eta abarren ondoren ere. Horietako zeinetan geldituko da gure eskaera? Horretarako da galdera ikurra. Hori gabe, gure adierazpen erregularrak ahalik eta kate luzeena aterako luke:

Adierazpen erregularrei dagokienez, halako eredua "zikoitsua" da. Egoera zuzentzeko, galdera ikurra behar da –zenbatzailea eta horren ondoren “zikorrak” jartzen ditu – eta gure kontsultak testua “g” ondoko lehenengo kontagailu komaren arte bakarrik hartzen du:

Fitxategiaren izena bide osotik

Oso ohikoa den beste egoera bat fitxategiaren izena bide osotik ateratzea da. Formaren adierazpen erregular sinple batek lagunduko du hemen:

Hemen trikimailua da bilaketa, hain zuzen ere, kontrako norabidean gertatzen dela - amaieratik hasierara, gure txantiloiaren amaieran baitago. $, eta aurretik dena bilatzen ari gara eskuinetik lehen barrara arte. Atzeko barrari ihes egiten zaio, aurreko adibideko puntuan bezala.

PS

“Azken aldera” argitu nahi dut aurreko guztia adierazpen erregularrek ematen dituzten aukera guztien zati txiki bat dela. Erabilerarako karaktere eta arau berezi asko daude, eta liburu osoak idatzi dira gai honi buruz (hasteko behintzat gomendatzen dut). Nolabait esateko, adierazpen erregularrak idaztea ia arte bat da. Ia beti, asmatutako adierazpen erregular bat hobetu edo osatu daiteke, dotoreagoa edo sarrerako datu sorta zabalagoarekin lan egiteko gai izan dadin.

Besteen esamolde erregularrak aztertu eta analizatzeko edo zureak arazteko, sareko hainbat zerbitzu eroso daude: RegEx101, RegExr eta gehiago

Zoritxarrez, adierazpen erregular klasikoen ezaugarri guztiak ez dira onartzen VBAn (adibidez, alderantzizko bilaketa edo POSIX klaseak) eta zirilikoarekin lan egin dezakete, baina uste dut dagoena nahikoa dela lehen aldiz zure gustura egoteko.

Gaian berria ez bazara eta partekatzeko zerbait baduzu, utzi adierazpen erregularrak erabilgarriak Excel-en lan egiten duzunean beheko iruzkinetan. Buru bat ona da, baina bi bota pare bat!

• ORDEZKO funtzioarekin testua ordezkatzea eta garbitzea
• Testuan latinezko karaktereak bilatzea eta nabarmentzea
• Bilatu hurbilen dagoen antzeko testua (Ivanov = Ivonov = Ivanof, etab.)