Dosrannu testun gydag ymadroddion rheolaidd (RegExp) yn Excel

Un o'r tasgau mwyaf llafurus a rhwystredig wrth weithio gyda thestun yn Excel yw dosrannu – dosrannu’r “uwd” alffaniwmerig yn gydrannau a thynnu’r darnau sydd eu hangen arnom ohono. Er enghraifft:

• tynnu'r cod zip o'r cyfeiriad (mae'n dda os yw'r cod zip bob amser ar y dechrau, ond beth os nad ydyw?)
• dod o hyd i rif a dyddiad yr anfoneb o'r disgrifiad o'r taliad yn y cyfriflen banc
• echdynnu TIN o ddisgrifiadau brith o gwmnïau yn y rhestr o wrthbartïon
• chwilio am rif car neu rif erthygl yn y disgrifiad, ac ati.
  

Fel arfer mewn achosion o'r fath, ar ôl hanner awr o ddewis y testun yn ddiflas â llaw, mae meddyliau'n dechrau dod i'r meddwl rywsut i awtomeiddio'r broses hon (yn enwedig os oes llawer o ddata). Mae yna nifer o atebion a gyda graddau amrywiol o gymhlethdod-effeithlonrwydd:

• Defnyddio adeiledig yn swyddogaethau testun Excel i chwilio-torri-glud testun: LEVSIMV (CHWITH), DDE (DDE), PSTR (canol), STsEPIT (CONCATENATE) a'i analogau, COMBINE (CYD-DESTUN), EXACT (UNION) ac ati Mae'r dull hwn yn dda os oes rhesymeg glir yn y testun (er enghraifft, mae'r mynegai bob amser ar ddechrau'r cyfeiriad). Fel arall, mae'r fformiwlâu yn dod yn llawer mwy cymhleth ac, weithiau, mae hyd yn oed yn dod i fformiwlâu arae, sy'n arafu'n fawr ar fyrddau mawr.
• Defnyddio fel gweithredwr tebygrwydd testun o Visual Basic lapio mewn swyddogaeth macro arferiad. Mae hyn yn eich galluogi i weithredu chwiliad mwy hyblyg gan ddefnyddio nodau nod gwyllt (*, #, ?, ac ati) Yn anffodus, ni all yr offeryn hwn dynnu'r is-linyn a ddymunir o'r testun - dim ond gwirio a yw wedi'i gynnwys ynddo.

Yn ogystal â'r uchod, mae yna ddull arall sy'n adnabyddus iawn mewn cylchoedd cul o raglenwyr proffesiynol, datblygwyr gwe a thechnolegau eraill - dyma ymadroddion rheolaidd (Mynegiadau Rheolaidd = RegExp = “regexps” = “rheolaidd”). Yn syml, Mae RegExp yn iaith lle mae nodau a rheolau arbennig yn cael eu defnyddio i chwilio am yr is-linynnau angenrheidiol yn y testun, eu hechdynnu neu roi testun arall yn eu lle.. Mae ymadroddion rheolaidd yn arf pwerus a hardd iawn sy'n rhagori ar bob ffordd arall o weithio gyda thestun yn ôl trefn maint. Mae llawer o ieithoedd rhaglennu (C#, PHP, Perl, JavaScript…) a golygyddion testun (Word, Notepad ++…) yn cefnogi ymadroddion rheolaidd.

Yn anffodus nid oes gan Microsoft Excel gefnogaeth RegExp allan o'r bocs, ond gellir gosod hyn yn hawdd gyda VBA. Agorwch y Golygydd Sylfaenol Gweledol o'r tab datblygwr (Datblygwr) neu lwybr byr bysellfwrdd Alt+F11. Yna mewnosodwch y modiwl newydd trwy'r ddewislen Mewnosod – Modiwl a chopïwch destun y swyddogaeth macro ganlynol yno:

Swyddogaeth Gyhoeddus RegExpExtract(Testun Fel Llinyn, Patrwm Fel Llinyn, Eitem Ddewisol Fel Cyfanrif = 1) Fel Llinyn Ymlaen Gwall GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Patrwm regex.Global = Gwir Os regex.Test (Testun) Yna Gosod matsis = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Gadael Swyddogaeth Diwedd Os ErrHandl: RegExpExtract = CVErr(xlErrValue) Diwedd Swyddogaeth  

Gallwn nawr gau'r Golygydd Visual Basic a dychwelyd i Excel i roi cynnig ar ein nodwedd newydd. Mae ei chystrawen fel a ganlyn:

=RegExpExtract( Txt; Patrwm; Eitem )

lle

• txt – cell gyda'r testun yr ydym yn ei wirio ac yr ydym am dynnu'r is-linyn sydd ei angen arnom ohoni
• patrwm - mwgwd (patrwm) ar gyfer chwiliad is-linyn
• Eitem - rhif dilyniant yr is-linyn i'w echdynnu, os oes sawl un ohonynt (os na nodir hynny, yna dangosir y digwyddiad cyntaf)

Y peth mwyaf diddorol yma, wrth gwrs, yw Patrwm - llinyn templed o gymeriadau arbennig “yn iaith” RegExp, sy'n nodi beth yn union a ble rydyn ni am ddod o hyd iddo. Dyma'r rhai mwyaf sylfaenol i'ch rhoi ar ben ffordd:

Os ydym yn chwilio am nifer penodol o nodau, er enghraifft, cod post chwe digid neu'r holl godau cynnyrch tair llythyren, yna rydyn ni'n dod i'r adwy. meintioli or meintioli yn fynegiadau arbennig sy'n nodi nifer y nodau i'w chwilio. Mae meintolion yn cael eu cymhwyso i'r cymeriad sy'n dod o'i flaen:

Nawr, gadewch i ni symud ymlaen at y rhan fwyaf diddorol - dadansoddiad o gymhwysiad y swyddogaeth a grëwyd a'r hyn a ddysgom am batrymau ar enghreifftiau ymarferol o fywyd.

Tynnu rhifau o destun

I ddechrau, gadewch i ni ddadansoddi achos syml - mae angen i chi dynnu'r rhif cyntaf o uwd alffaniwmerig, er enghraifft, pŵer cyflenwadau pŵer di-dor o'r rhestr brisiau:

Mae'r rhesymeg y tu ôl i'r mynegiant rheolaidd yn syml: d yn golygu unrhyw ddigid, a'r meintiolydd + yn dweud y dylai eu rhif fod yn un neu fwy. Mae angen y minws dwbl o flaen y ffwythiant i “ar y hedfan” drosi'r nodau a dynnwyd yn rhif llawn o'r rhif-fel-destun.

Cod post

Ar yr olwg gyntaf, mae popeth yn syml yma - rydym yn chwilio am union chwe digid yn olynol. Rydyn ni'n defnyddio cymeriad arbennig d ar gyfer digid a meintiolydd 6 {} am nifer y cymeriadau:

Fodd bynnag, mae sefyllfa'n bosibl pan, i'r chwith o'r mynegai yn y llinell, mae set fawr arall o rifau yn olynol (rhif ffôn, TIN, cyfrif banc, ac ati) Yna bydd ein tymor rheolaidd yn tynnu allan y 6 cyntaf digidau ohono, hy ni fydd yn gweithio'n gywir:

Er mwyn atal hyn rhag digwydd, mae angen inni ychwanegu addasydd o amgylch ymylon ein mynegiant rheolaidd b yn arwyddocau diwedd gair. Bydd hyn yn ei gwneud yn glir i Excel y dylai'r darn (mynegai) sydd ei angen arnom fod yn air ar wahân, ac nid yn rhan o ddarn arall (rhif ffôn):

Rhif Ffôn

Y broblem gyda dod o hyd i rif ffôn yn y testun yw bod cymaint o opsiynau ar gyfer ysgrifennu rhifau – gyda a heb gysylltiadau, trwy fylchau, gyda neu heb god rhanbarth mewn cromfachau, ac ati. Felly, yn fy marn i, mae'n haws i yn gyntaf glanhewch yr holl nodau hyn o'r testun ffynhonnell gan ddefnyddio sawl swyddogaeth nythu TANYSGRIFIAD (SUBSTITUTE)fel ei fod yn glynu at ei gilydd yn un cyfanwaith, ac yna gyda rheolaidd cyntefig d{11} tynnu allan 11 digid yn olynol:

ITN

Mae ychydig yn fwy cymhleth yma, oherwydd gall TIN (yn Ein Gwlad) fod yn 10 digid (ar gyfer endidau cyfreithiol) neu'n 12 digid (ar gyfer unigolion). Os na fyddwch chi'n dod o hyd i fai yn arbennig, yna mae'n eithaf posibl bod yn fodlon â'r rheolaidd d{10,12}, ond, yn fanwl gywir, bydd yn tynnu allan yr holl rifau o 10 i 12 nod, hy ac wedi nodi 11 digid ar gam. Byddai'n fwy cywir defnyddio dau batrwm wedi'u cysylltu gan weithredwr NEU rhesymegol | (bar fertigol):

Sylwch ein bod yn yr ymholiad yn edrych yn gyntaf am rifau 12-did, a dim ond wedyn am rifau 10-did. Os byddwn yn ysgrifennu ein mynegiant rheolaidd y ffordd arall, yna bydd yn tynnu allan i bawb, hyd yn oed tuniau hir 12-did, dim ond y 10 nod cyntaf. Hynny yw, ar ôl i'r cyflwr cyntaf gael ei sbarduno, ni chynhelir gwiriad pellach mwyach:

Dyma'r gwahaniaeth sylfaenol rhwng y gweithredwr | o swyddogaeth rhesymeg excel safonol OR (OR), lle nad yw aildrefnu'r dadleuon yn newid y canlyniad.

SKUs Cynnyrch

Mewn llawer o gwmnïau, neilltuir dynodwyr unigryw i nwyddau a gwasanaethau - erthyglau, codau SAP, SKUs, ac ati. Os oes rhesymeg yn eu nodiant, yna gellir eu tynnu allan yn hawdd o unrhyw destun gan ddefnyddio mynegiadau rheolaidd. Er enghraifft, os ydym yn gwybod bod ein herthyglau bob amser yn cynnwys tair prif lythyren Saesneg, cysylltnod a rhif tri digid dilynol, yna:

Mae'r rhesymeg y tu ôl i'r templed yn syml. [AY] – yn golygu unrhyw briflythrennau yn yr wyddor Ladin. Y meintiolydd nesaf 3 {} yn dweud ei bod yn bwysig i ni fod yna dri llythyr o'r fath yn union. Ar ôl y cysylltnod, rydym yn aros am dri digid, felly rydym yn ychwanegu ar y diwedd d{3}

Symiau arian parod

Mewn ffordd debyg i'r paragraff blaenorol, gallwch hefyd dynnu prisiau (costau, TAW ...) o'r disgrifiad o nwyddau. Os yw symiau ariannol, er enghraifft, yn cael eu nodi gyda chysylltnod, yna:

patrwm d gyda meintiolydd + yn chwilio am unrhyw rif hyd at gysylltnod, a d{2} yn edrych am geiniogau (dau ddigid) ar ôl.

Os oes angen i chi echdynnu nid prisiau, ond TAW, yna gallwch ddefnyddio trydedd ddadl ddewisol ein swyddogaeth RegExpExtract, sy'n pennu rhif trefnol yr elfen i'w hechdynnu. Ac, wrth gwrs, gallwch chi ddisodli'r swyddogaeth TANYSGRIFIAD (SUBSTITUTE) yn y canlyniadau, cysylltnod i'r gwahanydd degol safonol ac ychwanegwch finws dwbl ar y dechrau fel bod Excel yn dehongli'r TAW a ddarganfuwyd fel rhif arferol:

Rhifau platiau car

Os na fyddwch chi'n cymryd cerbydau arbennig, trelars a beiciau modur eraill, yna mae rhif safonol y car yn cael ei ddosrannu yn unol â'r egwyddor “llythyren - tri rhif - dwy lythyren - cod rhanbarth”. Ar ben hynny, gall y cod rhanbarth fod yn 2- neu 3-digid, a dim ond y rhai sy'n debyg o ran ymddangosiad i'r wyddor Ladin a ddefnyddir fel llythrennau. Felly, bydd y mynegiant rheolaidd canlynol yn ein helpu i dynnu rhifau o'r testun:

amser

I echdynnu'r amser yn y fformat HH:MM, mae'r mynegiant rheolaidd canlynol yn addas:

Ar ôl darn colon [0-5]ch, gan ei bod yn hawdd ei chyfrifo, yn gosod unrhyw rif yn yr ystod 00-59. Cyn y colon mewn cromfachau, mae dau batrwm yn gweithio, wedi'u gwahanu gan OR (pibell) resymegol:

• [0-1]ch – unrhyw rif yn yr ystod 00-19
• 2[0-3] – unrhyw rif yn yr ystod 20-23

I'r canlyniad a gafwyd, gallwch hefyd gymhwyso'r swyddogaeth Excel safonol AMSER (TÎM)i'w drosi i fformat amser sy'n ddealladwy i'r rhaglen ac sy'n addas ar gyfer cyfrifiadau pellach.

Gwiriad cyfrinair

Tybiwch fod angen i ni wirio'r rhestr o gyfrineiriau a ddyfeisiwyd gan ddefnyddwyr i sicrhau cywirdeb. Yn ôl ein rheolau, dim ond llythrennau Saesneg (llythrennau bach neu briflythrennau) a rhifau y gall cyfrineiriau eu cynnwys. Ni chaniateir bylchau, tanlinellu na marciau atalnodi eraill.

Gellir trefnu gwirio gan ddefnyddio'r mynegiant rheolaidd syml canlynol:

Mewn gwirionedd, gyda phatrwm o'r fath rydym yn mynnu bod rhwng y dechrau (^) a diwedd ($) dim ond cymeriadau o'r set a roddwyd mewn cromfachau sgwâr oedd yn ein testun. Os oes angen i chi hefyd wirio hyd y cyfrinair (er enghraifft, o leiaf 6 nod), yna'r meintiolydd + gellir ei ddisodli gan yr egwyl “chwech neu fwy” yn y ffurflen {6,}:

Dinas o gyfeiriad

Gadewch i ni ddweud bod angen i ni dynnu'r ddinas o'r bar cyfeiriad. Bydd y rhaglen reolaidd yn helpu, gan dynnu'r testun o “g.” i'r coma nesaf:

Gadewch i ni edrych yn agosach ar y patrwm hwn.

Os ydych chi wedi darllen y testun uchod, yna rydych chi eisoes wedi deall bod gan rai cymeriadau mewn ymadroddion rheolaidd (cyfnodau, sêr, arwyddion doler, ac ati) ystyr arbennig. Os oes angen i chi chwilio am y cymeriadau hyn eu hunain, yna mae slaes o'u blaenau (a elwir weithiau cysgodi). Felly, wrth chwilio am y darn “g.” rhaid i ni ysgrifennu mewn mynegiant rheolaidd Mr os ydym yn chwilio am fantais, yna + ac ati

Mae'r ddau gymeriad nesaf yn ein templed, y dot a'r seren meintiolydd, yn sefyll am unrhyw nifer o unrhyw nodau, hy unrhyw enw dinas.

Mae coma ar ddiwedd y templed, oherwydd rydym yn chwilio am destun o “g.” i goma. Ond gall fod sawl ataln yn y testun, iawn? Nid yn unig ar ôl y ddinas, ond hefyd ar ôl y stryd, tai, ac ati Ar ba un ohonynt y bydd ein cais yn dod i ben? Dyna beth yw pwrpas y marc cwestiwn. Hebddo, byddai ein mynegiant rheolaidd yn tynnu allan y llinyn hiraf posibl:

O ran ymadroddion rheolaidd, mae patrwm o'r fath yn “farus”. I gywiro’r sefyllfa, mae angen marc cwestiwn – mae’n gwneud y meintiolydd ac ar ôl hynny yn sefyll yn “stingy” – ac mae ein hymholiad yn mynd â’r testun hyd at y cownter coma cyntaf ar ôl “g.”:

Enw ffeil o'r llwybr llawn

Sefyllfa gyffredin iawn arall yw tynnu enw'r ffeil o'r llwybr llawn. Bydd mynegiant rheolaidd syml o’r ffurflen yn helpu yma:

Y tric yma yw bod y chwiliad, mewn gwirionedd, yn digwydd i'r cyfeiriad arall - o'r diwedd i'r dechrau, oherwydd ar ddiwedd ein templed mae $, ac rydym yn chwilio am bopeth cyn hynny hyd at yr adlach gyntaf o'r dde. Mae'r slaes yn dianc, fel y dot yn yr enghraifft flaenorol.

PS

“Tua’r diwedd” rwyf am egluro bod yr uchod i gyd yn rhan fach o’r holl bosibiliadau y mae ymadroddion rheolaidd yn eu darparu. Mae yna lawer o gymeriadau a rheolau arbennig ar gyfer eu defnyddio, ac mae llyfrau cyfan wedi'u hysgrifennu ar y pwnc hwn (rwy'n argymell yr un hwn o leiaf i ddechrau). Mewn ffordd, mae ysgrifennu ymadroddion rheolaidd bron yn gelfyddyd. Bron bob amser, gellir gwella neu ategu mynegiant rheolaidd wedi'i ddyfeisio, gan ei wneud yn fwy cain neu'n gallu gweithio gydag ystod ehangach o ddata mewnbwn.

I ddadansoddi a dosrannu ymadroddion rheolaidd pobl eraill neu ddadfygio eich rhai eich hun, mae yna nifer o wasanaethau ar-lein cyfleus: RegEx101, RegExr ac yn fwy

Yn anffodus, nid yw holl nodweddion ymadroddion rheolaidd clasurol yn cael eu cefnogi yn VBA (er enghraifft, chwiliad gwrthdro neu ddosbarthiadau POSIX) a gallant weithio gyda Cyrillic, ond credaf fod yr hyn sydd yno yn ddigon am y tro cyntaf i'ch plesio.

Os nad ydych chi'n newydd i'r pwnc, a bod gennych chi rywbeth i'w rannu, gadewch ymadroddion rheolaidd yn ddefnyddiol wrth weithio yn Excel yn y sylwadau isod. Mae un meddwl yn dda, ond mae dwy esgid yn bâr!

• Amnewid a glanhau testun gyda'r swyddogaeth SUBSTITUTE
• Chwilio ac amlygu cymeriadau Lladin mewn testun
• Chwiliwch am y testun tebyg agosaf (Ivanov = Ivonov = Ivanof, ac ati)