صيغة Excel: تحقق مما إذا كانت الخلية تحتوي على واحدة من عدة قيم ولكن مع استبعاد القيم الأخرى
لنفترض وجود قائمتين من القيم ، فأنت تريد التحقق مما إذا كانت الخلية B3 تحتوي على إحدى القيم الموجودة في النطاق E3: E5 ، ولكن في الوقت نفسه ، لا تحتوي على أي قيم في النطاق F3: F4 كما هو موضح أدناه لقطة الشاشة. سيوفر هذا البرنامج التعليمي صيغة للتعامل بسرعة مع هذه المهمة في Excel وشرح الحجج الخاصة بالصيغة.
الصيغة العامة:
| =(SUMPRODUCT(--ISNUMBER(SEARCH(include,text)))>0) *(SUMPRODUCT(--ISNUMBER(SEARCH(exclude,text)))=0) |
الحجج
| Text: the text string you want to check. |
| Include: the values you want to check if argument text contains. |
| Exclude: the values you want to check if argument text does not contain. |
قيمة الإرجاع:
تُرجع الصيغة 1 أو 0. عندما تحتوي الخلية على إحدى القيم المطلوب تضمينها ، ولا تحتوي على أي قيم مطلوبة للاستبعاد ، فإنها تُرجع 1 ، أو تُرجع 0. يتم التعامل مع هذه الصيغة ، 1 و 0 مثل القيم المنطقية صح وخطأ.
كيف تعمل هذه الصيغة
لنفترض أنك تريد التحقق مما إذا كانت الخلية B3 تحتوي على إحدى القيم الموجودة في النطاق E3: E5 ، ولكن في نفس الوقت تستبعد القيم الموجودة في النطاق F3: F4 ، يرجى استخدام الصيغة أدناه
| =(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) |
صحافة أدخل مفتاح للحصول على نتيجة الفحص.
تفسير
جزء 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) يتحقق مما إذا كانت الخلية تحتوي على قيم في E3: E5
ابحث دالة: تُرجع الدالة SEARCH موضع الحرف الأول من السلسلة النصية داخل أخرى ، إذا عثرت الدالة SEARCH على النص المطابق ، فإنها تُرجع الموضع النسبي ، وإذا لم يكن الأمر كذلك ، فإنها ترجع #VALUE! خطأ. على سبيل المثال ، هنا الصيغة SEARCH($E$3:$E$5,B3) سيبحث في كل قيمة من قيم النطاق E3: E5 في الخلية B3 ، ويعيد موقع كل سلسلة نصية في الخلية B3. سيعود مصفوفة نتيجة كالتالي: {1 ؛ 7 ؛ 12}.
دالة ISNUMBER: تعرض الدالة 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
الصيغة البحث (دولارات أمريكية 3: 4 دولارات أمريكية ، B3) سيبحث عن كل قيمة في النطاق E3: E5 في الخلية B3 ، ويعيد موقع كل سلسلة نصية في الخلية B3. سيعود مصفوفة نتيجة كالتالي: {#VALUE! ؛ # VALUE!}.
ISNUMBER(SEARCH($F$3:$F$4,B3)) سيعود نتيجة الصفيف كـ {خطأ ؛ خطأ} حيث تعثر الدالة SEARCH على 0 رقم.
--ISNUMBER(SEARCH($F$3:$F$4,B3)) تحول القيمة TRUE إلى 1 ، وتحول القيمة FALSE إلى 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 إذا كانت الخلية لا تحتوي على رقم.
أفضل أدوات إنتاجية المكتب
كوتولس ل إكسيل - يساعدك على التميز من بين الحشود
Kutools for Excel يضم أكثر من 300 ميزة، التأكد من أن ما تحتاجه هو مجرد نقرة واحدة...
علامة تبويب Office - تمكين القراءة والتحرير المبوب في Microsoft Office (بما في ذلك Excel)
- ثانية واحدة للتبديل بين عشرات المستندات المفتوحة!
- قلل مئات النقرات بالماوس كل يوم ، وداعًا ليد الماوس.
- يزيد من إنتاجيتك بنسبة 50٪ عند عرض مستندات متعددة وتحريرها.
- يجلب علامات التبويب الفعالة إلى Office (بما في ذلك Excel)، تمامًا مثل Chrome وEdge وFirefox.
