如何在 Excel 中创建堆积簇状柱形图/条形图？

在专业的数据分析中，将多个类别和数据组可视化到一个图表中可以提供更清晰的比较和更高效的数据解读。一个常见的需求是显示堆积和簇状数据的组合，例如按区域和季度显示销售额，并将它们并列分组。本文将引导您完成在 Excel 中创建堆积簇状柱形图的步骤，使您可以将各个类别的值作为簇状条形进行比较，而每个簇由堆积的部分组成。这种方法能够在组之间以及跨多个变量进行强大的对比分析，如下方截图所示。

在 Excel 中创建堆积簇状柱形图

要在 Excel 中创建堆积簇状柱形图，首先需要了解 Excel 并不原生支持这种类型的图表。然而，您可以通过精心准备数据并自定义图表布局来模拟这种效果。

✅ 首先需要了解的内容：

• Excel 没有内置的“堆积簇状柱形图”类型。结果是通过数据布局技巧实现的。
• 您必须重组源数据以模拟簇状分组。
• 在类别组之间添加空行以视觉上分离每个簇。

让我们通过示例销售数据逐步演示该过程，这些数据涉及多个季度的产品。

1. 组织原始数据：在此示例中，我们在 A 列中有产品名称，在相邻列中有销售数据（例如，Q1 和 Q2 的实际与目标）。目标是将每个产品的数据并排分组，并在每个簇中堆叠显示实际/目标值。

2. 重组数据：您需要将每组数据（例如，每个产品行）复制到新布局中，并在每组之间插入一个空行。这有助于 Excel 将每组解释为堆积柱形图中的单独簇。

3. 创建图表：选择重新组织后的数据。然后转到 插入 > 柱形图或条形图 > 堆积柱形图。

4. 格式化系列：右键单击图表中的任意柱形，选择 格式化数据系列。

5. 减小分类间距：在 格式化数据系列 窗格中，转到 系列选项，并设置 分类间距 = 0% 以视觉上压缩每个组为一个堆积簇。

6. 调整图例和布局：右键单击图例 > 格式化图例。

7. 选择图例位置：在 格式化图例 窗格中，在 图例选项 下选择首选的图例位置（右侧、顶部、左侧或底部），以最佳适应您的图表布局并避免与数据重叠。

✅ 结果：现在，您拥有了一个堆积簇状柱形图，其中每个产品的实际/目标数据被分组并并排堆叠以便快速比较。

⚠️ 局限性：此技术适用于较小的数据集。但对于大型数据集或频繁变化的数据，手动重组容易出错。有关自动执行此过程的 VBA 和公式解决方案，请参阅以下部分。

VBA 代码 – 自动化数据重塑和图表生成

在处理大型数据集或频繁更新的报告时，手动重新排列数据以创建堆积簇状图表可能既繁琐又容易出错。使用 VBA（Visual Basic for Applications）可以自动化整个过程——从重塑源表到插入图表——节省时间和精力。

此方法最适合熟悉 Excel 宏的用户或允许使用 VBA 的环境。如果您的图表结构保持不变但数据定期更新，则特别有效。以下是实施方法：

步骤 1：按 Alt + F11 打开 VBA 编辑器。在编辑器中，点击 插入 > 模块。

步骤 2：将以下 VBA 代码粘贴到模块窗口中：

Sub CreateStackedClusteredChart()
    Dim ws As Worksheet
    Dim rngData As Range
    Dim chartObj As ChartObject
    Dim chartRange As Range
    Dim xTitleId As String

    On Error Resume Next
    Set ws = ActiveSheet
    xTitleId = "KutoolsforExcel"

    ' Prompt user to select original data
    Set rngData = Application.InputBox("Select the original grouped data (including all headers):", xTitleId, Selection.Address, Type:=8)
    If rngData Is Nothing Then Exit Sub

    ' Create new worksheet for reshaped data
    Dim wsChartData As Worksheet
    Set wsChartData = Worksheets.Add
    wsChartData.Name = "ChartData_" & Format(Now(), "hhmmss")

    Dim numRows As Long, numCols As Long, i As Long, j As Long, outRow As Long
    numRows = rngData.Rows.Count
    numCols = rngData.Columns.Count
    outRow = 1

    ' Add headers
    wsChartData.Cells(outRow, 1).Value = "Category"
    For j = 2 To numCols
        wsChartData.Cells(outRow, j).Value = rngData.Cells(1, j).Value
    Next j
    outRow = outRow + 1

    ' Copy data and insert blank rows
    For i = 2 To numRows
        For j = 1 To numCols
            wsChartData.Cells(outRow, j).Value = rngData.Cells(i, j).Value
        Next j
        outRow = outRow + 1
        If i < numRows Then
            wsChartData.Cells(outRow, 1).Value = ""
            outRow = outRow + 1
        End If
    Next i

    ' Define chart data range
    Set chartRange = wsChartData.Range(wsChartData.Cells(1, 1), wsChartData.Cells(outRow - 1, numCols))

    ' Insert chart
    Set chartObj = wsChartData.ChartObjects.Add(Left:=100, Top:=30, Width:=500, Height:=350)
    With chartObj.Chart
        .SetSourceData Source:=chartRange
        .ChartType = xlColumnStacked
        .HasTitle = True
        .ChartTitle.Text = "Stacked Clustered Column Chart"
        .Legend.Position = xlLegendPositionRight
        .ChartGroups(1).GapWidth = 0
    End With

    MsgBox "Chart generated successfully.", vbInformation, "KutoolsforExcel"
End Sub

步骤 3：按 Alt + F8 打开宏对话框。选择 CreateStackedClusteredChart 并点击 运行。

步骤 4：当提示时，选择您的原始数据集（包含标题）。宏将生成一个带有插入空行的新工作表并自动创建堆积簇状柱形图。

📝 提示：

• 确保您的原始表格具有统一的列标题和格式。
• 可以在数据集更新时随时重新运行宏——无需重复手动步骤。

✅ 优点： 节省时间，布局准确，非常适合定期报告。
⚠️ 缺点： 需要启用宏的 Excel 并具备基本的 VBA 知识。

Excel 公式 – 动态重组数据以用于堆积簇状图表

如果您不想使用 VBA 或需要一种解决方案，让原始数据发生变化时可以动态更新图表，那么可以使用公式将源数据重组为适合堆积簇状图表的正确布局。通过利用内置函数如 INDEX、TRANSPOSE 和辅助列，您可以设置一个数据转换区域，以最少的努力始终为图表提供正确的结构。

当您的原始数据定期更新（新的时间段、类别等）并且希望图表在没有手动重组的情况下自动调整时，这种方法尤其实用。主要要求是构建一个“辅助”部分，使用公式提取并安排数据块和空行，这样您的图表源始终保持最新。

以下是您可能如何设置的一个示例：

• 假设您的原始数据在 A1:D7（A1 为左上角标题），结构为 A 列为区域/类别，B、C、D 列为子类别值（例如，Q1、Q2、Q3）。
• 您希望将每个类别显示为一个簇，并使用 Q 值堆积，使用空行分隔簇。

1. 在新工作表或相邻区域中，创建一个辅助结构以提取每个组并插入空行。例如，将第一行数据复制到 E2:G2：

=INDEX($A$2:$D$7,INT((ROW()-2)/2)+1,COLUMN()-4+1)

根据需要向下拖动此公式。为了在组之间插入空行，设置一个 IF 公式以在交替行返回空白（"")：

=IF(ISODD(ROW()), "", INDEX($A$2:$D$7,ROW()/2,COLUMN()-4+1))

结合这些公式与精心设计的引用，输出包括定期空行的重组数据。

2. 一旦您的转换范围完成（包括堆积和簇状），选择这个新范围并按照前面给出的原始方法创建堆积柱形图（插入 > 堆积柱形图）。现在，图表将自动反映您对原始数据表所做的任何更改。

对于大型数据集，使用 OFFSET 函数通常有助于使提取过程更加灵活，或者应用动态命名范围来定义图表源。

优点： 不需要 VBA 或宏，非常适合禁止脚本的环境。
缺点： 大型数据的复杂公式设置，非常大的动态范围可能导致性能滞后。

故障排除：如果您的图表未正确更新，请仔细检查辅助公式中的引用错误或不匹配。确保正确插入了空行，因为这些对于实现“簇状”外观至关重要。

更多相关图表文章：

---

最佳的办公生产力工具

Kutools for Excel 解决您的大部分问题，并将您的生产力提高 80%

• 超级公式栏（轻松编辑多行文本和公式）；阅读布局（轻松读取和编辑大量单元格）；粘贴到筛选区域...
• 合并单元格/行/列并保留数据；拆分单元格内容；合并重复行并求和/平均值... 防止重复单元格；比较区域...
• 选择重复或唯一行；选择空白行（所有单元格为空）；在多个工作簿中进行超级查找和模糊查找；随机选择...
• 精准复制多个单元格而不改变公式引用；自动创建对多个工作表的引用；插入项目符号、复选框等...
• 收藏并快速插入公式、区域、图表和图片；用密码加密单元格；创建邮件列表并发送电子邮件...
• 提取文本，添加文本，按位置删除，删除空格；创建并打印分页小计；在单元格内容和批注之间转换...
• 超级筛选（保存并应用筛选方案到其他工作表）；按月/周/日高级排序，频率等；按粗体、斜体特殊筛选...
• 合并工作簿和工作表；基于关键列汇总表格；将数据分割到多个工作表；批量转换 xls、xlsx 和 PDF...
• 数据透视表按周数、星期几等分组... 用不同颜色显示未锁定、已锁定单元格；高亮显示包含公式的单元格/名称...

了解更多... 全功能 30 天免费试用... 购买... 

---

Office Tab - 为 Office 带来标签式界面，让您的工作更加轻松

• 在 Word、Excel、PowerPoint、Publisher、Access、Visio 和 Project 中启用标签式编辑和阅读。
• 在同一窗口的新标签页中打开和创建多个文档，而不是在新窗口中。
• 将您的生产力提高 50%，每天为您减少数百次鼠标点击！

了解更多... 全功能 30 天免费试用... 购买...