При создании диаграмм в Excel исходные данные для нее не всегда находятся на одном листе. К счастью, Microsoft Excel предоставляет способ построения данных из двух или более различных листов в одном графике. Ниже приведены подробные инструкции.
- Как создать диаграмму из данных на нескольких листах в Excel
- 1. Строим диаграмму на основе данных первого листа
- 2. Вносим данные со второго листа
- 3. При необходимости добавляем еще слои
- 4. Настраиваем и улучшаем гистограмму (по желанию)
- Составляем диаграммы из итоговых данных в таблице
- Редактируем диаграммы, созданной из данных на нескольких листах
- Редактируем через меню Выбор источника данных
- Изменяем ряды через Фильтр диаграмм
- Редактируем ряд при помощи формулы
Как создать диаграмму из данных на нескольких листах в Excel
Предположим, в одном табличном файле есть несколько листов с данными о доходах за разные годы. Используя эти данные необходимо построить диаграмму, для визуализации общей картины.
1. Строим диаграмму на основе данных первого листа
Выделяем данные на первом листе, которые нужно отобразить в диаграмме. Далее открываем кладку Вставка. В группе Диаграммы выбираем нужный тип диаграммы. В нашем примере используется Объемная гистограмма с накоплением.
2. Вносим данные со второго листа
Выделите созданную диаграмму, чтобы активировать мини панель слева Инструменты диаграммы. Далее выбираем Конструктор и нажимаем на иконку Выбрать данные.
Можно также нажать кнопку Фильтры диаграммы 
. Справа, в самом низу появившегося списка кликнуть Выбрать данные.
В появившемся окне Выбор источника данных перейти по ссылке Добавить.
Добавляем данные со второго листа. Это важный момент, поэтому будьте внимательны.
При нажатии кнопки Добавить, выплывает диалоговое окно Изменение ряда. Рядом с полем Значение нужно выбрать иконку диапазона.
Окно Изменение ряда свернется. Но при переключении на другие листы останется на экране, но не будет активным. Нужно выбрать второй лист, с которого необходимо добавить данные.
На втором листе необходимо выделить данные, вносимые в диаграмму. Чтобы окно Изменения ряда активировалось, по нему нужно просто кликнуть один раз.
Чтобы ячейку с текстом, который будет названием нового ряда, нужно выбрать диапазон данных рядом с иконкой Имя ряда. Сверните окно диапазона, чтобы продолжить работу во вкладке Изменения ряда.
Убедитесь, что ссылки, указанные в строках Имя ряда и Значения указаны верно. Нажмите ОК.
Как видно из изображения, приложенного выше, имя ряда связано с ячейкой В1, где прописано. Вместо этого, название можно внести в виде текста. Например, второй ряд данных.
Названия рядов появятся в легенде диаграммы. Поэтому лучше давать им подходящие по смыслу имена.
На этом этапе создания диаграммы, рабочее окно должно выглядеть так:
3. При необходимости добавляем еще слои
Если необходимо еще вставить данные в график с других листов Excel, то повторите все действия из второго пункта для всех вкладок. После чего нажимаем ОК в появившемся окне Выбор источника данных.
В примере 3 ряда данных. После всех действий гистограмма выглядит так:
4. Настраиваем и улучшаем гистограмму (по желанию)
При работе в версии Excel 2013 и 2016, автоматически при создании гистограммы автоматически добавляются название и легенда. В нашем примере они не были добавлены, поэтому мы сами это сделаем.
Выделяем график. В появившемся меню Элементы диаграммы нажимаем зеленый крестик и выделяем все элементы, которые необходимо внести в гистограмму:
О настройке иных параметрах, таких как отображение подписи данных и формат осей, рассказано в отдельной публикации.
Составляем диаграммы из итоговых данных в таблице
Метод составления диаграммы, показанный выше, работает только в том случае, если данные на всех вкладках документа занесены в одну и ту же строку или столбец. В остальных случаях диаграмма будет неразборчива.
В нашем примере, все данные расположены в одинаковых таблицах на всех 3 листах. Если нет уверенности в том, что структура в них одинакова, лучше будет сначала составить итоговую таблицу, на основании имеющихся. Сделать это можно с помощью функции VLOOKUP или Мастера объединения таблиц.
Если бы в нашем примере все таблицы были разные, то формула была бы такой:
=VLOOKUP (A3, ‘2014’!$A$2:$B$5, 2, FALSE)
Получился бы такой результат:
После этого, просто выбираем полученную таблицу. Во вкладке Вставка находим Диаграммы и выбираем необходимый вид.
Редактируем диаграммы, созданной из данных на нескольких листах
Бывает и такое, что после построения графика, требуется изменение данных. В этом случае, проще отредактировать уже имеющуюся, нежели создавать новую диаграмму. Делается это через меню Работа с диаграммами, которое ничем не отличается для графиков, построенных из данных одной таблицы. Настройка основных элементов графика показано в отдельной публикации.
Изменить данные, отображаемые на самой диаграмме можно несколькими способами:
- через меню Выбор источника данных;
- через Фильтры;
- посредствам Формулы рядов данных.
Редактируем через меню Выбор источника данных
Чтобы открыть меню Выбор источника данных, необходимо во вкладке Конструктор нажать подменю Выбрать данные.
Для редактирования ряда необходимо:
- выбрать ряд;
- кликнуть на вкладку Изменить;
- изменить Значение или Имя, как делали ранее;
Для изменения порядка рядов значений нужно выделить ряд и перемещать его посредствам специальных стрелок вверх или вниз.
Для удаления ряда нужно его просто выделить и кликнуть на кнопку Удалить. Чтобы скрыть ряд, его также нужно выделить и снять флажок в меню Элементы легенды, что стоит в левой части окна.
Изменяем ряды через Фильтр диаграмм
Все настройки открыть можно, нажав на кнопку фильтра . Она появляется сразу же, как нажимаете на диаграмму.
Чтобы скрыть данные, просто кликните на Фильтр и снимайте флажки со строк, которых не должно быть в графике.
Наводим на ряд указатель и появляется кнопка Изменить ряд, кликаем на нее. Выплывет окно Выбор источника данных. В нем производим необходимые настройки.
Обратите внимание! При наведении мыши на ряд он выделяется для более понятного восприятия.
Редактируем ряд при помощи формулы
Все ряды в графике определяются формулой. Например, при выборе ряда на нашей диаграмме она будет выглядеть так:
=SERIES(‘2013′!$B$1,’2013′!$A$2:$A$5,’2013’!$B$2:$B$5,1)
Любая формула имеет 4 основных составляющих:
=РЯД([Название ряда], [значения x], [значения y], номер ряда)
Наша формула в примере имеет следующее объяснение:
- Название ряда (‘2013’!$B$1) взято из ячейки B1 на листе 2013.
- Значение строк (‘2013’!$A$2:$A$5) взяты из ячеек A2:A5 на листе 2013.
- Значение столбцов (‘2013’!$B$2:$B$5) взяты из ячеек B2:B5 на листе 2013.
- Число (1) означает то, что выделенный ряд имеет первое место в графике.
Чтобы изменить определенный ряд данных, выделите его на диаграмме, перейдите в строку формул и внесите необходимые изменения. Конечно, вы должны быть очень осторожны при редактировании формулы ряда, потому что это может привести к ошибкам, особенно если исходные данные находятся на другом листе, и не можете увидеть их при редактировании формулы. И все же, если вы опытный пользователь Excel, может понравиться этот способ, позволяющий быстро вносить небольшие изменения в диаграммы.
