Power Query-ում զանգվածային տեքստի փոխարինում List.Acumulate ֆունկցիայով

Ինչպես արագ և զանգվածաբար փոխարինել տեքստը ըստ հղումների ցանկի բանաձևերով. մենք արդեն դասավորել ենք այն: Այժմ փորձենք դա անել Power Query-ում:

Ինչպես հաճախ է պատահում կատարել այս խնդիրը շատ ավելի հեշտ է, քան բացատրելը ինչու դա աշխատում է, բայց եկեք փորձենք անել երկուսն էլ 🙂

Այսպիսով, մենք ունենք երկու «խելացի» դինամիկ աղյուսակ, որոնք ստեղծվել են սովորական տիրույթներից՝ ստեղնաշարի դյուրանցմամբ Ctrl+T կամ թիմ Գլխավոր – Ձևաչափել որպես աղյուսակ (Տուն — Ձևաչափել որպես աղյուսակ):

Ես կանչեցի առաջին սեղանը Ամսաթիվ, երկրորդ աղյուսակը – տեղեկատուօգտագործելով դաշտը Աղյուսակի անվանումը (Սեղանի անվանումը) ականջակալ շինարար (Դիզայն).

Առաջադրանք՝ փոխարինել աղյուսակում նշված հասցեներով Ամսաթիվ բոլոր երևույթները սյունակից Գտնել Ձեռնարկ սյունակից իրենց համապատասխան ճիշտ նմանակներին Փոխարինեք. Բջիջների տեքստի մնացած մասը պետք է մնա անձեռնմխելի:

Քայլ 1. Բեռնել գրացուցակը Power Query-ում և վերածել այն ցուցակի

Ակտիվ բջիջը հղման աղյուսակի ցանկացած տեղ դնելուց հետո կտտացրեք ներդիրին Ամսաթիվ (Ամսաթիվ)կամ ներդիրի վրա Power հարցում (եթե ունեք Excel-ի հին տարբերակ և տեղադրել եք Power Query-ը որպես հավելում առանձին ներդիրում) կոճակի վրա Սեղանից/տեսականից (Սեղանից / միջակայքից).

Հղման աղյուսակը կբեռնվի Power Query հարցման խմբագրիչում՝

Որպեսզի չխանգարեն, ինքնաբերաբար ավելացված քայլ փոփոխված տեսակը (Փոխված տեսակը) աջ վահանակում կիրառված քայլերը կարող են ապահով կերպով ջնջվել՝ թողնելով միայն քայլը Աղբյուր (Աղբյուր):

Այժմ, հետագա փոխակերպումներ և փոխարինումներ կատարելու համար, մենք պետք է այս աղյուսակը վերածենք ցուցակի (ցուցակի):

Քնարական շեղում

• Սեղան մի քանի տողերից և սյունակներից բաղկացած երկչափ զանգված է։
• Ձայնագրություն (ձայնագրություն) - միաչափ զանգված-տող, որը բաղկացած է մի քանի դաշտերից-տարրերից անուններով, օրինակ. [Անուն = «Մաշա», սեռ = «զ», Տարիքը = 25]
  
• Ցուցակ – միաչափ զանգված-սյունակ, որը բաղկացած է մի քանի տարրերից, օրինակ {1, 2, 3, 10, 42} or { «Հավատ հույս սեր» }
  

Մեր խնդիրը լուծելու համար մեզ առաջին հերթին կհետաքրքրի տեսակը Ցուցակ.

Այստեղ հնարքն այն է, որ Power Query-ում ցուցակի տարրերը կարող են լինել ոչ միայն սովորական թվեր կամ տեքստեր, այլև այլ ցուցակներ կամ գրառումներ: Այնպիսի բարդ ցուցակում (ցուցակում), որը բաղկացած է գրառումներից (գրառումներից), մենք պետք է շրջենք մեր գրացուցակը: Power Query-ի շարահյուսական նշումներում (գրառումները քառակուսի փակագծերում, ցուցակները գանգուր փակագծերում) սա կունենա հետևյալ տեսքը.

{

    [ Գտեք = «Սբ. Պետերբուրգ», Փոխարինել = «Սբ. Պետերբուրգ»] ,

    [ Գտեք = «Սբ. Պետերբուրգ», Փոխարինել = «Սբ. Պետերբուրգ»] ,

    [ Գտնել = «Պետրոս», Փոխարինել = «Սբ. Պետերբուրգ»] ,

եւ այլն:

}

Նման փոխակերպումն իրականացվում է Power Query-ում ներկառուցված M լեզվի հատուկ ֆունկցիայի միջոցով. Table.ToRecords. Այն ուղղակիորեն բանաձևի տողում կիրառելու համար ավելացրեք այս գործառույթը այնտեղ գտնվող քայլի կոդի մեջ Աղբյուր.

Դա էր ՝

Հետո:

Table.ToRecords ֆունկցիան ավելացնելուց հետո մեր աղյուսակի տեսքը կփոխվի՝ այն կվերածվի գրառումների ցանկի։ Անհատական ​​գրառումների բովանդակությունը կարելի է տեսնել դիտման պատուհանի ներքևում՝ սեղմելով ցանկացած բառի կողքին գտնվող բջջային ֆոնի վրա: Գրառում (բայց ոչ մի բառով!)

Բացի վերը նշվածից, իմաստ ունի ավելացնել ևս մեկ հարված՝ մեր ստեղծված ցուցակը քեշավորել (բուֆեր): Սա կստիպի Power Query-ին մեկ անգամ բեռնել մեր որոնման ցուցակը հիշողության մեջ և այն նորից չհաշվել, երբ մենք հետագայում մուտք գործենք այն փոխարինելու համար: Դա անելու համար մեր բանաձևը փաթաթեք մեկ այլ ֆունկցիայի մեջ. Ցուցակ.Բուֆեր:

Նման քեշավորումը արագության շատ նկատելի աճ կտա (մի քանի անգամ!) մեծ քանակությամբ նախնական տվյալների մաքրման դեպքում:

Սա ավարտում է ձեռնարկի պատրաստումը:

Մնում է սեղմել Գլխավոր – Փակել և բեռնել – Փակել և բեռնել… (Տուն — Փակել&Բեռնել — Փակել&Բեռնել..), ընտրեք տարբերակ Պարզապես կապ ստեղծեք (Միայն ստեղծել կապ) և վերադառնալ Excel:

Քայլ 2. Տվյալների աղյուսակի բեռնում

Այստեղ ամեն ինչ սովորական է. Ինչպես նախկինում տեղեկատուի դեպքում, մենք բարձրանում ենք աղյուսակի ցանկացած տեղ, սեղմում ենք ներդիրը Ամսաթիվ կոճակ Սեղանից / միջակայքից և մեր սեղանը Ամսաթիվ մտնում է Power Query-ի մեջ: Ավտոմատ ավելացված քայլ փոփոխված տեսակը (Փոխված տեսակը) կարող եք նաև հեռացնել՝

Դրա հետ կապված հատուկ նախապատրաստական ​​գործողություններ չեն պահանջվում, և մենք անցնում ենք ամենագլխավորին։

Քայլ 3. Կատարեք փոխարինումներ՝ օգտագործելով List.Acumulate ֆունկցիան

Եկեք հաշվարկված սյունակ ավելացնենք մեր տվյալների աղյուսակում՝ օգտագործելով հրամանը Սյունակի ավելացում – Պատվերով սյունակ (Ավելացնել սյունակ — Հատուկ սյունակ)և բացվող պատուհանում մուտքագրեք ավելացված սյունակի անունը (օրինակ՝ ուղղված հասցե) և մեր կախարդական գործառույթը Ցուցակ.Կուտակել:

Մնում է սեղմել OK – և մենք ստանում ենք սյունակ՝ կատարված փոխարինումներով.

Նշենք, որ.

• Քանի որ Power Query-ը մեծատառերի զգայուն է, նախավերջին տողում փոխարինում չկար, քանի որ գրացուցակում մենք ունենք «SPb», ոչ թե «SPb»:
• Եթե ​​աղբյուրի տվյալների մեջ կան միանգամից մի քանի ենթատողեր, որոնք պետք է փոխարինվեն (օրինակ, 7-րդ տողում դուք պետք է փոխարինեք և՛ «S-Pb»-ն և «Prospectus»-ը), ապա դա որևէ խնդիր չի ստեղծում (ի տարբերություն բանաձևերով փոխարինելու. նախորդ մեթոդը):
• Եթե ​​սկզբնական տեքստում փոխարինելու բան չկա (9-րդ տող), ապա սխալներ չեն լինում (ի տարբերություն, դարձյալ, բանաձևերով փոխարինման):

Նման խնդրանքի արագությունը շատ, շատ պարկեշտ է: Օրինակ, 5000 տող չափով նախնական տվյալների աղյուսակի համար այս հարցումը թարմացվել է մեկ վայրկյանից պակաս ժամանակում (առանց բուֆերացման, ի դեպ, մոտ 3 վայրկյան):

Ինչպես է աշխատում List.Acumulate ֆունկցիան

Սկզբունքորեն, սա կարող է լինել այս հոդվածի վերջը (ինձ համար գրեմ, իսկ դուք՝ կարդաք): Եթե ​​ցանկանում եք ոչ միայն կարողանալ, այլև հասկանալ, թե ինչպես է այն աշխատում «կապակի տակ», ապա ստիպված կլինեք մի փոքր ավելի խորանալ նապաստակի անցքի մեջ և գործ ունենալ List.Acumulate ֆունկցիայի հետ, որն արեց ամբողջ մեծածավալ փոխարինումը: աշխատիր մեզ համար:

Այս ֆունկցիայի շարահյուսությունը հետևյալն է.

=List.Acumulate(ցուցակ, սերմ, կուտակիչ)

որտեղ

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

Ընդհանուր առմամբ, Power Query-ում ֆունկցիաներ գրելու շարահյուսությունը հետևյալն է.

(արգումենտ1, արգումենտ2, … argumentN) => որոշ գործողություններ արգումենտներով

Օրինակ, գումարման գործառույթը կարող է ներկայացվել հետևյալ կերպ.

(a, b) => a + b

List.Accumulate-ի համար այս կուտակիչ ֆունկցիան ունի երկու պահանջվող արգումենտ (դրանք կարելի է անվանել ամեն ինչ, բայց սովորական անուններն են. էին и ընթացիկ, ինչպես այս գործառույթի պաշտոնական օգնության մեջ, որտեղ.

• էին – փոփոխական, որտեղ կուտակվում է արդյունքը (նրա սկզբնական արժեքը վերը նշվածն է սերմ)
  
• ընթացիկ – հաջորդ կրկնվող արժեքը ցանկից ցուցակ
  

Օրինակ, եկեք նայենք հետևյալ շինարարության տրամաբանության քայլերին.

=List.Acumulate({3, 2, 5}, 10, (վիճակ, ընթացիկ) => վիճակ + ընթացիկ)

Սա վերջին կուտակվածն է էին արժեքը մեր ցուցակն է: Արդյունքում կուտակեք գործառույթը և արդյունքները.

Եթե ​​մի փոքր ֆանտազիզացնում եք, ապա օգտագործելով List.Accumulate ֆունկցիան, կարող եք մոդելավորել, օրինակ, Excel ֆունկցիան CONCATENATE (Power Query-ում դրա անալոգը կոչվում է. Տեքստ. Միավորել) օգտագործելով արտահայտությունը.

Կամ նույնիսկ փնտրեք առավելագույն արժեքը (Excel-ի MAX ֆունկցիայի իմիտացիա, որը Power Query-ում կոչվում է Ցուցակ.Մաքս):

Այնուամենայնիվ, List.Accumulate-ի հիմնական առանձնահատկությունը ոչ միայն պարզ տեքստի կամ թվային ցուցակները որպես արգումենտ մշակելու ունակությունն է, այլ ավելի բարդ օբյեկտներ, օրինակ՝ ցուցակներ ցուցակներից կամ ցուցակներ գրառումներից (բարև, տեղեկատու:)

Եկեք նորից նայենք այն շինարարությանը, որն իրականացրել է փոխարինումը մեր խնդրի մեջ.

List.Acumulate(տեղեկատու, [Հասցեն], (վիճակ, ընթացիկ) => Տեքստ.Փոխարինել (վիճակ, ընթացիկ[Գտնել], ընթացիկ[Փոխարինել]) )

Ի՞նչ է իրականում կատարվում այստեղ:

1. Որպես սկզբնական արժեք (սերմ) սյունակից վերցնում ենք առաջին անշնորհք տեքստը [Հասցեն] մեր սեղանը. 199034, Սանկտ Պետերբուրգ, փող. Բերինգա, դ. 1
2. Այնուհետև List.Acumulate կրկնում է ցուցակի տարրերը մեկ առ մեկ – Ձեռնարկ. Այս ցանկի յուրաքանչյուր տարր իրենից ներկայացնում է գրառում, որը բաղկացած է մի զույգ դաշտերից՝ «Ինչ գտնել – ինչով փոխարինել» կամ, այլ կերպ ասած, գրացուցակի հաջորդ տողից:
3. Կուտակիչ ֆունկցիան դնում է փոփոխականի մեջ էին սկզբնական արժեքը (առաջին հասցե 199034, Սանկտ Պետերբուրգ, փող. Բերինգա, դ. 1) և դրա վրա կատարում է կուտակիչի ֆունկցիա՝ փոխարինման գործողություն՝ օգտագործելով ստանդարտ M-գործառույթը Տեքստ.Փոխարինել (Excel-ի SUBSTITUTE ֆունկցիայի անալոգը): Դրա շարահյուսությունը հետևյալն է.

   Text.Replace (բնօրինակ տեքստ, ինչ ենք փնտրում, ինչով ենք փոխարինում)

   և ահա մենք ունենք.

• էին մեր կեղտոտ հասցեն է, որը գտնվում է էին (հասնելով այնտեղ սերմ)
  
• ընթացիկ[Որոնում] - դաշտի արժեքը Գտնել ցուցակի հաջորդ կրկնվող մուտքից տեղեկատու, որը գտնվում է փոփոխականում ընթացիկ
• ընթացիկ[Փոխարինել] - դաշտի արժեքը Փոխարինեք ցուցակի հաջորդ կրկնվող մուտքից տեղեկատուպառկած ընթացիկ

Այսպիսով, յուրաքանչյուր հասցեի համար ամեն անգամ գործարկվում է գրացուցակի բոլոր տողերի թվարկման ամբողջական ցիկլը՝ [Find] դաշտի տեքստը փոխարինելով [Replace] դաշտի արժեքով։

Հուսով եմ, որ դուք հասկացաք գաղափարը 🙂

• Զանգվածային փոխարինել տեքստը ցուցակում՝ օգտագործելով բանաձևերը
  
• Կանոնավոր արտահայտություններ (RegExp) Power Query-ում