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

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

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

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

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

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

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

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

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

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

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

6. Для более точной настройки диаграммы нажмите Изменить под Горизонтальными метками оси (Категории) в окне Выбор источника данных. В диалоговом окне Метки оси выберите соответствующие метки для корректного выравнивания с вашими данными. Нажмите OK после завершения.

7. Закройте диалоговое окно Выбор источника данных, нажав OK. Ваша диаграмма теперь объединяет ряды данных из нескольких листов.

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?

Применение условного форматирования между листами/книгами