01 من 02
مجموع الخلايا التي تقع بين قيمتين
لايف واير
وظيفة SUMPRODUCT في Excel هي وظيفة متعددة الاستخدامات للغاية ستعطي نتائج مختلفة اعتمادًا على طريقة إدخال وسيطات الوظيفة.
عادة ، كما يوحي اسمها ، تضرب SUMPRODUCT عناصر مصفوفة واحدة أو أكثر للحصول على منتجها ثم تضيف أو تلخص المنتجات معًا.
من خلال ضبط بناء جملة الوظيفة ، ومع ذلك ، يمكن استخدامه لجمع البيانات فقط في الخلايا التي تفي بمعايير محددة.
منذ Excel 2007 ، احتوى البرنامج على وظيفتين - SUMIF و SUMIFS - التي ستجمع البيانات في الخلايا التي تلبي معيارًا واحدًا أو أكثر.
ومع ذلك ، في بعض الأحيان ، يكون SUMPRODUCT أسهل في العمل عندما يتعلق الأمر بالعثور على شروط متعددة تتعلق بنفس النطاق كما هو موضح في الصورة أعلاه.
تركيب دالة SUMPRODUCT إلى مجموع الخلايا
الصيغة المستخدمة للحصول على SUMPRODUCT لجمع البيانات في الخلايا التي تفي بشروط محددة هي:
=SUMPRODUCT ([condition1][condition2][array])
condition1، condition2 - الشروط التي يجب أن تتحقق قبل أن تجد الوظيفة حاصل ضرب المصفوفة.
صفيف - نطاق قريب من الخلايا
مثال: جمع البيانات في الخلايا التي تستوفي شروطًا متعددة
يضيف المثال في الصورة أعلاه البيانات الموجودة في الخلايا في النطاق من D1 إلى E6 بين 25 و 75.
إدخال وظيفة SUMPRODUCT
نظرًا لأن هذا المثال يستخدم نموذجًا غير منتظم للدالة SUMPRODUCT ، فلا يمكن استخدام مربع حوار الوظيفة لإدخال الدالة ووسائطها. بدلاً من ذلك ، يجب كتابة الوظيفة يدويًا في خلية ورقة العمل.
- انقر فوق الخلية B7في ورقة العمل لجعلها الخلية النشطة ؛
- أدخل الصيغة التالية في الخلية B7: =SUMPRODUCT (($ A $ 2: $ B $ 6>25)($ A $ 2: $ B $ 6<75)(A2: B6))
- يجب أن تظهر الإجابة 250في الخلية B7
- تم الوصول إلى الإجابة بجمع خمسة أرقام في النطاق (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.
ضرب الآحاد والأصفار في النطاق
هذه الآحاد والأصفار يتم ضربها بعد ذلك بالأرقام الموجودة في النطاق A2: B6
يتم ذلك لإعطائنا الأرقام التي سيتم جمعها بواسطة الوظيفة.
هذا يعمل بسبب:
- 1 مرات أي رقم يساوي الرقم الأصلي
- 0 مرات أي رقم يساوي 0
لذلك ننتهي بـ:
-
140=40
015=0
022=0
145=45
150=50
155=55
025=0
075=0
160=600100=0
تلخيص النتائج
SUMPRODUCT ثم يلخص النتائج أعلاه للعثور على الإجابة.
40 + 0 + 0 + 45 + 50 + 55 + 0 + 0 + 60 + 0=250
