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

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

تسمح لك هذه الصيغة المحددة بجمع القيم في النطاق C2:C11 حيث القيمة المقابلة في النطاق B2:B11 تساوي “الاحتفاظ” حتى بعد تصفية نطاق الخلايا هذا بطريقة ما.

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

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

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

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

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

ثم انقر فوق سهم القائمة المنسدلة بجوار ” المؤتمر ” وتأكد من تحديد المربع الموجود بجوار الغرب فقط، ثم انقر فوق “موافق” :

ستتم تصفية البيانات تلقائيًا لإظهار الصفوف التي يساوي فيها عمود “المؤتمر” الغرب فقط:

إذا حاولنا استخدام الدالة SUMIF() لجمع القيم في عمود النقاط حيث تساوي قيمة عمود الموضع “Guard”، فستعرض فعليًا مجموع النقاط للصفوف المساوية لـ “Guard” في SUMIF() وظيفة. مجموعة البيانات الأصلية:

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

 =SUMPRODUCT(SUBTOTAL(109,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),,1)),--( B2:B11 ="Guard"))

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

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

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

مجموع نقاط الحراسة: 12 + 28 + 30 + 15 = 85 .

مصادر إضافية

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

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