Excel/WPS表格中求一组数据中去掉最大最小值的平均数和标准差

时间:2024-02-17 16:49:31

问题

采集到一组数据后,用Excel/WPS表格处理。考虑到数据有波动,想去掉最大最小的K个数后,再计算剩下数据的平均数和标准差。
如果是计算去掉最大最小数的平均值,那么很容易想到用SUM对所有数据求和,再减去MAXMIN,最后除以总数-2即可。不过计算标准差则不那么容易了。如果考虑最大最小K个数的情况也复杂一点。
有人提到可以先排序再计算。对于单独一组数据确实可以。但是很多情况下,不方便改变数据排列顺序,而且对一组数据的处理可能影响其他数据。所以不是一个完美的解决方法。

方法

以下方法参考了这个问题和第一个回答:https://zhidao.baidu.com/question/358119912.html
用到的函数有:

  • AVERAGE:计算平均数
  • STDEV:计算标准差
  • LARGE:返回一组数中的第N大的数
  • ROW:返回一个引用的行号

假设数据存放的位置是:A1 - A100
计算去掉最大最小K个数后的平均数:=AVERAGE(LARGE(A1:A100, ROW($(K+1):$(100-K)))
计算去掉最大最小K个数后的标准差:=STDEV(LARGE(A1:A100, ROW($(K+1):$(100-K)))

假设 K=2,那么上述函数就写为:
=AVERAGE(LARGE(A1:A100, ROW($3:$98)))
=STDEV(LARGE(A1:A100, ROW($3:$98)))

解释:
假设 K=2,那么ROW($(K+1):$(100-K))会返回一个 3 ~ 98 的序列引用;传递给LARGE函数后,会返回第3~98大的数的序列,并传递给AVERAGE或者STDEV。这样就可以计算去掉最大最小K个数的平均值和标准差了。

举例

下面有10个数据,去掉最大最小值后,求剩余数据的平均数和标准差。