المحتويات
إذا كنت معتادًا قليلاً على الأقل على التعبيرات العادية ، فلا داعي للإعلان عنها. إذا لم تكن في الموضوع تمامًا ، فإن التعبيرات العادية (Regular Expressions = RegExp = “regexps” = “منتظم”) هي لغة يتم فيها ، باستخدام أحرف وقواعد خاصة ، البحث عن السلاسل الفرعية الضرورية في النص ، ويتم استخراجها أو استبدالها بنص آخر. هذه أداة قوية وجميلة للغاية ، ترتيب من حيث الحجم يتفوق على جميع الطرق الأخرى للعمل مع النص.
لقد وصفت بالفعل بالتفصيل ومع مجموعة من الأمثلة من الحياة كيف يمكنك إضافة دعم التعبير العادي إلى Excel باستخدام وحدات ماكرو بسيطة - إذا لم تكن قد قرأت هذه المقالة ، فإنني أوصي بشدة بقراءتها قبل المتابعة. سوف تكتشفين الكثير من الأشياء الجديدة ، أضمن لك 🙂
ومع ذلك ، يظل السؤال مفتوحًا - كيف تضيف القدرة على استخدام التعبيرات العادية في Power Query؟ Power Query ، بالطبع ، جيد بمفرده ويمكنه فعل الكثير مع النص (القص ، اللصق ، التنظيف ، إلخ) ، ولكن إذا تمكنت من عبوره بقوة التعبيرات العادية ، فسيكون مجرد قنبلة.
لسوء الحظ ، لا توجد وظائف مضمنة للعمل مع RegExps في Power Query ، وتساعد مساعدة Microsoft الرسمية والدعم الفني على الإجابة على هذا السؤال بالنفي. ومع ذلك ، هناك طريقة للتغلب على هذا القيد 🙂
جوهر الطريقة
الفكرة الرئيسية بسيطة للعار.
في قائمة إمكانيات Power Query المضمنة ، توجد وظيفة صفحة ويب. وصف هذه الوظيفة على موقع مساعدة Microsoft الرسمي موجز للغاية:
عند الترجمة ، سيكون هذا: "إرجاع محتويات مستند HTML مقسمًا إلى هياكل مكوناتها ، بالإضافة إلى تمثيل المستند الكامل وجسمه بعد إزالة العلامات". هكذا وصف ، بصراحة.
عادةً ما تُستخدم هذه الوظيفة عند استيراد البيانات من الويب ويتم استبدالها تلقائيًا ، على سبيل المثال ، عندما نختار في علامة التبويب البيانات أمر من الإنترنت (البيانات - من الويب). نعطي الوظيفة صفحة ويب كوسيطة ، وتقوم بإرجاع محتوياتها إلينا في شكل جداول ، بعد مسح جميع العلامات مسبقًا.
ما لا تقوله المساعدة هو أنه بالإضافة إلى لغة ترميز HTML وظيفة صفحة ويب يدعم نصوص جافا سكريبت، والتي أصبحت الآن موجودة في كل مكان على مواقع الويب على الإنترنت. و JavaScript ، بدوره ، كان دائمًا قادرًا على العمل مع التعبيرات العادية ولديه وظائف مضمنة لـ RegExps! لذلك ، لتنفيذ التعبيرات العادية في Power Query ، سنحتاج إلى تغذية وظائف Web.Page كوسيطة لبرنامج JavaScript صغير يقوم بجميع الأعمال الخاصة بـ Power Query.
كيف يبدو في نقية جافا سكريبت
هناك الكثير من الدروس التفصيلية حول التعامل مع التعبيرات العادية في JavaScript على الإنترنت (على سبيل المثال ، واحد ، اثنان).
باختصار ومبسط ، سيبدو كود JavaScript كما يلي:
هنا:
- var str = "دفع فواتير 123 و 789 مقابل النقانق" ؛ - إنشاء متغير شارع وإسناد النص المصدر الذي سنحلله.
- نمط فار = / د + / جي ؛ - إنشاء تعبير نمطي ووضعه في متغير النمط.
يبدأ التعبير بشرطة مائلة (/).
التعبير نفسه هنا ، على سبيل المثال ، هو d+ لتقف على أي تسلسل من الأرقام.
من خلال الكسر بعد التعبير ، توجد معلمات بحث إضافية (معدِّلات) - يمكن تحديدها بأي ترتيب:
- g - يعني البحث الشامل ، أي بعد العثور على تطابق ، يجب ألا تتوقف ، بل تواصل البحث حتى نهاية النص. إذا لم يتم تعيين هذا المعدل ، فسيعيد النص البرمجي المطابقة الأولى فقط (123)
- i - البحث بغض النظر عن حالة الحروف
- m - بحث متعدد الأسطر (يُستخدم عند تقسيم النص المصدر إلى عدة أسطر)
- نتيجة var = str.match (pattern) .join ('؛') ؛ - قم بإجراء بحث في النص المصدر (شارع) بالتعبير النمطي المعطى (النمط) ووضع النتائج في متغير نتيجة، وربطها بفاصلة منقوطة باستخدام الأمر الانضمام
- document.write (نتيجة) ؛ - عرض محتويات المتغير الناتج
لاحظ أيضًا أن السلاسل النصية (باستثناء التعبيرات العادية) في JavaScript محاطة بفواصل عليا ، وليست علامات اقتباس كما هي في Power Query أو VBA.
عند الإخراج ، سيعطينا هذا البرنامج النصي نتيجة لذلك جميع الأرقام الموجودة في النص المصدر:
يناير ٢٠٢٤
انتهت دورة JavaScript القصيرة ، شكرًا لكم جميعًا. أتمنى أن تحصل على المنطق 🙂
يبقى نقل هذا البناء إلى Power Query.
البحث عن وظيفة النص واستخراجها عن طريق التعبير العادي في Power Query
نقوم بما يلي:
1. افتح Excel وأنشئ Power Query جديدًا فارغًا في علامة التبويب بيانات - إحضار بيانات / إنشاء طلب - من مصادر أخرى - طلب فارغ (بيانات - إحضار بيانات / استعلام جديد - من مصادر أخرى - استعلام فارغ). إذا كان لديك إصدار قديم من Excel 2010-2013 و Power Query ، فليس لديك نسخة مضمنة ، ولكن تم تثبيتها كوظيفة إضافية منفصلة ، فسيكون كل هذا في علامة التبويب استفسار الطاقةو لا البيانات.
2. في النافذة الفارغة لمحرر الاستعلام الذي يفتح ، في اللوحة اليمنى ، أدخل على الفور اسم وظيفتنا المستقبلية (على سبيل المثال ، fxRegExpExtract)
3. دعنا نذهب إلى علامة التبويب عرض - محرر متقدم (عرض - محرر متقدم)، نقوم بمسح كود M بالكامل للطلب الفارغ ولصق كود وظيفتنا الفائقة هناك:
انتبه يديك:
في السطر الأول ، نقول إن وظيفتنا ستحتوي على ثلاث حجج نصية: TXT - النص الأصلي قيد التحليل ، رجإكس - نمط التعبير العادي ، delim - حرف محدد لعرض النتائج.
بعد ذلك نسمي الوظيفة صفحة ويب، وتشكيل كود JavaScript الموصوف أعلاه في الوسيطة الخاصة به. نقوم بلصق واستبدال وسيطاتنا المتغيرة في الكود.
شظية:
[البيانات] {0} [الأطفال] {0} [الأطفال] {1} [نص] {0}
... هناك حاجة "للسقوط" في الجدول مع النتائج التي نحتاجها. النقطة هي أن الوظيفة صفحة ويب ونتيجة لذلك ، فإنه ينتج عدة جداول متداخلة تكرر بنية صفحة الويب. بدون هذه القطعة من كود M ، ستخرج وظيفتنا هذا:
... وسيتعين علينا النقر فوق الكلمة عدة مرات طاولات ومكاتب ، "السقوط" على التوالي في جداول متداخلة فرعية في أعمدة أطفال:
بدلاً من كل هذا الاقتباس ، نشير على الفور في كود وظيفتنا إلى الجدول والعمود المتداخلين (نص) نحن نحتاج.
هنا ، في الواقع ، كل الأسرار. يبقى أن تضغط على الزر نهاية فى الشباك محرر متقدم، حيث أدخلنا الكود الخاص بنا ، ويمكنك المتابعة إلى الأكثر لذة - جرب وظيفتنا في العمل.
فيما يلي بعض الأمثلة للبذور.
مثال 1. استرجاع رقم الحساب والتاريخ من وصف الدفع
لدينا كشف حساب بنكي مع وصف (الغرض) للمدفوعات ، حيث تحتاج إلى سحب أرقام وتواريخ الفواتير المدفوعة في أعمدة منفصلة:
نقوم بتحميل الجدول في Power Query بالطريقة القياسية البيانات - من جدول / نطاق (البيانات - من T.قادر / R.ملاك).
ثم نضيف عمودًا محسوبًا بوظيفتنا عبر إضافة عمود - استدعاء وظيفة مخصصة (إضافة عمود - استدعاء وظيفة مخصصة) وادخل حججه:
كتعبير عادي (حجة رجإكس) القالب الذي نستخدمه:
(د {3,5}|د {2} .d {2} .d {4})
... ترجم إلى لغة بشرية بمعنى:
أرقام من 3 إلى 5 أرقام (أرقام حسابات)
or
أجزاء من النموذج "رقم 2 بت - نقطة - رقم 2 بت - نقطة - رقم 4 بت"، أي التواريخ بالنموذج DD.MM.YYYY.
كحرف محدد (وسيطة delim) أدخل فاصلة منقوطة.
بعد النقر على OK تقوم وظيفتنا السحرية بتحليل جميع البيانات الأولية وفقًا لتعبيرنا العادي وتشكل عمودًا لنا بالأرقام والتواريخ الموجودة في الفواتير:
يبقى فصله بفاصلة منقوطة باستخدام الأمر الصفحة الرئيسية - انقسام العمود - بواسطة محدد (المنزل - العمود المنقسم - بواسطة المحدد) ونحصل على ما أردناه:
جمال!
المثال 2: استخراج عناوين البريد الإلكتروني من النص
افترض أن لدينا الجدول التالي كبيانات أولية:
... من حيث نحتاج إلى سحب عناوين البريد الإلكتروني الموجودة هناك (للتوضيح ، قمت بتمييزها باللون الأحمر في النص).
كما في المثال السابق ، نقوم بتحميل الجدول في Power Query بالطريقة القياسية عبر البيانات - من جدول / نطاق (البيانات - من T.قادر / R.ملاك).
ثم نضيف عمودًا محسوبًا بوظيفتنا عبر إضافة عمود - استدعاء وظيفة مخصصة (إضافة عمود - استدعاء وظيفة مخصصة) وادخل حججه:
يعد تحليل عناوين البريد الإلكتروني مهمة أكثر صعوبة وهناك مجموعة من التعبيرات العادية بدرجات متفاوتة من الكوابيس لحلها. لقد استخدمت أحد الخيارات البسيطة - ليست مثالية ، ولكنها تعمل تمامًا في معظم الحالات:
[w |. | -] * @ w *. [w |.] *
كفاصل (delim) يمكنك إدخال فاصلة منقوطة ومسافة.
انقر على OK ونحصل على عمود بعناوين بريد إلكتروني مستخرجة من النص الأصلي "عصيدة":
السحر!
PS
كما يقول المثل: "لا يوجد شيء جيد لا يمكن تحسينه." يعد Power Query رائعًا من تلقاء نفسه ، وعندما يقترن بالتعبيرات العادية ، فإنه يمنحنا قوة ومرونة غير واقعية تمامًا في معالجة أي بيانات نصية. آمل أن تضيف Microsoft يومًا ما دعم RegExp في تحديثات Power Query و Power BI وستصبح جميع الرقصات المذكورة أعلاه مع الدف شيئًا من الماضي. حسنًا ، في الوقت الحالي ، نعم.
أريد أيضًا أن أضيف أنه من الملائم اللعب بالتعبيرات العادية على الموقع https://regexr.com/ - مباشرة في المحرر عبر الإنترنت. هناك في القسم أنماط المجتمع هناك عدد كبير من المواسم المنتظمة الجاهزة لجميع المناسبات. التجربة - أصبحت كل قوة التعبيرات العادية في خدمتك الآن في Power Query!
- ما هي التعبيرات العادية (RegExp) وكيفية استخدامها في Excel
- البحث عن نص غامض في Power Query
- تجميع الجداول من ملفات مختلفة باستخدام Power Query