当前位置: 首页 > Excel > Excel常用函数 > ExcelExcel技巧 > Excel实例挑战 求前50%人数的平均分数

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

发布时间:2020年09月27日 07:35:39 来源: 点击量:922

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

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

<a style=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%人数的平均分数

注意其中的相对绝对引用

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

分享到: 编辑:wangmin

就业培训申请领取
您的姓名
您的电话
意向课程
点击领取

环球青藤

官方QQ

扫描上方二维码或点击一键加群,免费领取大礼包,加群暗号:青藤。 一键加群

绑定手机号

应《中华人民共和国网络安全法》加强实名认证机制要求,同时为更加全面的体验产品服务,烦请您绑定手机号.

预约成功

本直播为付费学员的直播课节

请您购买课程后再预约

环球青藤移动课堂APP 直播、听课。职达未来!

安卓版

下载

iPhone版

下载
环球青藤官方微信服务平台

刷题看课 APP下载

免费直播 一键购课

代报名等人工服务

课程咨询 学员服务 公众号

扫描关注微信公众号

APP

扫描下载APP

返回顶部