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

افترض أن لديك قائمتين من القيم، وتريد التحقق مما إذا كانت الخلية B3 تحتوي على إحدى القيم في النطاق E3:E5، وفي الوقت نفسه لا تحتوي على أيٍّ من القيم في النطاق F3:F4، كما هو موضح في لقطة الشاشة أدناه. سيوفّر لك هذا البرنامج التعليمي صيغةً تُمكّنك من تنفيذ هذه المهمة بسرعة في Excel، مع شرحٍ واضح لوظائف الصيغة ووسائطها.

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

الوسائط

قيمة العودة:

تُرجع الصيغة 1 أو 0: فعندما تحتوي الخلية على إحدى القيم التي يجب تضمينها ولا تحتوي على أيٍّ من القيم التي يجب استبعادها، تُرجع 1؛ وإلا فتُرجع 0. وتعتبر الصيغة القيمتين 1 و0 معادلتين للقيمتين المنطقيتين TRUE وFALSE.

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

افترض أنك تريد التحقق مما إذا كانت الخلية B3 تحتوي على إحدى القيم في النطاق E3:E5، وفي الوقت نفسه تستبعد القيم في النطاق F3:F4، فالرجاء استخدام الصيغة التالية:

اضغط على مفتاحEnter للحصول على نتيجة التحقق.

التفسير

الجزء 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) يتحقق مما إذا كانت الخلية تحتوي على القيم في E3:E5

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

دالة ISNUMBER: تُرجع القيمة TRUE عندما تحتوي الخلية على رقم. لذا، فإنISNUMBER(SEARCH($E$3:$E$5,B3))ستُرجع مصفوفة نتائج {TRUE،TRUE،TRUE} لأن دالة SEARCH عثرت على تطابق مع 3 قيم.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) تحوّل القيمة TRUE إلى 1، وFALSE إلى 0، لذا تُغيّر هذه الصيغة نتيجة المصفوفة إلى{1;1;1}.

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

في النهاية، قارن بين الصيغة اليسرىSUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) و0؛ فطالما كانت نتيجة الصيغة اليسرى أكبر من 0، تكون النتيجة TRUE، وإلا تُرجع FALSE. وهنا تُرجع TRUE.

الجزء 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) يتحقق مما إذا كانت الخلية لا تحتوي على القيم في F3:F4

ستبحث الصيغةSEARCH($F$3:$F$4,B3) عن كل قيمة ضمن النطاق $F$3:$F$4 داخل الخلية B3، وستُرجع موضع كل سلسلة نصية موجودة في الخلية B3. وستكون النتيجة على شكل مصفوفة كالتالي: {#VALUE!;#VALUE!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) ستُرجع نتيجةً على شكل مصفوفة{false;false} لأن دالة SEARCH لم تجد أي تطابق.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) تحوّل القيمة TRUE إلى 1، وFALSE إلى 0، لذا تُغيّر هذه الصيغة نتيجة المصفوفة إلى{0;0}.

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

في النهاية، قارن بين الصيغة اليسرىSUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) و0؛ فطالما كانت نتيجة الصيغة اليسرى تساوي 0، تُرجع القيمة TRUE، وإلا تُرجع FALSE. وهنا تُرجع TRUE.

الجزء 3: ضرب الصيغتين معًا

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

في هذه الصيغة، يُعامل 1 و0 كقيمتَي منطقية TRUE وFALSE.

ملف العينة

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

الصيغ ذات الصلة

• التحقق مما إذا كانت الخلية تحتوي على نص معيّن
  للتحقُّق مما إذا كانت خلية ما تحتوي على نصٍّ معين في النطاق A ولا تحتوي على نصوص في النطاق B، يمكنك استخدام صيغة صفيفية تجمع بين دوال COUNT وSEARCH وAND في Excel.
• التحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة
  يقدّم هذا البرنامج التعليمي صيغةً للتحقق مما إذا كانت الخلية تحتوي على إحدى القيم العديدة في Excel، ويشرح الوسائط المستخدمة في الصيغة وكيفية عملها.
• التحقق مما إذا كانت الخلية تحتوي على إحدى القيم
  افترض أن لديك في Excel قائمةً بالقيم في العمود E، وتريد التحقق مما إذا كانت الخلايا في العمود B تحتوي على أيٍّ من القيم الموجودة في العمود E، ثم إرجاع TRUE أو FALSE.
• التحقق مما إذا كانت الخلية تحتوي على رقم
  قد ترغب أحيانًا في التحقق مما إذا كانت الخلية تحتوي على أحرف رقمية. يقدّم لك هذا البرنامج التعليمي صيغةً تُرجع القيمة TRUE إذا احتوت الخلية على رقم، وFALSE إذا لم تحتوِ على رقم.