设置
  • 日夜间
    随系统
    浅色
    深色
  • 主题色

Excel 中去极值求平均值案例介绍

秋叶Excel 2023/2/15 11:57:07 责编:梦泽

原文标题:《别再只会 AVERAGE 了!求平均值,这个神仙函数很多人都不知道!》

大家好,我是正在深挖函数的小爽~

在有些比赛中,有时候为了避免极端值的出现,对平均值的结果造成不良影响。

我们通常会在多个评委的评分中,去掉一个最高分和一个最低分后,再求平均值。

那么,我们现在以「武林歌手」为案例,讲解一下,去除一个最高分和一个最低分后求平均分,也就是最终的得分,在 Excel 中如何实现~

这里我们介绍两种方法:

❶ 传统数学逻辑法。

❷ TRIMMEAN 函数法。

传统数学逻辑法

基本操作

在 J3 单元格中输入如下公式,并向下填充。

=(SUM(C3:I3)-MAX(C3:I3)-MIN(C3:I3))/(COUNT(C3:I3)-2)

函数公式解析

SUM 函数是一个求和函数,MAX 函数是一个求最大值函数,MIN 函数是一个求最小值函数,COUNT 函数是一个统计区域个数的函数。

根据数学逻辑,我们可以知道:

剔除数据后的平均值 = 剔除一个最高分和一个最低分后的总分和 / 剔除最高分和最低分后的总个数

所以,在公式中,

=(SUM(C3:I3)-MAX(C3:I3)-MIN(C3:I3))/(COUNT(C3:I3)-2)

❶ (SUM (C3:I3)-MAX (C3:I3)-MIN (C3:I3)),是分子部分,为计算去除最高分和最低分后的总分和;

❷ (COUNT (C3:I3)-2),是分母部分,为计算刨去最高分和最低分以后的总个数。

这样我们就根据运算逻辑,直接写出公式,算出结果啦~

当然,除了常规做法,其实在 Excel 函数中,就有一个函数,专门用来剔除成对的最大值和最小值后求平均值的函数。

这个函数就是 TRIMMEAN 函数。

PS:成对是指 N 对最大值和最小值

我们可以用 TRIMMEAN 函数来修剪平均值,接下来我们就来瞧瞧它吧~

TRIMMEAN 函数法

基本操作

在 J3 单元格中输入如下公式,并向下填充。

=TRIMMEAN(C3:I3,2/COUNT(C3:I3))

函数语法解析

TRIMMEAN 函数用于返回数据集的内部平均值。

即先从数据集的头和尾除去一定百分比的数据点,然后再求平均值。

语法如下:

=TRIMMEAN(array,percent)

=TRIMMEAN(数组,百分比)

❶ 数组:需要进行整理并求平均值的数组或数值区域,如 C3:I3。

❷ 百分比:从计算中排除数据点的分数。

 百分比参数的通用公式:

Percent = 去除的总数 / 数据总数。

如 2 / COUNT (C3:I3)。

注意:

百分比参数区域需要在 [0,1],而且是 2 的倍数,不是 2 的倍数,就会自动向下舍入到最接近 2 的倍数。

举个例子:

在 10 个数据点的集合中,我们要除去两个数据点:头部除去 1 个,尾部除去 1 个。第二参数应该为 2/10=1/5=0.2。

所以案例中的函数公式写出来就是:

=TRIMMEAN(C3:I3,2/COUNT(C3:I3))

为了大家方便理解,我为大家专门整理了一个通用公式套路,一遇到这个问题直接套用公式就行啦!

通用公式

计算去掉 n 个最大值和 n 个最小值后的均值通用公式:

=TRIMMEAN数据区域  2*n/COUNT数据区域) ——n为n对最大值和最小值

通用公式解析

=TRIMMEAN数据区域2*n/COUNT数据区域)

假如我们需要剔除 1 组最大值和最小值后计算总分,那么对应的百分比为 2*1/ COUNT (数据区域)

打比方,数据区域为如下,直接套用公式:

函数公式:

=TRIMMEAN(B2:F2,2*1/COUNT(B2:F2))

一共有 5 个数字,也就是 COUNT (数据区域) 为 5。

对应的,百分比参数为 2/5 ,也就是 0.4,相当于 5 个数字中剔除 2 个。

那如果我们要剔除 2 组最大值和最小值后计算得分呢?百分比参数应该为多少?

百分比参数应该为 2*2/5=4/5=0.8(n 为 2,也就是 2 组)以此类推。

案例中,数据区域是 C3:I3,因为我们去除的是 1 对最大值和最小值,所以 n 为 1,我们直接套用公式,搞定!

TRIMMEAN 函数好用是好用,但是只能成对进行剔除,现实中还有不对称去除最高分和最低分的情况。

比如在投标的时候,经常见到去除二个最高分,和一个最低分,这样不对称的去除,求均值的情况。

这时候 TRIMMEAN 函数就没法做到了,还是需要由传统的数学逻辑去做,比如:

=(SUM(求和区域)-LARGE(求和区域,1)-LARGE(求和区域,2)-SMALL(求和区域,1))/(COUNT(求和区域)-3))

好了,到这里,两种方法我们就介绍完啦!接下来,我们简单总结一下~

总结一下

本文介绍两种去除最高值和最低值求平均值的方法:

❶ 传统的数学逻辑做法:虽然公式冗长,但是可以灵活地剔除不成对的数据。

❷ 利用 TRIMMEAN 函数:只能成对去除。

使用 TRIMMEAN 函数的通用公式:

=TRIMMEAN数据区域2*n/COUNT数据区域)

关于比赛计分方式,除了文中的去除成对最大值和最小值之外,还有哪些计分方式?

本文来自微信公众号:秋叶 Excel (ID:excel100),作者:小爽

广告声明:文内含有的对外跳转链接(包括不限于超链接、二维码、口令等形式),用于传递更多信息,节省甄选时间,结果仅供参考,IT之家所有文章均包含本声明。

相关文章

关键词:Excel教程Excel学院

软媒旗下网站: IT之家 辣品 - 超值导购,优惠券 最会买 - 返利返现优惠券 iPhone之家 Win7之家 Win10之家 Win11之家

软媒旗下软件: 魔方 云日历 酷点桌面 Win7优化大师 Win10优化大师 软媒手机APP应用