Day34 - 数据分析工具(1)

今日学习目标

  • 掌握数据统计方法
  • 实现多维度数据分析
  • 完成数据分析报告生成

知识点

### 1. 统计分析函数
- SumIfs、CountIfs多条件统计
- AverageIf条件平均
- 数组公式进行复杂计算
### 2. 数据对比分析
- 差异计算：同期比、环比
- 增长率计算
- 占比分析
### 3. 可视化分析
- 迷你图Sparklines
- 条件格式数据条
- 色阶热力图

今日代码

' 销售数据分析
Sub AnalyzeSales()
    Dim ws As Worksheet
    Dim lastRow As Long

    Set ws = Worksheets("销售数据")
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    ' 按产品分析
    Call AnalyzeByProduct(ws, lastRow)

    ' 按区域分析
    Call AnalyzeByRegion(ws, lastRow)

    MsgBox "数据分析完成！"
End Sub

' 按产品分析
Sub AnalyzeByProduct(ws As Worksheet, lastRow As Long)
    Dim dict As Object
    Dim i As Long
    Dim product As String
    Dim sales As Double

    Set dict = CreateObject("Scripting.Dictionary")

    For i = 2 To lastRow
        product = ws.Cells(i, 2).Value
        sales = ws.Cells(i, 5).Value
        If dict.Exists(product) Then
            dict(product) = dict(product) + sales
        Else
            dict.Add product, sales
        End If
    Next i

    ' 输出结果
    Dim resultWs As Worksheet
    Set resultWs = Worksheets("分析结果")
    resultWs.Range("A3").Value = "按产品分析"
    resultWs.Range("A4").Value = "产品"
    resultWs.Range("B4").Value = "销售额"

    Dim key As Variant
    i = 5
    For Each key In dict.Keys
        resultWs.Cells(i, 1).Value = key
        resultWs.Cells(i, 2).Value = dict(key)
        i = i + 1
    Next key
End Sub

练习题

  1. 实现按客户维度的销售分析
  2. 计算同比增长率
  3. 使用条件格式显示数据趋势