إكسيل: كيفية التحقق مما إذا كانت الخلية تحتوي على نص من قائمة

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

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

يتناول هذا المرجع البحثي الشامل تفكيكاً نظرياً وتطبيقياً متكاملاً لكافة الاستراتيجيات المتاحة لإجراء هذه المطابقات؛ حيث نسبر أغوار الصيغ التقليدية المعتمدة على دمج الدالتين COUNTIF و OR مع أحرف البدل، مروراً بالهياكل المصفوفية المتقدمة المستندة إلى SUMPRODUCT و SEARCH، وصولاً إلى تقنيات المصفوفات الديناميكية الحديثة ودوال المعالجة السطرية عبر LAMBDA و BYROW، مع تسليط الضوء على تقنيات الأداء العالي وتفادي الأخطاء في مجموعات البيانات الضخمة.

1. المقدمة والأسس النظرية لمعالجة النصوص المتقدمة في إكسيل

1.1 مفهوم مطابقة الأنماط والبحث النصي المتعدد

ترتكز معالجة النصوص الحسابية على مفهوم مطابقة الأنماط (Pattern Matching)، وهو فرع جوهري من علوم الحاسوب يهتم بفحص السلاسل النصية للتحقق من وجود ترتيبات محددة من المحارف. وفي سياق جداول البيانات، تنشأ المشكلة عندما يتطلب التحليل فحص خلية نصية مفردة تحتوي على وصف نصي مطول وغير مهيكل (Unstructured Text) لمعرفة ما إذا كانت تحتوي على أي عنصر من قائمة تحتوي على $N$ من الكلمات المفتاحية الفرعية.

يكمن الفارق الجوهري بين المطابقة التامة (Exact Match) والمطابقة الجزئية (Substring Matching) في آلية المقارنة؛ فالمطابقة التامة تشترط تطابق السلسلة النصية للخلية المستهدفة بالكامل وبكافة محارفها مع القيمة المرجعية دون زيادة أو نقصان. في المقابل، تتطلب المطابقة الجزئية البحث عن وجود “السلسلة الفرعية” داخل السلسلة الأم بغض النظر عن موقعها أو النصوص المحيطة بها. يمثل هذا التمييز حجر الزاوية في مشاريع تنظيف البيانات (Data Cleansing)، حيث لا يمكن الاعتماد على المطابقات التامة نظراً لتباين صياغات الإدخال البشري ووجود لواحق وسوابق نصية غير منضبطة.

تتجلى أهمية المطابقة الجزئية في تصنيف وتوصيف البيانات الضخمة في قطاعات المصارف واللوجستيات؛ إذ يتيح تطبيق هذه التقنية فحص الملايين من سجلات القيود اليومية لتصنيف النفقات تلقائياً (مثل تصنيف السجلات التي تحتوي على “Uber” أو “Careem” أو “Taxi” تحت بند التنقلات)، مما يوفر آلاف الساعات من الجهد اليدوي ويقضي على احتمالات الخطأ البشري.

1.2 التحديات الحسابية في التعامل مع المصفوفات النصية

عند الشروع في بناء حلول برمجية لمطابقة النصوص المتعددة، تبرز معضلة التعقيد الحسابي (Computational Complexity) واستهلاك الذاكرة. فإذا كان لدينا جدول يحتوي على $M$ من الصفوف، ونرغب في مقارنة كل صف بقائمة مرجعية تحتوي على $N$ من الكلمات، فإن عدد العمليات المقارنة النظرية يصل إلى $M \times N$. وفي مجموعات البيانات التي تحتوي على مئات الآلاف من السجلات، قد يؤدي استخدام خوارزميات غير كفؤة إلى تجميد محرك الحسابات في إكسيل واستهلاك مفرط لدورات وحدة المعالجة المركزية (CPU).

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

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

2. تفكيك الصيغة القياسية: COUNTIF وأحرف البدل (Wildcards)

2.1 آلية عمل أحرف البدل في بيئة إكسيل

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

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

لدمج أحرف البدل مع مراجع الخلايا التي تضم الكلمات المفتاحية، يُستخدم معامل الربط النصي (&)؛ حيث يتم بناء المعيار البرمجي على النحو الآتي: "*" & E2 & "*". يقوم هذا التعبير بدمج القيمة النصية الموجودة في الخلية المرجعية E2 مع النجوم المحيطة بها، محولاً القيمة النصية العادية إلى معيار بحث مرن يتعرف عليه محرك إكسيل كنمط مطابقة شامل.

2.2 الديناميكية الحسابية للدالة COUNTIF مع نطاقات المصفوفات

صُممت الدالة القياسية COUNTIF في الأصل لحساب عدد الخلايا داخل نطاق معين والتي تحقق معياراً محدداً وفق الصيغة COUNTIF(range, criteria). غير أن القوة الحقيقية لهذه الدالة تظهر عند عكس الاستخدام المألوف؛ أي عند تمرير خلية فردية واحدة كنطاق للبحث، وتمرير نطاق كامل من الخلايا كمعيار للبحث عبر استخدام أحرف البدل.

عند صياغة الدالة بالشكل التالي: COUNTIF(A2, "*" & $E$2:$E$8 & "*")، لا تقوم الدالة بإرجاع رقم فردي مباشر، بل تقوم بتقييم الخلية A2 مقابل كل عنصر من عناصر النطاق $E$2:$E$8 على حدة. ينتج عن هذه العملية مصفوفة ذاكرية وسيطة تتألف من قيم عددية؛ حيث يمثل الرقم 1 (واحد) تحقق الشرط ووجود الكلمة المفتاحية داخل الخلية، بينما يمثل الرقم 0 (صفر) إخفاق المطابقة وغياب الكلمة.

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

3. الهيكل المنطقي: تكامل الدالتين OR و IF

3.1 تحويل مصفوفة الأعداد إلى قيمة منطقية عبر دالة OR

عقب توليد مصفوفة الأصفار والآحاد بواسطة الدالة COUNTIF، تنشأ الحاجة إلى اختزال هذه السلسلة العددية المتعددة إلى قرار منطقي حاسم يحدد ما إذا كانت الخلية تحتوي على “أي” من تلك الكلمات المفتاحية. وهنا يأتي الدور المحوري للدالة المنطقية OR، التي تقوم باختبار المصفوفة التكرارية بالكامل للتحقق من وجود قيمة إيجابية واحدة على الأقل.

يعتمد المحرك الحسابي لإكسيل على قواعد المنطق البولياني؛ حيث يتم تقييم الرقم 0 تلقائياً كقيمة خطأ منطقي (FALSE)، بينما يُعامل أي رقم موجب (مثل الرقم 1 الناتج عن نجاح دالة العد) كقيمة صواب منطقي (TRUE). بناءً على ذلك، إذا احتوت المصفوفة على القيم {0; 0; 1; 0}، فإن دالة OR تفحص هذه القيم وتُرجع مباشرة القيمة البوليانية العامة TRUE نظراً لتحقق شرط واحد على الأقل.

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

3.2 صياغة المخرجات النهائية وتخصيصها عبر دالة IF

تكتمل المنظومة الحسابية بدمج الهيكل المنطقي داخل الدالة الشرطية الأساسية IF، مما يتيح للمحلل التحكم الكامل في شكل المخرجات وطبيعتها بما يتوافق مع متطلبات لوحات التحكم ونماذج التقارير الإدارية. يتخذ البناء العام للصيغة التعبير التالي:

=IF(OR(COUNTIF(A2, "*" & $E$2:$E$8 & "*")), "Yes", "No")

يقوم هذا التركيب بتقييم النتيجة الصادرة عن دالة OR؛ فإذا كانت النتيجة المنطقية TRUE، تنفذ الدالة المسار الأول وتعيد النص المخصص الممثل للمطابقة (مثل “Yes” أو “مطابق” أو “مقبول”). أما إذا كانت النتيجة FALSE لكافة عناصر القائمة المرجعية، فإن الدالة تسلك المسار الثاني وتعيد النص الممثل للإخفاق (مثل “No” أو “غير مطابق”).

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

4. التطبيق العملي خطوة بخطوة: دراسة حالة بيانات الفرق الرياضية

4.1 تهيئة مجموعة البيانات وهيكلة النطاقات المرجعية

لترسيخ المفاهيم السابقة في إطار تطبيقي عملي، نفترض وجود دراسة حالة تحليلية تتضمن سجلاً لبيانات عقود الرياضيين المحترفين في دوري كرة السلة الأمريكي، حيث يحتوي الجدول الرئيسي في العمود A على نصوص وصفية مركبة تضم اسم اللاعب متبوعاً بالفريق الحالي وتفاصيل العقد (مثل: “Luka Doncic – Dallas Mavs – Guard” أو “Stephen Curry – Golden State Warriors”). يكمن الهدف التحليلي في التحقق مما إذا كان اللاعب ينتمي إلى أي من فرق ولاية تكساس الشهيرة، وتحديد ذلك عبر مؤشر قطعي.

تتطلب الخطوة الأولى عزل القائمة المرجعية وهيكلتها في نطاق مستقل تماماً عن الجدول الرئيسي لضمان مبادئ هندسة البيانات السليمة. نخصص النطاق E2:E4 ليضم الكلمات المفتاحية الدالة على فرق تكساس: حيث تحتوي الخلية E2 على “Mavs”، والخلية E3 على “Spurs”، والخلية E4 على “Rockets”.

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

4.2 كتابة الصيغة وتعميمها عبر السجلات

نبدأ بتطبيق الصيغة المركبة في الخلية B2 المجاورة للسجل الأول في جدول البيانات، حيث ندخل الصيغة التالية بعناية فائقة:

=IF(OR(COUNTIF(A2, "*" & $E$2:$E$4 & "*")), "Texas Team", "Other")

عند ضغط مفتاح الإدخال، يقوم محرك إكسيل باختبار النص في A2؛ فإذا وجد داخله مقطع “Mavs”، تعيد دالة العد مصفوفة تحتوي على الرقم 1، وتلتقطه دالة OR ليتحول إلى TRUE، وتنتج الخلية في النهاية النص “Texas Team”. بعد التحقق من دقة الخلية التجريبية الأولى، يتم تعميم الصيغة عبر النقر المزدوج على مقبض التعبئة (Fill Handle) في الزاوية السفلية للخلية B2 لنسخ المعادلة حتى نهاية السجلات.

تُظهر المراجعة التدقيقية للنتائج أن السجلات التي تضم “Spurs” أو “Rockets” قد تم وسمها بدقة بـ “Texas Team”، في حين حصلت السجلات الأخرى الخاصة بفرق مثل “Warriors” أو “Lakers” على الوسم “Other”. يثبت هذا الاختبار الميداني فاعلية الصيغة في استخراج الأنماط النصية بكفاءة وموثوقية مطلقة.

5. إدارة مراجع الخلايا وأنواع البيانات في الصيغ المتقدمة

5.1 المراجع المطلقة والنسبية والمختلطة في سياق المطابقة النصية

تعد الإدارة الرصينة لمراجع الخلايا أحد المعايير الحاسمة التي تفصل بين النماذج التحليلية الاحترافية وتلك المعرضة للانهيار البرمجي. تنقسم المراجع في إكسيل إلى ثلاثة أنماط رئيسية: المراجع النسبية (Relative References مثل A1)، والمراجع المطلقة (Absolute References مثل $A$1)، والمراجع المختلطة (Mixed References مثل $A1 أو A$1). ويتحدد سلوك كل نمط عند سحب الصيغة وتكرارها عبر الصفوف والأعمدة.

في مسائل المطابقة النصية المتعددة، يؤدي إغفال تثبيت القائمة المرجعية بالرموز المطلقة إلى وقوع خطأ التحيز والانزياح المرجعي؛ فإذا كُتبت الصيغة بالمرجع النسبي E2:E8 وسُحبت للأسفل بمقدار صف واحد، ستتحول تلقائياً إلى E3:E9، مما يعني خروج العنصر الأول من نطاق الفحص والدخول في خلايا فارغة، مولداً ما يُعرف بالنتائج السلبية الكاذبة (False Negatives).

للارتقاء بجودة النموذج وتفادي أخطاء المراجع اليدوية، يُنصح بشدة بتحويل القائمة المرجعية إلى نطاق مسمى (Named Range) مثل تسمية النطاق بـ TargetKeywords. تتيح هذه التسمية صياغة المعادلة كالتالي: =IF(OR(COUNTIF(A2, "*" & TargetKeywords & "*")), "Yes", "No")، مما يمنح المعادلة وضوحاً لغوياً فائقاً ويجعلها مطلقة بطبيعتها دون الحاجة لاستخدام إشارات الدولار المتكررة.

5.2 التعامل مع الخلايا الفارغة والمسافات البيضاء المخفية

تشكل المسافات البيضاء المخفية (Leading and Trailing Spaces) أحد أكثر العوائق الخفية المسببة لفشل عمليات المطابقة النصية؛ إذ إن وجود مسافة إضافية غير مرئية في نهاية الكلمة المفتاحية (مثل “Spurs “) سيجعل الدالة تبحث عن الكلمة متبوعة بمسافة، مما يؤدي إلى إخفاق المطابقة إذا كان النص المستهدف يحتوي على الكلمة في نهاية السطر دون مسافات لاحقة.

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

علاوة على ذلك، يمثل وجود خلايا فارغة داخل نطاق القائمة المرجعية خطراً جسيماً؛ حيث يؤدي دمج خلية فارغة مع أحرف البدل "*" & "" & "*" إلى توليد المعيار "**"، وهو ما يتطابق مع أي نص على الإطلاق، مما يجعل الدالة تعيد النتيجة TRUE لكافة صفوف الجدول بلا استثناء (False Positive كارثي). يتطلب علاج هذه الحالة التأكد التام من خلو القائمة من الفراغات أو تصفيتها مسبقاً عبر معادلات ديناميكية تستثني الفراغ.

6. البدائل المتقدمة: استخدام SUMPRODUCT مع SEARCH و ISNUMBER

6.1 البحث غير الحساس لحالة الأحرف (Case-Insensitive Search)

رغم كفاءة صيغة COUNTIF، فإن بنيتها الصارمة قد لا تلائم كافة الهياكل الحسابية المتقدمة. تبرز هنا التركيبة الكلاسيكية العملاقة المكونة من الدوال SUMPRODUCT، و ISNUMBER، و SEARCH كواحدة من أكثر الصيغ مرونة وموثوقية في تاريخ إكسيل لمعالجة المطابقات غير الحساسة لحالة الأحرف.

تعمل الدالة SEARCH على فحص النص المستهدف والبحث عن مواضع الكلمات المفتاحية؛ فإذا عثرت على الكلمة، تُرجع رقماً يمثل الترتيب الموضعي للمحرف الذي تبدأ منه الكلمة داخل النص، أما إذا لم تعثر عليها، فإنها تُرجع خطأ القيمة #VALUE!. وهنا تتدخل الدالة ISNUMBER لتقوم بتطويق هذا المخرج؛ حيث تحول الأرقام الموضعية إلى TRUE، وتحول قيم الأخطاء إلى FALSE.

تُصاغ المعادلة الشاملة على النحو التالي:

=SUMPRODUCT(--(ISNUMBER(SEARCH($E$2:$E$8, A2)))) > 0

يقوم المعامل الحسابي المزدوج (Double Unary --) بتحويل القيم المنطقية TRUE و FALSE إلى قيم عددية 1 و 0 على التوالي. وتتولى الدالة SUMPRODUCT جمع عناصر المصفوفة دون الحاجة لضغط مفاتيح التحكم والصفائف التقليدية (CSE). فإذا كان المجموع الإجمالي أكبر من الصفر، دل ذلك قطيعةً على وجود تطابق واحد على الأقل، مما يمنح المحلل بنية حسابية صلبة وقابلة للدمج في العمليات الرياضية المعقدة.

6.2 البحث الحساس لحالة الأحرف باستخدام دالة FIND

في العديد من التطبيقات المهنية الحساسة، مثل تحليل الرموز المشفرة، وأكواد المنتجات الصناعية، والأرقام التسلسلية، أو التمييز بين الاختصارات اللاتينية المكتوبة بأحرف كبيرة وتلك المكتوبة بأحرف صغيرة، تصبح الدالة SEARCH غير مناسبة نظراً لطبيعتها المتساهلة التي لا تفرق بين الحروف الكبيرة والصغيرة (Case-Insensitive).

لحل هذه المعضلة، يتم استبدال الدالة SEARCH بالدالة الصارمة FIND، والتي تشترط تطابقاً حرفياً تاماً لحالة الأحرف ومواضعها. تأخذ الصيغة الشكل التالي:

=SUMPRODUCT(--(ISNUMBER(FIND($E$2:$E$8, A2)))) > 0

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

7. استثمار دوال Dynamic Arrays الحديثة في Microsoft 365

7.1 تطبيق الدالتين FILTER و BYROW في المعالجة التلقائية

أحدث إطلاق محرك الحسابات الديناميكي في إصدارات Microsoft 365 ثورة جذرية في كيفية بناء الصيغ؛ حيث انتهى عصر سحب الصيغ يدوياً عبر آلاف الصفوف بفضل خاصية “الانسكاب التلقائي” (Spill Behavior)، واستحداث دوال برمجية متقدمة من رتبة أعلى تعتمد على منطق حساب لامدا (Lambda Calculus).

تتيح الدالة الحديثة BYROW تطبيق منطق فحص مصفوفي على كل صف في نطاق البيانات دفعة واحدة من خلال خلية رئيسية مفردة. تُصاغ المعادلة المتطورة على النحو التالي:

=BYROW(A2:A1000, LAMBDA(row, IF(OR(COUNTIF(row, "*" & TargetList & "*")), "Yes", "No")))

تُكتب هذه الصيغة في الخلية B2 فقط، وتتكفل الدالة بمعالجة كافة الصفوف من A2 إلى A1000 ذاتياً وتسكب النتائج في العمود B بالكامل. يضمن هذا النهج المعماري حماية الصيغ من التعديل العبثي في الصفوف الوسيطة، ويقلل الحجم الكلي للمصنف، فضلاً عن سهولة التعديل المركزي لمنطق الحساب من موضع واحد.

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

7.2 استخدام دالة TEXTJOIN لدمج وتلخيص النتائج

في العديد من المهام التحليلية المتقدمة، لا يكتفي متخذ القرار بمعرفة مجرد وجود تطابق من عدمه، بل يحتاج إلى تقرير وصفي ملخص يوضح “كافة” الكلمات المفتاحية التي تطابقت مع السجل داخل نفس الخلية. تبرز هنا القوة التعبيرية للدالة TEXTJOIN المدعومة بالتصفية المصفوفية.

تُبنى صيغة التلخيص النصي الديناميكي كالتالي:

=TEXTJOIN(", ", TRUE, FILTER($E$2:$E$8, ISNUMBER(SEARCH($E$2:$E$8, A2)), "None"))

تقوم هذه الصيغة بفحص الخلية A2 مقابل القائمة المرجعية $E$2:$E$8؛ وتعمل دالة FILTER على استخراج كافة الكلمات التي تحقق شرط المطابقة، ثم تقوم دالة TEXTJOIN بدمج تلك الكلمات المعثور عليها في سلسلة نصية واحدة مفصولة بفواصل مخصصة (مثل: “Mavs, Spurs”). وفي حال انعدام أي تطابق، تُرجع الدالة كلمة “None”.

يعد هذا النمط الحسابي فائق الأهمية في بناء تقارير الوسوم المتعددة (Multi-tagging Systems) والتصنيفات الجغرافية والوظيفية المتداخلة، مما يحول جدول البيانات إلى أداة تصنيف دلالي بالغة التطور والتنظيم.

8. استخراج النص المطابق الفعلي بدلاً من المؤشرات البوليانية

8.1 صياغة معادلة إرجاع الكلمة المطابقة من القائمة

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

تتحقق هذه الوظيفة بامتياز عبر دمج الدالتين الكلاسيكيتين INDEX و MATCH مع مصفوفة الفحص الحسابي، وتُصاغ المعادلة كالتالي:

=INDEX($E$2:$E$8, MATCH(TRUE, ISNUMBER(SEARCH($E$2:$E$8, A2)), 0))

تبحث دالة MATCH هنا عن أول قيمة منطقية TRUE ناتجة عن فحص مصفوفة النصوص داخل الخلية A2، وتستخرج رقم الموقع الترتيبي لتلك الكلمة ضمن القائمة المرجعية. بعد ذلك، تتولى دالة INDEX جلب النص الفعلي المتواجد في ذلك الموقع الدقيق وإدراجه في الخلية المستهدفة.

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

8.2 معالجة التعدد والتكرار في التطابقات النصية

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

تعتمد صيغة INDEX/MATCH أو XLOOKUP القياسية على استرجاع “أول تطابق فقط” تصادفه في الترتيب الرأسي للقائمة المرجعية. بناءً على ذلك، يصبح الترتيب الهرمي للعناصر داخل نطاق القائمة ذا أهمية استراتيجية قصوى؛ إذ يجب وضع الكلمات الأكثر أهمية أو الأكثر تخصيصاً في أعلى القائمة لضمان منحها أولوية الاسترجاع عند التنافس والتداخل.

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

9. التنسيق الشرطي المؤتمت المعتمد على القوائم المرجعية

9.1 تطبيق قواعد التنسيق الشرطي باستخدام صيغ المطابقة

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

لإنشاء هذه القاعدة البصرية، يتم تحديد نطاق جدول البيانات المطلوب تظليله بالكامل (على سبيل المثال: $A$2:$C$100)، ثم التوجه إلى إدارة قواعد التنسيق الشرطي واختيار إنشاء قاعدة جديدة باستخدام صيغة مخصصة (Custom Formula). ندخل الصيغة المنطقية التي تم تقييمها مسبقاً، مع مراعاة تثبيت العمود النسبي للصف:

=OR(COUNTIF($A2, "*" &$E$2:$E$8 & "*"))

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

9.2 إدارة وتحديث القواعد في مجموعات البيانات المتغيرة

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

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

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

10. تشخيص الأخطاء الشائعة واستراتيجيات المعالجة والتصحيح

10.1 أخطاء الحساب والمخرجات غير المتوقعة (#VALUE!, #N/A)

أثناء بناء وتطبيق الصيغ المصفوفية المركبة، قد يواجه المحلل ظهور أخطاء حسابية مزعجة تعطل تدفق العمليات. يُعد خطأ القيمة #VALUE! من أكثر الأخطاء شيوعاً، وتعود أسبابه في الغالب إلى فشل الدالة SEARCH أو FIND في العثور على النص داخل الخلية عند استخدامها بشكل غير معزول بدالة التحقق ISNUMBER، أو نتيجة استخدام صيغ المصفوفات القديمة في إصدارات إكسيل السابقة لعام 2021 دون تثبيتها بمفاتيح Ctrl + Shift + Enter.

لتطويق الأخطاء وحماية النماذج من الانهيار، يجب استخدام دوال المعالجة الاستباقية مثل الدالة IFERROR أو الدالة IFNA لإرجاع قيم بديلة آمنة مثل النصوص الفارغة "" أو التنبيهات المخصصة عند حدوث أي خلل أثناء الحساب.

كما تبرز أخطاء خفية ناجمة عن عدم توافق ترميز النصوص (Text Encodings)، وخاصة عند استيراد نصوص متعددة اللغات تحتوي على محارف خاصة، أو اختلافات في الهمزات وعلامات التشكيل في اللغة العربية (مثل الخلط بين ‘أ’, ‘إ’, ‘ا’)؛ مما يفرض توحيد ومعايرة النصوص مسبقاً عبر عمليات استبدال منتظمة لضمان دقة وتطابق عمليات الفحص.

10.2 مشكلات التطابق الجزئي الخاطئ (False Positives)

يمثل التطابق الجزئي الخاطئ التحدي الأكبر والأخطر في خوارزميات مطابقة النصوص؛ ويحدث عندما تتطابق السلسلة الفرعية المطلوبة مع جزء داخلي من كلمة أطول ذات دلالة مختلفة تماماً. على سبيل المثال، إذا كانت القائمة المرجعية تحتوي على الكلمة الإنجليزية “cat” (قط)، فإن البحث الجزئي التقليدي سيعطي تطابقاً إيجابياً خاطئاً مع كلمات مثل “category” (فئة) أو “certificate” (شهادة) أو “vacation” (إجازة).

للتغلب على هذه المعضلة وتحقيق ما يُعرف بمطابقة الكلمة الكاملة (Whole Word Matching)، يتم استخدام تقنية “التطويق بالمسافات” (Space Padding). تتلخص هذه التقنية في إضافة مسافة فارغة قبل وبعد النص المستهدف، وكذلك قبل وبعد الكلمة المفتاحية، بحيث يتم البحث عن " cat " داخل النص " the category was clear "، وهو ما يفشل بالطبع ويمنع التطابق الخاطئ.

تُصاغ معادلة التطويق بالمسافات الاحترافية على النحو التالي:

=OR(ISNUMBER(SEARCH(" " & $E$2:$E$8 & " ", " " & A2 & " ")))

تضمن هذه الحيلة الرياضية الذكية تقييد نطاق البحث بمحددات الكلمات (Word Boundaries)، مما يرفع دقة الاسترجاع إلى مستويات تضاهي استخدام التعابير النمطية (Regular Expressions) المتقدمة.

11. تحسين الأداء والكفاءة الحسابية في مجموعات البيانات الضخمة (Big Data)

11.1 تحليل استهلاك الموارد الحسابية (CPU & Memory Profiling)

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

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

تشير القياسات المعيارية للأداء إلى أن صيغة COUNTIF مع أحرف البدل تتفوق نسبياً في سرعة التنفيذ على صيغ SUMPRODUCT الحسابية الثقيلة في مصفوفات البيانات الكبيرة، نظراً لأن COUNTIF مكتوبة بلغة C++ التحتية المحسنة داخلياً للتعامل مع السلاسل النصية، مما يقلل من وقت معالجة المصنف بنسب تصل إلى 40% في الملفات الضخمة.

11.2 الانتقال إلى Power Query كبديل عالي الكفاءة لمعالجة النصوص

عندما تتخطى أحجام البيانات قدرات المعادلات اللحظية داخل واجهة إكسيل وتصل إلى ملايين السجلات المستوردة، يصبح الانتقال إلى محرك Power Query المدمج الحل الهندسي الأمثل والأكثر استدامة واحترافية.

يوفر Power Query بيئة معالجة غير تزامنية تعتمد على لغة M Language الفائقة السرعة؛ حيث يمكن إجراء المطابقات النصية وتصفية الجداول عبر دوال نصية متخصصة مثل Text.Contains دون كتابة معادلة واحدة تثقل ذاكرة أوراق العمل. كما يدعم Power Query تنفيذ عمليات “الربط المضبب” (Fuzzy Merge/Join)، والتي تتيح مطابقة النصوص المتشابهة وغير المتطابقة بنسب مئوية يحددها المستخدم لتجاوز الأخطاء الإملائية الصعبة.

تتمثل الميزة الكبرى لـ Power Query في أتمتة خط أنابيب البيانات (Data Pipeline) بالكامل؛ حيث يتم استيراد البيانات وتطهيرها وتصنيفها خارج واجهة العرض، وتصدير النتائج النهائية النظيفة والمصنفة فقط إلى جدول إكسيل مستقر، مما يضمن أداءً فائقاً وتجربة استخدام سريعة وسلسة مهما تعاظم حجم البيانات المؤسسية.

12. دليل المقارنة الشامل والتوصيات التطبيقية لاختيار الأسلوب الأمثل

12.1 مصفوفة المفاضلة بين الأساليب المختلفة

لتسهيل عملية اتخاذ القرار واختيار المنهجية الأنسب لكل مشروع تحليلي، يلخص الجدول الشامل التالي أوجه المقارنة التقنية بين الأساليب الرئيسية المتاحة للتحقق من النصوص داخل إكسيل:

الأسلوب الحسابي	سهولة البناء والقراءة	السرعة الحسابية والأداء	الحساسية لحالة الأحرف	التوافق مع إصدارات إكسيل	الاستخدام الموصى به
IF + OR + COUNTIF	عالية وبسيطة	ممتازة وسريعة جداً	غير حساسة (Case-Insensitive)	كافة الإصدارات القديمة والحديثة	التحقق الثنائي العام والسريع في الجداول القياسية
SUMPRODUCT + SEARCH	متوسطة	متوسطة إلى جيدة	غير حساسة (Case-Insensitive)	كافة الإصدارات (لا تتطلب CSE)	العمليات الحسابية والعد المشروط المتقدم
SUMPRODUCT + FIND	متوسطة	متوسطة	حساسة تماماً (Case-Sensitive)	كافة الإصدارات	فحص الأكواد الحساسة والرموز المشفرة
BYROW + LAMBDA	متقدمة	عالية الكفاءة مع المجموعات الديناميكية	حسب الدالة الفرعية المستخدمة	Microsoft 365 و Excel 2021+ فقط	أتمتة الجداول الحديثة دون سحب الصيغ
INDEX + MATCH + SEARCH	معقدة نسبياً	جيدة	غير حساسة	كافة الإصدارات	استخراج النص المطابق الفعلي ونقله للجدول
Power Query (M Code)	منفصلة عبر واجهة التحويل	الأعلى كفاءة في البيانات الضخمة	قابلة للضبط التام والمطابقة المضببة	Excel 2016 والإصدارات الأحدث	خطوط أنابيب البيانات الكبرى وتجهيز قواعد البيانات

12.2 أفضل الممارسات الهندسية لبناء مصنفات عمل مرنة ومستدامة

يتطلب بناء مصنفات عمل احترافية ومستدامة الالتزام بمجموعة من القواعد الهندسية الصارمة؛ في مقدمتها توثيق الصيغ المركبة وتدوين شروحات توضيحية داخل الخلايا أو عبر بطاقات الملاحظات لشرح المنطق المتبع، مما يسهل عمليات المراجعة والتدقيق المالي والتشغيلي من قبل الزملاء وفرق العمل المستقبلية.

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

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

الخلاصة

استعرضنا في هذا الدليل التأسيسي الشامل الأبعاد النظرية والتطبيقية لمسألة التحقق من احتواء الخلية على نص من قائمة مرجعية داخل برنامج مايكروسوفت إكسيل. لقد أثبت التحليل المنهجي أن الصيغة القياسية =IF(OR(COUNTIF(A2, "*" & Range & "*")), "Yes", "No") تمثل حجر الزاوية للمطابقات السريعة وغير الحساسة لحالة الأحرف عبر كافة إصدارات البرنامج، لما تتميز به من كفاءة برمجية وسرعة حسابية.

كما تم تسليط الضوء على الحلول المتقدمة باستخدام دوال SUMPRODUCT و SEARCH و FIND لتلبية الاحتياجات المتخصصة في التمييز الدقيق لحالة الأحرف ومطابقة الأنماط الصارمة، وصولاً إلى استغلال قدرات محرك الحسابات الحديث عبر دوال LAMBDA و BYROW و TEXTJOIN التي تفتح آفاقاً جديدة لأتمتة المعالجة السطرية واستخراج التقارير النصية الموجزة بمرونة فائقة.

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

المراجع

• Alexander, M., & Kusleika, R. (2020). Excel 2019 Bible. John Wiley & Sons.
• Bill Jelen. (2021). MrExcel 2021: Unmasking Excel. Holy Macro! Books.
• Frye, C. (2019). Microsoft Excel 2019 Step by Step. Microsoft Press.
• Harvey, G. (2021). Excel All-in-One For Dummies. John Wiley & Sons.
• Microsoft Corporation. (2023). COUNTIF function reference. Microsoft Support. https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
• Microsoft Corporation. (2023). Dynamic arrays and spilled array behavior. Microsoft Support. https://support.microsoft.com/en-us/office/dynamic-array-formulas-and-spilled-array-behavior-205c6b06-03ba-4151-8ab0-a1849343eed9
• Microsoft Corporation. (2023). SUMPRODUCT function reference. Microsoft Support. https://support.microsoft.com/en-us/office/sumproduct-function-167b14d7-dd80-4bc7-b844-3de4f1ba7e49
• Walkenbach, J. (2015). Excel 2016 Formulas. John Wiley & Sons.
• Winston, W. (2021). Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365). Microsoft Press.