دالة SUMPRODUCT في Excel

في Excel، يمكن استخدام دالة SUMPRODUCT لضرب عمودين أو أكثر أو المصفوفات معًا، ثم الحصول على مجموع النواتج. في الواقع، تعد SUMPRODUCT دالة مفيدة يمكنها مساعدتك في عد أو جمع قيم الخلايا بناءً على معايير متعددة مثل دالتي COUNTIFS أو SUMIFS. سيقدم هذا المقال شرحًا لصيغة الدالة وبعض الأمثلة عليها.

• المثال 1: الاستخدام الأساسي لدالة SUMPRODUCT
• المثال 2: جمع الخلايا باستخدام معايير متعددة مع دالة SUMPRODUCT
• المثال 3: عد الخلايا باستخدام معايير متعددة مع دالة SUMPRODUCT
• المثال 4: حساب المتوسط المرجح باستخدام دالة SUMPRODUCT

 الصيغة:

الصيغة الخاصة بدالة SUMPRODUCT في Excel هي:

 المعاملات:

• array1: مطلوب. أول مجموعة أو نطاق من الخلايا الذي تريد ضربه ثم جمعه.
• array2: اختياري. ثاني مجموعة أو نطاق من الخلايا الذي تريد ضربه ثم جمعه.

ملاحظات:

• 1. إذا كانت هناك قيم غير رقمية في النطاق الخاص بك، فإن SUMPRODUCT تعاملها كأصفار.
• 2. إذا كانت المصفوفات أو النطاقات تحتوي على عدد مختلف من الصفوف والأعمدة، ستعيد دالة SUMPRODUCT خطأ #VALUE!
• 3. إذا كانت هناك اختبارات منطقية في المصفوفات، فستنشئ قيم TRUE وFALSE. في معظم الحالات، ستحتاج إلى تحويلها إلى 1 و0 باستخدام العامل الأحادي المزدوج (--).
• 4. يمكن لدالة SUMPRODUCT التعامل مع ما يصل إلى 255 وسيطة في Excel 2007 والإصدارات الأحدث، وفقط 30 وسيطة في الإصدارات السابقة من Excel.
• 5. لا تدعم دالة SUMPRODUCT الرموز البدل (* أو ?).

 القيمة المعادة:

تُعيد نتيجة المصفوفات التي تم ضربها وجمعها.

 أمثلة:

المثال 1: الاستخدام الأساسي لدالة SUMPRODUCT

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

للتعامل مع هذه المهمة، يرجى تطبيق الصيغة أدناه:

التفسير: هذه الصيغة SUMPRODUCT(B2:B7,C2:C7) = B2*C2+B3*C3+B4*C4+B5*C5+B6*C6+B7*C7.

ثم اضغط على مفتاح Enter، سيتم ضرب الخلايا في العمود B بخلاياها المقابلة في نفس صف العمود C، وتتم إضافة النتائج. انظر لقطة الشاشة:

المثال 2: جمع الخلايا باستخدام معايير متعددة مع دالة SUMPRODUCT

بخلاف دالة SUMIFS لجمع قيم الخلايا بناءً على شروط متعددة، يمكن لدالة SUMPRODUCT أيضًا حل هذه المهمة بسرعة وسهولة.

الحالة 1: جمع قيم الخلايا إذا كان يحتوي على نص معين في عمود آخر

لنفترض أن لديك جدول بيانات كما هو موضح في لقطة الشاشة أدناه، الآن تريد حساب السعر الإجمالي حيث يكون المنتج هو KTE والاسم هو David، كيف يمكنك القيام بذلك باستخدام صيغة SUMPRODUCT في Excel؟

1. يرجى إدخال أو نسخ أي واحدة من الصيغ التالية في خلية فارغة:

• نصائح: في الصيغة أعلاه:
• A2:A12="KTE": هو المعيار الأول والنطاق الذي تريد الجمع بناءً عليه.
• B2:B12="David": هو المعيار الثاني والنطاق الذي تريد الجمع بناءً عليه.
• D2:D12: هو نطاق الجمع الذي تريد جمع قيم الخلايا فيه.

2. ثم اضغط على مفتاح Enter للحصول على النتيجة التي تحتاجها:

الحالة 2: جمع الخلايا باستخدام معايير متعددة مع منطق OR

لجمع الخلايا بناءً على معايير متعددة باستخدام منطق OR، يجب عليك استخدام رمز الجمع (+) لربط الشروط المتعددة معًا داخل دالة SUMPRODUCT.

على سبيل المثال، أريد جمع السعر الإجمالي للمنتجات KTE وKTO في العمود A كما هو موضح في لقطة الشاشة أدناه:

يرجى تطبيق الصيغة التالية في خلية فارغة:

ثم اضغط على مفتاح Enter، تم حساب السعر الإجمالي للمنتجين KTE وKTO، انظر لقطة الشاشة:

الحالة 3: جمع الخلايا باستخدام معايير متعددة مع منطق OR وAND

في بعض الحالات، قد تحتاج إلى جمع الخلايا باستخدام كل من منطق OR وAND في نفس الوقت. يمكن لدالة SUMPRODUCT أيضًا حل هذه المهمة بسهولة.

يُستخدم الرمز النجمة (*) كعامل AND.

يُستخدم رمز الجمع (+) كعامل OR.

لنلقِ نظرة على مثال، لجمع السعر الإجمالي حيث يكون المنتج هو KTE وKTO عندما تكون المبيعات أكبر من 200.

يجب عليك نسخ أو إدخال الصيغة أدناه:

ثم اضغط على مفتاح Enter للحصول على النتيجة التي تحتاجها:

المثال 3: عد الخلايا باستخدام معايير متعددة مع دالة SUMPRODUCT

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

الحالة 1: عد الخلايا باستخدام معايير متعددة مع منطق AND

هنا، أريد عد الخلايا التي يكون فيها المنتج هو KTE والمبيعات أكبر من 200. لذا، يرجى استخدام أي واحدة من الصيغ أدناه:

• نصائح: في الصيغة أعلاه:
• A2:A12="KTE": هو المعيار الأول والنطاق الذي تريد العد بناءً عليه.
• B2:B12>200: هو المعيار الثاني والنطاق الذي تريد العد بناءً عليه.

ثم اضغط على مفتاح Enter للحصول على عدد النتائج:

الحالة 2: عد الخلايا باستخدام معايير متعددة مع منطق OR

لعَد عدد المنتجات KTE وKTO في العمود A، يرجى تطبيق هذه الصيغة:

ثم اضغط على مفتاح Enter للحصول على عدد المنتجين KTE وKTO. انظر لقطة الشاشة:

الحالة 3: عد الخلايا باستخدام معايير متعددة مع منطق OR وAND

لعَد عدد المنتجات KTE وKTO التي تكون مبيعاتها أكبر من 200، يجب عليك تطبيق الصيغة التالية:

ثم اضغط على مفتاح Enter للحصول على عدد النتائج التي تحتاجها:

المثال 4: حساب المتوسط المرجح باستخدام دالة SUMPRODUCT

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

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

لحساب المتوسط المرجح، يرجى استخدام الصيغة التالية:

التفسير: هذه الصيغة: SUMPRODUCT(B2:B6, C2:C6) / SUM(C2:C6) = (B2*C2+B3*C3+B4*C4+B5*C5+B6*C6)/(C2+C3+C4+C5+C6)

ثم اضغط على مفتاح Enter، تم حساب المتوسط المرجح على الفور، انظر لقطة الشاشة:

 مقالات أخرى حول دالة SUMPRODUCT: