Excel实例挑战 求前50%人数的平均分数

2020/09/27 07:35

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

Excel实例挑战 求前50%人数的平均分数

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

Excel实例挑战 求前50%人数的平均分数

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

计算各班级,在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班

Excel实例挑战 求前50%人数的平均分数

然后年级的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人

Excel实例挑战 求前50%人数的平均分数

?large函数求分数线

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

Excel实例挑战 求前50%人数的平均分数

?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%)))

Excel实例挑战 求前50%人数的平均分数

注意其中的相对绝对引用

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

免费直播

    精选课程 更多

    注册电脑版

    版权所有 2003-2020 广州环球青藤科技发展有限公司