Analizoni tekstin me shprehje të rregullta (RegExp) në Excel

Një nga detyrat që kërkon kohë dhe më frustruese kur punoni me tekst në Excel është analizë – analizimi i “qullit” alfanumerik në përbërës dhe nxjerrja e fragmenteve që na duhen prej tij. Për shembull:

• nxjerrja e kodit postar nga adresa (është mirë nëse kodi postar është gjithmonë në fillim, por çka nëse nuk është?)
• gjetjen e numrit dhe datës së faturës nga përshkrimi i pagesës në pasqyrën bankare
• nxjerrja e TIN-it nga përshkrimet e ndryshme të kompanive në listën e palëve
• kërkoni për një numër makine ose numrin e artikullit në përshkrim, etj.
  

Zakonisht në raste të tilla, pas gjysmë ore zgjedhjeje të zymtë në tekst me dorë, mendimet fillojnë të vijnë në mendje disi për të automatizuar këtë proces (veçanërisht nëse ka shumë të dhëna). Ka disa zgjidhje dhe me shkallë të ndryshme kompleksiteti-efikasiteti:

• përdorim funksionet e integruara të tekstit në Excel për të kërkuar tekstin-prerje-ngjit: LEVSIMV (Majtas), E DREJTA (E DREJTA), PSTR (mesi), STsEPIT (KONKATENIM) dhe analogët e tij, COMBINE (JOINTEXT), saktë (SAKTE) etj. Kjo metodë është e mirë nëse ka një logjikë të qartë në tekst (për shembull, indeksi është gjithmonë në fillim të adresës). Përndryshe, formulat bëhen shumë më të komplikuara dhe, ndonjëherë, vjen deri te formulat e grupeve, të cilat ngadalësohen shumë në tavolina të mëdha.
• Përdorim si operatori i ngjashmërisë së tekstit nga Visual Basic i mbështjellë në një funksion makro të personalizuar. Kjo ju lejon të zbatoni një kërkim më fleksibël duke përdorur shkronja të egra (*, #,?, etj.) Fatkeqësisht, ky mjet nuk mund të nxjerrë nënvargun e dëshiruar nga teksti - kontrolloni vetëm nëse ai është i përfshirë në të.

Përveç sa më sipër, ekziston një qasje tjetër që është shumë e njohur në rrethet e ngushta të programuesve profesionistë, zhvilluesve të uebit dhe teknikëve të tjerë - kjo është shprehje të rregullta (Shprehje të rregullta = RegExp = "regexps" = "të rregullta"). E thënë thjesht, RegExp është një gjuhë ku karaktere dhe rregulla të veçanta përdoren për të kërkuar nënvargjet e nevojshme në tekst, për t'i nxjerrë ato ose për t'i zëvendësuar me tekst tjetër.. Shprehjet e rregullta janë një mjet shumë i fuqishëm dhe i bukur që tejkalon të gjitha mënyrat e tjera të punës me tekstin me një renditje madhësie. Shumë gjuhë programimi (C#, PHP, Perl, JavaScript…) dhe redaktues teksti (Word, Notepad++…) mbështesin shprehjet e rregullta.

Microsoft Excel për fat të keq nuk ka mbështetje RegExp jashtë kutisë, por kjo mund të rregullohet lehtësisht me VBA. Hapni Redaktorin Visual Basic nga skeda zhvillues (Zhvilluesi) ose shkurtore të tastierës Ndalet+F11. Më pas futni modulin e ri përmes menysë Insert – Moduli dhe kopjoni tekstin e funksionit makro të mëposhtëm atje:

Funksioni publik RegExpExtract(Teksti si varg, modeli si varg, artikulli opsional si numër i plotë = 1) si varg në gabim Shkoni në ErrHandl Vendosni regex = CreateObject("VBScript.RegExp") regex. Modeli = Modeli regex.Global = i vërtetë nëse (Tekst) Pastaj Cakto ndeshjet = regex.Execute(Text) RegExpExtract = matches.Item(Artikulli - 1) Dil Funksionin Fund Nëse ErrHandl: RegExpExtract = CVERr(xlErrValue) Funksioni Fund  

Tani mund të mbyllim Redaktuesin Visual Basic dhe të kthehemi në Excel për të provuar veçorinë tonë të re. Sintaksa e tij është si më poshtë:

=RegExpExtract(Txt; Modeli; Artikull)

ku

• txt – një qelizë me tekstin që po kontrollojmë dhe nga e cila duam të nxjerrim nënvargun që na nevojitet
• model – maskë (modeli) për kërkimin e nënvargut
• Artikull – numri i sekuencës së nënvargut që do të nxirret, nëse ka disa prej tyre (nëse nuk specifikohet, atëherë shfaqet shfaqja e parë)

Gjëja më interesante këtu, natyrisht, është Pattern - një varg shabllonesh karakteresh speciale "në gjuhën" e RegExp, i cili specifikon se çfarë saktësisht dhe ku duam të gjejmë. Këtu janë ato më themelore për të filluar:

Nëse jemi duke kërkuar për një numër të caktuar karakteresh, për shembull, një kod postar gjashtëshifror ose të gjitha kodet e produktit me tre shkronja, atëherë ne vijmë në shpëtim kuantifikues or kuantifikues janë shprehje të veçanta që përcaktojnë numrin e karaktereve që duhen kërkuar. Kuantifikuesit zbatohen për karakterin që vjen përpara tij:

Tani le të kalojmë në pjesën më interesante - një analizë e aplikimit të funksionit të krijuar dhe asaj që mësuam rreth modeleve në shembuj praktikë nga jeta.

Nxjerrja e numrave nga teksti

Për të filluar, le të analizojmë një rast të thjeshtë - ju duhet të nxirrni numrin e parë nga qulli alfanumerik, për shembull, fuqinë e furnizimit me energji të pandërprerë nga lista e çmimeve:

Logjika pas shprehjes së rregullt është e thjeshtë: d do të thotë çdo shifër dhe sasior + thotë se numri i tyre duhet të jetë një ose më shumë. Minusi i dyfishtë përpara funksionit është i nevojshëm për të kthyer "në fluturim" karakteret e nxjerra në një numër të plotë nga numri-si-tekst.

Kodin postar

Në shikim të parë, gjithçka është e thjeshtë këtu - ne po kërkojmë saktësisht gjashtë shifra me radhë. Ne përdorim një karakter të veçantë d për shifrën dhe kuantifikuesin 6 {} për numrin e karaktereve:

Sidoqoftë, një situatë është e mundur kur, në të majtë të indeksit në rresht, ka një grup tjetër të madh numrash me radhë (numri i telefonit, TIN, llogaria bankare, etj.) Pastaj sezoni ynë i rregullt do të tërheqë 6 të parët shifrat prej tij, dmth nuk do të funksionojnë siç duhet:

Për të parandaluar që kjo të ndodhë, ne duhet të shtojmë një modifikues rreth skajeve të shprehjes sonë të rregullt b që tregon fundin e një fjale. Kjo do ta bëjë të qartë për Excel se fragmenti (indeksi) që na nevojitet duhet të jetë një fjalë e veçantë dhe jo pjesë e një fragmenti tjetër (numri telefoni):

Numri telefonit

Problemi me gjetjen e një numri telefoni në tekst është se ka kaq shumë opsione për të shkruar numra - me dhe pa viza, përmes hapësirave, me ose pa një kod rajoni në kllapa, etj. Prandaj, për mendimin tim, është më e lehtë të së pari pastroni të gjitha këto karaktere nga teksti burimor duke përdorur disa funksione të ndërthurura ZUBVENDITSUESI (ZËVENDËSUES)në mënyrë që të ngjitet së bashku në një tërësi të vetme, dhe më pas me një të rregullt primitive d{11} nxirrni 11 shifra me radhë:

ITN

Këtu është pak më e ndërlikuar, sepse TIN (në vendin tonë) mund të jetë 10-shifror (për personat juridikë) ose 12-shifror (për individët). Nëse nuk gjeni gabime veçanërisht, atëherë është mjaft e mundur të jeni të kënaqur me të rregulltin d{10,12}, por, në mënyrë rigoroze, do të tërheqë të gjithë numrat nga 10 në 12 karaktere, dmth dhe të futura gabimisht 11 shifra. Do të ishte më e saktë të përdorni dy modele të lidhura nga një operator logjik OR | (shirit vertikal):

Ju lutemi vini re se në pyetje ne fillimisht kërkojmë numra 12-bitësh, dhe vetëm atëherë për numra 10-bitësh. Nëse e shkruajmë shprehjen tonë të rregullt anasjelltas, atëherë ajo do të tërhiqet për të gjithë, madje edhe TIN-et e gjata 12-bit, vetëm 10 karakteret e para. Kjo do të thotë, pasi të jetë shkaktuar kushti i parë, verifikimi i mëtejshëm nuk kryhet më:

Ky është ndryshimi thelbësor midis operatorit | nga një funksion standard logjik excel OR (OR), ku riorganizimi i argumenteve nuk e ndryshon rezultatin.

SKU-të e produktit

Në shumë kompani, mallrave dhe shërbimeve u caktohen identifikues unikë - artikuj, kode SAP, SKU, etj. Nëse ka logjikë në shënimin e tyre, atëherë ato mund të nxirren lehtësisht nga çdo tekst duke përdorur shprehje të rregullta. Për shembull, nëse e dimë se artikujt tanë përbëhen gjithmonë nga tre shkronja të mëdha angleze, një vizë ndarëse dhe një numër pasues treshifror, atëherë:

Logjika pas shabllonit është e thjeshtë. [AZ] – nënkupton çdo shkronjë të madhe të alfabetit latin. Kuantifikuesi i radhës 3 {} thotë se është e rëndësishme për ne që të jenë saktësisht tre shkronja të tilla. Pas vizës presim tre shifra, ndaj shtojmë në fund d{3}

Shumat e parave të gatshme

Në mënyrë të ngjashme me paragrafin e mëparshëm, ju gjithashtu mund të tërhiqni çmimet (kostot, TVSH ...) nga përshkrimi i mallrave. Nëse shumat monetare, për shembull, tregohen me vizë, atëherë:

Model d me kuantifikues + kërkon për çdo numër deri në vizë, dhe d{2} do të kërkojë qindarka (dy shifra) pas.

Nëse ju duhet të nxirrni jo çmimet, por TVSH-në, atëherë mund të përdorni argumentin e tretë opsional të funksionit tonë RegExpExtract, i cili specifikon numrin rendor të elementit që do të nxirret. Dhe, sigurisht, mund ta zëvendësoni funksionin ZUBVENDITSUESI (ZËVENDËSUES) në rezultate, vizatoni në ndarësin standard dhjetor dhe shtoni një minus të dyfishtë në fillim në mënyrë që Excel ta interpretojë TVSH-në e gjetur si një numër normal:

Numrat e targave të makinave

If you do not take special vehicles, trailers and other motorcycles, then the standard car number is parsed according to the principle “letter – three numbers – two letters – region code”. Moreover, the region code can be 2- or 3-digit, and only those that are similar in appearance to the Latin alphabet are used as letters. Thus, the following regular expression will help us to extract numbers from the text:

kohë

Për të nxjerrë kohën në formatin HH:MM, shprehja e rregullt e mëposhtme është e përshtatshme:

Pas fragmentit të zorrës së trashë [0-5]d, siç është e lehtë të kuptohet, vendos çdo numër në rangun 00-59. Para dy pikave në kllapa, funksionojnë dy modele, të ndara nga një OR (tub) logjik:

• [0-1]d – çdo numër në rangun 00-19
• 2[0-3] – çdo numër në rangun 20-23

Për rezultatin e marrë, mund të aplikoni gjithashtu funksionin standard Excel TIME (EKIPI)për ta kthyer atë në një format kohor që është i kuptueshëm për programin dhe i përshtatshëm për llogaritjet e mëtejshme.

Kontrolli i fjalëkalimit

Supozoni se duhet të kontrollojmë listën e fjalëkalimeve të shpikura nga përdoruesit për korrektësi. Sipas rregullave tona, fjalëkalimet mund të përmbajnë vetëm shkronja angleze (të vogla ose të mëdha) dhe numra. Hapësirat, nënvizat dhe shenjat e tjera të pikësimit nuk lejohen.

Kontrolli mund të organizohet duke përdorur shprehjen e thjeshtë të mëposhtme të rregullt:

Në fakt, me një model të tillë ne kërkojmë që midis fillimit (^) dhe fund ($) në tekstin tonë kishte vetëm karaktere nga grupi i dhënë në kllapa katrore. Nëse gjithashtu duhet të kontrolloni gjatësinë e fjalëkalimit (për shembull, të paktën 6 karaktere), atëherë sasia + mund të zëvendësohet me intervalin "gjashtë ose më shumë" në formë {6,}:

Qyteti nga adresa

Le të themi se duhet ta tërheqim qytetin nga shiriti i adresave. Programi i rregullt do të ndihmojë, duke nxjerrë tekstin nga "g". në presjen tjetër:

Le të hedhim një vështrim më të afërt në këtë model.

Nëse e keni lexuar tekstin e mësipërm, atëherë e keni kuptuar tashmë se disa karaktere në shprehje të rregullta (periudha, yje, shenja dollari, etj.) kanë një kuptim të veçantë. Nëse duhet t'i kërkoni vetë këta personazhe, atëherë ata paraprihen nga një vijë e prapme (nganjëherë quhet mbrojtur). Prandaj, kur kërkoni për fragmentin "g". duhet të shkruajmë në shprehje të rregullt Zoti nëse po kërkojmë një plus, atëherë + etj.

Dy karakteret e ardhshme në shabllonin tonë, pika dhe ylli sasior, përfaqësojnë çdo numër të çdo karakteri, dmth çdo emër qyteti.

Ka një presje në fund të shabllonit, sepse ne po kërkojmë tekst nga "g". në një presje. Por mund të ketë disa presje në tekst, apo jo? Jo vetëm pas qytetit, por edhe pas rrugës, shtëpive etj. Në cilën prej tyre do të ndalet kërkesa jonë? Për këtë është pikëpyetja. Pa të, shprehja jonë e rregullt do të nxirrte vargun më të gjatë të mundshëm:

Për sa i përket shprehjeve të rregullta, një model i tillë është "i babëzitur". Për të korrigjuar situatën, nevojitet një pikëpyetje - ai bën kuantifikuesin pas të cilit qëndron "koprrac" - dhe pyetja jonë e çon tekstin vetëm deri në presjen e parë kundër-pas "g.":

Emri i skedarit nga shtegu i plotë

Një situatë tjetër shumë e zakonshme është nxjerrja e emrit të skedarit nga shtegu i plotë. Një shprehje e thjeshtë e rregullt e formularit do të ndihmojë këtu:

Truku këtu është se kërkimi, në fakt, ndodh në drejtim të kundërt - nga fundi në fillim, sepse në fund të shabllonit tonë është $, dhe ne po kërkojmë gjithçka përpara saj deri në vijën e parë të prapme nga e djathta. Vija e prapme është ikje, si pika në shembullin e mëparshëm.

PS

“Drejt fundit” dua të sqaroj se të gjitha sa më sipër janë një pjesë e vogël e të gjitha mundësive që ofrojnë shprehjet e rregullta. Ka shumë karaktere të veçanta dhe rregulla për përdorimin e tyre, dhe libra të tërë janë shkruar për këtë temë (unë rekomandoj të paktën këtë për fillim). Në një farë mënyre, të shkruarit e shprehjeve të rregullta është pothuajse një art. Pothuajse gjithmonë, një shprehje e rregullt e shpikur mund të përmirësohet ose plotësohet, duke e bërë atë më elegante ose të aftë për të punuar me një gamë më të gjerë të dhënash hyrëse.

Për të analizuar dhe analizuar shprehjet e rregullta të njerëzve të tjerë ose për të korrigjuar gabimet tuaja, ekzistojnë disa shërbime të përshtatshme në internet: RegEx101, RegExr më shumë

Fatkeqësisht, jo të gjitha tiparet e shprehjeve të rregullta klasike mbështeten në VBA (për shembull, kërkimi i kundërt ose klasat POSIX) dhe mund të funksionojnë me cirilik, por mendoj se ajo që është atje është e mjaftueshme për herë të parë për t'ju kënaqur.

Nëse nuk jeni i ri në këtë temë dhe keni diçka për të ndarë, lini shprehje të rregullta të dobishme kur punoni në Excel në komentet më poshtë. Një mendje është e mirë, por dy çizme janë një palë!

• Zëvendësimi dhe pastrimi i tekstit me funksionin SUBSTITUTE
• Search and highlighting of Latin characters in text
• Kërkoni tekstin më të afërt të ngjashëm (Ivanov = Ivonov = Ivanof, etj.)