Excel根据指定起始日期计算前N个月移动区间平均值

数据结构约定 #

以下默认数据列排布如下，可根据实际表结构调整公式引用范围：

• A列：ID
• B列：ID对应起始日期（标准Excel日期格式，非文本）
• C列：前3个月平均值输出列
• D列：前6个月平均值输出列
• E-J列：依次为2021年1月-6月数值列，E1-J1为对应列的月份列头，存储标准日期格式的当月首日（如E1值为2021/1/1）

方案1：纯内置公式实现（无宏、兼容性最佳） #

公式逻辑完全匹配给定计算规则：取起始日期前N个完整自然月的数值计算平均值，排除起始日期当月数据。
在C2单元格（首行ID对应的前3个月均值单元格）输入以下公式，按回车后下拉填充整列即可：

=AVERAGEIFS(E2:J2,E$1:J$1,">="&EDATE(B2,-3),E$1:J$1,"<"&EOMONTH(B2,-1)+1)

在D2单元格输入前6个月均值公式，下拉填充：

=AVERAGEIFS(E2:J2,E$1:J$1,">="&EDATE(B2,-6),E$1:J$1,"<"&EOMONTH(B2,-1)+1)

公式逻辑拆解 #

• EDATE(B2,-N)：计算起始日期往前推N个月的首日，作为统计区间左边界
• EOMONTH(B2,-1)+1：计算起始日期所在月的首日，作为统计区间右边界（不包含，即统计截止到上月最后一天）
• AVERAGEIFS自动匹配列头日期落在区间内的数值求平均，样例验证结果完全匹配：
  • ID1起始日期2021年5月：统计区间为2021年2月-4月，计算结果91.67
  • ID2起始日期2021年6月：统计区间为2021年3月-5月，计算结果108.33
  • ID3起始日期2021年4月：统计区间为2021年1月-3月，计算结果83.33

若月份列头为文本格式（如“2021年1月”），需先转换为标准日期格式，否则公式无法正常匹配日期区间。

方案2：VBA自定义函数实现（适合大数据量、规则可扩展场景） #

如果后续需要调整计算规则、或数据量较大公式卡顿，可使用VBA自定义函数，操作步骤如下：

1. 按Alt+F11快捷键打开VBA编辑器
2. 在左侧工程资源管理器中右键点击当前工作簿名称，选择「插入」-「模块」
3. 在弹出的代码编辑窗口粘贴以下代码：

Function AvgPrevNMonths(startDate As Date, dataRng As Range, headerRng As Range, nMonths As Integer) As Variant
    Dim totalVal As Double, validCount As Integer, i As Integer
    Dim rangeStart As Date, rangeEnd As Date, currentColDate As Date
    ' 计算统计区间边界
    rangeStart = DateSerial(Year(startDate), Month(startDate) - nMonths, 1)
    rangeEnd = DateSerial(Year(startDate), Month(startDate), 1)
    totalVal = 0
    validCount = 0
    ' 遍历列匹配符合区间的数值
    For i = 1 To dataRng.Columns.Count
        currentColDate = CDate(headerRng.Cells(1, i).Value)
        If currentColDate >= rangeStart And currentColDate < rangeEnd Then
            totalVal = totalVal + dataRng.Cells(1, i).Value
            validCount = validCount + 1
        End If
    Next
    ' 处理无匹配数据的情况
    If validCount > 0 Then
        AvgPrevNMonths = totalVal / validCount
    Else
        AvgPrevNMonths = CVErr(xlErrNA)
    End If
End Function

4. 关闭VBA编辑器返回Excel界面，即可像内置函数一样调用：

• 前3个月均值：C2单元格输入=AvgPrevNMonths(B2,E2:J2,E$1:J$1,3)后下拉填充
• 前6个月均值：D2单元格输入=AvgPrevNMonths(B2,E2:J2,E$1:J$1,6)后下拉填充

使用VBA的工作簿需保存为.xlsm格式，打开文件时需启用宏才能正常计算。

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