如何在 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 的環境。如果您的圖表結構保持不變但數據經常更新，它特別有效。以下是實現它的步驟：

第一步：按下 Alt + F11 打開 VBA 編輯器。在編輯器中，點擊 插入 > 模塊。

第二步：將以下 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

第三步：按下 Alt + F8 打開宏對話框。選擇 CreateStackedClusteredChart 並點擊 運行。

第四步：當提示時，選擇您的原始數據集（帶有標題）。該宏將生成一個新的工作表並插入空白行，自動創建堆疊簇狀柱形圖。

📝 提示：

• 確保您的原始表格擁有統一的列標題和格式。
• 您可以在數據集更新時隨時重新運行該宏——無需重複手動步驟。

✅ 優勢： 節省時間，準確的佈局，非常適合定期報告。
⚠️ 缺點： 需要啟用宏的 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 天免費試用... 購買...