Analizați textul cu expresii regulate (RegExp) în Excel

Una dintre cele mai consumatoare de timp și cele mai frustrante sarcini atunci când lucrați cu text în Excel este analizare – analizarea „terciului” alfanumeric în componente și extragerea fragmentelor de care avem nevoie din el. De exemplu:

• extragerea codului poștal din adresă (este bine dacă codul poștal este întotdeauna la început, dar dacă nu este?)
• aflarea numarului si a datei facturii din descrierea platii din extrasul bancar
• extragerea TIN-ului din descrieri pestrițe ale companiilor din lista contrapărților
• căutați un număr de mașină sau un număr de articol în descriere etc.
  

De obicei, în astfel de cazuri, după o jumătate de oră de luare manuală a textului, încep să vină în minte gânduri pentru a automatiza acest proces (mai ales dacă există o mulțime de date). Există mai multe soluții și cu grade diferite de complexitate-eficiență:

• Utilizare funcții de text Excel încorporate pentru a căuta text-cut-lipire: LEVSIMV (STÂNGA), DREAPTA (DREAPTA), PSTR (mijlocul), STsEPIT (ÎNLĂNŢUI) și analogii săi, COMBINA (JOINTEXT), CORECT (CORECT) etc. Această metodă este bună dacă există o logică clară în text (de exemplu, indexul este întotdeauna la începutul adresei). Altfel, formulele devin mult mai complicate și, uneori, se ajunge chiar și la formule matrice, care încetinește foarte mult pe tabele mari.
• Utilizarea ca operator de similaritate text din Visual Basic împachetat într-o funcție macro personalizată. Acest lucru vă permite să implementați o căutare mai flexibilă folosind caractere wildcard (*, #,?, etc.) Din păcate, acest instrument nu poate extrage subșirul dorit din text - verificați doar dacă este conținut în el.

În plus față de cele de mai sus, există o altă abordare care este foarte bine cunoscută în cercurile restrânse de programatori profesioniști, dezvoltatori web și alți tehnicieni - aceasta este expresii obisnuite (Expresii regulate = RegExp = „regexps” = „regulars”). Pur și simplu pune, RegExp este o limbă în care sunt folosite caractere și reguli speciale pentru a căuta subșirurile necesare în text, pentru a le extrage sau pentru a le înlocui cu alt text. Expresiile regulate sunt un instrument foarte puternic și frumos care depășește toate celelalte moduri de lucru cu textul cu un ordin de mărime. Multe limbaje de programare (C#, PHP, Perl, JavaScript...) și editori de text (Word, Notepad++...) acceptă expresii regulate.

Microsoft Excel, din păcate, nu are suport RegExp din cutie, dar acest lucru poate fi rezolvat cu ușurință cu VBA. Deschideți Editorul Visual Basic din filă dezvoltator (Dezvoltator) sau scurtătură de la tastatură Alt+F11. Apoi introduceți noul modul prin meniu Inserare – Modul și copiați acolo textul următoarei funcții macro:

Funcție publică RegExpExtract (Text ca șir, model ca șir, element opțional ca întreg = 1) ca șir la eroare GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True Dacă regex.Test (Text) Apoi Set matchs = regex.Execute(Text) RegExpExtract = matchs.Item(Item - 1) Ieșire Funcție End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Acum putem închide Editorul Visual Basic și să revenim la Excel pentru a încerca noua noastră funcție. Sintaxa sa este următoarea:

=RegExpExtract(Txt; Model; Articol)

Unde

• txt – o celulă cu textul pe care îl verificăm și din care dorim să extragem subșirul de care avem nevoie
• model – masca (model) pentru căutarea subșirurilor
• Articol – numărul de secvență al subșirului de extras, dacă sunt mai multe (dacă nu este specificat, atunci este afișată prima apariție)

Cel mai interesant lucru aici, desigur, este Pattern – un șablon șir de caractere speciale „în limba” RegExp, care specifică ce anume și unde vrem să găsim. Iată cele mai de bază pentru a începe:

Dacă căutăm un anumit număr de caractere, de exemplu, un cod poștal din șase cifre sau toate codurile de produs din trei litere, atunci venim în ajutor cuantificatoare or cuantificatoare sunt expresii speciale care specifică numărul de caractere care trebuie căutate. Cuantificatorii sunt aplicați caracterului care vine înaintea lui:

Acum să trecem la partea cea mai interesantă – o analiză a aplicării funcției create și a ceea ce am învățat despre tiparele pe exemple practice din viață.

Extragerea numerelor din text

Pentru început, să analizăm un caz simplu - trebuie să extrageți primul număr din terci alfanumeric, de exemplu, puterea surselor de alimentare neîntreruptibile din lista de prețuri:

Logica din spatele expresiei regulate este simplă: d înseamnă orice cifră și cuantificatorul + spune că numărul lor ar trebui să fie unul sau mai mulți. Minusul dublu din fața funcției este necesar pentru a converti „din zbor” caracterele extrase într-un număr complet din numărul ca text.

Cod poştal

La prima vedere, totul este simplu aici - căutăm exact șase cifre la rând. Folosim un caracter special d pentru cifră și cuantificator 6 {} pentru numărul de caractere:

Cu toate acestea, este posibilă o situație când, în stânga indexului din rând, există un alt set mare de numere la rând (număr de telefon, TIN, cont bancar etc.) Atunci sezonul nostru regulat va scoate primele 6 cifre din acesta, adică nu va funcționa corect:

Pentru a preveni acest lucru, trebuie să adăugăm un modificator în jurul marginilor expresiei noastre regulate b semnificând sfârşitul unui cuvânt. Acest lucru va clarifica pentru Excel că fragmentul (indexul) de care avem nevoie ar trebui să fie un cuvânt separat și nu o parte dintr-un alt fragment (număr de telefon):

Telefon

Problema cu găsirea unui număr de telefon în text este că există atât de multe opțiuni de scriere a numerelor – cu și fără cratime, prin spații, cu sau fără un cod de regiune între paranteze etc. Prin urmare, în opinia mea, este mai ușor să mai întâi curățați toate aceste caractere din textul sursă folosind mai multe funcții imbricate SUBSTITUI (SUBSTITUI)astfel încât să se lipească într-un singur întreg și apoi cu un obișnuit primitiv d{11} scoateți 11 cifre la rând:

ITN

Este puțin mai complicat aici, deoarece TIN (în țara noastră) poate fi de 10 cifre (pentru persoane juridice) sau de 12 cifre (pentru persoane fizice). Dacă nu găsești greșeli în special, atunci este foarte posibil să fii mulțumit de obișnuit d{10,12}, dar, strict vorbind, va scoate toate numerele de la 10 la 12 caractere, adică și a introdus eronat 11 cifre. Ar fi mai corect să folosiți două modele conectate printr-un operator logic OR | (bară verticală):

Vă rugăm să rețineți că în interogare căutăm mai întâi numere de 12 biți și abia apoi numere de 10 biți. Dacă ne scriem expresia obișnuită invers, atunci se va extrage pentru toată lumea, chiar și pentru TIN-uri lungi de 12 biți, doar pentru primele 10 caractere. Adică, după declanșarea primei condiții, nu se mai efectuează verificarea ulterioară:

Aceasta este diferența fundamentală dintre operator | dintr-o funcție logică standard Excel OR (SAU), unde rearanjarea argumentelor nu modifică rezultatul.

SKU-urile produselor

În multe companii, identificatori unici sunt alocați bunurilor și serviciilor – articole, coduri SAP, SKU-uri etc. Dacă există o logică în notația lor, atunci acestea pot fi extrase cu ușurință din orice text folosind expresii regulate. De exemplu, dacă știm că articolele noastre constau întotdeauna din trei litere mari de limba engleză, o cratimă și un număr ulterior de trei cifre, atunci:

Logica din spatele șablonului este simplă. [AZ] – înseamnă orice majuscule ale alfabetului latin. Următorul cuantificator 3 {} spune că este important pentru noi să existe exact trei astfel de litere. După cratima, așteptăm trei cifre, așa că adăugăm la sfârșit d{3}

Sume în numerar

În mod similar cu paragraful anterior, puteți, de asemenea, să scoateți prețuri (costuri, TVA ...) din descrierea mărfurilor. Dacă sumele monetare, de exemplu, sunt indicate cu o cratimă, atunci:

Model d cu cuantificator + caută orice număr până la o cratimă și d{2} va căuta bănuți (două cifre) după.

Dacă trebuie să extrageți nu prețuri, ci TVA, atunci puteți utiliza al treilea argument opțional al funcției noastre RegExpExtract, care specifică numărul ordinal al elementului care trebuie extras. Și, desigur, puteți înlocui funcția SUBSTITUI (SUBSTITUI) în rezultate, cratima la separatorul zecimal standard și adăugați un minus dublu la început, astfel încât Excel să interpreteze TVA-ul găsit ca un număr normal:

Numerele de plăcuță auto

Dacă nu luați vehicule speciale, remorci și alte motociclete, atunci numărul standard al mașinii este analizat conform principiului „litera – trei numere – două litere – cod de regiune”. Mai mult decât atât, codul de regiune poate fi de 2 sau 3 cifre și doar cele care sunt similare ca aspect cu alfabetul latin sunt folosite ca litere. Astfel, următoarea expresie regulată ne va ajuta să extragem numere din text:

Timp

Pentru a extrage ora în formatul HH:MM, este potrivită următoarea expresie regulată:

După fragment de colon [0-5]d, așa cum este ușor de înțeles, stabilește orice număr în intervalul 00-59. Înainte de două puncte din paranteză, funcționează două modele, separate printr-un SAU logic (conductă):

• [0-1]d – orice număr în intervalul 00-19
• 2[0-3] – orice număr în intervalul 20-23

La rezultatul obținut, puteți aplica suplimentar funcția standard Excel TIMP (ECHIPĂ)pentru a-l converti într-un format de timp care este înțeles de program și potrivit pentru calcule ulterioare.

Verificarea parolei

Să presupunem că trebuie să verificăm corectitudinea listei de parole inventate de utilizatori. Conform regulilor noastre, parolele pot conține doar litere în limba engleză (minuscule sau majuscule) și numere. Spațiile, liniuțele de subliniere și alte semne de punctuație nu sunt permise.

Verificarea poate fi organizată folosind următoarea expresie regulată simplă:

De fapt, cu un astfel de model cerem ca între început (^) si sfarsit ($) în textul nostru erau doar caractere din setul dat între paranteze drepte. Dacă trebuie să verificați și lungimea parolei (de exemplu, cel puțin 6 caractere), atunci cuantificatorul + poate fi înlocuit cu intervalul „șase sau mai mult” din formular {6,}:

Orașul de la adresa

Să presupunem că trebuie să scoatem orașul din bara de adrese. Programul obișnuit va ajuta, extragând textul din „g”. la următoarea virgulă:

Să aruncăm o privire mai atentă la acest model.

Dacă ai citit textul de mai sus, atunci ai înțeles deja că unele caractere din expresiile obișnuite (puncte, asteriscuri, semne de dolar etc.) au o semnificație aparte. Dacă trebuie să cauți singuri aceste personaje, atunci ele sunt precedate de o bară oblică inversă (uneori numită ocrotitor). Prin urmare, atunci când căutați fragmentul „g”. trebuie să scriem în expresie regulată Dl. dacă căutăm un plus, atunci + etc

Următoarele două caractere din șablonul nostru, punctul și asteriscul cuantificatorului, reprezintă orice număr de caractere, adică orice nume de oraș.

Există o virgulă la sfârșitul șablonului, deoarece căutăm text din „g”. la o virgulă. Dar pot fi mai multe virgule în text, nu? Nu numai după oraș, ci și după stradă, case etc. Pe care dintre ele se va opri cererea noastră? Pentru asta este semnul intrebarii. Fără el, expresia noastră regulată ar scoate cel mai lung șir posibil:

În ceea ce privește expresiile regulate, un astfel de model este „lacom”. Pentru a corecta situația, este nevoie de un semn de întrebare – face cuantificatorul după care stă „zgârcit” – iar interogarea noastră duce textul doar până la prima virgulă contor după „g.”:

Numele fișierului din calea completă

O altă situație foarte comună este extragerea numelui fișierului din calea completă. O expresie regulată simplă a formei va ajuta aici:

Trucul aici este că căutarea, de fapt, are loc în direcția opusă – de la sfârșit până la început, deoarece la sfârșitul șablonului nostru este $, și căutăm totul înainte de el până la prima bară oblică inversă din dreapta. Bara oblică inversă este eliminată, ca punctul din exemplul anterior.

PS

„Spre sfârșit” vreau să clarific că toate cele de mai sus reprezintă o mică parte din toate posibilitățile pe care le oferă expresiile regulate. Există o mulțime de caractere și reguli speciale pentru utilizarea lor și s-au scris cărți întregi pe această temă (recomand cel puțin pe aceasta pentru început). Într-un fel, scrierea expresiilor regulate este aproape o artă. Aproape întotdeauna, o expresie regulată inventată poate fi îmbunătățită sau completată, făcând-o mai elegantă sau capabilă să lucreze cu o gamă mai largă de date de intrare.

Pentru a analiza și analiza expresiile regulate ale altor persoane sau pentru a le depana pe ale dvs., există mai multe servicii online convenabile: RegEx101, RegExr şi mai mult

Din păcate, nu toate caracteristicile expresiilor regulate clasice sunt acceptate în VBA (de exemplu, căutare inversă sau clase POSIX) și pot funcționa cu chirilic, dar cred că ceea ce există este suficient pentru prima dată pentru a vă mulțumi.

Dacă nu sunteți nou în acest subiect și aveți ceva de împărtășit, lăsați expresii regulate utile atunci când lucrați în Excel în comentariile de mai jos. O singură minte este bună, dar două cizme sunt o pereche!

• Înlocuirea și curățarea textului cu funcția SUBSTITUTE
• Căutarea și evidențierea caracterelor latine în text
• Căutați cel mai apropiat text similar (Ivanov = Ivonov = Ivanof etc.)