Агуулга
Хэрэв та ердийн хэллэгийг бага зэрэг мэддэг бол тэдгээрийг сурталчлах шаардлагагүй болно. Хэрэв та энэ сэдвийг төдийлөн сайн мэдэхгүй бол ердийн хэллэгүүд (Энгийн илэрхийлэл = RegExp = "regexps" = "regulars") нь тусгай тэмдэгт, дүрмийг ашиглан текстээс шаардлагатай дэд мөрүүдийг хайж олох хэл юм. эсвэл өөр бичвэрээр сольсон. Энэ бол тексттэй ажиллах бусад бүх арга барилаас хамаагүй илүү хүчирхэг, үзэсгэлэнтэй хэрэгсэл юм.
Энгийн макро ашиглан Excel-д тогтмол илэрхийллийн дэмжлэгийг хэрхэн нэмэх талаар би аль хэдийн дэлгэрэнгүй, амьдралаас жишээ болгон тайлбарласан - хэрэв та энэ нийтлэлийг уншаагүй бол үргэлжлүүлэхээсээ өмнө уншихыг зөвлөж байна. Та маш олон шинэ зүйлийг нээх болно, би баталгаатай 🙂
Гэсэн хэдий ч асуулт нээлттэй хэвээр байна - Power Query дээр ердийн илэрхийлэл ашиглах чадварыг хэрхэн нэмэх вэ? Power Query нь мэдээжийн хэрэг дангаараа сайн бөгөөд тексттэй (зүсэх, наах, цэвэрлэх гэх мэт) маш их зүйлийг хийж чадна, гэхдээ хэрэв та үүнийг ердийн хэллэгийн хүчээр давж чадвал энэ нь зүгээр л бөмбөг болно.
Харамсалтай нь Power Query-д RegExps-тэй ажиллах үндсэн функц байхгүй бөгөөд Microsoft-ын албан ёсны тусламж, техникийн дэмжлэг энэ асуултад сөрөг хариу өгдөг. Гэсэн хэдий ч, энэ хязгаарлалтыг тойрч гарах арга зам бий 🙂
Аргын мөн чанар
Гол санаа нь гутаахад хялбар байдаг.
Суурилуулсан Power Query боломжуудын жагсаалтад функц байдаг Web.Page. Microsoft-ын албан ёсны тусламжийн сайт дээрх энэ функцийн тайлбар нь маш товч юм:
Орчуулбал, "HTML баримт бичгийн агуулгыг бүрэлдэхүүн хэсгүүдийн бүтцэд нь хуваасан, мөн шошгыг устгасны дараа баримт бичгийн бүрэн дүрс болон түүний биеийн дүрсийг буцаана." Үнэнийг хэлэхэд ийм тайлбар.
Ихэвчлэн энэ функцийг вэбээс өгөгдөл импортлоход ашигладаг бөгөөд жишээлбэл, таб дээрээс сонгох үед автоматаар орлуулдаг. Өгөгдөл тушаал Интернетээс (Өгөгдөл — вэбээс). Бид функцэд вэб хуудсыг аргумент болгон өгдөг бөгөөд энэ нь өмнө нь бүх шошгыг цэвэрлэсний дараа агуулгыг бидэнд хүснэгт хэлбэрээр буцааж өгдөг.
Тусламжийн хэлээгүй зүйл бол HTML тэмдэглэгээний хэлнээс гадна үйл ажиллагаа Web.Page JavaScript скриптүүдийг дэмждэг, энэ нь одоо Интернет дэх вэбсайтуудад хаа сайгүй байдаг. Мөн JavaScript нь эргээд тогтмол илэрхийллүүдтэй ажиллах чадвартай байсан бөгөөд RegExps-д зориулагдсан функцуудтай! Тиймээс Power Query-д ердийн илэрхийлэлүүдийг хэрэгжүүлэхийн тулд бид Power Query-ийн бүх ажлыг гүйцэтгэх жижиг JavaScript програмд аргумент болгон Web.Page функцуудыг оруулах шаардлагатай болно.
Энэ нь цэвэр JavaScript дээр ямар харагддаг
Интернет дээр JavaScript дээр ердийн илэрхийлэлтэй ажиллах талаар маш олон дэлгэрэнгүй заавар байдаг (жишээлбэл, нэг, хоёр).
Товч бөгөөд хялбаршуулсан байдлаар JavaScript код дараах байдалтай харагдана.
Энд:
- var str = 'Хиамны 123 ба 789 тоот төлбөрийг төлөх'; - хувьсагч үүсгэх гудамж мөн үүнд бидний дүн шинжилгээ хийх эх бичвэрийг оноо.
- var загвар = /d+/gi; – тогтмол илэрхийлэл үүсгээд хувьсагчид оруулна хэв маяг.
Илэрхийлэл нь ташуу зураасаар (/) эхэлдэг.
Энд байгаа илэрхийлэл нь өөрөө юм d+ ямар ч тооны дарааллыг илэрхийлнэ.
Илэрхийллийн дараах бутархайгаар дамжуулан хайлтын нэмэлт параметрүүд (өөрчлөгч) байдаг - тэдгээрийг ямар ч дарааллаар зааж өгч болно.
- g – гэдэг нь дэлхийн хайлт гэсэн үг, өөрөөр хэлбэл тохирохыг олсны дараа та зогсох ёсгүй, харин текстийн төгсгөл хүртэл хайлтаа үргэлжлүүлээрэй. Хэрэв энэ тохируулагчийг тохируулаагүй бол манай скрипт зөвхөн эхний тохирлыг буцаана (123)
- i – үсгийг харгалзахгүйгээр хайх
- m – олон мөрт хайлт (эх текстийг хэд хэдэн мөрөнд хуваасан үед ашигладаг)
- var үр дүн = str.match(загвар).нэгдэх(';'); - эх бичвэрээс хайлт хийх (гудамж) өгөгдсөн тогтмол илэрхийллээр (хэв маяг) үр дүнг хувьсагчид оруулна үр дүн, командыг ашиглан цэг таслалтай холбоно нэгдэх
- баримт бичиг.бичих(үр дүн); – үр дүнгийн хувьсагчийн агуулгыг харуулах
Мөн JavaScript дахь текстийн мөрүүд (тогтмол илэрхийллээс бусад) Power Query эсвэл VBA-д байгаа шиг ишлэл биш харин таслах тэмдэгт дотор бичигдсэн болохыг анхаарна уу.
Гаралтын үед энэ скрипт нь эх бичвэрээс олдсон бүх тоог бидэнд өгөх болно:
123, 789
JavaScript-н богино хугацааны сургалт дууслаа, бүгдэд нь баярлалаа. Та логикийг олж авсан гэж найдаж байна 🙂
Энэ бүтээн байгуулалтыг Power Query руу шилжүүлэх хэвээр байна.
Power Query дахь ердийн илэрхийллээр текст хайх, задлах функц
Бид дараахь зүйлийг хийдэг.
1. Excel програмыг нээж, таб дээр шинэ хоосон Power Query үүсгэ Өгөгдөл – Өгөгдөл авах / Хүсэлт үүсгэх – Бусад эх сурвалжаас – Хоосон хүсэлт (Өгөгдөл - Өгөгдөл авах / Шинэ асуулга - Бусад эх сурвалжаас - Хоосон асуулга). Хэрэв танд Excel 2010-2013 болон Power Query-ийн хуучин хувилбар байгаа бол танд суулгаагүй боловч тусдаа нэмэлт болгон суулгасан бол энэ бүхэн таб дээр байх болно. Эрчим хүчний асуулгаБас үгүй Өгөгдөл.
2. Нээгдсэн асуулга засварлагчийн хоосон цонхонд баруун талын самбарт бидний ирээдүйн функцийн нэрийг нэн даруй оруулна уу (жишээлбэл, fxRegExpExtract)
3. Таб руу орцгооё Харах - Нарийвчилсан редактор (Харах — Нарийвчилсан редактор), бид хоосон хүсэлтийн M кодыг бүхэлд нь устгаж, супер функцийнхээ кодыг тэнд буулгана:
Гараа ажигла:
Эхний мөрөнд бидний функц гурван текст аргументтай байх болно гэж бид хэлж байна: txt - анализ хийж буй эх бичвэр, регекс - тогтмол илэрхийллийн загвар, тусгаарлагч - үр дүнг харуулах хязгаарлагч тэмдэгт.
Дараа нь бид функцийг дууддаг Web.Page, аргумент дээрээ дээр дурдсан JavaScript кодыг бүрдүүлэх. Бид хувьсагчийн аргументуудаа кодод буулгаж, орлуулдаг.
Хэсэг:
[Өгөгдөл]{0}[Хүүхдүүд]{0}[Хүүхдүүд]{1}[Текст]{0}
… нь бидэнд хэрэгтэй үр дүнгийн хүснэгтэд "унах" хэрэгтэй. Гол нь функц юм Web.Page Үүний үр дүнд вэб хуудасны бүтцийг давтдаг хэд хэдэн үүрлэсэн хүснэгтүүдийг гаргадаг. Энэ M-кодын хэсэг байхгүй бол бидний функц үүнийг гаргах болно:
… мөн бид энэ үгийг хэд хэдэн удаа дарах хэрэгтэй болно Хүснэгт, багана дахь хүүхдийн үүрлэсэн хүснэгтүүд рүү дараалан "унадаг" Хүүхэд:
Энэ бүх ишлэлийн оронд бид функцийнхээ кодонд аль хүснэгт, баганыг (текст) бидэнд хэрэгтэй.
Энд үнэндээ бүх нууцууд байна. Товчлуур дээр дарахад л үлддэг Finish цонхонд дэвшилтэт редактор, бидний кодыг оруулсан газар, та хамгийн амттай руу шилжих боломжтой - бидний функцийг ажил дээрээ туршиж үзээрэй.
Энд үрийн хэдэн жишээ байна.
Жишээ 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 нь дангаараа гайхалтай бөгөөд ердийн илэрхийлэлтэй хослуулснаар бидэнд ямар ч текст өгөгдлийг боловсруулахад бодит бус хүч, уян хатан байдлыг өгдөг. Майкрософт хэзээ нэгэн цагт Power Query болон Power BI шинэчлэлтүүдэд RegExp дэмжлэгийг нэмж, хэнгэрэгтэй дээрх бүх бүжиг өнгөрсөн зүйл болно гэж найдаж байна. Одоохондоо тийм ээ.
Онлайн засварлагч дээр https://regexr.com/ сайт дээр тогтмол илэрхийлэлтэй тоглох нь тохиромжтой гэдгийг би нэмж хэлмээр байна. Тэнд хэсэгт Олон нийтийн хэв маяг Бүх тохиолдлуудад зориулсан маш олон тооны бэлэн улирал байдаг. Туршилт - Power Query дээр ердийн илэрхийллийн бүх хүч одоо таны үйлчилгээнд байна!
- Ердийн илэрхийлэл (RegExp) гэж юу вэ, тэдгээрийг Excel дээр хэрхэн ашиглах талаар
- Power Query дээрх бүдэг текст хайлт
- Power Query ашиглан өөр өөр файлуудаас хүснэгтүүдийг угсарч байна