Диаграмма с разделенной осью в Excel

12.01.2018

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

Попробуем, например, построить график по вот такой простейшей табличке:

Исходные данные
Исходные данные

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

График по исходным данным
График по исходным данным

На нем прекрасно видно, что в Феврале, Марте и Декабре происходит пик продаж (что, скорее, исключение, назовём эти точки - "точками выбросов"), а вот динамика по большей части года абсолютно не видна. А хотелось бы видеть на графике и её.

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

Подготовка исходных данных

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

Отсортированные исходные данные
Отсортированные исходные данные

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

Мы видим, что порядок точек выбросов примерно в 10 раз больше, чем порядок основных точек. Значит, чтобы привести их к примерно одинаковой величине, нужно поделить их на 10. Разделив все пиковые точки на одно и то же число, мы не изменим пропорции между ними (то есть они правильно расположатся на диаграмме, относительно друг друга), но приблизим их к основным данным.

Но нам нужно, чтобы выбросы были в верхней половине диаграммы, значит необходимо "перебросить" их через пограничную черту. Для этого к каждой точке прибавим 5000 (это поднимет их над основными, но оставит в поле видимости).

Для этого рядом с исходными данными создадим столбец "Данные для построения" и введем в него формулу: =ЕСЛИ(B4>5000;B4/10+5000;B4) (при условии, что исходные данные в столбце B). Эта формула проверит, относится ли число к выбросам (как мы выяснили, все выбросы - больше 5000) и если да - преобразует его в точку для построения.

Кроме данных, нам нужна также граница, которая будет делить ось на две части. Для этого в четвертом столбце (назовем его "Разделитель") введите во все ячейки нули.

Данные для построения
Данные для построения

Построение диаграммы

Теперь выделим столбцы "Месяц"; "Данные для построения" и "Разделитель" и построим Комбинированную диаграмму. Вставка - Диаграммы - Все диаграммы - Комбинированная. Для обоих рядов укажем тип "График", а для ряда "Разделитель" установим галочку "Вспомогательная ось".

Создание диаграммы
Создание диаграммы

Получим диаграмму такого вида:

График по новым данным
График по новым данным

Как видите, точки выбросов уже не так далеко от основных данных, как были раньше, но выше границы в 5000. Дальше нам нужно настроить диаграмму так, чтобы ось на ней выглядела "разделенной", значения левой оси относились к основным данным, а значения правой - к выбросам.

Настройка диаграммы

Выделяем левую ось и выбираем "Формат оси" либо нажимаем Ctrl+1.

В открывшейся панели на вкладке "Параметры оси" устанавливаем верхнюю и нижнюю границы так, чтобы разделитель был посередине оси (для нашего разделителя в 5000 ставим верхнюю границу оси в размере 10000, а нижнюю - 0). Цену деления сделаем в 1000. Далее находим группу команд "Число" и в поле "Код формата" вводим следующий код:

"[=0]0;[<=5000]0;" (без кавычек)

Снимаем галочку "Связь с источником" и жмём "Добавить"

Ввод формата
Ввод формата

Далее работаем со второй осью. Проделываем те же операции, только верхнюю границу ставим в 10 раз больше нашей линии разделителя (то есть 50000), а нижнюю границу делаем противоположной верхней (то есть -50000). Код формата введите вот такой "0;;" (без кавычек), а в качестве цены деления укажите цену, в 10 раз большую, чем на левой оси (то есть 10000). В результате оси примут следующий вид:

Отформатированные оси
Отформатированные оси

Выглядит неплохо, но еще лучше станет, когда добавятся подписи данных для точек. Для этого активируйте диаграмму, далее "Конструктор - Добавить элемент диаграммы - Метки данных - Сверху (или другой вариант)". Ко всем точкам будут добавлены подписи. Далее выделите любую из подписей, выберите "Формат подписей данных" (или Ctrl+1), перейдите на вкладку "Параметры подписи", поставьте галочку "Значения из ячеек" и уберите остальные (можно оставить линии выноски). В качестве диапазона значений укажите ИСХОДНЫЕ данные (если у Вас Excel 2010 или более старый, придётся пойти другим путем, описанным в конце статьи). Попутно можно настроить форматирование подписей на свой вкус.

Настройка подписей данных
Настройка подписей данных

Удалите ненужные метки данных (как минимум, подписи ряда-разделителя, а по желанию - подписи основных значений). Получится примерно следующее:

Настроенные подписи
Настроенные подписи

Осталось настроить линию разделения, чтобы ось выглядела "разорванной". Выделите ряд-разделитель, выберите "Формат ряда данных" (или Ctrl+1). В открывшейся панели на вкладке "Заливка и границы" настройте внешний вид границы (тип штриха, цвет, толщину). На вкладке "Эффекты" можно добавить свечение, сделав его белым и непрозрачным.

Далее настройте иное форматирование диаграммы: заголовок, легенду, фон и т.д. Все эти настройки - дело вкуса.

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

Итоговый результат
Итоговый результат

Примечание для пользователей Excel 2010 и более старых версий

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

1) Создаем на листе столбец, равный по высоте столбцу с исходными данными, назначаем ему текстовый формат;

2) Копируем в него ЗНАЧЕНИЯМИ исходные данные (нужно чтобы они приняли вид текста);

3) Выделяем ряд с данными на диаграмме, кликаем правой кнопкой мыши и выбираем "Выбрать данные";

4) Для подписей горизонтальной оси задаём созданный только что диапазон текстовых значений (в нижней части вместо месяцев появятся числа из исходного диапазона);

5) Добавляем на диаграмму метки данных, переходим в меню формата подписей данных и ставим галочку "Имя категории";

6) Для нижней оси (на которой вместо месяцев будут числа) назначаем код формата ";;;"

7) Добавляем на диаграмму элемент "Вспомогательная горизонтальная ось";

8) Кликаем по ней правой кнопкой мыши, "Формат оси". Убираем основные и промежуточные деления, убираем заливку линии. Положение подписи указываем "Внизу";

Готово! Теперь снизу будут месяцы, а подписи данных будут браться из созданного диапазона.

Файл пример с данными и построенной диаграммой можно найти вот в этом посте на нашем канале.

Поддержать наш проект и его дальнейшее развитие можно вот здесь.

Ваши вопросы по статье можете задавать через нашего бота обратной связи в Telegram: @ExEvFeedbackBot

С уважением, команда tDots.ru