كيفية الاستفادة من وظائف REGEX في Excel لتحسين عمليات البيانات

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

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

ملاحظة
تتوفر وظائف REGEX للأشخاص الذين يستخدمون Excel for Microsoft 365 على Windows أو Mac، وكذلك أولئك الذين يستخدمون Excel للويب.

كيفية استخدام REGEXTEST

تختبر هذه الوظيفة ما إذا كانت سلسلة من النصوص تطابق نمطًا معينًا، وترجع TRUE أو FALSE بناءً على هذا الاختبار. هذه طريقة رائعة لاختبار ما إذا كانت بياناتك تتبع نمطًا معينًا.

بناء الجملة

REGEXTEST(a,b,c)

حيث

• a (مطلوب) هو النص أو القيمة أو مرجع الخلية الذي يحتوي على النص الذي تريد اختباره،
• b (مطلوب) هو النمط المستخدم لإجراء الاختبار، و
• c (اختياري) إما 0 إذا كنت تريد أن يكون الاختبار حساسًا لحالة الأحرف، أو 1 إذا لم يكن كذلك.

مثال على كيفية استخدام REGEXTEST

تحتوي جدول البيانات هذا على قائمة بأكواد المنتجات التي يجب أن تتبع بنية صارمة.

يحتوي الكود الصحيح على:

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

لذا، في الخلية B2، سأكتب:

=REGEXTEST([@Code],"[xs|s|m|l|xl]-[0-9]{1,2}-[A-Z]{3}",0)

حيث

• [@Code] هو مرجع منظم إلى العمود الذي توجد فيه الرموز التي أريد اختبارها،
• [xs|s|m|l|xl] هو الجزء الأول من رمز المنتج الذي أريد اختباره، حيث تعني الخطوط الرأسية “أو”،
• [0-9]{1,2} هو الجزء الثاني من رمز المنتج الذي أريد اختباره، حيث يمثل [0-9] أي رقم مفرد، و{1,2} يعني أنه يمكن أن يكون هناك رقم مفرد واحد أو رقمان مفردان،
• [A-Z]{3} هو الجزء الثالث من رمز المنتج الذي أريد اختباره، حيث يمثل [A-Z] أي حرف كبير، و{3} يعني أنه يجب أن يكون هناك ثلاثة أحرف من هذه الأحرف بالضبط،
• يتم فصل الأجزاء الثلاثة من الرمز الذي أريد اختباره بواصلة، و
• 0 هو الوسيطة الأخيرة في الصيغة التي تخبر Excel أن الاختبار حساس لحالة الأحرف.

عندما أضغط على Enter لتطبيق هذه الصيغة على جميع الصفوف في العمود B، تكشف النتيجة أن اثنين فقط من الرموز صالحة (TRUE).

m-2-UK غير صالح (يُشار إليه بنتيجة FALSE) لأن رمز الدولة يحتوي على حرفين كبيرين فقط، وxl-714-AUS غير صالح لأن رمز المادة يحتوي على ثلاثة أرقام، وS-5-USA غير صالح لأن رمز الحجم كبير.

ملاحظة
يحتوي هذا المثال على استخدام أحرف مثل [ ] و{ }. ومع ذلك، هناك العديد من الأحرف الأخرى (المعروفة أيضًا باسم الرموز) التي يمكن استخدامها أيضًا لتحديد النمط المستخدم لإجراء الاختبار، وسأستخدم بعضها في الأمثلة أدناه.

REGEXEXTRACT: البحث عن أجزاء محددة من النص
ترجع هذه الوظيفة أجزاء من النص في خلية وفقًا لنمط محدد. على سبيل المثال، قد ترغب في فصل الأرقام والنص.

بناء الجملة

REGEXEXTRACT(d,e,f,g)

حيث

• d (مطلوب) هو النص أو القيمة أو مرجع الخلية الذي يحتوي على النص الذي تريد استخراجه منه،
• e (مطلوب) هو النمط الذي تريد استخراجه،
• f (اختياري) يساوي 0 إذا كنت تريد استخراج المطابقة الأولى فقط، و1 لاستخراج جميع التطابقات القابلة للتطبيق كمصفوفة، و2 لاستخراج المجموعات من المطابقة الأولى، و
• g (اختياري) يساوي إما 0 إذا كنت تريد أن يكون الاستخراج حساسًا لحالة الأحرف، أو 1 إذا لم يكن كذلك.

ملاحظة
نظرًا لأن جداول Excel المنسقة لا يمكنها التعامل مع المصفوفات المنسكبة، فإذا كنت تنوي استخراج التطابقات كمصفوفة في الوسيطة f، فتأكد من تنسيق بياناتك بشكل واضح.

مثال على كيفية استخدام REGEXTRACT

في هذا المثال، أريد استخراج الأسماء الأولى والأخيرة وأرقام هواتف العملاء في ثلاثة أعمدة منفصلة.

دعونا نركز على الأسماء أولاً. في الخلية B2، سأكتب:

=REGEXEXTRACT(A2,"[A-Z][a-z]+",1)

حيث

• A2 هي الخلية التي تحتوي على البيانات التي أريد استخراجها،
• [A-Z][a-z]+ يخبر Excel أنني أريد استخراج أي كلمات تبدأ بحرف كبير متبوعًا بأحرف صغيرة، مع الإشارة إلى “+” أنني أريد إرجاع حرف واحد أو أكثر من الأحرف الصغيرة في كل نمط، و
• 1 يشير إلى أنني أريد فصل كل مثال من النمط أعلاه إلى خلايا فردية كمصفوفة (بعبارة أخرى، الاسم الأول في الخلية B2، والاسم الثاني في الخلية C2). إذا حذفت هذه الوسيطة، فسيعيد Excel المطابقة الأولى (الاسم الأول) فقط في الخلية B2.

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

مع تحديد الخلية B2، يمكنني الآن استخدام مقبض التعبئة في الزاوية اليمنى السفلية من الخلية لمضاعفة هذه الصيغة النسبية إلى الصفوف المتبقية من التفاصيل.

الآن، أحتاج إلى استخدام صيغة REGEXTRACT مماثلة لاستخراج أرقام هواتف العملاء. في الخلية D2، سأكتب:

=REGEXEXTRACT(A2,"[0-9()]+ [0-9-]+")

حيث

• A2 هي الخلية التي تحتوي على البيانات التي أريد استخراجها،
• [0-9()]+ يستخرج الأرقام من صفر إلى تسعة الموجودة بين قوسين مدورين، مع استخراج “+” لرقم واحد أو أكثر في هذا النمط، و
• [0-9-]+ يستخرج الأرقام المتبقية من السلسلة، مع تمثيل “-” الثانية للشرطة التي تفصل بين جزأين من رقم الهاتف، و”+” تخبر Excel أنني أريد استخراج رقم واحد أو أكثر إذا كانت السلسلة تحتوي عليها.

نظرًا لوجود مثيل واحد فقط لهذا النمط في كل خلية في العمود A، فلا أحتاج إلى إضافة أي وسيطات أخرى. مرة أخرى، بمجرد التحقق من أن هذه الصيغة تنتج النتيجة المتوقعة، يمكنني استخدام مقبض التعبئة لتكرارها في الخلايا المتبقية في العمود D.

ملاحظة
توجد طرق أخرى في برنامج Excel لاستخراج البيانات وتحقيق نتائج مماثلة، مثل استخدام دالة TEXTSPLIT أو أداة Flash Fill في برنامج Excel.

التلاعب بالبيانات باستخدام REGEXREPLACE

تأخذ هذه الدالة النص الموجود في خلية وتنشئ نسخة جديدة من هذه البيانات في خلية أخرى. ورغم أن هذه الدالة تسمى REGEXREPLACE، إلا أنها لا تحل محل النص الأصلي في موقعه الأصلي.

بناء الجملة

REGEXREPLACE(h,i,j,k,l)

حيث

• h (مطلوب) هو النص أو القيمة أو مرجع الخلية الذي يحتوي على النص الذي تريد استبداله،
• i (مطلوب) هو النمط الذي تريد استبداله،
• j (مطلوب) هو الاستبدال الذي تريد إنشاءه،
• k (اختياري) هو تكرار النمط الذي تريد استبداله، و
• l (اختياري) إما 0 إذا كنت تريد أن يكون الاستبدال حساسًا لحالة الأحرف، أو 1 إذا لم يكن كذلك.

مثال على كيفية استخدام REGEXREPLACE

فيما يلي، يمكنك رؤية قائمة بالأسماء في العمود A. هدفي هو إعادة إنشاء هذه الأسماء في العمود B، ولكن باستخدام تنسيق “الاسم الأخير، الاسم الأول”، بما في ذلك الفاصلة التي تفصل الأسماء.

في الخلية B2 سأكتب:

=REGEXREPLACE([@Client name],"([A-Z][a-z]+) ([A-Z][a-z]+)","$2, $1")

حيث

• [@Client name] يشير إلى العمود الذي يحتوي على البيانات التي أريد التأثير عليها،
• [A-Z][a-z]+ المضمن مرتين في الصيغة (والمفصول بمسافة) يخبر Excel أنني أريد أخذ سلسلتي النص اللتين تحتويان على حرف كبير متبوعًا بحرف صغير واحد أو أكثر، و
• $2, $1 يخبر Excel أنني أريد عكس ترتيب سلسلتي النص اللتين تفصل بينهما فاصلة ومسافة. إذا لم أقم بتضمين رموز الدولار، فسيعيد Excel ببساطة “2, 1” كنتيجة في كل خلية.

لم أتناول الوسيطتين k وl في الصيغة أعلاه لأنني أريد أن يستبدل Excel جميع التكرارات (الافتراضي للوسيطة k)، وأريد أن يكون الاستبدال حساسًا لحالة الأحرف (الافتراضي للوسيطة l).

لأنني أستخدم جدولًا منسقًا، فعندما أضغط على Enter، ستطبق الصيغة على الخلايا المتبقية في العمود B.

لا تقتصر استخدامات التعبيرات العادية على برنامج Excel. في الواقع، يمكنك استخدام REGEX لأتمتة مهام أخرى على جهاز الكمبيوتر الخاص بك، مثل إصلاح نص PDF المنسوخ والملصق، وإعادة تسمية الملفات التي تم تنزيلها بشكل مجمع، وتنسيق العملة، وإزالة علامات HTML، والمزيد.