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

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

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

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

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

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

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

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

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

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

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

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

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

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

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

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

متوسط نقاط الحراسة: (12 + 28 + 30 + 15) / 4 = 21.25 .

مصادر إضافية

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

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