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