عد خلايا البيانات مع وظيفة SUMPRODUCT في Excel

تعد الدالة SUMPRODUCT في Excel دالة متعددة الاستخدامات تعطي نتائج مختلفة بناءً على الوسائط المدخلة.

ما تقوم به الدالة SUMPRODUCT عادةً هو ضرب عناصر صفيف أو أكثر ثم إضافة أو جمع المنتجات معاً.

ولكن عن طريق ضبط شكل الوسيطات ، سيحسب SUMPRODUCT عدد الخلايا في نطاق معين يحتوي على بيانات تفي بمعايير محددة.

01 من 04

SUMPRODUCT مقابل COUNTIF و COUNTIFS

منذ Excel 2007 ، يحتوي البرنامج أيضًا على دالات COUNTIF و COUNTIFS التي تسمح لك بحساب الخلايا التي تفي بمعايير مجموعة واحدة أو أكثر.

في بعض الأحيان ، يكون من السهل العمل مع SUMPRODUCT عندما يتعلق الأمر بالعثور على شروط متعددة تتعلق بنفس النطاق كما هو موضح في المثال الموجود في الصورة أعلاه.

02 من 04

بناء جملة الدالة SUMPRODUCT ووسائط لعد الخلايا

تشير صيغة الدالة إلى تخطيط الدالة وتتضمن اسم الدالة ، الأقواس ، فاصلات الفواصل ، والحجج .

للحصول على الدالة لحساب الخلايا بدلاً من تنفيذ غرضها القياسي ، يجب استخدام البنية غير القياسية التالية مع SUMPRODUCT:

= SUMPRODUCT ([condition1] * [condition2])

ويرد شرح لكيفية عمل هذا النحو أدناه المثال التالي.

مثال: عد الخلايا التي تلبي شروط متعددة

كما هو موضح في المثال في الصورة أعلاه ، يتم استخدام SUMPRODUCT لإيجاد العدد الإجمالي للخلايا في نطاق البيانات A2 إلى B6 التي تحتوي على بيانات بين قيم 25 و 75.

03 من 04

دخول دالة SUMPRODUCT

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

ومع ذلك ، لأن هذا المثال يستخدم نموذج غير منتظم للدالة SUMPRODUCT ، لا يمكن استخدام نهج مربع الحوار. بدلاً من ذلك ، يجب كتابة الدالة في خلية ورقة عمل .

في الصورة أعلاه ، تم استخدام الخطوات التالية لإدخال SUMPRODUCT في الخلية B7:

1. انقر فوق الخلية B7 في ورقة العمل - الموقع الذي سيتم عرض نتائج الدوال فيه
2. اكتب الصيغة التالية في الخلية E6 لورقة العمل:

   = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75))

3. يجب أن يظهر الجواب 5 في الخلية B7 حيث توجد خمس قيم فقط في النطاق - 40 و 45 و 50 و 55 و 60 - تتراوح بين 25 و 75
4. عند النقر فوق الخلية B7 ، تظهر الصيغة المكتملة = SUMPRODUCT (($ A $ 2: $ B $ 6> 25) * ($ A $ 2: $ B $ 6 <75)) في شريط الصيغة أعلى ورقة العمل

04 من 04

تحطيم وظيفة SUMPRODUCT

عند تعيين الشروط للوسائط ، تقوم الدالة SUMPRODUCT بتقييم كل عنصر صفيف مقابل الشرط وإرجاع قيمة منطقية (TRUE أو FALSE).

لأغراض العمليات الحسابية ، يقوم Excel بتعيين قيمة 1 لعناصر الصفيف التي تكون TRUE وقيمة 0 لعناصر الصفيف FALSE.

يتم ضرب الأعداد والأصفار المناظرة في كل مجموعة معًا:

• مع 1 × 1 لإرجاع قيمة 1
• مع 1 × 0 لإرجاع قيمة 0

ثم يتم جمع هذه الأصفار والأصفار بواسطة الدالة لتعطينا عددًا من القيم التي تستوفي الشرطين.

أو ، فكر في الأمر بهذه الطريقة ...

هناك طريقة أخرى للتفكير في ما تقوم به SUMPRODUCT هو التفكير في علامة الضرب كشرط AND .

مع وضع هذا في الاعتبار ، فقط عندما يتم استيفاء الشروط - أرقام أكبر من 25 وأقل من 75 - يتم إرجاع قيمة TRUE (التي تساوي واحد تذكر).

تلخص الدالة جميع القيم الحقيقية للوصول إلى نتيجة 5.