فهرست
اگر حداقل کمی با عبارات منظم آشنا هستید، پس نیازی به تبلیغ آنها ندارید. اگر کاملاً در موضوع نیستید، عبارات منظم (Regular Expressions = RegExp = "regexps" = "Regars") زبانی است که با استفاده از کاراکترها و قوانین خاص، زیررشته های لازم در متن جستجو می شوند، آنها استخراج می شوند. یا با متن دیگری جایگزین شود. این یک ابزار بسیار قدرتمند و زیبا است، مرتبه ای برتر از سایر روش های کار با متن.
قبلاً به تفصیل و با چندین مثال از زندگی توضیح داده‌ام که چگونه می‌توانید با استفاده از ماکروهای ساده، پشتیبانی از عبارت منظم را به اکسل اضافه کنید - اگر این مقاله را نخوانده‌اید، به شدت توصیه می‌کنم قبل از ادامه آن را بخوانید. شما چیزهای جدید زیادی کشف خواهید کرد، من تضمین می کنم 🙂
با این حال، این سوال همچنان باز است - چگونه می توان توانایی استفاده از عبارات منظم را در Power Query اضافه کرد؟ البته Power Query به خودی خود خوب است و می تواند کارهای زیادی را با متن انجام دهد (برش، چسباندن، تمیز کردن و غیره)، اما اگر بتوانید با قدرت عبارات منظم از آن عبور کنید، فقط یک بمب خواهد بود.
متأسفانه هیچ توابعی داخلی برای کار با RegExps در Power Query وجود ندارد و راهنما و پشتیبانی فنی رسمی مایکروسافت به این سؤال پاسخ منفی می دهد. با این حال، راهی برای دور زدن این محدودیت وجود دارد 🙂
ماهیت روش
ایده اصلی ساده تا آبروریزی است.
در لیست قابلیت های داخلی Power Query یک تابع وجود دارد صفحه وب. شرح این عملکرد در سایت راهنمای رسمی مایکروسافت بسیار مختصر است:
ترجمه شده، به این صورت خواهد بود: «محتوای سند HTML را به ساختارهای مؤلفه‌اش و همچنین نمایشی از سند کامل و بدنه آن پس از حذف برچسب‌ها برمی‌گرداند». رک و پوست کنده توصیف فلانی.
معمولاً این تابع هنگام وارد کردن داده از وب استفاده می شود و به طور خودکار جایگزین می شود، به عنوان مثال، زمانی که ما در برگه را انتخاب می کنیم داده ها فرمان از اینترنت (داده ها - از وب). ما به تابع یک صفحه وب را به عنوان آرگومان می‌دهیم و محتوای آن را در قالب جداول به ما برمی‌گرداند که قبلاً همه برچسب‌ها را پاک کرده است.
چیزی که راهنما نمی گوید این است که علاوه بر زبان نشانه گذاری HTML تابع صفحه وب از اسکریپت های جاوا اسکریپت پشتیبانی می کند، که اکنون در وب سایت های اینترنتی در همه جا وجود دارد. و جاوا اسکریپت به نوبه خود همیشه قادر به کار با عبارات منظم بوده و دارای توابع داخلی برای RegExps است! بنابراین برای پیاده سازی عبارات منظم در Power Query، باید توابع Web.Page را به عنوان آرگومان به یک برنامه کوچک جاوا اسکریپت تغذیه کنیم که همه کارها را برای Power Query انجام می دهد.
آنچه در جاوا اسکریپت خالص به نظر می رسد
آموزش های مفصل زیادی در مورد کار با عبارات منظم در جاوا اسکریپت در اینترنت وجود دارد (مثلاً یک، دو).
به طور خلاصه و ساده، کد جاوا اسکریپت به شکل زیر خواهد بود:
اینجا:
- var str = 'پرداخت قبوض 123 و 789 برای سوسیس'; - ایجاد یک متغیر خ و متن منبعی را که تحلیل خواهیم کرد به آن اختصاص دهید.
- الگوی var = /d+/gi; - یک عبارت منظم ایجاد کنید و آن را در یک متغیر قرار دهید الگو.
عبارت با علامت (/) شروع می شود.
خود عبارت در اینجا مثلاً این است d+ مخفف هر دنباله ای از ارقام است.
از طریق کسری بعد از عبارت، پارامترهای جستجوی اضافی (اصلاح کننده) وجود دارد - آنها را می توان به هر ترتیبی مشخص کرد:
- g – یعنی جستجوی سراسری، یعنی پس از یافتن مطابقت، توقف نکنید، بلکه جستجو را تا انتهای متن ادامه دهید. اگر این اصلاح کننده تنظیم نشده باشد، اسکریپت ما فقط اولین تطابق را برمی گرداند (123)
- i - جستجو بدون توجه به حروف
- m - جستجوی چند خطی (زمانی که متن منبع به چند خط تقسیم می شود استفاده می شود)
- var result = str.match(pattern).join(';'); - جستجو در متن مبدأ (خ) توسط عبارت منظم داده شده (الگو) و نتایج را در یک متغیر قرار دهید نتیجه، با استفاده از دستور آنها را با نقطه ویرگول به هم متصل کنید پیوستن
- document.write(نتیجه); - نمایش محتویات متغیر نتیجه
همچنین توجه داشته باشید که رشته‌های متنی (به استثنای عبارات معمولی) در جاوا اسکریپت در آپاستروف قرار می‌گیرند، نه نقل قول آن‌طور که در Power Query یا VBA هستند.
در خروجی، این اسکریپت در نتیجه تمام اعداد موجود در متن منبع را به ما می دهد:
123، 789
دوره کوتاه جاوا اسکریپت به پایان رسید، از همه شما متشکرم. امیدوارم منطق رو درک کرده باشید 🙂
باقی مانده است که این ساختار را به Power Query منتقل کنیم.
جستجو و استخراج تابع متن با بیان منظم در Power Query
ما موارد زیر را انجام می دهیم:
1. اکسل را باز کنید و یک Power Query خالی جدید در برگه ایجاد کنید داده – دریافت داده / ایجاد درخواست – از منابع دیگر – درخواست خالی (داده - دریافت داده / پرس و جو جدید - از منابع دیگر - پرس و جو خالی). اگر نسخه قدیمی اکسل 2010-2013 و Power Query دارید که داخلی ندارید، اما به عنوان یک افزونه جداگانه نصب شده است، همه اینها در برگه خواهد بود. پرس و جو برقو نه داده ها.
2. در پنجره خالی ویرایشگر پرس و جو که باز می شود، در پانل سمت راست، بلافاصله نام تابع آینده خود را وارد کنید (به عنوان مثال، fxRegExpExtract)
3. بیایید به برگه برویم مشاهده - ویرایشگر پیشرفته (مشاهده - ویرایشگر پیشرفته)، کل M-code درخواست خالی را پاک می کنیم و کد superfunction خود را در آنجا می چسبانیم:
مراقب دستان خود باشید:
در خط اول می گوییم که تابع ما سه آرگومان متنی خواهد داشت: کلیپ برد چند منظوره - متن اصلی در حال تجزیه و تحلیل، regex - الگوی بیان منظم، گول زدن - کاراکتر جداکننده برای نمایش نتایج.
سپس تابع را فراخوانی می کنیم صفحه وب، کد جاوا اسکریپت را که در آرگومان آن در بالا توضیح داده شد، تشکیل می دهد. آرگومان های متغیر خود را در کد قرار داده و جایگزین می کنیم.
قطعه:
[داده]{0}[فرزندان]{0}[فرزندان]{1}[متن]{0}
... لازم است تا با نتایجی که نیاز داریم، در جدول "در بیفتیم". نکته این است که تابع صفحه وب در نتیجه، چندین جدول تودرتو تولید می کند که ساختار یک صفحه وب را تکرار می کند. بدون این قطعه M-code، تابع ما این خروجی را خواهد داشت:
... و ما باید چندین بار روی کلمه کلیک کنیم جدول، به طور متوالی به جداول تودرتوی کودک در ستون‌ها «افتادن» می‌شود فرزندان:
به جای همه این نقل قول، بلافاصله در کد تابع خود مشخص می کنیم که کدام جدول و ستون تو در تو (متن) نیاز داریم.
در واقع، همه اسرار اینجا هستند. باقی مانده است که دکمه را فشار دهید پایان در پنجره ویرایشگر پیشرفته، جایی که ما کد خود را وارد کردیم، و شما می توانید به خوشمزه ترین آنها بروید - عملکرد ما را در محل کار امتحان کنید.
در اینجا چند نمونه بذر آورده شده است.
مثال 1. بازیابی شماره حساب و تاریخ از توضیحات پرداخت
ما یک صورت‌حساب بانکی با شرح (هدف) پرداخت‌ها داریم که در آن باید شماره و تاریخ فاکتورهای پرداخت شده را در ستون‌های جداگانه بیرون بیاورید:
جدول را به روش استاندارد در Power Query بارگذاری می کنیم داده ها - از جدول / محدوده (داده ها - از Tقادر/رفرشته).
سپس یک ستون محاسبه شده با تابع خود از طریق اضافه می کنیم اضافه کردن ستون - فراخوانی تابع سفارشی (افزودن ستون - فراخوانی تابع سفارشی) و آرگومان های آن را وارد کنید:
به عنوان یک عبارت منظم (استدلال regex) قالبی که ما استفاده می کنیم:
(d{3,5}|d{2}.d{2}.d{4})
ترجمه به زبان انسان به معنی:
اعداد از 3 تا 5 رقم (شماره حساب)
or
قطعاتی از فرم "عدد 2 بیتی - نقطه - عدد 2 بیتی - نقطه - عدد 4 بیتی"، یعنی تاریخ هایی به شکل DD.MM.YYYY.
به عنوان یک شخصیت جداکننده (استدلال گول زدن) یک نقطه ویرگول وارد کنید.
پس از کلیک روی OK تابع جادویی ما تمام داده های اولیه را مطابق با عبارت منظم ما تجزیه و تحلیل می کند و ستونی را با اعداد و تاریخ های یافت شده فاکتورها برای ما تشکیل می دهد:
باقی مانده است که آن را با استفاده از دستور با نقطه ویرگول جدا کنید صفحه اصلی — ستون تقسیم شده — توسط جداکننده (صفحه اصلی - تقسیم ستون - بر اساس جداکننده) و ما به آنچه می خواستیم می رسیم:
زیبایی!
مثال 2: آدرس های ایمیل را از متن استخراج کنید
فرض کنید جدول زیر را به عنوان داده اولیه داریم:
... از جایی که باید آدرس های ایمیل موجود در آنجا را بیرون بیاوریم (برای وضوح، آنها را با رنگ قرمز در متن برجسته کردم).
مانند مثال قبلی، جدول را به روش استاندارد از طریق Power Query بارگذاری می کنیم داده ها - از جدول / محدوده (داده ها - از Tقادر/رفرشته).
سپس یک ستون محاسبه شده با تابع خود از طریق اضافه می کنیم اضافه کردن ستون - فراخوانی تابع سفارشی (افزودن ستون - فراخوانی تابع سفارشی) و آرگومان های آن را وارد کنید:
تجزیه آدرس های ایمیل کار دشوارتری است و مجموعه ای از عبارات منظم با درجات مختلف کابوس برای حل آن وجود دارد. من از یکی از گزینه های ساده استفاده کردم - ایده آل نیست، اما در بیشتر موارد کاملاً کارآمد است:
[w|.|-]*@w*.[w|.]*
به عنوان جداکننده (گول زدن) می توانید یک نقطه ویرگول و یک فاصله وارد کنید.
با کلیک بر روی OK و ستونی با آدرس های ایمیل استخراج شده از متن اصلی "فرنی" دریافت می کنیم:
شعبده بازي!
PS
همانطور که گفته می شود: "هیچ چیز خوبی وجود ندارد که نتوان آن را حتی بهتر کرد." Power Query به خودی خود جالب است و هنگامی که با عبارات منظم ترکیب می شود، قدرت و انعطاف پذیری کاملا غیر واقعی را در پردازش هر داده متنی به ما می دهد. امیدوارم مایکروسافت روزی پشتیبانی RegExp را در به‌روزرسانی‌های Power Query و Power BI اضافه کند و تمام رقص‌های بالا با یک تنبور به گذشته تبدیل شود. خوب، در حال حاضر، بله.
همچنین می خواهم اضافه کنم که بازی با عبارات منظم در سایت https://regexr.com/ راحت است - درست در ویرایشگر آنلاین. آنجا در بخش الگوهای جامعه تعداد زیادی از فصل های منظم آماده برای همه موارد وجود دارد. آزمایش - تمام قدرت عبارات منظم اکنون در Power Query در خدمت شماست!
- عبارات منظم (RegExp) چیست و چگونه از آنها در اکسل استفاده کنیم
- جستجوی متن فازی در Power Query
- جمع آوری جداول از فایل های مختلف با استفاده از Power Query