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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: exceltable.com

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

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

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

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

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

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

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

Читайте также:  Как в excel текст в дату

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

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

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

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

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

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

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

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

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

Нажмите кнопку Полосы повышения и понижения, а затем щелкните Полосы повышения и понижения.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Источник: support.office.com

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

Ранее я показал, как добавить линию на график. Аналогично может возникнуть потребность в добавлении контрольной (опорной) линии на гистограмму. Например, описывая распределение объема продаж по диапазонам прибыльности сделок, захочется добавить линию, показывающую предельно допустимую прибыльность (рис. 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%) от левого края…

Источник: baguzin.ru

5 приемов, как улучшить диаграммы в Excel

Автор книги «Мастерство визуализации данных» и создатель блога data.vis Ольга Базалева поделилась приемами создания профессиональных диаграмм в Excel.

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

Прием 1: задать перекрытие столбцов на диаграмме

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

  1. Клик правой кнопкой мыши — ПКМ — по любому столбцу диаграммы;
  2. Формат ряда данных;
  3. Перекрытие рядов.

Чтобы одни столбцы полностью перекрывали другие, выбирайте перекрытие 100%. Можно настроить другое соотношение перекрытия, например, 70%.

Прием 2: уменьшить расстояние между столбцами

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

  1. ПКМ по ряду данных;
  2. Формат ряда данных;
  3. Выбрать боковой зазор в процентах.

Чтобы сохранить визуальную целостность и сделать акцент на данных, выбирайте зазор до 25-50%.

EXCEL

  • Учитесь анализировать любые данные и приводить таблицы в порядок
  • Работайте с любыми отчетами и формулами Excel

Прием 3: отобразить среднее скользящее

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

  1. ПКМ по линии графика;
  2. Добавить линию тренда;
  3. Выбрать скользящее среднее;
  4. Установить период.

Инструмент сглаживает колебания и позволяет четче увидеть тренд. Можно построить скользящее среднее за 21 день, 11, 5 или выбрать любое другое окно. Все зависит от уровня сглаживания, который вы хотите увидеть. Чем шире окно, тем более сглаженная линия.

Прием 4: оставить одну подпись на несколько диаграмм

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

  1. Построить первую диаграмму;
  2. Расположить следующие поверх первой основной;
  3. Убрать с листа все лишние элементы: подписи рядов, линии сетки, белую заливку области диаграмм.

Подробнее процесс показан в видеоуроке.

Прием 5: добавить вспомогательную ось

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

Источник: netology.ru

Как построить гистограмму в Excel по данным таблицы

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

Читайте также:  Excel если больше или меньше в excel

Как построить гистограмму в Excel

  • выделите область с данными таблицы, которые вы хотите отразить на гистограмме. Важно выделить все заголовки в столбцах и строках;

  • перейдите во вкладку “Вставка” на Панели инструментов, затем щелкните по пункту меню “Гистограмма”;

  • выберите тип гистограммы:

  • на листе с данными таблицы появится гистограмма:

Стиль и внешний вид гистограммы

После того, как вы создали гистограмму, вам может потребоваться внести корректировки в то, как выглядит ваш график. Для изменения дизайна и стиля используйте вкладку “Конструктор”. Эта вкладка отображается на Панели инструментов, когда вы выделяете левой клавишей мыши гистограмму. С помощью дополнительных настроек в разделе “Конструктор” вы сможете:

  • добавить заголовок и другие дополнительные данные для отображения. Для того, чтобы добавить данные на график, кликните на пункт “Добавить элемент диаграммы”, затем, выберите нужный пункт из выпадающего списка:

  • для редактирования элемента гистограммы, например заголовка – дважды кликните на него и внесите корректировки;

  • если вы не хотите добавлять элементы по отдельности, то можно воспользоваться пунктом меню “Экспресс-макет” и выбрать подготовленные системой наиболее популярные наборы элементов гистограммы;

  • в Excel также доступные несколько подготовленных стилей гистограммы, выбрать которые вы можете в разделе “Стили диаграмм” на вкладке “Конструктор”;

Вы также можете использовать кнопки быстрого доступа к редактированию элементов гистограммы, стиля и фильтров:

Как сменить строки и столбцы в гистограмме

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

Для того чтобы сменить порядок строк и столбцов в гистограмме проделайте следующие шаги:

  • Выберите гистограмму, которую вы хотите отредактировать;
  • На вкладке “Конструктор” выберите пункт “Строка/Столбец”;

  • Строки и столбцы в графике будут сменены. В нашем примере данные сгруппированы по жанрам, а столбцы по годам.

Как переместить гистограмму в Excel

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

  • Выделите гистограмму, которую вы хотите переместить;
  • Нажмите на пункт “Дизайн” на Панели инструментов, затем выберите пункт “Переместить диаграмму”;

  • В диалоговом окне выберите куда вы, хотите переместить гистограмму и нажмите ОК.

  • После этого, гистограмма будет перенесена в новое место назначения. В нашем примере, это новый лист.

Источник: excelhack.ru

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

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

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

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

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

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

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

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

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

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

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

Источник: excel2.ru