Excel公式技巧：获取最后5个数值中3个数的平均值

excelperfect

最近，使用工作表记录了员工日常的表现，表现是用分数来评估的。然而，记录并不连续，并且每位员工记录的次数又会有不同，如下图1所示。

图1

我想得到每位员工最后5次得分中，去除最高分和最低分后的平均值。当然，如果该名员工得分次数不足5次，则取平均值。

首先，我们需要确定最后的5位数值。但是，每位员工得分的次数不一样，且输入也不一定是连续的，例如代号A的员工最后5位数值位于B2:F2，而代号B的员工最后5位数值位于K3:Q3，一个起始于第2列，一个起始于第11列，如何获取这个起始位置是关键。

此时，COLUMN函数与比较判断相配合又派上用场了。

COLUMN(A2:Z2)*(A2:Z2<>'')

将会得到：

{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26}*{TRUE,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE,FALSE}

这样，就会提取出所有含有数值的列的列号，而空单元格的列号则对应为0：

{1,2,3,4,5,6,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0}

同样，对于：

COLUMN(A3:Z3)*(A3:Z3<>'')

会得到：

{1,2,3,4,5,6,0,0,9,10,11,0,0,14,15,16,17,0,0,0,0,0,0,0,0,0}

……

这样，我们使用LARGE函数取这个数组中第5大的值，也即最后5位数值的起始位置：

LARGE(COLUMN(A3:Z3)*(A3:Z3<>''),5)

获得起始位置后，我们使用INDEX函数返回该位置的单元格：

INDEX(LARGE(COLUMN(A3:Z3)*(A3:Z3<>''),5))

再与单元格Z2组合，得到最后5位数值所在的单元格区域：

Z2:INDEX(LARGE(COLUMN(A3:Z3)*(A3:Z3<>''),5))

注意，这里将最后一个单元格放在了前面，以确定要计算的单元格区域。

以上，就是公式的核心部分。理解了这部分，后面就好办了。

求该区域的数值之和，然后减去其中的最大值和最小值，除以3，就是平均值了。单元格2中完整的公式如下：

=IF(COUNT(B2:Z2)<5,AA2,(SUM((Z2:INDEX(A2:Z2,LARGE(COLUMN(A2:Z2)*(A2:Z2<>''),5))))-LARGE((Z2:INDEX(A2:Z2,LARGE(COLUMN(A2:Z2)*(A2:Z2<>''),5))),1)-SMALL((Z2:INDEX(A2:Z2,LARGE(COLUMN(A2:Z2)*(A2:Z2<>''),5))),1))/3)

向下拖拉复制公式至数据末尾。