اكسل

الخلية تساوي أحد الأشياء العديدة

Cell Equals One Many Things

صيغة Excel: الخلية تساوي أحد الأشياء العديدةصيغة عامة
= SUMPRODUCT (--(A1=things))>0
ملخص

إذا كنت تريد اختبار خلية لمعرفة ما إذا كانت تساوي أحد الأشياء المتعددة ، فيمكنك القيام بذلك باستخدام صيغة تستخدم الدالة SUMPRODUCT.



يتفوق التنسيق الشرطي 3 نسبة التدرج اللوني

مفهوم

لنفترض أن لديك قائمة بالسلاسل النصية في النطاق B5: B11 ، وتريد اختبار كل خلية مقابل قائمة أخرى من الأشياء في النطاق E5: E9. بمعنى آخر ، لكل خلية في B5: B11 ، تريد أن تعرف: هل هذه الخلية تساوي أيًا من الأشياء الموجودة في E5: E9؟

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





حل

الحل هو إنشاء صيغة تختبر لقيم متعددة وتعيد قائمة قيم TRUE / FALSE. بمجرد أن نحصل على ذلك ، يمكننا معالجة تلك القائمة (مصفوفة ، في الواقع) باستخدام SUMPRODUCT.

تبدو الصيغة التي نستخدمها كما يلي:



كيفية بناء ميزانية في Excel
 
= SUMPRODUCT (--(B5=things))>0
تفسير

المفتاح هو هذا المقتطف:

 
--(B5=things)

التي تقارن ببساطة القيمة في B5 بكل قيمة في النطاق المحدد 'الأشياء'. نظرًا لأننا نقارن B5 بمصفوفة (أي النطاق المسمى 'أشياء' ، E5: E11) ستكون النتيجة مصفوفة من قيم TRUE / FALSE مثل هذا:

{الخسارة في الخسارة}

إذا كان لدينا حتى TRUE واحدًا في المصفوفة ، فإننا نعلم أن B5 يساوي شيئًا واحدًا على الأقل في القائمة ، لذلك ، لفرض قيم TRUE / FALSE على 1s و 0s ، نستخدم سالب مزدوج (- ، يسمى أيضًا مزدوج أحادي). بعد هذا الإكراه ، لدينا هذا:

{10000}

كيفية الجمع بين الأسماء في التفوق مع الفاصلة

نقوم الآن بمعالجة النتيجة باستخدام SUMPRODUCT ، والتي ستضيف العناصر الموجودة في المصفوفة. إذا حصلنا على أي نتيجة غير صفرية ، فلدينا تطابق واحد على الأقل ، لذلك نستخدم> 1 لفرض نتيجة نهائية إما صواب أو خطأ.

مع قائمة مضمنة

ليس هناك شرط أن تستخدم نطاقًا لقائمة الأشياء الخاصة بك. إذا كنت تبحث فقط عن عدد صغير من الأشياء ، فيمكنك استخدام قائمة بتنسيق صفيف ، وهو ما يسمى ثابت الصفيف. على سبيل المثال ، إذا كنت تبحث فقط عن الألوان الأحمر والأزرق والأخضر ، فيمكنك استخدام {'أحمر' و 'أزرق' و 'أخضر'} على النحو التالي:

 
--(B5={'red','blue','green'})

التعامل مع المساحات الزائدة

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

 
= SUMPRODUCT (--( TRIM (A1)=things))>0
المؤلف ديف برونز


^