Fasa rubutu tare da maganganu na yau da kullun (RegExp) a cikin Excel

Ɗaya daga cikin ayyuka mafi cin lokaci da takaici yayin aiki tare da rubutu a cikin Excel shine nazari - tantance haruffan “porridge” cikin abubuwan da aka gyara da kuma fitar da gutsuttsuran da muke bukata daga ciki. Misali:

• cire lambar zip daga adireshin (yana da kyau idan zip code koyaushe yana farawa, amma idan ba haka bane fa?)
• gano lamba da kwanan wata na daftari daga bayanin biyan kuɗi a cikin bayanin banki
• cire TIN daga kwatancin kamfanoni a cikin jerin takwarorinsu
• nemo lambar mota ko lambar labarin a cikin bayanin, da sauransu.
  

Yawancin lokaci a irin waɗannan lokuta, bayan rabin sa'a na ɗaukar rubutu da hannu, tunani zai fara zuwa tunani ko ta yaya don sarrafa wannan tsari (musamman idan akwai bayanai da yawa). Akwai mafita da yawa kuma tare da mabambantan ma'auni na rikitaccen aiki:

• amfani ginanniyar ayyukan rubutu na Excel don bincika rubutu-yanke-manne: LEVSIMV (HAGU), RIGHT (HAKA), PSTR (tsakiyar), STsEPIT (CONCATENATE) da analogues, HADE (JOINTEXT), SAURARA (MAGANIN) da dai sauransu. Wannan hanya tana da kyau idan akwai ma'ana bayyananne a cikin rubutu (misali, fihirisar tana koyaushe a farkon adireshin). In ba haka ba, tsarin ya zama mafi rikitarwa kuma, wani lokacin, har ma ya zo ga tsarin tsararru, wanda ke raguwa sosai a kan manyan tebur.
• Amfani kamar ma'aikacin kamanni na rubutu daga Visual Basic nannade cikin aikin macro na al'ada. Wannan yana ba ku damar aiwatar da bincike mai sassauƙa ta amfani da haruffan jakunkuna (*, #,?, da sauransu) Abin baƙin ciki shine, wannan kayan aikin ba zai iya cire abin da ake so daga rubutun ba - kawai duba idan yana cikin sa.

Baya ga abin da ke sama, akwai wata hanyar da ta shahara sosai a cikin kunkuntar ƙwararrun masu tsara shirye-shirye, masu haɓaka gidan yanar gizo da sauran fasaha - wannan shine. maganganun yau da kullun (Magana na yau da kullum = RegExp = "regexps" = "na yau da kullum"). A taƙaice, RegExp yare ne inda ake amfani da haruffa na musamman da ƙa'idodi don nemo mahimman kalmomin da ake buƙata a cikin rubutu, cire su ko musanya su da wani rubutu.. Kalmomi na yau da kullun kayan aiki ne masu ƙarfi da kyan gani waɗanda suka zarce duk sauran hanyoyin aiki da rubutu ta tsari mai girma. Yawancin harsunan shirye-shirye (C#, PHP, Perl, JavaScript…) da masu gyara rubutu (Kalma, Notepad++…) suna goyan bayan maganganun yau da kullun.

Microsoft Excel da rashin alheri ba shi da tallafin RegExp daga cikin akwatin, amma ana iya gyara wannan cikin sauƙi tare da VBA. Bude Editan Kayayyakin Kaya daga shafin developer (Mai haɓakawa) ko gajeriyar hanyar keyboard alt+F11. Sannan saka sabon tsarin ta cikin menu Saka - Module kuma kwafi rubutun aikin macro mai zuwa a can:

Ayyukan Jama'a RegExpExtract(Rubutu A Matsayin Zati, Tsarin A Matsayin Zaɓuɓɓuka, Abun Zabi A Matsayin Ƙirar = 1) A Matsayin Kitin Kan Kuskure GoTo ErrHandl Saita regex = CreateObject("VBScript.RegExp") regex.Pattern = Tsarin regex.Global = Gaskiya Idan regexTest. (Text) Sa'an nan Saita matches = regex.Execute (Text) RegExpExtract = matches.Item (Abu - 1) Ƙarshen Ayyukan Ƙarshen Idan ErrHandl: RegExpExtract = CVERr (xlErrValue) Ƙarshen Aiki  

Yanzu za mu iya rufe Editan Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin Kayayyakin gani) kuma mu koma Excel don gwada sabon fasalin mu. Ma'anarsa shine kamar haka:

=RegExpExtract(Txt; Tsarin; Abu)

inda

• txt – tantanin halitta tare da rubutun da muke dubawa kuma daga abin da muke so mu cire abin da muke bukata
• tsari - abin rufe fuska (samfurin) don binciken substring
• Item - jerin jerin layin da za a fitar, idan akwai da yawa daga cikinsu (idan ba a bayyana ba, to ana nuna abin da ya faru na farko)

Abu mafi ban sha'awa a nan, ba shakka, shine Tsarin - ƙirar samfuri na haruffa na musamman "a cikin harshe" na RegExp, wanda ke ƙayyade ainihin abin da kuma inda muke so mu samu. Anan ga mafi mahimmancin abubuwan da za a fara ku:

Idan muna neman takamaiman adadin haruffa, misali, lambar akwatin gidan waya mai lamba shida ko duk lambobin samfurin harafi uku, to mun zo wurin ceto. masu ƙididdigewa or masu ƙididdigewa maganganu ne na musamman waɗanda ke ƙayyadad da adadin haruffan da za a bincika. Ana amfani da ƙididdiga ga halayen da ke gabansa:

Yanzu bari mu matsa zuwa mafi ban sha'awa sashi - nazarin aikace-aikace na halitta aikin da abin da muka koya game da alamu a kan m misalai na rayuwa.

Ciro lambobi daga rubutu

Don fara da, bari mu bincika wani lamari mai sauƙi - kuna buƙatar cire lambar farko daga porridge alphanumeric, alal misali, ikon samar da wutar lantarki mai katsewa daga jerin farashin:

Hankalin da ke bayan magana na yau da kullun yana da sauƙi: d yana nufin kowane lambobi, da ƙididdiga + ya ce adadin su ya zama daya ko fiye. Ana buƙatar rage ninki biyu a gaban aikin don "a kan tashi" canza haruffan da aka ciro zuwa cikakken lamba daga lamba-as-rubutu.

Lambar gidan waya

A kallon farko, komai yana da sauƙi a nan - muna neman daidai lambobi shida a jere. Muna amfani da hali na musamman d don lambobi da ƙididdiga 6 {} ga adadin haruffa:

Duk da haka, wani yanayi yana yiwuwa idan, a gefen hagu na index a cikin layi, akwai wani babban adadin lambobi a jere (lambar waya, TIN, asusun banki, da dai sauransu) Sa'an nan lokacin mu na yau da kullum zai fitar da 6 na farko. lambobi daga gare ta, watau ba za su yi aiki daidai ba:

Don hana hakan faruwa, muna buƙatar ƙara mai gyarawa a kusa da gefuna na maganganun mu na yau da kullun b yana nuna ƙarshen kalma. Wannan zai bayyana a fili ga Excel cewa guntun (index) da muke buƙata yakamata ya zama kalma dabam, kuma ba wani ɓangare na wani guntu (lambar waya):

Wayar

Matsalar neman lambar waya a cikin rubutu shine cewa akwai zaɓuɓɓuka da yawa don rubuta lambobi - tare da kuma ba tare da sarƙaƙƙiya ba, ta cikin sarari, tare da ko ba tare da lambar yanki a cikin maƙallan ba, da sauransu. Saboda haka, a ganina, yana da sauƙi don da farko tsaftace duk waɗannan haruffa daga rubutun tushe ta amfani da ayyuka da yawa da aka gina MUSA (MADAMA)ta yadda zai manne tare a cikin gaba ɗaya, sannan tare da na yau da kullun na farko d{11} fitar da lambobi 11 a jere:

ITN

Yana da ɗan rikitarwa a nan, saboda TIN (a cikin ƙasarmu) na iya zama lambobi 10 (na ƙungiyoyin doka) ko lambobi 12 (na mutane). Idan ba ku sami laifi musamman, to yana yiwuwa a gamsu da na yau da kullun d{10,12}, amma, a zahiri magana, zai fitar da duk lambobi daga haruffa 10 zuwa 12, watau kuma cikin kuskure ya shigar da lambobi 11. Zai zama mafi daidai don amfani da alamu biyu da aka haɗa ta ma'aikacin ma'ana KO ma'aikaci | (bargon tsaye):

Da fatan za a lura cewa a cikin tambayar za mu fara neman lambobi 12-bit, sannan kawai don lambobi 10-bit. Idan muka rubuta maganganun mu na yau da kullun ta wata hanya, to zai cire wa kowa, har ma da dogon TINs 12-bit, kawai haruffa 10 na farko. Wato, bayan an kunna yanayin farko, ba a sake yin ƙarin tabbaci:

Wannan shine babban bambanci tsakanin mai aiki | daga daidaitaccen aikin dabaru na Excel OR (OR), inda sake tsara gardama baya canza sakamakon.

Samfurin SKUs

A cikin kamfanoni da yawa, ana ba da masu ganewa na musamman ga kayayyaki da ayyuka - labarai, lambobin SAP, SKUs, da dai sauransu. Idan akwai ma'ana a cikin bayanin su, to ana iya cire su cikin sauƙi daga kowane rubutu ta amfani da maganganu na yau da kullum. Misali, idan mun san cewa a ko da yaushe labaranmu sun kunshi manyan haruffan turanci guda uku, da jita-jita da lamba mai lamba uku na gaba, to:

Hankalin da ke bayan samfurin yana da sauƙi. [AZ] – yana nufin kowane babban haruffa na haruffan Latin. Ƙidaya ta gaba 3 {} ya ce yana da mahimmanci a gare mu cewa akwai ainihin haruffa guda uku. Bayan jigon, muna jiran lambobi uku, don haka muna ƙara a ƙarshen d{3}

Adadin kuɗi

Hakazalika zuwa sakin layi na baya, zaku iya fitar da farashi (farashi, VAT…) daga bayanin kaya. Idan adadin kuɗi, alal misali, ana nuna su tare da saƙa, to:

juna d tare da ƙididdiga + yana neman kowace lamba har zuwa saƙa, kuma d{2} zai nemi dinari (lambobi biyu) bayan.

Idan kuna buƙatar cirewa ba farashi ba, amma VAT, to zaku iya amfani da hujjar zaɓi na uku na aikin RegExpExtract ɗin mu, wanda ke ƙayyadaddun adadin adadin abubuwan da za a fitar. Kuma, ba shakka, zaku iya maye gurbin aikin MUSA (MADAMA) a cikin sakamakon, ƙara ƙara zuwa daidaitaccen ma'aunin ƙima kuma ƙara ragi sau biyu a farkon don Excel ta fassara VAT da aka samo azaman lambar al'ada:

Lambobin farantin mota

Idan ba ku ɗauki motoci na musamman, tirela da sauran babura ba, to, daidaitattun lambar mota an rarraba bisa ga ka'idar "wasiƙa - lambobi uku - haruffa biyu - lambar yanki". Haka kuma, lambar yankin na iya zama lambobi 2 ko 3, kuma waɗanda kawai suke kama da haruffan Latin ne ake amfani da su azaman haruffa. Don haka, magana ta yau da kullun mai zuwa zata taimake mu mu ciro lambobi daga rubutun:

Time

Don cire lokaci a cikin tsarin HH:MM, magana ta yau da kullun mai zuwa ta dace:

Bayan gutsattsarin hanji [0-5] d, kamar yadda yake da sauƙin ganewa, saita kowane lamba a cikin kewayon 00-59. Kafin hanin a cikin bakan gizo, alamu biyu suna aiki, waɗanda ke raba su ta hanyar ma'ana OR (bututu):

• [0-1] d - kowace lamba a cikin kewayon 00-19
• 2[0-3] - kowace lamba a cikin kewayon 20-23

Don sakamakon da aka samu, zaku iya amfani da daidaitaccen aikin Excel TIME (TEAM)don canza shi zuwa tsarin lokaci wanda zai iya fahimtar shirin kuma ya dace da ƙarin ƙididdiga.

Duba kalmar sirri

A ce muna buƙatar bincika jerin kalmomin shiga da masu amfani suka ƙirƙira don daidaito. Bisa ga dokokin mu, kalmomin shiga za su iya ƙunsar haruffa Turanci kawai (ƙananan ko babba) da lambobi. Ba a yarda da sarari, ƙaranci da sauran alamomin rubutu ba.

Ana iya shirya dubawa ta amfani da magana mai sauƙi na yau da kullun:

A gaskiya ma, tare da irin wannan tsari muna buƙatar cewa tsakanin farkon (^) da kuma karshen ($) a cikin rubutunmu akwai haruffa kawai daga saitin da aka bayar a maƙallan murabba'i. Idan kuma kuna buƙatar bincika tsawon kalmar sirri (misali, aƙalla haruffa 6), sannan ƙididdigewa + za a iya maye gurbinsu da tazara "shida ko fiye" a cikin tsari {6,}:

City daga adireshin

Bari mu ce muna buƙatar cire gari daga mashaya adireshin. Shirin na yau da kullum zai taimaka, cire rubutun daga "g." zuwa waƙafi na gaba:

Bari mu dubi wannan tsari a hankali.

Idan kun karanta rubutun da ke sama, to kun riga kun fahimci cewa wasu haruffa a cikin maganganu na yau da kullun (lokaci, asterisks, alamun dollar, da sauransu) suna da ma'ana ta musamman. Idan kuna buƙatar neman waɗannan haruffan da kansu, to ana gabace su da ja da baya (wani lokaci ana kiran su garkuwa). Don haka, lokacin neman guntun "g." dole ne mu rubuta a cikin magana akai-akai Mr. idan muna neman ƙari, to + da dai sauransu.

Haruffa biyu masu zuwa a cikin samfurin mu, ɗigo da alamar ƙididdigewa, suna tsaye ga kowane adadin kowane haruffa, watau kowane sunan birni.

Akwai waƙafi a ƙarshen samfurin, saboda muna neman rubutu daga “g.” zuwa waƙafi. Amma ana iya samun waƙafi da yawa a cikin rubutun, daidai? Ba bayan gari kadai ba, har da bayan titi, gidaje da sauransu. A kan wanne ne daga cikinsu bukatarmu za ta tsaya? Abin da alamar tambaya ke nan. Idan ba tare da shi ba, maganganun mu na yau da kullun zai fitar da mafi tsayin kirtani mai yuwuwa:

Dangane da maganganun yau da kullum, irin wannan samfurin shine "m". Don gyara halin da ake ciki, ana buƙatar alamar tambaya - yana sanya ma'aunin ƙididdigewa bayan haka ya zama "mai rowa" - kuma tambayarmu tana ɗaukar rubutun har zuwa waƙafi na farko bayan "g.":

Sunan fayil daga cikakken hanya

Wani yanayi na yau da kullun shine cire sunan fayil daga cikakken hanyar. Sauƙaƙan bayanin tsari na yau da kullun zai taimaka anan:

Dabarar a nan ita ce binciken, a gaskiya, yana faruwa a cikin kishiyar shugabanci - daga ƙarshe zuwa farkon, saboda a ƙarshen samfurin mu shine. $, kuma muna neman komai kafin shi har zuwa baya na farko daga dama. An kubuta da koma baya, kamar ɗigon da ke cikin misalin da ya gabata.

PS

"Zuwa karshen" Ina so in bayyana cewa duk abubuwan da ke sama kadan ne na duk damar da maganganun yau da kullum ke bayarwa. Akwai haruffa da ƙa'idodi na musamman da yawa don amfani da su, kuma an rubuta littattafai duka akan wannan batu (Ina ba da shawarar aƙalla wannan don farawa). A wata hanya, rubuta maganganun yau da kullum kusan fasaha ne. Kusan koyaushe, ƙirƙira na yau da kullun za a iya inganta ko ƙarawa, yana mai da shi mafi ƙayatarwa ko iya aiki tare da kewayon bayanan shigarwa.

Don tantancewa da tantance maganganun wasu na yau da kullun ko gyara naku, akwai sabis na kan layi da yawa masu dacewa: RegEx101, RegExr kuma mafi

Abin takaici, ba duk fasalulluka na maganganun yau da kullun na yau da kullun suna tallafawa a cikin VBA (misali, bincike na baya ko azuzuwan POSIX) kuma suna iya aiki tare da Cyrillic, amma ina tsammanin abin da ke akwai ya isa a karon farko don faranta muku rai.

Idan ba ku saba da batun ba, kuma kuna da wani abu don raba, bar maganganun yau da kullun masu amfani yayin aiki a cikin Excel a cikin sharhin da ke ƙasa. Hankali ɗaya yana da kyau, amma takalma biyu biyu ne!

• Sauya da tsaftace rubutu tare da aikin SUBSTITUTE
• Bincika da haskaka haruffan Latin a cikin rubutu
• Nemo irin wannan rubutu mafi kusa (Ivanov = Ivonov = Ivanof, da dai sauransu)