如何用AVERAGEIFS计算多列同组数据的日均平均值

原始数据表格 #

用户输入参数 #

• 起始日期：1/1/2022
• 结束日期：1/5/2022
• 组别：Group B

原有单组别列的计算方案 #

针对单一列同组别数据，可使用以下公式计算指定日期范围内的日均平均值：

=AVERAGEIFS(INDEX($B$2:$E$11,,MATCH($I$3,$B$1:$E$1,0)), $A$2:$A$11, ">="&$G$3, $A$2:$A$11, "<="&$H$3)

计算Group B在指定日期范围的日均平均值结果为：44.6

但该公式存在局限性：当同一组别对应多列数据时（如Group A分为A1、A2两列），AVERAGEIFS仅能识别第一列匹配的组别数据，无法涵盖所有同组列。

多列同组别数据的计算需求 #

当Group A因存在多个子组无法合并为单列时，如何计算指定日期范围内Group A的日均平均值？

解决方案 #

可以使用SUMPRODUCT结合SUMIFS的组合公式，先计算所有匹配组别的数据总和，再统计有效数据的数量，最后求出平均值：

公式示例 #

假设：

• 日期列范围为$A$2:$A$11
• 数据区域为$B$2:$E$11
• 组别标题行范围为$B$1:$E$1
• 起始日期单元格为$G$3，结束日期单元格为$H$3，目标组别单元格为$I$3

公式如下：

=SUMPRODUCT(SUMIFS($B$2:$E$11,$A$2:$A$11,">="&$G$3,$A$2:$A$11,"<="&$H$3)*($B$1:$E$1=$I$3))/SUMPRODUCT(COUNTIFS($A$2:$A$11,">="&$G$3,$A$2:$A$11,"<="&$H$3)*($B$1:$E$1=$I$3))

公式解释 #

1. 分子部分：SUMPRODUCT(SUMIFS(...) * ($B$1:$E$1=$I$3))

   • SUMIFS计算日期范围内每一列的总和
   • ($B$1:$E$1=$I$3)生成布尔数组，匹配目标组别的列返回1，不匹配返回0
   • 两者相乘后，仅保留目标组别的列总和，最终通过SUMPRODUCT累加得到所有同组别列的总数据和

2. 分母部分：SUMPRODUCT(COUNTIFS(...) * ($B$1:$E$1=$I$3))

   • COUNTIFS统计日期范围内每一列的有效数据行数
   • 同样通过布尔数组筛选目标组别的列，累加得到所有同组别列的有效数据总数量

3. 最终用总数据和除以总数量，得到日均平均值

验证计算（Group A在1/1/2022至1/5/2022） #

• 各日期Group A的数值总和：(35+10)+(43+93)+(76+48)+(12+18)+(89+26) = 450
• 有效数据数量：5天×2列=10个
• 日均平均值：450/10 = 45

用上述公式计算结果为45，符合预期。

内容的提问来源于stack exchange，提问作者p1992