SUMPRODUCT مع IF

= SUMPRODUCT (expression,range)

لتصفية نتائج SUMPRODUCT بمعايير محددة ، يمكنك تطبيق تعبيرات منطقية بسيطة مباشرة على المصفوفات في الوظيفة ، بدلاً من استخدام الدالة IF . في المثال الموضح ، الصيغ في H5: H7 هي:

 
= SUMPRODUCT (--(color='red'),quantity,price) = SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price) = SUMPRODUCT (--(state='co'),--(color='blue'),quantity,price)

حيث ما يلي النطاقات المسماة تم تعريفها:

 
state=B5:B14 color=C5:C14 quantity=D5:D14 price=E5:E14

إذا كنت تفضل تجنب النطاقات المسماة ، فاستخدم النطاقات التي تم إدخالها أعلاه كـ مراجع مطلقة . يمكن دمج التعبيرات المنطقية في H6 و H7 ، كما هو موضح أدناه.

يوضح هذا المثال إحدى نقاط القوة الرئيسية لوظيفة SUMPRODUCT - وهي القدرة على تصفية البيانات باستخدام التعبيرات المنطقية الأساسية بدلاً من وظيفة IF. داخل SUMPRODUCT ، أول مجموعة مصفوفة هو تعبير منطقي للتصفية على اللون 'الأحمر':

 
--(color='red')

ينتج عن هذا مصفوفة أو قيم TRUE FALSE ، والتي يتم فرضها في الآحاد والأصفار باستخدام خطأ مزدوج (--) عملية. والنتيجة هي هذه المجموعة:

 
{1010001000}

لاحظ أن الصفيف يحتوي على 10 قيم ، واحدة لكل صف. يشير واحد إلى صف حيث يكون اللون 'أحمر' ويشير الصفر إلى صف بأي لون آخر.

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

 
= SUMPRODUCT ({1010001000},quantity,price)

توسيع المصفوفات ، لدينا:

 
= SUMPRODUCT ({1010001000},{1061491110891110},{15181516181815161816})

السلوك الأساسي لـ SUMPRODUCT هو الضرب ، ثم جمع المصفوفات. نظرًا لأننا نعمل بثلاث مصفوفات ، يمكننا تصور العملية كما هو موضح في الجدول أدناه ، حيث يكون عمود النتيجة هو نتيجة الضرب مجموعة 1 * array2 * array3 :

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

تتفوق البحث عن الخلية الأخيرة مع البيانات

 
= SUMPRODUCT ({1500210000120000})

والتي تُرجع نتيجة نهائية تساوي 480.

إضافة معايير إضافية

يمكنك توسيع المعايير عن طريق إضافة تعبير منطقي آخر. على سبيل المثال ، للعثور على إجمالي المبيعات حيث يكون اللون 'أحمر' والحالة 'TX' ، يحتوي H6 على:

 
= SUMPRODUCT (--(state='tx'),--(color='red'),quantity,price)

ملاحظة: SUMPRODUCT ليست حساسة لحالة الأحرف.

التبسيط بمصفوفة واحدة

غالبًا ما يبسط محترفو Excel البنية داخل SUMPRODUCT قليلاً عن طريق ضرب المصفوفات بالداخل مباشرةً مجموعة 1 مثله:

 
= SUMPRODUCT ((state='tx')*(color='red')*quantity*price)

يعمل هذا لأن العملية الحسابية (الضرب) تفرض تلقائيًا قيم TRUE و FALSE من أول تعبيرين إلى آحاد وأصفار.