如何结合Average与SumIf区分零值与空白单元格，实现随月份推进的动态预算平均值计算？

Hey Michael，我完全get到你的痛点了——想算已经过去的月份里某类支出的动态平均值，既要把当月没花钱的0算进去，又得排除还没到的空白月份，结果现在公式把空白也当成参与平均的项，导致结果不准对吧？别慌，咱们来一步步解决这个问题。

其实问题的关键就是区分已发生的月份（不管花了0还是正数，都要算入平均）和未发生的月份（空白单元格，直接排除）。Excel里的AVERAGEIFS函数刚好能帮我们搞定这个，因为它会自动忽略空白单元格，但会把数值0当成有效数据纳入计算——这完全匹配你的需求！

情况1：月份列是日期格式（比如1/31/2024、2/29/2024） #

假设你的表格结构是：

• 月份列：B列（B2:B13对应1-12月的日期）
• 类别列：C列（标记“Spending”“Gas”等类别）
• 支出列：D列（已发生月份填0或实际金额，未发生月份留空白）

那计算“Spending”类的动态平均，直接用这个公式：

=AVERAGEIFS(D:D, B:B, "<="&TODAY(), C:C, "Spending")

公式解释：

• AVERAGEIFS：多条件平均值函数
• D:D：要计算平均值的支出列
• B:B, "<="&TODAY()：只统计日期小于等于今天的已发生月份
• C:C, "Spending"：只统计“Spending”类别的支出

拿你的例子来说，1月和2月都花了900，12月留空白，现在是2月，公式会自动取1、2月的数值计算，结果就是(900+900)/2=900，正好是你想要的M5的结果；如果2月没花钱填了0，那结果就是(900+0)/2=450，完全符合动态平均的要求。

情况2：月份列是文本格式（比如“January”“February”） #

如果你的月份是英文文本，先把文本转成可判断的月份数字，用这个公式：

=AVERAGEIFS(D:D, MONTH(DATEVALUE(B:B&" 1")), "<="&MONTH(TODAY()), C:C, "Spending")

公式解释：

• DATEVALUE(B:B&" 1")：把“January”转成“January 1”的日期格式
• MONTH(...)：提取日期对应的月份数字（1-12）
• 剩下的逻辑和第一种情况一致，只统计当前月份及之前的支出

额外注意事项 #

• 一定要记得：已发生但没花钱的月份，要手动输入0，不能留空白——留空白会被公式当成未发生月份排除，而输入0才会被算入平均；
• 如果你的表格有备注列，完全不影响公式计算，因为我们只用到了月份、类别和支出三列的数据；
• 要是你习惯用SUMIF+COUNTIF的组合，也可以这么写（结果和AVERAGEIFS完全一样）：

=SUMIFS(D:D, B:B, "<="&TODAY(), C:C, "Spending") / COUNTIFS(B:B, "<="&TODAY(), C:C, "Spending", D:D, "<>""")

这个公式是先算出已发生月份的总支出，再除以已发生的月份数（包含0的情况，排除空白），本质和AVERAGEIFS是一样的，看你个人习惯选哪个就行。

备注：内容来源于stack exchange，提问作者Michael Lakner