在很多比赛中为了体现公平与公正,避免个别明显过高或过低的评分影响选手的成绩,一般都会设置诸如“去掉1个最低分和1个最高分”这样的规则。那么在Excel中如何快速解决这样的问题呢?
去掉1个最低分和1个最高分?用Excel函数快速求解
图1
比较“笨”的方法
所谓比较“笨”的方法就是虽然能够解决问题,但是比较麻烦的方法。我们可以通过MAX函数与MIN函数获取最大值与最小值,然后用总成绩减去这两个分数,最后除以“评委数-2”即可,如下图所示。
去掉1个最低分和1个最高分?用Excel函数快速求解
图2
在L2单元格输入如下公式,然后向下填充至L7单元格。=(SUM(B2:K2)-MAX(B2:K2)-MIN(B2:K2))/(COUNT(B2:K2)-2)
但是!如果先在要求变了,需要去掉2个最高分,2个最低分,然后算平均分,那么问题就变得复杂了,以上的公式肯定是不行了。这就需要借助LARGE函数与SMALL函数,找出第2大分值与第2小分值,在用总成绩减去它们,最后再除以“评委数-4”即可。具体的公式是:=(SUM(B2:K2)-MAX(B2:K2)-MIN(B2:K2)-LARGE(B2:K2,2)-SMALL(B2:K2,2))/(COUNT(B2:K2)-4)
但很显然,问题越复杂公式就越长,因此我们需要找到一种最简单的方法来解决问题。
去掉1个最低分和1个最高分?用Excel函数快速求解
图3
最高效的方法
其实Excel中有一个修剪函数TRIMMEAN可以完美解决上述问题,一个公式即可轻松搞定任务。TRIMMEAN函数的格式如下:TRIMMEAN(array,percent)
array:需要求平均值的数组或数据区域。
percent:修剪的比例,即需要去掉数据的百分比。如果“修剪比例*评分数”=N为偶数,则从最高分开始往下减去N/2个分数,再从最低分开始往上减去N/2个分数,然后求平均值。如果N为奇数,则Excel会返回小于N的最大偶数,比如N=3,则返回2,表示去掉1个最高分、1个最低分。
比如现有10个评分,需要去掉1个最高分,1个最低分,则修剪比例应该设置为2/10;若是去掉2个最高分、2个最低分,则修剪比例应该设置为4/10。
去掉1个最低分和1个最高分?用Excel函数快速求解
图4
因此,案例中的问题可以用下面的公式完美解决。=TRIMMEAN(B2:K2,2/COUNT(B2:K2))
最终得到和图2一致的结果。
去掉1个最低分和1个最高分?用Excel函数快速求解
图5
如果要去掉2个最高分、2个最低分,需要将上述公式中的修剪比例稍作修改,将2改为4即可,如下所示:=TRIMMEAN(B2:K2,4/COUNT(B2:K2))
以上就是今天和大家分享的内容,欢迎关注头条号【光速Excel】,每天与大家分享Excel知识,共同学习,共同进步!