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일 무료 평가판... 구매...