List.Accumute funksiyasi bilan Power Query-da matnni ommaviy almashtirish

Ma'lumotnomalar ro'yxati bo'yicha matnni qanday tez va ommaviy ravishda formulalar bilan almashtirish mumkin - biz uni allaqachon saralab oldik. Endi buni Power Query-da bajarishga harakat qilaylik.

Tez-tez sodir bo'lganidek amalga oshirish bu vazifa tushuntirishdan ko'ra ancha oson nima uchun u ishlaydi, lekin ikkalasini ham qilishga harakat qilaylik 🙂

Shunday qilib, bizda oddiy diapazonlardan klaviatura yorlig'i bilan yaratilgan ikkita "aqlli" dinamik jadval mavjud Ctrl+T yoki jamoa Bosh sahifa - Jadval sifatida formatlash (Uy — Jadval sifatida formatlash):

Men birinchi stolga qo'ng'iroq qildim ma'lumotlar, ikkinchi jadval - Directorymaydondan foydalanish Jadval nomi (Jadval nomi) tab konstruktor (Dizayn).

Vazifa: jadvaldagi manzillarni almashtiring ma'lumotlar ustundagi barcha hodisalar Topmoq Qo'llanma ustundan ularning mos keladigan to'g'ri o'xshashlariga O'zgartirish. Hujayralardagi matnning qolgan qismi tegmasdan qolishi kerak.

Qadam 1. Katalogni Power Query-ga yuklang va uni ro'yxatga aylantiring

Faol hujayrani mos yozuvlar jadvalining istalgan joyiga o'rnatgandan so'ng, yorliqni bosing ma'lumotlar (Sana)yoki yorliqda Quvvat so'rovi (agar sizda Excelning eski versiyasi bo'lsa va Power Query-ni alohida yorliqda plagin sifatida o'rnatgan bo'lsangiz) tugmachada Jadvaldan/diapazondan (Jadval/diapazondan).

Malumot jadvali Power Query so'rovlar muharririga yuklanadi:

Xalaqit bermaslik uchun avtomatik ravishda qo'shilgan qadam o'zgartirilgan turi (O'zgartirilgan tur) o'ng panelda qo'llaniladigan qadamlar faqat qadamni qoldirib, xavfsiz tarzda o'chirilishi mumkin manba (Manba):

Endi keyingi o'zgartirishlar va almashtirishlarni amalga oshirish uchun biz ushbu jadvalni ro'yxatga (ro'yxat) aylantirishimiz kerak.

Lirik chekinish

• stol bir necha satr va ustunlardan tashkil topgan ikki o'lchovli massivdir.
• Rekord (rekord) – bir o‘lchovli massiv qatori, masalan, nomlari bo‘lgan bir nechta maydon-elementlardan iborat [Ism = "Masha", jins = "f", Yosh = 25]
  
• ro'yxat - bir o'lchovli massiv-ustun, masalan, bir nechta elementlardan iborat {1, 2, 3, 10, 42} or { "Imon umid sevgi" }
  

Muammoni hal qilish uchun biz birinchi navbatda turga qiziqamiz ro'yxat.

Bu erda hiyla-nayrang shundaki, Power Query-dagi ro'yxat elementlari nafaqat oddiy raqamlar yoki matn, balki boshqa ro'yxatlar yoki yozuvlar ham bo'lishi mumkin. Aynan shunday qiyin ro'yxatda (ro'yxat), yozuvlardan (yozuvlardan) iborat bo'lib, biz katalogimizni aylantirishimiz kerak. Power Query sintaktik yozuvida (kvadrat qavs ichidagi yozuvlar, jingalak qavs ichidagi ro'yxatlar) bu quyidagicha ko'rinadi:

{

    [ Toping = “St. Sankt-Peterburg", almashtiring = "Sankt. Peterburg"] ,

    [ Toping = “St. Sankt-Peterburg", almashtiring = "Sankt. Peterburg"] ,

    [ Toping = "Piter", almashtiring = "St. Peterburg"] ,

va hokazo

}

Bunday o'zgartirish Power Query-ga o'rnatilgan M tilining maxsus funktsiyasi yordamida amalga oshiriladi - Jadval.To Records. Uni to'g'ridan-to'g'ri formulalar qatorida qo'llash uchun ushbu funktsiyani u yerdagi qadam kodiga qo'shing manba.

Bu bo'lgandi:

Keyin:

Table.ToRecords funksiyasini qo'shgandan so'ng, jadvalimizning ko'rinishi o'zgaradi - u yozuvlar ro'yxatiga aylanadi. Har qanday so'z yonidagi hujayra fonida bosish orqali alohida yozuvlarning mazmunini ko'rish panelining pastki qismida ko'rish mumkin. rekord (lekin bir so'z bilan emas!)

Yuqoridagilarga qo'shimcha ravishda, bizning yaratilgan ro'yxatimizni keshlash (buferlash) uchun yana bitta zarba qo'shish mantiqan. Bu Power Query-ni qidiruv ro'yxatini xotiraga bir marta yuklashga majbur qiladi va biz uni almashtirish uchun keyinroq foydalansak, uni qayta hisoblamaydi. Buning uchun formulamizni boshqa funktsiyaga o'rang - Roʻyxat.Bufer:

Bunday keshlash katta hajmdagi boshlang'ich ma'lumotlarning tozalanishi bilan tezlikni sezilarli darajada oshiradi (bir necha marta!).

Bu qo'llanmani tayyorlashni yakunlaydi.

Bosish qoladi Bosh sahifa – Yopish va yuklash – Yopish va yuklash… (Uy — Yopish va yuklash — Yopish va yuklash..), variantni tanlang Faqat ulanishni yarating (Faqat ulanish yaratish) va Excelga qayting.

2-qadam. Ma'lumotlar jadvalini yuklash

Bu erda hamma narsa oddiy. Ma'lumotnomada bo'lgani kabi, biz jadvalning istalgan joyiga chiqamiz, yorliqni bosing ma'lumotlar tugma Jadval/diapazondan va bizning stolimiz ma'lumotlar Power Query-ga kiradi. Avtomatik qo'shilgan qadam o'zgartirilgan turi (O'zgartirilgan tur) siz ham olib tashlashingiz mumkin:

U bilan hech qanday maxsus tayyorgarlik ishlarini bajarish talab qilinmaydi va biz eng muhim narsaga o'tamiz.

3-qadam. List.Accumate funksiyasidan foydalanib almashtirishlarni amalga oshiring

Buyruq yordamida ma'lumotlar jadvalimizga hisoblangan ustun qo'shamiz Ustun qo'shish - Maxsus ustun (Ustun qo'shish - Maxsus ustun): va ochilgan oynada qo'shilgan ustun nomini kiriting (masalan, tuzatilgan manzil) va bizning sehrli vazifamiz Ro'yxat. To'plash:

Bosish qoladi OK - va biz almashtirilgan ustunni olamiz:

Yozib oling:

• Power Query katta-kichik harflarga sezgir bo'lganligi sababli, oxirgi qatorda hech qanday almashtirish yo'q, chunki katalogda bizda "SPb" emas, "SPb" mavjud.
• Agar manba ma'lumotlarida bir vaqtning o'zida almashtirish uchun bir nechta pastki qatorlar mavjud bo'lsa (masalan, 7-qatorda siz ikkala "S-Pb" va "Prospekt" ni almashtirishingiz kerak), bu hech qanday muammo tug'dirmaydi (formulalar bilan almashtirishdan farqli o'laroq). oldingi usul).
• Agar manba matnida (9-qator) almashtirish uchun hech narsa bo'lmasa, unda hech qanday xatolik yuzaga kelmaydi (yana formulalar bilan almashtirishdan farqli o'laroq).

Bunday so'rovning tezligi juda va juda munosib. Masalan, 5000 satr o'lchamli dastlabki ma'lumotlar jadvali uchun bu so'rov bir soniyadan kamroq vaqt ichida yangilandi (buferlashsiz, aytmoqchi, taxminan 3 soniya!)

List.Accumate funksiyasi qanday ishlaydi

Asosan, bu maqolaning oxiri bo'lishi mumkin (men uchun yozish va siz o'qish uchun). Agar siz nafaqat qodir bo'lishni, balki uning "kaput ostida" qanday ishlashini ham tushunishni istasangiz, quyon teshigiga biroz chuqurroq sho'ng'ishingiz va barcha ommaviy almashtirishni amalga oshirgan List.Accmulate funktsiyasi bilan shug'ullanishingiz kerak bo'ladi. biz uchun ishlang.

Ushbu funktsiyaning sintaksisi:

=Roʻyxat.Toʻplash(ro'yxat, urug'lar, akkumulyator)

qayerda

• ro'yxat elementlarini biz takrorlayotgan ro'yxat. 
• urug'lar - boshlang'ich holati
• akkumulyator – ro‘yxatning keyingi elementida qandaydir amallarni (matematik, matn va boshqalar) bajaradigan va qayta ishlash natijasini maxsus o‘zgaruvchida to‘playdigan funksiya.
  

Umuman olganda, Power Query-da funksiyalarni yozish sintaksisi quyidagicha ko'rinadi:

(argument1, argument2, … argumentN) => argumentlar bilan ba'zi harakatlar

Masalan, yig'ish funktsiyasi quyidagicha ifodalanishi mumkin:

(a, b) => a + b

List.Acculate uchun ushbu akkumulyator funksiyasi ikkita talab qilinadigan argumentga ega (ularni har qanday nomlash mumkin, lekin odatiy nomlar Davlat и hozirgi, ushbu funktsiya uchun rasmiy yordamda bo'lgani kabi, bu erda:

• Davlat - natija to'plangan o'zgaruvchi (uning boshlang'ich qiymati yuqorida aytib o'tilgan qiymatdir urug'lar)
  
• hozirgi – roʻyxatdagi keyingi takrorlangan qiymat ro'yxat
  

Masalan, quyidagi qurilish mantiqining bosqichlarini ko'rib chiqamiz:

=Roʻyxat.Toʻplash({3, 2, 5}, 10, (holat, joriy) => holat + joriy)

Bu oxirgi to'plangan Davlat qiymat bizning List.Accumute funksiyamiz va natijada chiqadi:

Agar siz biroz xayol qilsangiz, List.Accumute funksiyasidan foydalanib, masalan, Excelning CONCATENATE funksiyasini taqlid qilishingiz mumkin (Power Query-da uning analogi deyiladi. Matn. Birlashtirish) ifoda yordamida:

Yoki hatto maksimal qiymatni qidiring (Excelning MAX funktsiyasiga taqlid qilish, bu Power Query da chaqiriladi Roʻyxat. Maks):

Biroq, List.Acculate-ning asosiy xususiyati argument sifatida nafaqat oddiy matn yoki raqamli ro'yxatlarni, balki murakkabroq ob'ektlarni - masalan, ro'yxatlardan-ro'yxatlarni yoki yozuvlardan-ro'yxatlarni (salom, Katalog!) qayta ishlash qobiliyatidir.

Keling, muammomizda almashtirishni amalga oshirgan qurilishni yana ko'rib chiqaylik:

Roʻyxat. Toʻplash(Directory, [Manzil], (holat, joriy) => Matn.Almashtirish(holat, joriy[Topish], joriy[almashtirish]) )

Haqiqatan ham bu erda nima bo'lyapti?

1. Dastlabki qiymat sifatida (urug'lar) ustundan birinchi bema'ni matnni olamiz [Manzil] bizning stolimiz: 199034, Sankt-Peterburg, ko'ch. Beringa, d. 1
2. Keyin List.Accumate roʻyxat elementlarini birma-bir takrorlaydi – Qo'llanma. Ushbu ro'yxatning har bir elementi "Nima topish kerak - nima bilan almashtirish" yoki boshqacha qilib aytganda, katalogdagi keyingi qatordan iborat juftlikdan iborat yozuvdir.
3. Akkumulyator funksiyasi o'zgaruvchiga qo'yadi Davlat boshlang'ich qiymat (birinchi manzil 199034, Sankt-Peterburg, ko'ch. Beringa, d. 1) va unda akkumulyator vazifasini bajaradi - standart M-funktsiyasidan foydalangan holda almashtirish operatsiyasi Matn.Almashtirish (Excelning SUBSTITUTE funksiyasiga o'xshash). Uning sintaksisi:

   Matn.Oʻzgartirish (asl matn, biz nimani qidirmoqdamiz, nima bilan almashtiramiz)

   va bizda:

• Davlat ichida joylashgan bizning iflos manzilimiz Davlat (u yerdan borish urug'lar)
  
• joriy[Qidiruv] - maydon qiymati Topmoq ro'yxatning keyingi takrorlangan yozuvidan Directory, o'zgaruvchida joylashgan hozirgi
• joriy[almashtirish] - maydon qiymati O'zgartirish ro'yxatning keyingi takrorlangan yozuvidan Directoryichida yotgan hozirgi

Shunday qilib, har bir manzil uchun katalogdagi barcha satrlarni sanab o'tishning to'liq sikli har safar bajariladi, [Topish] maydonidagi matnni [Almashtirish] maydonidagi qiymat bilan almashtiriladi.

Umid qilamanki, siz fikrni tushundingiz 🙂

• Formulalar yordamida ro'yxatdagi matnni ommaviy ravishda almashtirish
  
• Power Query-da muntazam ifodalar (RegExp).