Как отображать маленькие и большие значения на одном графике? Это один из наиболее часто задаваемых вопросов, когда речь идет о визуализации данных. Наверняка, вы сталкивались с ситуацией, когда при построении графика большинство элементов ряда данных близки по значению, но в массиве также присутствует пара значений, отличающихся на порядок или два от всех остальных. В этом случае построенная диаграмма не будет читабельной, а ее внешний вид не будет отражать реального положения дел. Выйти из положения можно, разделив ось Y на две составляющие, где по одной части будут строиться основные элементы ряда данных, а по другой – максимальные значения. Ниже приведен один из способов построения диаграммы в Excel с ломаной осью.
Конечный результат показан ниже. Здесь нижняя часть, показывающая малые значения, отложена по большой оси (слева), а большие значения отложены по малой оси (справа). Обе оси являются непрерывными и масштабируются от нуля. Поэтому данную диаграмму можно воспринимать как два графика, расположенных один под другим.
Подготовка данных
Прежде чем мы начнем рисовать график, нам нужно подготовить данные. Основной трюк будет заключаться в том, что значения, превышающие определенный предел, будут умножаться на некоторый коэффициент, что приблизит большие значения к основному массиву. Допустим, у нас есть временной ряд, отражающий ежедневные изменения какого-то показателя, и два значения этого массива находятся вне общего ряда.
Нам нужно преобразовать их в значения, близкие к основному массиву. Для этого воспользуемся формулой =SI(C3>100;100+C3*100/1000;C3), которая умножит значения больше 100 на 0,1 и прибавит к ним 100. Таким образом, значение 960 будет переведите в 196 = 100 + 960 * 0,1, и это не будет так уж сильно отличаться от общей массы.
Мы также добавим столбец для построения ряда данных, который визуально разграничит диаграмму маленькими и большими значениями. В приведенном выше примере это серая пунктирная линия, проходящая через центр диаграммы. Этот график будет построен на нулевой отметке вспомогательной оси, поэтому он заполнен нулями.
Построение диаграммы
Чтобы создать диаграмму, зажмите клавишу CTRL и выберите столбцы с заголовками Дата, Пересчет и Разделитель. В моем примере это диапазоны B2:B17 и D2:E17. Перейдите на вкладку «Вставка» в группе «Диаграммы» и выберите «Вставить диаграмму» -> «Диаграмма». У вас должен получиться следующий график.
Теперь нужно настроить отображение основной и вспомогательной осей. Чтобы у нас появилась вспомогательная ось, щелкаем правой кнопкой мыши по разделителю рядов данных. Выберите Формат ряда данных в раскрывающемся меню. В появившейся правой панели «Формат ряда данных» перейдите на вкладку «Параметры ряда» и поместите закладку «Построить ряд -> Вдоль вспомогательной оси». В правой части диаграммы у вас должна быть вспомогательная ось Y.
Далее настраиваем оси так, чтобы значения меньше 100 отображались на основной оси (той, что слева) с шагом 20, при этом значения от 100 и далее не должны быть видны на оси, а значения 100 и выше отображались на вспомогательной оси с шагом 200, но нижняя половина не была видна. В этом случае нам поможет пользовательский формат. Особенно по порядку.
Щелкните правой кнопкой мыши главную ось, выберите «Формат оси» в раскрывающемся меню. В появившейся правой панели во вкладке Axis Parameters установите значения Minimum равным 0, Maximum равным 200, Divisions больше равным 20. В этой же вкладке перейдите в группу Number и в поле Format Code вставить пользовательский формат [=0]0;[
Далее необходимо настроить вторичную ось. Для этого щелкните правой кнопкой мыши по оси, выберите «Формат оси» в раскрывающемся меню. В появившейся правой панели на вкладке «Параметры оси» установите минимальные значения на -1000, максимальные на 1000, основные деления на 200. В группе «Число» укажите формат 0;; . Это означает, что ко всем положительным значениям будет применен общий формат, а к 0 и отрицательным значениям форматирование не будет применено.
Нижняя часть вторичной оси должна исчезнуть, как показано на рисунке. Это перемещает разделитель строк данных в центр.
Следующий шаг — визуально отделить верхнюю часть диаграммы от нижней. Для этого щелкните разделитель рядов данных правой кнопкой мыши, в раскрывающемся меню выберите «Формат ряда данных». В появившейся справа панели переходим на вкладку «Заливка и границы» в группе «Линия» и меняем Цвет на белый, Непрозрачность 14%, Ширина равна 20.
Добавим еще одну строку данных со значениями из столбца Разделитель, чтобы была четкая граница между маленькими и большими значениями. Построим его по вспомогательной оси и назовем Divider2. Давайте отформатируем его, чтобы отличить от основного ряда данных. Результат показан на следующем изображении:
Осталось добавить метки данных и отформатировать нашу диаграмму. Выбираем наш основной ряд данных, переходим на вкладку «Работа с графиками» -> «Макет» в группе «Макеты графиков». Выберите «Добавить элемент диаграммы» -> «Метки данных» -> «По центру». Щелкните правой кнопкой мыши метки, которые появляются на диаграмме, выберите «Форматировать метки данных» в раскрывающемся меню. В появившейся справа панели переходим на вкладку «Параметры подписи», ставим галочку напротив пункта «Значения ячеек», в появившемся диалоговом окне «Диапазон меток данных» указываем диапазон столбца «Данные» (в моем например С3:С17) нажмите ОК и снимите все остальные галочки Включить в подпись.
Осталось внести коррективы в формат нашей диаграммы: убрать легенду, изменить название диаграммы на более осмысленное. Метки данных можно сделать более контрастными, потому что наш график не очень традиционный и может запутать пользователей.
Окончательная схема может выглядеть так:
Подписи данных в Excel 2010
Есть причуда при попытке добавить метки данных в Excel 2010. Дело в том, что в Excel 2010 нет возможности явно указать диапазон меток данных, поэтому приходится прибегать к некоторым хитростям. Основная идея заключается в том, что в качестве метки мы будем использовать Имена категорий, которые создадим из имеющегося у нас диапазона.
Добавим в нашу таблицу дополнительный столбец, назовем его Signatures и введем следующую формулу: =C3&» » , которая преобразует числовой формат в текстовый.
Затем щелкните правой кнопкой мыши строку данных пересчета и выберите «Выбрать данные» в раскрывающемся списке. В появившемся диалоговом окне «Выбор источника данных» в поле «Метки горизонтальной оси» нажмите «Изменить». В следующем окне выберите данные для столбца Подписи. Нажмите ОК дважды.
Как видите, теперь на горизонтальной оси диаграммы вместо дат отображаются значения диапазона.
Выберите ряд данных Recalculation, перейдите на вкладку Chart Tools -> Layout в группе Labels, выберите Data Labels -> Centered. Щелкаем по появившимся на диаграмме меткам правой кнопкой мыши, в выпадающем меню выбираем Формат меток данных. В диалоговом окне на вкладке «Параметры подписи» установите флажок рядом с полем «Имена категорий», остальные флажки снимите. Нажимаем Закрыть.
Осталось настроить отображение горизонтальных осей. Щелкните правой кнопкой мыши по нижней горизонтальной оси, выберите «Формат оси» в раскрывающемся меню. На вкладке «Параметры оси» в поле «Метки оси» выберите «Нет» и нажмите «Закрыть.
Снова выберите всю диаграмму, перейдите на вкладку «Инструменты диаграммы» -> «Макет» в группе «Оси», выберите «Оси» -> «Вспомогательная горизонтальная ось» -> «Слева направо». На диаграмме должна появиться дополнительная горизонтальная ось, которая по умолчанию находится вверху диаграммы. Ее нужно разместить на месте основной оси, для этого щелкните правой кнопкой мыши по вспомогательной оси, в выпадающем меню выберите «Формат оси». На вкладке «Параметры оси» в поле «Общие» выберите «Нет», а в поле «Метки оси» выберите «Низ». Перейдите на вкладку «Цвет линии», поместите маркер напротив элемента «Нет линии», нажмите «Закрыть.
Резюме
По сути, для отображения малых и больших значений на диаграмме в Excel мы построили диаграмму с измененными данными и наложили ее на подогнанные оси. Такой подход не является стандартным, но как показывает практика, в нем не особо сложно разобраться.
Чтобы лучше понять этот подход, вы можете скачать пример файла «Создание диаграммы с ломаной осью.
Вам также могут быть интересны следующие статьи
- Создайте диаграмму в Excel с отрицательными и положительными значениями
- Что такое Treemap и как это сделать в Excel
- Планки ошибок в Excel: нестандартное использование
- Функция РЯД в Excel для диаграмм
- Создайте сводную диаграмму в Excel, используя именованные диапазоны
- Диаграмма водопада в Excel
- Создать маркированную диаграмму
- Создайте диаграмму спидометра в Excel
- Диаграмма Ганта в Excel
- Воронка продаж в Excel