صيغة Excel: التحقق مما إذا كانت الخلية تحتوي على جميع العناصر المطلوبة

افترض أن لديك في Excel قائمةً من القيم في العمود E، وتريد التحقق مما إذا كانت الخلايا في العمود B تحتوي على جميع القيم الموجودة في العمود E، ثم إرجاع TRUE أو FALSE كما يظهر في لقطة الشاشة أدناه. يقدم هذا البرنامج التعليمي صيغةً فعّالةً لحل هذه المهمة.

الصيغة العامة:

المعاملات

قيمة العودة:

تُرجع هذه الصيغة قيمة منطقية: FALSE إذا كانت الخلية لا تحتوي على جميع العناصر، وTRUE إذا كانت تحتوي عليها جميعًا.

كيف تعمل هذه الصيغة

على سبيل المثال، إذا كنت تريد التحقق مما إذا كانت سلاسل النص في العمود B تحتوي على جميع القيم الموجودة في النطاق E3:E5، يُرجى استخدام الصيغة أدناه.

اضغط على مفتاحEnter، ثم اسحب مقبض التعبئة عبر الخلايا التي تريد التحقق منها. تشير القيمةFALSE إلى أن الخلية لا تحتوي على جميع القيم في النطاق E3:E5، بينما تشير القيمةTRUE إلى أن الخلية المقابلة تحتوي على جميع القيم.

التفسير

دالة SEARCH: تُرجع دالة SEARCH موضع الحرف الأول لسلسلة نصية داخل سلسلة أخرى. فإذا وجدت النص، تُرجع موضعه؛ وإذا لم تجده، تُرجع خطأ #VALUE!. على سبيل المثال، ستبحث الصيغةSEARCH($E$3:$E$5,B4) عن كل قيمة من النطاق E3:E5 في الخلية B4، وستُرجع موضع كل سلسلة نصية داخل تلك الخلية على هيئة مصفوفة كالتالي: {1;7;12}

دالة ISNUMBER: تقوم دالة ISNUMBER بالتحقق مما إذا كانت القيمة عدداً، وترجع TRUE أو FALSE. هنا، سترجع الصيغةISNUMBER(SEARCH($E$3:$E$5,B4)) نتيجةً على شكل مصفوفة كالتالي: {true;true;true}، لأن دالة SEARCH وجدت أرقاماً 3.

تُحوّل الصيغة--ISNUMBER(SEARCH($E$3:$E$5,B4)) القيمة TRUE إلى 1، وFALSE إلى 0، لتُعيد بهذه الصيغة مصفوفةً بالشكل{1;1;1}.

دالةSUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))): تُستخدم لضرب نطاقات أو مصفوفات معًا ثم جمع نواتج الضرب وإرجاع المجموع. وعند استخدام الصيغةSUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4)))، يصبح الناتج 1+1+1=3.

دالةCOUNTA($E$3:$E$5): تُرجع عدد الخلايا غير الفارغة. فبما أن الصيغةCOUNTA($E$3:$E$5) تُرجع الناتج 3، فإن نتيجة الصيغةSUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B4))) تساوي 3 أيضًا، وعليه تُرجع الصيغة بأكملها القيمةTRUE أوFALSE.

ملاحظات:

الصيغة=SUMPRODUCT(--ISNUMBER(SEARCH(things,text)))=COUNTA(things) ليست دقيقة تمامًا في التحقق. راجع لقطة الشاشة:

ملف العينة

انقر لتنزيل ملف العينة

الصيغ المرتبطة

• عد الخلايا المساوية لـ
  باستخدام دالة COUNTIF، يمكنك عد الخلايا التي تكون مساوية لقيمة محددة أو التي لا تحتويها.
• عد الخلايا المساوية لـ x أو y
  أحيانًا، قد ترغب في عد الخلايا التي تستوفي أحد معيارين، وهنا يمكنك استخدام دالة COUNTIF.
• عد الخلايا المساوية لـ x وy
  يقدّم هذا المقال الصيغة المثالية لعد الخلايا التي تستوفي معيارين معًا في آنٍ واحد!
• عد الخلايا غير المساوية لـ
  يتناول هذا المقال كيفية استخدام دالة COUNTIF لعدّ الخلايا التي لا تساوي قيمةً معيّنة.