Перейти к содержимому

Kutools для Office — один пакет. Пять инструментов. Выполняйте больше.

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

Author Siluvia Last modified

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

a forecast chart in Excel

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

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

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

steps of creating a simple forecast chart in Excel

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

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

steps of creating a simple forecast chart in Excel

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

steps of creating a simple forecast chart in Excel

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

steps of creating a simple forecast chart in Excel

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

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

steps of creating a simple forecast chart in Excel

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

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

steps of creating a simple forecast chart in Excel

5.5) В разделе Граница измените ширину границы на 1,5 пункта в поле Ширина;
5.6) В выпадающем списке Тип штриха выберите Квадратная точка;

steps of creating a simple forecast chart in Excel

5.7) Перейдите на вкладку Линия, а затем выберите Квадратная точка из выпадающего списка Тип штриха.

steps of creating a simple forecast chart in Excel

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

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

steps of creating a simple forecast chart in Excel

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

steps of creating a simple forecast chart in Excel

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

steps of creating a simple forecast chart in Excel

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

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

steps of creating a forecast chart based on a threshold value

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

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

steps of creating a forecast chart based on a threshold value

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

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

steps of creating a forecast chart based on a threshold value

2.2) Выберите первую пустую ячейку второго столбца (фактический столбец), введите в нее формулу ниже и нажмите клавишу Enter. Затем перетащите маркер автозаполнения результирующей ячейки вниз до нижней части таблицы.
=ЕСЛИ(B2,B2,НД())

steps of creating a forecast chart based on a threshold value

2.3) Выберите первую пустую ячейку столбца Прогноз, введите в нее формулу ниже и нажмите клавишу Enter. Затем перетащите маркер автозаполнения результирующей ячейки вниз до нижней части таблицы.
=ЕСЛИ(B2,ЕСЛИ(B3,НД(),B2),C2)

steps of creating a forecast chart based on a threshold value

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

steps of creating a forecast chart based on a threshold value

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

steps of creating a forecast chart based on a threshold value

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

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

steps of creating a forecast chart based on a threshold value

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

steps of creating a forecast chart based on a threshold value

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

steps of creating a forecast chart based on a threshold value

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

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

steps of creating a forecast chart based on a threshold value

5.6) Нажмите вкладку Линия, а затем выберите опцию Без линии в разделе Линия.

steps of creating a forecast chart based on a threshold value

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

steps of creating a sforecast chart based on a threshold value

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

6.1) Нажмите значок Заливка и линия;
6.2) Нажмите вкладку Маркер;
6.3) В разделе Опции маркера выберите опцию Нет;
6.4) Нажмите вкладку Линия, а затем выберите новый цвет для линии. Смотрите скриншоты:

steps of creating a forecast chart based on a threshold value

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

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

steps of creating a forecast chart based on a threshold value

Легкое создание прогнозной диаграммы в Excel

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

Скачать пример файла

sample of creating a simple forecast chart in Excel

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