Þjálfa texta með reglulegum segðum (RegExp) í Excel

Eitt af tímafrekustu og pirrandi verkefnum þegar unnið er með texta í Excel er þáttun - flokka alfanumeríska „grautinn“ í íhluti og draga úr honum brotin sem við þurfum. Til dæmis:

• draga út póstnúmerið úr heimilisfanginu (það er gott ef póstnúmerið er alltaf í byrjun, en hvað ef það er það ekki?)
• að finna númer og dagsetningu reiknings út frá lýsingu á greiðslu á bankayfirliti
• útdráttur á TIN úr björtum lýsingum fyrirtækja á mótaðilalista
• leita að bílnúmeri eða vörunúmeri í lýsingu o.fl.
  

Venjulega í slíkum tilfellum, eftir hálftíma af ömurlegum tökum á textanum handvirkt, byrja hugsanir einhvern veginn að koma upp í hugann til að gera þetta ferli sjálfvirkt (sérstaklega ef það er mikið af gögnum). Það eru til nokkrar lausnir og með mismunandi flóknu-skilvirkni:

• Nota innbyggðar Excel textaaðgerðir til að leita að-klippa-líma texta: LEVSIMV (VINSTRI), RIGHT (HÆGRI), PSTR (miðjan), STsEPIT (SAMENGJA) og hliðstæður þess, Sameina (SAMNINGUR), NÁKVÆMLEGA (NÁKVÆMLEGA) o.s.frv. Þessi aðferð er góð ef það er skýr rökfræði í textanum (til dæmis er vísitalan alltaf í upphafi heimilisfangs). Annars verða formúlurnar miklu flóknari og stundum kemur það jafnvel að fylkisformúlum, sem hægir verulega á stórum borðum.
• Notkun eins og textalíkingaroperator frá Visual Basic vafið inn í sérsniðna fjölviaðgerð. Þetta gerir þér kleift að útfæra sveigjanlegri leit með því að nota algildisstafi (*, #,?, osfrv.) Því miður getur þetta tól ekki dregið þann undirstreng sem óskað er eftir úr textanum – athugaðu aðeins hvort hann sé í honum.

Til viðbótar við ofangreint er önnur nálgun sem er mjög vel þekkt í þröngum hringum faglegra forritara, vefhönnuða og annarra tæknimanna - þetta er regluleg orðatiltæki (Regluleg tjáning = RegExp = „regexps“ = „venjulegur“). Einfaldlega sagt, RegExp er tungumál þar sem sérstafir og reglur eru notaðar til að leita að nauðsynlegum undirstrengjum í textanum, draga þá út eða skipta út fyrir annan texta. Regluleg tjáning er mjög öflugt og fallegt tæki sem fer fram úr öllum öðrum vinnubrögðum með texta í stærðargráðu. Mörg forritunarmál (C#, PHP, Perl, JavaScript…) og textaritlar (Word, Notepad++…) styðja reglulegar tjáningar.

Microsoft Excel er því miður ekki með RegExp stuðning beint úr kassanum, en þetta er auðvelt að laga með VBA. Opnaðu Visual Basic Editor frá flipanum verktaki (hönnuður) eða flýtilykla Alt+F11. Settu síðan nýju eininguna inn í gegnum valmyndina Settu inn - Eining og afritaðu texta eftirfarandi makrófalls þangað:

Public Function RegExpExtract(Texti sem strengur, mynstur sem strengur, valfrjálst atriði sem heiltala = 1) Sem strengur við villu GoTo ErrHandl Setja regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (Texti) Setjið síðan matches = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Exit Function End If ErrHandl: RegExpExtract = CVErr(xlErrValue) End Function  

Við getum nú lokað Visual Basic Editor og farið aftur í Excel til að prófa nýja eiginleikann okkar. Setningafræði þess er eftirfarandi:

=RegExpExtract( Txt ; Pattern ; Item )

þar sem

• txt – reit með textanum sem við erum að athuga og sem við viljum draga undirstrenginn sem við þurfum
• mynstur - gríma (mynstur) fyrir undirstrengsleit
• Liður – raðnúmer undirstrengsins sem á að draga út, ef þeir eru nokkrir (ef ekki tilgreint, þá birtist fyrsta tilvikið)

Það áhugaverðasta hér er auðvitað Pattern – sniðmátsstrengur sérstakra „á tungumáli“ RegExp, sem tilgreinir hvað nákvæmlega og hvar við viljum finna. Hér eru þau einföldustu til að koma þér af stað:

Ef við erum að leita að ákveðnum fjölda stafa, til dæmis sex stafa póstnúmeri eða öllum þriggja stafa vörukóðum, þá komum við til bjargar mælitölur or mælitölur eru sérstök orðatiltæki sem tilgreina fjölda stafa sem leita á. Magntölum er beitt á persónuna sem kemur á undan honum:

Nú skulum við halda áfram að áhugaverðasta hlutanum - greining á beitingu skapaðrar falls og hvað við lærðum um mynstur á hagnýtum dæmum úr lífinu.

Að draga tölur úr texta

Til að byrja með skulum við greina einfalt tilfelli - þú þarft að draga fyrstu töluna úr alfanumerískum graut, til dæmis afl truflana aflgjafa úr verðskránni:

Rökfræðin á bak við reglubundna tjáningu er einföld: d þýðir hvaða tölu sem er, og magnmælirinn + segir að fjöldi þeirra ætti að vera einn eða fleiri. Tvöfaldur mínus fyrir framan aðgerðina þarf til að „á flugi“ umbreyta útdregnum stöfum í fulla tölu úr númerinu sem texta.

Póstnúmer

Við fyrstu sýn er allt einfalt hér - við erum að leita að nákvæmlega sex tölustöfum í röð. Við notum sérstakan staf d fyrir tölustaf og magnmæli 6 {} fyrir fjölda stafa:

Hins vegar er sú staða möguleg þegar vinstra megin við vísitöluna í línunni er annað stórt sett af númerum í röð (símanúmer, TIN, bankareikningur osfrv.) Þá mun venjulegt tímabilið okkar draga út fyrstu 6 tölustafir úr því, þ.e. mun ekki virka rétt:

Til að koma í veg fyrir að þetta gerist þurfum við að bæta við breyti um brúnir reglubundinnar tjáningar okkar b táknar lok orðs. Þetta mun gera Excel ljóst að brotið (vísitalan) sem við þurfum ætti að vera sérstakt orð en ekki hluti af öðru broti (símanúmer):

Sími

Vandamálið við að finna símanúmer í textanum er að það eru svo margir möguleikar til að skrifa tölur – með og án bandstrik, í gegnum bil, með eða án svæðisnúmers í sviga o.s.frv. Þess vegna, að mínu mati, er auðveldara að hreinsaðu fyrst alla þessa stafi úr frumtextanum með því að nota nokkrar hreiðrar aðgerðir Varamaður (VARAMAÐUR)þannig að það festist saman í eina heild, og þá með frumstæðum reglulega d{11} draga út 11 tölustafi í röð:

ITN

Þetta er aðeins flóknara hér, því TIN (í okkar landi) getur verið 10 stafa (fyrir lögaðila) eða 12 stafa (fyrir einstaklinga). Ef þú finnur ekki galla sérstaklega, þá er alveg hægt að vera ánægður með venjulegan d{10,12}, en strangt til tekið mun það draga út allar tölur frá 10 til 12 stöfum, þ.e. og ranglega slegið inn 11 tölustafi. Réttara væri að nota tvö mynstur sem tengd eru með rökrænum OR rekstraraðila | (lóðrétt stika):

Vinsamlegast athugaðu að í fyrirspurninni leitum við fyrst að 12 bita tölum og aðeins síðan að 10 bita tölum. Ef við skrifum reglulega tjáningu okkar á hinn veginn, þá mun hún draga út fyrir alla, jafnvel löng 12-bita TIN, aðeins fyrstu 10 stafina. Það er að segja, eftir að fyrsta ástandið hefur verið kallað fram, er frekari sannprófun ekki lengur framkvæmd:

Þetta er grundvallarmunurinn á rekstraraðilanum | frá venjulegu excel rökfræðifalli OR (OR), þar sem endurröðun röksemda breytir ekki niðurstöðunni.

Vöru-SKU

Í mörgum fyrirtækjum er einstökum auðkennum úthlutað á vörur og þjónustu – greinar, SAP kóða, SKU, osfrv. Ef það er rökfræði í nótunum þeirra, þá er auðvelt að draga þau út úr hvaða texta sem er með því að nota reglubundnar segðir. Til dæmis, ef við vitum að greinar okkar samanstanda alltaf af þremur stórum enskum stöfum, bandstrik og síðari þriggja stafa tölu, þá:

Rökfræðin á bak við sniðmátið er einföld. [AZ] – þýðir hvaða hástafir sem er í latneska stafrófinu. Næsti magnari 3 {} segir að það sé mikilvægt fyrir okkur að það séu nákvæmlega þrír slíkir stafir. Á eftir bandstrikinu bíðum við eftir þremur tölustöfum, svo við bætum við í lokin d{3}

Fjárhæðir í reiðufé

Á svipaðan hátt og í fyrri málsgrein er einnig hægt að draga út verð (kostnað, virðisaukaskatt …) úr vörulýsingunni. Ef peningaupphæðir, til dæmis, eru sýndar með bandstrik, þá:

mynstur d með mælikvarða + leitar að hvaða tölu sem er upp að bandstrik, og d{2} mun leita að smáaurum (tveir tölustafir) eftir.

Ef þú þarft ekki að draga út verð, heldur VSK, þá geturðu notað þriðju valfrjálsu rökin RegExpExtract fallsins okkar, sem tilgreinir raðtölu frumefnisins sem á að draga út. Og auðvitað geturðu skipt um aðgerðina Varamaður (VARAMAÐUR) í niðurstöðunum skaltu binda bandstrik við venjulegan aukastafaskil og bæta við tvöföldum mínus í upphafi þannig að Excel túlkar virðisaukaskattinn sem fannst sem eðlilega tölu:

Bílaplötunúmer

Ef þú tekur ekki sérstök farartæki, tengivagna og önnur mótorhjól, þá er staðlað bílnúmer flokkað samkvæmt meginreglunni „bókstafur – þrír tölustafir – tveir bókstafir – svæðisnúmer“. Þar að auki getur svæðisnúmerið verið tveggja eða þriggja stafa tölur og aðeins þeir sem eru svipaðir í útliti og latneska stafrófið eru notaðir sem stafir. Þannig mun eftirfarandi regluleg tjáning hjálpa okkur að draga tölur úr textanum:

tími

Til að draga út tímann á HH:MM sniðinu hentar eftirfarandi regluleg segð:

Eftir ristilbrot [0-5]d, eins og auðvelt er að átta sig á, setur hvaða tölu sem er á bilinu 00-59. Á undan ristlinum innan sviga virka tvö mynstur, aðskilin með rökréttri OR (pípa):

• [0-1]d – hvaða tölu sem er á bilinu 00-19
• 2[0-3] – hvaða tölu sem er á bilinu 20-23

Til að fá niðurstöðuna geturðu einnig beitt venjulegu Excel aðgerðinni TIME (LIÐ)að breyta því í tímasnið sem er skiljanlegt fyrir forritið og hentar til frekari útreikninga.

Skoðaðu lykilorð

Segjum sem svo að við þurfum að athuga listann yfir lykilorð sem notendur hafa fundið upp til að vera rétt. Samkvæmt reglum okkar mega lykilorð aðeins innihalda enska bókstafi (lága eða hástafi) og tölustafi. Bil, undirstrik og önnur greinarmerki eru ekki leyfð.

Athugun er hægt að skipuleggja með því að nota eftirfarandi einfalda reglubundna tjáningu:

Reyndar, með slíku mynstri krefjumst við þess að á milli upphafs (^) og enda ($) í textanum okkar voru aðeins stafir úr settinu sem eru gefin í hornklofa. Ef þú þarft líka að athuga lengd lykilorðsins (til dæmis að minnsta kosti 6 stafir), þá er magn + má skipta út fyrir bilið „sex eða fleiri“ í eyðublaðinu {6,}:

Borg frá heimilisfangi

Segjum að við þurfum að draga borgina frá heimilisfangastikunni. Venjulegt forrit mun hjálpa, draga textann úr "g." í næstu kommu:

Við skulum skoða þetta mynstur nánar.

Ef þú hefur lesið textann hér að ofan, þá hefur þú þegar skilið að sumir stafir í reglulegum orðasamböndum (punktur, stjörnur, dollaramerki osfrv.) hafa sérstaka merkingu. Ef þú þarft að leita að þessum persónum sjálfum, þá er aftursnið á undan þeim (stundum kallað verja). Þess vegna, þegar leitað er að brotinu „g“. við verðum að skrifa í reglulegri tjáningu Mr ef við erum að leita að plús, þá + o.fl.

Næstu tveir stafir í sniðmátinu okkar, punkturinn og magnastjarnan, standa fyrir hvaða fjölda stafa sem er, þ.e. hvaða borgarnöfn sem er.

Það er kommu í lok sniðmátsins, vegna þess að við erum að leita að texta frá "g." í kommu. En það geta verið nokkrar kommur í textanum, ekki satt? Ekki aðeins eftir borginni, heldur einnig eftir götunni, húsum osfrv. Á hvoru þeirra mun beiðni okkar hætta? Til þess er spurningarmerkið. Án þess myndi venjuleg tjáning okkar draga fram lengsta streng sem mögulegt er:

Hvað varðar reglulegar tjáningar er slíkt mynstur „gráðugt“. Til að leiðrétta ástandið þarf spurningarmerki – það gerir mælikvarðann eftir „stunginn“ – og fyrirspurn okkar tekur textann aðeins upp í fyrstu teljarakommuna á eftir „g.“:

Skráarnafn af fullri slóð

Önnur mjög algeng staða er að draga skráarnafnið út úr fullri slóðinni. Einföld regluleg tjáning eyðublaðsins mun hjálpa hér:

Bragðið hér er að leitin fer í raun í gagnstæða átt - frá enda til upphafs, því í lok sniðmátsins okkar er $, og við erum að leita að öllu fyrir það upp að fyrsta bakslagi frá hægri. Afturskástrikið er sleppt, eins og punkturinn í fyrra dæminu.

PS

„Undir lokin“ Ég vil taka það fram að allt ofangreint er lítill hluti af öllum þeim möguleikum sem regluleg orðasambönd veita. Það er fullt af sérstöfum og reglum um notkun þeirra og heilu bækurnar hafa verið skrifaðar um þetta efni (ég mæli allavega með þessari til að byrja með). Að vissu leyti er það að skrifa reglubundnar tjáningar nánast list. Næstum alltaf er hægt að bæta eða bæta við uppfundna reglubundna tjáningu, sem gerir hana glæsilegri eða fær um að vinna með fjölbreyttari inntaksgögnum.

Til að greina og flokka reglubundnar tjáningar annarra eða kemba þínar eigin, eru nokkrar þægilegar netþjónustur: RegEx101, RegExr og meira

Því miður eru ekki allir eiginleikar klassískra reglulegra tjáninga studdir í VBA (til dæmis öfug leit eða POSIX flokkar) og geta virkað með kyrillísku, en ég held að það sem er til sé nóg í fyrsta skipti til að þóknast þér.

Ef þú ert ekki nýr í efninu og hefur eitthvað til að deila, skildu eftir reglulegar tjáningar sem eru gagnlegar þegar þú vinnur í Excel í athugasemdunum hér að neðan. Einn hugur er góður, en tvö stígvél eru par!

• Skipta út og hreinsa upp texta með SUBSTITUTE aðgerðinni
• Leit og auðkenning á latneskum stöfum í texta
• Leitaðu að næsta svipaða texta (Ivanov = Ivonov = Ivanof, osfrv.)