Как сделать вертикальный график в excel

Вертикальная линия на гистограмме в EXCEL

history 25 ноября 2017 г.
    Группы статей

  • Диаграммы и графики

Чтобы совместить вертикальную линию с гистограммой потребуется объединить 2 типа диаграмм: точечную и гистограмму. Покажем как это сделать.

Требуется получить диаграмму вот такого вида:

Гистограмма состоит из 6 столбцов. Вертикальная линия должна быть динамической, т.е. принимать заданные значения от 1 до 6 (порядковый номер максимальной категории, самого правого столбца гистограммы) в зависимости от заданного на листе значения. Например, при значении =1, вертикальная линия будет точно на первой категории.

В файле примера приведена сама диаграмма и исходные таблицы.

Сначала строим обычную гистограмму с группировкой (см. статью Основы построения диаграмм в MS EXCEL ). Затем добавим диаграмме еще один ряд. Для этого выделите диаграмму, выберите вкладку Конструктор , нажмите кнопку меню Выбрать данные . В качестве значений введите ссылку на любую ячейку со значениями будущей вертикальной линии, например, первое значение Х (ячейка А19 , желтая). Подробнее о построении диаграмм с несколькими рядами см. Построение диаграмм с несколькими рядами данных в MS EXCEL .

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

У нового ряда укажите для значений Х и Y нужные ссылки.

Чтобы вывести подпись у верхней точки вертикальной линии — применим трюк. В качестве имени ряда укажем ячейку со значением Х. Далее, через вкладку Макет установим подпись для точек вертикальной линии — Имя ряда .

У вертикальной линии нужно убрать маркеры и добавить линию соединяющую 2 точки. Диаграмма готова.

СОВЕТ : Гистограмму можно легко трансформировать в диаграмму типа График. Кликните на него правой клавишей мыши и в появившемся меню измените тип диаграммы у ряда на График .

График с вертикальной «зеброй»

Как на графике выделить данные с помощью вертикальной «зебры»? В таблицу, на основе которой строится график, необходимо добавить дополнительную строку с координатами фоновых столбцов. Немного манипуляций и все получится.

1. Исходные данные.
Главный секрет: заранее создать дополнительную строку чередующихся «0» и «1» — это будущие фоновые колонки.

2. Строим обычный график на основе приведенной выше таблицы. Самый простой способ: выделить таблицу → вкладка Вставка → График

3. Далее нужно изменить тип бордовой линии на Гистограмму с накоплением и сделать пару манипуляций.

Но есть нюанс. Во-первых, в Excel 2013 и Excel 2007-2010 это делается по-разному. Во-вторых, полученную гистограмму нужно поставить по вспомогательной оси. В-третьих, вспомогательную ось нужно ограничить по максимуму и скрыть.

4. Меняем тип диаграммы для бордовой линии.
4.1. Для Excel 2013
Выделяем диаграмму (можно выделить саму изменяемую линию). Появятся две дополнительные вкладки. Переходим КОНСТРУКТОР или щелкаем кнопкой мышки и выбираем Изменить тип диаграммы.
Если вы встали на линию, окно будет таким:

zebra4

Если выбрали весь график, тип диаграммы может поменяться. Ничего страшного. Сделайте так: для бордовой линии нужно выбрать Гистограмма с накоплением. Здесь же можете поставить галочку «Вспомогательная ось» — тоже для бордовой линии. (Можно поступить аналогично Excel 2007-2010 и сделать это в формате ряда данных).

4.2. Для Excel не 2013
ОБЯЗАТЕЛЬНО выделяем бордовую линию (только ее). Появятся 3 вкладки. Вкладка Конструктор → Изменить тип диаграммы.
Здесь нужно выбрать раздел Гистограмма, а затем — Гистограмма с накоплением. В этом окне нет возможности «запустить» данные по вспомогательной оси, поэтому после того, как бордовая линия будет преобразована, нужно нажать по ней правой кнопкой мышки.

zebra5

5. Настройка вспомогательной оси и фоновых блоков
В итоге, должно получиться что-то типа такой диаграммы. Если у вас линия и такие колонки — значит правильно.

Если у вас только колонки или линии, как на рисунке ниже, значит на каком-то шаге была ошибка — не выделили только бордовую линию или забыли поставить вспомогательную ось.

Остается настроить вспомогательную ось и блоки.
Настройка оси: щелкаем мышкой по вспомогательной оси → Свойства → Задаем максимальное значение оси = 1. Там же находим раздел Подписи → Нет.

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

Настройка столбцов (блоков) диаграммы: выделяем мышкой любой блок → Боковой зазор → Бегунок полностью влево.

Заливка блока: Сплошная заливка → Цвет серый (градиент выбирайте сами — как вам больше нравится) → Прозрачность — чтобы было видно сетку.

Ссылка на основную публикацию