Как разорвать ось диаграммы в Excel?

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

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

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

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

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

5. В панели Формат оси введите 160 в Максимум поле в разделе Границы , и в группе Число введите [<=80]0;;; в Формат кода поле и нажмите кнопку Добавить , а затем закройте панель.

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

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

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

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

• Использование встроенных функций Excel (16 шагов)
• Использование Truncate the Y-axis Chart из Kutools для Excel (3 шага)

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

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

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

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

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

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

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

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

8. В появившемся диалоговом окне Выбор источника данных нажмите кнопку Добавить . Теперь в открывшемся диалоговом окне Редактирование серии выберите Ячейку I1 (Для разорванной оси Y) как имя серии, и выберите Диапазон K3:K13 (столбец Ypos) как значения серии, и нажмите OK > OK чтобы закрыть два диалоговых окна.

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

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

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

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

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

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

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

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

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

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

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

Разрыв оси путем добавления фиктивной оси на графике с использованием Truncate the Y-axis Chart из Kutools для Excel (3 шага)

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

1. Нажмите Kutools > Диаграммы > Сравнение различий > Truncate the Y-axis Chart чтобы открыть диалоговое окно настроек.

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

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

Связанные статьи

Создание шаблона диаграммы колоколообразной кривой в Excel
Два решения для создания диаграммы колоколообразной кривой (называемой нормальными распределениями вероятностей в статистике) в Excel и сохранения диаграммы колоколообразной кривой как шаблона диаграммы.

 

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

 

Добавление горизонтальной средней линии к диаграмме в Excel
В Excel вы часто создаете диаграмму для анализа тенденции данных. Но иногда вам нужно добавить простую горизонтальную линию через диаграмму, которая представляет собой среднюю линию построенных данных, чтобы вы могли четко и легко видеть среднее значение данных.

 

Больше учебных пособий по диаграммам...
50+ учебных пособий по созданию всех видов диаграмм пошагово в Excel: гистограмма из ячеек да/нет, диаграмма колоколообразной кривой, контрольная диаграмма, плавающая гистограмма, искровая диаграмма выигрыша/проигрыша, воронкообразная диаграмма, диаграмма кумулятивного среднего, пузырьковая диаграмма и т.д.