Išanalizuoti tekstą su reguliariosiomis išraiškomis (RegExp) programoje „Excel“.

Viena iš daugiausiai laiko užimančių ir varginančių užduočių dirbant su tekstu „Excel“ yra analizuojant – raidinės ir skaitmeninės „košės“ išskaidymas į komponentus ir iš jos mums reikalingų fragmentų ištraukimas. Pavyzdžiui:

• Pašto kodo ištraukimas iš adreso (gerai, jei pašto kodas visada yra pradžioje, o jei ne?)
• sąskaitos numerio ir datos radimas iš mokėjimo aprašymo banko išraše
• TIN ištraukimas iš margų įmonių aprašymų sandorio šalių sąraše
• aprašyme ieškokite automobilio numerio ar prekės numerio ir pan.
  

Dažniausiai tokiais atvejais, po pusvalandžio niūraus teksto rinkimo rankiniu būdu, ima kilti mintys kaip nors automatizuoti šį procesą (ypač jei duomenų daug). Yra keletas sprendimų, kurių sudėtingumas ir efektyvumas skiriasi:

• naudojimas įmontuotos Excel teksto funkcijos norėdami ieškoti – iškirpti – klijuoti tekstą: LEVSIMV (Kairė), TEISĖ (Teisingai), PSTR (viduryje), STsEPIT (SUJUNGTI) ir jo analogai, KOMBINUOKITE (JOINTEXT), TIKSLAS (TIKSLAI) tt Šis metodas yra geras, jei tekste yra aiški logika (pavyzdžiui, indeksas visada yra adreso pradžioje). Priešingu atveju formulės tampa daug sudėtingesnės, o kartais net atsiranda masyvo formulės, o tai labai sulėtėja didelėse lentelėse.
• Naudojant kaip teksto panašumo operatorius iš „Visual Basic“, įtrauktas į pasirinktinę makrokomandos funkciją. Tai leidžia įgyvendinti lankstesnę paiešką naudojant pakaitos simbolius (*, #,? ir tt). Deja, šis įrankis negali išgauti norimos poeilutės iš teksto – tik patikrinkite, ar ji jame yra.

Be to, kas išdėstyta aukščiau, yra dar vienas požiūris, kuris yra labai gerai žinomas siauruose profesionalių programuotojų, interneto kūrėjų ir kitų technikų ratuose – tai yra reguliarūs posakiai (Reguliarūs reiškiniai = RegExp = "reguliarieji reiškiniai" = "reguliarūs"). Paprasčiau pasakius, RegExp yra kalba, kurioje specialieji simboliai ir taisyklės naudojami ieškant reikalingų poeilučių tekste, jas išskleisti arba pakeisti kitu tekstu.. Reguliarūs posakiai yra labai galingas ir gražus įrankis, savo dydžiu pranokstantis visus kitus darbo su tekstu būdus. Daugelis programavimo kalbų (C#, PHP, Perl, JavaScript…) ir teksto rengyklės (Word, Notepad++…) palaiko reguliariąsias išraiškas.

Deja, „Microsoft Excel“ neturi „RegExp“ palaikymo, tačiau tai galima lengvai išspręsti naudojant VBA. Skirtuke atidarykite „Visual Basic“ rengyklę ryškalas (Programuotojas) arba spartusis klavišas Kitas+F11. Tada įdėkite naują modulį per meniu Įterpimas – modulis ir nukopijuokite ten šios makrokomandos funkcijos tekstą:

Viešoji funkcija RegExpExtract(tekstas kaip eilutė, šablonas kaip eilutė, pasirenkamas elementas kaip sveikasis skaičius = 1) kaip eilutė, kai įvyko klaida GoTo ErrHandl Nustatyti regex = CreateObject("VBScript.RegExp") regex.Pattern = šablonas regex.Global = teisingas, jei regex.T (Tekstas) Tada nustatykite atitikmenis = regex.Execute(tekstas) RegExpExtract = atitikmenys.Elementas (elementas - 1) Išeiti iš funkcijos pabaigos, jei ErrHandl: RegExpExtract = CVERr(xlErrValue) Pabaigos funkciją  

Dabar galime uždaryti „Visual Basic“ rengyklę ir grįžti į „Excel“, kad išbandytume naują funkciją. Jo sintaksė yra tokia:

=RegExpExtract(Txt; Šablonas; Elementas)

kur

• txt – langelis su tekstu, kurį tikriname ir iš kurio norime išgauti mums reikalingą eilutę
• modelis – kaukė (šablonas) poeilutės paieškai
• Punktas – ištraukiamos poeilės eilės numeris, jei jų yra keletas (jei nenurodyta, rodomas pirmasis įvykis)

Įdomiausias dalykas čia, be abejo, yra Pattern – šabloninė specialiųjų simbolių eilutė RegExp „kalba“, nurodanti, ką tiksliai ir kur norime rasti. Štai patys paprasčiausi, kad galėtumėte pradėti:

Jei ieškome tam tikro skaičiaus simbolių, pavyzdžiui, šešių skaitmenų pašto kodo arba visų trijų raidžių gaminių kodų, tada ateiname į pagalbą kiekybiniai rodikliai or kiekybiniai rodikliai yra specialios išraiškos, nurodančios ieškomų simbolių skaičių. Prieš jį esančiam simboliui taikomi kvantoriai:

Dabar pereikime prie įdomiausios dalies – sukurtos funkcijos taikymo analizės ir to, ką sužinojome apie modelius pagal praktinius gyvenimo pavyzdžius.

Skaičių ištraukimas iš teksto

Pirmiausia paanalizuokime paprastą atvejį – iš raidinės ir skaitmeninės košės reikia ištraukti pirmąjį skaičių, pavyzdžiui, nepertraukiamo maitinimo šaltinių galią iš kainoraščio:

Reguliariosios išraiškos logika yra paprasta: d reiškia bet kurį skaitmenį ir kvantorių + sako, kad jų skaičius turėtų būti vienas ar daugiau. Dvigubas minusas prieš funkciją reikalingas norint „skraidydami“ konvertuoti ištrauktus simbolius į pilną skaičių iš skaičiaus kaip teksto.

pašto indeksas

Iš pirmo žvilgsnio čia viskas paprasta – ieškome lygiai šešių skaitmenų iš eilės. Mes naudojame specialų simbolį d skaitmeniui ir kvantoriui 6 {} simbolių skaičiui:

Tačiau galima situacija, kai kairėje nuo indekso eilutėje yra dar vienas didelis skaičių rinkinys iš eilės (telefono numeris, TIN, banko sąskaita ir kt.) Tada mūsų reguliarusis sezonas ištrauks pirmuosius 6 skaitmenys iš jo, ty neveiks tinkamai:

Kad taip nenutiktų, turime pridėti modifikatorių aplink mūsų reguliariosios išraiškos kraštus b reiškiantis žodžio pabaigą. Taip „Excel“ bus aišku, kad mums reikalingas fragmentas (rodyklė) turi būti atskiras žodis, o ne kito fragmento (telefono numerio) dalis:

Mobilus telefonas

Problema ieškant tekste telefono numerio yra ta, kad yra tiek daug galimybių rašyti skaičius – su brūkšneliais ir be jų, tarpais, su regiono kodu arba be jo skliausteliuose ir pan. Todėl, mano nuomone, lengviau pirmiausia išvalykite visus šiuos simbolius iš šaltinio teksto naudodami kelias įdėtas funkcijas PAKEITIMAS (PAKEIT.)kad suliptų į vientisą visumą, o paskui su primityviu taisyklingu d{11} ištraukite 11 skaitmenų iš eilės:

ITN

Čia yra šiek tiek sudėtingiau, nes TIN (mūsų šalyje) gali būti 10 skaitmenų (juridiniams asmenims) arba 12 skaitmenų (fiziniams asmenims). Jei kaltės ypač nerandate, tuomet visai įmanoma pasitenkinti įprastais d{10,12}, bet, griežtai tariant, jis ištrauks visus skaičius nuo 10 iki 12 simbolių, ty ir klaidingai įvedė 11 skaitmenų. Teisingiau būtų naudoti du šablonus, sujungtus loginiu ARBA operatoriumi | (vertikali juosta):

Atkreipkite dėmesį, kad užklausoje pirmiausia ieškome 12 bitų skaičių, o tik tada 10 bitų skaičių. Jei savo reguliarųjį posakį parašysime atvirkščiai, jis ištrauks visiems, net ilgiems 12 bitų TIN, tik pirmuosius 10 simbolių. Tai yra, suaktyvinus pirmą sąlygą, tolesnis patikrinimas nebeatliekamas:

Tai esminis skirtumas tarp operatoriaus | iš standartinės Excel logikos funkcijos OR (ARBA), kur argumentų pertvarkymas nekeičia rezultato.

Produkto SKU

Daugelyje įmonių unikalūs identifikatoriai priskiriami prekėms ir paslaugoms – straipsniams, SAP kodams, SKU ir tt Jei jų žymėjime yra logikos, tuomet juos galima lengvai ištraukti iš bet kokio teksto naudojant reguliariąsias išraiškas. Pavyzdžiui, jei žinome, kad mūsų straipsnius visada sudaro trys didžiosios angliškos raidės, brūkšnelis ir vėlesnis trijų skaitmenų skaičius, tada:

Šablono logika yra paprasta. [AZ] – reiškia bet kokias didžiąsias lotyniškos abėcėlės raides. Kitas kvantorius 3 {} sako, kad mums svarbu, kad tokių raidžių būtų lygiai trys. Po brūkšnelio laukiame trijų skaitmenų, todėl pridedame pabaigoje d{3}

Grynųjų pinigų sumos

Panašiai kaip ir ankstesnėje pastraipoje, iš prekių aprašymo galite ištraukti kainas (išlaidas, PVM...). Jei, pavyzdžiui, piniginės sumos nurodomos brūkšneliu, tada:

Raštai d su kvantoriumi + ieško bet kokio skaičiaus iki brūkšnelio ir d{2} ieškos centų (dviejų skaitmenų) po to.

Jei reikia išgauti ne kainas, o PVM, tuomet galite naudoti trečiąjį pasirenkamą mūsų RegExpExtract funkcijos argumentą, kuris nurodo ištraukiamo elemento eilės numerį. Ir, žinoma, galite pakeisti funkciją PAKEITIMAS (PAKEIT.) rezultatuose brūkšneliu iki standartinio dešimtainio skyriklio ir pradžioje pridėkite dvigubą minusą, kad „Excel“ rastą PVM interpretuotų kaip įprastą skaičių:

Automobilio valstybiniai numeriai

Jei nevežate specialių transporto priemonių, priekabų ir kitų motociklų, tada standartinis automobilio numeris analizuojamas pagal principą „raidė – trys skaičiai – dvi raidės – regiono kodas“. Be to, regiono kodas gali būti 2 arba 3 skaitmenų, o kaip raidės naudojamos tik tos, kurios savo išvaizda yra panašios į lotynišką abėcėlę. Taigi ši reguliarioji išraiška padės mums išgauti skaičius iš teksto:

Laikas

Norint išgauti laiką formatu HH:MM, tinka ši reguliarioji išraiška:

Po dvitaškio fragmento [0-5]d, kaip nesunku išsiaiškinti, nustato bet kokį skaičių diapazone nuo 00 iki 59. Prieš dvitaškį skliausteliuose veikia du šablonai, atskirti loginiu ARBA (vamzdžiu):

• [0-1]d – bet koks skaičius nuo 00 iki 19
• 2[0-3] – bet koks skaičius nuo 20 iki 23

Gautam rezultatui galite papildomai pritaikyti standartinę Excel funkciją LAIKAS (KOMANDA)konvertuoti jį į programai suprantamą ir tolimesniems skaičiavimams tinkamą laiko formatą.

Slaptažodžio patikrinimas

Tarkime, kad turime patikrinti vartotojų sugalvotų slaptažodžių sąrašo teisingumą. Pagal mūsų taisykles slaptažodžius gali sudaryti tik angliškos raidės (mažosios arba didžiosios) ir skaičiai. Tarpai, apatiniai brūkšniai ir kiti skyrybos ženklai neleidžiami.

Tikrinimas gali būti organizuojamas naudojant šią paprastą reguliariąją išraišką:

Tiesą sakant, naudojant tokį modelį, mes reikalaujame, kad tarp pradžios (^) ir pabaiga ($) mūsų tekste buvo tik simboliai iš rinkinio, pateikto laužtiniuose skliaustuose. Jei taip pat reikia patikrinti slaptažodžio ilgį (pavyzdžiui, bent 6 simboliai), tada kvantorius + formoje gali būti pakeistas intervalu „šeši ar daugiau“. {6,}:

Miestas iš adreso

Tarkime, kad turime ištraukti miestą iš adreso juostos. Įprasta programa padės išgauti tekstą iš „g“. prie kito kablelio:

Pažvelkime į šį modelį atidžiau.

Jei perskaitėte aukščiau esantį tekstą, tuomet jau supratote, kad kai kurie įprastų posakių simboliai (taškai, žvaigždutės, dolerio ženklai ir kt.) turi ypatingą reikšmę. Jei reikia ieškoti pačių šių simbolių, prieš juos rašomas pasvirasis brūkšnys (kartais vadinamas ekranavimas). Todėl ieškant fragmento „g“. turime rašyti reguliariąja išraiška Ponas. jei mes ieškome pliuso, tada + ir taip toliau

Kiti du simboliai mūsų šablone, taškas ir kvantoriaus žvaigždutė, reiškia bet kokį skaičių simbolių, ty bet kurį miesto pavadinimą.

Šablono pabaigoje yra kablelis, nes ieškome teksto iš „g“. į kablelį. Bet tekste gali būti keli kableliai, tiesa? Ne tik po miestą, bet ir po gatvę, namus ir pan. Kuriame iš jų sustos mūsų prašymas? Tam ir skirtas klaustukas. Be jo mūsų reguliarioji išraiška ištrauktų ilgiausią įmanomą eilutę:

Kalbant apie reguliarius posakius, toks modelis yra „godus“. Norint ištaisyti situaciją, reikia klaustuko – jis paverčia kiekybinį rodiklį, po kurio jis yra „šykštus“, o mūsų užklausoje tekstas paimamas tik iki pirmojo priešinio kablelio po „g“.

Failo pavadinimas iš viso kelio

Kita labai paplitusi situacija yra failo pavadinimo ištraukimas iš viso kelio. Čia padės paprasta reguliari formos išraiška:

Apgaulė čia ta, kad paieška iš tikrųjų vyksta priešinga kryptimi – nuo ​​pabaigos iki pradžios, nes mūsų šablono pabaigoje yra $, ir mes ieškome visko prieš tai iki pirmojo pasvirojo brūkšnio iš dešinės. Pasvirasis brūkšnys pašalinamas, kaip ir taškas ankstesniame pavyzdyje.

PS

„Pabaigoje“ Noriu paaiškinti, kad visa tai, kas išdėstyta aukščiau, yra maža dalis visų reguliarių posakių teikiamų galimybių. Yra daug specialių simbolių ir jų naudojimo taisyklių, šia tema yra parašytos ištisos knygos (pradžiai rekomenduoju bent šią). Tam tikra prasme reguliarių posakių rašymas yra beveik menas. Beveik visada sugalvotą reguliariąją išraišką galima patobulinti arba papildyti, kad ji būtų elegantiškesnė arba gali dirbti su įvairesniais įvesties duomenimis.

Norėdami analizuoti ir analizuoti kitų žmonių reguliariąsias išraiškas arba derinti savo, yra keletas patogių internetinių paslaugų: RegEx101, RegExr ir dar

Deja, ne visos klasikinių reguliariųjų posakių funkcijos yra palaikomos VBA (pavyzdžiui, atvirkštinė paieška ar POSIX klasės) ir gali veikti su kirilica, bet manau, kad to, kas yra, užtenka pirmą kartą, kad patiktų.

Jei nesate šios temos naujokas ir turite kuo pasidalinti, toliau pateiktuose komentaruose palikite įprastus posakius, naudingus dirbant su Excel. Vienas protas yra geras, bet du batai yra pora!

• Teksto keitimas ir išvalymas naudojant funkciją SUBSTITUTE
• Lotyniškų rašmenų paieška ir paryškinimas tekste
• Ieškokite artimiausio panašaus teksto (Ivanov = Ivonov = Ivanof ir kt.)