AVERAGE函数示例：平均值（带过滤器）

概括

要计算多列数据的条件平均值，可以使用 AVERAGE 函数和FILTER 函数。在所示工作表中，单元格 H5 中的公式为：

=AVERAGE(FILTER(data,group=G5))

其中数据(C5:E16) 和分组(B5:B16) 是已命名的区域。结果是分组“A”中三个月数据的平均值。公式向下复制时，它会计算 G 列中每个分组的平均值。FILTER 函数是 Excel 中的一个新函数。请参阅下文，了解适用于旧版本 Excel 的替代方法。

通用公式

=AVERAGE(FILTER(data,group=A1))

解释

在这个例子中，目标是计算给定组（“A”、“B”或“C”）在 C5:E16 区域内三个月数据的平均值。为方便起见，数据(C5:E16) 和组(B5:B16) 均为命名区域。在下面的文章中，我们将探讨解决此问题的几种方法：

为什么AVERAGEIFS 函数不起作用。
基于平均值+滤波器的解决方案
基于 AVERAGE + IF 函数的解决方案
基于求和积和布尔代数的解决方案

在最新版本的 Excel 中，“筛选”选项（#2）简单易用。在旧版 Excel中，您可以使用方法 #3 或 #4。

AVERAGEIFS 函数不起作用

你可能会想用AVERAGEIFS 函数来解决这个问题。毕竟，它似乎很符合要求。我们只需要根据一个条件计算一段数据的平均值：我们需要检查组(B5:B16) 是否等于“A”、“B”或“C”。实际上，我们可以轻松地使用 AVERAGEIFS 来计算给定组在一个月内的数据平均值。例如，要计算 1 月份组“A”的平均值，我们可以使用如下公式：

=AVERAGEIFS(C5:C16,group,"A") // returns 42

但是，如果我们尝试将 average_range扩展到包含数据中的所有三列（C5:E16），则会收到 #VALUE! 错误：

=AVERAGEIFS(data,group,"A") // returns #VALUE!

为什么？原因是 AVERAGEIFS 函数要求 average_range 的大小与criteria_range 的大小相同。当我们尝试将单列范围组(B5:B16) 与三列范围数据(C5:E16) 一起使用时，AVERAGEIFS 会返回错误。顺便一提，如果我们使用旧版的AVERAGEIF 函数并传入整个数据范围和相同的条件，虽然不会报错，但会得到错误的结果：

=AVERAGEIF(group,"A",data) // returns 42

这是因为 AVERAGEIF 函数对average_range做了一些假设，本质上是将其调整为与range参数匹配，并以范围的左上角单元格为原点。值得注意的是，这种“静默失败”很危险，因为结果看似合理，但实际上是错误的。你可能不喜欢公式错误，但至少它们能告诉你哪里出了问题。

带滤波器的平均值

在最新版本的 Excel 中，这种情况的一个好解决方法是使用AVERAGE 函数和FILTER 函数。图中所示的工作表就采用了这种方法，其中单元格 H5 中的公式向下复制后为：

=AVERAGE(FILTER(data,group=G5))

数据 (C5:E16) 和组(B5:B16) 都是命名区域。在 AVERAGE 函数内部，FILTER 函数配置为使用简单的逻辑表达式过滤 C5:E16 中的数据：

FILTER(data,group=G5)

因为单元格 G5 包含“A”，且组(B5:B16) 包含 12 个值，所以该表达式返回一个包含 12 个 TRUE 和 FALSE 值的数组，如下所示：

{TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}

请注意，数组中的前四个值均为 TRUE，对应于数据中的前 4 行，即 A 组中的数据。该数组作为include参数返回给 FILTER 函数，FILTER 函数使用该数组选择数据的前 4 行（C5:E16）。

FILTER 函数的结果直接作为单个数组传递给 AVERAGE 函数：

=AVERAGE({58,41,48;37,46,32;38,48,38;35,59,46})

AVERAGE 函数返回的最终结果为 43.8，即 FILTER 函数返回的数组中 12 个数字的平均值。由于公式向下复制，它会使用 G 列中对应组的值来计算每个组的平均值。

平均值（带IF）

FILTER 函数是一个较新的函数，旧版 Excel中不存在。如果您使用的是旧版 Excel，可以使用类似这样的简单数组公式来解决此问题：

{=AVERAGE(IF(group=G5,data))}

在这个公式中，我们使用IF 函数而不是 FILTER 函数来筛选每个组中的值。当组中的值与 G5 单元格（“A”）中的值匹配时，IF 函数返回数据中对应的值。当值不匹配时，IF 函数返回数据中对应值的 FALSE。IF函数计算完成后，返回给 AVERAGE 函数的结果数组如下所示：

=AVERAGE({58,41,48;37,46,32;38,48,38;35,59,46;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE})

之所以能做到这一点，是因为AVERAGE 函数会自动忽略逻辑值 TRUE 和 FALSE。这是一个数组公式，在旧版本的 Excel 中必须使用 Ctrl + Shift + Enter 组合键输入。

使用这种方法需要注意的一点是，空单元格会被视为零值，并被计入平均值的计算中。这是因为当空单元格通过 IF 函数处理时，它们会被赋值为零 (0)。虽然 AVERAGE 函数会忽略空值，但它会将零 (0) 值包含在平均值的计算中。为了避免这个问题，您可以添加第二个 IF 函数来检测空值，如下所示：

{=AVERAGE(IF(group=G5,IF(data<>"",data)))}

在这个公式中，只有属于组“A”且不为空的值才会传递给 AVERAGE 函数。所有其他值都会变为 FALSE，并被 AVERAGE 函数忽略。

以上两个公式均为数组公式，在旧版 Excel 中必须使用 Ctrl + Shift + Enter 组合键输入。在当前版本的 Excel 中，由于原生支持数组公式，这些公式可以直接使用。

SUMPRODUCT 函数

正如您可能已经猜到的，您也可以使用功能强大的SUMPRODUCT 函数在旧版本的 Excel 中解决这个问题。公式如下：

=SUMPRODUCT(--(group=G5)*data)/SUMPRODUCT(--(group=G5)*(data<>""))

在这个公式中，第一个 SUMPRODUCT 函数计算组“A”（从单元格 G5 开始）中所有数据的总和：

=SUMPRODUCT(--(group=G5)*data) // sum (526)

第二个 SUMPRODUCT 函数计算同一组中所有数据的计数：

SUMPRODUCT(--(group=G5)*(data<>"")) // count (12)

在计算完两个 SUMPRODUCT 公式后，最后一步是将总和除以计数：

=SUMPRODUCT(--(group=G5)*data)/SUMPRODUCT(--(group=G5)*(data<>""))
=526/12
=43.8

虽然 SUMPRODUCT 公式稍微复杂一些，但不需要像 Control + Shift + Enter 那样以特殊方式输入，因为SUMPRODUCT 本身就可以处理数组运算。

详情页