Создать диаграмму прогноза в Excel

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

Создайте диаграмму прогноза на основе порогового значения
Легко создайте прогноз диаграмма с помощью замечательного инструмента
Загрузите файл примера
Видео: создание диаграммы прогнозов в Excel


Создание простой диаграммы прогнозов

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

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

Примечание: рядом с последней ячейкой с фактическими данными введите те же данные.

2. Выделите данные всей таблицы, нажмите Вставить > Вставить линию или диаграмму с областями > Линия с маркерами .

3. Щелкните правой кнопкой мыши синюю линию (фактический ряд), а затем выберите Форматировать ряд данных из контекстного меню.

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

4.1) Нажмите Заливка и линия ;
4.2) Нажмите
4.3) Разверните Параметры маркера , выберите параметр Встроенный , оставьте круглую точку выбранным в раскрывающемся списке Тип , замените исходный размер на 10. в поле Размер .

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

5.1) Нажмите кнопку Значок “Заливка и линия” ;
5.2) Нажмите
5.3) В Параметры маркера выберите параметр Встроенный , оставьте выделенным символ круглой точки и затем измените размер на 10 i в поле Размер ;
5.4) В разделе Заливка выберите Белый в раскрывающемся списке Цвет ;

р>

5. 5) В разделе Border измените ширину границы на 1.5pt в поле Width ;
5.6) В раскрывающемся списке Тип тире выберите Квадрат Точка ;

5.7) Перейдите к строке и выберите Square Dot в раскрывающемся списке Тип тире .

6. Поскольку данные Sep актуальны, нам необходимо настроить этот маркер для отображения в виде серии Actual.

Дважды щелкните этот маркер, чтобы выбрать только его, и в точке данных формата отдельно выберите тот же цвет Fill и цвет Border , что и цвет серии Actual , а затем выберите Solid из раскрывающегося списка Тип тире .

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

7. Измените название диаграммы по своему усмотрению. Затем создается простая диаграмма прогнозов.


Создание диаграммы прогноза на основе порогового значения

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

1. Добавьте два новых столбца к исходному диапазону данных, которые отдельно содержат данные прогноза и пороговое значение. См. Снимок экрана:

Примечание. Рядом с последней ячейкой с фактическими данными введите то же значение.

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

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

2.2) Выберите первую пустую ячейку второго столбца (фактический столбец), введите в нее формулу ниже и нажмите Введите ключ. Затем перетащите маркер автозаполнения ячейки результата вниз, пока он не достигнет нижней части таблицы.
= IF (B2, B2, NA ())

2.3) Выберите в первой пустой ячейке столбца “Прогноз” введите в нее формулу ниже и нажмите клавишу Enter . Затем перетащите маркер автозаполнения ячейки результата вниз, пока он не достигнет нижней части таблицы.
= IF (B2, IF (B3 , NA (), B2), C2)

2.4) Выберите первую пустую ячейку четвертого столбца, введите в нее приведенную ниже формулу и нажмите клавишу Enter . Затем перетащите маркер автозаполнения ячейки результата вниз до конца таблицы.
= $ D $ 2

2.5) В пятом столбце введите приведенную ниже формулу в сначала пустую ячейку, а затем нажмите клавишу Enter . Затем примените эту формулу к другим ячейкам, перетащив ее дескриптор автозаполнения до конца таблицы.
= IF (B18> D18, NA (), B18)

Примечание. В приведенных выше формулах все ссылочные ячейки взяты из исходного диапазона данных.

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

Затем диаграмма вставляется в текущий рабочий лист, как показано на скриншоте ниже.

4. Теперь вам нужно отформатировать фактический ряд и ряд прогнозов до тех пор, пока диаграмма не отобразится, как показано ниже. Нажмите, чтобы узнать, как это сделать .

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

5.1) Щелкните значок Заливка и линия ;
5.2) Откройте вкладку Маркер ;
5.3) Разверните раздел Параметры маркера , выберите параметр Встроенный , оставьте круглую точку выбранной в поле Тип . раскрывающийся список, а затем измените Размер на 10 ;
5.4) В Заливке укажите новый цвет для маркера. Здесь я выбираю красный цвет, чтобы выделить данные ниже пороговой линии;
5.5) В разделе Border укажите тот же цвет, что и цвет заливки;

5.6) Щелкните строку , а затем выберите параметр Без строки в разделе Line .

Теперь диаграмма отображается, как показано на скриншоте ниже.

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

6.1) Щелкните значок Заливка и линия ;
6.2) Щелкните вкладка Marker ;
6.3) В разделе Параметры маркера выберите параметр Нет ;
6.4) Щелкните вкладку Line , а затем выберите новый цвет для линии. См. снимки экрана:

7. Измените заголовок диаграммы по своему усмотрению.

Затем диаграмма прогноза на основе порогового значения завершена, как показано на скриншоте ниже.


Легко создавать диаграмму прогноза в Excel

Утилита Диаграмма прогноза от Kutools для Excel может помочь вам быстро создать простую диаграмму прогнозов или диаграмма прогноза на основе порогового значения в Excel с помощью нескольких щелчков мышью, как показано в демонстрации ниже.
Загрузите и попробуйте прямо сейчас! 30-дневная бесплатная пробная версия


Загрузите образец файла


Видео: создание диаграммы прогнозов в Excel


Оцените статью
Frestage.ru
Добавить комментарий