تحليل النص باستخدام التعبيرات العادية (RegExp) في Excel

واحدة من أكثر المهام استهلاكا للوقت وإحباطا عند العمل مع النص في Excel هي تحليل - تقسيم "العصيدة" الأبجدية الرقمية إلى مكونات واستخراج الأجزاء التي نحتاجها منها. فمثلا:

• استخراج الرمز البريدي من العنوان (من الجيد أن يكون الرمز البريدي دائمًا في البداية ، ولكن ماذا لو لم يكن كذلك؟)
• العثور على رقم الفاتورة وتاريخها من وصف الدفع في كشف الحساب البنكي
• استخراج رقم التعريف الضريبي TIN من الأوصاف المتنوعة للشركات في قائمة الأطراف المقابلة
• ابحث عن رقم السيارة أو رقم المقالة في الوصف ، إلخ.
  

عادة في مثل هذه الحالات ، بعد نصف ساعة من الانتقاء الكئيب للنص يدويًا ، تبدأ الأفكار بالتبادر إلى الذهن بطريقة ما لأتمتة هذه العملية (خاصة إذا كان هناك الكثير من البيانات). هناك عدة حلول وبدرجات متفاوتة من التعقيد والكفاءة:

• استعمل وظائف نصية Excel مضمنة للبحث - قص - لصق النص: ليفسيمف (اليسار), يمين (حق), بستر (منتصف), STSEPIT (سلسل) ونظائرها, الجمع بين (نص مشترك), EXACT (بالضبط) هذه الطريقة جيدة إذا كان هناك منطق واضح في النص (على سبيل المثال ، يكون الفهرس دائمًا في بداية العنوان). خلاف ذلك ، تصبح الصيغ أكثر تعقيدًا ، وفي بعض الأحيان ، يصل الأمر إلى صيغ الصفيف ، والتي تتباطأ بشكل كبير على الجداول الكبيرة.
• باستخدام مثل عامل تشابه النص ملفوفة من Visual Basic في دالة ماكرو مخصصة. يتيح لك ذلك تنفيذ بحث أكثر مرونة باستخدام أحرف البدل (* ، # ،؟ ، إلخ.) للأسف ، لا تستطيع هذه الأداة استخراج السلسلة الفرعية المطلوبة من النص - فقط تحقق مما إذا كانت مضمنة فيها.

بالإضافة إلى ما سبق ، هناك نهج آخر معروف جيدًا في الدوائر الضيقة للمبرمجين المحترفين ومطوري الويب وغيرهم من التقنيين - هذا هو تعبيرات منتظمة (التعبيرات العادية = RegExp = "regexps" = "العادية"). ببساطة، RegExp هي لغة يتم فيها استخدام أحرف وقواعد خاصة للبحث عن السلاسل الفرعية الضرورية في النص أو استخراجها أو استبدالها بنص آخر. التعبيرات العادية هي أداة قوية وجميلة للغاية تتفوق على جميع الطرق الأخرى للعمل مع النص بترتيب من حيث الحجم. تدعم العديد من لغات البرمجة (C # و PHP و Perl و JavaScript ...) ومحررات النصوص (Word و Notepad ++…) التعبيرات العادية.

لسوء الحظ ، لا يحتوي Microsoft Excel على دعم RegExp خارج الصندوق ، ولكن يمكن إصلاح ذلك بسهولة باستخدام VBA. افتح محرر Visual Basic من علامة التبويب المطور (مطور) أو اختصار لوحة المفاتيح قديم+F11. ثم أدخل الوحدة الجديدة من خلال القائمة إدراج - وحدة وانسخ نص وظيفة الماكرو التالية هناك:

RegExpExtract للوظيفة العامة (نص كسلسلة ، نقش كسلسلة ، عنصر اختياري كعدد صحيح = 1) كسلسلة عند الخطأ انتقل إلى ErrHandl مجموعة regex = CreateObject ("VBScript.RegExp") regex.Pattern = Pattern regex.Global = True If regex.Test (نص) ثم قم بتعيين التطابقات = regex.Execute (نص) RegExpExtract = متطابقات.  

يمكننا الآن إغلاق محرر Visual Basic والعودة إلى Excel لتجربة ميزتنا الجديدة. تركيبها كما يلي:

= RegExpExtract (Txt ، Pattern ، Item)

أين

• TXT - خلية بها النص الذي نتحقق منه ونريد استخراج السلسلة الفرعية التي نحتاجها
• النمط - قناع (نمط) للبحث عن السلاسل الفرعية
• العناصر - الرقم التسلسلي للسلسلة الفرعية المراد استخراجها ، إذا كان هناك العديد منها (إذا لم يتم تحديدها ، فسيتم عرض التكرار الأول)

الشيء الأكثر إثارة للاهتمام هنا ، بالطبع ، هو النمط - سلسلة من الأحرف الخاصة "بلغة" RegExp ، والتي تحدد بالضبط ما نريد أن نجد وأين. فيما يلي أهمها لتبدأ:

إذا كنا نبحث عن عدد معين من الأحرف ، على سبيل المثال ، رمز بريدي مكون من ستة أرقام أو جميع رموز المنتج المكونة من ثلاثة أحرف ، فإننا نأتي إلى الإنقاذ محددو الكمية or محددو الكمية هي تعبيرات خاصة تحدد عدد الأحرف المطلوب البحث عنها. يتم تطبيق المحددات الكمية على الشخصية التي تسبقها:

الآن دعنا ننتقل إلى الجزء الأكثر إثارة للاهتمام - تحليل تطبيق الوظيفة التي تم إنشاؤها وما تعلمناه عن الأنماط في الأمثلة العملية من الحياة.

استخلاص الأرقام من النص

بادئ ذي بدء ، دعنا نحلل حالة بسيطة - تحتاج إلى استخراج الرقم الأول من العصيدة الأبجدية الرقمية ، على سبيل المثال ، قوة مصادر الطاقة غير المنقطعة من قائمة الأسعار:

المنطق الكامن وراء التعبير النمطي بسيط: d يعني أي رقم ، والمحدد الكمي + يقول أن عددهم يجب أن يكون واحدًا أو أكثر. هناك حاجة إلى علامة الطرح المزدوجة الموجودة أمام الوظيفة "أثناء التنقل" لتحويل الأحرف المستخرجة إلى رقم كامل من الرقم كنص.

الرمز البريدي

للوهلة الأولى ، كل شيء بسيط هنا - نحن نبحث بالضبط عن ستة أرقام متتالية. نحن نستخدم شخصية خاصة d للرقم والمحدِّد الكمي 6،XNUMX،XNUMX {} لعدد الأحرف:

ومع ذلك ، فإن الموقف يكون ممكنًا عندما ، على يسار الفهرس في السطر ، هناك مجموعة كبيرة أخرى من الأرقام المتتالية (رقم الهاتف ، رقم التعريف الضريبي ، الحساب المصرفي ، إلخ.) ثم موسمنا العادي سوف يسحب أول 6 أرقام منه ، أي لن تعمل بشكل صحيح:

لمنع حدوث ذلك ، نحتاج إلى إضافة معدل حول حواف تعبيرنا النمطي b دلالة على نهاية الكلمة. سيوضح هذا لبرنامج Excel أن الجزء (الفهرس) الذي نحتاجه يجب أن يكون كلمة منفصلة ، وليس جزءًا من جزء آخر (رقم الهاتف):

الهاتف:

تكمن المشكلة في العثور على رقم هاتف في النص في وجود العديد من الخيارات لكتابة الأرقام - مع أو بدون واصلات ، من خلال المسافات ، مع أو بدون رمز المنطقة بين قوسين ، إلخ. لذلك ، في رأيي ، من الأسهل قم أولاً بتنظيف كل هذه الأحرف من النص المصدر باستخدام عدة وظائف متداخلة استبدل (بديل)بحيث تلتصق ببعضها البعض في كل واحد ، ثم مع نظام بدائي د {11} اسحب 11 رقمًا على التوالي:

ITN

الأمر أكثر تعقيدًا هنا ، لأن رقم التعريف الضريبي (في بلدنا) يمكن أن يتكون من 10 أرقام (للكيانات القانونية) أو 12 رقمًا (للأفراد). إذا لم تجد خطأ بشكل خاص ، فمن الممكن تمامًا أن تكون راضيًا عن النظام العادي د {10,12}، ولكن ، بالمعنى الدقيق للكلمة ، سيتم سحب جميع الأرقام من 10 إلى 12 حرفًا ، أي وتم إدخال 11 رقمًا بشكل خاطئ. سيكون من الأصح استخدام نمطين متصلين بواسطة عامل OR منطقي | (شريط عمودي):

يرجى ملاحظة أنه في الاستعلام ، نبحث أولاً عن أرقام 12 بت ، وبعد ذلك فقط عن أرقام 10 بت. إذا كتبنا تعبيرنا العادي في الاتجاه المعاكس ، فسيتم سحبه للجميع ، حتى الأحرف الطويلة المكونة من 12 بت ، الأحرف العشرة الأولى فقط. أي بعد تشغيل الشرط الأول ، لم يعد يتم إجراء مزيد من التحقق:

هذا هو الاختلاف الأساسي بين المشغل | من وظيفة منطق Excel القياسية OR (OR)، حيث لا يؤدي إعادة ترتيب الحجج إلى تغيير النتيجة.

وحدات SKU للمنتج

في العديد من الشركات ، يتم تعيين معرفات فريدة للسلع والخدمات - المقالات ، أكواد SAP ، رموز التخزين التعريفية ، إلخ. إذا كان هناك منطق في تدوينها ، فيمكن عندئذٍ سحبها بسهولة من أي نص باستخدام التعبيرات العادية. على سبيل المثال ، إذا علمنا أن مقالاتنا تتكون دائمًا من ثلاثة أحرف إنجليزية كبيرة وواصلة ورقم لاحق مكون من ثلاثة أرقام ، إذن:

المنطق وراء النموذج بسيط. [من الألف إلى الياء] - تعني أي أحرف كبيرة من الأبجدية اللاتينية. المحدد التالي 3،XNUMX،XNUMX {} يقول أنه من المهم بالنسبة لنا أن يكون هناك بالضبط ثلاثة أحرف من هذا القبيل. بعد الواصلة ، ننتظر ثلاثة أرقام ، لذا نضيف في النهاية د {3}

المبالغ النقدية

بطريقة مشابهة للفقرة السابقة ، يمكنك أيضًا سحب الأسعار (التكاليف ، ضريبة القيمة المضافة ...) من وصف البضائع. إذا تمت الإشارة إلى المبالغ النقدية ، على سبيل المثال ، بشرطة ، فعندئذٍ:

نمط d مع محدد الكم + يبحث عن أي رقم يصل إلى واصلة ، و د {2} سيبحث عن البنسات (رقمين) بعد.

إذا كنت بحاجة إلى استخراج ليس الأسعار ، ولكن ضريبة القيمة المضافة ، فيمكنك استخدام الوسيطة الاختيارية الثالثة لوظيفة RegExpExtract ، والتي تحدد الرقم الترتيبي للعنصر المراد استخراجه. وبالطبع يمكنك استبدال الوظيفة استبدل (بديل) في النتائج ، واصلة إلى الفاصل العشري القياسي وأضف علامة ناقص مزدوجة في البداية حتى يفسر Excel ضريبة القيمة المضافة التي تم العثور عليها كرقم عادي:

أرقام لوحات السيارة

إذا كنت لا تأخذ مركبات خاصة ومقطورات ودراجات نارية أخرى، فسيتم تحليل رقم السيارة القياسي وفقًا لمبدأ "حرف - ثلاثة أرقام - حرفين - رمز المنطقة". علاوة على ذلك، يمكن أن يتكون رمز المنطقة من رقمين أو ثلاثة أرقام، ويتم استخدام الحروف المشابهة في مظهرها للأبجدية اللاتينية فقط. وبالتالي فإن التعبير العادي التالي سيساعدنا في استخراج الأرقام من النص:

الوقت:

لاستخراج الوقت بتنسيق HH: MM ، يكون التعبير النمطي التالي مناسبًا:

بعد كسر القولون [0-5] د، لأنه من السهل معرفة ذلك ، يحدد أي رقم في النطاق 00-59. قبل النقطتين الموجودتين بين قوسين ، يعمل نمطين ، مفصولين عن طريق OR (أنبوب) منطقي:

• [0-1] د - أي رقم في النطاق 00-19
• 2 [0-3] - أي رقم في النطاق 20-23

للنتيجة التي تم الحصول عليها ، يمكنك أيضًا تطبيق وظيفة Excel القياسية الوقت (الفريق)لتحويله إلى تنسيق زمني مفهوم للبرنامج ومناسب لمزيد من العمليات الحسابية.

التحقق من كلمة المرور

افترض أننا بحاجة إلى التحقق من قائمة كلمات المرور التي اخترعها المستخدمون للتأكد من صحتها. وفقًا لقواعدنا ، لا يمكن أن تحتوي كلمات المرور إلا على أحرف إنجليزية (أحرف صغيرة أو كبيرة) وأرقام. غير مسموح بالمسافات والشرطات السفلية وعلامات الترقيم الأخرى.

يمكن تنظيم التدقيق باستخدام التعبير النمطي البسيط التالي:

في الواقع ، مع مثل هذا النمط نطلب ذلك بين البداية (^) ونهاية ($) في نصنا ، لم يكن هناك سوى أحرف من المجموعة الواردة بين قوسين معقوفين. إذا كنت بحاجة أيضًا إلى التحقق من طول كلمة المرور (على سبيل المثال ، 6 أحرف على الأقل) ، فعندئذٍ المحدد + يمكن استبدالها بالفاصل الزمني "ستة أو أكثر" في النموذج {6،XNUMX،XNUMX}:

المدينة من العنوان

لنفترض أننا بحاجة إلى سحب المدينة من شريط العناوين. سيساعدك البرنامج العادي في استخراج النص من "g". إلى الفاصلة التالية:

دعونا نلقي نظرة فاحصة على هذا النمط.

إذا كنت قد قرأت النص أعلاه ، فأنت تدرك بالفعل أن بعض الأحرف في التعبيرات العادية (النقاط ، والعلامات النجمية ، وعلامات الدولار ، وما إلى ذلك) لها معنى خاص. إذا كنت بحاجة للبحث عن هذه الأحرف بأنفسهم ، فحينئذٍ يسبقهم شرطة مائلة للخلف (تسمى أحيانًا التدريع). لذلك ، عند البحث عن الجزء "g." علينا أن نكتب في التعبير النمطي السيد إذا كنا نبحث عن علامة زائد ، إذن + وما إلى ذلك.

الحرفان التاليان في النموذج ، النقطة وعلامة النجمة المحددة ، يمثلان أي عدد من أي حرف ، أي اسم أي مدينة.

توجد فاصلة في نهاية النموذج ، لأننا نبحث عن نص من "g". إلى فاصلة. لكن يمكن أن يكون هناك عدة فواصل في النص ، أليس كذلك؟ ليس فقط بعد المدينة ، ولكن أيضًا بعد الشارع والمنازل وما إلى ذلك. على أي منها يتوقف طلبنا؟ هذا هو سبب علامة الاستفهام. بدونها ، فإن تعبيرنا النمطي سيسحب أطول سلسلة ممكنة:

من حيث التعبيرات النمطية ، فإن هذا النمط هو "الجشع". لتصحيح الموقف ، هناك حاجة إلى علامة استفهام - تجعل المحدد الكمي يقف بعد ذلك "بخيلًا" - ويأخذ استعلامنا النص فقط إلى أول فاصلة مضادة بعد "g".:

اسم الملف من المسار الكامل

هناك موقف شائع آخر وهو استخراج اسم الملف من المسار الكامل. سيساعد هنا التعبير العادي البسيط للنموذج:

الحيلة هنا هي أن البحث ، في الواقع ، يحدث في الاتجاه المعاكس - من النهاية إلى البداية ، لأنه في نهاية القالب لدينا $، ونحن نبحث عن كل شيء قبل أن يصل إلى أول شرطة مائلة للخلف من اليمين. تم تخطي الشرطة المائلة للخلف ، مثل النقطة في المثال السابق.

PS

"نحو النهاية" أريد أن أوضح أن كل ما سبق هو جزء صغير من جميع الاحتمالات التي توفرها التعبيرات العادية. هناك الكثير من الشخصيات والقواعد الخاصة لاستخدامها ، وقد تمت كتابة كتب كاملة حول هذا الموضوع (أوصي بهذا الكتاب كبداية على الأقل). بطريقة ما ، تعد كتابة التعبيرات العادية فنًا تقريبًا. دائمًا تقريبًا ، يمكن تحسين التعبير النمطي المبتكر أو استكماله ، مما يجعله أكثر أناقة أو قادرًا على العمل مع نطاق أوسع من بيانات الإدخال.

لتحليل التعبيرات العادية للأشخاص الآخرين وتحليلها أو تصحيح الأخطاء الخاصة بك ، هناك العديد من الخدمات المريحة عبر الإنترنت: RegEx101, RegExr و اكثر

لسوء الحظ ، لا يتم دعم جميع ميزات التعبيرات العادية الكلاسيكية في VBA (على سبيل المثال ، البحث العكسي أو فصول POSIX) ويمكن أن تعمل مع السيريلية ، لكنني أعتقد أن ما هو موجود يكفي لأول مرة لإرضائك.

إذا لم تكن جديدًا على الموضوع ، وكان لديك شيء تريد مشاركته ، فاترك التعبيرات العادية مفيدة عند العمل في Excel في التعليقات أدناه. عقل واحد جيد ، لكن حذائين هما زوج!

• استبدال النص وتنظيفه بوظيفة الاستبدال
• بحث وتسليط الضوء على الأحرف اللاتينية في النص
• ابحث عن أقرب نص مشابه (Ivanov = Ivonov = Ivanof ، إلخ.)