Матнро бо ифодаҳои муқаррарӣ (RegExp) дар Excel таҳлил кунед

Ҳангоми кор бо матн дар Excel яке аз вазифаҳои аз ҳама вақтталаб ва рӯҳафтодакунанда мебошад таҳлил – “порридж”-и алифбои рақамиро ба ҷузъҳо ҷудо карда, порчаҳои ба мо лозимиро аз он ҷудо мекунем. Барои намуна:

• истихроҷи индекси почта аз суроға (хуб аст, агар индекси почта ҳамеша дар ибтидо бошад, аммо агар ин тавр набошад?)
• пайдо кардани ракам ва санаи счёт-фактура аз тавсифи пардохт дар хисоботи бонк
• истихроҷи ТИН аз тавсифи рангоранги ширкатҳо дар рӯйхати контрагентҳо
• дар тавсиф рақами мошин ё рақами мақоларо ҷустуҷӯ кунед ва ғайра.
  

Одатан, дар чунин мавридҳо, пас аз ним соати дастӣ чидани ғамангези матн, фикрҳо дар бораи автоматикунонии ин раванд (хусусан агар маълумот зиёд бошад) ба хотир меоянд. Якчанд роҳҳо мавҷуданд ва бо дараҷаҳои гуногуни мураккабӣ-самаранокӣ:

• истифода бурдан функсияҳои матнии Excel дарунсохт барои ҷустуҷӯ-буридани матн: ЛЕВСИМВ (ЧАП), Ҳуқуқ (ҲАҚ), PSTR (миёна), STsEPIT (МАЙДОН) ва аналогҳои он, КОМБАЙН (JOINTEXT), ЭЗОҲ (АЙН) ва ғайра Ин усул хуб аст, агар дар матн мантиқи равшан мавҷуд бошад (масалан, индекс ҳамеша дар аввали суроға бошад). Дар акси ҳол, формулаҳо хеле мураккабтар мешаванд ва баъзан он ҳатто ба формулаҳои массив меояд, ки дар ҷадвалҳои калон хеле суст мешавад.
• Бо истифода аз монанди оператори шабоҳати матн аз Visual Basic дар вазифаи макросҳои фармоишӣ печонда шудааст. Ин ба шумо имкон медиҳад, ки бо истифода аз аломатҳои ҷонишин (*, #,?, ва ғ.) Ҷустуҷӯи чандиртарро амалӣ кунед, мутаассифона, ин асбоб зерсатри дилхоҳро аз матн берун карда наметавонад – танҳо тафтиш кунед, ки оё он дар он мавҷуд аст.

Илова ба гуфтаҳои боло, як равиши дигаре вуҷуд дорад, ки дар доираи танги барномасозони касбӣ, таҳиягарони веб ва дигар техникҳо хеле хуб маълум аст - ин аст ибораҳои муқаррарӣ (Ифодаҳои муқаррарӣ = RegExp = "regexps" = "муқаррарӣ"). Оддӣ карда гӯем, RegExp забонест, ки дар он аломатҳо ва қоидаҳои махсус барои ҷустуҷӯи зерсатрҳои зарурӣ дар матн, истихроҷи онҳо ё иваз кардани онҳо бо матни дигар истифода мешаванд. Ифодаҳои муқаррарӣ як воситаи хеле пурқувват ва зебо мебошанд, ки аз ҳама усулҳои дигари кор бо матн аз рӯи андоза болотаранд. Бисёр забонҳои барномасозӣ (C#, PHP, Perl, JavaScript…) ва муҳаррирони матнӣ (Word, Notepad++…) ифодаҳои муқаррариро дастгирӣ мекунанд.

Мутаассифона, Microsoft Excel дастгирии RegExp надорад, аммо онро бо VBA ба осонӣ ислоҳ кардан мумкин аст. Муҳаррири Visual Basic -ро аз ҷадвал кушоед таҳиякунанда (Таҳиягар) ё миёнабури клавиатура Alt+F11. Сипас модули навро тавассути меню гузоред Ворид - Модул ва матни функсияи макроси зеринро дар он ҷо нусхабардорӣ кунед:

Функсияи ҷамъиятии RegExpExtract(Матн ҳамчун сатр, намуна ҳамчун сатр, банди ихтиёрӣ ҳамчун бутун = 1) Ҳамчун сатр дар хато GoTo ErrHandl Танзими regex = CreateObject("VBScript.RegExp") regex.Pattern = Намунаи regex.Global = Ҳақиқӣ Агар regex.Test бошад (Матн) Сипас мувофиқатҳоро танзим кунед = regex.Execute(Text) RegExpExtract = matches.Item(Item - 1) Баромади функсия Агар ErrHandl: RegExpExtract = CVErr(xlErrValue) End Функсияи  

Ҳоло мо метавонем муҳаррири Visual Basic-ро пӯшем ва ба Excel баргардем, то хусусияти нави худро санҷем. Синтаксиси он чунин аст:

=RegExpExtract ( Txt ; Шаблон ; Элемент )

ки дар

• txt – ячейка бо матне, ки мо онро тафтиш карда истодаем ва аз он мо мехоҳем зерсатри ба мо лозимиро хориҷ кунем
• намуна – ниқоб (намуна) барои ҷустуҷӯи зерсатр
• Банди - рақами пайдарпаии зерсатри истихроҷшаванда, агар якчандтои онҳо вуҷуд дошта бошанд (агар нишон дода нашуда бошад, пас ҳодисаи аввал нишон дода мешавад)

Чизи ҷолибтарин дар ин ҷо, албатта, Pattern аст - сатри қолаби аломатҳои махсуси "ба забони" RegExp, ки мушаххас мекунад, ки мо чӣ ва дар куҷо пайдо кардан мехоҳем. Инҳоянд асосӣтаринҳо барои оғоз кардани шумо:

Агар мо шумораи муайяни аломатҳоро ҷустуҷӯ кунем, масалан, индекси шаш-рақама ё ҳама рамзҳои се ҳарфи маҳсулот, мо ба наҷот меоем. миқдорҳо or миқдорҳо ифодаҳои махсусе мебошанд, ки шумораи аломатҳои ҷустуҷӯшавандаро муайян мекунанд. Миқдорҳо ба аломати пеш аз он омада истифода мешаванд:

Акнун биёед ба қисми ҷолибтарин - таҳлили татбиқи функсияи сохташуда ва он чизе ки мо дар бораи намунаҳо дар мисолҳои амалӣ аз ҳаёт омӯхтаем, мегузарем.

Аз матн баровардани рақамҳо

Барои оғоз кардан, биёед як мисоли оддиро таҳлил кунем - шумо бояд рақами аввалро аз порчаи алифбои рақамӣ гиред, масалан, қудрати таъминоти барқи бефосила аз рӯйхати нархҳо:

Мантиқи паси ифодаи муқаррарӣ оддӣ аст: d маънои ҳар як рақам ва миқдорро дорад + мегӯяд, ки шумораи онҳо бояд як ё якчанд бошад. Минуси дукарата дар пеши функсия барои табдил додани аломатҳои истихроҷшуда ба рақами пурра аз рақам ҳамчун матн лозим аст.

Postcode

Дар назари аввал, дар ин ҷо ҳама чиз оддӣ аст - мо дар як саф маҳз шаш рақамро ҷустуҷӯ мекунем. Мо аломати махсусро истифода мебарем d барои рақам ва миқдор 6 {} барои шумораи аломатҳо:

Бо вуҷуди ин, вазъият имконпазир аст, ки дар тарафи чапи индекс дар сатр як қатор дигар рақамҳои калон (рақами телефон, ТИН, суратҳисоби бонкӣ ва ғайра) мавҷуд бошад, пас мавсими муқаррарии мо 6-и аввалро берун мекунад. рақамҳо аз он, яъне дуруст кор намекунанд:

Барои пешгирии ин ҳодиса, мо бояд тағирдиҳандаеро дар атрофи кунҷҳои ифодаи муқаррарии худ илова кунем b маънои охири калима. Ин ба Excel равшан хоҳад кард, ки фрагмент (индекс), ки ба мо лозим аст, бояд калимаи алоҳида бошад, на қисми порчаи дигар (рақами телефон):

телефон

Мушкилоти дарёфти рақами телефон дар матн дар он аст, ки имконоти зиёде барои навиштани рақамҳо вуҷуд дорад – бо ва бидуни дефис, ба воситаи фосила, бо ё бе рамзи минтақа дар қавс ва ғайра. Аз ин рӯ, ба назари ман, осонтар аст. аввал ҳамаи ин аломатҳоро аз матни сарчашма бо истифода аз якчанд функсияҳои лона тоза кунед ИСТИФОДА (ҶАВОБ)ба тавре ки он ба як бутуни ягона ва баъд бо регуляти ибтидоӣ мечаспад г{11} 11 рақамро дар як саф кашед:

ITN

Дар ин ҷо ин каме мураккабтар аст, зеро РМА (дар кишвари мо) метавонад 10-рақама (барои шахсони ҳуқуқӣ) ё 12-рақама (барои шахсони воқеӣ) бошад. Агар шумо махсусан айб наёбед, пас аз муқаррарӣ қаноатманд шудан мумкин аст г{10,12}, аммо, ба таври қатъӣ гӯем, он ҳамаи рақамҳоро аз 10 то 12 аломат берун мекунад, яъне 11 рақамро хато ворид кардааст. Истифодаи ду намунаи бо оператори мантиқии OR пайвастшуда дурусттар мебуд | (бар амудӣ):

Лутфан қайд кунед, ки дар дархост мо аввал рақамҳои 12-битро ҷустуҷӯ мекунем ва танҳо баъд рақамҳои 10-битро меҷӯем. Агар мо ифодаи муқаррарии худро ба таври дигар нависем, он барои ҳама, ҳатто TIN-ҳои дарози 12-бит, танҳо 10 аломати аввалро берун мекунад. Яъне, пас аз ба кор андохтани ҳолати аввал, санҷиши минбаъда дигар гузаронида намешавад:

Ин фарқи асосии байни оператор аст | аз функсияи мантиқии стандартии Excel OR (Ё), ки дар он ҷо аз нав тартиб додани далелҳо натиҷаро тағир намедиҳад.

SKUs маҳсулот

Дар бисёр ширкатҳо идентификаторҳои беназир ба молҳо ва хидматҳо таъин карда мешаванд - мақолаҳо, кодҳои SAP, SKU ва ғайра. Агар дар қайди онҳо мантиқ мавҷуд бошад, пас онҳоро бо истифода аз ибораҳои муқаррарӣ ба осонӣ аз ҳама гуна матн кашидан мумкин аст. Масалан, агар мо донем, ки мақолаҳои мо ҳамеша аз се ҳарфи калони англисӣ, дефис ва рақами се рақами минбаъда иборатанд, пас:

Мантиқи паси қолаб оддӣ аст. [AZ] – маънои ҳама гуна ҳарфҳои калони алифбои лотинӣ. Миқдори навбатӣ 3 {} мегуяд, ки барои мо махз се чунин мактуб мавчуд будани мухнм аст. Пас аз дефис, мо се рақамро интизорем, бинобар ин дар охири он илова мекунем г{3}

Маблағҳои нақд

Ба ҳамин монанд ба параграфи қаблӣ, шумо инчунин метавонед нархҳоро (арзишҳо, ААИ ...) аз тавсифи мол кашед. Агар маблағи пулӣ, масалан, бо дефис нишон дода шуда бошад, пас:

намуна d бо миқдор + ягон ададро то дефис ҷустуҷӯ мекунад ва г{2} пас аз пенни (ду рақам) ҷустуҷӯ хоҳад кард.

Агар ба шумо лозим ояд, ки на нархҳо, балки ААИ-ро истихроҷ кунед, пас шумо метавонед аргументи сеюми ихтиёрии функсияи RegExpExtract-ро истифода баред, ки рақами тартибии элементи истихроҷшавандаро муайян мекунад. Ва, албатта, шумо метавонед функсияро иваз кунед ИСТИФОДА (ҶАВОБ) Дар натиҷаҳо, ба ҷудокунандаи даҳии стандартӣ дефис гузошта, дар аввал минуси дукарата илова кунед, то Excel андоз аз арзиши иловашударо ҳамчун рақами муқаррарӣ шарҳ диҳад:

Рақамҳои рақами мошин

Агар шумо мошинҳои махсус, прицепҳо ва дигар мотоциклҳоро нагиред, рақами стандартии мошин аз рӯи принсипи «ҳарф - се рақам - ду ҳарф - коди минтақавӣ» таҳлил карда мешавад. Гузашта аз ин, рамзи минтақа метавонад 2 ё 3-рақама бошад ва танҳо онҳое, ки аз ҷиҳати зоҳирӣ ба алифбои лотинӣ монанданд, ҳамчун ҳарф истифода мешаванд. Ҳамин тариқ, ифодаи муқаррарии зерин ба мо барои гирифтани рақамҳо аз матн кӯмак мекунад:

Time

Барои истихроҷи вақт дар формати HH:MM, ифодаи муқаррарии зерин мувофиқ аст:

Пас аз пораи ғафс [0-5]д, чунон ки фаҳмидан осон аст, ҳама рақамро дар диапазони 00-59 муқаррар мекунад. Пеш аз ду нуқта дар қавс, ду намуна кор мекунанд, ки бо OR (қубур) мантиқӣ ҷудо карда шудаанд:

• [0-1]д – ҳама гуна рақам дар диапазони 00-19
• 2[0-3] – ҳама гуна рақам дар диапазони 20-23

Ба натиҷаи бадастомада, шумо метавонед ба таври иловагӣ функсияи стандартии Excel-ро татбиқ кунед ВАҚТИ (ДАМА)ки онро ба формати вакт табдил дихад, ки барои барнома фахмо ва барои хисобхои минбаъда мувофик бошад.

Санҷиши парол

Фарз мекунем, ки мо бояд рӯйхати паролҳои ихтироъкардаи корбаронро барои дурустӣ тафтиш кунем. Тибқи қоидаҳои мо, паролҳо метавонанд танҳо ҳарфҳои англисӣ (ҳарфи хурд ё калон) ва рақамҳоро дар бар гиранд. Фосилаҳо, зерхатҳо ва дигар аломатҳои пунктуатсия иҷозат дода намешавад.

Санҷишро бо истифода аз ибораи оддии зерин ташкил кардан мумкин аст:

Дар асл, бо чунин намуна мо талаб мекунем, ки дар байни ибтидо (^) ва хотима ($) дар матни мо танҳо аломатҳои дар қавси мураббаъ овардашуда мавҷуд буданд. Агар ба шумо инчунин лозим бошад, ки дарозии паролро тафтиш кунед (масалан, ҳадди аққал 6 аломат), пас миқдор + дар шакл бо фосилаи «шаш ва зиёда» иваз кардан мумкин аст {6,}:

Шахр аз адрес

Фарз мекунем, ки мо бояд шаҳрро аз сатри адресҳо кашем. Барномаи муқаррарӣ кӯмак мекунад, ки матнро аз "g" хориҷ мекунад. ба вергули навбатӣ:

Биёед ба ин намуна бодиққат назар кунем.

Агар шумо матни дар боло зикршударо хонда бошед, пас шумо аллакай фаҳмидед, ки баъзе аломатҳои ибораҳои муқаррарӣ (нуқтаҳо, ситорачаҳо, аломатҳои доллар ва ғайра) маънои махсус доранд. Агар ба шумо лозим ояд, ки худи ин аломатҳоро ҷустуҷӯ кунед, пас онҳо пеш аз хатти баръакс (баъзан номида мешавад сипаршавӣ). Аз ин рӯ, ҳангоми ҷустуҷӯи порчаи "г". мо бояд бо ифодаи муқаррарӣ нависем Ҷаноб. агар мо як плюсро ҷустуҷӯ кунем, пас + ва ѓайра

Ду аломати навбатӣ дар қолаби мо, нуқта ва ситорачаи миқдор, барои ҳар як рақами ҳар гуна аломатҳо, яъне ягон номи шаҳр рост меоянд.

Дар охири қолаб вергул мавҷуд аст, зеро мо матнро аз "g" ҷустуҷӯ мекунем. ба вергул. Аммо дар матн метавонад якчанд вергул бошад, дуруст? На танхо пас аз шахр, балки пас аз куча, хонахо ва гайра дар кадоми онхо талаби мо бас мешавад? Ин аст, ки аломати савол барои он аст. Бе он, ифодаи муқаррарии мо дарозтарин сатри имконпазирро мебарорад:

Аз чихати иборахои мукаррарй чунин намуна «тамаъ» аст. Барои ислоҳи вазъият, аломати савол лозим аст - он миқдорро "бахил" мекунад - ва дархости мо матнро танҳо то вергули муқобили аввал пас аз "g." мегирад:

Номи файл аз роҳи пурра

Вазъияти дигари маъмул ин истихроҷи номи файл аз роҳи пурра мебошад. Дар ин ҷо ифодаи оддии муқаррарии форма кӯмак хоҳад кард:

Дар ин ҷо ҳилла дар он аст, ки ҷустуҷӯ, воқеан, дар самти муқобил сурат мегирад - аз охир то аввал, зеро дар охири қолаби мо $, ва мо ҳама чизеро, ки пеш аз он, то хати хати аввал аз тарафи рост ҷустуҷӯ мекунем. Хати акибмонда, ба мисли нуқта дар мисоли қаблӣ халос мешавад.

PS

"То ба охир" Ман мехоҳам равшан кунам, ки ҳамаи гуфтаҳои боло як қисми хурди тамоми имкониятҳое мебошанд, ки ифодаҳои муқаррарӣ пешкаш мекунанд. Бисёр аломатҳо ва қоидаҳои истифодаи онҳо вуҷуд доранд ва дар ин мавзӯъ китобҳои пурра навишта шудаанд (ман ҳадди аққал инро барои оғоз тавсия медиҳам). Ба як навъ навиштани ибораҳои муқаррарӣ қариб як санъат аст. Қариб ҳамеша, ифодаи муқаррарии ихтироъшуда метавонад такмил ё илова карда шавад, ки онро шевотар ё қодир ба кор бо доираи васеи маълумоти воридотӣ кунад.

Барои таҳлил ва таҳлили ибораҳои муқаррарии одамони дигар ё ислоҳи худ, якчанд хидматҳои онлайнии қулай мавҷуданд: RegEx101, RegExr ва бештар

Мутаассифона, на ҳама хусусиятҳои ибораҳои муқаррарии классикӣ дар VBA дастгирӣ карда мешаванд (масалан, ҷустуҷӯи баръакс ё синфҳои POSIX) ва метавонанд бо кириллӣ кор кунанд, аммо ман фикр мекунам, ки он чизе ки барои шумо бори аввал аст, кофӣ аст.

Агар шумо дар ин мавзӯъ нав набошед ва чизе барои мубодила дошта бошед, дар шарҳҳои зер ибораҳои муқаррариро ҳангоми кор дар Excel муфид гузоред. Як ақл хуб аст, аммо ду мӯза як ҷуфт аст!

• Иваз ва тоза кардани матн бо функсияи SUBSTITUTE
• Ҷустуҷӯ ва равшан кардани аломатҳои лотинӣ дар матн
• Ҷустуҷӯи матни наздиктарин (Иванов = Ивонов = Иваноф ва ғ.)