Построение комбинированной диаграммы с дополнительной осью
Чтобы отобразить на одном графике данные с разными единицами измерения или сильно отличающимися значениями (например, количество продаж и выручку), используйте комбинированную диаграмму с двумя осями. Это позволяет визуализировать взаимосвязь показателей, которые иначе было бы невозможно сравнить из-за разницы в масштабах.
Зачем использовать вторичную ось
Стандартная гистограмма или линейный график работают отлично, пока все ряды данных сопоставимы. Проблемы возникают, когда один показатель измеряется в тысячах (например, 15 000 рублей), а другой — в единицах или процентах (например, 5 сотрудников). На обычном графике меньшие значения превратятся в прямую линию у основания, и динамику увидеть будет нельзя.
Вторичная ось решает эту проблему, добавляя отдельную шкалу справа. Это идеально подходит для анализа:
- Динамики цен и объемов продаж.
- Температуры и количества осадков.
- Расходов бюджета и процентного выполнения плана.
Лучший визуальный эффект достигается при сочетании разных типов графиков: например, столбцы для абсолютных значений (левая ось) и линия для относительных показателей или трендов (правая ось).
Пошаговая инструкция создания
Процесс одинаков для всех современных версий Excel (2016, 2019, 2021, 365).
1. Подготовка данных
Убедитесь, что ваша таблица структурирована корректно. Первый столбец должен содержать подписи категорий (даты, месяцы, названия), а последующие — числовые данные.
| Месяц | Количество сделок | Сумма сделки (руб.) |
|---|---|---|
| Январь | 45 | 1 200 000 |
| Февраль | 52 | 1 450 000 |
| Март | 38 | 980 000 |
2. Выбор типа диаграммы
- Выделите всю таблицу с данными, включая заголовки.
- Перейдите на вкладку Вставка (Insert).
- В группе «Диаграммы» нажмите кнопку Рекомендуемые диаграммы или выберите значок Комбинированная диаграмма (иконка со столбцом и линией).
- В открывшемся списке выберите Создать комбинированную диаграмму (Create Custom Combination Chart).
3. Настройка осей
Откроется окно настройки, где для каждого ряда данных можно выбрать свой тип отображения:
- Для ряда «Количество сделок» оставьте тип Гистограмма с группировкой. Галочка «Вторичная ось» должна быть снята.
- Для ряда «Сумма сделки» выберите тип График или Линейчатая. Обязательно поставьте галочку Вторичная ось (Secondary Axis) напротив этого ряда.
- Нажмите ОК.
На листе появится график, где слева отображается шкала количества, а справа — шкала суммы. Линии и столбцы теперь читаемы и не перекрывают друг друга визуально.
Тонкая настройка и оформление
После создания базового графика стоит уделить время его читаемости.
- Подписи осей. Если единицы измерения не очевидны из легенды, добавьте названия осей через вкладку Конструктор диаграмм → Добавить элемент диаграммы → Названия осей. Укажите «Шт.» для левой оси и «Руб.» для правой.
- Цветовое кодирование. Убедитесь, что цвет линии на графике совпадает с цветом соответствующей оси. Это помогает глазу быстрее считывать информацию. Чтобы изменить цвет оси, кликните по ней правой кнопкой мыши → Формат оси → выберите цвет шрифта и линии.
- Масштаб. Иногда автоматический масштаб правой оси выглядит неудобно (например, начинается с 800 000). Кликните правой кнопкой по правой оси, выберите Формат оси и вручную задайте минимальное и максимальное значение или цену делений.
Избегайте использования двух осей, если они измеряют одну и ту же величину. Это может запутать читателя и создать ложное впечатление о корреляции данных. Две оси оправданы только при разной размерности показателей.
Частые ошибки
- Неверный выбор данных. Если выделить только числа без заголовков, Excel присвоит стандартные имена («Ряд 1», «Ряд 2»), и легенда станет непонятной. Всегда выделяйте шапку таблицы.
- Перегрузка графика. Не пытайтесь отобразить на одной диаграмме более двух осей или более 3–4 рядов данных. График превратится в «кашу», которую невозможно проанализировать.
- Игнорирование нуля. Проверьте, чтобы обе оси начинались с нуля (если это логически верно для ваших данных). Обрезание шкалы может искусственно преувеличить рост показателей.
FAQ
Можно ли добавить третью ось в стандартном Excel? Нет, стандартными средствами можно создать только две оси (первичную и вторичную). Для отображения трех и более шкал требуется использование надстроек или переход в Power BI.
Как поменять оси местами (что было справа, стало слева)? Зайдите в меню изменения типа диаграммы (правая кнопка мыши по графику → Изменить тип диаграммы → Комбинированная). Снимите галочку «Вторичная ось» с текущего правого ряда и поставьте её на тот ряд, который должен уйти вправо.
Почему моя линия не видна на графике? Скорее всего, вы забыли назначить этому ряду «Вторичную ось». Пока оба ряда привязаны к одной шкале с огромным разбросом значений, меньший ряд будет выглядеть как плоская линия у основания.