使用Excel TRIMMEAN忽略异常值-CSDN博客

From what I've seen in workbooks over the years, SUM is the most frequently used Excel function, and AVERAGE is the runner-up. Would you agree, or do you see other functions used more often than those two?

根据我多年来在工作簿中看到的内容，SUM是最常用的Excel函数，而AVERAGE是第二名。您是否同意，或者您看到其他功能比这两个功能使用得更多？

排除异常值 (Exclude the Outliers)

Last week, a client asked about excluding some of the highest and lowest numbers from a data set, to give a better average. It looked like some of those outliers had been incorrectly entered (an extra zero, or a mistyped numbers), and they were throwing off the results.

上周，一位客户询问要从数据集中排除一些最高和最低的数字，以获得更好的平均值。看起来其中一些异常值输入错误(一个额外的零或数字输入错误)，并且它们排除了结果。

So, we did some tests with the TRIMMEAN function, to compare the results with a simple AVERAGE formula.

因此，我们使用TRIMMEAN函数进行了一些测试，以将结果与简单的AVERAGE公式进行比较。

Do you ever use TRIMMEAN? I'm not a statistician, but it could be useful for giving a slightly different perspective on your data.

您曾经使用TRIMMEAN吗？我不是统计学家，但对于为您的数据提供稍微不同的观点可能很有用。

排除数据百分比 (Exclude a Percentage of Data)

With the TRIMMEAN function, you can exclude a specific percentage of the data points from the top and bottom of the data set. TRIMMEAN returns the average (mean) of the remaining interior data points.

使用TRIMMEAN功能，可以从数据集的顶部和底部排除特定百分比的数据点。 TRIMMEAN返回其余内部数据点的平均值(均值)。

In the sample data for this demo, there is a list of quantities sold, for stationery orders. There are 20 records, with some outliers at the top and bottom. The data will be trimmed by 25%, to get the trimmed mean.

在此演示的样本数据中，有文具订单的已售数量清单。有20条记录，顶部和底部有一些离群值。数据将被修整25％，以获得修整后的平均值。

NOTE: In this example, the quantities are sorted in ascending order, so it is easier to see the top and bottom numbers. Numbers do NOT need to be sorted, for the TRIMMEAN function to calculate correctly.

注意：在此示例中，数量按升序排序，因此更容易看到顶部和底部的数字。不需要对数字进行排序，以便TRIMMEAN函数可以正确计算。

Item	Qty
Binder	3
Pen	4
Pen	14
Pencil	20
Binder	22
Pencil	30
Pen	36
Binder	41
Pen	44
Binder	52
Pencil	59
Pen	65
Pen	66
Pen	72
Binder	78
Pencil	81
Pen	84
Binder	85
Pencil	86
Binder	97

项目	数量
活页夹	3
钢笔	4
钢笔	14
铅笔	20
活页夹	22
铅笔	30
钢笔	36
活页夹	41
钢笔	44
活页夹	52
铅笔	59
钢笔	65岁
钢笔	66
钢笔	72
活页夹	78
铅笔	81
钢笔	84
活页夹	85
铅笔	86
活页夹	97

TRIMMEAN函数参数 (TRIMMEAN Function Arguments)

The TRIMMEAN function requires 2 arguments: TRIMMEAN(array, percent)

TRIMMEAN函数需要2个参数： TRIMMEAN(array，percent)

In this example,

在这个例子中

the values for the array are in cells B2:B21
数组的值在单元格B2：B21中
the trim percent is entered in cell E3, as 25% (or 0.25)
修剪百分比在E3单元格中输入，为25％(或0.25)

To calculate the trimmed mean, enter this formula in cell E4:

要计算修整后的平均值，请在单元格E4中输入以下公式：

     =TRIMMEAN(B2:B21,E)

The TRIMMEAN result (53.06) is different from the AVERAGE (51.95), which is shown in cell E5.

TRIMMEAN结果(53.06)与单元格E5中显示的平均值(51.95)不同。

Excel TRIMMEAN function http://www.contextures.com/excelaveragefunctions.html

TRIMMEAN如何运作 (How TRIMMEAN Works)

In this example, there are 20 values, and the trim percent is 25%.

在此示例中，有20个值，修剪百分比为25％。

To calculate how many number to trim,

要计算要修剪的数字，

the values are counted, then multiplied by the trim percentage (e.g. 20 * .25 = 5
计算值，然后乘以修整百分比(例如20 * .25 = 5
That number is divided by 2, to get the number to trim at each end ( e.g. 5 / 2 = 2.5)
将该数字除以2，得到要在每一端修剪的数字(例如5/2 = 2.5)
To remove an equal number of data points at each end, the number is rounded down to the nearest integer ( e.g. INT( 2.5) = 2)
为了在每一端删除相等数量的数据点，该数字将四舍五入为最接近的整数(例如INT(2.5)= 2)

So, in this example, the top 2 (86,97) and bottom 2 (3,4) data points will not be included in the average that TRIMMEAN calculates.

因此，在此示例中，顶部2(86,97)和底部2(3,4)数据点将不包括在TRIMMEAN计算的平均值中。

To verify the result, use the AVERAGE function with cells B4:B19 – ignoring the top 2 and bottom 2 numbers. This returns 53.06 – the same result as the TRIMMEAN function in cell E4.

要验证结果，请对单元格B4：B19使用AVERAGE函数-忽略前2个和后2个数字。这将返回53.06 –与单元格E4中的TRIMMEAN函数相同的结果。

Verify Excel TRIMMEAN function http://www.contextures.com/excelaveragefunctions.html

观看视频 (Watch the Video)

Watch this video to see how to set up a TRIMMEAN formula, and see how it works.

观看此视频，了解如何设置TRIMMEAN公式，以及其工作原理。

演示地址

下载样本文件 (Download the Sample File)

To follow along with the video, and to see the TRIMMEAN example, you can download the sample file from the Excel Average Functions page on my website. The file is in xlsx format, and does not contain macros.

要观看视频并观看TRIMMEAN示例，可以从我的网站上的Excel Average Functions页面下载示例文件。该文件为xlsx格式，不包含宏。