Excel график с разрывом оси

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

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

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

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

Нам необходимо конвертировать их в, близкие к основному массиву, значения. Для этого воспользуемся формулой =ЕСЛИ(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, но нижняя половина не была видна. В этом деле нам поможет пользовательский формат. Обо все по порядку.

Щелкаем правой кнопкой мыши по основной оси, в выпадающем меню выбираем Формат оси. В появившейся правой панели во вкладке Параметры оси устанавливаем значения Минимум равным 0, Максимум равным 200, Основные деления равным 20. В этой же вкладке переходим в группу Число и в поле Код формата вставляем пользовательский формат [=0]0;[<=100]0; , убираем галочку Связь с источником и щелкаем по кнопке Добавить. Верхняя половина основной оси должна исчезнуть, как показано на рисунке:

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

Нижняя часть вспомогательной оси должна исчезнуть, как показано на рисунке. При этом ряд данных Разделитель переместится к центру.

Следующим шагом нужно визуально разделить верхнюю часть графика от нижней. Для этого щелкаем по ряду данных Разделитель правой кнопкой мыли, в выпадающем меню выбираем Формат ряда данных. В появившейся справа панели переходим во вкладке Заливка и границы в группу Линия и меняем значения Цвет на белый, Прозрачность 14%, Ширина равная 20.

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

Осталось добавить подписи данных и отформатировать наш график. Выделяем наш основной ряд данных, переходим по вкладке Работа с диаграммами -> Конструктор в группу Макеты диаграмм. Выбираем Добавить элемент диаграммы -> Подписи данных -> По центру. Щелкаем правой кнопкой мыши по появившимся на графике подписям, из выпадающего меню выбираем Формат подписей данных. В появившейся справа панели переходим во вкладку Параметры подписи, ставим галочку напротив пункта Значения из ячеек, в появившемся диалоговом окне Диапазон меток данных указываем диапазон столбца Данные (у меня в примере это С3:С17) щелкаем ОК и убираем все остальные галочки из пункта Включать в подпись.

Осталось внести коррективы в формат нашей диаграммы – убрать легенду, название диаграммы изменить на что-нибудь более осмысленное. Подписи данных можно сделать более контрастными, так как наша диаграмма имеет не совсем традиционный вид и может ввести в заблуждение пользователей.

Итоговая диаграмма может иметь следующий вид:

Подписи данных в Excel 2010

Есть некоторая особенность при попытке добавить подписи данных в Excel 2010. Дело в том, что в Excel 2010 нет способа явно указать диапазон меток данных, поэтому придется прибегнуть к некоторым хитростям. Основная идея заключается в том, что в качестве подписи мы будем использовать Имена категорий, которые мы создадим из имеющегося у нас диапазона.

Добавим дополнительный столбец в нашу таблицу, назовем его Подписи и введем следующую формулу: =C3&» » , которая будет переводить числовой формат в текстовый.

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

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

Выделяем ряд данных Пересчет, переходим во вкладку Работа с диаграммами -> Макет в группу Подписи, выбираем Подписи данных -> По центру. Щелкаем по появившимся подписям на диаграмме правой кнопкой мыши, в выпадающем меню выбираем Формат подписей данных. В диалоговом окне во вкладке Параметры подписи ставим галочку напротив поля Имена категорий, остальные галочки убираем. Щелкаем Закрыть.

Осталось настроить отображение горизонтальных осей. Щелкаем правой кнопкой мыши по нижней горизонтальной оси, в выпадающем меню выбираем Формат оси. Во вкладке Параметры оси в поле Подписи оси выбираем значение Нет, щелкаем Закрыть.

Снова выделяем всю диаграмму, переходим во вкладку Работа с диаграммами -> Макет в группу Оси, выбираем Оси -> Вспомогательная горизонтальная ось -> Слева направо. На диаграмме должна появиться дополнительная горизонтальная ось, которая по умолчанию находится в верхней части графика. Ее необходимо поместить на место основной оси, для этого щелкаем правой кнопкой мыши по вспомогательной оси, в выпадающем меню выбираем Формат оси. Во вкладке Параметры оси в поле Основные выбираем значение Нет, в поле Подписи оси выбираем Внизу. Переходим во вкладку Цвет линии, устанавливаем маркер напротив пункта Нет линии, щелкаем Закрыть.

Резюме

Фактически, чтобы отобразить на одной диаграмме в Excel маленькие и большие значения, мы построили график с изменёнными данными и наложили его на скорректированные оси. Такой подход не стандартен, но как показывает практика, особых сложной с пониманием его не возникает.

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

Предположим, что в исходных данных есть две серии данных, как показано на снимке экрана ниже, мы можем легко добавить диаграмму и разбить ось диаграммы, добавив в диаграмму дополнительную ось. А сделать можно так:

1. Выберите исходные данные и добавьте линейную диаграмму, нажав кнопку Вставить линейную диаграмму или диаграмму с областями (или линия)> линия на Вставить меню.

2. На диаграмме щелкните правой кнопкой мыши следующую серию, а затем выберите Форматировать ряд данных из контекстного меню.

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

4. На диаграмме щелкните правой кнопкой мыши вторичную вертикальную ось (правую) и выберите Ось формата из контекстного меню.

5. На панели «Ось формата» введите 160 в максимальная коробка в Bounds раздел, а в Число группа войти [<= 80] 0 ;;; в Код формата и нажмите Добавить кнопку, а затем закройте панель.

Функции: В Excel 2010 или более ранних версиях откроется диалоговое окно «Формат оси». Пожалуйста, нажмите Вариант оси в левом баре проверьте Исправлена вариант позади максимальная и затем введите 200 в следующее поле; щелкнуть Число в левой панели введите [<= 80] 0 ;;; в Код формата и нажмите Добавить кнопку, наконец закройте диалоговое окно.

6. Щелкните правой кнопкой мыши основную вертикальную ось (левую) на диаграмме и выберите Ось формата чтобы открыть панель «Ось формата», затем введите [> = 500] 0 ;;; в Код формата и нажмите Добавить кнопку и закройте панель.
Функции: Если вы используете Excel 2007 или 2010, щелкните правой кнопкой мыши основную вертикальную ось на диаграмме и выберите Ось формата чтобы открыть диалоговое окно «Ось формата», нажмите Число в левой панели введите [> = 500] 0 ;;; в Код формата и нажмите Добавить кнопку и закройте диалоговое окно.)

Затем вы увидите две оси Y на выбранной диаграмме, которые выглядят так, как будто ось Y сломана. См. Снимок экрана ниже:

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

• Использование встроенных функций Excel (16 шагов)
• . Kutools for Excel’s Обрезать диаграмму по оси Y (3 шага)

Разбейте ось, добавив на диаграмму фиктивную ось с помощью встроенных функций Excel (16 шагов)

1. Чтобы сломать ось Y, мы должны определить минимальное значение, значение разрыва, значение перезапуска и максимальное значение на новой сломанной оси. В нашем примере мы получаем четыре значения в диапазоне A11: B14.

2. Нам нужно настроить исходные данные, как показано ниже:
(1) В ячейке C2 введите = ЕСЛИ (B2> $ B $ 13, $ B $ 13, B2), и перетащите маркер заливки в диапазон C2: C7;
(2) В ячейке D2 введите = ЕСЛИ (B2> B $ 13,100 XNUMX; NA ())и перетащите маркер заливки в диапазон D2: D7;
(3) В ячейке E2 введите =IF(B2>$B$13,B2-$B$12-1,NA())и перетащите маркер заливки в диапазон E2: E7.

3. Создайте диаграмму с новыми исходными данными. Выберите Диапазон A1: A7, затем выберите Диапазон C1: E7, удерживая Ctrl и вставьте диаграмму, щелкнув Вставить столбец или гистограмму (или Колонка)> Столбец с накоплением.

4. На новом графике щелкните правой кнопкой мыши серию разрывов (красный) и выберите Форматировать ряд данных из контекстного меню.

5. На открывшейся панели Форматировать ряд данных щелкните значок Цвет Кнопка на Заливка и линия вкладку, а затем выберите тот же цвет, что и цвет фона (в нашем примере белый).
Функции: Если вы используете Excel 2007 или 2010, откроется диалоговое окно «Форматировать ряд данных». Нажмите Заполнять в левой панели, а затем проверьте Без заливки вариант, наконец, закройте диалоговое окно.)
И измените цвет серии «После» на тот же цвет, что и для серии «До», тем же способом. В нашем примере мы выбираем Синии.

6. Теперь нам нужно выяснить исходные данные для фиктивной оси. Мы перечисляем данные в диапазоне I1: K13, как показано ниже:
(1) В столбце «Ярлыки» укажите все ярлыки на основе минимального значения, значения прерывания, значения перезапуска и максимального значения, которое мы указали на шаге 1.
(2) В столбце Xpos введите 0 для всех ячеек, кроме сломанной. В сломанной ячейке типа 0.25. См. Снимок экрана слева.
(3) В столбце Ypos введите числа на основе меток оси Y на диаграмме с накоплением.

7. Щелкните диаграмму правой кнопкой мыши и выберите Выберите данные из контекстного меню.

8. Во всплывающем диалоговом окне «Выбор источника данных» нажмите кнопку Добавить кнопка. Теперь в открывшемся диалоговом окне Edit Series выберите Cell I1 (For Broken Y Axis) как название сериии выберите Диапазон K3: K13 (столбец Ypos) как значения сериии нажмите OK > OK закрыть два диалоговых окна.

9. Теперь вернитесь к диаграмме, щелкните правой кнопкой мыши новую добавленную серию и выберите Изменить тип диаграммы серии из контекстного меню.

10. В открывшемся диалоговом окне «Изменить тип диаграммы» перейдите к Выберите тип диаграммы и ось для ряда данных , нажмите Для сломанной оси Y поле и выберите Скаттер с прямой линией из раскрывающегося списка и щелкните OK кнопку.

Примечание: Если вы используете Excel 2007 и 2010, в диалоговом окне «Изменить тип диаграммы» щелкните XY (разброс) на левой панели, а затем щелкните, чтобы выбрать Скаттер с прямой линией из раскрывающегося списка и щелкните OK кнопку.

11. Еще раз щелкните правой кнопкой мыши новую серию и выберите Выберите данные из контекстного меню.

12. В диалоговом окне Выбор источника данных щелкните, чтобы выбрать Для сломанной оси Y в Легендарные записи (серия) и нажмите Редактировать кнопка. Затем в открывшемся диалоговом окне Edit Series выберите Range J3: J13 (столбец Xpos) как Значения серии Xи нажмите OK > OK закрыть два диалоговых окна.

13. Щелкните правой кнопкой мыши новый разброс с прямой линией и выберите Форматировать ряд данных в контекстном меню.

14. На открывшейся панели «Форматировать ряд данных» в Excel 2013 щелкните значок цвет Кнопка на Заливка и линия вкладку, а затем выберите тот же цвет, что и столбцы «До». В нашем примере выберите Синии, (Внимание: Если вы используете Excel 2007 или 2010, в диалоговом окне Форматирование ряда данных щелкните Цвет линии в левом баре проверьте Сплошная линия вариант, щелкните Цвет и выберите тот же цвет, что и перед столбцами, и закройте диалоговое окно.)

15. Продолжайте выделять разброс прямой линией, а затем нажмите Добавить элемент диаграммы > Этикетки данных > левый на Дизайн меню.
Функции: Нажмите Этикетки данных > левый on макет вкладка в Excel 2007 и 2010.

16. Измените все метки на основе столбца «Метки». Например, выберите метку вверху диаграммы, а затем введите = на панели форматирования, затем выберите ячейку I13 и нажмите Enter .

16. Удалите некоторые элементы диаграммы. Например, выберите исходную вертикальную ось Y, а затем нажмите Удалить .

Наконец, вы увидите, что ваша диаграмма с ломаной осью Y создана.

Прервите ось, добавив фиктивную ось в диаграмму, используя Kutools for Excel’s Обрезать диаграмму по оси Y (3 шага)

Вышеописанный метод сложен и требует много времени. Следовательно, Kutools for Excel представляет простую в использовании функцию под названием Обрезать диаграмму оси Y, который позволяет быстро и легко создать столбчатую диаграмму с ломаной осью Y.

Примечание: Для использования Обрезать диаграмму оси Y особенность, вы должны иметь Kutools for Excel установлен на вашем компьютере. Пожалуйста нажмите здесь, чтобы скачать и установить. Профессиональная надстройка Excel предлагает 30-дневную бесплатную пробную версию без ограничений.

1. Нажмите Кутулс > Графики > Сравнение различий > Обрезать диаграмму оси Y чтобы открыть диалоговое окно настроек.

3. Появится всплывающее окно с напоминанием о том, что будет создан скрытый лист для хранения промежуточных данных. Нажмите Да кнопку.

Столбчатая диаграмма теперь создается с усеченной осью Y, как показано ниже.

Demo: Разорвать ось Y в диаграмме Excel

Статьи по теме

Суть проблемы заключается в следующем — мы имеем какой-то ряд данных, но одно или два значения очень сильно выбиваются из ряда, они очень большие по отношению к другим данным. Если построить обычную «Гистограмму с накоплением», эта «выпячивающаяся» часть исказит разницу между другими рядами. Эту проблему можно решить, например, таким способом.

Файлы для скачивания:

Файл	Описание	Размер файла:	Скачивания
Пример		13 Кб	1875

Итак, имеем вот такую таблицу и построенную на ее основе диаграмму — она нам не нравиться тем, что очень плохо видна разница между товарами с маленьким объемом продаж:

А хотим мы, для лучшей наглядности, получить вот такую диаграмму:

Приступаем. Секрет построения такой диаграммы прост. На самом деле, это две диаграммы, построенные на основе одной и той же таблицы, расположенные друг над другом.

Для начала давайте откорректируем первую диаграмму, которая у нас уже построена. Щелкаем мышкой по оси «Y» там, где деньги, нажимаем правую клавишу мышки и в выпавшем контекстном меню выбираем пункт «Формат оси»:

В открывшемся окне «Формат оси» в разделе «Параметры оси» в поле «Максимум» вводим «10 000,0», в поле «Основные деления» вводим «2 000,0» и нажимаем кнопку «Закрыть»:

Диаграмма приобретет следующий вид:

Уменьшаем диаграмму по вертикали, чтобы получилось так:

Строим вторую диаграмму на основе тех же данных. Должно получиться так:

Начинаем ее корректировать. Для начала удалим название. Щелкаем по нему левой клавишей мышки и нажимаем на клавиатуре клавишу «Del«. Удаляем нижнюю ось там, где написаны товары, так же щелкаем мышкой и «Del«:

Далее корректируем ось «Y» там, где деньги. Щелкаем по ней, нажимаем правую клавишу и выбираем «Формат оси». Устанавливаем следующие параметры:

• «Минимум» — «20 000,0»
• «Максимум» — «120 000,0»
• «Основные деления» — «25 000,0» 

Нажимаем кнопку «Закрыть»:

Получаем что-то вроде этого:

Начинаем подгонять (двигаем влево, вправо, сжимаем, растягиваем) вторую диаграмму под первую так, чтобы сошлись оси. Задача для терпеливых. У меня, к слову, ушло меньше минуты. В итоге получаем примерно следующее:

Продолжаем корректировку. Нам необходимо убрать рамку у второй диаграммы и сделать ее прозрачной. Для этого щелкаем мышкой по диаграмме, нажимаем правую клавишу, в контекстном меню выбираем «Формат области диаграммы…».

В открывшемся окне, в пункте «Заливка» переключаем тумблер в положение «Нет заливки»:

В пункте «Граница» переключаем тумблер в положение «Нет линий» и нажимаем кнопку «Закрыть»:

У нас, почти, все готово:

Осталось нарисовать линию — разделитель:

После того как линии нарисованы, щелкаем мышкой на какой ни будь пустой ячейке и наслаждаемся результатом. Да, не забудьте сохраниться, а то столько усилий — даром.

@spstelly 

Hi

I just want to provide a general answer as I do not see any sample data.

Plotting 2 data series on the same Vertical (value) axis, while one has large numbers and the second has small numbers may not allow you to view the small one (although it’s there).

With the chart selected >> click on the Format or Layout Tab >> in the upper left corner there is a drop list for all chart elements >> select the small Data Series >> click on the format option just below the drop list >> a dialog box opens >> Select Secondary Axis

Then, right click on the new data series that popup in the chart and change the chart type to a line chart.

Hope that helps, I am trying to provide help blindly as I do not have Excel 2010, last time I used it was 6 years ago.

Good Luck

Nabil Mourad

Как отображать маленькие и большие значения на одном графике? Это один из наиболее часто задаваемых вопросов, когда речь идет о визуализации данных. Наверняка, вы сталкивались с ситуацией, когда при построении графика большинство элементов ряда данных близки по значению, но в массиве также присутствует пара значений, отличающихся на порядок или два от всех остальных. В этом случае построенная диаграмма не будет читабельной, а ее внешний вид не будет отражать реального положения дел. Выйти из положения можно, разделив ось 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

Я рассмотрел эту технику в недавнем сообщении в блоге:Сломанная ось Y в диаграмме Excel

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

В статье выше я предлагаю альтернативный подход, который состоит в том, чтобы сделать диаграмму панели, одну с масштабом Y, которая включает в себя значения до разрыва, другую панель с масштабом оси Y, который включает в себя все значения, с уменьшенными значениями против оси X. Панельная диаграмма требует определенных усилий, не столько, сколько ломаная ось, и, если хотите, вы можете просто создать две отдельные диаграммы вместо одной диаграммы.