اكسل

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

Cell Contains One Many Things

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

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



 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

أين أشياء هل نطاق مسمى E5:E7.

تفسير

الهدف من هذا المثال هو اختبار كل خلية في B5: B14 لمعرفة ما إذا كانت تحتوي على أي من السلاسل في نطاق مسمى أشياء (E5: E7). يمكن أن تظهر هذه السلاسل في أى مكان في الخلية ، لذا فهذه مشكلة 'تحتوي على' حرفية. الصيغة في C5 ، المنسوخة ، هي:





 
= SUMPRODUCT (-- ISNUMBER ( SEARCH (things,B5)))>0

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

 
 ISNUMBER ( SEARCH (substring,B5)) // test for substring

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



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

مجموع العمود في التفوق
 
{#VALUE!#VALUE!23}

نظرًا لعدم العثور على 'أحمر' و 'أزرق' ، فإن عملية البحث تُرجع الخطأ #VALUE! خطأ. ومع ذلك ، نظرًا لظهور 'أخضر' بالقرب من نهاية النص في الخلية B5 ، تُرجع الدالة SEARCH 23 (أي يبدأ 'أخضر' عند الحرف الثالث والعشرين).

يتم إرجاع هذه المجموعة مباشرة إلى دالة ISNUMBER ، والذي يحول العناصر في المصفوفة إما إلى TRUE أو FALSE:

 
 ISNUMBER ({#VALUE!#VALUE!23}) // returns {FALSEFALSETRUE}

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

 
--{FALSEFALSETRUE} // coerce to 1s and 0s

ينتج عن هذا مصفوفة جديدة تحتوي فقط على 1 و 0:

كيفية إصلاح خلية في صيغة في Excel
 
{001}

التي يتم تسليمها مباشرة إلى SUMPRODUCT:

 
= SUMPRODUCT ({001}) // returns 1

باستخدام مصفوفة واحدة فقط لمعالجتها ، تجمع SUMPRODUCT العناصر الموجودة في المصفوفة وتُرجع نتيجة. أي نتيجة غير صفرية تعني أن لدينا 'نتيجة' ، لذلك نضيف> 0 لفرض النتيجة النهائية من TRUE أو FALSE:

 
= SUMPRODUCT ({001})>0 // returns TRUE

لاحظ أن أي مجموعة من المطابقات سترجع رقمًا أكبر من الصفر وتتسبب في إرجاع الصيغة TRUE.

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

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

 
= SUMPRODUCT (-- ISNUMBER ( SEARCH ({'red','blue','green'},B5)))>0

دالة SUM

تاريخيا ، إنتاج غالبًا ما يظهر في صيغ الصفيف ، لأنه يمكنه التعامل مع المصفوفات محليًا ، بدون تحكم + تحول + دخول . هذا يجعل الصيغة 'أكثر ملاءمة' لمعظم المستخدمين. في اكسل 365 ، الذي يتعامل مع المصفوفات أصلا ، ال دالة SUM يمكن استخدامها بدلاً من SUMPRODUCT بدون تحكم + shift + إدخال:

كيفية حساب السيرة الذاتية في Excel
 
= SUM (-- ISNUMBER ( SEARCH (things,A1)))>0

منع التطابقات الكاذبة

تتمثل إحدى مشكلات هذا النهج في أنك قد تحصل على مطابقات خاطئة من سلاسل فرعية تظهر داخل كلمات أطول. على سبيل المثال ، إذا حاولت مطابقة 'dr' فقد تجد أيضًا 'Andrea' و 'drink' و 'dry' وما إلى ذلك نظرًا لأن 'dr' يظهر داخل هذه الكلمات. يحدث هذا لأن عملية البحث SEARCH تقوم تلقائيًا بمطابقة 'يحتوي على'.

لاختراق سريع ، يمكنك إضافة مسافة حول كلمات البحث (مثل 'dr' أو 'dr') لتجنب التقاط كلمة 'dr' في كلمة أخرى. ولكن هذا سيفشل إذا ظهر 'dr' أولاً أو أخيرًا في خلية ، أو ظهر بعلامات ترقيم.

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

المرفقات ملف تحتوي الخلية على أحد الأشياء العديدة المؤلف ديف برونز


^