Как создать диаграмму в Excel на основе данных из нескольких листов?

Когда ваша цель — сравнить ряды данных по нескольким рабочим листам с одинаковой структурой, например продажи по регионам за четыре разных месяца, размещённые на отдельных вкладках, сводная круговая диаграмма с несколькими рядами данных обеспечит эффективное визуальное сравнение.

Этот метод требует ручного выбора, но является простым и не нуждается в сложных формулах или макросах. Он лучше всего работает, когда ваши рабочие листы имеют идентичную структуру, а диаграмму необходимо составить непосредственно из исходных листов.

Выполните следующие шаги для настройки диаграммы:

1. Нажмите Вставка > Вставить круговую диаграмму(или)гистограмму) > Сгруппированная гистограмма. На листе появится пустая диаграмма.

2. Щелкните правой кнопкой мыши по вставленной пустой диаграмме и выберите в контекстном меню Выбрать данные.

3. В диалоговом окне «Источник данных: Выбрать данные» нажмите кнопку Добавить, чтобы начать добавление нового ряда данных.

4. В диалоговом окне «Изменение ряда» укажите имя серии и задайте значения ряда: перейдите на нужный лист и выделите требуемый диапазон данных. Обязательно проверьте корректность ссылок — ошибки могут привести к отображению неверных данных или появлению ошибок вроде #ССЫЛ!. Нажмите ОК, чтобы подтвердить.

Совет: чтобы сослаться на данные с другого рабочего листа в поле «Значения ряда», перейдите на нужный лист и выделите требуемый диапазон — Excel автоматически добавит имя листа в ссылку.

5. Повторите шаги 3 и 4 для каждого рабочего листа, который вы хотите включить в диаграмму. После добавления всех рядов они появятся в списке под заголовком Диапазон имени ряда в диалоговом окне.

Примечание: убедитесь, что на каждом листе вы всегда выбираете одинаковую структуру диапазона ячеек — несоответствия могут исказить диаграмму или вызвать проблемы с выравниванием.

6. Чтобы точно настроить диаграмму, в окне «Источник данных (Выбрать данные)» нажмите Изменить в разделе Горизонтальные метки оси. В диалоговом окне «Диапазон меток оси» выберите нужные метки, чтобы корректно сопоставить их с вашими данными. Нажмите ОК, когда завершите.

7. Закройте диалоговое окно «Источник данных — Выбрать данные», нажав ОК. Теперь ваша диаграмма объединяет ряды данных из нескольких рабочих листов.

8. (Необязательно) Чтобы улучшить наглядность, выделите диаграмму и перейдите в меню Конструктор > Добавить элемент диаграммы > Легендаи выберите подходящий вариант (например,)Легенда > Внизу), чтобы отобразить легенду, идентифицирующую каждый ряд.

Этот подход идеально подходит для статических наборов данных. Однако если структура ваших рабочих листов или диапазоны данных различаются, сначала устраните эти несоответствия. Если данные часто обновляются или требуется автоматическая консолидация, воспользуйтесь другими решениями ниже для повышения эффективности.

Вот получившаяся Диаграмма столбцов с группировкой, которая визуально сравнивает ряды данных, взятые с четырёх разных рабочих листов:

В случаях, когда вы хотите построить диаграмму, выбирая отдельные точки данных с нескольких рабочих листов вместо целых рядов, сначала соберите целевые ячейки на сводном листе, а затем постройте диаграмму. Такой подход часто используется, когда нужно сравнить один показатель, например значение «Итого», из нескольких ведомственных листов.

Kutools для Excel предлагает функцию Автоматическое инкрементирование ссылок на листе, которая эффективно извлекает определённые значения с нескольких листов на один сводный лист и особенно полезна для динамических данных, значения которых со временем могут изменяться.

Вот как собрать точки данных и создать диаграмму:

1. На листе Панель вкладок нажмите кнопку Создать или , чтобы создать новый рабочий лист для консолидации.

2. На новом листе выберите ячейку, в которую вы хотите извлечь данные с других листов. Затем перейдите в меню Kutools > Дополнительно(в группе)Формулы) > Автоматическое инкрементирование ссылок на листе.

3. В диалоговом окне «Заполнить ссылки на листе» выполните следующие действия:

• Выберите Заполнить по столбцу, затем по строке из раскрывающегося списка Порядок заполнения. Это организует возвращаемое значение в вертикальный список.
• Отметьте листы, содержащие ячейки, на которые вы хотите сослаться, и убедитесь, что выбраны только нужные исходные вкладки.
• Нажмите Заполнить диапазон, чтобы извлечь значения, затем нажмите Закрыть после завершения.
  

Совет: вы можете повторять этот шаг сколько угодно раз, если вам нужно собрать разные типы данных, размещая их в отдельных столбцах или строках на сводном листе — по мере необходимости.

После выполнения этих шагов выбранные данные с каждого рабочего листа аккуратно организуются на новом листе.

4. Выделите сводные точки данных и создайте диаграмму обычным способом: Вставка > Вставить круговую диаграмму(или)гистограмму) > Сгруппированная гистограмма.

Теперь вы создали диаграмму с группированными столбцами, которая визуально сравнивает точки «Выбрать данные», взятые с отдельных рабочих листов.

Советы:

• Этот метод идеально подходит для динамического обновления диаграмм: ссылки автоматически обновляются при изменении исходных данных (при условии использования прямых ссылок или формул).
• Проверьте исходное имя листа при возникновении ошибок #ССЫЛ!, поскольку переименованные или удалённые листы нарушают ссылки.

Демонстрация: создание диаграммы на основе нескольких рабочих листов в Excel

Для пользователей, которым нужно более автоматизированное и масштабируемое решение — особенно при работе с большим количеством листов или при регулярной необходимости обновлять данные — макрос VBA эффективно собирает информацию с нескольких листов и мгновенно создаёт диаграмму. Благодаря возможности настройки процесса консолидации данных, VBA идеально подходит как для опытных пользователей, так и для тех, кто часто обновляет свои отчёты.

Преимущества: Автоматизация, высокая гибкость для решения индивидуальных задач и отличная работа с большим количеством рабочих листов.
Возможные недостатки: Требуется разрешение на запуск макросов, а также то, что некоторые пользователи могут быть не знакомы с синтаксисом VBA или устранением неполадок.

Вы можете использовать приведённый ниже пример для объединения конкретной ячейки (например, B2) со всех рабочих листов на новом сводном листе, а затем создать Диаграмма столбцов с группировкой на основе объединённых данных:

1. Нажмите Средства разработчика > Visual Basic, чтобы открыть окно Microsoft Visual Basic для приложений. Затем выберите Вставка > Модуль и вставьте приведённый ниже код в модуль:

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?

Применение Использовать условное форматирование ко всем листам или книгам