Բովանդակություն
Եթե դուք գոնե մի փոքր ծանոթ եք կանոնավոր արտահայտություններին, ապա ձեզ հարկավոր չէ դրանք գովազդել։ Եթե դուք ամբողջությամբ չեք մտնում թեմայի մեջ, ապա կանոնավոր արտահայտությունները (Regular Expressions = RegExp = «regexps» = «կանոնավոր») լեզու է, որտեղ հատուկ նիշերի և կանոնների միջոցով որոնվում են անհրաժեշտ ենթատողերը տեքստում, դրանք հանվում են: կամ փոխարինվել այլ տեքստով: Սա շատ հզոր և գեղեցիկ գործիք է, որը գերազանցում է տեքստի հետ աշխատելու բոլոր այլ եղանակներին:
Ես արդեն մանրամասն նկարագրել եմ կյանքից մի շարք օրինակներով, թե ինչպես կարող եք սովորական արտահայտությունների աջակցություն ավելացնել Excel-ին՝ օգտագործելով պարզ մակրոներ. եթե չեք կարդացել այս հոդվածը, խորհուրդ եմ տալիս կարդալ այն նախքան շարունակելը: Շատ նոր բաներ կբացահայտեք, երաշխավորում եմ 🙂
Այնուամենայնիվ, հարցը մնում է բաց. ինչպե՞ս ավելացնել կանոնավոր արտահայտություններ օգտագործելու հնարավորություն Power Query-ում: Power Query-ն, իհարկե, ինքնին լավ է և կարող է շատ բան անել տեքստի հետ (կտրում, սոսնձում, մաքրում և այլն), բայց եթե կարողանայիք այն հատել կանոնավոր արտահայտությունների ուժով, դա պարզապես ռումբ կլիներ:
Ցավոք, Power Query-ում RegExps-ի հետ աշխատելու համար ներկառուցված գործառույթներ չկան, և Microsoft-ի պաշտոնական օգնությունն ու տեխնիկական աջակցությունը բացասական պատասխան են տալիս այս հարցին: Այնուամենայնիվ, կա այս սահմանափակումը շրջանցելու ճանապարհ 🙂
Մեթոդի էությունը
Հիմնական գաղափարը պարզ է, որ խայտառակվի:
Ներկառուցված Power Query հնարավորությունների ցանկում կա գործառույթ Ինտերնետային էջ. Այս գործառույթի նկարագրությունը Microsoft-ի օգնության պաշտոնական կայքում չափազանց հակիրճ է.
Թարգմանաբար սա կլինի. «Վերադարձնում է HTML փաստաթղթի բովանդակությունը՝ բաժանված իր բաղադրիչ կառուցվածքներով, ինչպես նաև ամբողջական փաստաթղթի և դրա մարմնի ներկայացումը պիտակները հեռացնելուց հետո»: Այսքան նկարագրություն, անկեղծ ասած։
Սովորաբար այս ֆունկցիան օգտագործվում է համացանցից տվյալներ ներմուծելիս և ավտոմատ կերպով փոխարինվում է, օրինակ, երբ մենք ընտրում ենք ներդիրում Ամսաթիվ Հրաման Համացանցից (Տվյալներ — համացանցից). Մենք ֆունկցիային տալիս ենք վեբ էջ որպես արգումենտ, և այն մեզ վերադարձնում է դրա բովանդակությունը աղյուսակների տեսքով՝ նախապես մաքրելով բոլոր թեգերը։
Այն, ինչ օգնությունը ՉԻ ասում, այն է, որ բացի HTML նշագրման լեզվից ֆունկցիա Ինտերնետային էջ աջակցում է JavaScript սկրիպտներին, որն այժմ ամենուր տարածված է համացանցի կայքերում: Իսկ JavaScript-ն իր հերթին միշտ կարողացել է աշխատել կանոնավոր արտահայտությունների հետ և ունի ներկառուցված գործառույթներ RegExps-ի համար: Այսպիսով, Power Query-ում կանոնավոր արտահայտություններ իրականացնելու համար մենք պետք է սնուցենք Web.Page ֆունկցիաները՝ որպես արգումենտ մի փոքրիկ JavaScript ծրագրի, որը կկատարի Power Query-ի ողջ աշխատանքը:
Ինչ տեսք ունի մաքուր JavaScript-ում
Ինտերնետում կան շատ մանրամասն ձեռնարկներ JavaScript-ում կանոնավոր արտահայտությունների հետ աշխատելու վերաբերյալ (օրինակ՝ մեկ, երկու):
Կարճ և պարզեցված՝ JavaScript կոդը կունենա հետևյալ տեսքը.
Այստեղ `
- var str = 'Վճարեք 123 և 789 հաշիվները երշիկի համար'; - ստեղծել փոփոխական փող և նրան նշանակեք սկզբնաղբյուր տեքստը, որը մենք կվերլուծենք:
- var օրինակ = /d+/gi; – ստեղծել կանոնավոր արտահայտություն և տեղադրել այն փոփոխականի մեջ օրինակ.
Արտահայտությունը սկսվում է կտրվածքով (/):
Արտահայտությունն ինքնին այստեղ, օրինակ, հետևյալն է d+ նշանակում է թվանշանների ցանկացած հաջորդականություն:
Արտահայտությունից հետո կոտորակի միջոցով կան լրացուցիչ որոնման պարամետրեր (փոփոխիչներ) – դրանք կարող են նշվել ցանկացած հերթականությամբ.
- g – նշանակում է գլոբալ որոնում, այսինքն համընկնում գտնելուց հետո պետք է ոչ թե կանգ առնել, այլ շարունակել որոնումը մինչև տեքստի ավարտը։ Եթե այս փոփոխիչը կարգավորված չէ, ապա մեր սցենարը կվերադարձնի միայն առաջին համընկնումը (123)
- i - որոնել առանց տառերի գործի
- m - բազմակողմ որոնում (օգտագործվում է, երբ սկզբնական տեքստը բաժանված է մի քանի տողերի)
- var result = str.match(pattern).join(';'); - կատարել որոնում սկզբնական տեքստում (փող) տրված կանոնավոր արտահայտությամբ (օրինակ) և արդյունքները դրեք փոփոխականի մեջ արդյունք, դրանք միացնելով ստորակետի հետ՝ օգտագործելով հրամանը միանալ
- document.write (արդյունք); - ցուցադրել արդյունքի փոփոխականի բովանդակությունը
Նկատի ունեցեք նաև, որ JavaScript-ում տեքստային տողերը (բացառությամբ կանոնավոր արտահայտությունների) փակցված են ապոստրոֆներով, այլ ոչ թե չակերտներով, ինչպես դրանք Power Query-ում կամ VBA-ում են:
Ելքի ժամանակ այս սցենարը մեզ կտա աղբյուրի տեքստում հայտնաբերված բոլոր թվերը.
123, 789
JavaScript-ի կարճ դասընթացն ավարտվեց, շնորհակալություն բոլորիդ: Հուսով եմ, որ դուք հասկանում եք տրամաբանությունը 🙂
Մնում է այս կոնստրուկցիան փոխանցել Power Query-ին։
Որոնել և արդյունահանել տեքստային ֆունկցիան կանոնավոր արտահայտությամբ Power Query-ում
Մենք անում ենք հետևյալը.
1. Բացեք Excel-ը և ներդիրում ստեղծեք նոր դատարկ Power Query Տվյալներ – Ստացեք տվյալներ / Ստեղծեք հարցում – Այլ աղբյուրներից – Դատարկ հարցում (Տվյալներ — Ստացեք տվյալներ / Նոր հարցում — Այլ աղբյուրներից — Դատարկ հարցում). Եթե ունեք Excel 2010-2013 և Power Query-ի հին տարբերակ, դուք չունեք ներկառուցված, բայց տեղադրվել է որպես առանձին հավելում, ապա այս ամենը կլինի ներդիրում: Power հարցումԵւ ոչ Ամսաթիվ.
2. Հարցման խմբագրի դատարկ պատուհանում, որը բացվում է, աջ վահանակում անմիջապես մուտքագրեք մեր ապագա ֆունկցիայի անունը (օրինակ. fxRegExpExtract)
3. Եկեք գնանք ներդիր Դիտել – Ընդլայնված խմբագիր (Դիտել — Ընդլայնված խմբագիր), մենք ջնջում ենք դատարկ հարցման ամբողջ M-կոդը և տեղադրում մեր գերֆունկցիայի կոդը այնտեղ.
Դիտեք ձեր ձեռքերը.
Առաջին տողում մենք ասում ենք, որ մեր ֆունկցիան կունենա երեք տեքստային արգումենտ. txt - վերլուծվող բնօրինակ տեքստը, կարգավորումը - կանոնավոր արտահայտման օրինակ, զառանցանք — Արդյունքները ցուցադրելու համար սահմանազատող նիշ:
Հաջորդը մենք կոչում ենք գործառույթը Ինտերնետային էջ, ձևավորելով JavaScript-ի ծածկագիրը, որը նկարագրված է վերը նշված իր արգումենտում: Մենք տեղադրում և փոխարինում ենք մեր փոփոխական արգումենտները կոդի մեջ:
Հատված:
[Տվյալներ]{0}[Children]{0}[Children]{1}[Text]{0}
… անհրաժեշտ է, որպեսզի «ընկնենք» աղյուսակում մեզ անհրաժեշտ արդյունքներով: Բանն այն է, որ ֆունկցիան Ինտերնետային էջ արդյունքում՝ այն արտադրում է մի քանի ներդիր աղյուսակներ, որոնք կրկնում են վեբ էջի կառուցվածքը։ Առանց M-կոդի այս հատվածի, մեր գործառույթը կարտադրի սա.
… և մենք ստիպված կլինենք մի քանի անգամ սեղմել բառը Սեղան, հաջորդաբար «ընկնում» է սյունակներով երեխաների մեջ տեղադրված աղյուսակների մեջ Երեխաներ:
Այս ամբողջ մեջբերումի փոխարեն մենք անմիջապես նշում ենք մեր ֆունկցիայի կոդում, թե որ աղյուսակը և սյունակը (Տեքստ) կարիք ունենք.
Ահա, փաստորեն, բոլոր գաղտնիքները։ Մնում է սեղմել կոճակը Ավարտել պատուհանում առաջադեմ խմբագիր, որտեղ մենք զետեղեցինք մեր կոդը, և դուք կարող եք անցնել ամենահամեղին՝ փորձեք մեր ֆունկցիան աշխատավայրում:
Ահա մի քանի սերմերի օրինակներ:
Օրինակ 1. Հաշվի համարը և ամսաթիվը ստանալը վճարման նկարագրությունից
Մենք ունենք բանկային քաղվածք՝ վճարումների նկարագրությամբ (նպատակով), որտեղ անհրաժեշտ է առանձին սյունակներում հանել վճարված հաշիվ-ապրանքագրերի համարներն ու ամսաթվերը.
Մենք աղյուսակը բեռնում ենք Power Query-ում ստանդարտ ձևով Տվյալներ – Աղյուսակից / միջակայքից (Տվյալներ - Տկարող/Ռհրեշտակ).
Այնուհետև մենք ավելացնում ենք հաշվարկված սյունակ մեր ֆունկցիայի միջոցով Ավելացնել սյունակ – Զանգահարել հատուկ գործառույթ (Ավելացնել սյունակ — կանչել հատուկ գործառույթը) և մուտքագրեք դրա փաստարկները.
Որպես կանոնավոր արտահայտություն (փաստարկ կարգավորումը) ձևանմուշ, որը մենք օգտագործում ենք.
(դ{3,5}|d{2}.d{2}.d{4})
… թարգմանվել է մարդկային լեզվի իմաստով.
3-ից 5 նիշ թվեր (հաշվի համարներ)
or
«2-բիթանոց թիվ – կետ – 2-բիթանոց համար – կետ – 4-բիթ» ձևի հատվածներ., այսինքն՝ DD.MM.YYYY ձևի ամսաթվերը։
Որպես սահմանազատող կերպար (փաստարկ զառանցանք) մուտքագրեք ստորակետ:
Սեղմելուց հետո OK մեր կախարդական ֆունկցիան վերլուծում է բոլոր նախնական տվյալները՝ ըստ մեր կանոնավոր արտահայտության և մեզ համար կազմում սյունակ՝ գտնված հաշիվ-ապրանքագրերի թվերով և ամսաթվերով.
Մնում է այն բաժանել ստորակետով՝ օգտագործելով հրամանը Գլխավոր — Պառակտված սյուն — ըստ սահմանազատողի (Գլխավոր — Բաժանված սյունակ — ըստ սահմանազատողի) և մենք ստանում ենք այն, ինչ ուզում էինք.
Գեղեցկուհի:
Օրինակ 2. տեքստից հանեք էլփոստի հասցեները
Ենթադրենք որպես նախնական տվյալ ունենք հետևյալ աղյուսակը.
… որտեղից մենք պետք է հանենք այնտեղ հայտնաբերված էլփոստի հասցեները (հստակության համար ես տեքստում դրանք կարմիրով ընդգծեցի):
Ինչպես նախորդ օրինակում, մենք աղյուսակը բեռնում ենք Power Query-ում ստանդարտ եղանակով՝ միջոցով Տվյալներ – Աղյուսակից / միջակայքից (Տվյալներ - Տկարող/Ռհրեշտակ).
Այնուհետև մենք ավելացնում ենք հաշվարկված սյունակ մեր ֆունկցիայի միջոցով Ավելացնել սյունակ – Զանգահարել հատուկ գործառույթ (Ավելացնել սյունակ — կանչել հատուկ գործառույթը) և մուտքագրեք դրա փաստարկները.
Էլփոստի հասցեների վերլուծությունն ավելի բարդ խնդիր է, և կան տարբեր աստիճանի մղձավանջի կանոնավոր արտահայտություններ այն լուծելու համար: Ես օգտագործեցի պարզ տարբերակներից մեկը՝ ոչ իդեալական, բայց շատ դեպքերում բավականին աշխատող.
[w|.|-]*@w*.[w|.]*
Որպես տարանջատող (զառանցանք) կարող եք մուտքագրել ստորակետ և բացատ:
Սեղմիր OK և մենք ստանում ենք «շիլա» բնօրինակ տեքստից քաղված էլեկտրոնային փոստի հասցեներով սյունակ.
Մոգություն:
PS
Ինչպես ասում են. «Չկա այնպիսի լավ բան, որը հնարավոր չլինի ավելի լավը դարձնել»: Power Query-ն ինքնին հիանալի է, և երբ զուգակցվում է կանոնավոր արտահայտությունների հետ, այն մեզ լիովին անիրատեսական ուժ և ճկունություն է տալիս ցանկացած տեքստային տվյալների մշակման հարցում: Հուսով եմ, որ Microsoft-ը մի օր կավելացնի RegExp-ի աջակցությունը Power Query-ի և Power BI-ի թարմացումներում, և բոլոր վերը նշված պարերը դափի հետ կդառնան անցյալի բան: Դե, առայժմ, այո:
Ավելացնեմ նաև, որ https://regexr.com/ կայքում հարմար է խաղալ կանոնավոր արտահայտություններով՝ հենց առցանց խմբագրիչում։ Այնտեղ բաժնում Համայնքի նախշեր Կան մեծ թվով պատրաստի կանոնավոր սեզոններ բոլոր առիթների համար: Փորձարկում – կանոնավոր արտահայտությունների ողջ հզորությունն այժմ ձեր ծառայության մեջ է Power Query-ում:
- Որոնք են կանոնավոր արտահայտությունները (RegExp) և ինչպես օգտագործել դրանք Excel-ում
- Անորոշ տեքստի որոնում Power Query-ում
- Power Query-ի միջոցով տարբեր ֆայլերից աղյուսակների հավաքում