Как создать диаграмму в Excel из нескольких листов
Некоторое время назад мы опубликовали первую часть нашего учебника по диаграммам Excel, в котором содержится подробное руководство для начинающих. И самый первый вопрос, опубликованный в комментариях, был таким: «А как мне создать диаграмму из нескольких вкладок?» Спасибо за этот отличный вопрос, Спенсер!
Действительно, при создании диаграмм в Excel исходные данные не всегда находятся на одном листе. К счастью, Microsoft Excel позволяет отображать данные из двух или более разных листов на одном графике. Подробные шаги следуют ниже.
Как создать диаграмму из нескольких листов в Excel
Предположим, у вас есть несколько листов с данными о доходах за разные годы, и вы хотите построить диаграмму на основе этих данных, чтобы визуализировать общую тенденцию.
1. Создайте диаграмму на основе вашего первого листа
Откройте свой первый рабочий лист Excel, выберите данные, которые вы хотите отобразить на диаграмме, перейдите к Вставлять вкладка > Графики группу и выберите тип диаграммы, которую вы хотите создать. В этом примере мы будем создавать диаграмму Stack Column:
2. Добавьте второй ряд данных с другого листа
Нажмите на только что созданную диаграмму, чтобы активировать Инструменты диаграммы вкладки на ленте Excel, перейдите к Дизайн вкладка (Дизайн диаграммы в Excel 365) и щелкните значок Выберите данные кнопка.
Или нажмите кнопку Фильтры диаграммы кнопка 
справа от графика, а затем щелкните значок Выберите данные… ссылка внизу.
в Выберите источник данных окно, нажмите на Добавлять кнопка.
Теперь мы собираемся добавить вторую серию данных на основе данных, расположенных на другом листе. Это ключевой момент, поэтому внимательно следуйте инструкциям.
Нажав на Добавлять кнопка открывает Редактировать серию диалоговое окно, в котором вы нажимаете кнопку Свернуть диалоговое окно рядом с полем Значения серии.
Редактировать серию диалоговое окно уменьшится до узкого окна выбора диапазона. Щелкните вкладку листа, содержащего другие данные, которые вы хотите включить в диаграмму Excel (вкладка Редактировать серию окно останется на экране при перемещении между листами).
На втором листе выберите столбец или строку данных, которые вы хотите добавить к диаграмме Excel, а затем щелкните значок Развернуть диалоговое окно значок, чтобы вернуться к полноразмерному Редактировать серию окно.
А теперь нажмите кнопку Свернуть диалоговое окно справа от поля Название серии и выберите ячейку, содержащую текст, который вы хотите использовать в качестве имени серии. Нажмите на Развернуть диалоговое окно вернуться к исходному Редактировать серию окно.
Убедитесь, что ссылки в Название серии а также Значение серии поля указаны правильно и щелкните ХОРОШО кнопка.
Как вы видите на снимке экрана выше, мы связали имя серии с ячейкой B1, которая является именем столбца. Вместо имени столбца вы можете ввести собственное имя серии в двойных кавычках, например, =”Второй ряд данных“.
Имена серий будут отображаться в легенде вашей диаграммы, поэтому вы можете потратить пару минут на то, чтобы дать несколько осмысленных и описательных имен для ваших рядов данных.
На этом этапе результат должен выглядеть примерно так:
3. Добавьте больше рядов данных (необязательно)
Если вы хотите отобразить на диаграмме данные из нескольких рабочих листов, повторите процесс, описанный в шаге 2, для каждой серии данных, которую вы хотите добавить. Когда закончите, нажмите кнопку ХОРОШО кнопка на Выберите источник данных диалоговое окно.
В этом примере я добавил 3-й ряд данных, вот как теперь выглядит моя диаграмма Excel:
4. Настройте и улучшите диаграмму (необязательно)
При создании диаграмм в Excel 2013 и 2016 обычно элементы диаграммы, такие как заголовок диаграммы и легенда, добавляются Excel автоматически. Для нашей диаграммы, построенной из нескольких рабочих листов, заголовок и легенда не были добавлены по умолчанию, но мы можем быстро исправить это.
Выберите свой график, нажмите кнопку Элементы диаграммы кнопку (зеленый крестик) в правом верхнем углу и выберите нужные параметры:
Дополнительные параметры настройки, такие как добавление меток данных или изменение способа отображения осей на диаграмме, см. в следующем руководстве: Настройка диаграмм Excel.
Составление диаграммы из сводной таблицы
Продемонстрированное выше решение работает только в том случае, если ваши записи отображаются в одном и том же порядке на всех листах, которые вы хотите отобразить на диаграмме. В противном случае ваш график не будет испорчен.
В этом примере порядок записей (Апельсины, яблоки, Лимоны, Виноград) одинакова во всех 3-х листах. Если вы создаете диаграмму из больших рабочих листов и не уверены в порядке расположения всех элементов, имеет смысл сначала создать сводную таблицу, а затем построить диаграмму на основе этой таблицы. Чтобы вывести совпадающие данные в сводную таблицу, вы можете использовать функцию ВПР или мастер объединения таблиц.
Например, если рабочие листы, обсуждаемые в этом примере, имеют другой порядок элементов, мы могли бы составить сводную таблицу, используя следующую формулу:
=ВПР(A3,’2014′!$A$2:$B$5, 2,ЛОЖЬ)
И получил следующий результат:
А затем, просто выберите сводную таблицу, перейдите к Вставлять вкладка > Графики группу и выберите нужный тип диаграммы.
Изменение диаграммы Excel, построенной из нескольких листов
Создав диаграмму на основе данных с двух или более листов, вы можете понять, что хотите, чтобы она отображалась по-другому. А поскольку создание таких диаграмм не является мгновенным процессом, как построение графика из одного листа в Excel, вы можете отредактировать существующую диаграмму, а не создавать новую с нуля.
В целом параметры настройки для диаграмм Excel на основе нескольких листов такие же, как и для обычных диаграмм Excel. Вы можете использовать Инструменты для работы с диаграммами вкладки на ленте, контекстное меню или кнопки настройки диаграммы в правом верхнем углу диаграммы для изменения основных элементов диаграммы, таких как заголовок диаграммы, заголовки осей, легенда диаграммы, стили диаграммы и т. д. Подробные пошаговые инструкции приведены в разделе Настройка диаграмм Excel.
И если вы хотите изменить ряды данных, нанесенные на диаграмму, есть три способа сделать это:
Редактировать ряды данных с помощью диалогового окна «Выбор источника данных»
Открой Выберите источник данных диалоговое окно (Дизайн вкладка > Выберите данные).
Чтобы изменить ряд данных, нажмите на него, затем нажмите кнопку «Редактировать» и измените Название серии или же Значения серии как мы делали, когда добавление ряда данных на диаграмму.
Чтобы изменить порядок рядов на диаграмме, выберите ряд и используйте стрелки вверх и вниз для перемещения этого ряда вверх или вниз.
Чтобы скрыть ряд данных, просто снимите его флажок в Легендарные записи (серия) список в левой части Выберите источник данных диалог.
Чтобы навсегда удалить определенный ряд данных с диаграммы, выберите этот ряд и нажмите кнопку «Удалить» внизу.
Скрытие или отображение серий с помощью кнопки «Фильтр диаграмм»
Другой способ управления рядом данных, отображаемых на диаграмме Excel, — использовать кнопку «Фильтры диаграммы». . Эта кнопка появляется справа от вашего графика, как только вы нажимаете на нее.
Чтобы скрыть определенные данные, нажмите кнопку Фильтры диаграммы кнопку и снимите отметку с соответствующих серий данных или категорий.
Чтобы изменить ряд данных, щелкните значок Редактировать серию Кнопка справа от названия серии. старый добрый Выберите источник данных Появится диалоговое окно, в котором вы можете внести необходимые изменения. Для Редактировать серию Чтобы появилась кнопка, вам просто нужно навести курсор мыши на название серии. Как только вы это сделаете, соответствующий ряд будет выделен на графике, так что вы четко увидите, какой именно элемент вы собираетесь изменить.
Редактирование ряда данных с помощью формулы
Как вы, наверное, знаете, каждый ряд данных в диаграмме Excel определяется формулой. Например, если вы выберете один из рядов на графике, который мы только что создали, формула ряда будет выглядеть следующим образом:
=SERIES(‘2013′!$B$1,’2013′!$A$2:$A$5,’2013’!$B$2:$B$5,1)
Каждую формулу ряда данных можно разбить на четыре основных элемента:
=СЕРИЯ([Series Name], [X Values], [Y Values], [Plot Order])
Итак, нашу формулу можно интерпретировать следующим образом:
- Название серии (‘2013’!$B$1) берется из ячейки B1 на листе “2013”.
- Значения по горизонтальной оси (‘2013’!$A$2:$A$5) берутся из ячеек A2:A5 на листе “2013”.
- Значения вертикальной оси (‘2013’!$B$2:$B$5) берутся из ячеек B2:B5 на листе “2013”.
- Порядок сюжета (1) указывает, что этот ряд данных стоит первым на диаграмме.
Чтобы изменить определенный ряд данных, выберите его на графике, перейдите в строку формул и внесите необходимые изменения. Конечно, вам нужно быть очень осторожным при редактировании формулы ряда, потому что это может быть подвержено ошибкам, особенно если исходные данные расположены на другом листе, и вы не видите их при редактировании формулы. И все же, если вы чувствуете себя более комфортно с формулами Excel, чем с пользовательскими интерфейсами, вам может понравиться этот способ быстрого внесения небольших изменений в диаграммы Excel.
Это все на сегодня. Я благодарю вас за ваше время и надеюсь увидеть вас в нашем блоге на следующей неделе!