Как использовать ПРОГНОЗ в Excel и строить графики тренда

8 апреля 2022 г.

Хотя текущие данные полезны для анализа успеха компании, вы можете задаться вопросом, как она может работать в будущем. Программы для работы с электронными таблицами позволяют использовать ваши данные для создания прогнозов и линий трендов. Понимание того, как выполнить эту задачу, может помочь вам прогнозировать будущую производительность, чтобы предоставить компаниям ценную информацию. В этой статье мы объясним, как использовать ПРОГНОЗ в Excel, и обсудим другие способы прогнозирования тенденций в ваших электронных таблицах.

Зачем создавать прогноз в Excel?

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

Как использовать ПРОГНОЗ в Excel

Вот как использовать функцию ПРОГНОЗ в Excel:

1. Создайте две колонки

Откройте электронную таблицу и озаглавьте два столбца в соответствии с переменными в ваших данных. Введите значения x или независимые переменные в первый столбец. В целях прогнозирования эти значения обычно представляют собой даты в виде дней, месяцев, кварталов или лет. Введите значения y или зависимые переменные во второй столбец. Каждое значение y соответствует одному из значений в первом столбце. Например, рассмотрим этот набор данных, который отображает стоимость дома каждый год.

А

ВС1

Год**Домашняя стоимость**2

2016$304,5543

2017$320,8524

2018 г. 321 4465 долларов США

2019$319,7926

2020$325,6377

2021 327 4888 долларов

Прогноз:

2025

2. Выберите дату, которую вы хотите прогнозировать

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

3. Выберите функцию ПРОГНОЗ

Выберите ячейку справа от ячейки, содержащей дату, которую вы хотите спрогнозировать. В приведенном выше примере это будет ячейка C8. Введите «= ПРОГНОЗ», выберите одну из функций и нажмите «Ввод» на клавиатуре. Если вы не уверены, какой у вас тип функции, выберите опцию «ПРОГНОЗ». Если вы знаете, какой тип функции у вас есть, вы можете выбрать один из конкретных типов. Например, вы можете выбрать «ПРОГНОЗ.ЛИНЕЙНЫЙ», если у вас есть линейная функция, или «ПРОГНОЗ.ETS», если у вас есть экспоненциальная функция.

4. Введите аргументы ПРОГНОЗ

Функция ПРОГНОЗ имеет три аргумента. Вы можете ввести первый аргумент, щелкнув ячейку, содержащую значение x, которое вы хотите спрогнозировать. В приведенном выше примере вы можете щелкнуть ячейку B8, чтобы назначить ее первым аргументом. После первого аргумента добавьте запятую и выделите диапазон ячеек, содержащих известные значения y. Добавьте запятую после этого второго аргумента и выделите диапазон ячеек, содержащих известные значения x. Добавьте закрывающую скобку. В приведенном выше примере функция в ячейке C8 будет выглядеть так:

“=ПРОГНОЗ(B8, C2:C7, B2:B7)”

5. Нажмите «Ввод».

После ввода трех аргументов нажмите «Enter» на клавиатуре. Функция использует введенные вами данные для прогнозирования значения y заданного значения x. В приведенном выше примере функция будет отображать прогнозируемую стоимость дома в 2025 году.

Как создать график прогноза с помощью функции «Линия тренда»

Вот как создать график прогноза с помощью функции «Линия тренда»:

1. Вставьте базовую линейную диаграмму

Создайте два столбца, один для значений x ваших данных и один для значений y. Значения x обычно являются датами, поскольку они являются независимыми переменными, а значения y указывают на производительность в эти даты. Вводим эти данные в столбцы, нажимаем вкладку «Вставка» и переходим в группу «Графики». Наведите указатель мыши на значок «Вставить линию или диаграмму с областями» и выберите «Линия» под «2-D линия».

2. Выберите подходящий вариант линии тренда

Щелкните правой кнопкой мыши график и выберите «Добавить линию тренда». В меню справа установите флажок «Отображать значение R-квадрата на графике». Это действие вызывает появление на графике коэффициента детерминации, который представляет собой число от нуля до единицы, указывающее точность линии тренда. Нажмите на каждый из параметров линии тренда и просмотрите их значения R-квадрата или коэффициента детерминации. Выберите любую линию тренда, имеющую значение R-квадрата, ближайшее к единице.

3. Назначение периода прогноза

В этом же меню справа перейдите в раздел «Прогноз». Вы можете указать, насколько далеко вперед или назад должна двигаться линия тренда, указав количество периодов. Например, представьте, что у вас есть набор данных со значением y для каждого дня. Если вы хотите, чтобы линия тренда показывала прогнозы на месяц вперед, вы можете ввести 30 в поле рядом с «Вперед». Закройте меню справа и просмотрите график прогноза.

Как создать график с помощью функции «Прогнозный лист»

Вот как создать график с помощью функции «Прогнозный лист»:

1. Введите свои данные в электронную таблицу

Откройте таблицу и введите свои данные. В идеале вы должны создать два столбца для значений x и значений y. Откройте вкладку «Данные» и нажмите «Прогнозный лист» в группе «Прогноз».

2. Укажите период прогноза

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

3. Настройте «Параметры»

Нажмите «Параметры» в нижней части диалогового окна «Создать лист прогноза». Вот некоторые полезные настройки, которые вы можете настроить:

• Доверительный интервал: доверительный интервал показывает, насколько вероятно, что линия тренда будет давать точные прогнозы. Значение по умолчанию — 95 %, но вы можете увеличить или уменьшить это число, чтобы создать больший или меньший доверительный интервал соответственно.

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

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

• Агрегировать с использованием дубликатов: этот параметр сообщает функции, что делать, если есть два значения y для одного значения x. Например, вы можете сделать так, чтобы функция усредняла значения или использовала только самое большое значение.

4. Нажмите «Создать».

После окончательной настройки нажмите «Создать» в правом нижнем углу диалогового окна. Это действие создает новый лист, содержащий график линии тренда и таблицу со старыми данными и новыми прогнозами. Если вы обновите какие-либо числа в этой таблице, график линии тренда обновится автоматически.