Как добавить линию на гистограмму excel

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

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

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

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

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

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

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

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

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

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

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

Добавление линий ряда, линий проекции, коридоров колебания или полос повышения и понижения на диаграмму

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

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

Новый формат диаграммы в Excel ? Щелкните здесь для 5 минут бесплатные учебные о том, как форматирование диаграммы.

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

Предварительно определенных типов линии и линейчатые диаграммы, которые можно добавить в диаграмму

На некоторые диаграммы можно добавить следующие линии и полосы:

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

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

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

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

Добавление линий или полос

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

Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.

На вкладке Макет в группе Анализ выполните одно из следующих действий:

Нажмите кнопку линии и выберите нужный тип линии.

Примечание: Типы другую строку доступны для различных типов диаграмм.

Нажмите кнопку Понижения и нажмите кнопку Понижения.

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

Удаление линий или полос

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

Откроется панель Работа с диаграммами с дополнительными вкладками Конструктор, Макет и Формат.

На вкладке Макет в группе Анализ нажмите кнопку линии или Понижения и нажмите кнопку Нет для удаления строки или погрешностей на диаграмме.

Совет: Вы также можете удалить линии или полосы сразу же после их добавления, нажав кнопку Отменить на панели быстрого доступа или клавиши CTRL+Z.

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

Добавление других линий

Это действие применяется в Microsoft Word для Mac только: В меню Вид выберите пункт Разметка страницы.

На диаграмме выберите ряд данных, который требуется добавить линию и откройте вкладку Конструктор диаграмм.

Например, щелкните одну из линий графика. Будут выделены все маркеры данных этого ряда.

Нажмите кнопку Добавить элемент диаграммы и нажмите кнопку Сетка.

Выберите параметр строки, которые вы и нажмите кнопку Дополнительные параметры сетки.

В зависимости от типа диаграммы некоторые параметры могут быть недоступны.

Удаление других линий

Это действие применяется в Microsoft Word для Mac только: В меню Вид выберите пункт Разметка страницы.

Щелкните диаграмму с линиями, а затем откройте вкладку Конструктор диаграмм.

Нажмите кнопку Добавить элемент диаграммы, выберите команду линии сетки и нажмите кнопку Дополнительные параметры сетки.

Выберите Нет линии.

Также можно щелкнуть линию и нажать кнопку DELETE.

Как добавить линию на гистограмму

Ранее я показал, как добавить линию на график. Аналогично может возникнуть потребность в добавлении контрольной (опорной) линии на гистограмму. Например, описывая распределение объема продаж по диапазонам прибыльности сделок, захочется добавить линию, показывающую предельно допустимую прибыльность (рис. 1):

Рис 1. Распределение выручки (по оси ординат) по диапазонам нормы валовой прибыли (по оси абсцисс).

Скачать заметку в формате Word, примеры в формате Excel

На рис.1 я провел эту линию путем вставки фигуры (линии):

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

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

Обобщим данные в сводной таблице

Представим данные в сводной таблице в виде долей. (Так мы сможем сравнивать разных клиентов, у которых абсолютное число накладных колеблется.) Для этого встанем на любую клеточку в столбце сводной таблицы «Количество по полю накладная», кликнем правой кнопкой мыши, выберем в контекстном меню пункт «Параметры полей значений». В окне «Параметры поля значений» перейдем на вкладку «Дополнительные вычисления» и выберем «Доля от суммы по столбцу»:

Скопируем данные из сводной таблицу в обычную таблицу (к сожалению манипуляции, которые мы хотим выполнить далее, невозможны в отношении сводной таблицы). Построим стандартную гистограмму на основе обычной таблицы и отформатируем её, как показано ниже. Важно! Вертикальная ось не удалена, а сделана невидимой. Для этого в формате оси выбран «Цвет линии оси» – «Нет линии», «Основные деления» – «Нет», «Подписи оси» – «Нет»:

Проведем вертикальную линию, отделяющую столбцы гистограммы, относящиеся к своевременной оплате – по 30-й день включительно. Для этого создадим дополнительный набор данных, смысл которых станет понятен чуть позже. Скопируйте в буфер памяти таблицу (ячейки L27:M29), выделите диаграмму и выберите «Специальная вставка»:

В появившемся окне установите «Добавить значение как» в положение «новые ряды», «Значения Y» в положение «в столбцах»; поставьте галочки в «Имена рядов в первой строке» и «Категории (подписи оси Х) в первом столбце:

Должно получиться так:

Выделите синий столбик и измените тип диаграммы для ряда на точечную без маркеров:

На диаграмме появились две вспомогательные оси: вертикальная и горизонтальная. Задайте для обеих минимальное и максимальное значения ноль и 100, а в формате осей выберите «Цвет линии» – «Нет линии», «Основные деления» – «Нет», «Подписи оси» – «Нет». Вот, что в итоге получилось:

Пояснение. Откуда взялись данные для построения контрольной линии. В точечных диаграммах использование значений 0–100 позволяет задавать расположение линии в процентах. В рассмотренном примере, значение 37,5 (расположенное в ячейках L28:L29) означает, что линия размещается в точке (по оси абсцисс), представляющей 37,5% длины оси категорий. Поскольку в нашей гистограмме было 8 столбцов, для отделения первых трех, линию нужно расположить на расстоянии 3/8 (или 37,5%) от левого края…

Как построить гистограмму в Excel и объединить ее с графиком

Гистограмма в Excel – это способ построения наглядной диаграммы, отражающей изменение нескольких видов данных за какой-то период времени.

С помощью гистограммы удобно иллюстрировать различные параметры и сравнивать их. Рассмотрим самые популярные виды гистограмм и научимся их строить.

Как построить обновляемую гистограмму?

Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.

Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.

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

Если мы будем вносить в таблицу изменения, внешний вид гистограммы тоже будет меняться. Для примера вместо 1400 в январе по кефиру поставим 4000. Видим, как зеленый столбец полетел вверх.

Гистограмма с накоплением

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

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

Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.

Аналогично можно производить и другие изменения гистограммы, что мы и сделали:

• поменяли шрифта на Arial и изменили его цвет на фиолетовый;
• сделали подчеркивание пунктирной линией;
• переместили легенду немного выше;
• добавили подписи к столбцам.

Как объединить гистограмму и график в Excel?

Некоторые массивы данных подразумевают построение более сложных диаграмм, которые совмещают несколько их видов. К примеру, гистограмма и график.

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

Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.

Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.

Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.

Видим, что график сразу изменился. Теперь фиолетовый столбик с выручкой имеет свою область значения (справа).

Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.

Получаем вполне наглядную диаграмму, представляющую собой объединение гистограммы и графика. Видим, что максимальная выручка была в январе и ноябре, а минимальная – в августе.

Аналогично можно совмещать любые виды диаграмм.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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