如何在 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 或巨集，非常適合禁止使用腳本的環境。
缺點：處理大型資料時需設定複雜公式，且在應對極大動態範圍時可能出現效能延遲。

疑難排解：若圖表未正確更新，請仔細檢查輔助公式是否存在參照錯誤或不一致之處。務必確認已正確插入空白列，因為這對於實現「群集」外觀至關重要！

---

更多相關圖表文章：