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함수와 보조 열을 활용하면 최소한의 노력으로 차트에 항상 올바른 구조를 제공하는 데이터 변환 영역을 설정할 수 있습니다。

이 방법은 원시 데이터가 정기적으로 업데이트되는 경우(새로운 기간， 범주 등) 특히 실용적이며， 차트를 수동으로 재구성하지 않고도 자동으로 조정되기를 원할 때 유용합니다。 주요 요구사항은 수식을 사용하여 데이터 블록과 빈 행를 가져와 배열하는 ‘헬퍼(helper)’ 섹션을 구성하는 것이며， 이를 통해 차트의 데이터 소스가 항상 최신 상태를 유지합니다。

다음은 이 설정 방법의 예입니다：

• 원본 데이터가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 나 매크로가 필요 없어 스크립팅이 제한된 환경에 이상적입니다。
단점：대용량 데이터의 경우 수식 설정이 복잡하며， 매우 큰 동적 범위에서는 성능 지연이 발생할 수 있습니다。

문제 해결：차트가 올바르게 업데이트되지 않으면 헬퍼 수식 내 참조 오류나 불일치 여부를 다시 확인하세요。 클러스터링된 모양을 구현하려면 빈 행이 정확히 삽입되어야 하므로 이 점을 반드시 확인하세요。

---

관련 차트 문서 더 보기：