使用VBA将表中的每周数据转换为每月数据

Sub ConvertWeeklyToMonthly()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim currentMonth As String
    Dim currentMonthTotal As Double
    Dim currentRow As Long
    
    ' 设置要操作的工作表
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
    ' 获取最后一行的行号
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 初始化变量
    currentMonth = ""
    currentMonthTotal = 0
    currentRow = 2 ' 假设数据从第二行开始
    
    ' 循环遍历每一行数据
    Do While currentRow <= lastRow
        ' 获取当前行的日期
        Dim currentDate As Date
        currentDate = ws.Cells(currentRow, "A").Value
        
        ' 获取当前行的值
        Dim currentValue As Double
        currentValue = ws.Cells(currentRow, "B").Value
        
        ' 判断是否为新的月份
        If Format(currentDate, "yyyy-mm") <> currentMonth Then
            ' 如果是新的月份，则将上一个月的总和写入结果列
            If currentMonth <> "" Then
                ws.Cells(currentRow - 1, "C").Value = currentMonthTotal
            End If
            
            ' 更新当前月份和总和
            currentMonth = Format(currentDate, "yyyy-mm")
            currentMonthTotal = 0
        End If
        
        ' 累加当前月份的总和
        currentMonthTotal = currentMonthTotal + currentValue
        
        ' 移动到下一行
        currentRow = currentRow + 1
    Loop
    
    ' 将最后一个月的总和写入结果列
    ws.Cells(lastRow, "C").Value = currentMonthTotal
End Sub