如何在Excel中根据指定起止日期区间计算USD数值的动态平均值

方案1：内置公式实现（优先推荐，比VBA更简单） #

直接用Excel自带的AVERAGEIFS函数即可实现需求，无需编写任何代码，结果支持自动动态更新。

前提假设（你可以根据自己的实际单元格位置调整参数） #

• 原始数据的日期列区域为A2:A1000
• 原始数据的USD数值列区域为B2:B1000
• 你设置的「数据起始日期」输入单元格为D1
• 你设置的「数据结束日期」输入单元格为E1

计算公式 #

在你需要展示平均值的单元格输入如下公式即可：

=AVERAGEIFS(B2:B1000, A2:A1000, ">="&D1, A2:A1000, "<="&E1)

如果需要处理区间内无匹配数据的报错问题，可以套一层IFERROR：

=IFERROR(AVERAGEIFS(B2:B1000, A2:A1000, ">="&D1, A2:A1000, "<="&E1), "无匹配数据")

特点 #

• 修改起始/结束日期、修改原始USD数值后，结果会自动刷新，完全满足动态更新要求
• 不需要启用宏，没有宏安全限制，操作成本极低

方案2：VBA实现（仅适合需要联动其他自动化逻辑的场景） #

如果你的需求需要和其他VBA自动化流程联动，可以用以下两种VBA方案实现：

方案2.1 自定义函数 #

打开VBA编辑器（快捷键Alt+F11），插入模块，粘贴如下代码：

Function CalcUSDAvg(startDateRng As Range, endDateRng As Range, dateCol As Range, valueCol As Range) As Variant
    On Error Resume Next
    CalcUSDAvg = WorksheetFunction.AverageIfs(valueCol, dateCol, ">=" & startDateRng.Value, dateCol, "<=" & endDateRng.Value)
    If Err.Number <> 0 Then CalcUSDAvg = "无匹配数据"
End Function

回到Excel界面，在结果单元格输入=CalcUSDAvg(D1,E1,A2:A1000,B2:B1000)即可使用，同样支持自动更新。

方案2.2 事件触发自动计算 #

如果需要把结果固定写入某个单元格，修改起止日期后自动刷新，可以用工作表Change事件。右键点击当前工作表标签，选择「查看代码」，粘贴如下代码：

Private Sub Worksheet_Change(ByVal Target As Range)
    ' 仅修改起止日期单元格时触发计算，D1为起始日期单元格，E1为结束日期单元格，可自行修改
    If Intersect(Target, Me.Range("D1:E1")) Is Nothing Then Exit Sub
    
    On Error Resume Next
    Dim avgResult As Variant
    avgResult = WorksheetFunction.AverageIfs(Me.Range("B2:B1000"), Me.Range("A2:A1000"), ">=" & Me.Range("D1").Value, Me.Range("A2:A1000"), "<=" & Me.Range("E1").Value)
    
    If Err.Number <> 0 Then
        ' 无匹配数据时的返回值
        Me.Range("F1").Value = "无匹配数据"
    Else
        ' 计算结果写入F1单元格，可自行修改
        Me.Range("F1").Value = avgResult
    End If
End Sub

方案选择建议 #

• 普通需求直接选内置公式方案，比VBA简单很多，不需要额外配置，稳定性更高
• 只有当你需要把这个计算逻辑和其他自动化操作（比如批量生成报表、自动发送邮件）联动时，再选择VBA方案

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