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

АвторSiluviaДата изменения2024-11-06

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

прогнозный график в Excel

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

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

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

этапы создания простого прогнозного графика в Excel

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

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

этапы создания простого прогнозного графика в Excel

2. Выделите все данные таблицы и нажмите Вставка > Диаграммы «График» или «Область» > График с маркерами.

этапы создания простого прогнозного графика в Excel

3. Щелкните правой кнопкой мыши синюю линию (ряд «Факт») и в контекстном меню выберите пункт Формат ряда данных.

этапы создания простого прогнозного графика в Excel

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

4,1) Нажмите значок Заливка и границы;

4,2) Нажмите Маркер;

4,3) Разверните раздел Параметры маркера, выберите опцию Встроенные, оставьте выбранным символ круглой точки в раскрывающемся списке Типи замените значение Оригинальный размер на 10в поле Размер.

этапы создания простого прогнозного графика в Excel

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

5,1) Нажмите значок Заливка и границы;

5,2) Нажмите Маркер;

5,3) В разделе Параметры маркеравыберите опцию Встроенные, оставьте выбранным символ круглой точки и установите размер 10в поле Размер;

5,4) В разделе Заливкавыберите значение Белыйв раскрывающемся списке Цвет;

этапы создания простого прогнозного графика в Excel

5,5) В разделе Границаустановите ширину границы 1,5 птв поле Ширина;

5,6) В раскрывающемся списке Тип штрихавыберите пункт КвадратТочка;

этапы создания простого прогнозного графика в Excel

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

этапы создания простого прогнозного графика в Excel

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

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

этапы создания простого прогнозного графика в Excel

После этого маркер за сентябрь изменится, как показано ниже.

этапы создания простого прогнозного графика в Excel

7. При необходимости измените заголовок диаграммы. Простая линейная диаграмма прогнозных данных готова.

этапы создания простого прогнозного графика в Excel

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

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

этапы создания прогнозного графика на основе порогового значения

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

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

этапы создания прогнозного графика на основе порогового значения

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

2,1) Таблица состоит из пяти столбцов: первый столбец содержит те же Значения ряда, что и исходная таблица; второй, третий и четвёртый столбцы имеют те же заголовки, что и исходная таблица; данные пятого столбца используются для отображения значений, находящихся ниже пороговой линии.

этапы создания прогнозного графика на основе порогового значения

2,2) В Select the first blank cell of the second column (the actual column) введите приведённую ниже формулу и нажмите клавишу Enter. Затем перетащите маркер автозаполненияячейки с результатом вниз до конца таблицы.

=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) В разделе Границаукажите тот же цвет, что и у Цвет заполнения;

этапы создания прогнозного графика на основе порогового значения

5,6) Перейдите на вкладку Линияи выберите опцию Нет линиив разделе Линия.

этапы создания прогнозного графика на основе порогового значения

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

этапы создания прогнозного графика на основе порогового значения

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

6,1) Щелкните значок Заливка и линия;

6,2) Перейдите на вкладку Маркер;

6,3) В разделе Параметры маркеравыберите опцию Нет;

6,4) Перейдите на вкладку Линияи выберите новый цвет для линии. См. снимки экрана:

этапы создания прогнозного графика на основе порогового значения

7. При необходимости измените заголовок диаграммы.

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

этапы создания прогнозного графика на основе порогового значения

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

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