AVERAGE函数示例：每月平均通话时长

概括

要计算每月平均通话时长，可以使用AVERAGEIFS 函数和EDATE 函数。在所示示例中，H5 单元格中的公式为：

=AVERAGEIFS(data[Duration],data[Date],">="&G5,data[Date],"<"&EDATE(G5,1))

其中，数据是位于 B5:E16 区域的Excel 表格，G5:G8 区域中的值是有效的Excel 日期。公式向下复制后，结果是 G 列中列出的每个月的平均通话时长。

通用公式

=AVERAGEIFS(durations,dates,">="&A1,dates,"<"&EDATE(A1,1))

解释

在本例中，目标是利用 B 列中的日期和 E 列中的通话时长，计算 G 列中列出的每个月的平均通话时长（以分钟为单位）。下文将介绍两种方法。第一种公式基于AVERAGEIFS 函数，该函数用于根据多个条件计算平均值。第二种公式基于FILTER 函数和AVERAGE 函数。为方便起见，所有数据都位于名为“data”的Excel 表格中，范围为 B5:E16。

注意：G5:G8 单元格中的值是有效的Excel 日期。这样可以方便地在公式条件中使用这些值。您可以使用自定义数字格式以任何您喜欢的方式显示这些日期。

平均函数

AVERAGEIFS 函数计算指定范围内满足一个或多个条件（称为标准）的单元格的平均值。AVERAGEIFS 的通用语法如下：

=AVERAGEIFS(avg_range,range1,criteria1,range2,criteria2,...)

请注意，每个条件都以单独的 [范围，条件] 对的形式输入。在本题中，我们需要配置 AVERAGEIFS 函数，使其使用两个条件按月计算平均金额：(1) 日期大于或等于当月的第一天，(2) 日期小于下个月的第一天。我们首先从平均范围入手，该范围对应于表中的“通话时长”列：

=AVERAGEIFS(data[Duration],

接下来，我们需要输入筛选条件，以便确定每个月的正确日期。为了简化这一步骤，我们将 G5:G8 单元格中的值设置为“每月第一天”的日期。对于起始日期，我们使用数据[日期]列作为筛选条件范围，并将大于等于运算符 (>=)连接到单元格 G5 作为筛选条件：

=AVERAGEIFS(data[Duration],data[Date],">="&G5,

对于结束日期，我们再次使用data[Date]列作为条件范围：

=AVERAGEIFS(data[Duration],data[Date],">="&G5,data[Date],

对于筛选条件，我们使用EDATE 函数返回下个月的第一天：

=EDATE(E5,1) // first of next month

H5单元格中的最后一个公式（向下复制）为：

=AVERAGEIFS(data[Duration],data[Date],">="&G5,data[Date],"<"&EDATE(G5,1))

请注意，我们需要将日期与逻辑运算符连接起来，这是 AVERAGEIFS 函数的要求。结构化引用类似于绝对引用，不会改变，而对 G5 列的引用是相对引用，每行都会改变。公式向下复制时，会返回 G 列中每个月的平均通话时长。

使用平均值进行过滤

另一种按月计算平均值的好方法是使用FILTER 函数和AVERAGE 函数，如下所示：

=AVERAGE(FILTER(data[Duration],TEXT(data[Date],"mmyy")=TEXT(G5,"mmyy")))

从内到外，FILTER 函数提取给定月份的持续时间，并将这些数值返回给 AVERAGE 函数，由 AVERAGE 函数计算平均值。FILTER 函数的配置如下：

FILTER(data[Duration],TEXT(data[Date],"mmyy")=TEXT(G5,"mmyy"))

第一个参数array被设置为data[Duration]。第二个参数include是大部分工作完成的地方：

TEXT(data[Date],"mmyy")=TEXT(G5,"mmyy")

这里，我们使用TEXT 函数将日期转换为“mmyy”格式的文本字符串。由于列表中有 12 个日期，全部都在 2023 年，因此结果是一个包含 12 个值的数组，如下所示：

{"0123";"0123";"0123";"0223";"0223";"0223";"0223";"0323";"0323";"0323";"0323";"0423"}

接下来，使用相同的方法，通过 TEXT 函数从 G5 中的日期中提取月份和年份：

TEXT(G5,"mmyy") // returns "0123"

然后将上述两个结果进行比较。结果是一个包含 12 个 TRUE 和 FALSE 值的数组，如下所示：

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

在这个数组中，TRUE 值表示 B5:B16 单元格中的日期与 G5 单元格中的日期属于同一月份和年份，即一月份的日期。FILTER 函数使用此数组仅检索一月份的值。结果直接传递给 AVERAGE 函数，如下所示：

{0.0111111111111111;0.0118055555555556;0.0173611111111112}

Excel 中的时间单位是1 天的小数部分，因此小数部分代表的是原始时间。AVERAGE 函数返回的结果约为 0.01343，使用自定义数字格式“mm:ss”格式化后，显示为 19:20。公式向下复制时，FILTER 函数会将每个月的持续时间传递给 AVERAGE 函数，最终返回结果。

注意：此公式的整体结构更加简洁优雅，因此您可能会疑惑，为什么我们不以与 AVERAGEIFS 函数相同的方式使用 TEXT 函数？遗憾的是，这是不可能的，因为 AVERAGEIFS 函数不支持在范围参数中使用数组运算。

详情页