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

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

 =SUMPRODUCT(SUBTOTAL(3,OFFSET( B2:B11 ,ROW( B2:B11 )-ROW( B2 ),0,1)),--( B2:B11 ="Guard"))

تسمح لك هذه الصيغة المحددة بحساب عدد الخلايا في النطاق B2:B11 المساوٍ لـ “Guard” حتى بعد تصفية نطاق الخلايا هذا بطريقة ما.

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

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

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

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

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

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

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

إذا حاولنا استخدام الدالة COUNTIF() لحساب عدد الصفوف التي يساوي Position فيها “Guard”، فسوف تُرجع فعليًا عدد الصفوف المساوية لـ “Guard” في مجموعة البيانات الأصلية:

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

 =SUMPRODUCT(SUBTOTAL(3,OFFSET( B2:B11 ,ROW( B2:B11 )-ROW( B2 ),0,1)),--( B2:B11 ="Guard"))

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

تحسب هذه الوظيفة فقط عدد الخطوط المرئية التي يكون فيها الموضع مساويًا لـ “Guard”، أي 4 خطوط.

مصادر إضافية

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

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