如何用Visual Basic提取指定工作日销售额用于Excel Average()函数

方案1：遍历筛选周一销售额并计算平均 #

以下是可直接运行的VBA代码，能遍历数据区域筛选2022年所有周一的销售额，再计算平均值：

Sub CalculateMondayAverage()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim cell As Range
    Dim salesArray() As Double
    Dim arrIndex As Integer
    
    ' 指定目标工作表（根据实际表名修改）
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    ' 定义数据区域（假设表头在第1行，数据从第2行到最后一行）
    Set dataRange = ws.Range("A2:C" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    
    arrIndex = 0
    ' 遍历日期列单元格
    For Each cell In dataRange.Columns(1).Cells
        ' 筛选2022年且为周一的记录（vbMonday将周一设为一周第1天，返回1代表周一）
        If Year(cell.Value) = 2022 And Weekday(cell.Value, vbMonday) = 1 Then
            arrIndex = arrIndex + 1
            ' 动态扩展数组并存入对应销售额
            ReDim Preserve salesArray(1 To arrIndex)
            salesArray(arrIndex) = cell.Offset(0, 2).Value
        End If
    Next cell
    
    ' 计算并输出结果
    If arrIndex > 0 Then
        MsgBox "2022年周一的平均销售额为：" & Application.Average(salesArray)
        ' 也可将结果写入指定单元格，比如：ws.Range("E2").Value = Application.Average(salesArray)
    Else
        MsgBox "未找到2022年周一的销售额数据"
    End If
End Sub

关键代码说明 #

• Weekday(cell.Value, vbMonday)：把周一设为一周起始日，返回值1对应周一、2对应周二，便于精准筛选
• Year(cell.Value) = 2022：确保只统计2022年的有效数据
• ReDim Preserve salesArray：动态扩展数组存储符合条件的销售额，避免数组长度不足问题
• Application.Average(salesArray)：调用Excel内置的Average函数计算平均值

扩展：批量计算所有工作日平均值 #

如果需要一次性算出所有工作日的平均销售额，可借助字典分组存储数据，代码示例如下：

Sub CalculateAllWeekdayAverages()
    Dim ws As Worksheet
    Dim dataRange As Range
    Dim cell As Range
    Dim salesDict As Object
    Dim weekdayName As String
    
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set dataRange = ws.Range("A2:C" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row)
    Set salesDict = CreateObject("Scripting.Dictionary")
    
    For Each cell In dataRange.Columns(1).Cells
        If Year(cell.Value) = 2022 Then
            ' 获取星期名称（如"周一"）
            weekdayName = Format(cell.Value, "aaaa")
            ' 将销售额按星期分组存储
            If salesDict.Exists(weekdayName) Then
                salesDict(weekdayName) = salesDict(weekdayName) & "," & cell.Offset(0, 2).Value
            Else
                salesDict(weekdayName) = cell.Offset(0, 2).Value
            End If
        End If
    Next cell
    
    ' 将结果写入工作表E、F列
    Dim key As Variant
    Dim avgValue As Double
    Dim outputRow As Integer
    outputRow = 2
    ws.Range("E1:F1").Value = Array("工作日", "平均销售额")
    
    For Each key In salesDict.Keys
        avgValue = Application.Average(Split(salesDict(key), ","))
        ws.Range("E" & outputRow).Value = key
        ws.Range("F" & outputRow).Value = avgValue
        outputRow = outputRow + 1
    Next key
End Sub

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