List.Accumate функц бүхий Power Query дотор текстийг бөөнөөр нь солих

Лавлах жагсаалтын дагуу текстийг хэрхэн хурдан, бөөнөөр нь томъёогоор солих вэ - бид үүнийг аль хэдийн ангилсан. Одоо үүнийг Power Query дээр хийхийг оролдъё.

Ихэнхдээ тохиолддог шиг гүйцэтгэх Энэ даалгавар нь тайлбарлахаас хамаагүй хялбар юм яагаад энэ нь ажилладаг, гэхдээ хоёуланг нь хийхийг хичээцгээе 🙂

Тиймээс бид энгийн мужуудаас гарын товчлолоор бүтээсэн хоёр "ухаалаг" динамик хүснэгттэй байна Ctrl+T эсвэл баг Нүүр хуудас - Хүснэгт хэлбэрээр форматлах (Нүүр хуудас - Хүснэгт хэлбэрээр форматлах):

Би эхний ширээ рүү залгалаа Өгөгдөл, хоёр дахь хүснэгт - Лавлахашиглах талбар Хүснэгтийн нэр (Хүснэгтийн нэр) таб байгуулагч (Дизайн).

Даалгавар: Хүснэгт дэх хаягуудыг солих Өгөгдөл баганаас бүх тохиолдлууд Олох Гарын авлага баганаас тэдгээрийн харгалзах зөв аналогууд руу Орлуулагч. Нүдэнд байгаа текстийн үлдсэн хэсэг нь хөндөгдөөгүй хэвээр байх ёстой.

Алхам 1. Лавлахыг Power Query руу ачаалж, жагсаалт болгон хувирга

Идэвхтэй нүдийг лавлагааны хүснэгтийн аль ч газар байрлуулсны дараа таб дээр дарна уу Өгөгдөл (Огноо)эсвэл таб дээр Эрчим хүчний асуулга (хэрэв танд Excel-ийн хуучин хувилбар байгаа бөгөөд та Power Query-г тусдаа таб дээр нэмэлт болгон суулгасан бол) товчлуур дээр Хүснэгтээс/мужаас (Хүснэгт/Мужаас).

Лавлагаа хүснэгтийг Power Query асуулгын засварлагч руу ачаалах болно:

Саад болохгүйн тулд автоматаар нэмсэн алхам өөрчлөгдсөн төрөл (Өөрчлөгдсөн төрөл) баруун талын самбарт ашигласан алхмуудыг аюулгүйгээр устгаж, зөвхөн алхамыг нь үлдээж болно Эх сурвалж (Эх сурвалж):

Одоо цаашдын хувиргалт, орлуулалт хийхийн тулд бид энэ хүснэгтийг жагсаалт (жагсаалт) болгон хувиргах хэрэгтэй.

Лирик ухралт

• Хүснэгт нь хэд хэдэн мөр, баганаас бүрдэх хоёр хэмжээст массив юм.
• Бичлэг (Бичлэг) – жишээ нь нэртэй хэд хэдэн талбар-элементүүдээс бүрдэх нэг хэмжээст массив-мөр [Нэр = “Маша”, Хүйс = “f”, Нас = 25]
  
• Жагсаалт – жишээ нь хэд хэдэн элементээс бүрдэх нэг хэмжээст массив багана {1, 2, 3, 10, 42} or { "Итгэл найдвар хайр" }
  

Асуудлаа шийдэхийн тулд бид үндсэн төрлийг сонирхох болно Жагсаалт.

Энд байгаа заль мэх нь Power Query дахь жагсаалтын зүйлүүд нь зөвхөн тоо эсвэл текстээс гадна бусад жагсаалт эсвэл бичлэгүүд байж болно. Энэ нь бүртгэл (бичлэг) -ээс бүрдсэн ийм төвөгтэй жагсаалтад (жагсаалтад) байгаа тул бид лавлахаа эргүүлэх хэрэгтэй. Power Query-ийн синтакс тэмдэглэгээнд (дөрвөлжин хаалт доторх оруулгууд, буржгар хаалт доторх жагсаалтууд) дараах байдалтай харагдана.

{

    [ Хай = "Гэгээн. Петербург", солих = "Санкт. Петербург"] ,

    [ Хай = "Гэгээн. Петербург", солих = "Санкт. Петербург"] ,

    [ Хай = "Петр", солих = "Гэгээн. Петербург"] ,

гэх мэт

}

Ийм хувиргалтыг Power Query-д суулгасан M хэлний тусгай функц ашиглан гүйцэтгэдэг. Хүснэгт.ToRecords. Үүнийг томьёоны мөрөнд шууд хэрэглэхийн тулд энэ функцийг алхамын код руу нэмнэ үү Эх сурвалж.

Байсан:

Дараа нь:

Table.ToRecords функцийг нэмсний дараа манай хүснэгтийн харагдах байдал өөрчлөгдөх бөгөөд энэ нь бичлэгийн жагсаалт болж хувирна. Бие даасан бичлэгийн агуулгыг харах самбарын доод хэсэгт дурын үгийн хажууд байгаа нүдний дэвсгэр дээр дарж харж болно. Бичлэг (гэхдээ нэг үгээр биш!)

Дээр дурдсан зүйлсээс гадна бидний үүсгэсэн жагсаалтыг кэш (буфер) болгохын тулд өөр нэг цохилт нэмэх нь зүйтэй юм. Энэ нь Power Query-г бидний хайлтын жагсаалтыг санах ойд нэг удаа ачаалах бөгөөд дараа нь солих гэж хандах үед дахин тооцоолохгүй байх болно. Үүнийг хийхийн тулд бидний томъёог өөр функцээр боож өгнө - Жагсаалт. Буфер:

Ийм кэш нь маш их хэмжээний анхны өгөгдлийг цэвэрлэхэд хурдыг мэдэгдэхүйц нэмэгдүүлэх болно (хэд хэдэн удаа!).

Ингэснээр гарын авлагыг бэлтгэх ажил дуусна.

Энэ нь дээр дарахад л үлддэг Нүүр хуудас – Хаах ба Ачаалах – Хаах болон Ачаалах… (Нүүр хуудас — Хаах&Ачаалах — Хаах&Ачаалах..), сонголтыг сонгоно уу Зүгээр л холболт үүсгэ (Зөвхөн холболт үүсгэх) мөн Excel рүү буцна уу.

Алхам 2. Өгөгдлийн хүснэгтийг ачаалж байна

Энд бүх зүйл улиг болсон. Лавлах номны нэгэн адил бид хүснэгтийн аль ч хэсэгт хүрч, таб дээр дарна уу Өгөгдөл товч Хүснэгтээс/Мужаас мөн бидний ширээ Өгөгдөл Power Query руу орно. Алхам автоматаар нэмэгдсэн өөрчлөгдсөн төрөл (Өөрчлөгдсөн төрөл) Та мөн устгаж болно:

Үүнтэй хамт бэлтгэлийн тусгай арга хэмжээ авах шаардлагагүй бөгөөд бид хамгийн чухал зүйл рүү шилждэг.

Алхам 3. List.Accumate функцийг ашиглан орлуулалт хийнэ

Командыг ашиглан өгөгдлийн хүснэгтдээ тооцоолсон багана нэмье Багана нэмэх - Тусгай багана (Багана нэмэх - Тусгай багана): болон нээгдэх цонхонд нэмсэн баганын нэрийг оруулна уу (жишээ нь, хаягийг зассан) болон бидний ид шидийн функц Жагсаалт. Хуримтлуулах:

Энэ нь дээр дарахад л үлддэг OK – мөн бид солигдсон баганыг авна:

Тэрийг тэмдэглэ:

• Power Query нь жижиг жижиг жижиг том үсгийн мэдрэмжтэй тул эцсийн өмнөх мөрөнд орлуулалт хийгдээгүй, учир нь бидний лавлахад "SPb" биш харин "SPb" байгаа.
• Хэрэв эх өгөгдөлд нэг дор орлуулах хэд хэдэн дэд мөр байгаа бол (жишээлбэл, 7-р мөрөнд "S-Pb" болон "Танилцуулга" хоёуланг нь солих шаардлагатай) энэ нь ямар ч асуудал үүсгэхгүй (томьёоноор солихоос ялгаатай нь). өмнөх арга).
• Хэрэв эх бичвэрт (9-р мөр) орлуулах зүйл байхгүй бол алдаа гарахгүй (дахин томьёогоор солихоос ялгаатай).

Ийм хүсэлтийн хурд нь маш, маш зохистой юм. Жишээлбэл, 5000 мөрийн хэмжээтэй анхны өгөгдлийн хүснэгтийн хувьд энэ асуулга нь секунд хүрэхгүй хугацаанд шинэчлэгдсэн (дашрамд хэлэхэд, буфер хийхгүйгээр 3 секунд орчим!)

List.Accumate функц хэрхэн ажилладаг

Зарчмын хувьд энэ нь энэ өгүүллийг төгсгөл болгож магадгүй юм (миний хувьд бичих, та унших). Хэрэв та "бүрээсний доор" хэрхэн ажилладагийг ойлгох төдийгүй, үүнийг ойлгохыг хүсч байвал туулайн нүх рүү бага зэрэг гүнзгий шумбаж, бүх бөөнөөр нь солих ажлыг хийсэн List.Accumulation функцтэй ажиллах хэрэгтэй болно. бидний төлөө ажилла.

Энэ функцийн синтакс нь:

=Жагсаалт. Хуримтлуулах(жагсаалт, үрийг, аккумлятор)

хаана

• жагсаалт нь бидний элементүүдийг давтаж байгаа жагсаалт юм. 
• үрийг - анхны төлөв
• аккумлятор – жагсаалтын дараагийн элемент дээр зарим үйлдлийг (математик, текст гэх мэт) гүйцэтгэж, боловсруулалтын үр дүнг тусгай хувьсагчид хуримтлуулдаг функц.
  

Ерөнхийдөө Power Query дээр функц бичих синтакс дараах байдалтай байна.

(аргумент1, аргумент2, … аргументN) => аргументтай зарим үйлдэл

Жишээлбэл, нийлбэрийн функцийг дараах байдлаар илэрхийлж болно.

(a, b) => a + b

List.Acculate-ийн хувьд энэ аккумлятор функц нь шаардлагатай хоёр аргументтай (тэдгээрийг юу ч гэж нэрлэж болно, гэхдээ ердийн нэрс нь төлөв и одоогийн, энэ функцийн албан ёсны тусламжийн нэгэн адил, энд:

• төлөв – үр дүн хуримтлагдсан хувьсагч (түүний анхны утга нь дээр дурдсан утга юм үрийг)
  
• одоогийн – жагсаалтаас дараагийн давтагдсан утга жагсаалт
  

Жишээлбэл, дараах барилгын логикийн алхамуудыг харцгаая.

=Жагсаалт. Хуримтлуулах({3, 2, 5}, 10, (төлөв, одоогийн) => төлөв + одоогийн)

Энэ бол хамгийн сүүлийн үеийн хуримтлал юм төлөв утга нь бидний Жагсаалт юм. Хуримтлуулах функц ба үр дүнд нь гаралт:

Хэрэв та бага зэрэг төсөөлж байвал List.Accumate функцийг ашиглан жишээ нь Excel-ийн CONCATENATE функцийг дуурайж болно (Power Query-д түүний аналогийг дуурайлган) Текст. нэгтгэх) илэрхийллийг ашиглан:

Эсвэл хамгийн их утгыг хайж олох боломжтой (Excel-ийн MAX функцийг дуурайсан бөгөөд үүнийг Power Query дээр дууддаг Жагсаалт. Макс):

Гэсэн хэдий ч List.Acculate-ийн гол онцлог нь зөвхөн энгийн текст эсвэл тоон жагсаалтыг аргумент болгон боловсруулах чадвар биш харин илүү төвөгтэй объектуудыг - жишээ нь жагсаалтаас жагсаалт эсвэл бүртгэлээс жагсаалт (сайн уу, лавлах!)

Бидний асуудалд орлуулалт хийсэн барилгын ажлыг дахин харцгаая.

Жагсаалт. Хуримтлуулах(Лавлах, [Хаяг], (төлөв, одоогийн) => Текст. Солих (төлөв, одоогийн[Олох], одоогийн[Орлуулах]) )

Энд үнэхээр юу болоод байна вэ?

1. Анхны утга болгон (үрийг) бид баганаас эхний болхи текстийг авдаг [Хаяг] манай ширээ: 199034, Санкт-Петербург, гудамж. Беринга, д. 1
2. Дараа нь List.Accumate жагсаалтын элементүүдийг нэг нэгээр нь давтана – Гарын авлага. Энэ жагсаалтын элемент бүр нь "Юу олох вэ - Юугаар солих вэ" гэсэн хос талбар, өөрөөр хэлбэл лавлахын дараагийн мөрөөс бүрдэх бичлэг юм.
3. Аккумляторын функц нь хувьсагч руу оруулдаг төлөв анхны утга (эхний хаяг 199034, Санкт-Петербург, гудамж. Беринга, д. 1) ба үүн дээр аккумляторын функцийг гүйцэтгэдэг - стандарт M-функцийг ашиглан солих үйл ажиллагаа Текст. Солих (Excel-ийн SUBSTITUTE функцтэй адил). Түүний синтакс нь:

   Text.Replace( эх бичвэр, бидний хайж байгаа зүйл, юугаар сольж байгаа вэ)

   мөн бид энд байна:

• төлөв оршдог бидний бохир хаяг юм төлөв (тэнд очих үрийг)
  
• одоогийн[Хайлт] - талбарын утга Олох жагсаалтын дараагийн давтагдсан оруулгаас Лавлах, хувьсагчид оршдог одоогийн
• одоогийн[Орлуулах] - талбарын утга Орлуулагч жагсаалтын дараагийн давтагдсан оруулгаас Лавлаххэвтэж байна одоогийн

Тиймээс хаяг бүрийн хувьд лавлах дахь бүх мөрийг тоолох бүрэн мөчлөгийг бүртгэж, [Find] талбарын текстийг [Replace] талбарын утгаар солино.

Та санаагаа авсан гэж найдаж байна 🙂

• Томьёо ашиглан жагсаалт дахь текстийг бөөнөөр нь солих
  
• Power Query дахь ердийн илэрхийллүүд (RegExp).