Parsi teksti regulaaravaldistega (RegExp) Excelis

Üks aeganõudvamaid ja masendavamaid ülesandeid Excelis tekstiga töötamisel on sõelumine – tähtnumbrilise “pudru” komponentideks sõelumine ja meile vajalike fragmentide eraldamine sellest. Näiteks:

• sihtnumbri väljavõtmine aadressist (see on hea, kui sihtnumber on alati alguses, aga mis siis, kui see pole nii?)
• arve numbri ja kuupäeva leidmine pangaväljavõttelt makse kirjeldusest
• TIN väljavõte osapoolte nimekirjas olevate ettevõtete kirjudest kirjeldustest
• otsi kirjeldusest auto number või artikli number jne.
  

Tavaliselt hakkavad sellistel puhkudel pärast pooletunnist süngelist käsitsi tekstis nokitsemist pähe tulema mõtted seda protsessi kuidagi automatiseerida (eriti kui andmeid on palju). Lahendusi on mitu ja erineva keerukuse ja tõhususega:

• Kasutama sisseehitatud Exceli tekstifunktsioonid teksti otsimiseks-lõikamiseks-liimimiseks: LEVSIMV (VASAK), PAREM (PAREM), PSTR (keskel), STsEPIT (CONCATENATE) ja selle analoogid, COMBINE (JOINTEXT), TÄIELIK (TÄPNE) jne. See meetod on hea, kui tekstis on selge loogika (näiteks indeks on alati aadressi alguses). Vastasel juhul muutuvad valemid palju keerulisemaks ja mõnikord jõuavad isegi massiivivalemid, mis aeglustab suurtes tabelites oluliselt.
• Kasutamine nagu teksti sarnasuse operaator Visual Basicult, mis on ümbritsetud kohandatud makrofunktsiooniga. See võimaldab rakendada paindlikumat otsingut metamärkide (*, #,? jne) abil. Kahjuks ei saa see tööriist tekstist soovitud alamstringi välja võtta – kontrollige vaid, kas see on selles.

Lisaks eelmainitule on veel üks lähenemine, mis on professionaalsete programmeerijate, veebiarendajate ja teiste tehnikute kitsastes ringkondades väga tuntud – see on regulaarsed väljendid (Regulaaravaldised = RegExp = "regulaaravaldised" = "regulaarsed"). Lihtsamalt öeldes, RegExp on keel, kus erimärke ja reegleid kasutatakse tekstist vajalike alamstringide otsimiseks, nende väljavõtmiseks või muu tekstiga asendamiseks. Regulaaravaldised on väga võimas ja ilus tööriist, mis ületab suurusjärgu võrra kõik muud tekstiga töötamise viisid. Paljud programmeerimiskeeled (C#, PHP, Perl, JavaScript…) ja tekstiredaktorid (Word, Notepad++…) toetavad regulaaravaldisi.

Kahjuks pole Microsoft Excelil RegExpi tuge, kuid seda saab VBA abil hõlpsasti parandada. Avage vahekaardil Visual Basicu redaktor arendaja (Arendaja) või klaviatuuri otsetee muu+F11. Seejärel sisestage menüü kaudu uus moodul Sisesta – moodul ja kopeerige sinna järgmise makrofunktsiooni tekst:

Avalik funktsioon RegExpExtract(tekst stringina, muster stringina, valikuline üksus täisarvuna = 1) stringina vea korral GoTo ErrHandl Määra regex = CreateObject("VBScript.RegExp") regex.Pattern = muster regex.Global = Tõene, kui regex.T (Tekst) Seejärel määra vasted = regex.Execute(Text) RegExpExtract = vasted.Üksus(Üksus - 1) Välju funktsioonist End If ErrHandl: RegExpExtract = CVERr(xlErrValue) Lõppfunktsioon  

Nüüd saame sulgeda Visual Basicu redaktori ja naasta Excelisse, et meie uut funktsiooni proovida. Selle süntaks on järgmine:

=RegExpExtract(Txt; Muster; Üksus)

kus

• txt – lahter tekstiga, mida me kontrollime ja millest tahame vajaliku alamstringi eraldada
• muster – mask (muster) alamstringi otsimiseks
• Kirje – ekstraheeritava alamstringi järjekorranumber, kui neid on mitu (kui pole määratud, siis kuvatakse esimene esinemine)

Kõige huvitavam on siin muidugi Pattern – RegExpi “keeles” erimärkidest koosnev mallijada, mis määrab, mida täpselt ja kust leida tahame. Siin on alustamiseks kõige elementaarsemad.

Kui otsime teatud arvu märke, näiteks kuuekohalist sihtnumbrit või kõiki kolmetähelisi tootekoode, siis tuleme appi kvantorid or kvantorid on eriavaldised, mis määravad otsitavate märkide arvu. Kvantorid rakendatakse sellele eelnevale märgile:

Liigume nüüd kõige huvitavama osa juurde – loodud funktsiooni rakendamise ja mustrite kohta õpitu analüüs praktilistel elu näidetel.

Numbrite eraldamine tekstist

Alustuseks analüüsime lihtsat juhtumit – tähtnumbrilisest pudrust tuleb välja võtta esimene number, näiteks hinnakirjast katkematute toiteallikate võimsus:

Regulaaravaldise taga olev loogika on lihtne: d tähendab mis tahes numbrit ja kvantorit + ütleb, et nende arv peaks olema üks või mitu. Funktsiooni ees olev topeltmiinus on vajalik selleks, et "lennult" teisendada väljavõetud märgid numbrist tekstina täisarvuks.

Postiindeks

Esmapilgul on siin kõik lihtne – otsime täpselt kuut numbrit järjest. Kasutame erimärki d numbri ja kvantori jaoks 6 {} märkide arvu jaoks:

Võimalik on aga olukord, kus reas olevast indeksist vasakul on reas veel üks suur numbrikomplekt (telefoninumber, TIN, pangakonto jne). Siis tõmbab meie tavahooaeg välja 6 esimest. numbrid sellest, st ei tööta õigesti:

Selle vältimiseks peame lisama oma regulaaravaldise servade ümber modifikaatori b mis tähistab sõna lõppu. See teeb Excelile selgeks, et vajalik fragment (indeks) peaks olema eraldi sõna, mitte osa teisest fragmendist (telefoninumber):

TELEFON

Tekstist telefoninumbri leidmisel on probleem selles, et numbrite kirjutamise võimalusi on väga palju – sidekriipsudega ja ilma, tühikute kaudu, piirkonnakoodiga või ilma sulgudes jne. Seetõttu on minu arvates lihtsam esmalt puhastage kõik need märgid lähtetekstist, kasutades mitmeid pesastatud funktsioone ASENDA (ASENDAJA)nii, et see kleepub kokku ühtseks tervikuks ja seejärel primitiivse regulaarsusega d{11} tõmba välja 11 numbrit järjest:

ITN

Siin on asi veidi keerulisem, sest TIN (meie riigis) võib olla 10-kohaline (juriidilistel isikutel) või 12-kohaline (eraisikutel). Kui eriti vigu ei leia, siis on täiesti võimalik tavalisega rahule jääda d{10,12}, kuid rangelt võttes tõmbab see välja kõik numbrid vahemikus 10 kuni 12 tähemärki, st ekslikult sisestatud 11 numbrit. Õigem oleks kasutada kahte loogilise VÕI-operaatoriga ühendatud mustrit | (vertikaalne riba):

Pange tähele, et päringus otsime esmalt 12-bitiseid numbreid ja alles seejärel 10-bitisi numbreid. Kui kirjutame oma regulaaravaldise teistpidi, tõmbab see kõigi jaoks välja, isegi pikad 12-bitised TIN-id, ainult esimesed 10 tähemärki. See tähendab, et pärast esimese tingimuse käivitamist täiendavat kontrolli enam ei teostata:

See on operaatori põhiline erinevus | standardsest Exceli loogikafunktsioonist OR (VÕI), kus argumentide ümberpaigutamine ei muuda tulemust.

Toote SKU-d

Paljudes ettevõtetes määratakse kaupadele ja teenustele kordumatud identifikaatorid – artiklid, SAP-koodid, SKU-d jne. Kui nende tähistuses on loogikat, saab neid regulaaravaldiste abil hõlpsasti igast tekstist välja tõmmata. Näiteks kui teame, et meie artiklid koosnevad alati kolmest ingliskeelsest suurtähest, sidekriipsust ja sellele järgnevast kolmekohalisest numbrist, siis:

Malli taga olev loogika on lihtne. [AZ] – tähendab kõiki ladina tähestiku suuri tähti. Järgmine kvantor 3 {} ütleb, et meie jaoks on oluline, et selliseid tähti oleks täpselt kolm. Pärast sidekriipsu ootame kolme numbrit, nii et lisame lõpus d{3}

Sularaha summad

Sarnaselt eelmise lõiguga saate kauba kirjeldusest välja tõmmata ka hinnad (kulud, käibemaks ...). Kui näiteks rahasummad on märgitud sidekriipsuga, siis:

Muster d kvantoriga + otsib mis tahes arvu kuni sidekriipsuni ja d{2} otsib pärast sente (kaks numbrit).

Kui teil on vaja ekstraheerida mitte hindu, vaid käibemaksu, võite kasutada meie funktsiooni RegExpExtract kolmandat valikulist argumenti, mis määrab ekstraheeritava elemendi järjekorranumbri. Ja loomulikult saate funktsiooni asendada ASENDA (ASENDAJA) tulemustes sisestage sidekriips tavalise kümnendkoha eraldajani ja lisage algusesse topeltmiinus, et Excel tõlgendaks leitud KM-i tavalise numbrina:

Auto numbrid

Kui te ei võta erisõidukeid, haagiseid ja muid mootorrattaid, sõelutakse standardne auto number põhimõttel "täht - kolm numbrit - kaks tähte - piirkonnakood". Veelgi enam, piirkonnakood võib olla 2- või 3-kohaline ning tähtedena kasutatakse ainult neid, mis on välimuselt sarnased ladina tähestikuga. Seega aitab järgmine regulaaravaldis meil tekstist numbreid eraldada:

aeg

Aja eraldamiseks vormingus HH:MM sobib järgmine regulaaravaldis:

Pärast käärsoole fragment [0-5]d, nagu seda on lihtne aru saada, määrab mis tahes arvu vahemikus 00–59. Enne koolonit sulgudes töötavad kaks mustrit, mis on eraldatud loogilise VÕI (toruga):

• [0-1]d – mis tahes arv vahemikus 00–19
• 2 [0-3] – mis tahes arv vahemikus 20–23

Saadud tulemusele saate lisaks rakendada standardset Exceli funktsiooni AEG (MEESKOND)teisendada see programmile arusaadavasse ajavormingusse, mis sobib edasisteks arvutusteks.

Parooli kontroll

Oletame, et peame kontrollima kasutajate leiutatud paroolide loendi õigsust. Meie reeglite kohaselt võivad paroolid sisaldada ainult ingliskeelseid tähti (väike- või suurtähti) ja numbreid. Tühikud, allkriipsud ja muud kirjavahemärgid ei ole lubatud.

Kontrolli saab korraldada järgmise lihtsa regulaaravaldise abil:

Tegelikult nõuame sellise mustri puhul, et alguses (^) ja lõpp ($) meie tekstis olid ainult märgid nurksulgudes antud komplektist. Kui on vaja kontrollida ka parooli pikkust (näiteks vähemalt 6 tähemärki), siis kvantor + võib vormis asendada intervalliga "kuus või enam". {6,}:

Linn aadressist

Oletame, et peame aadressiribalt linna tõmbama. Abiks on tavaline programm, mis eraldab teksti "g"-st. järgmise koma juurde:

Vaatame seda mustrit lähemalt.

Kui olete ülaltoodud teksti lugenud, siis olete juba aru saanud, et mõnel regulaaravaldise märgil (punktid, tärnid, dollarimärgid jne) on eriline tähendus. Kui peate neid märke ise otsima, siis eelneb neile kaldkriips (mõnikord nimetatakse seda varjestus). Seetõttu otsides fragmendi "g." peame kirjutama regulaaravaldises Härra. kui otsime plussi, siis + ja nii edasi

Meie malli kaks järgmist märki, punkt ja kvantori tärn, tähistavad mis tahes arvu märke, st mis tahes linna nime.

Malli lõpus on koma, kuna otsime teksti tähest „g”. koma juurde. Aga tekstis võib olla mitu koma, eks? Mitte ainult pärast linna, vaid ka pärast tänavat, maju jne. Millel neist meie taotlus peatub? Selleks on küsimärk. Ilma selleta tõmbaks meie regulaaravaldis välja pikima võimaliku stringi:

Regulaaravaldiste osas on selline muster “ahne”. Olukorra parandamiseks on vaja küsimärki – see muudab kvantori, mille järel jääb “ihne” – ja meie päring viib teksti ainult kuni esimese loenduri koma pärast “g”.

Failinimi täisteelt

Teine väga levinud olukord on failinime eraldamine kogu teest. Siin aitab vormi lihtne regulaaravaldis:

Siin on trikk selles, et otsing toimub tegelikult vastupidises suunas – lõpust alguseni, sest meie malli lõpus on $, ja me otsime kõike enne seda kuni esimese paremalt kaldkriipsuni. Kaldkriips on välditud, nagu punkt eelmises näites.

PS

"Lõpu poole" Tahan selgitada, et kõik eelnev on väike osa kõigist regulaaravaldiste pakutavatest võimalustest. Erimärke ja nende kasutamise reegleid on palju ning sellel teemal on kirjutatud terveid raamatuid (soovitan alustuseks vähemalt seda). Mõnes mõttes on regulaaravaldiste kirjutamine peaaegu kunst. Peaaegu alati saab väljamõeldud regulaaravaldist täiustada või täiendada, muutes selle elegantsemaks või suutmaks töötada suurema hulga sisendandmetega.

Teiste regulaaravaldiste analüüsimiseks ja sõelumiseks või enda silumiseks on mitu mugavat võrguteenust. RegEx101, RegExr rohkem

Kahjuks ei toeta VBA kõik klassikaliste regulaaravaldiste funktsioonid (näiteks pöördotsing või POSIX klassid) ja ei saa töötada kirillitsaga, kuid arvan, et see, mis on, on esimeseks korraks piisav, et teile meeldida.

Kui te pole teemas uus ja teil on midagi jagada, jätke allolevatesse kommentaaridesse regulaaravaldised, mis on Excelis töötamisel kasulikud. Üks mõistus on hea, aga kaks saabast on paar!

• Teksti asendamine ja puhastamine funktsiooniga SUBSTITUTE
• Ladina tähtede otsimine ja esiletõstmine tekstis
• Otsige lähimat sarnast teksti (Ivanov = Ivonov = Ivanof jne)