Mundarija
Agar siz oddiy iboralar bilan kamida bir oz tanish bo'lsangiz, ularni reklama qilishingiz shart emas. Agar siz ushbu mavzuni to'liq o'rganmasangiz, unda muntazam iboralar (Regular Expressions = RegExp = "regexps" = "regulars") bu til bo'lib, unda maxsus belgilar va qoidalardan foydalangan holda matnda kerakli pastki qatorlar qidiriladi va ular chiqariladi. yoki boshqa matn bilan almashtiriladi. Bu juda kuchli va chiroyli vosita bo'lib, matn bilan ishlashning boshqa barcha usullaridan ustun turadi.
Men allaqachon batafsil va hayotdan bir qancha misollar bilan oddiy makroslar yordamida Excelga muntazam ifodani qo'llab-quvvatlashni qanday qo'shishingiz mumkinligini tasvirlab berdim - agar siz ushbu maqolani o'qimagan bo'lsangiz, davom ettirishdan oldin uni o'qib chiqishingizni tavsiya qilaman. Siz juda ko'p yangi narsalarni kashf qilasiz, kafolat beraman 🙂
Biroq, savol ochiqligicha qolmoqda - Power Query-da oddiy iboralarni ishlatish qobiliyatini qanday qo'shish mumkin? Power Query, albatta, o'z-o'zidan yaxshi va matn bilan juda ko'p ish qila oladi (kesish, yopishtirish, tozalash va h.k.), lekin siz uni muntazam iboralar kuchi bilan kesib o'tsangiz, bu shunchaki bomba bo'lar edi.
Afsuski, Power Query-da RegExps bilan ishlash uchun o'rnatilgan funktsiyalar mavjud emas va rasmiy Microsoft yordami va texnik yordami bu savolga salbiy javob beradi. Biroq, bu cheklovni chetlab o'tishning yo'li bor 🙂
Usulning mohiyati
Asosiy g'oya sharmanda qilish uchun oddiy.
O'rnatilgan Power Query imkoniyatlari ro'yxatida funktsiya mavjud Veb-sahifa. Rasmiy Microsoft yordam saytida ushbu funksiyaning tavsifi juda qisqa:
Tarjima qilinganda, bu shunday bo'ladi: "HTML hujjatining tarkibiy tuzilmalariga bo'lingan mazmunini, shuningdek teglar olib tashlanganidan keyin to'liq hujjat va uning tanasining tasvirini qaytaradi." Ochig'ini aytganda, shunday tavsif.
Odatda bu funktsiya internetdan ma'lumotlarni import qilishda ishlatiladi va avtomatik ravishda almashtiriladi, masalan, biz yorliqda tanlaganimizda ma'lumotlar buyruq Internetdan (Ma'lumotlar - Internetdan). Biz funktsiyaga argument sifatida veb-sahifani beramiz va u barcha teglarni oldindan tozalagan holda o'z tarkibini bizga jadvallar shaklida qaytaradi.
Yordam EMAS, HTML belgilash tiliga qo'shimcha ravishda Funktsiyasi Veb-sahifa JavaScript skriptlarini qo'llab-quvvatlaydi, bu hozir Internetdagi veb-saytlarda hamma joyda mavjud. Va JavaScript, o'z navbatida, har doim muntazam iboralar bilan ishlay olgan va RegExps uchun o'rnatilgan funktsiyalarga ega! Shunday qilib, Power Query'da muntazam ifodalarni amalga oshirish uchun biz Power Query uchun barcha ishlarni bajaradigan kichik JavaScript dasturiga argument sifatida Web.Page funksiyalarini berishimiz kerak bo'ladi.
Bu toza JavaScript-da qanday ko'rinadi
Internetda JavaScript-da oddiy iboralar bilan ishlash bo'yicha juda ko'p batafsil qo'llanmalar mavjud (masalan, bitta, ikkita).
Qisqacha va sodda qilib aytganda, JavaScript kodi quyidagicha ko'rinadi:
Bu yerga:
- var str = 'Kolbasa uchun 123 va 789 hisoblarni to'lash'; - o'zgaruvchi yaratish str va unga biz tahlil qiladigan manba matnini belgilang.
- var naqsh = /d+/gi; – muntazam ifoda yarating va uni o‘zgaruvchiga qo‘ying namuna.
Ifoda qiyshiq chiziq (/) bilan boshlanadi.
Bu erda ifodaning o'zi, masalan, d+ har qanday raqamlar ketma-ketligini bildiradi.
Ifodadan keyingi kasr orqali qo'shimcha qidiruv parametrlari (modifikatorlar) mavjud - ularni istalgan tartibda ko'rsatish mumkin:
- g – global qidiruvni bildiradi, ya'ni moslikni topgandan so'ng siz to'xtamasligingiz kerak, lekin matn oxirigacha qidirishni davom ettirishingiz kerak. Agar ushbu modifikator o'rnatilmagan bo'lsa, bizning skriptimiz faqat birinchi moslikni qaytaradi (123)
- i – harflarni hisobga olmasdan qidirish
- m – ko‘p qatorli qidiruv (manba matn bir necha qatorga bo‘linganda qo‘llaniladi)
- var natija = str.match(pattern).join(';'); - manba matnda qidiruvni amalga oshirish (str) berilgan muntazam ifoda orqali (namuna) va natijalarni o'zgaruvchiga qo'ying Natijalar, buyruq yordamida ularni nuqtali vergul bilan birlashtirish Qo'shiling
- hujjat.yozish(natija); – natija o‘zgaruvchisining mazmunini ko‘rsatish
Shuni ham yodda tutingki, JavaScript-dagi matn satrlari (muntazam iboralar bundan mustasno) Power Query yoki VBA-da bo'lgani kabi tirnoqlarda emas, balki apostroflar ichiga olingan.
Natijada, ushbu skript bizga manba matnida topilgan barcha raqamlarni beradi:
123, 789
JavaScript qisqa kursi tugadi, barchangizga rahmat. Umid qilamanki, siz mantiqni tushunasiz 🙂
Ushbu qurilishni Power Query-ga o'tkazish qoladi.
Power Query-da oddiy ifoda orqali matnni qidirish va chiqarish funksiyasi
Biz quyidagilarni bajaramiz:
1. Excel-ni oching va yorliqda yangi bo'sh Power Query yarating Ma'lumotlar - Ma'lumotlarni olish / So'rov yaratish - Boshqa manbalardan - Bo'sh so'rov (Ma'lumotlar - Ma'lumot olish / Yangi so'rov - Boshqa manbalardan - Bo'sh so'rov). Agar sizda Excel 2010-2013 ning eski versiyasi va Power Query o'rnatilgan bo'lmasa, lekin alohida plagin sifatida o'rnatilgan bo'lsa, bularning barchasi yorliqda bo'ladi. Quvvat so'roviVa emas ma'lumotlar.
2. Ochilgan so'rovlar muharririning bo'sh oynasida, o'ng panelda darhol kelajakdagi funksiyamiz nomini kiriting (masalan, fxRegExpExtract)
3. Keling, tabga o'tamiz Ko'rish - Kengaytirilgan muharrir (Koʻrish — Kengaytirilgan muharrir), biz bo'sh so'rovning to'liq M-kodini o'chirib tashlaymiz va u erda superfunktsiyamiz kodini joylashtiramiz:
Qo'llaringizga e'tibor bering:
Birinchi qatorda biz funksiyamiz uchta matn argumentiga ega bo'lishini aytamiz: txt - tahlil qilinayotgan asl matn; regex - muntazam ifoda namunasi, chekka — natijalarni ko'rsatish uchun ajratuvchi belgi.
Keyin biz funktsiyani chaqiramiz Veb-sahifa, argumentida yuqorida tavsiflangan JavaScript kodini shakllantirish. Biz o'zgaruvchi argumentlarimizni kodga joylashtiramiz va almashtiramiz.
Fragment:
[Maʼlumotlar]{0}[Bolalar]{0}[Bolalar]{1}[Matn]{0}
... bizga kerakli natijalar bilan jadvalga "tushish" uchun kerak. Gap shundaki, funktsiya Veb-sahifa natijada u web-sahifa tuzilishini takrorlaydigan bir nechta ichki jadvallarni ishlab chiqaradi. Ushbu M-kodi bo'lmasa, bizning funktsiyamiz buni chiqaradi:
... va biz so'zni bir necha marta bosishimiz kerak edi stol, ketma-ket ustunlardagi ichki o'rnatilgan jadvallarga "tushish" Bolalar:
Ushbu kotirovka o'rniga biz darhol funktsiyamiz kodida qaysi jadval va ustun joylashtirilganligini ko'rsatamiz (matn) bizga kerak.
Bu erda, aslida, barcha sirlar. Tugmani bosish qoladi tugatmoq derazada rivojlangan muharriri, biz kodimizni joylashtirgan joy va siz eng mazali bo'lishga o'tishingiz mumkin - bizning funksiyamizni ish joyida sinab ko'ring.
Mana bir nechta urug'lik misollari.
Misol 1. To'lov tavsifidan hisob raqami va sanasini olish
Bizda to'lovlar tavsifi (maqsadlari) ko'rsatilgan bank ko'chirmasi bor, unda siz to'langan schyot-fakturalarning raqamlari va sanalarini alohida ustunlarga ajratishingiz kerak:
Biz jadvalni Power Query-ga standart tarzda yuklaymiz Ma'lumotlar - Jadvaldan/diapazondan (Ma'lumotlar - T.danqodir / Rfarishta).
Keyin orqali funksiyamiz bilan hisoblangan ustunni qo'shamiz Ustun qo'shish - Maxsus funktsiyani chaqirish (Ustun qo'shish - maxsus funktsiyani chaqirish) va uning argumentlarini kiriting:
Muntazam ifoda sifatida (argument regex) biz foydalanadigan shablon:
(d{3,5}|d{2}.d{2}.d{4})
… inson tiliga tarjima qilingan ma’nosi:
3 dan 5 gacha raqamlar (hisob raqamlari)
or
"2 bitli raqam - nuqta - 2 bitli raqam - nuqta - 4 bitli raqam" shaklidagi qismlar, ya'ni DD.MM.YYYY shaklidagi sanalar.
Ajratuvchi belgi sifatida (argument chekka) nuqtali vergulni kiriting.
Tugmasini bosgandan so'ng OK Bizning sehrli funktsiyamiz barcha dastlabki ma'lumotlarni oddiy ifodamiz bo'yicha tahlil qiladi va biz uchun hisob-fakturalarning raqamlari va sanalari bilan ustun hosil qiladi:
Buyruq yordamida uni nuqta-vergul bilan ajratish qoladi Bosh sahifa - Ajratilgan ustun - Ajratuvchi bo'yicha (Uy - Ajratish ustuni - ajratuvchi bo'yicha) va biz xohlagan narsani olamiz:
Go'zallik!
2-misol: matndan elektron pochta manzillarini ajratib oling
Aytaylik, bizda dastlabki ma'lumotlar sifatida quyidagi jadval mavjud:
... u erda joylashgan elektron pochta manzillarini olib tashlashimiz kerak bo'lgan joydan (aniqlik uchun ularni matnda qizil rang bilan ta'kidladim).
Oldingi misolda bo'lgani kabi, jadvalni Power Query-ga standart tarzda yuklaymiz Ma'lumotlar - Jadvaldan/diapazondan (Ma'lumotlar - T.danqodir / Rfarishta).
Keyin orqali funksiyamiz bilan hisoblangan ustunni qo'shamiz Ustun qo'shish - Maxsus funktsiyani chaqirish (Ustun qo'shish - maxsus funktsiyani chaqirish) va uning argumentlarini kiriting:
Elektron pochta manzillarini tahlil qilish qiyinroq vazifa bo'lib, uni hal qilish uchun turli darajadagi dahshatli tushlarning bir qator muntazam ifodalari mavjud. Men oddiy variantlardan birini ishlatdim - ideal emas, lekin ko'p hollarda juda yaxshi ishlaydi:
[w|.|-]*@w*.[w|.]*
Ajratuvchi sifatida (chekka) nuqtali vergul va bo'sh joyni kiritishingiz mumkin.
bosing OK va biz "porridge" asl matnidan olingan elektron pochta manzillari bo'lgan ustunni olamiz:
Sehrli!
PS
Aytishlaricha: "Bundan ham yaxshiroq bo'lmaydigan yaxshi narsa yo'q". Power Query o'z-o'zidan ajoyib va oddiy iboralar bilan birlashganda, u bizga har qanday matn ma'lumotlarini qayta ishlashda mutlaqo haqiqiy bo'lmagan kuch va moslashuvchanlikni beradi. Umid qilamanki, Microsoft qachonlardir Power Query va Power BI yangilanishlarida RegExp yordamini qo'shadi va yuqoridagi barcha daf bilan raqslar o'tmishda qoladi. Xo'sh, hozircha, ha.
Shuni ham qo'shimcha qilmoqchimanki, https://regexr.com/ saytida oddiy iboralar bilan o'ynash qulay - to'g'ridan-to'g'ri onlayn muharrirda. U erda bo'limda Jamiyat namunalari Barcha holatlar uchun juda ko'p tayyor muntazam mavsumlar mavjud. Tajriba - oddiy iboralarning barcha kuchi endi Power Query xizmatida!
- Muntazam iboralar (RegExp) nima va ularni Excelda qanday ishlatish kerak
- Power Query-da noaniq matn qidirish
- Power Query yordamida turli fayllardan jadvallarni yig'ish