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

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

Author: Siluvia Last Modified: 2025-08-07

По умолчанию 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