Parse test b'espressjonijiet regolari (RegExp) f'Excel

Waħda mill-aktar kompiti li jieħdu ħafna ħin u frustranti meta taħdem mat-test f'Excel hija parsing – parsing tal-"porridge" alfanumeriku f'komponenti u estrazzjoni tal-frammenti li neħtieġu minnu. Pereżempju:

• estrazzjoni tal-kodiċi zip mill-indirizz (huwa tajjeb jekk il-kodiċi zip ikun dejjem fil-bidu, imma x'jiġri jekk ma jkunx?)
• issib in-numru u d-data tal-fattura mid-deskrizzjoni tal-ħlas fir-rendikont bankarju
• estrazzjoni ta 'TIN minn deskrizzjonijiet differenti ta' kumpaniji fil-lista ta 'kontropartijiet
• fittex numru tal-karozza jew numru tal-artiklu fid-deskrizzjoni, eċċ.
  

Normalment f'każijiet bħal dawn, wara nofs siegħa ta 'picking dreary fit-test manwalment, il-ħsibijiet jibdew jiġu f'moħħna b'xi mod biex awtomat dan il-proċess (speċjalment jekk ikun hemm ħafna dejta). Hemm diversi soluzzjonijiet u bi gradi differenti ta' kumplessità-effiċjenza:

• użu funzjonijiet tat-test Excel integrati biex tfittex test-cut-kolla: LEVSIMV (XELLUG), DRITT (DRITT), PSTR (nofs), STsEPIT (KONCATENA) u l-analogi tagħha, KOMBINA (TEST KONĠUNT), EXACT (EŻATT) eċċ Dan il-metodu huwa tajjeb jekk ikun hemm loġika ċara fit-test (per eżempju, l-indiċi jkun dejjem fil-bidu tal-indirizz). Inkella, il-formuli jsiru ħafna aktar ikkumplikati u, xi drabi, anke jiġu għal formuli ta 'firxa, li jonqos ħafna fuq tabelli kbar.
• Bl-użu bħal operatur ta' xebh tat-test minn Visual Basic imgeżwer f'funzjoni makro personalizzata. Dan jippermettilek li timplimenta tfittxija aktar flessibbli billi tuża karattri wildcard (*, #,?, eċċ.) Sfortunatament, din l-għodda ma tistax tiġbed is-substring mixtieqa mit-test - iċċekkja biss jekk tinsabx fiha.

Minbarra dan ta 'hawn fuq, hemm approċċ ieħor li huwa magħruf ħafna f'ċrieki dojoq ta' programmaturi professjonali, żviluppaturi tal-web u techies oħra - dan huwa espressjonijiet regolari (Espressjonijiet Regolari = RegExp = “regexps” = “regolari”). Fi kliem sempliċi, RegExp hija lingwa fejn jintużaw karattri u regoli speċjali biex ifittxu s-substrings meħtieġa fit-test, jiġu estratti jew jissostitwixxuhom b'test ieħor.. L-espressjonijiet regolari huma għodda qawwija ħafna u sabiħa li taqbeż il-modi l-oħra kollha ta 'ħidma mat-test b'ordni ta' kobor. Ħafna lingwi ta' programmar (C#, PHP, Perl, JavaScript...) u edituri tat-test (Word, Notepad++...) jappoġġjaw espressjonijiet regolari.

Microsoft Excel sfortunatament m'għandux appoġġ RegExp barra mill-kaxxa, iżda dan jista 'jiġi ffissat faċilment b'VBA. Iftaħ l-Editur Visual Basic mit-tab iżviluppatur (Żviluppatur) jew shortcut tat-tastiera Alt+F11. Imbagħad daħħal il-modulu l-ġdid permezz tal-menu Daħħal – Modulu u kkopja t-test tal-funzjoni makro li ġejja hemmhekk:

Funzjoni Pubblika RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) As String On Error GoTo ErrHandl Issettja regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = Veru Jekk regex.Test (Test) Imbagħad Issettja logħbiet = regex.Eżegwixxi (Text) RegExpExtract = matchs.Item (Oġġett - 1) Ħruġ Funzjoni Tmiem Jekk ErrHandl: RegExpExtract = CVErr (xlErrValue) Tmiem Funzjoni  

Issa nistgħu nagħlqu l-Editur Visual Basic u nerġgħu lura għal Excel biex nippruvaw il-karatteristika l-ġdida tagħna. Is-sintassi tagħha hija din li ġejja:

=RegExpExtract(Txt; Mudell; Oġġett)

fejn

• txt – ċellula bit-test li qed niċċekkjaw u li minnha rridu nġibu s-substring li għandna bżonn
• mudell – maskra (mudell) għat-tfittxija tas-substring
• Punt – in-numru tas-sekwenza tas-substring li għandu jiġi estratt, jekk ikun hemm diversi minnhom (jekk mhux speċifikat, allura l-ewwel okkorrenza hija murija)

L-aktar ħaġa interessanti hawnhekk, ovvjament, hija Pattern - mudell ta 'sekwenza ta' karattri speċjali "fil-lingwa" ta 'RegExp, li jispeċifika x'eżatt u fejn irridu nsibu. Hawn huma l-aktar bażiċi biex tibda:

Jekk qed infittxu ċertu numru ta 'karattri, pereżempju, kodiċi postali b'sitt ċifri jew il-kodiċijiet kollha tal-prodott bi tliet ittri, allura niġu għas-salvataġġ kwantifikaturi or kwantifikaturi huma espressjonijiet speċjali li jispeċifikaw in-numru ta' karattri li jridu jiġu mfittxija. Il-kwantifikaturi huma applikati għall-karattru li jiġi qabel:

Issa ejja ngħaddu għall-aktar parti interessanti – analiżi tal-applikazzjoni tal-funzjoni maħluqa u dak li tgħallimna dwar mudelli fuq eżempji prattiċi mill-ħajja.

Estrazzjoni ta 'numri mit-test

Biex tibda, ejja janalizzaw każ sempliċi - għandek bżonn tiġbed l-ewwel numru mill-porridge alfanumeriku, pereżempju, il-qawwa ta 'provvisti ta' enerġija mingħajr interruzzjoni mil-lista tal-prezzijiet:

Il-loġika wara l-espressjoni regolari hija sempliċi: d tfisser kwalunkwe ċifra, u l-kwantifikatur + jgħid li n-numru tagħhom għandu jkun wieħed jew aktar. Il-minus doppju quddiem il-funzjoni huwa meħtieġ biex "fuq it-titjir" jikkonverti l-karattri estratti f'numru sħiħ min-numru bħala test.

Kodiċi postali

L-ewwel daqqa t'għajn, kollox huwa sempliċi hawn - qed infittxu eżattament sitt ċifri wara xulxin. Aħna nużaw karattru speċjali d għal ċifra u kwantifikatur 6 {} għan-numru ta' karattri:

Madankollu, sitwazzjoni hija possibbli meta, fuq ix-xellug tal-indiċi fil-linja, hemm sett kbir ieħor ta 'numri wara xulxin (numru tat-telefon, TIN, kont bankarju, eċċ.) Imbagħad l-istaġun regolari tagħna se jiġbed l-ewwel 6. ċifri minnha, jiġifieri mhux se taħdem b'mod korrett:

Biex ma jseħħx dan, irridu nżidu modifikatur madwar it-truf tal-espressjoni regolari tagħna b li jfisser it-tmiem ta’ kelma. Dan jagħmilha ċara lil Excel li l-framment (indiċi) li neħtieġu għandu jkun kelma separata, u mhux parti minn framment ieħor (numru tat-telefon):

phone

Il-problema biex issib numru tat-telefon fit-test hija li hemm ħafna għażliet għall-kitba tan-numri - bi u mingħajr sings, permezz ta 'spazji, bi jew mingħajr kodiċi tar-reġjun fil-parentesi, eċċ. Għalhekk, fl-opinjoni tiegħi, huwa aktar faċli li l-ewwel naddaf dawn il-karattri kollha mit-test sors billi tuża diversi funzjonijiet nested SOSTITUT (SOSTITUT)sabiex twaħħal flimkien f'wieħed sħiħ, u mbagħad ma 'regolari primittiv d{11} iġbed 11-il ċifra wara xulxin:

ITN

Hawn xi ftit iktar ikkumplikat, għax it-TIN (f’Pajjiżna) jista’ jkun ta’ 10 ċifri (għall-entitajiet legali) jew ta’ 12-il ċifri (għall-individwi). Jekk ma ssibx tort speċjalment, allura huwa pjuttost possibbli li tkun sodisfatt bir-regolari d{10,12}, iżda, strettament, se tiġbed in-numri kollha minn 10 sa 12-il karattru, jiġifieri u daħħal 11-il ċifra bi żball. Ikun aktar korrett li jintużaw żewġ mudelli konnessi minn operatur OR loġiku | (bar vertikali):

Jekk jogħġbok innota li fil-mistoqsija l-ewwel infittxu numri 12-bit, u mbagħad biss għal numri 10-bit. Jekk niktbu l-espressjoni regolari tagħna bil-maqlub, allura se tiġbed għal kulħadd, anke TINs twal 12-bit, biss l-ewwel 10 karattri. Jiġifieri, wara li tiġi attivata l-ewwel kundizzjoni, ma tibqax issir aktar verifika:

Din hija d-differenza fundamentali bejn l-operatur | minn funzjoni loġika standard excel OR (JEW), fejn l-arranġament mill-ġdid tal-argumenti ma jbiddilx ir-riżultat.

SKUs tal-Prodott

F'ħafna kumpaniji, identifikaturi uniċi huma assenjati għal oġġetti u servizzi - artikoli, kodiċi SAP, SKUs, eċċ. Jekk ikun hemm loġika fin-notazzjoni tagħhom, allura jistgħu jinġibdu faċilment minn kwalunkwe test bl-użu ta 'espressjonijiet regolari. Pereżempju, jekk nafu li l-artikoli tagħna dejjem jikkonsistu fi tliet ittri kapitali bl-Ingliż, sing u numru sussegwenti bi tliet ċifri, allura:

Il-loġika wara l-mudell hija sempliċi. [AZ] – tfisser kwalunkwe ittra kapitali tal-alfabett Latin. Il-kwantifikatur li jmiss 3 {} jgħid li huwa importanti għalina li hemm eżattament tliet ittri bħal dawn. Wara s-sing, qed nistennew tliet ċifri, għalhekk inżidu fl-aħħar d{3}

Ammonti fi flus

B'mod simili għall-paragrafu preċedenti, tista' wkoll tiġbed il-prezzijiet (spejjeż, VAT ...) mid-deskrizzjoni tal-merkanzija. Jekk l-ammonti monetarji, pereżempju, huma indikati b'sing, allura:

Pattern d bil-kwantifikatur + tfittxijiet għal kwalunkwe numru sa sing, u d{2} se tfittex pennies (żewġ ċifri) wara.

Jekk għandek bżonn tiġbed mhux il-prezzijiet, iżda l-VAT, allura tista 'tuża t-tielet argument fakultattiv tal-funzjoni RegExpExtract tagħna, li tispeċifika n-numru ordinali tal-element li għandu jiġi estratt. U, ovvjament, tista 'tissostitwixxi l-funzjoni SOSTITUT (SOSTITUT) fir-riżultati, sing mas-separatur deċimali standard u żid minus doppju fil-bidu sabiex Excel jinterpreta l-VAT misjuba bħala numru normali:

Numri tal-pjanċa tal-karozzi

Jekk ma tieħux vetturi speċjali, karrijiet u muturi oħra, allura n-numru standard tal-karozza jiġi analizzat skond il-prinċipju "ittra - tliet numri - żewġ ittri - kodiċi tar-reġjun". Barra minn hekk, il-kodiċi tar-reġjun jista 'jkun ta' 2 jew 3 ċifri, u dawk biss li huma simili fid-dehra għall-alfabett Latin huma użati bħala ittri. Għalhekk, l-espressjoni regolari li ġejja tgħinna nġibu n-numri mit-test:

ħin

Biex tiġi estratta l-ħin fil-format HH:MM, l-espressjoni regolari li ġejja hija adattata:

Wara framment tal-kolon [0-5]d, peress li huwa faċli biex insemmu, jistabbilixxi kwalunkwe numru fil-medda 00-59. Qabel il-kolon fil-parentesi, jaħdmu żewġ mudelli, separati minn OR loġiku (pajp):

• [0-1]d – kwalunkwe numru fil-medda 00-19
• 2[0-3] – kwalunkwe numru fil-medda 20-23

Għar-riżultat miksub, tista 'addizzjonalment tapplika l-funzjoni standard Excel ĦIN (TIM)biex jaqilbu f'format ta 'ħin li jinftiehem mill-programm u adattat għal aktar kalkoli.

Iċċekkja l-password

Ejja ngħidu li għandna bżonn niċċekkjaw il-lista ta 'passwords ivvintati mill-utenti għall-korrettezza. Skont ir-regoli tagħna, il-passwords jista' jkun fihom biss ittri bl-Ingliż (ittri żgħar jew kbar) u numri. L-ispazji, is-sottolinja u marki oħra ta' punteġġjatura mhumiex permessi.

L-iċċekkjar jista’ jiġi organizzat bl-użu tal-espressjoni regolari sempliċi li ġejja:

Fil-fatt, b'tali mudell neħtieġu li bejn il-bidu (^) u tmiem ($) fit-test tagħna kien hemm biss karattri mis-sett mogħti fil-parentesi kwadri. Jekk għandek bżonn ukoll tiċċekkja t-tul tal-password (per eżempju, mill-inqas 6 karattri), allura l-kwantifikatur + jistgħu jiġu sostitwiti bl-intervall "sitta jew aktar" fil-formola {6,}:

Belt mill-indirizz

Ejja ngħidu li għandna bżonn iġbed il-belt mill-bar tal-indirizz. Il-programm regolari se jgħin, jiġbed it-test minn "g." għall-virgola li jmiss:

Ejja nagħtu ħarsa aktar mill-qrib lejn dan il-mudell.

Jekk qrajt it-test ta 'hawn fuq, allura diġà fhimt li xi karattri f'espressjonijiet regolari (perijodi, asterisks, sinjali tad-dollaru, eċċ.) għandhom tifsira speċjali. Jekk għandek bżonn tfittex dawn il-karattri huma stess, imbagħad ikunu preċeduti minn backslash (xi kultant imsejħa shielding). Għalhekk, meta tkun qed tfittex il-framment “g.” irridu niktbu b’espressjoni regolari Mr jekk qed infittxu plus, allura + eċċ.

Iż-żewġ karattri li jmiss fil-mudell tagħna, it-tikka u l-asterisk tal-kwantifikatur, huma għal kull numru ta' kwalunkwe karattri, jiġifieri kwalunkwe isem ta' belt.

Hemm virgola fl-aħħar tal-mudell, għax qed infittxu test minn "g." għal virgola. Imma jista’ jkun hemm diversi virgoli fit-test, hux? Mhux biss wara l-belt, imma wkoll wara t-triq, djar, eċċ. Fuq liema minnhom se tieqaf it-talba tagħna? Għal dan huwa l-punt tal-mistoqsija. Mingħajrha, l-espressjoni regolari tagħna tiġbed l-itwal string possibbli:

F'termini ta 'espressjonijiet regolari, mudell bħal dan huwa "greedy". Biex tikkoreġi s-sitwazzjoni, hemm bżonn ta' marka ta' mistoqsija - tagħmel il-kwantifikatur li warajh ikun "tagħna" - u l-mistoqsija tagħna tieħu t-test biss sal-ewwel counter virgola wara "g.":

Isem tal-fajl mill-mogħdija sħiħa

Sitwazzjoni oħra komuni ħafna hija li jiġi estratt l-isem tal-fajl mill-mogħdija sħiħa. Espressjoni regolari sempliċi tal-formola tgħin hawn:

Il-trick hawnhekk huwa li t-tfittxija, fil-fatt, isseħħ fid-direzzjoni opposta - mill-aħħar sal-bidu, għaliex fl-aħħar tal-mudell tagħna huwa $, u qed infittxu kollox qabel sa l-ewwel backslash mil-lemin. Il-backslash huwa maħruba, bħall-tikka fl-eżempju preċedenti.

PS

"Lejn l-aħħar" Irrid niċċara li dak kollu ta 'hawn fuq huwa parti żgħira mill-possibbiltajiet kollha li jipprovdu espressjonijiet regolari. Hemm ħafna karattri speċjali u regoli għall-użu tagħhom, u kotba sħaħ inkitbu fuq dan is-suġġett (nirrakkomanda tal-inqas dan wieħed għall-bidu). B'xi mod, il-kitba ta 'espressjonijiet regolari hija kważi arti. Kważi dejjem, espressjoni regolari ivvintata tista 'tiġi mtejba jew supplimentata, u tagħmilha aktar eleganti jew kapaċi taħdem ma' firxa usa 'ta' data input.

Biex tanalizza u teżamina l-espressjonijiet regolari ta’ nies oħrajn jew tiddibaggja tiegħek, hemm diversi servizzi konvenjenti online: RegEx101, RegExr u aktar

Sfortunatament, mhux il-karatteristiċi kollha ta 'espressjonijiet regolari klassiċi huma appoġġjati f'VBA (per eżempju, tfittxija b'lura jew klassijiet POSIX) u jistgħu jaħdmu ma' Cyrillic, iżda naħseb li dak li hemm huwa biżżejjed għall-ewwel darba biex jekk jogħġbok.

Jekk m'intix ġdid fis-suġġett, u għandek xi ħaġa x'taqsam, ħalli espressjonijiet regolari utli meta taħdem f'Excel fil-kummenti hawn taħt. Moħħ wieħed huwa tajjeb, iżda żewġ stivali huma par!

• Sostituzzjoni u tindif tat-test bil-funzjoni SOSTITUT
• Tiftix u enfasi ta' karattri Latini fit-test
• Fittex l-eqreb test simili (Ivanov = Ivonov = Ivanof, eċċ.)