دالة Excel SUMPRODUCT

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

• مثال 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 معاملًا فقط في الإصدارات السابقة.
• 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. وكما هو الحال عند جمع القيم، كل ما عليك فعله هو إزالة معامل نطاق الجمع من الصيغة.

الحالة 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: