Sanjani mawu ndi mawu okhazikika (RegExp) mu Excel

Imodzi mwantchito zowononga nthawi komanso zokhumudwitsa mukamagwira ntchito ndi zolemba mu Excel ndi kusanthula - kugawa "phala" la alphanumeric kukhala zigawo ndikuchotsa zidutswa zomwe timafunikira. Mwachitsanzo:

• kuchotsa zip code ku adilesi (ndibwino ngati zip code nthawi zonse ili pachiyambi, koma bwanji ngati sichoncho?)
• kupeza nambala ndi tsiku la invoice kuchokera ku malongosoledwe a malipiro mu sitetimenti ya banki
• Kutulutsa kwa TIN kuchokera ku mafotokozedwe a motley amakampani omwe ali pamndandanda wamagulu
• fufuzani nambala yagalimoto kapena nambala yankhani muzofotokozera, ndi zina.
  

Nthawi zambiri, pakatha theka la ola ndikusankha mawu pamanja, malingaliro amayamba kubwera m'maganizo mwanjira ina kuti asinthe izi (makamaka ngati pali zambiri). Pali mayankho angapo komanso mosiyanasiyana movutikira:

• ntchito ntchito zolembedwa mu Excel zomangidwa kufufuza-kudula-mawu: LEVSIMV (Kumanzere), ZABWINO (KUKWERA), Zithunzi za PSTR (pakati), Mtengo wa STsEPIT (CONCATENATE) ndi analogues ake, GWANIZANI (JOINTEXT), CHITSANZO (CHONCHO) etc. Njirayi ndi yabwino ngati pali mfundo zomveka bwino m'malemba (mwachitsanzo, index nthawi zonse imakhala kumayambiriro kwa adiresi). Kupanda kutero, mafomuwa amakhala ovuta kwambiri ndipo, nthawi zina, amafika pamapangidwe angapo, omwe amachepetsa kwambiri pamatebulo akulu.
• kugwiritsa monga wogwiritsa ntchito mawu ofanana kuchokera ku Visual Basic atakulungidwa muzochita zazikulu. Izi zimakulolani kuti mugwiritse ntchito kufufuza kosasinthika pogwiritsa ntchito zilembo zakutchire (*, #,?, etc.) Tsoka ilo, chida ichi sichingatulutse kachidutswa komwe kakufunidwa kuchokera m'malemba - fufuzani ngati chili mmenemo.

Kuphatikiza pa zomwe tafotokozazi, pali njira ina yomwe imadziwika bwino m'magulu ochepera a akatswiri opanga mapulogalamu, opanga mawebusayiti ndi matekinoloje ena - iyi ndi malankhulidwe pafupipafupi (Mawu Okhazikika = RegExp = "regexps" = "okhazikika"). Mwachidule, RegExp ndi chilankhulo chomwe zilembo ndi malamulo apadera amagwiritsidwa ntchito posaka zingwe zofunikira m'mawu, kuzichotsa kapena kuzisintha ndi mawu ena.. Mawu okhazikika ndi chida champhamvu kwambiri komanso chokongola chomwe chimaposa njira zina zonse zogwirira ntchito ndi zolemba ndi dongosolo la ukulu. Zilankhulo zambiri zamapulogalamu (C#, PHP, Perl, JavaScript…) ndi okonza zolemba (Mawu, Notepad++…) amathandizira mawu okhazikika.

Microsoft Excel mwatsoka ilibe thandizo la RegExp kunja kwa bokosi, koma izi zitha kukhazikitsidwa mosavuta ndi VBA. Tsegulani Visual Basic Editor kuchokera pa tabu Woyambitsa (Wolemba Mapulogalamu) kapena njira yachidule ya kiyibodi alt+F11. Kenako ikani gawo latsopano kudzera mu menyu Ikani - Module ndi kukopera zolemba za macro ntchito zotsatirazi:

Public Function RegExpExtract(Text As String, Pattern As String, Optional Item As Integer = 1) Monga String On Error GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Pattern regex.Global = Zoona Ngati regex.Test (Mawu) Kenako Khazikitsani machesi = regex.Execute(Text) RegExpExtract = match.Item(Item - 1) Tulukani Ntchito Yatha Ngati ErrHandl: RegExpExtract = CVERr(xlErrValue) End Function  

Titha tsopano kutseka Visual Basic Editor ndikubwerera ku Excel kuti tiyese mawonekedwe athu atsopano. Syntax yake ndi iyi:

=RegExpExtract(Txt; Chitsanzo; Chinthu)

kumene

• txt - cell yokhala ndi mawu omwe tikuyang'ana ndi komwe tikufuna kuchotsako chingwe chomwe tikufuna
• chitsanzo - chigoba (chitsanzo) pakusaka kwa chingwe chocheperako
• katunduyo - nambala yotsatizana yachingwe chomwe chiyenera kuchotsedwa, ngati pali angapo (ngati sichinatchulidwe, ndiye kuti chochitika choyamba chikuwonetsedwa)

Chinthu chochititsa chidwi kwambiri apa, ndithudi, ndi Chitsanzo - chingwe cha template cha zilembo zapadera "m'chinenero" cha RegExp, chomwe chimalongosola zomwe kwenikweni ndi kumene tikufuna kupeza. Nazi zofunika kwambiri kuti muyambe:

Ngati tikuyang'ana chiwerengero cha zilembo, mwachitsanzo, nambala ya positi ya manambala asanu ndi limodzi kapena zilembo zonse za zilembo zitatu, ndiye kuti tidzapulumutsa. zowonjezera or zowonjezera ndi mawu apadera omwe amafotokoza kuchuluka kwa zilembo zomwe ziyenera kufufuzidwa. Ma quantifiers amagwiritsidwa ntchito pamtundu womwe umabwera patsogolo pake:

Tsopano tiyeni tipitirire ku gawo losangalatsa kwambiri - kusanthula kagwiritsidwe ntchito ka zomwe zidapangidwa ndi zomwe tidaphunzira pamachitidwe pazitsanzo zenizeni za moyo.

Kuchotsa manambala palemba

Poyamba, tiyeni tifufuze nkhani yosavuta - muyenera kuchotsa nambala yoyamba ku phala la alphanumeric, mwachitsanzo, mphamvu yamagetsi osasunthika pamtengo wamtengo wapatali:

Lingaliro la mawu okhazikika ndilosavuta: d amatanthauza nambala iliyonse, ndi quantifier + amanena kuti chiwerengero chawo chiyenera kukhala chimodzi kapena zingapo. Kuchotsa pawiri kutsogolo kwa ntchitoyi kumafunika kuti "pa ntchentche" kutembenuzira zilembo zochotsedwa kukhala nambala yathunthu kuchokera pa chiwerengero-monga malemba.

Positi kodi

Poyang'ana koyamba, zonse ndi zophweka apa - tikuyang'ana manambala asanu ndi limodzi ndendende. Timagwiritsa ntchito khalidwe lapadera d kwa digito ndi quantifier 6 {} za kuchuluka kwa zilembo:

Komabe, zochitika zimatheka pamene, kumanzere kwa ndondomeko mu mzere, pali chiwerengero china chachikulu cha nambala (nambala ya foni, TIN, akaunti yakubanki, ndi zina zotero) Kenako nyengo yathu yokhazikika idzatulutsa 6 yoyamba. manambala kuchokera pamenepo, ie sizigwira ntchito moyenera:

Kuti izi zisachitike, tifunika kuwonjezera chosinthira m'mphepete mwa mawu athu okhazikika b kutanthauza mapeto a mawu. Izi zidzamveketsa bwino kwa Excel kuti chidutswa (chilozera) chomwe tikufunikira chiyenera kukhala mawu osiyana, osati gawo la chidutswa china (nambala ya foni):

Phone

Vuto lopeza nambala yafoni m'malembawo ndikuti pali zosankha zambiri zolembera manambala - ndi opanda ma hyphens, kudzera m'mipata, kapena opanda chigawo code m'mabulaketi, ndi zina zotero. Choncho, mwa lingaliro langa, n'zosavuta Choyamba chotsani zilembo zonsezi kuchokera kutsamba loyambira pogwiritsa ntchito ntchito zingapo zomwe zasungidwa WOLEMBEDWA (MALOWA)kotero kuti zimamatira pamodzi kukhala chinthu chimodzi, ndiyeno ndi zakale wokhazikika d{11} Tumizani manambala 11 motsatana:

ITN

Ndizovuta kwambiri pano, chifukwa TIN (M'dziko Lathu) imatha kukhala manambala 10 (kwa mabungwe ovomerezeka) kapena manambala 12 (kwa anthu pawokha). Ngati simukupeza cholakwika makamaka, ndiye kuti ndizotheka kukhutitsidwa ndi nthawi zonse d{10,12}, koma, kunena mosamalitsa, itulutsa manambala onse kuyambira zilembo 10 mpaka 12, mwachitsanzo, ndikulowetsa manambala 11 molakwika. Kungakhale kolondola kugwiritsa ntchito njira ziwiri zolumikizidwa ndi wogwiritsa ntchito OR womveka | (mizere yowongoka):

Chonde dziwani kuti mufunso timayang'ana manambala a 12-bit, kenako manambala a 10-bit. Ngati tilemba mawu athu anthawi zonse mozungulira, ndiye kuti amakokera aliyense, ngakhale ma TIN aatali a 12-bit, zilembo 10 zokha. Ndiye kuti, chikhalidwe choyamba chikayambika, kutsimikizira kwina sikukuchitikanso:

Uku ndiye kusiyana kwakukulu pakati pa wogwiritsa ntchito | kuchokera ku standard excel logic function OR (KAPENA), pamene kukonzanso mikangano sikusintha zotsatira.

Zogulitsa zama SKU

M'makampani ambiri, zizindikiro zapadera zimaperekedwa ku katundu ndi mautumiki - zolemba, ma SAP codes, SKUs, ndi zina zotero. Ngati pali logic muzolemba zawo, ndiye kuti akhoza kutulutsidwa mosavuta palemba lililonse pogwiritsa ntchito mawu okhazikika. Mwachitsanzo, ngati tikudziwa kuti zolemba zathu nthawi zonse zimakhala ndi zilembo zazikulu zitatu zachingerezi, hyphen ndi nambala ya manambala atatu, ndiye:

Malingaliro kumbuyo kwa template ndi osavuta. [AZ] - amatanthauza zilembo zazikulu zilizonse za zilembo zachilatini. Chowerengera chotsatira 3 {} akuti m'pofunika kwa ife kuti pali zilembo zitatu zoterozo. Pambuyo pa hyphen, tikudikirira manambala atatu, kotero timawonjezera kumapeto d{3}

Ndalama zandalama

Momwemonso ndime yapitayi, mutha kutulutsanso mitengo (mitengo, VAT ...) kuchokera pakufotokozera kwazinthu. Ngati ndalama, mwachitsanzo, zikuwonetsedwa ndi hyphen, ndiye:

chitsanzo d ndi quantifier + amafufuza nambala iliyonse mpaka hyphen, ndi d{2} adzayang'ana ndalama (ma manambala awiri) pambuyo pake.

Ngati mukufuna kuchotsa mitengo, koma VAT, ndiye kuti mutha kugwiritsa ntchito mfundo yachitatu yosankhira ntchito yathu ya RegExpExtract, yomwe imatchula nambala ya chinthu chomwe chiyenera kuchotsedwa. Ndipo, ndithudi, mukhoza m'malo ntchito WOLEMBEDWA (MALOWA) muzotsatira, lowetsani ku cholekanitsa cha decimal ndikuwonjezera kuchotsera kawiri koyambirira kuti Excel imatanthauzire VAT yomwe yapezeka ngati nambala yabwinobwino:

Nambala za mbale zagalimoto

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:

Time

Kuti muchotse nthawi mumtundu wa HH:MM, mawu otsatirawa ndioyenera:

Pambuyo pa gawo la colon [0-5]d, monga ndizosavuta kuzizindikira, zimayika nambala iliyonse mumtundu wa 00-59. Pamaso pa m'matumbo m'makolo, njira ziwiri zimagwira ntchito, zolekanitsidwa ndi zomveka OR (chitoliro):

• [0-1]d - nambala iliyonse mumitundu 00-19
• 2 [0-3] - nambala iliyonse mumitundu 20-23

Pazotsatira zomwe mwapeza, mutha kugwiritsanso ntchito muyezo wa Excel TIME (TEAM)kuti musinthe kukhala mtundu wa nthawi womwe umamveka ku pulogalamuyo komanso yoyenera kuwerengera kwina.

fufuzani mawu achinsinsi

Tiyerekeze kuti tikuyenera kuyang'ana mndandanda wa mawu achinsinsi opangidwa ndi ogwiritsa ntchito kuti ndiwolondola. Malinga ndi malamulo athu, mapasiwedi amatha kukhala ndi zilembo zachingerezi (zochepa kapena zazikulu) ndi manambala. Mipata, pansi ndi zizindikiro zina zopumira ndizosaloledwa.

Kuwunika kumatha kukonzedwa pogwiritsa ntchito mawu osavuta awa:

M'malo mwake, ndi dongosolo lotere timafunikira kuti pakati pa chiyambi (^) ndi kumaliza ($) m'mawu athu munali zilembo zokha kuchokera pagulu lomwe laperekedwa m'mabulaketi apakati. Ngati mukufunanso kuyang'ana kutalika kwa mawu achinsinsi (mwachitsanzo, zilembo 6 zosachepera), ndiye quantifier + akhoza kusinthidwa ndi nthawi "zisanu ndi chimodzi kapena kuposerapo" mu mawonekedwe $ 6,}:

Mzinda kuchokera ku adilesi

Tiyerekeze kuti tikufunika kukokera mzindawu kuchokera pa adilesi. Pulogalamu yokhazikika ithandizira, kuchotsa mawu kuchokera ku "g." mpaka kumakoma otsatirawa:

Tiyeni tione bwinobwino chitsanzo ichi.

Ngati mwawerenga zomwe zili pamwambapa, ndiye kuti mwamvetsetsa kale kuti zilembo zina m'mawu okhazikika (nthawi, nyenyezi, zizindikiro za dollar, ndi zina zotero) zili ndi tanthauzo lapadera. Ngati muyenera kuyang'ana otchulidwawo okha, ndiye kuti amatsogozedwa ndi kubwerera kumbuyo (nthawi zina amatchedwa kutchinjiriza). Chifukwa chake, pofufuza kachidutswa "g". tiyenera kulemba mwachizolowezi Bambo. ngati tikufuna kuwonjezera, ndiye + etc.

Zilembo ziwiri zotsatira mu template yathu, dontho ndi quantifier asterisk, zimayimira nambala iliyonse ya zilembo, mwachitsanzo, dzina la mzinda uliwonse.

Pamapeto pa template pali koma, chifukwa tikuyang'ana mawu kuchokera ku "g." ku koma. Koma pakhoza kukhala makoma angapo m'mawu, sichoncho? Osati pambuyo pa mzinda, komanso pambuyo pa msewu, nyumba, ndi zina zotero. Kodi pempho lathu lidzayima pati? Ndicho chimene chizindikiro cha funso ndi chake. Popanda izi, mawu athu okhazikika amatha kutulutsa chingwe chachitali kwambiri:

Ponena za mawu okhazikika, chitsanzo choterocho ndi "umbombo". Kuti tikonze vutoli, chizindikiro chimafunika - chimapangitsa chowerengera kenako n'kuyimira "chotopetsa" - ndipo funso lathu limatengera mawuwo mpaka pa comma yoyamba pambuyo pa "g.":

Filename kuchokera panjira yonse

Chinthu china chodziwika bwino ndikuchotsa dzina la fayilo panjira yonse. Kufotokozera mwachidule kwa fomu kungathandize apa:

Chinyengo apa ndikuti kusaka, kwenikweni, kumachitika mbali ina - kuyambira kumapeto mpaka koyambirira, chifukwa kumapeto kwa template yathu. $, ndipo tikuyang'ana chilichonse chisanachitike mpaka kubwereranso koyamba kuchokera kumanja. Kubwerera m'mbuyo kwathawa, monga dontho mu chitsanzo chapitacho.

PS

"Kumapeto" Ndikufuna kufotokozera kuti zonse zomwe zili pamwambazi ndi gawo laling'ono la zotheka zomwe mawu okhazikika amapereka. Pali otchulidwa ambiri apadera ndi malamulo oti agwiritse ntchito, ndipo mabuku athunthu alembedwa pamutuwu (ndikupangira izi poyambira). Mwanjira ina, kulemba mawu okhazikika ndi pafupifupi luso. Pafupifupi nthawi zonse, mawu opangidwa nthawi zonse amatha kuwongoleredwa kapena kuwonjezeredwa, kupangitsa kuti ikhale yokongola kwambiri kapena yotha kugwira ntchito ndi mitundu yambiri yazolowera.

Kuti muwunike ndikuwunika zomwe anthu amalankhula nthawi zonse kapena kukonza zanu, pali mautumiki angapo osavuta pa intaneti: RegEx101, Malangizo Ndi zina

Tsoka ilo, sizinthu zonse zamawu okhazikika omwe amathandizidwa mu VBA (mwachitsanzo, kusaka mosintha kapena makalasi a POSIX) ndipo amatha kugwira ntchito ndi Cyrillic, koma ndikuganiza kuti zomwe zilipo ndizokwanira koyamba kukusangalatsani.

Ngati simunayambepopo pamutuwu, ndipo muli ndi zomwe mungagawire, siyani mawu okhazikika pogwira ntchito ku Excel mu ndemanga pansipa. Malingaliro amodzi ndi abwino, koma nsapato ziwiri ndi ziwiri!

• Kusintha ndi kukonza malemba ndi ntchito ya SUBSTITUTE
• Search and highlighting of Latin characters in text
• Sakani zolemba zofananira zapafupi (Ivanov = Ivonov = Ivanof, etc.)