List.Accumate funksiyası ilə Power Query-də toplu mətnin dəyişdirilməsi

İstinad siyahısına uyğun olaraq mətni necə tez və toplu şəkildə düsturlarla əvəz etmək olar – biz artıq onu sıralamışıq. İndi gəlin bunu Power Query-də etməyə çalışaq.

Tez-tez olduğu kimi həyata bu vəzifə izah etməkdən çox asandır niyə işləyir, amma hər ikisini etməyə çalışaq 🙂

Beləliklə, klaviatura qısayolu ilə adi diapazonlardan yaradılmış iki "ağıllı" dinamik cədvəlimiz var Ctrl+T və ya komanda Ev - Cədvəl kimi formatlayın (Ev - Cədvəl kimi format):

Birinci masaya zəng etdim Tarix, ikinci cədvəl - Directorysahəsində istifadə Cədvəlin adı (Cədvəl adı) nişanı konstruktor (Dizayn).

Tapşırıq: cədvəldəki ünvanları dəyişdirin Tarix sütundakı bütün hadisələr Tapmaq Stolüstü kitab sütundan onların müvafiq düzgün analoqlarına Əvəz. Hüceyrələrdə mətnin qalan hissəsi toxunulmaz qalmalıdır.

Addım 1. Kataloqu Power Query-ə yükləyin və onu siyahıya çevirin

Aktiv xananı istinad cədvəlində istənilən yerə təyin etdikdən sonra nişanı vurun Tarix (Tarix)və ya tabda Güc sorğusu (əgər sizdə Excel-in köhnə versiyası varsa və Power Query-ni ayrıca tabda əlavə kimi quraşdırmısınızsa) düymədə Cədvəldən/aralıqdan (Cədvəldən/Aralıqdan).

İstinad cədvəli Power Query sorğu redaktoruna yüklənəcək:

Müdaxilə etməmək üçün avtomatik olaraq əlavə edilən addım dəyişdirilmiş növü (Dəyişən Növ) sağ paneldə tətbiq olunan addımlar təhlükəsiz şəkildə silinə bilər, yalnız addım qalır mənbə (Mənbə):

İndi əlavə transformasiya və dəyişdirmələri yerinə yetirmək üçün bu cədvəli siyahıya (siyahıya) çevirməliyik.

Lirik təxribat

• Masa bir neçə sətir və sütundan ibarət ikiölçülü massivdir.
• Rekord (rekord) – məsələn, adları olan bir neçə sahə-elementdən ibarət birölçülü massiv-sətir [Ad = “Maşa”, Cins = “f”, Yaş = 25]
  
• siyahı – məsələn, bir neçə elementdən ibarət birölçülü massiv-sütun {1, 2, 3, 10, 42} or { "İman ümid sevgi" }
  

Problemimizi həll etmək üçün biz ilk növbədə tiplə maraqlanacağıq siyahı.

Buradakı hiylə ondan ibarətdir ki, Power Query-dəki siyahı elementləri yalnız sıradan nömrələr və ya mətn deyil, həm də digər siyahılar və ya qeydlər ola bilər. Məhz qeydlərdən (qeydlərdən) ibarət belə çətin siyahıda (siyahı) qovluğumuzu çevirməliyik. Power Query sintaktik notasiyasında (kvadrat mötərizədə yazılar, buruq mötərizədə siyahılar) bu belə görünür:

{

    [ Tap = “St. Sankt-Peterburq", Əvəz et = "Sankt. Peterburq”] ,

    [ Tap = “St. Sankt-Peterburq", Əvəz et = "Sankt. Peterburq”] ,

    [ Tap = "Peter", Əvəz et = "Müqəddəs. Peterburq”] ,

s.

}

Belə bir transformasiya Power Query-də quraşdırılmış M dilinin xüsusi funksiyasından istifadə etməklə həyata keçirilir – Cədvəl.ToRecords. Onu birbaşa düstur sətrinə tətbiq etmək üçün bu funksiyanı oradakı addım koduna əlavə edin mənbə.

Bu idi:

Sonra:

Table.ToRecords funksiyasını əlavə etdikdən sonra cədvəlimizin görünüşü dəyişəcək – o, qeydlər siyahısına çevriləcək. Fərdi qeydlərin məzmunu hər hansı bir sözün yanında xana fonunda klikləməklə görünüş panelinin aşağı hissəsində görünə bilər. rekord (lakin bir sözlə deyil!)

Yuxarıda göstərilənlərə əlavə olaraq, daha bir vuruş əlavə etmək məqsədəuyğundur - yaradılmış siyahımızı keş (bufer) üçün. Bu, Power Query-ni axtarış siyahımızı yaddaşa bir dəfə yükləməyə məcbur edəcək və sonra onu əvəz etmək üçün daxil olduğumuz zaman onu yenidən hesablamayacağıq. Bunu etmək üçün düsturumuzu başqa bir funksiyaya sarın - Siyahı.Bufer:

Bu cür keşləmə sürətdə çox nəzərə çarpan bir artım verəcək (bir neçə dəfə!) Böyük miqdarda ilkin məlumatların təmizlənməsi ilə.

Bu, kitabçanın hazırlanmasını tamamlayır.

Klikləmək qalır Ev – Bağlayın və Yükləyin – Bağlayın və Yükləyin… (Ev — Bağla&Yüklə — Bağla&Yüklə..), seçim seçin Sadəcə əlaqə yaradın (Yalnız əlaqə yaradın) və Excel-ə qayıdın.

Addım 2. Məlumat cədvəlinin yüklənməsi

Burada hər şey sadədir. Əvvəlki məlumat kitabçasında olduğu kimi, cədvəlin istənilən yerinə qalxırıq, nişanı vurun Tarix düyməsini Cədvəldən/Aralıqdan və masamız Tarix Power Query-ə daxil olur. Avtomatik əlavə edilmiş addım dəyişdirilmiş növü (Dəyişən Növ) siz də silə bilərsiniz:

Bununla heç bir xüsusi hazırlıq hərəkətləri tələb olunmur və biz ən vacib şeyə keçirik.

Addım 3. List.Accumate funksiyasından istifadə edərək dəyişdirmələri yerinə yetirin

Komandadan istifadə edərək verilənlər cədvəlimizə hesablanmış sütun əlavə edək Sütun əlavə etmək - Fərdi Sütun (Sütun əlavə et - Fərdi sütun): və açılan pəncərədə əlavə edilmiş sütunun adını daxil edin (məsələn, düzəldilmiş ünvan) və sehrli funksiyamız Siyahı.Yığmaq:

Klikləmək qalır OK – və biz dəyişdirmələrlə bir sütun alırıq:

Qeyd:

• Power Query hərflərə həssas olduğundan, sondan əvvəlki sətirdə heç bir dəyişiklik baş vermədi, çünki kataloqda "SPb" yox, "SPb" var.
• Mənbə məlumatında bir anda dəyişdiriləcək bir neçə alt sətir varsa (məsələn, 7-ci sətirdə həm “S-Pb”, həm də “Prospekt”i əvəz etmək lazımdır), bu, heç bir problem yaratmır (düsturlarla əvəz etməkdən fərqli olaraq). əvvəlki üsul).
• Mənbə mətndə (9-cu sətir) əvəz ediləcək heç nə yoxdursa, onda heç bir səhv baş vermir (yenidən düsturlarla əvəzlənmədən fərqli olaraq).

Belə bir sorğunun sürəti çox, çox layiqdir. Məsələn, 5000 sətir ölçüsü olan ilkin məlumat cədvəli üçün bu sorğu bir saniyədən az müddətdə yeniləndi (buferləşdirmədən, yeri gəlmişkən, təxminən 3 saniyə!)

List.Accumate funksiyası necə işləyir

Prinsipcə, bu məqalənin sonu ola bilər (mənim yazmağım, sənin oxumağım üçün). Əgər siz nəinki bacarmaq, həm də onun “kapağın altında” necə işlədiyini başa düşmək istəyirsinizsə, onda siz dovşan dəliyinə bir az daha dərinə enməli və bütün toplu əvəzetməni həyata keçirən List.Accmulate funksiyası ilə məşğul olmalı olacaqsınız. bizim üçün işləyin.

Bu funksiyanın sintaksisi:

=Siyahı.Yığmaq(siyahı, toxum, akkumulyator)

hara

• siyahı elementlərini təkrarladığımız siyahıdır. 
• toxum - ilkin vəziyyət
• akkumulyator – siyahının növbəti elementi üzərində hansısa əməliyyatı (riyazi, mətn və s.) yerinə yetirən və emal nəticəsini xüsusi dəyişəndə ​​toplayan funksiya.
  

Ümumiyyətlə, Power Query-də funksiyaların yazılması sintaksisi belə görünür:

(arqument1, arqument2, … arqumentN) => arqumentlərlə bəzi hərəkətlər

Məsələn, toplama funksiyası aşağıdakı kimi təqdim edilə bilər:

(a, b) => a + b

List.Acculate üçün bu akkumulyator funksiyasının iki tələb olunan arqumenti var (onları hər hansı adlandırmaq olar, lakin adi adlar idi и cari, bu funksiya üçün rəsmi yardımda olduğu kimi, burada:

• idi – nəticənin yığıldığı dəyişən (onun ilkin dəyəri yuxarıda qeyd olunan dəyərdir toxum)
  
• cari – siyahıdan növbəti təkrarlanan dəyər siyahı
  

Məsələn, aşağıdakı konstruksiyanın məntiqinin addımlarına nəzər salaq:

=Siyahı.Yığmaq({3, 2, 5}, 10, (dövlət, cari) => vəziyyət + cari)

Bu, ən son yığılandır idi dəyər List.Accumate funksiyamızdır və nəticədə çıxışlar:

Bir az fantaziya etsəniz, List.Accumate funksiyasından istifadə edərək, məsələn, Excel-in CONCATENATE funksiyasını simulyasiya edə bilərsiniz (Power Query-də onun analoqu adlanır) Mətn.Birləşdirin) ifadəsindən istifadə edərək:

Və ya hətta maksimum dəyəri axtarın (Power Query-də adlandırılan Excel-in MAX funksiyasının təqlidi). Siyahı.Maks):

Bununla belə, List.Acculate-in əsas xüsusiyyəti təkcə sadə mətn və ya rəqəmli siyahıları arqument kimi deyil, daha mürəkkəb obyektləri – məsələn, siyahılardan siyahıları və ya qeydlərdən siyahıları (salam, Directory!) emal etmək qabiliyyətidir.

Problemimizdə dəyişdirməni həyata keçirən tikintiyə yenidən baxaq:

Siyahı.Yığmaq(Directory, [Ünvan], (dövlət, cari) => Mətn. Əvəz (vəziyyət, cari[Tap], cari[Əvəz etmək]) )

Burada həqiqətən nə baş verir?

1. İlkin dəyər kimi (toxum) sütundan ilk yöndəmsiz mətni götürürük [Ünvan] masamız: 199034, Sankt-Peterburq, küç. Berinqa, d. 1
2. Sonra List.Accumute siyahının elementləri üzərində bir-bir təkrarlanır – Stolüstü kitab. Bu siyahının hər bir elementi “Nə tapmaq lazımdır – Nə ilə əvəz edilməlidir” bir cüt sahəsindən və ya başqa sözlə, kataloqdakı növbəti sətirdən ibarət qeyddir.
3. Akkumulyator funksiyası dəyişənə qoyur idi ilkin dəyər (ilk ünvan 199034, Sankt-Peterburq, küç. Berinqa, d. 1) və onun üzərində akkumulyator funksiyasını yerinə yetirir - standart M-funksiyasından istifadə edərək dəyişdirmə əməliyyatı Mətn.Əvəz et (Excel-in SUBSTITUTE funksiyasının analoqu). Onun sintaksisi belədir:

   Text.Replace (orijinal mətn, nə axtarırıq, nə ilə əvəz edirik)

   və burada bizdə:

• idi içində olan çirkli ünvanımızdır idi (oradan gəlmək toxum)
  
• cari[Axtar] - sahə dəyəri Tapmaq siyahının növbəti təkrarlanan girişindən Directory, dəyişəndə ​​yerləşir cari
• cari[Əvəz et] - sahə dəyəri Əvəz siyahının növbəti təkrarlanan girişindən Directoryyatan cari

Beləliklə, hər bir ünvan üçün kataloqdakı bütün sətirlərin tam sayılması dövrü hər dəfə yerinə yetirilir, [Tap] sahəsindəki mətn [Əvəz] sahəsindəki qiymətlə əvəz olunur.

Ümid edirəm ki, fikriniz var 🙂

• Düsturlardan istifadə edərək siyahıdakı mətni toplu şəkildə əvəz edin
  
• Power Query-də müntəzəm ifadələr (RegExp).