كيفية استخدام subtotal مع sumproduct في excel

يمكنك استخدام الصيغة التالية لدمج الدالتين SUBTOTAL و SUMPRODUCT في Excel:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

تسمح لك هذه الصيغة المحددة بجمع حاصل ضرب قيم النطاق C2:C11 والنطاق D2:D11 حتى بعد تصفية نطاق الخلايا هذا بطريقة ما.

يوضح المثال التالي كيفية استخدام هذه الصيغة عمليًا.

مثال: كيفية استخدام SUBTOTAL مع SUMPRODUCT في Excel

لنفترض أن لدينا مجموعة البيانات التالية التي تحتوي على معلومات حول مبيعات المنتجات المختلفة في متجرين مختلفين للبقالة:

بعد ذلك، لنقم بتصفية البيانات لإظهار الصفوف التي تكون قيمتها في عمود المتجر هي B فقط.

للقيام بذلك، قم بتمييز نطاق الخلايا A1:D11 . ثم انقر فوق علامة التبويب “بيانات” الموجودة على الشريط العلوي وانقر فوق الزر “تصفية” .

ثم انقر فوق السهم المنسدل بجوار Store وتأكد من تحديد المربع الموجود بجوار B فقط، ثم انقر فوق OK :

ستتم تصفية البيانات تلقائيًا لإظهار الصفوف التي يكون فيها عمود المتجر مساويًا لـ B فقط:

إذا حاولنا استخدام الدالة SUMRPODUCT() لجمع منتج قيم عمودي المبيعات والسعر، فسوف تقوم في الواقع بإرجاع مجموع منتج هذين العمودين في مجموعة البيانات الأصلية:

بدلا من ذلك، نحن بحاجة إلى استخدام الصيغة التالية:

 =SUMPRODUCT( C2:C11 ,SUBTOTAL(9,OFFSET( D2:D11 ,ROW( D2:D11 )-MIN(ROW( D2:D11 )),0,1)))

توضح لقطة الشاشة التالية كيفية استخدام هذه الصيغة عمليًا:

ترجع هذه الصيغة المجموع الصحيح وهو 77.5 .

يمكننا التأكد من صحة ذلك عن طريق حساب مجموع حاصل ضرب القيم بين عمودين المبيعات والسعر يدويًا:

مجموع حاصل ضرب القيم بين المبيعات والسعر: (3*2) + (12*2.5) + (5*3.5) + (8*3) = 77.5 .

مصادر إضافية

تشرح البرامج التعليمية التالية كيفية تنفيذ العمليات الشائعة الأخرى في Excel:

كيفية حذف الصفوف التي تمت تصفيتها في إكسيل
كيفية حساب الصفوف التي تمت تصفيتها في إكسيل
كيفية جمع الصفوف التي تمت تصفيتها في إكسيل