来自一个小伙伴的实例分析，我们对数据使用随机数进行模拟，左边是各个班级的成绩单，现在要统计分析，各科50%前面的人的平均分数是多少

 

比如要求1班语文，那必须把1班语文成绩前50%的人找出来，然后求平均值，以此类推，如果一个个去手动计算的话，耗费1天都很难求出来，大家思考一下，有没有什么好办法

 

下面解读一下小编的解决办法

计算各班级，在I3输入公式：

=AVERAGEIFS(C:C,$A:$A,$H3,C:C,">="&LARGE(OFFSET(C$1,MATCH($H3,$A:$A,0)-2,0,COUNTIFS($A:$A,$H3),1),INT(COUNTIFS($A:$A,$H3)*50%)))

然后向右填充，向下填充至6班

 

然后年级的I9的公式是：

=AVERAGEIFS(C:C,C:C,">="&LARGE(C:C,INT((COUNTA($C:$C)-1)*50%)))

向右填充

公式很少，主要在于思路

思路分析

小编用的思路是，找到各班级，各科目前50%的那个分数线是多少，然后我们对大于这个分数线的学生进行求条件平均值

❶各科前50%的人分别是多少人

=INT(COUNTIFS($A:$A,$H3)*50%)

COUNTIFS函数对各班级的人数进行统计，然后乘以50%就是人数，这里是15人

 

❷large函数求分数线

large(数据列,15)，表示第15大的数据，而这个数据列需要根据班级不同，引用的数据位置不同，OFFSET(C$1,MATCH($H3,$A:$A,0)-2,0,COUNTIFS($A:$A,$H3),1)，这个OFFSET函数动态引用不同班级的不同区域

 

❸AVERAGEIFS求条件平均值

AVERAGEIFS(求平均值区域，条件区域1，条件1，条件区域2，条件2）

我们这里的使用的函数是：

AVERAGEIFS(分数区域，班级区域，班级，分数区域，大于等于50%的那个分数线）

把前面的公式套用进来，就得到了分数解决，对应的公式是：

=AVERAGEIFS(E:E,$A:$A,$H3,E:E,">="&LARGE(OFFSET(E$1,MATCH($H3,$A:$A,0)-2,0,COUNTIFS($A:$A,$H3),1),INT(COUNTIFS($A:$A,$H3)*50%)))

 

注意其中的相对绝对引用

小伙伴们有没有其它更好的方法？不妨用小编的思路写写公式吧，综合应用能有个很好的提升