Վերլուծեք տեքստը սովորական արտահայտություններով (RegExp) Excel-ում

Excel-ում տեքստի հետ աշխատելիս առավել ժամանակատար և հիասթափեցնող խնդիրներից մեկն է վերլուծություն – այբբենական «շիլա» վերլուծելը բաղադրիչների և դրանից մեզ անհրաժեշտ բեկորները հանելը: Օրինակ:

• փոստային կոդը հասցեից հանելը (լավ է, եթե փոստային կոդը միշտ սկզբում է, իսկ եթե ոչ):
• բանկային քաղվածքում վճարման նկարագրությունից գտնելով հաշիվ-ապրանքագրի համարը և ամսաթիվը
• TIN-ի արդյունահանում կոնտրագենտների ցուցակում գտնվող ընկերությունների խայտաբղետ նկարագրություններից
• որոնել մեքենայի համարը կամ հոդվածի համարը նկարագրության մեջ և այլն:
  

Սովորաբար նման դեպքերում, կես ժամ տխուր տեքստը ձեռքով հավաքելուց հետո, մտքերը սկսում են ինչ-որ կերպ ավտոմատացնել այս գործընթացը (հատկապես եթե շատ տվյալներ կան): Կան մի քանի լուծումներ և բարդության-արդյունավետության տարբեր աստիճաններով.

• օգտագործում ներկառուցված Excel տեքստային գործառույթներ որոնել-կտրել-սոսնձել տեքստը՝ ԼԵՎՍԻՄՎ (ՁԱԽ), RIGHT (ՃԻՇՏ), ՊՍՏՐ (միջին), STsEPIT (ՄԻԱՑՈՒՄ) և դրա անալոգները, COMBINE- ը (ՀԱՄԱՏԵՔՍՏ), EXACT- ը (ՃՈՒՇՏ) և այլն: Այս մեթոդը լավ է, եթե տեքստում կա հստակ տրամաբանություն (օրինակ, ինդեքսը միշտ հասցեի սկզբում է): Հակառակ դեպքում, բանաձևերը շատ ավելի բարդ են դառնում, և երբեմն խոսքը նույնիսկ զանգվածային բանաձևերի մասին է, ինչը մեծ սեղանների վրա մեծապես դանդաղում է:
• Օգտագործելով ինչպես տեքստի նմանության օպերատորը Visual Basic-ից՝ փաթաթված հատուկ մակրո ֆունկցիայի մեջ: Սա թույլ է տալիս իրականացնել ավելի ճկուն որոնում` օգտագործելով նիշերը (*, #,?, և այլն): Ցավոք, այս գործիքը չի կարող տեքստից հանել ցանկալի ենթատողը. միայն ստուգեք, թե արդյոք այն պարունակվում է դրանում:

Բացի վերը նշվածից, կա ևս մեկ մոտեցում, որը շատ լավ հայտնի է պրոֆեսիոնալ ծրագրավորողների, վեբ ծրագրավորողների և այլ տեխնոլոգիաների նեղ շրջանակներում. կանոնավոր արտահայտություններ (Կանոնավոր արտահայտություններ = RegExp = «regexps» = «կանոնավոր»): Պարզապես դիր, RegExp-ը լեզու է, որտեղ հատուկ նիշերն ու կանոններն օգտագործվում են տեքստում անհրաժեշտ ենթատողերը որոնելու, դրանք հանելու կամ այլ տեքստով փոխարինելու համար։. Կանոնավոր արտահայտությունները շատ հզոր և գեղեցիկ գործիք են, որոնք մեծության կարգով գերազանցում են տեքստի հետ աշխատելու բոլոր այլ եղանակներին: Ծրագրավորման շատ լեզուներ (C#, PHP, Perl, JavaScript…) և տեքստային խմբագրիչներ (Word, Notepad++…) աջակցում են կանոնավոր արտահայտություններին:

Microsoft Excel-ը, ցավոք, չունի RegExp-ի աջակցություն, բայց դա կարելի է հեշտությամբ շտկել VBA-ի միջոցով: Բացեք Visual Basic խմբագրիչը ներդիրից երեվակիչ (մշակող) կամ ստեղնաշարի դյուրանցում ալտ+F11. Այնուհետև մենյուի միջոցով տեղադրեք նոր մոդուլը Ներդիր – մոդուլ և այնտեղ պատճենեք հետևյալ մակրո ֆունկցիայի տեքստը.

Հանրային ֆունկցիա RegExpExtract (Տեքստը որպես տող, նախշը որպես տող, կամընտիր տարրը որպես ամբողջ թիվ = 1) Որպես տող Սխալի վրա Գնալ դեպի ErrHandl Սահմանել regex = CreateObject ("VBScript.RegExp") regex.Pattern = Pattern regex.Global =Treest regex. (Տեքստ) Այնուհետև Սահմանեք համընկնումները = regex. Կատարեք (Տեքստ) RegExpExtract = matches. Նյութ (Նյութ - 1) Ելք ֆունկցիան ավարտվի, եթե ErrHandl. RegExpExtract = CVERr(xlErrValue) Վերջ ֆունկցիա  

Այժմ մենք կարող ենք փակել Visual Basic խմբագրիչը և վերադառնալ Excel՝ փորձելու մեր նոր հնարավորությունը: Դրա շարահյուսությունը հետևյալն է.

=RegExpExtract(Txt; Pattern; Նյութ)

որտեղ

• txt – բջիջ տեքստով, որը մենք ստուգում ենք, և որից ցանկանում ենք հանել մեզ անհրաժեշտ ենթատողը
• օրինակ – դիմակ (նախշ) ենթալարի որոնման համար
• կետ – արդյունահանվող ենթաշարի հաջորդականության համարը, եթե դրանցից մի քանիսը կան (եթե նշված չէ, ապա ցուցադրվում է առաջին դեպքը)

Այստեղ ամենահետաքրքիրը, իհարկե, Pattern-ն է՝ RegExp-ի «լեզվով» հատուկ նիշերի կաղապար, որը հստակեցնում է, թե կոնկրետ ինչ և որտեղ ենք ուզում գտնել: Ահա ամենահիմնականները՝ սկսելու համար.

Եթե ​​մենք փնտրում ենք որոշակի թվով նիշեր, օրինակ՝ վեցանիշ փոստային ինդեքս կամ ապրանքի բոլոր եռատառ կոդերը, ապա մենք օգնության ենք հասնում։ քանակականացուցիչներ or քանակականացուցիչներ հատուկ արտահայտություններ են, որոնք նշում են փնտրվող նիշերի քանակը: Քանակիչները կիրառվում են այն նիշի նկատմամբ, որը գալիս է դրանից առաջ.

Հիմա եկեք անցնենք ամենահետաքրքիր մասին՝ ստեղծված ֆունկցիայի կիրառման վերլուծությանը և կյանքի գործնական օրինակների օրինաչափությունների մասին սովորածին:

Թվերի հանում տեքստից

Սկսելու համար, եկեք վերլուծենք մի պարզ դեպք. դուք պետք է առաջին համարը հանեք այբբենական շիլաից, օրինակ, անխափան սնուցման սնուցման հզորությունը գնացուցակից.

Կանոնավոր արտահայտության տրամաբանությունը պարզ է. d նշանակում է ցանկացած թվանշան և քանակական + ասում է, որ դրանց թիվը պետք է լինի մեկ կամ ավելի։ Գործառույթի դիմաց կրկնակի մինուսն անհրաժեշտ է «թռիչքի վրա» արդյունահանված նիշերը համարը որպես տեքստ ամբողջական թվի վերածելու համար:

Ինդեքս

Առաջին հայացքից այստեղ ամեն ինչ պարզ է. մենք անընդմեջ փնտրում ենք ուղիղ վեց թվանշան: Մենք օգտագործում ենք հատուկ նիշ d թվանշանի և քանակի համար 6 {} նիշերի քանակի համար.

Այնուամենայնիվ, հնարավոր է մի իրավիճակ, երբ գծի ցուցիչից ձախ անընդմեջ թվերի ևս մեկ մեծ հավաքածու կա (հեռախոսահամար, TIN, բանկային հաշիվ և այլն): Այնուհետև մեր կանոնավոր սեզոնը դուրս կգա առաջին 6-ը: դրանից թվանշաններ, այսինքն՝ ճիշտ չեն աշխատի.

Որպեսզի դա տեղի չունենա, մենք պետք է ավելացնենք փոփոխիչ մեր կանոնավոր արտահայտության եզրերին b նշանակում է բառի վերջը. Սա պարզ կդարձնի Excel-ին, որ մեզ անհրաժեշտ հատվածը (ինդեքսը) պետք է լինի առանձին բառ, այլ ոչ թե մեկ այլ հատվածի (հեռախոսահամարի) մաս.

Հեռախոս

Տեքստում հեռախոսահամար գտնելու խնդիրն այն է, որ թվեր գրելու այնքան շատ տարբերակներ կան՝ գծիկներով և առանց գծիկներով, բացատներով, փակագծերում շրջանի կոդով կամ առանց դրա և այլն: Հետևաբար, իմ կարծիքով, ավելի հեշտ է նախ մաքրեք այս բոլոր նիշերը սկզբնաղբյուր տեքստից՝ օգտագործելով մի քանի ներդիր ֆունկցիաներ ՓՈԽԱՐITԱԿ (ՓՈԽԱՐԱԿԻՑ)այնպես, որ այն կպչում է մեկ ամբողջության մեջ, իսկ հետո պարզունակ կանոնավորի հետ դ{11} անընդմեջ հանեք 11 թվանշան.

ITN

Այստեղ ամեն ինչ մի փոքր ավելի բարդ է, քանի որ TIN-ը (մեր երկրում) կարող է լինել 10 նիշ (իրավաբանական անձանց համար) կամ 12 նիշ (ֆիզիկական անձանց համար): Եթե ​​հատկապես սխալ չեք գտնում, ապա միանգամայն հնարավոր է բավարարվել սովորականից դ{10,12}, բայց, խստորեն ասած, այն կհանի բոլոր թվերը 10-ից մինչև 12 նիշ, այսինքն՝ սխալ մուտքագրված 11 նիշ: Ավելի ճիշտ կլինի օգտագործել տրամաբանական OR օպերատորով միացված երկու օրինաչափություններ | (ուղղահայաց բար):

Խնդրում ենք նկատի ունենալ, որ հարցումում մենք նախ փնտրում ենք 12-բիթանոց թվեր, իսկ հետո միայն 10-բիթանոց թվեր: Եթե ​​մենք գրենք մեր կանոնավոր արտահայտությունը հակառակը, ապա այն դուրս կգա բոլորի համար, նույնիսկ երկար 12-բիթանոց TIN-ները, միայն առաջին 10 նիշերը: Այսինքն՝ առաջին պայմանը գործարկվելուց հետո հետագա ստուգումն այլևս չի իրականացվում.

Սա է օպերատորի հիմնական տարբերությունը | Excel-ի ստանդարտ տրամաբանական ֆունկցիայից OR (ԿԱՄ), որտեղ փաստարկների վերադասավորումը չի փոխում արդյունքը։

Արտադրանքի SKU- ներ

Բազմաթիվ ընկերություններում ապրանքներին և ծառայություններին վերագրվում են եզակի նույնացուցիչներ՝ հոդվածներ, SAP կոդեր, SKU-ներ և այլն: Եթե դրանց նշումներում կա տրամաբանություն, ապա դրանք հեշտությամբ կարելի է դուրս բերել ցանկացած տեքստից՝ օգտագործելով կանոնավոր արտահայտություններ: Օրինակ, եթե գիտենք, որ մեր հոդվածները միշտ բաղկացած են երեք մեծ անգլերեն տառերից, գծիկից և հաջորդող եռանիշ թվից, ապա.

Կաղապարի տրամաբանությունը պարզ է. [ԱZ] - նշանակում է լատինական այբուբենի ցանկացած մեծատառ: Հաջորդ քանակական ցուցանիշը 3 {} ասում է, որ մեզ համար կարևոր է, որ այդպիսի տառերը ճիշտ երեքն են։ Գծիկից հետո սպասում ենք երեք թվանշանի, ուստի վերջում ավելացնում ենք դ{3}

Կանխիկ գումարներ

Նախորդ պարբերության նման ձևով դուք կարող եք նաև հանել գները (ծախսեր, ԱԱՀ…) ապրանքների նկարագրությունից: Եթե ​​դրամական գումարները, օրինակ, նշվում են գծիկով, ապա.

Օրինակ d քանակականով + որոնում է ցանկացած թիվ մինչև գծիկ և դ{2} հետո կփնտրի կոպեկներ (երկու նիշ):

Եթե ​​Ձեզ անհրաժեշտ է հանել ոչ թե գները, այլ ԱԱՀ-ն, ապա կարող եք օգտագործել մեր RegExpExtract ֆունկցիայի երրորդ ընտրովի արգումենտը, որը նշում է հանվող տարրի հերթական համարը։ Եվ, իհարկե, դուք կարող եք փոխարինել գործառույթը ՓՈԽԱՐITԱԿ (ՓՈԽԱՐԱԿԻՑ) Արդյունքներում գծիկ գրեք ստանդարտ տասնորդական բաժանարարին և սկզբում ավելացրեք կրկնակի մինուս, որպեսզի Excel-ը գտնված ԱԱՀ-ն մեկնաբանի որպես նորմալ թիվ.

Մեքենայի համարանիշները

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:

ժամանակ

HH:MM ձևաչափով ժամանակը հանելու համար հարմար է հետևյալ կանոնավոր արտահայտությունը.

Հաստ աղիքի բեկորից հետո [0-5]դ, ինչպես հեշտ է պարզել, սահմանում է ցանկացած թիվ 00-59 միջակայքում: Փակագծերում դրված երկու կետից առաջ աշխատում են երկու օրինաչափություններ՝ բաժանված տրամաբանական OR (խողովակով).

• [0-1]դ – ցանկացած թիվ 00-19 միջակայքում
• 2 [0-3] – ցանկացած թիվ 20-23 միջակայքում

Ստացված արդյունքին կարող եք լրացուցիչ կիրառել Excel-ի ստանդարտ գործառույթը ԺԱՄԱՆԱԿԸ (ԹԻՄ)այն փոխարկելու ժամանակի ձևաչափի, որը հասկանալի է ծրագրին և հարմար է հետագա հաշվարկների համար:

Գաղտնաբառի ստուգում

Ենթադրենք, որ մենք պետք է ստուգենք օգտատերերի կողմից հորինված գաղտնաբառերի ցանկը՝ ճշտության համար։ Մեր կանոնների համաձայն՝ գաղտնաբառերը կարող են պարունակել միայն անգլերեն տառեր (փոքր կամ մեծատառ) և թվեր։ Բացատներ, ընդգծումներ և այլ կետադրական նշաններ չեն թույլատրվում:

Ստուգումը կարող է կազմակերպվել՝ օգտագործելով հետևյալ պարզ կանոնավոր արտահայտությունը.

Փաստորեն, նման օրինաչափությամբ մենք պահանջում ենք, որ սկզբի միջև (^) և վերջ ($) մեր տեքստում կային միայն քառակուսի փակագծերում տրված բազմությունից։ Եթե ​​Ձեզ անհրաժեշտ է նաև ստուգել գաղտնաբառի երկարությունը (օրինակ՝ առնվազն 6 նիշ), ապա քանակական + կարող է փոխարինվել ձևի «վեց և ավելի» միջակայքով {6,}:

Քաղաքը հասցեից

Ենթադրենք, մենք պետք է քաղաքը հանենք հասցեի տողից: Սովորական ծրագիրը կօգնի՝ հանելով տեքստը «g»-ից։ հաջորդ ստորակետին.

Եկեք ավելի սերտ նայենք այս օրինակին:

Եթե ​​կարդացել եք վերը նշված տեքստը, ապա արդեն հասկացել եք, որ կանոնավոր արտահայտությունների որոշ նիշեր (կետ, աստղանիշ, դոլարի նշաններ և այլն) հատուկ նշանակություն ունեն։ Եթե ​​դուք պետք է փնտրեք այս կերպարները, ապա նրանց նախորդում է հետադարձ շեղ (երբեմն կոչվում է. shielding) Հետևաբար, «g» հատվածը որոնելիս: մենք պետք է կանոնավոր արտահայտությամբ գրենք Պարոն եթե մենք փնտրում ենք գումարած, ապա + եւ այլն:

Մեր ձևանմուշի հաջորդ երկու նիշերը՝ կետը և քանակական աստղանիշը, նշանակում են ցանկացած նիշ, այսինքն՝ ցանկացած քաղաքի անվանում:

Կաղապարի վերջում կա ստորակետ, քանի որ մենք փնտրում ենք «g» տեքստը: ստորակետին. Բայց տեքստում կարող են լինել մի քանի ստորակետեր, չէ՞: Ոչ միայն քաղաքից հետո, այլև փողոցից, տներից և այլնից հետո, դրանցից ո՞ւմ վրա է կանգնելու մեր խնդրանքը։ Ահա թե ինչի համար է հարցական նշանը: Առանց դրա, մեր կանոնավոր արտահայտությունը կհանի հնարավոր ամենաերկար տողը.

Կանոնավոր արտահայտությունների առումով նման օրինաչափությունը «ագահ» է։ Իրավիճակը շտկելու համար անհրաժեշտ է հարցական նշան. այն դարձնում է քանակական, որից հետո այն մնում է «ժլատ», և մեր հարցումը տեքստը տանում է միայն մինչև «g»-ից հետո առաջին հաշվիչը:

Ֆայլի անունը ամբողջական ուղուց

Մեկ այլ շատ տարածված իրավիճակ է ֆայլի անունը ամբողջական ճանապարհից հանելը: Ձևի պարզ կանոնավոր արտահայտությունը կօգնի այստեղ.

Այստեղ հնարքն այն է, որ որոնումը, ըստ էության, տեղի է ունենում հակառակ ուղղությամբ՝ վերջից սկիզբ, քանի որ մեր կաղապարի վերջում կա. $, և մենք փնտրում ենք ամեն ինչ մինչ այդ, մինչև աջից առաջին հետշեղը: Հետադարձ կտրվածքը կտրված է, ինչպես նախորդ օրինակի կետը:

PS

«Դեպի վերջ» ուզում եմ պարզաբանել, որ վերը նշված բոլորը մի փոքր մասն են այն բոլոր հնարավորությունների, որոնք տալիս են կանոնավոր արտահայտությունները: Կան շատ հատուկ նիշեր և դրանց օգտագործման կանոններ, և այս թեմայով գրվել են ամբողջ գրքեր (սկզբի համար խորհուրդ եմ տալիս գոնե այս մեկը): Ինչ-որ առումով կանոնավոր արտահայտություններ գրելը գրեթե արվեստ է: Գրեթե միշտ, հորինված կանոնավոր արտահայտությունը կարող է բարելավվել կամ լրացվել՝ դարձնելով այն ավելի էլեգանտ կամ կարող է աշխատել մուտքային տվյալների ավելի լայն շրջանակի հետ:

Այլ մարդկանց կանոնավոր արտահայտությունները վերլուծելու և վերլուծելու կամ ձեր սեփականը վրիպազերծելու համար կան մի քանի հարմար առցանց ծառայություններ. RegEx101, RegExr- ը եւ ավելի

Ցավոք, դասական կանոնավոր արտահայտությունների ոչ բոլոր հնարավորություններն են աջակցվում VBA-ում (օրինակ՝ հակադարձ որոնում կամ POSIX դասեր) և կարող են աշխատել կիրիլիցայի հետ, բայց կարծում եմ, որ այն, ինչ կա, առաջին անգամ բավարար է ձեզ գոհացնելու համար:

Եթե ​​դուք նոր չեք թեմայում, և ունեք կիսվելու բան, ներքևում գտնվող մեկնաբանություններում թողեք սովորական արտահայտություններ, որոնք օգտակար են Excel-ում աշխատելիս: Մի միտքը լավ է, բայց երկու կոշիկները զույգ են:

• Տեքստի փոխարինում և մաքրում SUBSTITUTE գործառույթով
• Search and highlighting of Latin characters in text
• Որոնել մոտակա նմանատիպ տեքստը (Իվանով = Իվոնով = Իվանոֆ և այլն)