Как на гистограмму добавить линейный график
Как на гистограмму добавить линейный график
Как создать столбчатую диаграмму, наложенную на другую гистограмму в Excel?
Когда мы создаем кластеризованную столбчатую или столбчатую диаграмму с двумя рядами данных, эти две полосы ряда данных будут отображаться рядом. Но иногда нам нужно использовать наложенную или перекрывающуюся гистограмму, чтобы более четко сравнить две серии данных. В этой статье я расскажу о том, как создать перекрывающуюся гистограмму в Excel.
Пожалуйста, сделайте следующие шаги, чтобы выполнить эту задачу.
1. Выберите диапазон данных, в котором вы хотите создать перекрывающуюся диаграмму, а затем щелкните Вставить > Вставить столбец или гистограмму > Кластерная диаграмма, см. снимок экрана:
2. После создания кластерной диаграммы щелкните правой кнопкой мыши одну полосу серии и выберите Изменить тип диаграммы серии из контекстного меню см. снимок экрана:
3. В Изменить тип диаграммы диалоговое окно, нажмите Комбо на левой панели под Все диаграммы Вкладка, а затем нажмите кнопку Таблица пользовательских комбинаций в правом разделе и установите один флажок в Вторичная ось список, см. снимок экрана:
4. Затем нажмите OK чтобы закрыть диалоговое окно, выберите строку ряда данных и щелкните правой кнопкой мыши, выберите Форматировать ряд данных… Из контекстного меню, см. Снимок экрана:
5. В всплывающем Форматировать ряд данных панели, нажмите Варианты серий кнопку, отрегулируйте Ширина зазора Чтобы сделать планку шире или уже, см. снимок экрана:
6. Затем нажмите Заливка и линия нажмите кнопку, нажмите Заполнять расширить его, в Заполнять раздел, выберите Сплошная заливка вариант, а затем выберите тот же цвет с другой серией данных, в то же время увеличьте прозрачность, вы получите следующую перекрывающуюся гистограмму:
Как построить гистограмму в Excel и объединить ее с графиком
Гистограмма в Excel – это способ построения наглядной диаграммы, отражающей изменение нескольких видов данных за какой-то период времени.
С помощью гистограммы удобно иллюстрировать различные параметры и сравнивать их. Рассмотрим самые популярные виды гистограмм и научимся их строить.
Как построить обновляемую гистограмму?
Имеем данные по продажам разных видов молочной продукции по каждому месяцу за 2015 год.
Построим обновляемую гистограмму, которая будет реагировать на вносимые в таблицу изменения. Выделим весь массив вместе с шапкой и кликнем на вкладку ВСТАВКА. Найдем так ДИАГРАММЫ – ГИСТОГРАММА и выберем самый первый тип. Он называется ГИСТОГРАММА С ГРУППИРОВКОЙ.
Получили гистограмму, размер поля которой можно менять. На такой диаграмме наглядно видно, например, что самые большие продажи были по молоку в ноябре, а самые маленькие – по сливкам в июне.
Если мы будем вносить в таблицу изменения, внешний вид гистограммы тоже будет меняться. Для примера вместо 1400 в январе по кефиру поставим 4000. Видим, как зеленый столбец полетел вверх.
Гистограмма с накоплением
Теперь рассмотрим, как построить гистограмму с накоплением в Excel. Еще один тип гистограмм, который позволяет отразить данные в процентном соотношении. Строится она точно так же, но выбирается другой тип.
Получаем диаграмму, на которой можно видеть, что, например, в январе больше продано молока, чем кефира или сливок. А в августе, по сравнению с другими молочными продуктами, молока было продано мало. И т.п.
Гистограммы в Excel можно изменять. Так, если мы кликнем правой кнопкой мыши в пустом месте диаграммы и выберем ИЗМЕНИТЬ ТИП, то сможем несколько ее видоизменить. Поменяем тип нашей гистограммы с накоплением на нормированную. Результатом будет та же самая диаграмма, но по оси Y будут отражены соотношения в процентном эквиваленте.
Аналогично можно производить и другие изменения гистограммы, что мы и сделали:
Как объединить гистограмму и график в Excel?
Некоторые массивы данных подразумевают построение более сложных диаграмм, которые совмещают несколько их видов. К примеру, гистограмма и график.
Рассмотрим пример. Для начала добавим к таблице с данными еще одну строку, где прописана выручка за каждый месяц. Она указана в рублях.
Теперь изменим существующую диаграмму. Кликнем в пустом месте правой кнопкой и выберем ВЫБРАТЬ ДАННЫЕ. Появится такое поле, на котором будет предложено выбрать другой интервал. Выделяем всю таблицу снова, но уже охватывая и строку с выручкой.
Excel автоматически расширил область значений по оси Y, поэтому данные по количеству продаж остались в самом низу в виде незаметных столбиков.
Но такая гистограмма неверна, потому что на одной диаграмме у нас значатся числа в рублевом и количественном эквиваленте (рублей и литров). Поэтому нужно произвести изменения. Перенесем данные по выручке на правую сторону. Кликнем по фиолетовым столбикам правой кнопкой, выберем ФОРМАТ РЯДА ДАННЫХ и отметим ПО ВСПОМОГАТЕЛЬНОЙ ОСИ.
Видим, что график сразу изменился. Теперь фиолетовый столбик с выручкой имеет свою область значения (справа).
Но это все равно не очень удобно, потому что столбики почти сливаются. Поэтому произведем еще одно дополнительное действие: кликнем правой кнопкой по фиолетовым столбцам и выберем ИЗМЕНИТЬ ТИП ДИАГРАММЫ ДЛЯ РЯДА. Появится окно, в котором выбираем график, самый первый тип.
Получаем вполне наглядную диаграмму, представляющую собой объединение гистограммы и графика. Видим, что максимальная выручка была в январе и ноябре, а минимальная – в августе.
Аналогично можно совмещать любые виды диаграмм.
Как на гистограмму добавить линейный график
Создать диаграмму со средней / целевой линией в Excel
В Excel обычной задачей может быть вставка горизонтальной или вертикальной линии в диаграмму, горизонтальная или вертикальная линия может ссылаться на некоторое целевое или среднее значение. Этот тип линии позволяет легко сравнивать фактические значения с целевым или средним значением, и с помощью этой линии вы можете легко увидеть, где значения находятся выше и ниже этого эталонного значения, как показано на скриншотах ниже. В этой статье я расскажу о том, как создать столбчатую или гистограмму со средней или целевой линией в Excel.
Создать столбчатую диаграмму со средней или целевой линией по горизонтали
Чтобы создать столбчатую диаграмму со средней или целевой линией по горизонтали, выполните следующие действия:
1. Если вы хотите создать столбчатую диаграмму со средней линией, сначала вы должны вычислить среднее значение списка данных, введите приведенную ниже формулу в пустую ячейку, а затем перетащите дескриптор автозаполнения, чтобы скопировать формулу в другой ячейки, см. снимок экрана:
2. Затем выберите диапазон данных, включая средние значения, и нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец, см. снимок экрана:
3. И столбчатая диаграмма вставлена в рабочий лист, вы можете удалить ненужные элементы, такие как заголовок диаграммы, линии сетки, как вам нужно, см. Скриншоты:
 |  |  |
4. Затем щелкните правой кнопкой мыши средний ряд данных (оранжевые полосы) и выберите Изменить тип диаграммы серии из контекстного меню см. снимок экрана:
5. В Изменить тип диаграммы диалоговое окно под Выберите тип диаграммы и ось для ряда данных список, выберите Линейный график из Тип диаграммы выпадающий список для Средняя ряд данных, см. снимок экрана:
6. А затем нажмите OK Кнопка, вы получите диаграмму, как показано на скриншоте ниже:
7. Затем вы можете добавить метку данных для этой средней линии, дважды щелкните, чтобы выбрать крайнюю правую точку данных линии, см. Снимок экрана:
8. А затем нажмите Элементы диаграммы, проверить Этикетки данных поле, и метка данных была вставлена для средней линии, см. снимок экрана:
Добавьте целевую линию с разными значениями в столбчатую диаграмму
Иногда вам может потребоваться сравнить фактические значения с целевыми значениями, которые различаются для каждой строки, в этом случае для более четкой визуализации целевых значений вам может помочь приведенный ниже тип диаграммы.
 | |  |
Чтобы создать этот тип диаграммы, сделайте следующее:
1. Сначала выберите диапазон данных, а затем нажмите Вставить > Вставить столбец или гистограмму > Кластерный столбец чтобы вставить диаграмму.
2. Затем щелкните правой кнопкой мыши целевой ряд данных (оранжевые полосы) и выберите Изменить тип диаграммы серии из контекстного меню в Изменить тип диаграммы диалоговое окно, выберите Линейный график для целевого ряда данных из Тип диаграммы выпадающий список, см. снимок экрана:
3, Затем нажмите OK кнопку, а затем дважды щелкните целевую линию и выберите Форматировать ряд данных, см. снимок экрана:
4. В открытом Форматировать ряд данных панель, под Заливка и линия вкладка, выберите Нет линии из линия раздел, см. снимок экрана:
5. Все еще в Заливка и линия вкладку, переключитесь на Маркер раздел, выберите Встроенный из Параметры маркера раздел, а затем выберите горизонтальную полосу в Тип раскрывающийся список, наконец, установите размер, соответствующий ширине столбцов в Size box, см. снимок экрана:
6. А затем установите цвет для целевых линий, как вам нравится, из Заполнять раздел, см. снимок экрана:
7. Наконец, вы можете удалить ненужные элементы, такие как заголовок диаграммы, линии сетки по мере необходимости.
Динамическое создание столбчатой диаграммы со средней линией с помощью переключателей
Если у вас есть большая таблица с продажами продукта за несколько лет, теперь вы хотите показать среднюю линию за каждый год на диаграмме. Для решения этой задачи вы можете использовать диаграмму со средней линией, щелкнув переключатель, как показано ниже в демонстрации. При выборе соответствующего года график со средней линией будет отображаться динамически.
Чтобы создать эту динамическую диаграмму, сначала вы должны вставить несколько переключателей, а затем создать вспомогательную таблицу данных, на которой основана диаграмма, и, наконец, вставить ссылку диаграммы на переключатель. Пожалуйста, выполните следующие действия:
Сначала вставьте несколько переключателей
1. Нажмите разработчик > Вставить > Кнопка выбора (элемент управления формой), а затем нарисуйте четыре переключателя и отредактируйте текст по своему усмотрению, см. скриншоты:
 | |  |
2. Затем щелкните правой кнопкой мыши первую радиокнопку и выберите Управление форматом из контекстного меню см. снимок экрана:
3. В открытом Форматировать объект диалоговое окно под Control вкладку, выберите ячейку, чтобы связать переключатель с Ячейка текстовое поле ссылки и щелкните OK чтобы закрыть его, см. снимок экрана:
Во-вторых, вставьте вспомогательную таблицу данных для создания диаграммы на основе
4. Скопируйте названия продуктов из исходной таблицы в новый диапазон, а затем примените приведенную ниже формулу в пустую ячейку рядом с новыми скопированными данными, а затем перетащите дескриптор заполнения, чтобы получить результат, см. Снимок экрана:
5. Теперь, когда вы нажимаете один переключатель, соответствующий год данных будет отображаться автоматически, как показано ниже:
6. А затем вы можете рассчитать средние значения, используя формулу ниже, см. Снимок экрана:
В-третьих, создайте диаграмму на основе новой таблицы вспомогательных данных.
Затем выберите новые созданные данные, а затем примените вышеуказанное шаг 2- шаг 8 метода 1 для создания столбчатой диаграммы со средней линией, и вы получите динамическую диаграмму со средней линией. См. Демонстрацию ниже:
Создание гистограммы со средней или целевой линией по вертикали
В этом разделе будет рассказано, как создать гистограмму со средним значением или целевым значением по вертикали, пожалуйста, сделайте следующее:
1. Сначала создайте таблицу вспомогательных данных, введите X и Y в две ячейки, а затем вычислите средние значения в ячейках D2 и D3, см. снимок экрана:
2. Затем введите номер 0 и 1 в ячейки E2 и E3 для значений Y, см. снимок экрана:
3. Затем выберите исходный диапазон данных и нажмите Вставить > Вставить столбец или гистограмму > Кластерный бар, см. снимок экрана:
4. Гистограмма была вставлена в лист, затем щелкните ее правой кнопкой мыши и выберите Выберите данные из контекстного меню см. снимок экрана:
5. В выскочившем Выберите источник данных диалога, нажмите Добавить кнопку, см. снимок экрана:
6. А потом в Редактировать серию диалоговом окне введите название серии в Название серии текстовое поле, а затем выберите ячейки со значениями X (D2: D3 в этом примере) в Значения серии текстовое поле, см. снимок экрана:
7. Нажмите OK > OK чтобы закрыть диалоговые окна, и новый ряд данных теперь добавлен к вашей гистограмме (две оранжевые полосы), а затем щелкните оранжевую полосу правой кнопкой мыши и выберите Изменить тип диаграммы серии, см. снимок экрана:
8. В выскочившем Изменить тип диаграммы диалоговое окно, выберите Скаттер с плавными линиями из Тип диаграммы выпадайте для Средняя ряд данных, см. снимок экрана:
9, Затем нажмите OK чтобы закрыть диалоговое окно, и новый ряд данных преобразуется в точку данных вдоль основной оси Y, затем щелкните полосу правой кнопкой мыши и выберите Выберите данные из контекстного меню см. снимок экрана:
11. Затем Редактировать серию всплывающее окно подсказки, в Значения серии X выберите две ячейки X (D2: D3), а в Значения серии Y выберите две ячейки Y (E2: E3), см. снимок экрана:
12. Нажмите OK > OK чтобы закрыть диалоговые окна, и вы увидите, что к диаграмме добавлена вертикальная средняя линия, см. снимок экрана:
13. Затем щелкните правой кнопкой мыши вторичную вертикальную ось (правая ось) и выберите Ось формата из контекстного меню см. снимок экрана:
14. В открытом Ось формата панель, под Параметры оси в Параметры оси раздел, тип 1.0 в максимальная связанная коробка, и вы получите диаграмму, как показано на скриншоте ниже:
15. Наконец, вы можете удалить вторичную вертикальную ось и ненужные линии сетки по мере необходимости. Теперь гистограмма с вертикальной средней линией готова. Смотрите скриншот:
Добавить целевую линию с разными значениями на гистограмму
Чтобы добавить целевую линию с разными значениями в гистограмму, как показано на скриншотах ниже, выполните следующие действия шаг за шагом:
 | |  |
1. Выберите диапазон данных и нажмите Вставить > Вставить столбец или гистограмму > Кластерный бар чтобы вставить гистограмму, см. снимок экрана:
2. Затем щелкните правой кнопкой мыши целевой ряд данных (оранжевые полосы) и выберите Форматировать ряд данных из контекстного меню см. снимок экрана:
3. В открытом Форматировать ряд данных панель, под Варианты серий вкладку, измените перекрытие серий на 100%, см. снимок экрана:
4. Все еще в Форматировать ряд данных панель, переключитесь на Заливка и линия вкладка, выберите Без заливки из Заполнять раздел, см. снимок экрана:
5, Далее перейдите к эффекты вкладка под тень раздел, установите следующие операции:
6. Теперь переключитесь на Варианты серий вкладку снова, выберите Вторичная ось из Варианты серий раздел и измените ширину зазора на 90% как вам нужно. Смотрите скриншот:
7. Наконец, удалите ненужные элементы, такие как плитка диаграммы, верхняя ось X, линии сетки по мере необходимости, вы получите гистограмму с разными целевыми линиями, как показано ниже:
Добавьте целевую / среднюю линию в столбчатую или линейчатую диаграмму с помощью удобной функции
