如何用VBA创建带指定颜色与分组图例的Excel堆叠直方图

核心思路 #

要实现需求，需先按错误名称（AA列）分组，每个错误名称对应一个独立图表系列；为每个系列匹配对应颜色（AC列）；最后将图表设为堆叠柱状图，X轴绑定标识符（Z列），图例自动按错误名称分组显示。

完整VBA代码 #

Sub CreateStackedBarChartWithColors()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim uniqueErrors As Collection
    Dim errName As Variant
    Dim xLabels As Variant
    Dim chartObj As ChartObject
    Dim chartSeries As Series
    Dim i As Long, j As Long
    
    ' 指定数据源工作表（根据实际修改）
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    lastRow = ws.Cells(ws.Rows.Count, "Z").End(xlUp).Row
    
    ' 提取唯一错误名称（用于生成系列和图例）
    Set uniqueErrors = New Collection
    On Error Resume Next
    For i = 2 To lastRow ' 假设第1行为表头
        uniqueErrors.Add ws.Cells(i, "AA").Value, Key:=CStr(ws.Cells(i, "AA").Value)
    Next i
    On Error GoTo 0
    
    ' 获取X轴标签（标识符）
    xLabels = ws.Range("Z2:Z" & lastRow).Value
    
    ' 创建堆叠柱状图对象
    Set chartObj = ws.ChartObjects.Add(Left:=ws.Range("AD2").Left, Width:=600, Top:=ws.Range("AD2").Top, Height:=400)
    With chartObj.Chart
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "错误频次堆叠直方图"
        .Axes(xlCategory).Title.Text = "标识符"
        .Axes(xlValue).Title.Text = "频次"
    End With
    
    ' 为每个错误名称创建系列并配置数据与颜色
    For Each errName In uniqueErrors
        ' 初始化频次数据数组
        Dim freqData() As Double
        ReDim freqData(1 To UBound(xLabels, 1))
        
        ' 匹配当前错误的频次数据与对应颜色
        Dim targetColor As Long
        targetColor = vbWhite ' 默认填充色
        For j = 1 To UBound(xLabels, 1)
            If ws.Cells(j + 1, "AA").Value = errName Then
                freqData(j) = ws.Cells(j + 1, "AB").Value
                targetColor = ws.Cells(j + 1, "AC").Interior.Color ' 获取单元格填充色
            Else
                freqData(j) = 0 ' 无对应数据的位置填0
            End If
        Next j
        
        ' 添加新系列并设置属性
        Set chartSeries = chartObj.Chart.SeriesCollection.NewSeries
        With chartSeries
            .Name = errName
            .Values = freqData
            .XValues = xLabels
            .Format.Fill.ForeColor.RGB = targetColor
            .Format.Line.Visible = False ' 隐藏条形边框
        End With
    Next errName
    
    ' 调整图例位置（可选）
    chartObj.Chart.Legend.Position = xlLegendPositionRight
End Sub

关键要点说明 #

• 错误名称分组：通过Collection对象去重AA列的错误名称，确保每个错误对应一个独立系列，自动实现图例的分组展示。
• 颜色匹配：遍历数据时提取对应错误名称行的AC列单元格填充色，直接赋值给系列的填充颜色，保证条形颜色与表格配置一致。
• 堆叠格式设置：将图表类型设为xlColumnStacked，确保同一标识符下的不同错误频次堆叠显示。
• 空值处理：对当前错误名称未覆盖的标识符，频次填0，保证X轴标签完整无缺失。

注意事项 #

1. 需将代码中的Sheet1替换为实际数据源所在工作表名称，若表头不在第1行，需修改循环起始行。
2. 若AC列使用的是字体颜色而非填充色，需将Interior.Color改为Font.Color。
3. 图表的位置、尺寸可通过ChartObjects.Add的参数调整。

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