Changanua maandishi kwa maneno ya kawaida (RegExp) katika Excel

Moja ya kazi zinazotumia wakati mwingi na za kukatisha tamaa wakati wa kufanya kazi na maandishi katika Excel ni kupitisha - kuchanganua "uji" wa alphanumeric katika vipengele na kutoa vipande tunavyohitaji kutoka kwake. Kwa mfano:

• kutoa msimbo wa zip kutoka kwa anwani (ni vizuri ikiwa nambari ya zip iko mwanzoni kila wakati, lakini vipi ikiwa sivyo?)
• kutafuta nambari na tarehe ya ankara kutoka kwa maelezo ya malipo katika taarifa ya benki
• uchimbaji wa TIN kutoka kwa maelezo ya motley ya makampuni katika orodha ya wenzao
• tafuta nambari ya gari au nambari ya kifungu katika maelezo, nk.
  

Kawaida katika hali kama hizi, baada ya nusu saa ya kuokota kwa maandishi kwa mikono, mawazo huanza kukumbuka kwa njia fulani ili kurekebisha mchakato huu (haswa ikiwa kuna data nyingi). Kuna suluhisho kadhaa na kwa viwango tofauti vya ugumu-ufanisi:

• Kutumia kazi za maandishi za Excel zilizojengwa ndani kutafuta-kata-gundi maandishi: LEVSIMV (KUSHOTO), HAKI (HAKI), PSTR (katikati), STsEPIT (CONCATENATE) na analogi zake, BONYEZA (JOINTEXT), SURA (HASA) nk Njia hii ni nzuri ikiwa kuna mantiki wazi katika maandishi (kwa mfano, index daima ni mwanzo wa anwani). Vinginevyo, fomula zinakuwa ngumu zaidi na, wakati mwingine, hata huja kwa fomula za safu, ambazo hupunguza sana meza kubwa.
• Kutumia kama opereta wa kufanana kwa maandishi kutoka kwa Visual Basic iliyofunikwa kwa kitendaji maalum cha jumla. Hii inakuwezesha kutekeleza utafutaji unaonyumbulika zaidi kwa kutumia vibambo vya wildcard (*, #,?, nk.) Kwa bahati mbaya, zana hii haiwezi kutoa kamba ndogo inayotaka kutoka kwa maandishi - angalia tu ikiwa iko ndani yake.

Mbali na hayo hapo juu, kuna mbinu nyingine ambayo inajulikana sana katika duru nyembamba za waandaaji wa programu za kitaaluma, watengenezaji wa mtandao na teknolojia nyingine - hii ni. maneno ya kawaida (Maelezo ya Kawaida = RegExp = "regexps" = "regulars"). Kwa ufupi, RegExp ni lugha ambayo herufi na sheria maalum hutumiwa kutafuta vifungu vidogo vinavyohitajika katika maandishi, kuzitoa au kuzibadilisha na maandishi mengine.. Maneno ya kawaida ni zana yenye nguvu sana na nzuri ambayo inapita njia zingine zote za kufanya kazi na maandishi kwa mpangilio wa ukubwa. Lugha nyingi za programu (C#, PHP, Perl, JavaScript…) na vihariri vya maandishi (Neno, Notepad++…) vinaunga mkono usemi wa kawaida.

Microsoft Excel kwa bahati mbaya haina usaidizi wa RegExp nje ya boksi, lakini hii inaweza kusasishwa kwa urahisi na VBA. Fungua Kihariri cha Msingi cha Visual kutoka kwa kichupo developer (Msanidi programu) au njia ya mkato ya kibodi Alt+F11. Kisha ingiza moduli mpya kupitia menyu Ingiza - Moduli na unakili maandishi ya kazi ifuatayo ya jumla hapo:

Kazi ya Umma ya RegExpExtract(Nakala Kama Kamba, Mchoro Kama Mfuatano, Kipengee cha Hiari Kama Nambari kamili = 1) Kama Mfuatano Kwenye Hitilafu Nenda kwa ErrHandl Weka regex = CreateObject("VBScript.RegExp") regex.Pattern = Muundo regex.Global = Kweli Ikiwa regex.Test (Nakala) Kisha Weka zinazolingana = regex.Execute(Text) RegExpExtract = mechi.Kipengee(Kipengee - 1) Toka Mwisho wa Kutenda Kama Hitilafu: RegExpExtract = CVERr(xlErrValue) Kazi ya Mwisho  

Sasa tunaweza kufunga Kihariri cha Msingi cha Visual na kurudi kwa Excel ili kujaribu kipengele chetu kipya. Syntax yake ni kama ifuatavyo:

=RegExpExtract( Txt ; Muundo ; Bidhaa )

ambapo

• txt - seli iliyo na maandishi ambayo tunakagua na ambayo tunataka kutoa kamba ndogo tunayohitaji
• mfano - kinyago (muundo) kwa utafutaji wa kamba ndogo
• Item - nambari ya mlolongo wa kamba ndogo ambayo itatolewa, ikiwa kuna kadhaa (ikiwa haijabainishwa, tukio la kwanza linaonyeshwa)

Jambo la kuvutia zaidi hapa, bila shaka, ni Muundo - kamba ya template ya wahusika maalum "katika lugha" ya RegExp, ambayo inabainisha nini hasa na wapi tunataka kupata. Hapa kuna zile za msingi zaidi za kukufanya uanze:

Ikiwa tunatafuta idadi fulani ya wahusika, kwa mfano, nambari ya posta yenye tarakimu sita au nambari zote za bidhaa zenye herufi tatu, basi tunakuja kuwaokoa. viambishi or viambishi ni misemo maalum inayobainisha idadi ya herufi za kutafutwa. Vipimo vinatumika kwa herufi inayokuja mbele yake:

Sasa hebu tuendelee kwenye sehemu ya kuvutia zaidi - uchambuzi wa matumizi ya kazi iliyoundwa na kile tulichojifunza kuhusu mifumo kwenye mifano ya vitendo kutoka kwa maisha.

Kutoa nambari kutoka kwa maandishi

Kuanza, hebu tuchambue kesi rahisi - unahitaji kutoa nambari ya kwanza kutoka kwa uji wa alphanumeric, kwa mfano, nguvu ya vifaa vya umeme visivyoweza kuingiliwa kutoka kwa orodha ya bei:

Mantiki nyuma ya usemi wa kawaida ni rahisi: d ina maana tarakimu yoyote, na quantifier + inasema kwamba idadi yao inapaswa kuwa moja au zaidi. Minus mbili mbele ya chaguo za kukokotoa inahitajika ili "kurusha" kubadilisha herufi zilizotolewa kuwa nambari kamili kutoka kwa nambari-kama-maandishi.

Postcode

Kwa mtazamo wa kwanza, kila kitu ni rahisi hapa - tunatafuta tarakimu sita mfululizo. Tunatumia tabia maalum d kwa tarakimu na quantifier 6 {} kwa idadi ya wahusika:

Hata hivyo, hali inawezekana wakati, upande wa kushoto wa index katika mstari, kuna seti nyingine kubwa ya nambari mfululizo (nambari ya simu, TIN, akaunti ya benki, nk) Kisha msimu wetu wa kawaida utatoa 6 za kwanza za kwanza. tarakimu kutoka kwayo, yaani haitafanya kazi ipasavyo:

Ili kuzuia hili kutokea, tunahitaji kuongeza kirekebishaji karibu na kingo za usemi wetu wa kawaida b kuashiria mwisho wa neno. Hii itafanya iwe wazi kwa Excel kwamba kipande (faharisi) tunachohitaji kinapaswa kuwa neno tofauti, na sio sehemu ya kipande kingine (nambari ya simu):

Namba ya simu

Shida ya kupata nambari ya simu katika maandishi ni kwamba kuna chaguzi nyingi za kuandika nambari - na na bila hyphens, kupitia nafasi, na au bila msimbo wa mkoa kwenye mabano, nk. Kwa hivyo, kwa maoni yangu, ni rahisi zaidi. kwanza safisha herufi hizi zote kutoka kwa maandishi chanzo kwa kutumia vitendaji kadhaa vilivyowekwa MBADALA (BADALA)ili ishikamane kuwa nzima moja, na kisha kwa kawaida ya zamani d{11} toa tarakimu 11 mfululizo:

ITN

Hapa ni ngumu zaidi, kwa sababu TIN (katika Nchi Yetu) inaweza kuwa na tarakimu 10 (kwa vyombo vya kisheria) au tarakimu 12 (kwa watu binafsi). Ikiwa hautapata kosa hasa, basi inawezekana kabisa kuridhika na mara kwa mara d{10,12}, lakini, kwa kusema madhubuti, itatoa nambari zote kutoka kwa herufi 10 hadi 12, ambayo ni, na kuingiza nambari 11 kimakosa. Itakuwa sahihi zaidi kutumia mifumo miwili iliyounganishwa na opereta wa kimantiki AU | (bar wima):

Tafadhali kumbuka kuwa katika swali tunatafuta kwanza nambari 12-bit, na kisha tu kwa nambari 10-bit. Ikiwa tutaandika usemi wetu wa kawaida kwa njia nyingine, basi itavutia kila mtu, hata TIN ndefu 12-bit, herufi 10 za kwanza tu. Hiyo ni, baada ya hali ya kwanza kuanzishwa, uthibitishaji zaidi haufanyiki tena:

Hii ndio tofauti kuu kati ya opereta | kutoka kwa utendaji wa kawaida wa mantiki ya Excel OR (OR), ambapo kupanga upya hoja hakubadilishi matokeo.

SKU za bidhaa

Katika makampuni mengi, vitambulisho vya kipekee vinawekwa kwa bidhaa na huduma - makala, SAP codes, SKUs, nk Ikiwa kuna mantiki katika nukuu zao, basi zinaweza kuvutwa kwa urahisi kutoka kwa maandishi yoyote kwa kutumia maneno ya kawaida. Kwa mfano, ikiwa tunajua kuwa nakala zetu huwa na herufi kubwa tatu za Kiingereza, kistari na nambari inayofuata ya nambari tatu, basi:

Mantiki nyuma ya template ni rahisi. [AZ] - inamaanisha herufi kubwa zozote za alfabeti ya Kilatini. Kikadiriaji kinachofuata 3 {} inasema kwamba ni muhimu kwetu kwamba kuna herufi tatu kama hizo. Baada ya hyphen, tunasubiri tarakimu tatu, kwa hiyo tunaongeza mwishoni d{3}

Kiasi cha fedha

Kwa njia sawa na aya iliyotangulia, unaweza pia kutoa bei (gharama, VAT ...) kutoka kwa maelezo ya bidhaa. Ikiwa kiasi cha fedha, kwa mfano, kinaonyeshwa na hyphen, basi:

Pattern d na quantifier + hutafuta nambari yoyote hadi kistari, na d{2} itatafuta senti (tarakimu mbili) baada ya.

Iwapo unahitaji kutoa si bei, bali VAT, basi unaweza kutumia hoja ya tatu ya hiari ya kipengele chetu cha kukokotoa cha RegExpExtract, ambacho hubainisha nambari ya mpangilio wa kipengele kitakachotolewa. Na, bila shaka, unaweza kuchukua nafasi ya kazi MBADALA (BADALA) katika matokeo, unganisha kwa kitenganishi cha kawaida cha desimali na ongeza minus mara mbili mwanzoni ili Excel itafsiri VAT iliyopatikana kama nambari ya kawaida:

Nambari za sahani za gari

Ikiwa hutachukua magari maalum, trela na pikipiki nyingine, basi nambari ya gari ya kawaida inachukuliwa kulingana na kanuni "barua - nambari tatu - barua mbili - msimbo wa mkoa". Zaidi ya hayo, msimbo wa eneo unaweza kuwa na tarakimu 2 au 3, na ni zile tu zinazofanana kwa mwonekano wa alfabeti ya Kilatini ndizo zinazotumiwa kama herufi. Kwa hivyo, usemi ufuatao wa kawaida utatusaidia kutoa nambari kutoka kwa maandishi:

Wakati

Ili kutoa muda katika umbizo la HH:MM, usemi ufuatao wa kawaida unafaa:

Baada ya kipande cha koloni [0-5] siku, kwa kuwa ni rahisi kujua, huweka nambari yoyote katika safu 00-59. Kabla ya koloni kwenye mabano, mifumo miwili hufanya kazi, ikitenganishwa na mantiki AU (bomba):

• [0-1] siku - nambari yoyote katika safu 00-19
• 2[0-3] - nambari yoyote katika safu 20-23

Kwa matokeo yaliyopatikana, unaweza kuongeza kazi ya kawaida ya Excel TIME (TIMU)ili kuibadilisha kuwa muundo wa wakati unaoeleweka kwa programu na unafaa kwa mahesabu zaidi.

Angalia nenosiri

Tuseme kwamba tunahitaji kuangalia orodha ya nywila zilizovumbuliwa na watumiaji kwa usahihi. Kwa mujibu wa sheria zetu, nywila zinaweza tu kuwa na herufi za Kiingereza (herufi ndogo au kubwa) na nambari. Nafasi, mistari chini na alama zingine za uakifishaji haziruhusiwi.

Kuangalia kunaweza kupangwa kwa kutumia usemi rahisi wa kawaida ufuatao:

Kwa kweli, kwa muundo kama huu tunahitaji kwamba kati ya mwanzo (^) na mwisho ($) katika maandishi yetu kulikuwa na wahusika tu kutoka kwa seti iliyotolewa katika mabano ya mraba. Ikiwa unahitaji pia kuangalia urefu wa nenosiri (kwa mfano, angalau herufi 6), basi kihesabu + inaweza kubadilishwa na muda "sita au zaidi" katika fomu {6,}:

Jiji kutoka kwa anwani

Wacha tuseme tunahitaji kuvuta jiji kutoka kwa upau wa anwani. Programu ya kawaida itasaidia, kutoa maandishi kutoka kwa "g." kwa koma ifuatayo:

Hebu tuangalie kwa karibu muundo huu.

Ikiwa umesoma maandishi hapo juu, basi tayari umeelewa kuwa baadhi ya wahusika katika maneno ya kawaida (vipindi, nyota, ishara za dola, nk) zina maana maalum. Ikiwa unahitaji kutafuta wahusika hawa wenyewe, basi hutanguliwa na kurudi nyuma (wakati mwingine huitwa shielding) Kwa hivyo, wakati wa kutafuta kipande "g". inabidi tuandike kwa kujieleza mara kwa mara Mheshimiwa ikiwa tunatafuta nyongeza, basi + nk

Herufi mbili zinazofuata katika kiolezo chetu, nukta na nyota ya kipimaji, huwakilisha idadi yoyote ya herufi zozote, yaani, jina lolote la jiji.

Kuna koma mwishoni mwa kiolezo, kwa sababu tunatafuta maandishi kutoka kwa "g." kwa koma. Lakini kunaweza kuwa na koma kadhaa katika maandishi, sivyo? Sio tu baada ya jiji, lakini pia baada ya barabara, nyumba, nk. Ni nani kati yao ataacha ombi letu? Hiyo ndiyo sababu ya alama ya kuuliza. Bila hivyo, usemi wetu wa kawaida ungetoa kamba ndefu zaidi iwezekanavyo:

Kwa maneno ya kawaida, muundo kama huo ni "uchoyo". Ili kusahihisha hali hiyo, alama ya kuuliza inahitajika - hufanya kibainishi kisha kusimama "chochovu" - na swala letu huchukua maandishi hadi kwenye koma ya kwanza ya kaunta baada ya "g.":

Jina la faili kutoka kwa njia kamili

Hali nyingine ya kawaida ni kutoa jina la faili kutoka kwa njia kamili. Usemi rahisi wa kawaida wa fomu utasaidia hapa:

Ujanja hapa ni kwamba utafutaji, kwa kweli, hutokea kwa mwelekeo tofauti - kutoka mwisho hadi mwanzo, kwa sababu mwisho wa template yetu ni. $, na tunatafuta kila kitu kabla yake hadi mwanzo wa kurudi nyuma kutoka kulia. Mshtuko wa nyuma umeepukwa, kama kitone kwenye mfano uliopita.

PS

"Kuelekea mwisho" nataka kufafanua kwamba yote yaliyo hapo juu ni sehemu ndogo ya uwezekano wote ambao maneno ya kawaida hutoa. Kuna wahusika wengi maalum na sheria za matumizi yao, na vitabu vizima vimeandikwa juu ya mada hii (ninapendekeza angalau hii kwa mwanzo). Kwa njia fulani, kuandika maneno ya kawaida ni karibu sanaa. Takriban kila mara, usemi wa kawaida uliobuniwa unaweza kuboreshwa au kuongezwa, na kuifanya kuwa ya kifahari zaidi au kuweza kufanya kazi na anuwai kubwa ya data ya ingizo.

Ili kuchanganua na kuchanganua misemo ya kawaida ya watu wengine au kutatua yako mwenyewe, kuna huduma kadhaa zinazofaa mtandaoni: RegEx101, RegExr na zaidi

Kwa bahati mbaya, sio sifa zote za misemo ya kawaida ya kawaida inayoungwa mkono katika VBA (kwa mfano, utaftaji wa nyuma au madarasa ya POSIX) na inaweza kufanya kazi na Kicyrillic, lakini nadhani kile kilichopo kinatosha kwa mara ya kwanza kukufurahisha.

Ikiwa wewe si mgeni kwa mada, na una kitu cha kushiriki, acha maneno ya kawaida yenye manufaa wakati wa kufanya kazi katika Excel katika maoni hapa chini. Akili moja ni nzuri, lakini buti mbili ni jozi!

• Kubadilisha na kusafisha maandishi kwa chaguo la kukokotoa SUBSTITUTE
• Kutafuta na kuangazia herufi za Kilatini katika maandishi
• Tafuta maandishi ya karibu sawa (Ivanov = Ivonov = Ivanof, nk.)