Excel에서 여러 워크시트를 통합하여 차트를 만드는 방법은 무엇입니까?

여러 워크시트에서 동일한 구조의 데이터로 구성된 시리즈를 비교하려는 경우, 예를 들어 네 달 동안 지역별 판매량을 별도의 탭으로 정리할 때, 다중 데이터 시리즈를 포함한 통합된 열 차트를 생성하면 효과적인 시각적 비교를 제공합니다.

이 방법은 수동 선택이 필요하지만 간단하며 복잡한 수식이나 매크로가 필요하지 않습니다. 워크시트의 레이아웃이 동일하고 소스 시트에서 바로 차트를 작성하려는 경우에 가장 적합합니다.

차트를 설정하기 위해 다음 단계를 따르세요:

1. 클릭하세요 삽입 > 열 삽입 차트 (또는 열) > 클러스터형 열). 그러면 워크시트에 빈 차트가 열립니다.

2. 새로 삽입된 빈 차트를 마우스 오른쪽 버튼으로 클릭하고 데이터 선택 컨텍스트 메뉴에서 선택하세요.

3. 데이터 원본 선택 대화 상자에서 추가 버튼을 클릭하여 새로운 데이터 시리즈를 추가하기 시작하세요.

4. 시리즈 편집 대화 상자에서 시리즈 이름을 입력하고 관련 워크시트로 이동하여 필요한 데이터 범위를 선택하여 시리즈 값을 지정합니다. 참조가 정확한지 다시 확인하세요. 참조 오류는 차트가 잘못된 데이터나 #REF 같은 오류를 표시하게 할 수 있습니다. 확인을 클릭하세요.

팁: 시리즈 값 상자에 다른 워크시트의 데이터를 참조하려면, 대상 시트로 전환한 후 원하는 범위를 선택하세요. Excel은 자동으로 참조에 워크시트 이름을 포함합니다.

5. 차트에 포함하려는 각 워크시트에 대해 3단계와 4단계를 반복합니다. 모든 시리즈를 추가한 후, 대화 상자에서 범례 항목 (시리즈) 아래에 목록으로 표시됩니다.

참고: 항상 각 시트에서 동일한 셀 범위 구조를 선택하세요. 불일치는 차트를 혼란스럽게 하거나 정렬 문제가 발생할 수 있습니다.

6. 차트를 세부 조정하려면, Select Data Source 창에서 가로 (범주) 축 레이블 아래의 편집을 클릭하세요. Axis Labels 대화 상자에서 관련 레이블을 선택하여 데이터와 올바르게 정렬하세요. 완료되면 확인을 클릭하세요.

7. 확인을 클릭하여 Select Data Source 대화 상자를 닫습니다. 이제 차트는 여러 워크시트에서 데이터 시리즈를 결합합니다.

8. (선택사항) 시각적 명확성을 개선하려면 차트를 선택한 후 디자인 > 차트 요소 추가 > 범례그리고 옵션 (예: 범례 > 하단)을 선택하여 각 시리즈를 식별하는 범례를 표시하세요.

이 접근법은 정적 데이터셋에 이상적입니다. 그러나 워크시트 레이아웃이 다르거나 시트 간 데이터 범위가 변경되는 경우, 이러한 차이를 먼저 해결해야 합니다. 데이터가 자주 업데이트되거나 자동화된 통합이 필요한 경우 아래의 다른 솔루션을 고려하여 더 효율적으로 처리하세요.

다음은 네 개의 서로 다른 워크시트에서 데이터 시리즈를 시각적으로 비교하는 클러스터형 열 차트입니다:

전체 시리즈 대신 여러 워크시트에서 개별 데이터 포인트를 선택하여 차트를 구성하려는 경우, 대상 셀을 요약 시트에 수집한 다음 이를 함께 차트로 작성할 수 있습니다. 이것은 여러 부서 시트에서 '총계' 값을 비교하려는 일반적인 경우입니다.

Kutools for Excel의 워크시트 참조 자동 증가는 여러 시트에서 특정 값을 하나의 요약 시트로 추출하는 효율적인 방법을 제공하며, 소스 값이 시간이 지남에 따라 변할 수 있는 동적 데이터에서 특히 유용합니다.

데이터 포인트를 수집하고 차트를 만드는 방법은 다음과 같습니다:

1. Sheet Tab 바에서 새로 만들기 버튼을 클릭하여 또는 통합할 새 워크시트를 생성하세요.

2. 이 새 워크시트에서, 다른 시트에서 데이터를 추출하려는 셀을 선택한 후 Kutools > 추가 (< 수식 그룹) > 워크시트 참조 자동 증가.

3. Fill Worksheets References 대화 상자에서 다음을 수행하세요:

• 채우기 순서 드롭다운에서 열로 채우고 행으로 채우기를 선택하세요. 이렇게 하면 반환된 값이 세로 목록으로 정리됩니다.
• 참조하려는 셀을 포함하는 워크시트를 선택하여 관련 소스 탭만 선택되었는지 확인하세요.
• 클릭 범위 채우기 값을 가져온 후 닫기 완료되면 클릭하세요.
  

팁: 다양한 종류의 데이터를 수집해야 하는 경우 이 단계를 반복해서 수행할 수 있으며, 요약 시트에서 필요에 따라 별도의 열 또는 행으로 배열할 수 있습니다.

이러한 단계 이후, 각 워크시트에서 선택한 데이터가 새 시트에 깔끔하게 정리되어 있는 것을 볼 수 있습니다.

4. 통합된 데이터 포인트를 강조 표시한 다음, 평소처럼 차트를 만드세요: 삽입 > 열 삽입 차트 (또는 열) > 클러스터형 열.

이제, 각각의 데이터 포인트가 다른 워크시트에서 온 클러스터형 열 차트를 통해 시각적으로 비교할 수 있게 됩니다.

팁:

• 이 방법은 소스 데이터가 변경될 때 링크가 자동으로 새로 고쳐지는 동적 업데이트 차트에 가장 적합합니다 (직접 참조 또는 수식을 사용하는 경우).
• #REF! 오류가 발생하면 소스 워크시트 이름을 확인하세요. 이름이 변경되거나 삭제된 시트는 참조를 깨뜨릴 수 있습니다.

데모: Excel에서 여러 워크시트에서 차트 만들기

특히 많은 수의 워크시트를 처리하거나 정기적인 업데이트가 필요한 경우, 더욱 자동화되고 확장 가능한 솔루션을 찾는 사용자를 위한 VBA 매크로는 여러 시트에서 데이터를 효율적으로 수집하고 즉시 차트를 생성할 수 있습니다. VBA는 데이터 통합 프로세스를 커스터마이징할 수 있어 고급 사용자나 자주 보고서를 업데이트하는 누구에게나 적합합니다.

장점: 자동화, 사용자 정의 요구에 대한 높은 유연성, 많은 워크시트와 잘 작동합니다.
잠재적 단점: 매크로 실행 권한이 필요하며 일부 사용자는 VBA 문법이나 문제 해결에 익숙하지 않을 수 있습니다.

다음 예제를 사용하여 모든 워크시트에서 특정 셀 (예: B2)을 요약 시트에 결합한 다음 결합된 데이터에서 클러스터형 열 차트를 생성할 수 있습니다:

1. 개발 도구 > Visual Basic을 클릭하여 Microsoft Visual Basic for Applications 창을 엽니다. 삽입 > 모듈을 클릭한 다음 아래 코드를 모듈에 붙여넣으세요:

Sub CombineDataAndChart()
    Dim ws As Worksheet
    Dim summarySheet As Worksheet
    Dim lastRow As Long
    Dim destRow As Long
    Dim wsCount As Integer
    Dim i As Integer
    Dim rng As Range
    
    On Error Resume Next
    
    ' Create summary sheet or clear previous one
    Application.DisplayAlerts = False
    For Each ws In Worksheets
        If ws.Name = "SummaryChartData" Then
            ws.Delete
            Exit For
        End If
    Next
    Application.DisplayAlerts = True
    
    Set summarySheet = Worksheets.Add
    summarySheet.Name = "SummaryChartData"
    
    destRow = 1
    
    ' Set header
    summarySheet.Cells(destRow, 1).Value = "Sheet"
    summarySheet.Cells(destRow, 2).Value = "Value"
    destRow = destRow + 1
    
    ' Collect data from all sheets (change range as needed)
    For Each ws In Worksheets
        If ws.Name <> "SummaryChartData" Then
            summarySheet.Cells(destRow, 1).Value = ws.Name
            summarySheet.Cells(destRow, 2).Value = ws.Range("B2").Value ' Modify "B2" as needed
            destRow = destRow + 1
        End If
    Next
    
    ' Create chart
    Dim chartObj As ChartObject
    Set chartObj = summarySheet.ChartObjects.Add(Left:=250, Width:=350, Top:=20, Height:=250)
    
    chartObj.Chart.ChartType = xlColumnClustered
    chartObj.Chart.SetSourceData Source:=summarySheet.Range("A1:B" & destRow - 1)
    chartObj.Chart.HasTitle = True
    chartObj.Chart.ChartTitle.Text = "Combined Data from All Sheets"
    
    xTitleId = "KutoolsforExcel"
End Sub

2. 클릭하세요 VBA 편집기에서 실행 버튼을 눌러 코드를 실행하세요. 매크로는 자동으로 요약 시트 ("SummaryChartData")를 생성하고, 요약 시트를 제외한 모든 워크시트에서 데이터 (이 예에서는 셀 B2의 값)를 수집하여 컴파일된 데이터를 기반으로 차트를 작성합니다.

참고:

• 각 워크시트에서 다른 셀을 추출하려면 ws.Range("B2") 참조를 그에 맞게 조정하세요.
• 더 많은 열이나 유연한 범위를 포함하려면 코드 논리를 확장하거나 열 인덱스를 반복하는 방법을 사용할 수 있습니다.
• 워크시트 이름 충돌이 발생하면, 매크로는 필요에 따라 요약 시트를 자동으로 덮어쓰거나 재생성합니다.
• 매크로를 실행하기 전에 Excel 설정이 매크로 실행을 허용하는지 확인하세요.

오류가 발생하면 워크시트 이름, 지정된 셀 참조를 다시 확인하고, 어떤 워크시트도 보호되거나 숨겨지지 않았는지 확인하세요.

Excel에서 여러 워크시트에 걸쳐 셀 미러링/링크하기

Excel에서 여러 워크시트에 걸쳐 이름이 지정된 범위 정의하기

Excel에서 여러 워크시트에서 데이터 복사하기/붙여넣기

워크시트/워크북 간 조건부 서식 적용하기