Rondedzera zvinyorwa nezvirevo zvenguva dzose (RegExp) muExcel

Imwe yeanonyanya kupedza nguva uye anoshungurudza mabasa kana uchishanda nemavara muExcel ndeye parsing - kuparadzanisa alphanumeric "porridge" muzvikamu uye kubvisa zvidimbu zvatinoda kubva mairi. Semuyenzaniso:

• kubvisa zip code kubva kukero (zvakanaka kana zip code inogara iri pakutanga, asi ko kana isiri?)
• kuwana nhamba uye zuva reinvoice kubva pane tsananguro yekubhadhara mubhangi statement
• kubviswa kweTIN kubva mumotley tsananguro dzemakambani mune runyorwa rweanopikisa
• tsvaga nhamba yemotokari kana nhamba yechinyorwa mune tsananguro, nezvimwe.
  

Kazhinji mumamiriro ezvinhu akadaro, mushure mehafu yeawa yekutora dreary muzvinyorwa, pfungwa dzinotanga kuuya mupfungwa neimwe nzira kugadzirisa iyi nzira (kunyanya kana paine data rakawanda). Kune akati wandei magadziriso uye ane akasiyana dhigirii ekuomarara-kushanda zvakanaka:

• shandisa yakavakirwa-mukati Excel mavara mabasa kutsvaga-chekwa-nama mavara: LEVSIMV (RUBOSHWE), RUDYI (RUDYI), PSTR (pakati), STsEPIT (CONCATENATE) uye analogues ayo, COMBINE (JOINTEXT), BASA ( CHAIZVO) etc. Iyi nzira yakanaka kana pane pfungwa yakajeka mumashoko (somuenzaniso, index inogara iri pakutanga kwekero). Zvikasadaro, iwo mafomula anove akaomarara uye, dzimwe nguva, anotouya kune array mafomula, ayo anononoka zvakanyanya pasi pamatafura makuru.
• kushandisa semushandisi wemavara akafanana kubva kuVisual Basic yakaputirwa mune yakasarudzika macro basa. Izvi zvinokutendera kuti ushandise kutsvaga kunoshanduka-shanduka uchishandisa wildcard characters (*, #,?, etc.) Sezvineiwo, chishandiso ichi hachikwanisi kubvisa substring yaunoda kubva muzvinyorwa - tarisa chete kana irimo.

Pamusoro pezviri pamusoro apa, pane imwe nzira inonyanyozivikanwa mumakwikwi akamanikana evagadziri vepurogiramu, vanogadzira webhu uye mamwe matekinoroji - izvi ndizvo. kutaura kwese (Regular Expressions = RegExp = "regexps" = "regulars"). Kungotaura, RegExp mutauro unoshandiswa mavara akakosha uye mirau kutsvaga anodiwa substrings muzvinyorwa, kuabvisa kana kutsiva iwo nemamwe mavara.. Matauriro enguva dzose chishandiso chine simba kwazvo uye chakanaka chinodarika dzimwe nzira dzese dzekushanda nemavara nehurongwa hwehukuru. Mitauro yakawanda yekuronga (C#, PHP, Perl, JavaScript…) uye zvinyorwa zvemanyorero (Izwi, Notepad++…) zvinotsigira zvinogara zvichitaurwa.

Microsoft Excel zvinosuwisa kuti haina RegExp rutsigiro kunze kwebhokisi, asi izvi zvinogona kugadziriswa nyore neVBA. Vhura iyo Visual Basic Mharidzo kubva pane tab yokuvaka (Mugadziri) kana nzira yekudimbudzira keyboard alt+F11. Wobva waisa iyo module nyowani kuburikidza nemenyu Pinza – Module uye kopira zvinyorwa zveinotevera macro basa ipapo:

Public Function RegExpExtract(Mashoko SeTring, Pateni SeTring, Optional Item As Integer = 1) Se String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = Ichokwadi Kana regex.Test (Mashoko) Wobva waSeta machisi = regex.Execute(Text) RegExpExtract = match.Item(Item - 1) Buda Basa Rinopera Kana ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Isu tinogona ikozvino kuvhara iyo Visual Basic Mharidzo todzokera kuExcel kuti tiedze chimiro chedu chitsva. Syntax yaro ndeiyi inotevera:

=RegExpExtract(Txt; Pattern; Item)

apo

• txt - sero rine zvinyorwa zvatiri kutarisa uye kubva kwatinoda kubvisa substring yatinoda
• pattern - mask (pattern) yekutsvaga substring
• chinhu - iyo nhamba yekutevedzana kweiyo substring kuti ibudiswe, kana paine akati wandei (kana isina kutaurwa, ipapo yekutanga kuitika inoratidzwa)

Chinhu chinonyanya kufadza pano, hongu, iPateni - template tambo yeakakosha mavara "mumutauro" weRegExp, iyo inotsanangura chii chaizvo uye kwatinoda kuwana. Heano akanyanya kukosha kuti utange:

Kana isu tichitsvaga imwe nhamba yemavara, semuenzaniso, nhanhatu-manhamba yepositi kana ese matatu-mavara echigadzirwa makodhi, saka tinouya kuzonunura. zvigadziri or zvigadziri ndiwo mataurirwo akakosha anotsanangura huwandu hwemavara achatsvakwa. Quantifiers inoshandiswa kune hunhu hunouya pamberi payo:

Zvino ngatifambei kune chikamu chinonyanya kunakidza - ongororo yekushandiswa kweiyo yakagadzirwa basa uye zvatakadzidza nezve mapatani pamienzaniso inoshanda kubva muhupenyu.

Kutora nhamba kubva mumashoko

Kutanga, ngationgororei nyaya yakapfava - iwe unofanirwa kubvisa nhamba yekutanga kubva kune alphanumeric porridge, semuenzaniso, simba rezvisingapindike magetsi emagetsi kubva pamutengo runyorwa:

Zvinonzwisisika zviri kuseri kwekutaura kwenguva dzose zviri nyore: d zvinoreva chero digit, uye quantifier + inoti nhamba yavo inofanira kuva imwe kana kupfuura. Iyo minus kaviri pamberi pebasa inodiwa kuti "pane nhunzi" shandura mavara akabviswa kuita nhamba yakazara kubva kunhamba-se-zvinyorwa.

Postcode

Pakutanga kuona, zvese zviri nyore pano - isu tiri kutsvaga chaizvo manhamba matanhatu mumutsara. Isu tinoshandisa chimiro chakakosha d yedhijiti uye quantifier 6, XNUMX, XNUMX {} yehuwandu hwemavara:

Zvisinei, mamiriro ezvinhu anogoneka apo, kuruboshwe rweindekisi mumutsara, kune imwe nhamba huru yenhamba mumutsara (nhamba yefoni, TIN, account yebhangi, nezvimwewo) Zvadaro mwaka wedu wenguva dzose uchabudisa 6 yekutanga XNUMX. manhamba kubva pairi, kureva kuti haishande nemazvo:

Kudzivirira izvi kuti zvisaitike, isu tinofanirwa kuwedzera gadziriso pamipendero yekutaura kwedu kwenguva dzose b zvichireva kupera kwezwi. Izvi zvichajekesa kuExcel kuti chidimbu (index) chatinoda chinofanira kunge chiri izwi rakasiyana, uye kwete chikamu chechimwe chidimbu (nhamba yefoni):

Phone

Dambudziko rekutsvaga nhamba yefoni mumagwaro nderokuti pane zvakawanda zvingasarudzwa zvekunyora nhamba - ine uye isina hyphens, kuburikidza nenzvimbo, ine kana isina kodhi yedunhu mumabhuraketi, nezvimwewo. Naizvozvo, mukuona kwangu, zviri nyore tanga wabvisa mavara ese aya kubva kune chinyorwa uchishandisa akati wandei nested mabasa SUBSTITUTE (SUBSTITUTE)kuitira kuti ibatane pamwe chete kuita imwechete, uyezve neyechinyakare yenguva dzose d{11} buritsa manhamba gumi nerimwe mumutsara:

ITN

Zvakati wedzerei pano, nekuti TIN (Munyika Yedu) inogona kuita gumi-manhamba (yemasangano epamutemo) kana gumi nemaviri (kuvanhu). Kana iwe usingawani kukanganisa kunyanya, saka zvinokwanisika kugutsikana neyakajairwa d{10,12}, asi, kutaura chaiko, ichaburitsa manhamba ese kubva pagumi kusvika pamavara gumi nemaviri, kureva kuti uye yakapinda zvisizvo gumi nerimwe. Zvingave zvakanyanya kunaka kushandisa mapatani maviri akabatanidzwa neane musoro OR opareta | (vertical bar):

Tapota cherechedzai kuti mumubvunzo tinotanga tatsvaga nhamba dzegumi nembiri, tozongozotsvaga nhamba dzegumi-bit. Kana tikanyora kutaura kwedu kwenguva dzose neimwe nzira kutenderedza, zvino inoburitsa kune wese munhu, kunyangwe akareba gumi nemaviri-bit TINs, ekutanga mavara gumi chete. Kureva kuti, mushure mekunge mamiriro ekutanga atanga, kumwe kuongorora hakuchaitwe:

Uyu ndiwo musiyano mukuru pakati pemushandisi | kubva pane yakajairwa excel logic function OR (KANA), uko kurongazve nharo hakuchinji mhedzisiro.

Chigadzirwa SKUs

Mumakambani akawanda, zviziviso zvakasiyana-siyana zvakagoverwa kune zvinhu nemabasa - zvinyorwa, SAP codes, SKUs, nezvimwewo. Kana pane logic muzvinyorwa zvavo, zvino vanogona kubudiswa nyore nyore kubva pane chero zvinyorwa vachishandisa nguva dzose kutaura. Semuenzaniso, kana tichiziva kuti zvinyorwa zvedu zvinogara zvine mavara matatu makuru echiRungu, hyphen uye inotevera nhamba ine manhamba matatu, zvino:

Iyo logic kuseri kwetemplate iri nyore. [AZ] - zvinoreva chero mavara makuru echiLatin alphabet. Iyo inotevera quantifier 3, XNUMX, XNUMX {} inoti zvakakosha kwatiri kuti kune mavara matatu akadaro. Mushure meiyo hyphen, isu takamirira manhamba matatu, saka tinowedzera kumagumo d{3}

Mari yemari

Nenzira yakafanana kune yakapfuura ndima, iwe unogona zvakare kuburitsa mitengo (mitengo, VAT ...) kubva pakutsanangurwa kwezvinhu. Kana huwandu hwemari, semuenzaniso, hunoratidzwa nehyphen, zvino:

muenzaniso d pamwe quantifier + inotsvaga chero nhamba kusvika pahfeni, uye d{2} achatsvaga pennies (manhamba maviri) mushure.

Kana iwe uchida kuburitsa kwete mitengo, asi VAT, saka unogona kushandisa yechitatu sarudzo sarudzo yebasa redu RegExpExtract, iyo inotsanangura nhamba yeordinal yechinhu chinobviswa. Uye, chokwadi, unogona kutsiva basa SUBSTITUTE (SUBSTITUTE) mune zvabuda, hyphen kune yakajairwa decimal separator uye wedzera kaviri minus pakutanga kuitira kuti Excel kududzira yakawanikwa VAT senhamba yakajairika:

Nhamba dzeplate yemota

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:

nguva

Kuti ubvise nguva muHH: MM fomati, iyo inotevera yenguva dzese kutaura kwakakodzera:

Mushure me colon fragment [0-5]d, sezvazviri nyore kufunga, inoisa chero nhamba muhuwandu 00-59. Pamberi pekoroni mumaparentheses, mapatani maviri anoshanda, akaparadzaniswa neane musoro OR (pombi):

• [0-1]d - chero nhamba muhuwandu 00-19
• 2[0-3] - chero nhamba muhuwandu 20-23

Kune mhedzisiro yakawanikwa, iwe unogona kuwedzera kushandisa yakajairwa Excel basa NGUVA (TEAM)kuishandura kuita nguva yefomati inonzwisisika kuchirongwa uye yakakodzera mamwe maverengero.

Password check

Ngatitii tinoda kutarisa rondedzero yemapassword akagadzirwa nevashandisi kuti ndeapi. Zvinoenderana nemitemo yedu, mapassword anogona kungova nemavara echiRungu (madiki kana makuru) nenhamba. Nzvimbo, underscores nemamwe mamarks epunctuation hazvibvumidzwe.

Kutarisa kunogona kurongeka uchishandisa inotevera yakapusa kutaura:

Muchokwadi, nemaitiro akadaro tinoda kuti pakati pekutanga (^) uye kupera ($) muchinyorwa chedu maingove nemabhii kubva pane zvakapihwa mumabhuraketi akaenzana. Kana iwe zvakare uchida kutarisa kureba kwepassword (semuenzaniso, angangoita 6 mavara), ipapo iyo quantifier + inogona kutsiviwa nepakati "nhanhatu kana kupfuura" mufomu {6,}:

Guta kubva kukero

Ngatitii tinoda kudhonza guta kubva kubhawa rekero. Chirongwa chenguva dzose chichabatsira, kubvisa zvinyorwa kubva ku "g." kune comma inotevera:

Ngatitarisei patani iyi.

Kana wakaverenga chinyorwa chiri pamusoro, saka iwe watonzwisisa kuti mamwe mavara mumashoko enguva dzose (nguva, asterisks, zviratidzo zvemadhora, nezvimwewo) ane zvaanoreva. Kana iwe uchida kutsvaga aya mavara ivo pachavo, ivo vanotangirwa nebackslash (dzimwe nguva inonzi kudzivirirwa) Naizvozvo, kana uchitsvaga chidimbu "g." tinofanira kunyora mukutaura nguva dzose Mr. kana tichitsvaga kuwedzera, saka + etc.

Mavara maviri anotevera mutemplate yedu, dot uye quantifier asterisk, inomiririra chero nhamba yechero mavara, kureva chero zita reguta.

Kune koma kumagumo kwetemplate, nekuti isu tiri kutsvaga mavara kubva ku "g." kune koma. Asi panogona kuve nemakoma akati wandei muzvinyorwa, handiti? Kwete chete mushure meguta, asiwo mushure memugwagwa, dzimba, nezvimwewo. Ndeipi yavo ichamira chikumbiro chedu? Ndiko kunoitwa nemubvunzo. Pasina iyo, kutaura kwedu kwenguva dzose kwaizoburitsa tambo refu inobvira:

Mukutaura kwekutaura nguva dzose, muenzaniso wakadaro "unokara". Kugadzirisa mamiriro acho ezvinhu, mucherechedzo wemubvunzo unodiwa - inoita kuti chiyereso ichizomira "chinonyima" - uye mubvunzo wedu unotora zvinyorwa kusvika pacomma yekutanga mushure me "g.":

Filename kubva munzira yakazara

Imwe mamiriro akajairika ndeyekubvisa zita refaira kubva munzira yakazara. Chirevo chenguva dzose kutaura kwefomu kuchabatsira pano:

Chinonyengera apa ndechekuti kutsvaga, kutaura zvazviri, kunoitika kwakatarisana - kubva kumagumo kusvika kumavambo, nekuti pakupera kwetemplate yedu. $, uye isu tiri kutsvaga zvese zvisati zvaitika kusvika kune yekutanga backslash kubva kurudyi. Iyo backslash inopukunyuka, senge doti riri mumuenzaniso wapfuura.

PS

"Kusvika kumagumo" ndinoda kujekesa kuti zvese zviri pamusoro apa chikamu chidiki chezvose zvinogoneka zvinopihwa nekutaura nguva dzose. Kune akawanda akakosha mavara uye mitemo yekushandiswa kwavo, uye mabhuku ese akanyorwa pane iyi dingindira (ini ndinokurudzira ingangoita iyi yekutanga). Neimwe nzira, kunyora matauriro enguva dzose hunenge hunyanzvi. Kanenge nguva dzose, yakarongwa yekutaura yenguva dzose inogona kuvandudzwa kana kuwedzerwa, zvichiita kuti iwedzere kunaka kana kukwanisa kushanda nehuwandu hwakawanda hwe data yekuisa.

Kuongorora uye kuenzanisa zvinogara zvichitaurwa nevamwe vanhu kana kugadzirisa yako, pane akati wandei ari nyore masevhisi epamhepo: RegEx101, RegExr uye zvakawanda

Nehurombo, haasi ese maficha echinyakare mataurirwo ekutaura anotsigirwa muVBA (semuenzaniso, reverse kutsvaga kana POSIX makirasi) uye anogona kushanda neCyrillic, asi ini ndinofunga kuti zviripo zvinokwana kekutanga kukufadza iwe.

Kana usiri mutsva kumusoro wenyaya, uye uine chekugovana, siya zvenguva dzose mataurirwo anobatsira paunenge uchishanda muExcel mune zvakataurwa pazasi. Imwe pfungwa yakanaka, asi mabhutsu maviri ari peya!

• Kutsiva uye kuchenesa zvinyorwa neiyo SUBSTITUTE basa
• Search and highlighting of Latin characters in text
• Tsvaga mavara ari pedyo akafanana (Ivanov = Ivonov = Ivanof, nezvimwewo)