Skip to main content

مجموع الخلايا التي تفي بمعايير متعددة مع Excel SUMPRODUCT

الجمع وفق شرط مع وجود القيم في أكثر من عامود #95 - إيكسل (قد 2025)

الجمع وفق شرط مع وجود القيم في أكثر من عامود #95 - إيكسل (قد 2025)
Anonim
01 من 02

مجموع الخلايا التي تقع بين قيمتين

نظرة عامة على SUMPRODUCT

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

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

من خلال تعديل بناء جملة الدالة ، ومع ذلك ، يمكن استخدامه لجمع البيانات في الخلايا التي تفي بمعايير محددة فقط.

منذ Excel 2007 ، يحتوي البرنامج على وظيفتين - SUMIF و SUMIFS - والتي ستجمع البيانات في الخلايا التي تفي بمعايير مجموعة واحدة أو أكثر.

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

بناء جملة الدالة SUMPRODUCT إلى Sum Cells

بناء الجملة المستخدم للحصول على SUMPRODUCT على جمع البيانات في الخلايا التي تفي بشروط معينة هو:

= SUMPRODUCT (condition1 * condition2 * array)

condition1، condition2 - الشروط التي يجب تلبيتها قبل أن تعثر الدالة على منتج الصفيف.

مجموعة - مجموعة متجاورة من الخلايا

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

يضيف المثال الموجود في الصورة أعلاه البيانات في الخلايا في النطاق من D1 إلى E6 والتي تتراوح بين 25 و 75.

دخول دالة SUMPRODUCT

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

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

كسر صيغة SUMPRODUCT

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

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

على سبيل المثال ، الرقم 40:

  • هو TRUE للشرط الأول لذا قيمة 1 يتم تعيينه في المصفوفة الأولى ؛
  • هو TRUE للشرط الثاني بحيث تكون قيمة 1 يتم تعيينها في المصفوفة الثانية.

الرقم 15:

  • هي FALSE للشرط الأول لذا قيمة 0 يتم تعيينه في المصفوفة الأولى ؛
  • هو TRUE للشرط الثاني بحيث تكون قيمة 1 يتم تعيينها في المصفوفة الثانية.

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

  • بالنسبة للرقم 40 - لدينا 1 × 1 يعيد القيمة 1 ؛
  • بالنسبة للرقم 15 - لدينا 0 × 1 لإرجاع قيمة 0.

مواصلة القراءة أدناه

02 من 02

ضرب الأصفار والأصفار بواسطة النطاق

ثم يتم ضرب هذه الأصفار والأصفار بالأرقام في النطاق A2: B6.

يتم ذلك لإعطائنا الأرقام التي سيتم جمعها بواسطة الدالة.

هذا يعمل بسبب:

  • 1 أي عدد يساوي الرقم الأصلي
  • 0 يساوي أي رقم يساوي 0

سننتهي بـ:

  • 1 * 40 = 400 * 15 = 00 * 22 = 01 * 45 = 451 * 50 = 501 * 55 = 550 * 25 = 00 * 75 = 01 * 60 = 600 * 100 = 0

تلخيص النتائج

ثم تلخص SUMPRODUCT النتائج المذكورة أعلاه للعثور على الإجابة.

40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0 = 250