Ku kala saar qoraalka odhaahyo joogto ah (RegExp) gudaha Excel

Mid ka mid ah hawlaha ugu waqti-qaadka iyo niyad-jabka ah marka lagu shaqeynayo qoraalka Excel waa falanqaynta - In la kala saaro xarfaha "boorash" qaybo ka mid ah oo laga soo saaro jajabyada aan u baahanahay. Tusaale ahaan:

• ka soo saarida zip code-ka ciwaanka ( way fiicantahay haddii sibka koodhka uu had iyo jeer bilowga yahay, laakiin ka waran haddii aanay ahayn?)
• helitaanka lambarka iyo taariikhda qaansheegta ee sharraxaadda lacag bixinta ee bayaanka bangiga
• ka soo saarida TIN ee sharraxaadaha motley ee shirkadaha ku jira liiska dhigooda
• raadi lambarka baabuurka ama lambarka maqaalka ee sharaxaadda, iwm.
  

Sida caadiga ah xaaladahan oo kale, ka dib markii nus saac ah oo lagu soo qaato qoraalka gacanta, fikradaha ayaa bilaabaya in ay maskaxda ku hayaan si ay u habeeyaan habkan (gaar ahaan haddii ay jiraan xog badan). Waxaa jira dhowr xalal oo leh heerar kala duwan oo kakanaanta-wax ku oolnimada:

• Isticmaal ku-dhisan hawlaha qoraalka Excel si aad u raadiso qoraalka-kobag-goynta: LEVSIMV (BIDIX), XAQA (MIDIG), PSTR (mid), STsEPIT (CONCATENATE) iyo analooyadiisa, ISKU DIR (JOINTEXT), DHAQAN (SAX AH) iwm. Habkani wuu fiican yahay haddii ay jirto caqli-gal cad oo qoraalka ah (tusaale ahaan, tusmada had iyo jeer waxay ku yaalaan bilowga cinwaanka). Haddii kale, qaacidooyinka ayaa noqda kuwo aad u dhib badan, mararka qaarkoodna, xitaa waxay timaaddaa qaacidooyin isku dhafan, kuwaas oo si weyn hoos ugu dhigaya miisaska waaweyn.
• Isticmaalka sida hawlwadeenka u ekaanshaha qoraalka laga soo bilaabo Visual Basic oo ku duudduubay hawl macro caadadii ah. Tani waxay kuu ogolaaneysaa inaad fuliso raadinta dabacsanaan badan adoo isticmaalaya xarfaha duurjoogta ah (*, #,?, iwm.) Nasiib darro, qalabkani kama soo saari karo qoraalka hoose ee la rabo - kaliya hubi haddii ay ku jirto.

Marka lagu daro kuwa kor ku xusan, waxaa jira hab kale oo si aad ah loogu yaqaan wareegyada cidhiidhiga ah ee barnaamijyada xirfadlayaasha ah, horumarinta webka iyo farsamada kale - tani waa tibaaxaha joogtada ah (Regular Expressions = RegExp = "regexps" = "regulars"). Si fudud u dhig, RegExp waa luqad lagu isticmaalo xarfo iyo xeerar gaar ah si loogu raadiyo xarfaha hoose ee lagama maarmaanka u ah qoraalka, laga soo saaro ama lagu beddelo qoraal kale. Tibaaxaha joogtada ahi waa qalab aad u xoog badan oo qurux badan oo ka sarreeya dhammaan siyaabaha kale ee qoraalka loogu shaqeeyo. Luqado badan oo barnaamijka ah (C#, PHP, Perl, JavaScript…) iyo tafatirayaasha qoraalka (Word, Notepad++…) waxay taageeraan tibaaxaha caadiga ah.

Microsoft Excel nasiib daro kuma laha taageerada RegExp ee sanduuqa, laakiin tan si fudud ayaa loogu hagaajin karaa VBA. Ka fur tafatiraha Visual Basic tafatirka sameeysa (horumariye) ama kiiboodhka gaaban Alt+F11. Markaas geli moduleka cusub dhex menu Geli - Module oo nuqul ka samee qoraalka shaqada makro ee soo socota:

Hawlaha Guud ee RegExpExtract (Qoraalka Xarigga, Qaabka Sida Xadhkaha, Shayga Ikhtiyaarka Sida Isku-dhafka = 1) Sida Xadhkaha Qaladka GoTo ErrHandl Set regex = CreateObject("VBScript.RegExp") regex.Pattern = Habka regex.Global = Run Haddii regexTest. (Qoraal) Kadibna deji matches = regex.Execute(Qoraal) RegExpExtract = matches.Waxyaabaha (Qaybta - 1) Shaqada ka bixida Dhamaadka Hadii ErrHandl: RegExpExtract = CVERr (xlErrValue) Dhamaystirka Shaqada  

Hadda waxaan xiri karnaa Tifaftiraha Aasaasiga ah ee Visual oo ku laaban karnaa Excel si aan u tijaabino qaabkayaga cusub. Qoraalkeedu waa sidan:

=RegExpExtract ( Txt ; Qaabka ; Shayga )

halkaas oo

• txt - unug leh qoraalka aan hubineyno oo aan rabno inaan ka soo saarno xargaha hoose ee aan u baahanahay
• qaab - maaskaro (qaabka) raadinta xargaha hoose
• Shayga - tirada isku xigxiga ee substring-ka la soo saarayo, haddii ay jiraan dhowr ka mid ah (haddii aan la cayimin, ka dibna dhacdada ugu horreysa ayaa la soo bandhigay)

Waxa ugu xiisaha badan halkan, dabcan, waa Qaabka - qaab-dhismeedka qaab-dhismeedka xarfaha gaarka ah "luqada" RegExp, kaas oo qeexaya waxa saxda ah iyo meesha aan rabno inaan helno. Waa kuwan kuwa aasaasiga ah ee kugu bilaabi kara:

Haddii aan raadineyno tiro xarfo ah oo gaar ah, tusaale ahaan, koodka boostada ee lix-god ah ama dhammaan summada alaabada saddexda xaraf ah, markaa waxaan u nimid samatabbixinta qiyaasaha or qiyaasaha waa tibaaxo gaar ah oo tilmaamaya tirada xarfaha la raadinayo. Quantifiers ayaa lagu dabaqaa dabeecadda ka horreysa:

Hadda aan u gudubno qaybta ugu xiisaha badan - falanqaynta ku-dhaqanka shaqada la abuuray iyo waxa aan ka baranay qaababka tusaalooyinka la taaban karo ee nolosha.

Ka soo saarida nambarada qoraalka

Si aan u bilowno, aan falanqeyno kiis fudud - waxaad u baahan tahay inaad ka soo saarto lambarka ugu horreeya ee boorash alphanumeric, tusaale ahaan, awoodda sahayda korontada ee aan kala go'in ee liiska qiimaha:

Caqliga ka dambeeya odhaahda caadiga ah waa mid fudud: d macneheedu waa lambar kasta, iyo qiyaasaha + ayaa sheegay in lambarkoodu uu ahaado mid ama ka badan. Laba-jibaarka laga jaro shaqada horteeda ayaa loo baahan yahay si "duuqsi" loogu beddelo jilayaasha la soo saaray tiro buuxa oo ka mid ah lambarka-sida-qoraalka.

Postcode

Jaleecada hore, wax walba waa sahlan yihiin halkan - waxaan raadineynaa lix lambar oo isku xigta. Waxaan isticmaalnaa dabeecad gaar ah d ee digit iyo quantifier 6 {} tirada jilayaasha:

Si kastaba ha ahaatee, xaalad ayaa suurtagal ah marka, dhanka bidix ee tusaha safka, ay jiraan tiro kale oo tiro badan oo isku xigta (lambarka telefoonka, TIN, xisaab bangi, iwm.) Kadibna xilligeena caadiga ah wuxuu soo saari doonaa 6-da hore. nambarada ka soo baxa, ie si sax ah uma shaqeyn doonaan:

Si aan taas uga hortagno, waxaan u baahanahay in aan ku darno wax ka beddelka hareeraha cidhifyada muujintayada caadiga ah b tilmaamaya dhamaadka kelmad. Tani waxay u caddayn doontaa Excel in jajabka (index) aan u baahanahay ay tahay inuu noqdo kelmad gooni ah, oo aan ka mid ahayn jajab kale (lambarka telefoonka):

Phone

Dhibaatada helitaanka lambarka taleefanka ee qoraalka ayaa ah in ay jiraan fursado badan oo loogu talagalay qorista nambarada - oo leh iyo la'aanteed, iyada oo loo marayo meelo bannaan, oo leh ama la'aanteed koodka gobolka ee ku yaala xargaha, iwm. Sidaa darteed, fikradayda, way fududahay in la sameeyo. marka hore ka nadiifi dhammaan jilayaashan qoraalka isha adiga oo isticmaalaya dhowr hawlood oo buul ah MAGACAAB (BADAL)si ay isugu dhegto hal dhan, ka dibna leh mid caadi ah d{11} soo saar 11 nambar oo isku xigta:

ITN

Way ka yara adag tahay halkan, sababtoo ah TIN (Dalkayaga) waxay noqon kartaa 10-god (loogu talagalay hay'adaha sharciga ah) ama 12-god ( shaqsiyaadka). Haddii aadan qalad ku helin gaar ahaan, markaas waa suurtogal in lagu qanco sida caadiga ah d{10,12}, laakiin, si adag u hadlaya, waxay ka soo saari doontaa dhammaan tirooyinka 10 ilaa 12 xaraf, ie oo si khaldan u gashay 11 lambar. Waxa aad u saxnaan lahayd in la isticmaalo laba qaab oo ay ku xidhan yihiin hawlwadeen AMA macquul ah | (bar toosan):

Fadlan la soco in su'aasha marka hore aan raadinno lambarro 12-bit ah, ka dibna kaliya 10-bit nambar. Haddii aan si kale u qorno tibaaxayada caadiga ah, markaas waxay u soo jiidan doontaa qof walba, xitaa 12-bit TINs dheer, kaliya 10ka xaraf ee ugu horreeya. Taasi waa, ka dib marka xaaladda ugu horreysa ay kiciso, xaqiijin dheeraad ah lama sameeyo:

Tani waa farqiga aasaasiga ah ee u dhexeeya hawlwadeenka | laga bilaabo shaqo macquul ah oo heer sare ah OR (AMA), meesha dib u habeynta dooda aysan waxba ka bedelin natiijada.

Waxyaabaha loo yaqaan 'SKUs'

Shirkado badan, aqoonsiyo gaar ah ayaa loo qoondeeyay alaabada iyo adeegyada - maqaallo, koodhadhka SAP, SKUs, iwm. Haddii ay jirto macquul ah qoraalkooda, ka dibna si fudud ayaa looga soo saari karaa qoraal kasta iyadoo la adeegsanayo tibaaxo joogto ah. Tusaale ahaan, haddii aan ognahay in maqaaladeenu ay mar walba ka kooban yihiin saddex xaraf oo Ingiriisi ah oo waaweyn, jilaa-jiid iyo nambar saddex-god ah oo xiga, markaa:

Macnaha ka dambeeya template waa mid fudud. [AZ] - waxaa loola jeedaa xarfo waaweyn oo kasta oo alifbeetada Laatiinka ah. Qiyaasta soo socota 3 {} ayaa sheegay in ay muhiim noo tahay in ay jiraan saddex xaraf oo kale. Jidka ka dib, waxaan sugeynaa seddex lambar, markaa waxaan ku darnaa dhamaadka d{3}

Qadarka lacagta caddaanka ah

Si la mid ah cutubkii hore, waxaad sidoo kale ka saari kartaa qiimaha (kharashyada, VAT…) ee sharaxaadda alaabta. Haddii xaddi lacageed, tusaale ahaan, lagu tilmaamo xaraf, markaas:

Qaabka d oo leh xisaabiye + wuxuu raadiyaa nambar kasta ilaa jikaab, iyo d{2} waxay raadin doontaa pennies (laba lambar) ka dib.

Haddii aad u baahan tahay inaadan soo saarin qiimayaal, laakiin VAT, markaa waxaad isticmaali kartaa doodda ikhtiyaariga ah ee saddexaad ee shaqadayada RegExpExtract, kaas oo qeexaya lambarka caadiga ah ee walxaha la soo saarayo. Iyo, dabcan, waxaad bedeli kartaa shaqada MAGACAAB (BADAL) Natiijooyinka, ku dheji jaangooyeeyaha caadiga ah ee jajab tobanle oo ku dar labanlaab laga jaray bilowga si Excel uu u tarjumo VAT-ka la helay lambar caadi ah:

Nambarada saxanka baabuurka

Haddii aadan qaadan baabuur gaar ah, taraaro iyo mootooyin kale, markaas lambarka gaariga caadiga ah waa la kala qaybiyey sida waafaqsan mabda'a "xaraf - saddex lambar - laba xaraf - code gobolka". Intaa waxaa dheer, koodka gobolku wuxuu noqon karaa 2- ama 3-god, oo kaliya kuwa la mid ah muuqaalka alifbeetada Laatiinka ayaa loo isticmaalaa xarfo ahaan. Haddaba, tibaaxaha joogtada ah ee soo socda ayaa naga caawin doona inaan ka soo saarno tirooyinka qoraalka:

Time

Si loo soo saaro wakhtiga qaabka HH:MM, tibaaxaha joogtada ah ee soo socda ayaa ku habboon:

Ka dib burburka mindhicirka [0-5]d, sida ay fududahay in la ogaado, dhigaysa tiro kasta oo u dhexeeya 00-59. Kahor mindhicirka qawlka, laba qaab ayaa shaqeeya, oo ay kala soocaan AMA macquul ah (tuubo):

• [0-1]d - nambar kasta oo u dhexeeya 00-19
• 2[0-3] - nambar kasta oo u dhexeeya 20-23

Natiijada la helay, waxaad sidoo kale codsan kartaa shaqada caadiga ah ee Excel TIME (TEAM)si loogu beddelo qaab wakhti ah oo la fahmi karo barnaamijka oo ku habboon xisaabinta dheeraadka ah.

Hubinta erayga sirta ah

Ka soo qaad inaan u baahanahay inaan hubinno liiska sirta ah ee isticmaalayaashu ay ikhtiraaceen saxnaanta. Marka loo eego xeerarkeena, furaha sirta ah waxa uu ka koobnaan karaa oo keliya xarfo Ingiriisi (yaro ama far waaweyn) iyo lambaro. Meel bannaan, hoosta hoose iyo calamadaha kale ee xarakaynta lama oggola.

Hubinta waxaa lagu abaabuli karaa iyadoo la adeegsanayo tibaaxaha soo socda ee caadiga ah:

Dhab ahaantii, qaabkan oo kale waxaan u baahanahay in inta u dhaxaysa bilowga (^) iyo dhamaad ($) qoraalkayagu waxa ku jiray oo kaliya xuruuf ka mid ah jaangooyooyinka lagu bixiyay xargaha labajibbaaran. Haddii aad sidoo kale u baahan tahay inaad hubiso dhererka erayga sirta ah (tusaale, ugu yaraan 6 xaraf), ka dibna qiyaasaha + waxaa lagu bedeli karaa inta u dhaxaysa "lix ama ka badan" foomka {6,}:

Magaalada ka ciwaanka

Aynu nidhaahno waxaan u baahanahay inaan ka soo saarno magaalada barta ciwaanka. Barnaamijka caadiga ah ayaa ku caawin doona, ka soo saarista qoraalka "g." ilaa comma xiga:

Aan si hoose u eegno qaabkan.

Haddii aad akhriday qoraalka kor ku xusan, markaa waxaad hore u fahamtay in qaar ka mid ah jilayaasha tibaaxaha caadiga ah (muddo, xiddigiye, calaamadaha dollarka, iwm) ay leeyihiin macne gaar ah. Haddii aad u baahan tahay inaad raadiso jilayaashan laftooda, markaa waxaa ka horreeya dib-u-dhac (mararka qaarkood loo yaqaan gaashaandhigga). Sidaa darteed, markaad raadinayso jajabka "g." waa in aan ku qornaa hadal joogto ah Mr. haddii aan raadineyno dheeri, markaa + iwm

Labada xaraf ee soo socda ee qaab-dhismeedkayada, dhibicda iyo calaamadda cabbirka, waxay u taagan yihiin tiro kasta oo xaraf ah, ie magac kasta oo magaalada ah.

Dhammaadka templateka waxaa jira comma, sababtoo ah waxaan ka raadineynaa qoraalka "g." kooma. Laakin waxaa jiri kara dhowr hakad oo qoraal ah, sax? Ma aha oo kaliya magaalada ka dib, laakiin sidoo kale ka dib wadada, guryaha, iwm. Midkee ka joojin doonaa codsigayagu? Taasi waa waxa calaamad su'aal u tahay. La'aanteed, odhaahdeenna joogtada ahi waxay soo saari doontaa xadhigga ugu dheer ee suurtogalka ah:

Marka la eego tibaaxaha caadiga ah, qaabkan oo kale waa " hunguri weyn ". Si loo saxo xaaladda, calaamad su'aal ayaa loo baahan yahay - waxay ka dhigaysaa qiyaasaha ka dib waxay u taagan tahay "bakhayl" - weydiinteenna waxay qaadanaysaa qoraalka oo keliya ilaa comma-ga koowaad ka dib "g.":

Magaca faylka oo ka yimid jidka buuxa

Xaalad kale oo caam ah ayaa ah in magaca faylka laga soo saaro jidka buuxa. Tilmaanta fudud ee joogtada ah ee foomka ayaa halkan ku caawin doonta:

Khiyaamada halkan ayaa ah in raadinta, dhab ahaantii, ay ku dhacdo jihada ka soo horjeeda - laga bilaabo dhamaadka ilaa bilawga, sababtoo ah dhamaadka qaabkayaga waa $, waxaanan raadineynaa wax kasta ka hor inta aan la gaarin dib u celinta ugu horeysa ee midigta. Dib-u-dhacii waa la baxsaday, sida dhibicda tusaalaha hore.

PS

"Dhammaadka" Waxaan rabaa in aan caddeeyo in dhammaan kuwa kor ku xusan ay yihiin qayb yar oo ka mid ah dhammaan fursadaha ay bixiyaan tibaaxaha caadiga ah. Waxaa jira jilayaal gaar ah oo badan oo loo isticmaalo isticmaalkooda, buugaag dhan ayaa laga qoray mawduucan (waxaan ku talinayaa ugu yaraan kan bilawga ah). Marka la eego, qorista tibaaxaha caadiga ah waxay ku dhowdahay farshaxan. Had iyo jeer, odhaah joogto ah oo la ikhtiraacay waa la wanaajin karaa ama waa la kaabi karaa, taasoo ka dhigaysa mid qurux badan ama awood u leh inay ku shaqeyso tiro balaadhan oo ah xogta wax gelinta.

Si loo falanqeeyo oo loo kala qaybiyo odhaahyada caadiga ah ee dadka kale ama aad adigu iska saxdo, waxa jira adeegyo khadka ah oo dhowr ah: RegEx101, Deeqo iyo in ka badan

Nasiib darrose, ma aha in dhammaan sifooyinka tibaaxaha caadiga ah ee caadiga ah lagu taageerayo VBA (tusaale ahaan, raadinta gadaal ama fasalada POSIX) waxayna la shaqeyn karaan Cyrillic, laakiin waxaan u maleynayaa in waxa jira ay ku filan yihiin markii ugu horeysay si ay kuugu farxiyaan.

Haddii aadan ku cusbayn mawduuca, oo aad haysato wax aad la wadaagto, ka tag tibaaxaha caadiga ah ee waxtarka leh markaad ka shaqeyneyso Excel faallooyinka hoose. Hal maskax ayaa wanaagsan, laakiin laba kabood waa labo!

• Ku beddelka iyo nadiifinta qoraalka shaqada BEDELKA
• Ka raadi oo iftiiminta xuruufta Laatiinka ee qoraalka
• Raadi qoraalka la midka ah ee kuugu dhow (Ivanov = Ivonov = Ivanof, iwm.)