Hlaziya umbhalo ngezinkulumo ezivamile (RegExp) ku-Excel

Omunye wemisebenzi edla isikhathi kakhulu futhi ekhungathekisayo lapho usebenza ngombhalo ku-Excel ngu ukuhlahlela - ukuhlukanisa "iphalishi" le-alphanumeric libe izingxenye futhi likhiphe izingcezu esizidingayo kulo. Ngokwesibonelo:

• ukukhipha ikhodi ye-zip ekhelini (kuhle uma ikhodi ye-zip ihlezi isekuqaleni, kodwa kuthiwani uma ingekho?)
• ukuthola inombolo nosuku lwe-invoyisi encazelweni yenkokhelo esitatimendeni sasebhange
• ukukhishwa kwe-TIN ezincazelweni ze-motley zezinkampani ezisohlwini lwabalingani
• sesha inombolo yemoto noma inombolo yesihloko encazelweni, njll.
  

Ngokuvamile ezimweni ezinjalo, ngemva kwesigamu sehora sokukhetha okudabukisayo embhalweni ngesandla, imicabango iqala ukufika engqondweni ngandlela-thile ukuze kusebenze le nqubo (ikakhulukazi uma kunedatha eningi). Kunezixazululo eziningana futhi ezinamazinga ahlukene obunzima-ukusebenza kahle:

• Sebenzisa imisebenzi yombhalo eyakhelwe ngaphakathi ye-Excel ukucinga umbhalo-cut-glue: I-LEVSIMV (KUSELE), KUPHELA (KWESOKUDLA), PSTR (phakathi), I-STsEPIT (CONCATENATE) kanye nezifaniso zayo, hlanganisa (JOINTEXT), ISEXOLO (NGQO) njll. Le ndlela inhle uma kukhona i-logic ecacile embhalweni (isibonelo, inkomba ihlale isekuqaleni kwekheli). Uma kungenjalo, amafomula aba inkimbinkimbi kakhulu futhi, ngezinye izikhathi, afika ngisho nakumafomethi ahlukahlukene, anciphisa kakhulu ijubane kumatafula amakhulu.
• Ukusebenzisa njengo-opharetha wokufana kombhalo kusuka ku-Visual Basic esongwe ngomsebenzi wamakhro wangokwezifiso. Lokhu kukuvumela ukuthi usebenzise usesho oluvumelana nezimo usebenzisa izinhlamvu ze-wildcard (*, #,?, njll.) Ngeshwa, leli thuluzi alikwazi ukukhipha uchungechunge oluncane olufunekayo embhalweni - hlola kuphela ukuthi uqukethwe kulo.

Ngaphezu kwalokhu okungenhla, kunenye indlela eyaziwa kakhulu emibuthanweni emincane yabahleli bezinhlelo abangochwepheshe, abathuthukisi bewebhu kanye namanye ama-techies - lokhu izinkulumo ezijwayelekile (Regular Expressions = RegExp = “regexps” = “regulars”). Kalula nje, I-RegExp iwulimi lapho izinhlamvu ezikhethekile nemithetho kusetshenziswa khona ukuze kuseshelwe iyunithi yezinhlamvu edingekayo embhalweni, ukuyikhipha noma esikhundleni sayo kufakwe omunye umbhalo.. Izinkulumo ezivamile ziyithuluzi elinamandla kakhulu futhi elihle elidlula zonke ezinye izindlela zokusebenza ngombhalo ngokuhleleka kobukhulu. Izilimi eziningi zokuhlela (C#, PHP, Perl, JavaScript…) kanye nabahleli bombhalo (IZwi, i-Notepad++…) zisekela izinkulumo ezivamile.

I-Microsoft Excel ngeshwa ayinakho ukwesekwa kwe-RegExp ngaphandle kwebhokisi, kodwa lokhu kungalungiswa kalula nge-VBA. Vula i-Visual Basic Editor kuthebhu Unjiniyela (Unjiniyela) noma isinqamuleli sekhibhodi Alt+F11. Bese ufaka imojuli entsha ngemenyu Faka – Imojula bese ukopisha umbhalo womsebenzi omkhulu olandelayo lapho:

I-Public Function RegExpExtract(Umbhalo Njengeyunithi Yezintambo, Iphethini Njengochungechunge, Into Ongakhetha Ngayo Njengenani Eliphelele = 1) Njengentambo Ephutheni GoTo ErrHandl Setha i-regex = CreateObject("VBScript.RegExp") regex.Pattern = Iphethini regex.Global = Iqiniso Uma regex.Test (Umbhalo) Bese Setha okufanayo = regex.Execute(Umbhalo) RegExpExtract = okufanayo.Into(Into - 1) Phuma Umsebenzi End Uma IphuthaHandl: RegExpExtract = CVERr(xlErrValue) End Function  

Manje sesingakwazi ukuvala i-Visual Basic Editor futhi sibuyele ku-Excel ukuze sizame isici sethu esisha. I-syntax yayo yile elandelayo:

=RegExpExtract( Txt ; Iphethini ; Into )

lapho

• txt – iseli elinombhalo esiwuhlolayo nesifuna ukukhipha kuwo uchungechunge oluncane esiludingayo
• iphethini – imaski (iphethini) yokusesha intambo encanyana
• Into - inombolo yokulandelana yochungechunge oluncane oluzokhishwa, uma kuneziningana zazo (uma zingacacisiwe, khona-ke ukwenzeka kokuqala kuyaboniswa)

Into ethakazelisa kakhulu lapha, yiqiniso, i-Pattern - intambo yesifanekiso yezinhlamvu ezikhethekile "ngolimi" lwe-RegExp, ecacisa ukuthi yini ngempela nokuthi sifuna ukuphi. Nazi eziyisisekelo kakhulu ukuze uqale:

Uma sifuna inombolo ethile yezinhlamvu, isibonelo, ikhodi yeposi enezinhlamvu eziyisithupha noma wonke amakhodi omkhiqizo anezinhlamvu ezintathu, khona-ke siyasiza. izihlanganisi or izihlanganisi yizinkulumo ezikhethekile ezicacisa inani lezinhlamvu okufanele ziseshwe. Ama-Quantifiers asetshenziswa kuhlamvu oluza ngaphambi kwalo:

Manje ake sidlulele engxenyeni ethakazelisa kakhulu - ukuhlaziya ukusetshenziswa komsebenzi odaliwe kanye nalokho esikufundile ngamaphethini ezibonelo ezingokoqobo zokuphila.

Ikhipha izinombolo embhalweni

Okokuqala, ake sihlaziye icala elilula - udinga ukukhipha inombolo yokuqala kuphalishi we-alphanumeric, isibonelo, amandla okunikezwa kwamandla angaphazamiseki ohlwini lwamanani:

I-logic ngemuva kwenkulumo evamile ilula: d kusho noma iyiphi idijithi, kanye nesilinganisi + ithi inombolo yabo kufanele ibe eyodwa noma ngaphezulu. Ukukhipha kabili okuphambi komsebenzi kuyadingeka ukuze "uphaphame" uguqule izinhlamvu ezikhishiwe zibe inombolo egcwele ukusuka kunombolo-njengombhalo.

Ikhodi

Uma uthi nhlá, yonke into ilula lapha - sifuna amadijithi ayisithupha ngqo ngokulandelana. Sisebenzisa uhlamvu olukhethekile d okwedijithi kanye ne-quantifier 6 {} ngenani lezinhlamvu:

Kodwa-ke, isimo singenzeka lapho, kwesokunxele senkomba emgqeni, kukhona enye isethi enkulu yezinombolo ngokulandelana (inombolo yocingo, i-TIN, i-akhawunti yasebhange, njll.) Khona-ke inkathi yethu evamile izokhipha i-6 yokuqala amadijithi asuka kuyo, okungukuthi ngeke asebenze kahle:

Ukuvimbela lokhu ukuthi kungenzeki, sidinga ukungeza isilungisi emaphethelweni enkulumo yethu evamile b okukhombisa ukuphela kwegama. Lokhu kuzokucacisa ku-Excel ukuthi ucezu (inkomba) esiludingayo kufanele lube igama elihlukile, hhayi ingxenye yolunye ucezu (inombolo yocingo):

Hambayo

Inkinga ngokuthola inombolo yocingo embhalweni ukuthi kunezinketho eziningi zokubhala izinombolo - ngamakhonco noma ngaphandle, ngokusebenzisa izikhala, noma ngaphandle kwekhodi yesifunda kubakaki, njll. Ngakho-ke, ngokubona kwami, kulula qala ngokukhipha zonke lezi zinhlamvu embhalweni oyimithombo usebenzisa imisebenzi embalwa evalelwe I-SUBSTITUTE (SUBSTITUTE)ukuze inamathele ndawonye ibe yinto eyodwa, bese iba nejwayelekile yakudala d{11} khipha izinombolo eziyi-11 zilandelana:

I-ITN

Kuxaka kakhulu lapha, ngoba i-TIN (Ezweni Lethu) ingaba namadijithi angu-10 (kumabhizinisi asemthethweni) noma amadijithi angu-12 (kubantu ngabanye). Uma ungatholi iphutha ikakhulukazi, khona-ke kungenzeka ukuthi waneliseke ngokujwayelekile d{10,12}, kodwa, uma sikhuluma nje, izokhipha zonke izinombolo kusuka ezinhlamvini eziyi-10 kuye kweziyi-12, okungukuthi futhi ifake amadijithi ayi-11 ngephutha. Kungaba okulungile kakhulu ukusebenzisa amaphethini amabili axhunywe u-opharetha onengqondo NOMA | (ibha eqondile):

Sicela uqaphele ukuthi embuzweni siqala ngokubheka izinombolo ze-12-bit, bese kuphela izinombolo ze-10-bit. Uma sibhala inkulumo yethu evamile ngenye indlela, izokhipha wonke umuntu, ngisho nama-TIN amade angu-12-bit, izinhlamvu zokuqala ezingu-10 kuphela. Okusho ukuthi, ngemuva kokuthi isimo sokuqala sicushiwe, ukuqinisekiswa okwengeziwe akwenziwa:

Lona umehluko oyisisekelo phakathi komsebenzisi | kusukela kumsebenzi ojwayelekile we-excel logic OR (OR), lapho ukuhlela kabusha izimpikiswano kungawushintshi umphumela.

Ama-SKU womkhiqizo

Ezinkampanini eziningi, izihlonzi eziyingqayizivele zinikezwa izimpahla nezinsizakalo - izindatshana, amakhodi we-SAP, ama-SKU, njll. Uma kukhona okunengqondo ekubhaleni kwabo, khona-ke bangakhishwa kalula kunoma yimuphi umbhalo ngokusebenzisa izinkulumo ezivamile. Isibonelo, uma sazi ukuthi izindatshana zethu zihlala zinezinhlamvu ezintathu ezinkulu zesiNgisi, i-hyphen kanye nenombolo yamadijithi amathathu elandelayo:

I-logic ngemuva kwesifanekiso ilula. [AZ] – kusho noma yiziphi izinhlamvu ezinkulu zezinhlamvu zesiLatini. I-quantifier elandelayo 3 {} ithi kubalulekile kithi ukuthi zibe ntathu ncamashi izinhlamvu ezinjalo. Ngemuva kwe-hyphen, silinde amadijithi amathathu, ngakho-ke sengeza ekugcineni d{3}

Amanani emali

Ngendlela efanayo nesigaba esandulele, ungakwazi futhi ukukhipha amanani (izindleko, i-VAT ...) encazelweni yezimpahla. Uma amanani emali, ngokwesibonelo, ekhonjiswa ngekhonco, khona-ke:

Iphethini d nge-quantifier + icinga noma iyiphi inombolo ifike ku-hyphen, futhi d{2} uzobheka amapeni (amadijithi amabili) ngemuva kwalokho.

Uma udinga ukukhipha hhayi amanani, kodwa i-VAT, ungasebenzisa i-agumenti yesithathu yokuzikhethela yomsebenzi wethu we-RegExpExtract, ecacisa inombolo ye-ordinal ye-elementi ezokhishwa. Futhi, yiqiniso, ungabuyisela umsebenzi I-SUBSTITUTE (SUBSTITUTE) emiphumeleni, xhumanisa isihlukanisi sedesimali esijwayelekile bese wengeza ukususa okuphindwe kabili ekuqaleni ukuze i-Excel ihumushe i-VAT etholiwe njengenombolo evamile:

Izinombolo zamapuleti emoto

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:

Isikhathi

Ukuze kukhishwe isikhathi ngefomethi ye-HH:MM, inkulumo evamile elandelayo ifanelekile:

Ngemva kocezu lwekholoni [0-5]d, njengoba kulula ukuyithola, isetha noma iyiphi inombolo ebangeni 00-59. Ngaphambi kwekholoni kubakaki, amaphethini amabili ayasebenza, ahlukaniswe yi-logical NOMA (ipayipi):

• [0-1]d - noma iyiphi inombolo ebangeni 00-19
• 2[0-3] - noma iyiphi inombolo ebangeni 20-23

Emphumeleni otholiwe, ungasebenzisa futhi umsebenzi ojwayelekile we-Excel ISIKHATHI (TEAM)ukuyiguqulela kwifomethi yesikhathi eqondakalayo ohlelweni futhi elungele ezinye izibalo.

Ukuhlola iphasiwedi

Ake sithi sidinga ukuhlola uhlu lwamagama-mfihlo aqanjwe abasebenzisi ngokunemba. Ngokwemithetho yethu, amagama ayimfihlo angaba nezinhlamvu zesiNgisi kuphela (ofeleba abancane noma abakhulu) nezinombolo. Izikhala, ama-underscore nezinye izimpawu zokubhala azivunyelwe.

Ukuhlola kungahlelwa kusetshenziswa izinkulumo ezivamile ezilandelayo:

Eqinisweni, ngephethini elinjalo sidinga ukuthi phakathi kokuqala (^) futhi ekugcineni ($) embhalweni wethu kwakunezinhlamvu kuphela ezivela kwisethi enikezwe kubakaki abayisikwele. Uma futhi udinga ukuhlola ubude bephasiwedi (isibonelo, okungenani izinhlamvu eziyisi-6), bese-ke isilinganisi. + indawo yayo ingashintshwa yisikhawu esithi “isithupha noma ngaphezulu” efomini {6,}:

Idolobha kusuka ekhelini

Ake sithi sidinga ukudonsa idolobha kusukela kubha yamakheli. Uhlelo olujwayelekile luzosiza, lukhiphe umbhalo kokuthi “g.” kukhefana olandelayo:

Ake sibhekisise leli phethini.

Uma ufunde umbhalo ongenhla, khona-ke usuyaqonda ukuthi ezinye izinhlamvu zezinkulumo ezijwayelekile (izikhathi, izinkanyezi, izimpawu zamadola, njll.) zinencazelo ekhethekile. Uma udinga ukuzibheka lezi zinhlamvu ngokwazo, khona-ke zandulelwa i-backslash (ngezinye izikhathi ebizwa ngokuthi ukuvikela). Ngakho-ke, lapho usesha ucezu “g.” kufanele sibhale ngendlela evamile Mr. uma sifuna ukuhlanganisa, ke + njll

Izinhlamvu ezimbili ezilandelayo kusifanekiso sethu, ichashazi kanye nenkanyezi ye-quantifier, imele noma iyiphi inombolo yanoma yiziphi izinhlamvu, okungukuthi noma yiliphi igama ledolobha.

Kukhona ukhefana ekugcineni kwesifanekiso, ngoba sifuna umbhalo osuka kokuthi “g.” kukhefana. Kodwa kungase kube namakhoma amaningana embhalweni, akunjalo? Hhayi kuphela ngemva kwedolobha, kodwa nangemva komgwaqo, izindlu, njll. Isicelo sethu sizoyeka kuphi kubo? Yilokho okushiwo uphawu lombuzo. Ngaphandle kwayo, ukusho kwethu okujwayelekile kuzokhipha iyunithi yezinhlamvu ende kakhulu ngangokunokwenzeka:

Ngokuphathelene nezinkulumo ezivamile, iphethini elinjalo "lihaha". Ukuze kulungiswe isimo, kudingeka uphawu lombuzo - lenza isilinganisi ngemuva kwalokho sime "sincishana" - futhi umbuzo wethu uthatha umbhalo ufinyelele kuphela kukhefana wokuqala wekhawunta ngemva kuka "g.":

Igama lefayela kusuka kumzila ogcwele

Esinye isimo esivame kakhulu ukukhipha igama lefayela endleleni egcwele. Ukuveza okuvamile kwefomu kuzosiza lapha:

Iqhinga lapha ukuthi ukusesha, empeleni, kwenzeka ngakolunye uhlangothi - kusukela ekugcineni kuya ekuqaleni, ngoba ekugcineni kwesifanekiso sethu $, futhi sibheka yonke into ngaphambi kwayo kuze kufike ku-backslash yokuqala ukusuka kwesokudla. I-backslash iphunyukile, njengechashaza esibonelweni sangaphambilini.

PS

"Ngasekupheleni" ngifuna ukucacisa ukuthi konke okungenhla kuyingxenye encane yawo wonke amathuba anikezwa yizinkulumo ezivamile. Kunezinhlamvu eziningi ezikhethekile nemithetho yokusetshenziswa kwazo, futhi zonke izincwadi zibhaliwe ngalesi sihloko (ngincoma ukuthi okungenani lesi siqale). Ngandlela-thile, ukubhala izinkulumo ezivamile cishe kuwubuciko. Cishe njalo, isisho esivamile esiqanjiwe singathuthukiswa noma sengezwe, sikwenze sibe sihle kakhulu noma sikwazi ukusebenza ngebanga elibanzi ledatha yokufaka.

Ukuze uhlaziye futhi uhlukanise izinkulumo ezivamile zabanye abantu noma ulungise okwakho, kunamasevisi ambalwa afaneleka ku-inthanethi: I-RegEx101, I-RegExr futhi nokuningi

Ngeshwa, akuzona zonke izici zezinkulumo ezivamile ezisekelwe ku-VBA (isibonelo, ukusesha okuphambene noma amakilasi e-POSIX) futhi angasebenza ngeCyrillic, kodwa ngicabanga ukuthi lokho okukhona kwanele okokuqala ukukujabulisa.

Uma ungemusha esihlokweni, futhi unokuthile ongakwaba, shiya izinkulumo ezivamile ziwusizo lapho usebenza ku-Excel kumazwana angezansi. Umqondo owodwa muhle, kodwa amabhuzu amabili ayipheya!

• Ukushintsha nokuhlanza umbhalo ngomsebenzi we-SUBSTITUTE
• Search and highlighting of Latin characters in text
• Sesha umbhalo oseduze ofanayo (Ivanov = Ivonov = Ivanof, njll.)