二维码 购物车
部落窝在线教育欢迎您!

去掉最低分和最高分算平均分:SUBTOTAL等四个函数法

 

作者:EXCEL应用之家来源:部落窝教育发布时间:2021-02-23 17:56:11点击:516

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:
去掉一个最低分,去掉一个最高分,计算平均分作为选手的最终得分,是所有比赛的评分规则。那么如何用Excel一键自动得出最终得分呢?今天,小E通过三国英雄们的比赛,向大家介绍四个函数法,它们分别是:SUBTOTAL、TRIMMEAN、LARGE、SUM-MAX-MIN函数法……

 

很多人都玩过《三国志》这款游戏,作者本人也是一个三国迷,三国演义的故事烂熟于胸,也曾经幻想着自己穿越到那个贤臣明主辈出的时代,替君主实现一统河山的梦想……

 

跑题了哈!今天和大家交流的可不是三国故事,不过,三国的英雄们也出现在了其中。下图中,英雄们在一轮现代化的切磋后,还邀请了众望所归的“牛人”们来做裁判,打分。

 

 

在今天的这道脑洞题里,各位观众老爷们则需要负责为三国英雄们算出各自的最终比赛得分!

下面,作者作为此次比赛的主办方,为大家介绍解答这个问题的四种思路和方法,请大家自行选择。

 

问题分析:

根据一般性比赛的规则,总是要去掉一个最高分,去掉一个最低分,然后再对剩余的分数进行统计或者平均处理。

当然,最简单却最笨拙的方法依然是凭肉眼去除最高和最低分,然后计算平均分。但这是非常低效率的方法,不值得提倡。

 

方法一:SUBTOTAL函数法

 

 

我们在单元格I2中输入公式“=SUM(SUBTOTAL({9,5,4},B2:G2)*{1,-1,-1})/4”并向下拖曳即可。

 

函数解析:

1.   这个思路利用的Subtotal函数的特点。第一个参数是以数组的形式输入{9,5,4},分别计算总和、最小值和最大值。

 

 

 

 

2.   SUBTOTAL({9,5,4},B2:G2)*{1,-1,-1}这部分是本例中的亮点。SUBTOTAL({9,5,4},B2:G2)部分的结果是{34,2,10}{34,2,10}*{1,-1,-1}的结果就是是{34,-2,-10}。其中,用“-1”把最大值和最小值都转变为负数。

3.   SUM(SUBTOTAL({9,5,4},B2:G2)*{1,-1,-1})部分就是对{34,-2,-10}来求和。实际上就是去掉的最大值和最小值。

4.   最后再求出平均值。

 

请注意,这里求平均值需要除的数字是4,即去掉最大值和最小值后的得分个数。下面几个方法都是基于这样的原理。

 

方法二:TRIMMEAN函数法

 

 

TRIMMEAN函数的语法结构如下:

=TRIMMEAN(array,percent)

其中:Array,必须项,是需要进行整理并求平均值的数组或数值区域

      Percent,必须项,是计算是需要去除的数据点的比例。

 

在单元格J2中输入公式“=TRIMMEAN(B2:G2,2/6)”并向下拖曳即可。

 

函数解析:

这个函数书写比较简单,唯一要特别讲解一下的是“2/6”,这部分是第二个参数,“percent”。如果要按上面解释的“比例”来理解可能有些困难。你可以这样理解为,从6个得分中去除2个,一个最大值,一个最小值。

 

如果是去除两个最大值和两个最小值,那么这部分就会变成“4/6”。这里不需要你把它再变成百分数的形式了。

 

方法三:LARGE函数法

 

 

这个方法也是比较简单的。在单元格K2中输入公式“=AVERAGE(LARGE(B2:G2,{2,3,4,5}))”,按SHIFT+CTRL+ENTER三键,并向下拖曳即可。

 

函数解析:

利用LARGE函数,提取第2大、第3大、第4大和第5大数字,再进行平均计算。这里LARGE函数使用的是数组形式。

 

方法四:SUM-MAX-MIN函数法

 

 

这个方法和上一个一样,纯粹是数学逻辑:对所有打分求和,再减去最大值和最小值,最后对剩余部分求平均值。

 

在单元格L2中输入公式“=AVERAGE(SUM(B2:G2)-MAX(B2:G2)-MIN(B2:G2),,,)”并向下拖曳即可。

 

函数没有过多需要解释的。给大家提一个小问题:为什么在AVERAGE(SUM(B2:G2)-MAX(B2:G2)-MIN(B2:G2),,,)中有三个“,,,”?

 

好了,今天和大家的分享就是这些。希望大家都能灵活运用!

 

函数的应用和积累离不开平时多多练习,大家都动起来哦~

 

本文配套的练习课件请加入QQ群:902294808下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

IMG_256

相关推荐:

爷们福音:算老婆每次平均花费的Excel居家爱心公式!

以一敌十的SUBTOTAL函数,你怎能错过?

SUM函数的进阶用法:快速对交叉区域、应收款项、小计行求和!

如何用Excel计算比赛需要多少场才能比完?

版权申明:

本文作者EXCEL应用之家;同时部落窝教育享有专有使用权。若需转载请联系部落窝教育。