Как построить график (диаграмму) с двумя осями «Y» в программе «Excel».
Очень часто при проведении аналитики различных показателей для наглядности нужно уместить на одном графике или диаграмме показатели, выраженные в единицах и показатели в долях или процентах (%).
Например: существуют данные о часовом потреблении электроэнергии двух различных электрических печей. Эти показатели выражаются в тысячах ватт.
Необходимо отобразить на одном графике (диаграмме) потребление этих печей и экономичность одной из печей в процентах.
| Время | Печь №1, Вт | Печь №2 Вт | Экономия в % |
| 0:00 | 5600 | 3800 | 32% |
| 1:00 | 5600 | 3920 | 30% |
| 2:00 | 5400 | 3789 | 30% |
| 3:00 | 5421 | 3724 | 31% |
| 4:00 | 5203 | 3800 | 27% |
| 5:00 | 5400 | 3920 | 27% |
| 6:00 | 5560 | 3789 | 32% |
| 7:00 | 5523 | 3800 | 31% |
| 8:00 | 4950 | 3920 | 21% |
| 9:00 | 5525 | 3789 | 31% |
| 10:00 | 5123 | 3724 | 27% |
| 11:00 | 5842 | 3800 | 35% |
| 12:00 | 5422 | 3920 | 28% |
| 13:00 | 5523 | 3789 | 31% |
| 14:00 | 4950 | 3800 | 23% |
| 15:00 | 5525 | 3789 | 31% |
| 16:00 | 5123 | 3800 | 26% |
| 17:00 | 5842 | 3789 | 35% |
| 18:00 | 5560 | 3800 | 32% |
| 19:00 | 5523 | 3654 | 34% |
| 20:00 | 4950 | 3245 | 34% |
| 21:00 | 5525 | 3658 | 34% |
| 22:00 | 5123 | 3952 | 23% |
| 23:00 | 5525 | 3698 | 33% |
Если мы отобразим на одной диаграмме и потребление в ваттах и процент экономии, получится неинформативная картина — проценты будут теряется на фоне остальных показателей.
Что же сделать, чтобы проценты на графике отображались в другом масштабе?
Необходимо построить две оси в графике (вставить дополнительную вертикальную ось «Y»).
Рассмотрим, как создается диаграмма с двумя вертикальными осями в «Excel».
Шаг 1. Для начала следует построить обычную диаграмму или график.
Шаг 2. Кликнуть левой кнопкой мыши по нужной колонке (в нашем случае это колонка с % процентами).
Хитрость: если колонка с процентами очень мала (неразличима на графике) и Вы не можете по ней попасть курсором, выделите соседнюю колонку (столбик) и при помощи кнопок навигации на клавиатуре переставьте выделение на невидимый столбик.
Шаг 3. Кликните правой кнопкой мыши по выделению и выберете в появившемся меню «Изменитьтип диаграммы для ряда»и поставьте флажек (галочку) напротив пункта «Вспомогательная ось» (как показано на рисунке).
Теперь справа появилась дополнительная (вторая) ось «игрек» с шагом пропорциональным показателям процентов.
Чтобы проценты выделялись и визуально отличались от потребления, можно показатели потребления отобразить в виде колонок диаграммы, а проценты в виде линии графика.
XYZ 3D-диаграмма в Excel
Если цель-просто для отображения действительных х значения, чтобы их можно было прочитать непосредственно из диаграммы, обычным способом сделать это было бы использование стандартных линий сетки и добавление меток данных, где это необходимо.
| Время | Печь №1, Вт | Печь №2 Вт | Экономия в % |
| 0:00 | 5600 | 3800 | 32% |
| 1:00 | 5600 | 3920 | 30% |
| 2:00 | 5400 | 3789 | 30% |
| 3:00 | 5421 | 3724 | 31% |
| 4:00 | 5203 | 3800 | 27% |
| 5:00 | 5400 | 3920 | 27% |
| 6:00 | 5560 | 3789 | 32% |
| 7:00 | 5523 | 3800 | 31% |
| 8:00 | 4950 | 3920 | 21% |
| 9:00 | 5525 | 3789 | 31% |
| 10:00 | 5123 | 3724 | 27% |
| 11:00 | 5842 | 3800 | 35% |
| 12:00 | 5422 | 3920 | 28% |
| 13:00 | 5523 | 3789 | 31% |
| 14:00 | 4950 | 3800 | 23% |
| 15:00 | 5525 | 3789 | 31% |
| 16:00 | 5123 | 3800 | 26% |
| 17:00 | 5842 | 3789 | 35% |
| 18:00 | 5560 | 3800 | 32% |
| 19:00 | 5523 | 3654 | 34% |
| 20:00 | 4950 | 3245 | 34% |
| 21:00 | 5525 | 3658 | 34% |
| 22:00 | 5123 | 3952 | 23% |
| 23:00 | 5525 | 3698 | 33% |
5 приёмов, как улучшить диаграммы в Excel
Автор книги «Мастерство визуализации данных» и создатель блога data.vis Ольга Базалева поделилась приёмами создания профессиональных диаграмм в Excel.
В статье — продвинутые способы создания качественных диаграмм. С основными правилами визуализации данных можно ознакомиться в предыдущей публикации.
Приём 1: задать перекрытие столбцов на диаграмме
Когда и зачем применять. Когда нужно сравнить показатели текущего года и прошлого. Если на гистограмме или линейчатой диаграмме вывести текущий год на передний план, а прошлый оставить на заднем, информация будет легче считываться.
Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы.
Чтобы одни столбцы полностью перекрывали другие, выбирайте перекрытие 100%. Можно настроить другое соотношение перекрытия, например, 70%.
Приём 2: уменьшить расстояние между столбцами
Когда и зачем применять. Прием подходит для любых диаграмм динамики, например, результатов работы или выручки по месяцам. Сокращать расстояние между столбцами нужно, чтобы внимание не рассеивалось на них, динамика воспринималась целостной.
Чтобы сохранить визуальную целостность и сделать акцент на данных, выбирайте зазор до 25-50%.
Курс
EXCEL
Приём 3: отобразить среднее скользящее
Когда и зачем применять. Приём нужен при анализе динамики какого-либо показателя за длительный период с разбивкой по дням, когда ежедневные показатели значительно отличаются. Из-за сильных колебаний будет сложно рассмотреть тенденцию. Наглядность диаграммы улучшит скользящее среднее.
Инструмент сглаживает колебания и позволяет чётче увидеть тренд. Можно построить скользящее среднее за 21 день, 11, 5 или выбрать любое другое окно. Все зависит от уровня сглаживания, который вы хотите увидеть. Чем шире окно, тем более сглаженная линия.
Приём 4: оставить одну подпись на несколько диаграмм
Когда и зачем применять. Если вы построили две диаграммы с одинаковыми категориями данных на одном листе, оставьте одну подпись. Это поможет сэкономить место, избежать повторов одной и той же информации и исключить распространенную ошибку, когда на двух диаграммах категории расположены в разной последовательности.
Приём 5: добавить вспомогательную ось
Когда и зачем применять. Когда на одном графике отображается динамика двух показателей, которые сложно сравнить друг с другом. Например, показателей в рублях и процентах или с разным порядком чисел — выручкой в миллионном исчислении и средним чеком в тысячном. Ось помогает проще сопоставлять и считывать данные.
Подборка курсов, бесплатных лекций и материалов по Excel
Чтобы упростить восприятие информации, сделайте на диаграмме подсказки цветом. На примере все элементы ряда «Выручка», в том числе сами столбцы, название и подписи оси, окрашены в зеленый. Ряд «Средний чек» и его элементы — в красный.
На вспомогательной оси проще оценить динамику среднего чека, элементы двух рядов выделены разными цветами
Мнение автора и редакции может не совпадать. Хотите написать колонку для «Нетологии»? Читайте наши условия публикации. Чтобы быть в курсе всех новостей и читать новые статьи, присоединяйтесь к Телеграм-каналу Нетологии.
Подборка книг и блогов по визуализации данных
[expert_bq id=»1570″]С помощью вкладки Конструктор можно изменить тип и стиль диаграммы, переместить график на отдельный лист Excel, выбрать диапазон данных для отображения на графике. Если же вы хотите что-то уточнить, обращайтесь ко мне![/expert_bq] В Экселе есть множество различных графиков, и чтобы их построить, необходимо составить таблицу данных. Что бы создать диаграмму нужно открыть вкладку «Вставка» и выбрать на панели «Диаграммы» подходящий вид.Как создать смешанную диаграмму в Excel — Трюки и приемы в Microsoft Excel
Выделяем диапазон ячеек А6:J7. В ленте выбираем вкладку Вставка в разделе Диаграммы указываем тип Точечная и вид Точечная с гладкими кривыми и маркерами.
