Добавлять вспомогательную (вторую) вертикальную оси на график умеете? (если вдруг нет, для вас статья, там в шаге 3 — про добавление вспомогательной оси).
Третья вертикальная ось обычно нужна, если величины показателей сильно отличаются по порядку цифр(например, миллионы и десятки). Если расположить графики по одной вертикальной оси, то один из графиков сольется с горизонтальной осью.
А что делать, если рядов данных больше двух? И все они очень разные по величине? Например: выручка — в миллионах, количество чеков — в десятках тысяч, а средний чек — в сотнях рублей. Если их запихнуть в одну диаграмму, то ряд с самыми маленькими цифрами сольется с горизонтом.
Обхитрим Excel?
У нас есть таблица с данными по продажам за десять месяцев, содержащая три столбца с абсолютно разным порядком цифр:
- Выручка — в миллионах
- Количество чеков — в десятках тысяч
- Средний чек — в сотнях рублей
Задача: отразить все три ряда данных на одной диаграмме, чтобы увидеть взаимосвязь динамики показателей.
Если построить диаграмму стандартным способом (комбинированная диаграмма с использованием вспомогательной оси), то получится такая ситуация:
Ряд данных Средний чек (серый) слился с горизонтальной осью. Это произошло потому, что мы расположили ряд Средний чек (в сотнях рублей) на одной оси с рядом Количество чеков (в десятках тысяч).
В данном случае, какой бы ряд мы ни расположили по вспомогательной оси — нормально увидеть все три показателя на одной диаграмме не получится.
Поэтому единственный выход — добавить третью вертикальную ось.
Конечно, чисто технически это сделать невозможно. Но мы создадим виртуальную вертикальную ось.
На виртуальную ось будем помещать данные ряда Средний чек.
- Изменим название столбца Средний чек на Ср.чек_вспомог (или любое другое — это техническое имя, которое не будет видно на диаграмме).
- Добавим новый столбец к таблице, который назовем Средний чек.
- Теперь наша задача — подтянуть порядок цифр столбца Средний чек к значениям ряда Количество чеков.
Мы помним, что средний чек у нас в сотнях рублей, а количество чеков — в десятках тысяч. Значит, в новом столбце Средний чек нужно умножить значения из столбца Ср.чек_вспомог на 100. Таким образом, мы подтянем их к десяткам тысяч.
4. Теперь нужно добавить вновь созданный ряд Средний чек на нашу диаграмму. Если вы создавали диаграмму на основе «умной» таблицы, как в нашем примере, то ряд добавится автоматически. Если делали всё в обычной таблице, то смотрите пост, как быстро добавить новый ряд на диаграмму.
Старый ряд данных Средний чек (серый, который слился с горизонтальной осью) нужно удалить. Просто выделите его и нажмите Delete.
Теперь на диаграмме нормально отображаются все три ряда.
5. Доработаем внешний вид диаграммы:
- Уберем вертикальные оси. Это нужно сделать, потому что ряд Средний чек только виртуально отображается в десятках тысяч — по факту он остался в сотнях. И сравнивать его с осью, на который отображены десятки тысяч — некорректно.
Совсем удалить оси нельзя, иначе все данные снова встанут по одной оси. Но можно их скрыть при помощи формата: дважды щелкнем по оси — откроется окно Формат оси — выберем Параметры текста — Заливка текста — Нет заливки.
Так же поступим и с левой вертикальной осью (хотя ее можно и оставить).
6. Теперь добавим подписи данных для ряда Средний чек. Щелкнем правой кнопкой мыши на любом месте линии графика и выберем Добавить подписи данных.
Данные добавились — но не те, которые нам нужны. Это данные, на основании которых построен ряд — в десятках тысяч. Исправим это.
Щелкнем правой кнопкой мыши на любой подписи данных и выберем Формат подписи данных.
(1) Перейдем на вкладку Параметры подписи.
(2) Установим галочку Значения из ячеек (другие галочки пока не снимаем!)
(3) Установим курсор в поле Выберите диапазон меток данных — и выделим значения в столбце Ср.чек_вспомог. Нажмем Ок.
Теперь уберем галочку Значения в окне Формат подписей данных.
Теперь подписи данных для линии графика Средний чек соответствуют значениям среднего чека.
Добавим подписи данных на остальные ряды данных. Для ряда Количество чеков сделаем это обычным способом (правая кнопка мыши на ряде — Добавить подписи данных). А для ряда Выручка (белые цифры), чтобы визуально уменьшить число, также сделаем дополнительный ряд в нашей таблице, где разделим выручку на миллион — и значения из этих ячеек добавить в подпись данных (аналогично как делали для среднего чека).
Таким образом, мы добавили виртуальную третью ось на диаграмму, и ряд Средний чек хорошо визуализирован.
Статья взята с моего сайта https://excel-analytics.ru/