[已解决]怎么用EXCEL函数求一列数据中最大的n个数的平均值，n为变量

金碟神偷

在计算学生考试成绩时想要用一个函数，可以直接求出一列数据中最大的n个数的平均值，n为数据总数乘以0.8，希望可以有人帮忙解决一下。表格见附件，公式输出在B5。

最佳答案

月排行榜 / 总排行榜

砂海

2016-7-5 22:43

=AVERAGE(LARGE(B12:B111,ROW(OFFSET($A$1,,,B4))))

跳转到最佳答案楼层

砂海

=AVERAGE(LARGE(B12:B111,ROW(OFFSET($A$1,,,B4))))

baksy

附件

砂海

baksy 发表于 2016-7-6 06:17
附件

" =AVERAGEIF(B12:B111,">="&LARGE(B12:B111,INT(B3*0.8))) "
分数有重复时 , 人数 会超过 " =B3*0.8 "

ouyangxx

砂海 发表于 2016-7-6 08:04
" =AVERAGEIF(B12:B111,">="&LARGE(B12:B111,INT(B3*0.8))) "
分数有重复时 , 人数 会超过 " =B3*0.8 "
...

老师，我怎么没发现这个重复的问题呢？看不太明白。

砂海

为方便查看
你把成绩 从大到小 排

然后 把 第7名 的成绩 改成 与 第6名 一样 , 平均值会发生变化 ,
这个 结果 是 你 预期的么.?

金碟神偷

砂海 发表于 2016-7-5 22:43
=AVERAGE(LARGE(B12:B111,ROW(OFFSET($A$1,,,B4))))

嗯，您的方法是对的，我EXCEL的函数用的不好，不过用百度搜过后算是理解这个函数的意义了，谢谢！
不过我的题目没说清，按要求参评人数是乘0.8后四舍五入，因为我算参评人数B4格的时候用的是B3*0.8，然后设置单元格不保留小数，所以引用B4单元格时出现小数公式就达不到追求的效果了，所以我把B4单元格加了round函数，这样就没问题了。

金碟神偷

baksy 发表于 2016-7-6 06:17
附件

=AVERAGEIF(B12:B111,">="&LARGE(B12:B111,INT(B3*0.8)))
您的函数作用是求B12:B111单元格区域内，所有大于等于该区域中第B3*0.8大的数的平均数，所以参评人数中成绩最低的和未参评人数中成绩最高的成绩相同时就会出错，不过我还是学到了很多思路，谢谢~

金碟神偷

砂海 发表于 2016-7-5 22:43
=AVERAGE(LARGE(B12:B111,ROW(OFFSET($A$1,,,B4))))

老师，我本来以为根据平均数函数的写法可以想出来及格率和优秀率的函数写法，但是我还是没有头绪啊，能不能麻烦您再指点一下，这两个应该就区别在筛选条件不同吧？您说一个应该就可以了。
及格率是参评人员成绩中大于等于60分的人数占参评人数的百分比；
优秀率是参评人员成绩中大于等于80分的人数占参评人数的百分比.

砂海

金碟神偷 发表于 2016-7-6 15:07
老师，我本来以为根据平均数函数的写法可以想出来及格率和优秀率的函数写法，但是我还是没有头绪啊，能不 ...

这个条件的话
baksy 的公式 刚好