怎样去掉最大值和最小值，计算每类的平均值？

前段时间在一家企业培训EXCEL课程的时候，学员小V咨询如何计算出下方这个表格中，每个部门的考试分，去掉一个最高分和一个最低分之后的平均分。

我们先来介绍如果不分类，只是计算考试分数列，去掉最大值和最小值之后计算平均值，应该用哪个函数来实现。

这里需要用到TRIMMEAN函数返回数据集的内部平均值。TRIMMEAN 计算排除数据集顶部和底部尾数中数据点的百分比后取得的平均值。

函数语法：TRIMMEAN(array, percent)

Array：需要进行整理并求平均值的数组或数值区域。

percent：从计算中排除数据点的分数。例如，如果 percent=0.2，从 20 点 (20 x 0.2) 的数据集中剪裁 4 点：数据集顶部的 2 点和底部的2 点。

那这里的公式要这样写：

=TRIMMEAN(C2:C17,2/COUNT(C2:C17))

第1个参数是考试分数所在的数据区域，第2个参数是要去掉几个数字，比如这里要去掉1个最大值和1个最小值，也就是2个数字，去除除以这一列的总个数，用COUNT函数计算出总个数，算出百分比。就得到了去掉最大值和最小值之后的平均值。

理解了这个函数的语法之后，我们再来思考学员小V要实现的分类去最大值和最小值算平均值要如何实现。也就是说，第1个参数要是动态的一个区域，根据E4到E7的部门名称，到C列获取对应部门的考试分。

这里为了方便后面的计算，我们先用COUNTIFS的分类计数函数，计算出每个部门的人数。此函数的语法是：

COUNTIFS（条件范围1，条件1，条件范围2，条件2，……）

本例中的函数是：=COUNTIFS($A$2:$A$17,E4)

之后我们需要借助动态区域的OFFSET函数来根据E4:E7的部门名称，到A列中动态选择对应的部门列表。我们先来看一下OFFSET函数的语法：

OFFSET（起始地址，向上/下偏移的行数，向左/右偏移的列数，返回的行数，返回的列数）

如果是向上和向左偏移就输入负数。

举例说明一下这个函数的用法，比如我现在要返回“办公室”这个部门的员工对应的考试分数所在的区域，也就是C2:C5这个范围，那我的OFFSET函数就可以这样来写：

OFFSET(C1,1,0,4,1)

C1是考试分列的起始地址，第1个参数1是指向下偏移1行，移动到C2；第3个参数0是指没有向右的偏移；第4个参数4是指一共有4个单元格是办公室；第5个参数1是指要返回1列的数据，也就是最后的数据区域是C2:C5这个动态的区域。

现在我们就需要把第2个和第3个参数变成动态。

我们需要查询“办公室”在A列中的起始位置，这时候就需要用到MATCH这个查询位置的函数，我们先来看看MATCH函数的语法。

MATCH（查询值，包含查询值的1行或1列，0/1）

0：精确查询 1：区间查询

本例中的函数是：MATCH(E2,A1:A17,0)

现在我们来把这几个函数组合在一起，公式如下：

=TRIMMEAN(OFFSET($C$1,MATCH(E4,$A$2:$A$17,0),0,F4,1),2/F4)

到此，就实现了小V需要的去掉最大值和最小值，计算出每类的平均值。