excel宏处理魔法代码，实现按月份统计销售额和按产品统计销售额

前言

在日常办公中，我们经常会遇到统计数据的问题。
今天我来给大家介绍，一秒钟生成统计数据的方法。
在这里插入图片描述

第一步：打开文件

在这里插入图片描述

按ALT + F11 打开Excel的VBA编辑器
在这里插入图片描述

第二步：选中左侧任意一个sheet双击

在这里插入图片描述

第三步：粘贴魔法代码

Sub ProcessSalesData()
    Dim ws As Worksheet
    Dim summaryWs As Worksheet
    Dim lastRow As Long
    Dim i As Long
    Dim monthSales As Object
    Dim productSales As Object
    Dim dateKey As String
    Dim product As String
    Dim summaryRow As Long
    Dim key As Variant

    ' 设置原始数据工作表为第一个工作表
    Set ws = ThisWorkbook.Sheets(1)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' 删除空行
    For i = lastRow To 2 Step -1
        If Application.WorksheetFunction.CountA(ws.Rows(i)) = 0 Then
            ws.Rows(i).Delete
        End If
    Next i

    ' 格式化日期
    For i = 2 To lastRow
        If IsDate(ws.Cells(i, 1).Value) Then
            ws.Cells(i, 1).NumberFormat = "yyyy-mm-dd"
        End If
    Next i

    ' 创建汇总工作表
    On Error Resume Next
    Set summaryWs = ThisWorkbook.Sheets("销售额统计")
    On Error GoTo 0
    If summaryWs Is Nothing Then
        Set summaryWs = ThisWorkbook.Sheets.Add(After:=ws)
        summaryWs.Name = "销售额统计"
    Else
        summaryWs.Cells.Clear
    End If

    ' 添加汇总表头
    summaryWs.Cells(1, 1).Value = "月份"
    summaryWs.Cells(1, 2).Value = "销售额"
    summaryWs.Cells(1, 4).Value = "产品"
    summaryWs.Cells(1, 5).Value = "销售额"
    summaryRow = 2

    ' 创建字典对象存储每月和每个产品的销售总额
    Set monthSales = CreateObject("Scripting.Dictionary")
    Set productSales = CreateObject("Scripting.Dictionary")

    ' 计算每月和每个产品的总销售额
    For i = 2 To lastRow
        If IsDate(ws.Cells(i, 1).Value) Then
            dateKey = Format(ws.Cells(i, 1).Value, "yyyy-mm")
            If Not monthSales.exists(dateKey) Then
                monthSales.Add dateKey, ws.Cells(i, 3).Value
            Else
                monthSales(dateKey) = monthSales(dateKey) + ws.Cells(i, 3).Value
            End If
        End If
        
        product = ws.Cells(i, 2).Value
        If product <> "" Then
            If Not productSales.exists(product) Then
                productSales.Add product, ws.Cells(i, 3).Value
            Else
                productSales(product) = productSales(product) + ws.Cells(i, 3).Value
            End If
        End If
    Next i

    ' 将每月销售总额写入汇总工作表
    For Each key In monthSales.keys
        summaryWs.Cells(summaryRow, 1).Value = key
        summaryWs.Cells(summaryRow, 2).Value = monthSales(key)
        summaryRow = summaryRow + 1
    Next key

    ' 将产品销售总额写入汇总工作表
    summaryRow = 2
    For Each key In productSales.keys
        summaryWs.Cells(summaryRow, 4).Value = key
        summaryWs.Cells(summaryRow, 5).Value = productSales(key)
        summaryRow = summaryRow + 1
    Next key

    ' 清理
    Set monthSales = Nothing
    Set productSales = Nothing
End Sub