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

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

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

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

1. Строим диаграмму на основе данных первого листа

Выделяем данные на первом листе, которые нужно отобразить в диаграмме. Далее открываем кладку Вставка. В группе Диаграммы выбираем нужный тип диаграммы. В нашем примере используется Объемная гистограмма с накоплением.

Объемная гистограмма
Именно объемная гистограмма с накоплением является самым популярным типом, используемых диаграмм

2. Вносим данные со второго листа

Выделите созданную диаграмму, чтобы активировать мини панель слева Инструменты диаграммы. Далее выбираем Конструктор и нажимаем на иконку Выбрать данные. 

Можно также нажать кнопку Фильтры диаграммы Как сделать диаграммы в Excel из данных на двух или более листах. Справа, в самом низу появившегося списка кликнуть Выбрать данные. 

В появившемся окне Выбор источника данных перейти по ссылке Добавить.

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

Добавляем данные со второго листа. Это важный момент, поэтому будьте внимательны. 

При нажатии кнопки Добавить, выплывает диалоговое окно Изменение ряда. Рядом с полем Значение нужно выбрать иконку диапазона.  

Как сделать диаграммы в Excel из данных на двух или более листах
Очень важно выбрать правильный диапазон, чтобы диаграмма получилась верной

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

На втором листе необходимо выделить данные, вносимые в диаграмму. Чтобы окно Изменения ряда активировалось, по нему нужно просто кликнуть один раз. 

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

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

Убедитесь, что ссылки, указанные в строках Имя ряда и Значения указаны верно. Нажмите ОК.

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

Как видно из изображения, приложенного выше, имя ряда связано с ячейкой В1, где прописано. Вместо этого, название можно внести в виде текста. Например, второй ряд данных. 

Названия рядов появятся в легенде диаграммы. Поэтому лучше давать им подходящие по смыслу имена. 

На этом этапе создания диаграммы, рабочее окно должно выглядеть так:

Как сделать диаграммы в Excel из данных на двух или более листах
Если у вас что-то не так, как на изображении выше, значит где-то допустили ошибку, и нужно начать сначала

3. При необходимости добавляем еще слои

Если необходимо еще вставить данные в график с других листов Excel, то повторите все действия из второго пункта для всех вкладок. После чего нажимаем ОК в появившемся окне Выбор источника данных.

В примере 3 ряда данных. После всех действий гистограмма выглядит так:

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

4. Настраиваем и улучшаем гистограмму (по желанию)

При работе в версии Excel 2013 и 2016, автоматически при создании гистограммы автоматически добавляются название и легенда. В нашем примере они не были добавлены, поэтому мы сами это сделаем. 

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

Как сделать диаграммы в Excel из данных на двух или более листах
Можно оставить все как есть и не добавлять дополнительные параметры

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

Составляем диаграммы из итоговых данных в таблице

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

В нашем примере, все данные расположены в одинаковых таблицах на всех 3 листах. Если нет уверенности в том, что структура в них одинакова, лучше будет сначала составить итоговую таблицу, на основании имеющихся. Сделать это можно с помощью функции VLOOKUP или Мастера объединения таблиц.

Если бы в нашем примере все таблицы были разные, то формула была бы такой:

=VLOOKUP (A3, ‘2014’!$A$2:$B$5, 2, FALSE)

Получился бы такой результат:

Как сделать диаграммы в Excel из данных на двух или более листах
С итоговой таблицей и работать проще

После этого, просто выбираем полученную таблицу. Во вкладке Вставка находим Диаграммы и выбираем необходимый вид.

Редактируем диаграммы, созданной из данных на нескольких листах

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

Изменить данные, отображаемые на самой диаграмме можно несколькими способами:

  • через меню Выбор источника данных;
  • через Фильтры;
  • посредствам Формулы рядов данных.

Редактируем через меню Выбор источника данных

Чтобы открыть меню Выбор источника данных, необходимо во вкладке Конструктор нажать подменю Выбрать данные.

Для редактирования ряда необходимо:

  • выбрать ряд;
  • кликнуть на вкладку Изменить;
  • изменить Значение или Имя, как делали ранее;

Для изменения порядка рядов значений нужно выделить ряд и перемещать его посредствам специальных стрелок вверх или вниз.

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

Для удаления ряда нужно его просто выделить и кликнуть на кнопку Удалить. Чтобы скрыть ряд, его также нужно выделить и снять флажок в меню Элементы легенды, что стоит в левой части окна. 

Изменяем ряды через Фильтр диаграмм

Все настройки открыть можно, нажав на кнопку фильтра Как сделать диаграммы в Excel из данных на двух или более листах. Она появляется сразу же, как нажимаете на диаграмму. 

Чтобы скрыть данные, просто кликните на Фильтр и снимайте флажки со строк, которых не должно быть в графике. 

Наводим на ряд указатель и появляется кнопка Изменить ряд, кликаем на нее. Выплывет окно Выбор источника данных. В нем производим необходимые настройки. 

Обратите внимание! При наведении мыши на ряд он выделяется для более понятного восприятия.

Как сделать диаграммы в Excel из данных на двух или более листах
Меню изменения данных – просто снимаем флажки и изменяем периоды

Редактируем ряд при помощи формулы

Все ряды в графике определяются формулой. Например, при выборе ряда на нашей диаграмме она будет выглядеть так:

=SERIES(‘2013′!$B$1,’2013′!$A$2:$A$5,’2013’!$B$2:$B$5,1)

Как сделать диаграммы в Excel из данных на двух или более листах
Любые данные в Excel принимают вид формулы

Любая формула имеет 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, может понравиться этот способ, позволяющий быстро вносить небольшие изменения в диаграммы.

Добавить комментарий

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

19 − 6 =

ehhu.ru w0o.ru 4-man.ru Adblock
detector