Ṣọ ọrọ pẹlu awọn ikosile deede (RegExp) ni Excel

Ọkan ninu awọn iṣẹ-ṣiṣe ti n gba akoko pupọ julọ ati idiwọ nigbati o n ṣiṣẹ pẹlu ọrọ ni Excel jẹ itupalẹ - sisọ awọn “porridge” alphanumeric sinu awọn paati ati yiyọ awọn ajẹkù ti a nilo lati inu rẹ. Fun apere:

• Yiyọ koodu zip lati adirẹsi naa (o dara ti koodu zip ba wa nigbagbogbo ni ibẹrẹ, ṣugbọn kini ti kii ba ṣe bẹ?)
• wiwa awọn nọmba ati ọjọ ti awọn risiti lati awọn apejuwe ti awọn sisan ninu awọn ifowo gbólóhùn
• isediwon TIN lati awọn apejuwe motley ti awọn ile-iṣẹ ninu atokọ ti awọn ẹlẹgbẹ
• wa nọmba ọkọ ayọkẹlẹ tabi nọmba nkan ninu apejuwe, ati bẹbẹ lọ.
  

Ni igbagbogbo ni iru awọn ọran, lẹhin idaji wakati kan ti gbigbe ọrọ inu ọrọ pẹlu ọwọ, awọn ero bẹrẹ lati wa si ọkan bakan lati ṣe adaṣe ilana yii (paapaa ti data pupọ ba wa). Awọn solusan pupọ lo wa ati pẹlu awọn iwọn oriṣiriṣi ti idiju-ṣiṣe:

• lilo -itumọ ti ni tayo ọrọ awọn iṣẹ lati wa ọrọ-pipa-lẹ pọ: LEVSIMV (Osi), ọtun (Ọtun), PSTR (aarin), STsEPIT (CONCATENATE) ati awọn afọwọṣe rẹ, PỌPỌ (JOINTEXT), OWO (GANKAN) bbl Ọna yii dara ti o ba wa ni oye oye ninu ọrọ (fun apẹẹrẹ, atọka nigbagbogbo ni ibẹrẹ adirẹsi). Bibẹẹkọ, awọn agbekalẹ di idiju pupọ sii ati, nigbami, paapaa wa si awọn ilana agbekalẹ, eyiti o fa fifalẹ pupọ lori awọn tabili nla.
• lilo bi onišẹ ibajọra ọrọ lati Visual Ipilẹ ti a we ni a aṣa Makiro iṣẹ. Eyi n gba ọ laaye lati ṣe wiwa wiwa rọ diẹ sii nipa lilo awọn ohun kikọ egan (*, #,?, ati bẹbẹ lọ) Laanu, ọpa yii ko le yọkuro ti o fẹ lati inu ọrọ naa - ṣayẹwo nikan ti o ba wa ninu rẹ.

Ni afikun si eyi ti o wa loke, ọna miiran wa ti o mọ daradara ni awọn iyika dín ti awọn olutọpa ọjọgbọn, awọn olupilẹṣẹ wẹẹbu ati awọn imọ-ẹrọ miiran - eyi ni deede ikosile (Awọn ikosile deede = RegExp = "regexps" = "awọn ilana"). Ni kukuru, RegExp jẹ ede nibiti awọn kikọ pataki ati awọn ofin ti lo lati wa awọn gbolohun ọrọ pataki ninu ọrọ naa, jade wọn jade tabi rọpo wọn pẹlu ọrọ miiran. Awọn ikosile igbagbogbo jẹ ohun elo ti o lagbara pupọ ati ẹwa ti o kọja gbogbo awọn ọna miiran ti ṣiṣẹ pẹlu ọrọ nipasẹ aṣẹ titobi. Ọpọlọpọ awọn ede siseto (C#, PHP, Perl, JavaScript…) ati awọn olootu ọrọ (Ọrọ, Notepad++…) ṣe atilẹyin awọn ikosile deede.

Microsoft Excel laanu ko ni atilẹyin RegExp lati inu apoti, ṣugbọn eyi le ṣe atunṣe ni rọọrun pẹlu VBA. Ṣii Olootu Ipilẹ Visual lati taabu developer (Olùgbéejáde) tabi ọna abuja keyboard alt+F11. Lẹhinna fi module tuntun sii nipasẹ akojọ aṣayan Fi sii - Module ati daakọ ọrọ ti iṣẹ macro atẹle nibẹ:

Išẹ ti gbogbo eniyan RegExpExtract(Ọrọ Bi Okun, Ilana Bi Okun, Ohun Iyan Bi Odidi = 1) Bi Okun Lori Aṣiṣe GoTo ErrHandl Ṣeto regex = CreateObject("VBScript.RegExp") regex.Pattern = Àpẹẹrẹ regex.Global = Otitọ Ti regexTest. (Text) Lẹhinna Ṣeto awọn ere-kere = regex.Execute (Ọrọ) RegExpExtract = matches.Nkan (Nkan kan - 1) Iṣe Ipari Ti ErrHandl: RegExpExtract = CVERrr (xlErrValue) Iṣẹ Ipari  

A le ni bayi pa Olootu Ipilẹ wiwo ati pada si Excel lati gbiyanju ẹya tuntun wa. Sintasi rẹ jẹ atẹle yii:

=RegExpExtract (Txt; Àpẹẹrẹ; Nkan)

ibi ti

• txt – sẹẹli kan pẹlu ọrọ ti a n ṣayẹwo ati lati inu eyiti a fẹ lati fa jade okun ti a nilo
• apẹẹrẹ – boju (apẹẹrẹ) fun wiwa substring
• ohun - nọmba ọkọọkan ti okun-ọrọ lati fa jade, ti ọpọlọpọ wọn ba wa (ti ko ba sọ pato, lẹhinna iṣẹlẹ akọkọ ti han)

Ohun ti o nifẹ julọ nibi, dajudaju, jẹ Apẹrẹ - okun awoṣe ti awọn ohun kikọ pataki “ni ede” ti RegExp, eyiti o ṣalaye kini gangan ati ibiti a fẹ wa. Eyi ni awọn ipilẹ julọ lati jẹ ki o bẹrẹ:

Ti a ba n wa nọmba awọn ohun kikọ kan, fun apẹẹrẹ, koodu ifiweranse oni-nọmba mẹfa tabi gbogbo awọn koodu ọja mẹta-mẹta, lẹhinna a wa si igbala quantifiers or quantifiers jẹ awọn ikosile pataki ti o pato nọmba awọn ohun kikọ lati wa. Quantifiers ni a lo si iwa ti o wa niwaju rẹ:

Bayi jẹ ki a lọ si apakan ti o nifẹ julọ - itupalẹ ti ohun elo ti iṣẹ ti a ṣẹda ati ohun ti a kọ nipa awọn ilana lori awọn apẹẹrẹ iṣe lati igbesi aye.

Yiyọ awọn nọmba lati ọrọ

Lati bẹrẹ pẹlu, jẹ ki a ṣe itupalẹ ọran ti o rọrun - o nilo lati jade nọmba akọkọ lati inu porridge alphanumeric, fun apẹẹrẹ, agbara ti awọn ipese agbara ailopin lati atokọ idiyele:

Imọye ti o wa lẹhin ikosile deede jẹ rọrun: d tumo si eyikeyi nọmba, ati awọn quantifier + sọ pe nọmba wọn yẹ ki o jẹ ọkan tabi diẹ sii. Iyokuro ilọpo meji ni iwaju iṣẹ naa ni a nilo lati “lori fo” yi awọn ohun kikọ ti a fa jade sinu nọmba ni kikun lati nọmba-bi-ọrọ.

Kóòdù

Ni wiwo akọkọ, ohun gbogbo rọrun nibi - a n wa awọn nọmba mẹfa gangan ni ọna kan. A lo ohun kikọ pataki kan d fun oni-nọmba ati quantifier 6 {} fun nọmba awọn ohun kikọ:

Sibẹsibẹ, ipo kan ṣee ṣe nigbati, si apa osi ti atọka ni ila, awọn nọmba nla miiran wa ni ọna kan (nọmba foonu, TIN, akọọlẹ banki, bbl) Lẹhinna akoko deede wa yoo fa jade 6 akọkọ. awọn nọmba lati inu rẹ, ie kii yoo ṣiṣẹ bi o ti tọ:

Lati ṣe idiwọ eyi lati ṣẹlẹ, a nilo lati ṣafikun modifier ni ayika awọn egbegbe ti ikosile deede wa b nfihan opin ọrọ kan. Eyi yoo jẹ ki o ye wa si Excel pe ajẹkù (atọka) ti a nilo yẹ ki o jẹ ọrọ lọtọ, kii ṣe apakan ti ajẹkù miiran (nọmba foonu):

Phone

Iṣoro pẹlu wiwa nọmba foonu kan ninu ọrọ naa ni pe ọpọlọpọ awọn aṣayan fun kikọ awọn nọmba - pẹlu ati laisi hyphens, nipasẹ awọn alafo, pẹlu tabi laisi koodu agbegbe ni awọn biraketi, bbl Nitorina, ninu ero mi, o rọrun lati Ni akọkọ nu gbogbo awọn ohun kikọ wọnyi kuro lati ọrọ orisun nipa lilo awọn iṣẹ itẹle pupọ AKỌRỌ (APAPO)ki o duro papọ sinu odidi ẹyọkan, ati lẹhinna pẹlu deede atijo d{11} fa awọn nọmba 11 jade ni ọna kan:

ITN

O jẹ idiju diẹ sii nibi, nitori TIN (ni Orilẹ-ede Wa) le jẹ oni-nọmba 10 (fun awọn ile-iṣẹ ti ofin) tabi oni-nọmba 12 (fun awọn eniyan kọọkan). Ti o ko ba rii ẹbi paapaa, lẹhinna o ṣee ṣe pupọ lati ni itẹlọrun pẹlu deede d{10,12}, ṣugbọn, ni muna soro, yoo jade gbogbo awọn nọmba lati 10 to 12 ohun kikọ, ie ati ki o asise ti tẹ 11 awọn nọmba. Yoo jẹ deede diẹ sii lati lo awọn ilana meji ti o ni asopọ nipasẹ onisẹ OR ọgbọn kan | (ọpa inaro):

Jọwọ ṣe akiyesi pe ninu ibeere a kọkọ wa awọn nọmba 12-bit, ati lẹhinna nikan fun awọn nọmba 10-bit. Ti a ba kọ ikosile deede wa ni ọna miiran, lẹhinna yoo fa jade fun gbogbo eniyan, paapaa awọn TINs 12-bit gun, nikan awọn ohun kikọ 10 akọkọ. Iyẹn ni, lẹhin ipo akọkọ ti nfa, ijẹrisi siwaju ko ṣee ṣe:

Eyi ni iyatọ ipilẹ laarin oniṣẹ ẹrọ | lati kan boṣewa tayo kannaa iṣẹ OR (OR), nibiti atunto awọn ariyanjiyan ko yi abajade pada.

Awọn SKU ọja

Ni ọpọlọpọ awọn ile-iṣẹ, awọn idamọ alailẹgbẹ ni a yàn si awọn ọja ati awọn iṣẹ - awọn nkan, awọn koodu SAP, SKUs, bbl Ti o ba wa ni imọran ninu akọsilẹ wọn, lẹhinna wọn le ni irọrun fa jade kuro ninu eyikeyi ọrọ nipa lilo awọn ikosile deede. Fun apẹẹrẹ, ti a ba mọ pe awọn nkan wa nigbagbogbo ni awọn lẹta Gẹẹsi nla mẹta, hyphen ati nọmba oni-nọmba mẹta ti o tẹle, lẹhinna:

Awọn kannaa sile awọn awoṣe ni o rọrun. [AZ] – tumo si eyikeyi olu awọn lẹta ti awọn Latin alfabeti. Nigbamii ti quantifier 3 {} so wipe o jẹ pataki fun wa pe o wa ni pato mẹta iru awọn lẹta. Lẹhin hyphen, a n duro de awọn nọmba mẹta, nitorinaa a ṣafikun ni ipari d{3}

Awọn iye owo

Ni ọna kanna si paragira ti tẹlẹ, o tun le fa awọn idiyele jade (awọn idiyele, VAT…) lati apejuwe awọn ẹru. Ti awọn iye owo, fun apẹẹrẹ, jẹ itọkasi pẹlu hyphen, lẹhinna:

Àpẹẹrẹ d pẹlu quantifier + awọrọojulówo fun eyikeyi nọmba to a hyphen, ati d{2} yoo wa awọn pennies (awọn nọmba meji) lẹhin.

Ti o ba nilo lati jade kii ṣe awọn idiyele, ṣugbọn VAT, lẹhinna o le lo ariyanjiyan aṣayan kẹta ti iṣẹ RegExpExtract wa, eyiti o ṣalaye nọmba ordinal ti eroja lati fa jade. Ati, dajudaju, o le rọpo iṣẹ naa AKỌRỌ (APAPO) ninu awọn abajade, hyphen si iyasọtọ eleemewa boṣewa ki o ṣafikun iyokuro ilọpo meji ni ibẹrẹ ki Excel tumọ VAT ti o rii bi nọmba deede:

Car awo awọn nọmba

Ti o ko ba gba awọn ọkọ ayọkẹlẹ pataki, awọn tirela ati awọn alupupu miiran, lẹhinna nọmba ọkọ ayọkẹlẹ boṣewa ti pin ni ibamu si ipilẹ “lẹta - awọn nọmba mẹta - awọn lẹta meji - koodu agbegbe”. Pẹlupẹlu, koodu agbegbe le jẹ oni-nọmba 2- tabi 3, ati pe awọn ti o jọra ni irisi si alfabeti Latin ni a lo bi awọn lẹta. Nitorinaa, ikosile deede atẹle yoo ṣe iranlọwọ fun wa lati yọ awọn nọmba jade lati ọrọ naa:

Time

Lati yọ akoko jade ni ọna kika HH:MM, ikosile deede atẹle yii dara:

Lẹhin ajeku oluṣafihan [0-5] d, bi o ṣe rọrun lati ro ero, ṣeto nọmba eyikeyi ni ibiti 00-59. Ṣaaju ki oluṣafihan ninu akomo, awọn ilana meji n ṣiṣẹ, ti o yapa nipasẹ ọgbọn OR (pipe):

• [0-1] d - eyikeyi nọmba ninu awọn 00-19
• 2[0-3] - eyikeyi nọmba ninu awọn 20-23

Si abajade ti o gba, o tun le lo iṣẹ boṣewa Excel Akoko (EGBE)lati yi pada si ọna kika akoko ti o jẹ oye si eto ati pe o dara fun awọn iṣiro siwaju sii.

Ṣayẹwo ọrọ igbaniwọle

Ṣebi pe a nilo lati ṣayẹwo atokọ ti awọn ọrọ igbaniwọle ti awọn olumulo ṣe fun titọ. Gẹgẹbi awọn ofin wa, awọn ọrọ igbaniwọle le ni awọn lẹta Gẹẹsi nikan (kekere tabi nla) ati awọn nọmba. Awọn aaye, awọn ami-ami ati awọn aami ifamisi miiran ko gba laaye.

Ṣiṣayẹwo le jẹ ṣeto pẹlu lilo ikosile deede ti o rọrun wọnyi:

Ni otitọ, pẹlu iru apẹẹrẹ a nilo pe laarin ibẹrẹ (^) ati ipari ($) ninu ọrọ wa awọn ohun kikọ nikan ni o wa lati ṣeto ti a fun ni awọn biraketi onigun mẹrin. Ti o ba tun nilo lati ṣayẹwo ipari ọrọ igbaniwọle (fun apẹẹrẹ, o kere ju awọn ohun kikọ 6), lẹhinna quantifier + le paarọ rẹ nipasẹ aarin “mefa tabi diẹ sii” ni fọọmu naa {6,}:

Ilu lati adirẹsi

Jẹ ki a sọ pe a nilo lati fa ilu naa kuro ni ọpa adirẹsi. Eto deede yoo ṣe iranlọwọ, yiyo ọrọ jade lati “g.” si aami idẹsẹ atẹle:

Jẹ ki a ṣe akiyesi apẹrẹ yii ni pẹkipẹki.

Ti o ba ti ka ọrọ ti o wa loke, lẹhinna o ti loye tẹlẹ pe diẹ ninu awọn ohun kikọ ni awọn ọrọ deede (awọn akoko, awọn asterisks, awọn ami dola, bbl) ni itumọ pataki kan. Ti o ba nilo lati wa awọn ohun kikọ wọnyi funrara wọn, lẹhinna wọn ti ṣaju nipasẹ ifẹhinti (nigbakugba ti a pe idabobo). Nítorí náà, nígbà tí a bá ń wá àjákù “g” náà. a ni lati kọ ni deede ikosile Ogbeni ti a ba n wa afikun, lẹhinna + ati be be lo

Awọn ohun kikọ meji ti o tẹle ninu awoṣe wa, aami ati ami akiyesi quantifier, duro fun nọmba eyikeyi ti eyikeyi ohun kikọ, ie eyikeyi orukọ ilu.

Koma wa ni opin awoṣe, nitori a n wa ọrọ lati “g.” to koma. Ṣugbọn aami idẹsẹ pupọ le wa ninu ọrọ naa, otun? Kii ṣe lẹhin ilu nikan, ṣugbọn lẹhin opopona, awọn ile, ati bẹbẹ lọ Lori tani ninu wọn yoo da ibeere wa duro? Iyẹn ni ami ibeere fun. Laisi rẹ, ikosile deede wa yoo fa okun to gun julọ ti o ṣeeṣe:

Ni awọn ofin ti awọn ikosile deede, iru apẹẹrẹ jẹ "ojukokoro". Lati ṣe atunṣe ipo naa, aami ibeere ni a nilo – o jẹ ki iwọn-iwọn lẹhin eyiti o duro “korira” – ati pe ibeere wa gba ọrọ naa nikan titi de ami idẹsẹ akọkọ lẹhin “g.”:

Orukọ faili lati ọna kikun

Ipo miiran ti o wọpọ julọ ni lati jade orukọ faili lati ọna kikun. Ikosile deede ti o rọrun ti fọọmu naa yoo ṣe iranlọwọ nibi:

Ẹtan nibi ni pe wiwa, ni otitọ, waye ni idakeji - lati opin si ibẹrẹ, nitori ni opin awoṣe wa jẹ $, ati pe a n wa ohun gbogbo ṣaaju ki o to si ẹhin akọkọ lati ọtun. Awọn ifẹhinti salọ, bi aami ninu apẹẹrẹ ti tẹlẹ.

PS

"Si ọna opin" Mo fẹ lati ṣalaye pe gbogbo awọn ti o wa loke jẹ apakan kekere ti gbogbo awọn ti o ṣeeṣe ti awọn ikosile deede pese. Ọpọlọpọ awọn kikọ pataki ati awọn ofin wa fun lilo wọn, ati pe gbogbo awọn iwe ti kọ lori koko yii (Mo ṣeduro o kere ju eyi fun ibẹrẹ). Ni ọna kan, kikọ awọn ikosile deede jẹ fere aworan kan. O fẹrẹ jẹ nigbagbogbo, ikosile deede ti a ṣẹda le ni ilọsiwaju tabi ṣe afikun, ti o jẹ ki o yangan diẹ sii tabi ni anfani lati ṣiṣẹ pẹlu titobi data igbewọle.

Lati ṣe itupalẹ ati itupalẹ awọn ikosile deede ti awọn eniyan miiran tabi ṣatunṣe tirẹ, ọpọlọpọ awọn iṣẹ ori ayelujara ti o rọrun wa: RegEx101, RegExr ati siwaju sii

Laanu, kii ṣe gbogbo awọn ẹya ti awọn ikosile deede Ayebaye ni atilẹyin ni VBA (fun apẹẹrẹ, wiwa iyipada tabi awọn kilasi POSIX) ati pe o le ṣiṣẹ pẹlu Cyrillic, ṣugbọn Mo ro pe ohun ti o wa ni to fun igba akọkọ lati wu ọ.

Ti o ko ba jẹ tuntun si koko-ọrọ naa, ati pe o ni nkan lati pin, fi awọn ọrọ deede silẹ ti o wulo nigbati o ṣiṣẹ ni Excel ninu awọn asọye ni isalẹ. Ọkan jẹ dara, ṣugbọn awọn bata orunkun meji jẹ bata!

• Rirọpo ati nu ọrọ soke pẹlu iṣẹ SUBSTITUTE
• Wa ati afihan awọn ohun kikọ Latin ninu ọrọ
• Wa ọrọ ti o jọra ti o sunmọ julọ (Ivanov = Ivonov = Ivanof, ati bẹbẹ lọ)