来自一个小伙伴的实例分析,我们对数据使用随机数进行模拟,左边是各个班级的成绩单,现在要统计分析,各科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%)))
注意其中的相对绝对引用
小伙伴们有没有其它更好的方法?不妨用小编的思路写写公式吧,综合应用能有个很好的提升