Excel 中求平均值的好用函数
消息来源:baojiabao.com 作者: 发布时间:2024-05-13
原文标题:《别再只会 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),作者:小爽,审核:玛奇鹅,编辑:田果果、竺兰
2022-12-21 18:09:01相关文章
- 美国法院裁定阿里须为Squishmallows玩具侵权案答辩
2023-12-28 19:59:34
- 小米汽车传员工3700人 雷军称小米汽车不可能卖9万9
2023-12-28 19:41:57
- 国家新闻出版署:认真研究《网络游戏管理办法(草桉徵求意见稿)》关切 实行前进一步完善
2023-12-28 19:14:56
- 天猫新规可以无条件申请“仅退款”了?淘宝天猫又离狗多多零元购近了一步
2023-12-28 18:57:55
- 印度以打击金融犯罪为由逮捕了两名 vivo 高管
2023-12-26 16:49:01
- 在国外微信收不到国内信息?微信和WeChat将被拆分
2023-12-15 10:40:15
- 苹果iPhone15 系列手机发布最新消息 预计上市发布时间9月
2023-08-06 23:21:02
- 华为将发布鸿蒙HarmonyOS4操作系统 功能五大升级支持设备清单
2023-08-06 23:17:37
- 整治自媒体网红账号 400万粉丝网红发布擦边视频被无限期封禁
2023-07-12 09:56:09
- 网传微信文件传输助手是真人是真的吗?微信官方回应
2023-06-27 15:53:32
- 电信移动送手机成了“信用购”?你上了运营商的贷款套路了吗?
2023-06-12 17:18:55
- 中国电信广东地区崩了无信号 客服回应已在核实处理
2023-06-08 15:39:04
- 消息称小米新能源汽车价格表正讨论定价区间:双版本不同配置,高配或超 35 万元
2023-03-06 12:56:03
- 华为因制裁被传或分拆剥离手机业务? 内部人士回应:可能性不大.
2023-03-05 23:26:41
- OPPO正式发布安第斯智能云,让终端更智能
2023-02-24 16:02:27
- 华为与OPPO签订全球专利交叉许可协议 包括5G蜂窝通信专利
2023-02-24 16:02:26
- 老蛙将推MINI镜头新品:目前未知具体规格 官宣将于12月20日发布
2023-02-24 16:02:26
- 首发全新35mm定制光学系统 努比亚Z50性能同样强悍
2023-02-24 16:02:25
- Redmi K60屏幕细节曝光:全系标配2K护眼柔性直屏+5000mAh大容量电池
2023-02-24 16:02:25
- OPPO Find N2今天发 合金金属折叠屏更轻了
2023-02-24 16:02:24