Если нужно на одной диаграмме изобразить разные ряды данных (по величине, по типу), то добавляется вспомогательная ось. Ее масштаб соответствует значениям связанного ряда. Excel позволяет добавить ось значений (вертикальную) и категорий (горизонтальную). Последний вариант используется при построении точечных и пузырьковых диаграмм.

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

Диаграмма с двумя вертикальными осями

Чтобы построить диаграмму с двумя вертикальными осями для примера построим таблицу следующего вида:

Столбцы В и С имеют ряды разного типа: рубли (числовые значения) и проценты. На основе таблицы построим простой график с маркерами:

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

Как в диаграмме сделать две оси:

1. Щелкнуть по графику, который отображает долю продаж в процентах. Нажать правую кнопку мыши. Выбрать инструмент «Формат ряда данных».
2. В открывшемся меню перейти на вкладку «Параметры ряда». Назначить отображение данных этого ряда «По вспомогательной оси».
3. В результате проделанной работы в области построения появляется вторая вертикаль значений с процентами. Масштаб соответствует значениям столбца «Доля».
4. Чтобы «разделить» визуальное отображение двух рядов данных, изменим тип графика. Необходимо щелкнуть по области построения, чтобы активизировалась вкладка «Конструктор». Нажать кнопку «Изменить тип диаграммы».
5. Откроется окно с различными шаблонами. Выберем для одного из графиков гистограмму с группировкой.

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

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

Теперь в параметрах редактирования диаграмм появилась возможность работать со вспомогательной осью:

Чтобы удалить вспомогательную вертикальную ось, можно выделить ее, щелкнуть правой кнопкой мыши – нажать «Удалить».

Еще один способ. Перейти на вкладку «Макет». Для изменения форматирования и разметки каждой оси выбрать инструмент «Оси». Далее – «Вспомогательная вертикальная» — «Не показывать».

Вспомогательная горизонтальная ось

Чтобы добавить вторую горизонтальную ось (категорий), в области построения уже должна отображаться вспомогательная.

Порядок добавления:

1. Щелкнуть по графику, который построен по вертикальной вспомогательной оси. Перейти на вкладку «Макет» и открыть группу «Оси».
2. Раскрыть пункт «Вспомогательная горизонтальная». По умолчанию при добавлении второй оси значений Excel не показывает вторую ось категорий. Но пункт становится доступным для пользователя. Раскрыв его, выбираем подходящий вариант отображения.

На рисунке это будет выглядеть так:

Удалить дополнительную горизонталь значений можно теми же способами, что и вспомогательную вертикальную.

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

Скачать пример диаграммы с двумя осями

Для дальнейшего применения построенную диаграмму с двумя осями можно сохранить в виде шаблона. Для этого нужно щелкнуть по области построения. На вкладке «Конструктор» нажать кнопку «Сохранить как шаблон».

Далее программа предложит место сохранения шаблона. Пользователь должен назначить имя и нажать ОК.

Метод 1 Добавление второй оси Y

1. 

   Создайте свой график в Excel так, будто у вас одинаковые единицы измерения.

2. 

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

3. 

   Выберите «Формат ряда данных».

4. 

   Под «Ось» установите переключатель на «Вспомогательная ось».

5. 

   Выберите OK. Теперь вы должны увидеть на графике вторую ось Y.

Метод 2 Изменение типа диаграммы второго набора данных

1. 

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

2. 

   Выберите «Изменить тип диаграммы».

3. 

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

Советы

• Вы можете сначала опробовать этот способ на простом графике.
• Вы можете создать три или более Y осей в Excel с помощью EZplot или Multy_Y от OfficeExpander.com. Сначала испробуйте демонстрационную версию, чтобы быть уверенным, что это то, что вы хотите.

Информация о статье

Категории: Microsoft Excel

На других языках:

English: Add a Second Y Axis to a Graph in Microsoft Excel, Español: agregar un eje Y secundario en una gráfica de Excel, Italiano: Aggiungere un Secondo Asse Y in un Grafico di Microsoft Excel, Português: Inserir um Segundo Eixo Y num Gráfico do Microsoft Excel, Deutsch: Einfügen einer zweiten Y Achse in einem Diagramm in Microsoft Excel, Bahasa Indonesia: Menyisipkan Sumbu Y Kedua pada Grafik Excel, Français: ajouter un 2e axe Y dans un graphique d’Excel, Nederlands: Een tweede y as toevoegen aan een grafiek in Excel, 中文: 给Excel图表再加一条Y轴, ไทย: สร้างแกน Y แกนที่ 2 ในกราฟของ Microsoft Excel, العربية: إضافة محور ص جديد إلى رسم بياني في برنامج مايكروسوفت إكسل, Tiếng Việt: Thêm trục Y thứ hai vào biểu đồ Microsoft Excel

• Печать
• Править
• Написать благодарственное письмо авторам

Эту страницу просматривали 77 692 раз.

Была ли эта статья полезной?

Возникало ли у Вас когда-либо желание построить на одной диаграмме графики по двум различным (хотя и связанным) наборам данных? Возможно, Вы хотели видеть количество лидов, одновременно пришедших из нескольких каналов, и уровень конверсии этих каналов. Поместить эти два набора данных на одну диаграмму было бы очень полезно для выяснения закономерностей и определения трендов, наполняющих воронку.

Лид (lead, целевой лид) — потенциальный клиент, тем или иным образом отреагировавший на маркетинговую коммуникацию. Термином лид стало принято обозначать потенциального покупателя, контакт с ним, полученный для последующей менеджерской работы с клиентом.

Но есть проблема. Эти два набора данных имеют совершенно разные размерности оси Y (количество лидов и уровень конверсии) – в итоге график получается очень сомнительный.

К счастью, есть простое решение. Вам потребуется то, что называют вспомогательной осью: с её помощью можно использовать одну общую ось Х и две оси Y различной размерности. Чтобы помочь Вам с решением этой досадной задачи, мы покажем, как добавить вспомогательную ось к диаграмме в Excel на Mac и PC, а также в таблицах Google Doc.

Как добавить вспомогательную ось к диаграмме Excel на Mac

Шаг 1: Вводим данные в таблицу

Пусть в строке 1 будут находиться подписи оси Х, а в строках 2 и 3 – подписи для двух осей Y.

В данном примере: в первой строке указаны размеры обуви, во второй строке – количество проданной обуви, а в третьей – процент проданной обуви.

Шаг 2: Создаём диаграмму из имеющихся данных

Выделяем данные, которые нужно показать на диаграмме. Затем в меню нажимаем Диаграммы (Charts), выбираем Гистограмма (Column) и кликаем вариант Гистограмма с группировкой (Clustered Column) вверху слева.

Диаграмма появится чуть ниже набора данных.

Шаг 3: Добавляем вспомогательную ось

Теперь построим график по вспомогательной оси для данных из строки Percent of Nike Shoes Sold. Выделяем диаграмму, рядом с меню Диаграммы (Charts) должен появиться сиреневый ярлык – Макет диаграммы (Chart Layout). Жмем на него.

В левом верхнем углу в разделе Текущий фрагмент (Current selection) открываем выпадающий список и выбираем Percent of Nike Shoes Sold или любой другой ряд данных, который нужно отложить по вспомогательной оси.

Когда выбран нужный ряд данных, нажимаем кнопку Формат выделенного (Format Selection) сразу под выпадающим списком. Появится диалоговое окно, в котором можно выбрать вспомогательную ось для построения графика. Если раздел Оси (Axis) не открылся автоматически, кликните по нему в меню слева и выберите вариант Вспомогательная ось (Secondary Axis). После этого нажмите ОК.

Шаг 4: Настраиваем форматирование

Теперь график Percent of Nike Shoes Sold перекрывает график Number of Shoes Sold. Давайте исправим это.

Выделяем график и снова кликаем зелёную вкладку Диаграммы (Charts). Слева вверху нажимаем График > График (Line > Line).

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

Как добавить вспомогательную ось к диаграмме Excel в Windows

Шаг 1: Добавляем данные на лист

Пусть в строке 1 будут находиться подписи оси Х, а в строках 2 и 3 – подписи для двух осей Y.

Шаг 2: Создаём диаграмму из имеющихся данных

Выделяем данные, которые нужно показать на диаграмме. Затем открываем вкладку Вставка (Insert) и находим раздел Диаграммы (Charts). Кликаем по маленькой иконке с изображением вертикальных линий. Откроется диалоговое окно с несколькими вариантами построения графика на выбор. Выбираем самый первый: Гистограмма с группировкой (Clustered Column).

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

Шаг 3: Добавляем вспомогательную ось

Теперь отобразим данные Percent of Nike Shoes Sold по вспомогательной оси. После того, как была создана диаграмма, на Ленте появилось две дополнительные вкладки: Конструктор (Design) и Формат (Format). Открываем вкладку Формат (Format). В левой части вкладки в разделе Текущий фрагмент (Current Selection) раскрываем выпадающий список Область построения (Chart Area). Выбираем ряд с именем Percent of Nike Shoes Sold – или любой другой ряд, который должен быть построен по вспомогательной оси. Затем нажимаем кнопку Формат выделенного (Format Selection), которая находится сразу под раскрывающимся списком.

В правой части окна появится панель Формат с открытым разделом Параметры ряда (Series Options). Отметьте флажком опцию По вспомогательной оси (Secondary Axis).

Шаг 4: Настраиваем форматирование

Обратите внимание, что график ряда данных Percent of Nike Shoes Sold теперь перекрывает ряд Number of Shoes Sold. Давайте исправим это.

Откройте вкладку Конструктор (Design) и нажмите Изменить тип диаграммы (Change Chart Type).

Появится диалоговое окно. В нижней части окна рядом с Percent of Nike Shoes Sold кликните раскрывающийся список и выберите вариант построения График (Line).

Убедитесь, что рядом с этим выпадающим списком галочкой отмечен параметр Вспомогательная ось (Secondary Axis).

Вуаля! Диаграмма построена!

Как добавить вспомогательную ось в таблицах Google Doc

Шаг 1: Добавляем данные на лист

Пусть в строке 1 будут находиться подписи оси Х, а в строках 2 и 3 – подписи для двух осей Y.

Шаг 2: Создаём диаграмму из имеющихся данных

Выделите данные. Затем зайдите в раздел меню Вставка (Insert) и в открывшемся списке нажмите Диаграмма (Chart) – этот пункт находится ближе к концу списка. Появится вот такое диалоговое окно:

Шаг 3: Добавляем вспомогательную ось

На вкладке Рекомендуем (Start) выберите комбинированную диаграмму, показывающую гистограмму с наложенным на неё линейным графиком. Если этого варианта нет на начальном экране, то нажав ссылку Дополнительно (More) рядом с заголовком Рекомендуем (Recommended charts) выбираем его из полного списка вариантов.

Параметр Заголовки – значения столбца А (Use column A as headers) должен быть отмечен галочкой. Если диаграмма выглядит неправильно, попробуйте изменить параметр Строки / столбцы (Switch rows / columns).

Шаг 4: Настраиваем форматирование

Настало время привести в порядок форматирование. На вкладке Настройка (Customize) пролистываем вниз до раздела Серии (Series). Раскрываем выпадающий список и выбираем имя вспомогательной оси, в нашем случае это Percent of Nike Shoes Sold. В выпадающем списке Ось (Axis) измените Левая ось (Left) на Правая ось (Right). Теперь вспомогательная ось будет отчётливо видна на диаграмме. Далее нажимаем кнопку Вставить (Insert), чтобы разместить диаграмму на листе.

Вуаля! Диаграмма готова.

А какие данные строите по вспомогательной оси Вы?

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Рубрика Excel

Также статьи о графиках в Экселе:

• Как сделать диаграмму в Эксель?
• Как сделать круговую диаграмму в Экселе?
• Как построить график в Экселе?
• Построение точечной диаграммы в Excel

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

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

Выделяем два столбца результатов расчетов и строим обычную гистограмму.

Теперь попробуем добавить еще одну гистограмму к имеющимся с таким же количеством результатов расчетов. Для добавления графика в Экселе делаем активным имеющийся график, выделив его, и на появившейся вкладке «Конструктор» выбираем «Выбрать данные». В появившемся окошке в разделе «Элементы легенды» нажимаем добавить, и указываем ячейки «Имя ряда:» и «Значения:» на листе, в качестве которых будут значения расчета функции «j».

Теперь посмотрим, как будет выглядеть наша диаграмма, если мы к имеющимся гистограммам добавим еще одну, у которой количество значений почти в два раза больше. Добавим к графику значения функции «k».

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

Если изменить тип диаграммы с гистограммы на обычный график, результат получится в нашем случае более наглядным.

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

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

Для добавления еще одного графика выделяем имеющиеся, и на вкладке «Конструктор» нажимаем «Выбрать данные».

В новом окошке в графе «Элементы легенды» нажимаем «Добавить», и указываем ячейки для «Имя ряда:», «Значения X:» и «Значения Y:». Добавим таким образом функцию «j» на график.

Теперь добавим функцию «k», у которой совершенно другие исходные данные и их количество.

Как видно, на точечном графике функция «k» практически незаметна, но зато построена с учетом значений по обеим осям.

Некоторое время назад мы опубликовали первую часть нашего самоучителя по созданию диаграмм в Excel для начинающих, в которой была дана подробная инструкция, как построить график в Excel. И самым первым вопросом, заданным в комментариях, был: «Как показать на диаграмме данные, расположенные на разных рабочих листах?«. Хочу поблагодарить читателя, задавшего этот замечательный вопрос!

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

• Создаём диаграмму из нескольких листов Excel
• Настраиваем диаграмму, созданную из нескольких рабочих листов

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

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

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

Откройте первый рабочий лист Excel, выделите данные, которые нужно отобразить на диаграмме, откройте вкладку Вставка (Insert) и в разделе Диаграммы (Charts) выберите нужный тип диаграммы. Для нашего примера мы выберем Объёмная гистограмма с накоплением (Stack Column).

2. Добавьте второй ряд данных с другого листа

Кликните по только что созданной диаграмме, чтобы на Ленте меню появилась группа вкладок Работа с диаграммами (Chart Tools), откройте вкладку Конструктор (Design) и нажмите кнопку Выбрать данные (Select Data). Или нажмите по иконке Фильтры диаграммы (Chart Filters) справа от диаграммы и в самом низу появившегося меню, кликните ссылку Выбрать данные (Select Data).

В диалоговом окне Выбор источника данных (Select Data Source) нажмите кнопку Добавить (Add).

Теперь добавим второй ряд данных с другого рабочего листа. Этот момент очень важен, поэтому внимательно следуйте инструкциям. После нажатия кнопки Добавить (Add) откроется диалоговое окно Изменение ряда (Edit Series), здесь нужно нажать иконку выбора диапазона рядом с полем Значения (Series values).

Диалоговое окно Изменение ряда (Edit Series) свернётся. Кликните по ярлычку листа, содержащего следующую часть данных, которые нужно показать на диаграмме Excel. При переключении на другой лист диалоговое окно Изменение ряда (Edit Series) останется на экране.

На втором листе выделите столбец или строку данных, которые нужно добавить на диаграмму Excel, и снова кликните по иконке выбора диапазона, чтобы диалоговое окно Изменение ряда (Edit Series) вернулось к первоначальному размеру.

Теперь кликните по иконке выбора диапазона рядом с полем Имя ряда (Series name) и выберите ячейку, содержащую текст, который нужно использовать в качестве имени ряда данных. Вновь кликните по иконке выбора диапазона, чтобы вернуться к первоначальному диалоговому окну Изменение ряда (Edit Series).

Проверьте ссылки, которые теперь появились в полях Имя ряда (Series name) и Значения (Series values), и нажмите ОК.

Как видно на рисунке выше, мы связали имя ряда с ячейкой B1, в которой содержится заголовок столбца. Вместо ссылки на заголовок столбца, можно ввести имя в виде текстовой строки, заключённой в кавычки, например:

="Второй ряд данных"

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

3. Добавьте еще больше рядов данных (по желанию)

Если в диаграмме должны быть показаны данные более, чем с двух рабочих листов, то повторите предыдущий этап для каждого ряда данных, который нужно добавить на диаграмму. Когда закончите, нажмите ОК в диалоговом окне Выбор источника данных (Select Data Source).

Я для примера добавил третий ряд данных, и моя диаграмма теперь выглядит так:

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

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

Выделите диаграмму, нажмите иконку Элементы диаграммы (Chart Elements) в виде зелёного креста возле правого верхнего угла диаграммы, и отметьте галочкой нужные параметры:

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

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

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

В данном примере расположение данных одинаково на всех трёх листах. Если требуется построить график из гораздо больших таблиц, и нет уверенности в том, что структура данных в этих таблицах одинакова, то разумнее будет сначала создать итоговую таблицу, а уже из получившейся итоговой таблицы создавать диаграмму. Чтобы заполнить итоговую таблицу нужными данными, можно использовать функцию ВПР (VLOOKUP).

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

=ВПР(A3;'2014'!$A$2:$B$5;2;ЛОЖЬ)
=VLOOKUP(A3,'2014'!$A$2:$B$5,2,FALSE)

И получить вот такой результат:

Далее просто выбираем итоговую таблицу, открываем вкладку Вставка (Insert) и в разделе Диаграммы (Charts) выбираем нужный тип диаграммы.

Настраиваем в Excel диаграмму, созданную из нескольких рабочих листов

Может случиться так, что после завершения создания диаграммы из двух или более рабочих листов, становится ясно, что она должна быть построена по-другому. А так как создание такой диаграммы в Excel – это не такой быстрый процесс, как создание диаграммы из одного листа, то вероятно, что проще будет переделать созданную диаграмму, чем создавать новую с чистого листа.

В целом, параметры для диаграммы Excel, созданной из нескольких рабочих листов, ничем не отличаются от параметров для обычной диаграммы Excel. Можно использовать группу вкладок Работа с диаграммами (Charts Tools), или контекстное меню, или иконки настройки в правом верхнем углу диаграммы, чтобы настроить основные элементы, такие как название диаграммы, названия осей, легенду, стиль диаграммы и прочее. Пошаговые инструкции по настройке этих параметров можно найти в статье о настройке диаграмм в Excel.

Если же требуется изменить ряд данных, показанный на диаграмме, то сделать это можно одним из трёх способов:

• Диалоговое окно «Выбор источника данных»
• Иконка «Фильтры диаграммы»
• Формулы рядов данных

Изменяем ряд данных при помощи диалогового окна «Выбор источника данных»

Откройте диалоговое окно Выбор источника данных (Select Data Source), для этого на вкладке Конструктор (Design) нажмите кнопку Выбрать данные (Select data).

Чтобы изменить ряд данных, кликните по нему, затем нажмите кнопку Изменить (Edit) и отредактируйте параметры Имя ряда (Series Name) или Значение (Series Values), как мы делали ранее в этой статье. Чтобы изменить порядок рядов данных на диаграмме, выделите ряд данных и переместите его вверх или вниз при помощи соответствующих стрелок.

Чтобы скрыть ряд данных, просто уберите галочку в списке Элементы легенды (Legend Entries) в левой части диалогового окна. Чтобы удалить ряд данных из диаграммы полностью, выделите его и нажмите кнопку Удалить (Remove).

Скрываем или показываем ряды данных при помощи иконки «Фильтры диаграммы»

Другой способ управлять рядами данных, которые отображаются на диаграмме Excel, это иконка Фильтры диаграммы (Chart Filters). Если кликнуть по диаграмме, то эта иконка тут же появится справа.

• Чтобы скрыть данные, кликните по иконке Фильтры диаграммы (Chart Filters) и уберите галочку возле соответствующего ряда данных или категории.
• Чтобы изменить ряд данных, нажмите кнопку Изменить ряд (Edit Series) справа от имени ряда. Появится уже знакомое диалоговое окно Выбор источника данных (Select Data Source), в котором можно сделать нужные настройки. Чтобы кнопка Изменить ряд (Edit Series) появилась, достаточно просто навести указатель мыши на имя ряда. При этом ряд данных, на который наведена мышь, выделяется на диаграмме цветом, чтобы было проще понять, какой элемент будет изменён.

Изменяем ряд данных при помощи формулы

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

=РЯД('2013'!$B$1;'2013'!$A$2:$A$5;'2013'!$B$2:$B$5;1)
=SERIES('2013'!$B$1,'2013'!$A$2:$A$5,'2013'!$B$2:$B$5,1)

Каждая формула ряда данных состоит из нескольких основных элементов:

=РЯД(;;диапазон_данных;номер_ряда)

То есть наша формула может быть расшифрована так:

• Имя ряда (‘2013’!$B$1) взято из ячейки B1 на листе .
• Имена категорий (‘2013’!$A$2:$A$5) взяты из ячеек A2:A5 на листе .
• Данные (‘2013’!$B$2:$B$5) взяты из ячеек B2:B5 на листе .
• Номер ряда (1) говорит о том, что этот ряд занимает на графике первое место.

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

На сегодня всё! Спасибо за внимание!

Урок подготовлен для Вас командой сайта office-guru.ru
Источник: /> Перевел: Антон Андронов

Правила перепечаткиЕще больше уроков по Microsoft Excel

Оцените качество статьи. Нам важно ваше мнение:

Информация воспринимается легче, если представлена наглядно. Один из способов презентации отчетов, планов, показателей и другого вида делового материала – графики и диаграммы. В аналитике это незаменимые инструменты.

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

Простейший график изменений

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

Допустим, у нас есть данные по чистой прибыли предприятия за 5 лет:

Год	Чистая прибыль*
2010	13742
2011	11786
2012	6045
2013	7234
2014	15605

* Цифры условные, для учебных целей.

Заходим во вкладку «Вставка». Предлагается несколько типов диаграмм:

Выбираем «График». Во всплывающем окне – его вид. Когда наводишь курсор на тот или иной тип диаграммы, показывается подсказка: где лучше использовать этот график, для каких данных.

Выбрали – скопировали таблицу с данными – вставили в область диаграммы. Получается вот такой вариант:

Прямая горизонтальная (синяя) не нужна. Просто выделяем ее и удаляем. Так как у нас одна кривая – легенду (справа от графика) тоже убираем. Чтобы уточнить информацию, подписываем маркеры. На вкладке «Подписи данных» определяем местоположение цифр. В примере – справа.

Улучшим изображение – подпишем оси. «Макет» – «Название осей» – «Название основной горизонтальной (вертикальной) оси»:

Заголовок можно убрать, переместить в область графика, над ним. Изменить стиль, сделать заливку и т.д. Все манипуляции – на вкладке «Название диаграммы».

Вместо порядкового номера отчетного года нам нужен именно год. Выделяем значения горизонтальной оси. Правой кнопкой мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». В открывшейся вкладке выбрать диапазон. В таблице с данными – первый столбец. Как показано ниже на рисунке:

Можем оставить график в таком виде. А можем сделать заливку, поменять шрифт, переместить диаграмму на другой лист («Конструктор» — «Переместить диаграмму»).

График с двумя и более кривыми

Допустим, нам нужно показать не только чистую прибыль, но и стоимость активов. Данных стало больше:

Но принцип построения остался прежним. Только теперь есть смысл оставить легенду. Так как у нас 2 кривые.

Добавление второй оси

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

Сначала строим график так, будто у нас одинаковые единицы измерения.

Выделяем ось, для которой хотим добавить вспомогательную. Правая кнопка мыши – «Формат ряда данных» – «Параметры ряда» — «По вспомогательной оси».

Нажимаем «Закрыть» — на графике появилась вторая ось, которая «подстроилась» под данные кривой.

Это один из способов. Есть и другой – изменение типа диаграммы.

Щелкаем правой кнопкой мыши по линии, для которой нужна дополнительная ось. Выбираем «Изменить тип диаграммы для ряда».

Определяемся с видом для второго ряда данных. В примере – линейчатая диаграмма.

Всего несколько нажатий – дополнительная ось для другого типа измерений готова.

Строим график функций в Excel

Вся работа состоит из двух этапов:

1. Создание таблицы с данными.
2. Построение графика.

Пример: y=x(√x – 2). Шаг – 0,3.

Составляем таблицу. Первый столбец – значения Х. Используем формулы. Значение первой ячейки – 1. Второй: = (имя первой ячейки) + 0,3. Выделяем правый нижний угол ячейки с формулой – тянем вниз столько, сколько нужно.

В столбце У прописываем формулу для расчета функции. В нашем примере: =A2*(КОРЕНЬ(A2)-2). Нажимаем «Ввод». Excel посчитал значение. «Размножаем» формулу по всему столбцу (потянув за правый нижний угол ячейки). Таблица с данными готова.

Переходим на новый лист (можно остаться и на этом – поставить курсор в свободную ячейку). «Вставка» — «Диаграмма» — «Точечная». Выбираем понравившийся тип. Щелкаем по области диаграммы правой кнопкой мыши – «Выбрать данные».

Выделяем значения Х (первый столбец). И нажимаем «Добавить». Открывается окно «Изменение ряда». Задаем имя ряда – функция. Значения Х – первый столбец таблицы с данными. Значения У – второй.

Жмем ОК и любуемся результатом.

С осью У все в порядке. На оси Х нет значений. Проставлены только номера точек. Это нужно исправить. Необходимо подписать оси графика в excel. Правая кнопка мыши – «Выбрать данные» — «Изменить подписи горизонтальной оси». И выделяем диапазон с нужными значениями (в таблице с данными). График становится таким, каким должен быть.

Наложение и комбинирование графиков

Построить два графика в Excel не представляет никакой сложности. Совместим на одном поле два графика функций в Excel. Добавим к предыдущей Z=X(√x – 3). Таблица с данными:

Выделяем данные и вставляем в поле диаграммы. Если что-то не так (не те названия рядов, неправильно отразились цифры на оси), редактируем через вкладку «Выбрать данные».

А вот наши 2 графика функций в одном поле.

Графики зависимости

Данные одного столбца (строки) зависят от данных другого столбца (строки).

Построить график зависимости одного столбца от другого в Excel можно так:

Условия: А = f (E); В = f (E); С = f (E); D = f (E).

Выбираем тип диаграммы. Точечная. С гладкими кривыми и маркерами.

Выбор данных – «Добавить». Имя ряда – А. Значения Х – значения А. Значения У – значения Е. Снова «Добавить». Имя ряда – В. Значения Х – данные в столбце В. Значения У – данные в столбце Е. И по такому принципу всю таблицу.

Скачать все примеры графиков

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

Диаграмма Excel с двумя осями ординат

В тех случаях, когда требуется отобразить на одной диаграмме данные разного масштаба, полезно использовать две оси ординат. Типичный случай – абсолютные (рубли) и относительные (проценты) показатели. Например, размер дебиторской задолженности и её доля от реализации.

(Дальнейший текст будет более понятен, если открыть в Excel2007 пример Диаграмма)

Скачать статью в формате Word2007 Диаграмма Excel с двумя осями ординат

Дополнение от 2 ноября 2016 г. В Excel 2013 работа с такими диаграмма существенно упростилась, см. Шаблон комбинированной диаграммы