Parsējiet tekstu ar regulārām izteiksmēm (RegExp) programmā Excel

Viens no laikietilpīgākajiem un nomāktākajiem uzdevumiem, strādājot ar tekstu programmā Excel, ir parsēšana – burtu un ciparu “putras” parsēšana komponentos un mums nepieciešamo fragmentu izvilkšana no tās. Piemēram:

• pasta indeksa izvilkšana no adreses (labi, ja pasta indekss vienmēr ir sākumā, bet ja tā nav?)
• rēķina numura un datuma atrašana no maksājuma apraksta bankas izrakstā
• NĪN izņemšana no darījuma partneru sarakstā esošo uzņēmumu raibajiem aprakstiem
• aprakstā meklē automašīnas numuru vai preces numuru utt.
  

Parasti šādos gadījumos pēc pusstundas drūmās teksta manuālas smelšanas sāk nākt prātā domas kaut kā automatizēt šo procesu (sevišķi, ja datu ir daudz). Ir vairāki risinājumi un ar dažādu sarežģītības un efektivitātes pakāpi:

• lietošana iebūvētās Excel teksta funkcijas lai meklētu-izgrieztu-līmētu tekstu: LEVSIMV (KREISI), TIESĪBAS (PA LABI), PSTR (vidus), STsEPIT (SAISTĒT) un tā analogi, COMBINE (JOINTEXT), TIKAI (PRECĪTI) utt. Šī metode ir laba, ja tekstā ir skaidra loģika (piemēram, indekss vienmēr ir adreses sākumā). Pretējā gadījumā formulas kļūst daudz sarežģītākas, un dažreiz runa ir pat par masīvu formulām, kas lielās tabulās ievērojami palēninās.
• Izmantojot piemēram, teksta līdzības operators no Visual Basic, kas ietīts pielāgotā makro funkcijā. Tas ļauj ieviest elastīgāku meklēšanu, izmantojot aizstājējzīmes (*, #,? utt.) Diemžēl šis rīks nevar izvilkt no teksta vēlamo apakšvirkni – pārbaudiet tikai, vai tā ir ietverta.

Papildus iepriekšminētajam ir vēl viena pieeja, kas ir ļoti labi zināma šaurās profesionālu programmētāju, tīmekļa izstrādātāju un citu tehniķu aprindās – tā ir regulāras izteiksmes (Regulārās izteiksmes = RegExp = "regulārie izteiksmes" = "regulārie"). Vienkārši liec, RegExp ir valoda, kurā tiek izmantotas speciālās rakstzīmes un kārtulas, lai tekstā meklētu nepieciešamās apakšvirknes, izvilktu tās vai aizstātu ar citu tekstu.. Regulārās izteiksmes ir ļoti spēcīgs un skaists rīks, kas ar lielumu pārspēj visus citus veidus, kā strādāt ar tekstu. Daudzas programmēšanas valodas (C#, PHP, Perl, JavaScript…) un teksta redaktori (Word, Notepad++…) atbalsta regulārās izteiksmes.

Diemžēl programmā Microsoft Excel nav pieejams RegExp atbalsts, taču to var viegli novērst, izmantojot VBA. Cilnē atveriet Visual Basic redaktoru attīstītājs (Izstrādātājs) vai īsinājumtaustiņš cits+F11. Pēc tam izvēlnē ievietojiet jauno moduli Ievietot - modulis un kopējiet tur šādas makro funkcijas tekstu:

Publiskā funkcija RegExpExtract(teksts kā virkne, modelis kā virkne, neobligāts vienums kā vesels skaitlis = 1) kā virkne kļūdas gadījumā GoTo ErrHandl Iestatīt regex = CreateObject("VBScript.RegExp") regex.Pattern = modelis regex.Global = patiess, ja regex.Test (Teksts) Pēc tam iestatiet atbilstības = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVERr(xlErrValue) beigu funkcija  

Tagad mēs varam aizvērt Visual Basic redaktoru un atgriezties programmā Excel, lai izmēģinātu mūsu jauno funkciju. Tās sintakse ir šāda:

=RegExpExtract(Txt; Pattern; Item)

kur

• txt – šūna ar tekstu, kuru pārbaudām un no kuras vēlamies izvilkt vajadzīgo apakšvirkni
• modelis – maska ​​(raksts) apakšvirknes meklēšanai
• Punkts - izvelkamās apakšvirknes kārtas numurs, ja tās ir vairākas (ja nav norādīts, tiek parādīts pirmais gadījums)

Interesantākais šeit, protams, ir Pattern – speciālo rakstzīmju veidnes virkne RegExp “valodā”, kas norāda, ko tieši un kur vēlamies atrast. Tālāk ir norādīti visvienkāršākie, lai sāktu darbu.

Ja meklējam noteiktu rakstzīmju skaitu, piemēram, sešciparu pasta indeksu vai visus trīs burtu preču kodus, tad nākam palīgā kvantifikatori or kvantifikatori ir īpašas izteiksmes, kas norāda meklējamo rakstzīmju skaitu. Kvantifikatori tiek piemēroti rakstzīmei, kas ir pirms tā:

Tagad pāriesim pie interesantākās daļas – izveidotās funkcijas pielietojuma analīzi un to, ko mēs uzzinājām par modeļiem uz praktiskiem dzīves piemēriem.

Ciparu izvilkšana no teksta

Sākumā analizēsim vienkāršu gadījumu – no burtciparu putras jāizvelk pirmais cipars, piemēram, nepārtrauktās barošanas avotu jauda no cenrāža:

Regulārās izteiksmes loģika ir vienkārša: d nozīmē jebkuru ciparu un kvantoru + saka, ka to skaitam jābūt vienam vai vairākiem. Divkāršais mīnuss funkcijas priekšā ir nepieciešams, lai “lidojumā” pārvērstu izvilktās rakstzīmes par pilnu skaitli no numura kā teksta.

Pasta indekss

No pirmā acu uzmetiena šeit viss ir vienkārši – mēs meklējam tieši sešus ciparus pēc kārtas. Mēs izmantojam īpašu rakstzīmi d ciparam un kvantatoram 6 {} rakstzīmju skaitam:

Tomēr ir iespējama situācija, kad rindā pa kreisi no indeksa ir vēl viens liels skaitļu kopums rindā (tālruņa numurs, TIN, bankas konts utt.) Tad mūsu regulārā sezona izvilks pirmos 6 cipari no tā, ti, nedarbosies pareizi:

Lai tas nenotiktu, mums ir jāpievieno modifikators ap mūsu regulārās izteiksmes malām b kas apzīmē vārda beigas. Tādējādi programmai Excel būs skaidrs, ka vajadzīgajam fragmentam (indeksam) jābūt atsevišķam vārdam, nevis cita fragmenta (tālruņa numura) daļai:

Mob. tālr.

Problēma ar tālruņa numura atrašanu tekstā ir tā, ka ir tik daudz iespēju ciparu rakstīšanai – ar defisēm un bez tām, caur atstarpēm, ar vai bez reģiona koda iekavās utt. Tāpēc, manuprāt, ir vieglāk vispirms notīriet visas šīs rakstzīmes no avota teksta, izmantojot vairākas ligzdotas funkcijas Aizstāt (AIZSTĀTĀJS)lai tas saliptu vienotā veselumā un tad ar primitīvu regulāru d{11} izvelciet 11 ciparus pēc kārtas:

ITN

Šeit ir nedaudz sarežģītāk, jo TIN (Mūsu valstī) var būt 10 ciparu (juridiskām personām) vai 12 ciparu (fiziskām personām). Ja īpaši neatrodat vainu, tad pilnīgi iespējams būt apmierinātam ar parasto d{10,12}, bet, stingri ņemot, tas izvilks visus skaitļus no 10 līdz 12 rakstzīmēm, ti, un kļūdaini ievadīti 11 cipari. Pareizāk būtu izmantot divus modeļus, kas savienoti ar loģisku VAI operatoru | (vertikālā josla):

Lūdzu, ņemiet vērā, ka vaicājumā vispirms tiek meklēti 12 bitu skaitļi un tikai pēc tam 10 bitu skaitļi. Ja mēs rakstām savu regulāro izteiksmi otrādi, tad tā izvilks visiem, pat gariem 12 bitu TIN, tikai pirmās 10 rakstzīmes. Tas nozīmē, ka pēc pirmā nosacījuma aktivizēšanas turpmāka pārbaude vairs netiek veikta:

Šī ir galvenā atšķirība starp operatoru | no standarta Excel loģikas funkcijas OR (OR), kur argumentu pārkārtošana nemaina rezultātu.

Produktu SKU

Daudzos uzņēmumos unikālie identifikatori tiek piešķirti precēm un pakalpojumiem – rakstiem, SAP kodiem, SKU utt. Ja to apzīmējumā ir loģika, tad tos var viegli izvilkt no jebkura teksta, izmantojot regulārās izteiksmes. Piemēram, ja mēs zinām, ka mūsu raksti vienmēr sastāv no trim lielajiem angļu burtiem, defises un sekojoša trīsciparu skaitļa, tad:

Veidnes loģika ir vienkārša. [AZ] – nozīmē jebkurus lielos latīņu alfabēta burtus. Nākamais kvantētājs 3 {} saka, ka mums ir svarīgi, lai šādi burti būtu tieši trīs. Pēc defises mēs gaidām trīs ciparus, tāpēc mēs pievienojam beigās d{3}

Skaidras naudas summas

Līdzīgi kā iepriekšējā rindkopā, jūs varat arī izņemt cenas (izmaksas, PVN …) no preču apraksta. Ja naudas summas, piemēram, ir norādītas ar defisi, tad:

Modelis d ar kvantatoru + meklē jebkuru skaitli līdz defisei un d{2} meklēs santīmus (divi cipari) pēc.

Ja jums ir jāizņem nevis cenas, bet gan PVN, tad varat izmantot mūsu funkcijas RegExpExtract trešo izvēles argumentu, kas norāda izvelkamā elementa kārtas numuru. Un, protams, jūs varat aizstāt funkciju Aizstāt (AIZSTĀTĀJS) rezultātos atzīmējiet defisi līdz standarta decimālo atdalītāju un pievienojiet dubulto mīnusu sākumā, lai Excel interpretētu atrasto PVN kā parastu skaitli:

Automašīnu numura numuri

Ja neņem speciālos transportlīdzekļus, piekabes un citus motociklus, tad standarta automašīnas numurs tiek parsēts pēc principa “burts – trīs cipari – divi burti – reģiona kods”. Turklāt reģiona kods var būt 2 vai 3 ciparu, un kā burti tiek izmantoti tikai tie, kas pēc izskata ir līdzīgi latīņu alfabētam. Tādējādi šī regulārā izteiksme palīdzēs mums izvilkt skaitļus no teksta:

Laiks

Lai iegūtu laiku formātā HH:MM, ir piemērota šāda regulārā izteiksme:

Pēc resnās zarnas fragments [0-5]d, kā to ir viegli izdomāt, iestata jebkuru skaitli diapazonā no 00 līdz 59. Pirms kola iekavās darbojas divi modeļi, kas atdalīti ar loģisku VAI (caurule):

• [0-1]d – jebkurš skaitlis diapazonā no 00 līdz 19
• 2[0-3] – jebkurš skaitlis diapazonā no 20 līdz 23

Iegūtajam rezultātam varat papildus izmantot standarta Excel funkciju LAIKS (KOMANDA)lai to pārvērstu programmai saprotamā un turpmākiem aprēķiniem piemērotā laika formātā.

Paroles pārbaude

Pieņemsim, ka mums ir jāpārbauda lietotāju izgudroto paroļu saraksta pareizība. Saskaņā ar mūsu noteikumiem paroles var saturēt tikai angļu valodas burtus (mazos vai lielos) un ciparus. Atstarpes, pasvītras un citas pieturzīmes nav atļautas.

Pārbaudi var organizēt, izmantojot šādu vienkāršu regulāro izteiksmi:

Faktiski, izmantojot šādu modeli, mēs pieprasām, lai starp sākuma (^) un beigas ($) mūsu tekstā bija tikai rakstzīmes no kvadrātiekavās norādītās kopas. Ja jāpārbauda arī paroles garums (piemēram, vismaz 6 rakstzīmes), tad kvantors + formā var aizstāt ar intervālu “seši vai vairāk”. {6,}:

Pilsēta no adreses

Pieņemsim, ka mums ir jāatrod pilsēta no adreses joslas. Parastā programma palīdzēs, izvelkot tekstu no “g”. uz nākamo komatu:

Apskatīsim šo modeli tuvāk.

Ja esat izlasījis iepriekš minēto tekstu, tad jau sapratāt, ka dažām rakstzīmēm regulārajās izteiksmēs (punkti, zvaigznītes, dolāra zīmes utt.) ir īpaša nozīme. Ja jums ir jāmeklē pašas šīs rakstzīmes, tad pirms tām ir slīpsvītra (dažreiz to sauc pasargājot). Tāpēc, meklējot fragmentu “g.” mums ir jāraksta regulārā izteiksmē Kungs ja meklējam plusu, tad + un tā joprojām

Nākamās divas rakstzīmes mūsu veidnē, punkts un kvantatora zvaigznīte, apzīmē jebkuru rakstzīmju skaitu, ti, jebkuru pilsētas nosaukumu.

Veidnes beigās ir komats, jo mēs meklējam tekstu no “g”. uz komatu. Bet tekstā var būt vairāki komatiņi, vai ne? Ne tikai aiz pilsētas, bet arī pēc ielas, mājām utt. Uz kuru no tām mūsu pieprasījums apstāsies? Tam ir domāta jautājuma zīme. Bez tā mūsu regulārā izteiksme izvilktu pēc iespējas garāko virkni:

Regulāro izteiksmju ziņā šāds modelis ir “mantkārīgs”. Lai labotu situāciju, ir nepieciešama jautājuma zīme — tā padara kvantatoru, pēc kura tas ir “skops”, un mūsu vaicājums aizņem tekstu tikai līdz pirmajam skaitītāja komatam pēc “g”.

Faila nosaukums no pilna ceļa

Vēl viena ļoti izplatīta situācija ir faila nosaukuma izvilkšana no pilna ceļa. Šeit palīdzēs vienkārša regulāra veidlapas izteiksme:

Šeit triks ir tāds, ka meklēšana faktiski notiek pretējā virzienā - no beigām līdz sākumam, jo ​​mūsu veidnes beigās ir $, un mēs meklējam visu pirms tā līdz pirmajai slīpsvītrai no labās puses. Atkāpes slīpsvītra tiek izmantota, tāpat kā punkts iepriekšējā piemērā.

PS

“Tuvojoties uz beigām” Es vēlos precizēt, ka viss iepriekš minētais ir neliela daļa no visām regulāro izteiksmju sniegtajām iespējām. Ir daudz īpašu rakstzīmju un to lietošanas noteikumu, un par šo tēmu ir uzrakstītas veselas grāmatas (iesākumam iesaku vismaz šo). Savā ziņā regulāru izteiksmju rakstīšana ir gandrīz māksla. Gandrīz vienmēr izdomātu regulāro izteiksmi var uzlabot vai papildināt, padarot to elegantāku vai spējīgāku strādāt ar plašāku ievaddatu klāstu.

Lai analizētu un parsētu citu cilvēku regulārās izteiksmes vai atkļūdotu savu, ir vairāki ērti tiešsaistes pakalpojumi: RegEx101, RegExr un vairāk

Diemžēl ne visas klasisko regulāro izteiksmju iespējas tiek atbalstītas VBA (piemēram, reversā meklēšana vai POSIX klases) un var darboties ar kirilicu, taču domāju, ka ar to, kas ir, pirmajā reizē pietiek, lai iepriecinātu.

Ja neesat iesācējs šajā tēmā un jums ir ar ko dalīties, zemāk esošajos komentāros atstājiet regulāras izteiksmes, kas ir noderīgas, strādājot programmā Excel. Viens prāts ir labs, bet divi zābaki ir pāris!

• Teksta aizstāšana un tīrīšana ar funkciju SUBSTITUTE
• Latīņu rakstzīmju meklēšana un izcelšana tekstā
• Meklēt tuvāko līdzīgu tekstu (Ivanov = Ivonov = Ivanof utt.)