Как сделать прогноз в excel

«ПРОГНОЗ» и «ПРОГНОЗ». Функции ЛИНЕЙЛ

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel Starter 2010 Еще. Меньше

В этой статье описаны синтаксис формулы и использование прогноза. Функции LINEARи FORECAST в Microsoft Excel.

Примечание: В Excel 2016 функция ПРОГНОЗ была заменена функцией ПРОГНОЗ. LINEAR в составе новых функций прогнозирования. Синтаксис и использование этих двух функций одинаковы, но старая функция ПРЕДСПРОС в конечном итоге будет отознана. Он по-прежнему доступен для обратной совместимости, но мы думайте использовать новую функцию ПРОГНОЗ. Функция ЛИНЕЙЛ.

Описание

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

Синтаксис

ПРОГНОЗ или ПРОГНОЗ. Аргументы функции ЛИННЕЯ следующую:

Обязательно

«Указывает на»

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

Известные_значения_y.

Зависимый массив или интервал данных.

Известные_значения_x.

Независимый массив или интервал данных.

Замечания

• Если x не является числом, ТО ЕСТЬ и ПРОГНОЗ. ЛиНЕЙНАЯ возвращает #VALUE! значение ошибки #ЗНАЧ!.
• Если known_y или known_x пустые или имеет больше точек данных, чем в других, ТО ЕСТЬ и ПРОГНОЗ. ЛИНЕЙЛ возвращает значение #N/A.
• Если дисперсия known_x равна нулю, то ЕСТЬ ПРОГНОЗ и ПРОГНОЗ. Linear возвращает #DIV/0! значение ошибки #ЗНАЧ!.
• Уравнение для FORECAST и FORECAST. Это a+bx, где: и где x и y — средние значения выборок СРЗНАЧ(известные_значения_x) и СРЗНАЧ(известные_значения_y).

Пример

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

Известные значения y

Известные значения x

Прогнозирование тенденций в данных

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

Ниже приведены инструкции по отображению и распознаванию трендов, а также по составлению прогноза.

Прогнозирование трендов на базе имеющихся данных

Создание линейного приближения

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

1. Выделите не менее двух ячеек, содержащих начальные значения для тренда. Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.
2. Перетащите маркер заполнения в сторону увеличения или уменьшения значений. Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.

Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.

Создание экспоненциального приближения

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

1. Выделите не менее двух ячеек, содержащих начальные значения для тренда. Чтобы повысить точность значений последовательности, укажите дополнительные начальные значения.
2. Удерживая нажатой клавишу CONTROL, перетащите маркер заполнения в нужном направлении, чтобы заполнить ячейки возрастающими или убывающими значениями. Например, если вы выбрали ячейки C1:E1, содержащие начальные значения 3, 5 и 8, то при перетаскивании маркера заполнения вправо значения будут возрастать, а влево — убывать.
3. Отпустите клавишу CONTROL и кнопку мыши, а затем в контекстном меню выберите команду Экспоненциальное приближение. Excel автоматически рассчитывает экспоненциальное приближение и продолжает ряд, заполняя значениями выделенные ячейки.

Совет: Чтобы вручную настроить создаваемые последовательности, в меню Правка выберите пункт Заполнить и команду Ряд.

Отображение ряда на диаграмме с помощью линии тренда

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

1. На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.
2. На вкладке Конструктор нажмите кнопку Добавить элемент диаграммы и выберите пункт Линия тренда.

Примечание: Линии тренда можно добавлять только на диаграммы без накопления, плоские диаграммы, диаграммы с областями, линейчатые диаграммы, гистограммы, графики, биржевые, точечные и пузырьковые диаграммы.

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

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

1. На диаграмме выберите ряд данных, для которого требуется добавить линию тренда или скользящее среднее.
2. В меню Диаграмма выберите команду Добавить линию тренда, а затем — пункт Тип.

Примечание: Линии тренда можно добавлять только на диаграммы без накопления, плоские диаграммы, диаграммы с областями, линейчатые диаграммы, гистограммы, графики, биржевые, точечные и пузырьковые диаграммы.

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

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

Прогнозирование значений в рядах

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

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

Автоматическое заполнение ряда для линейного наиболее точного тренда

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

Начальное значение

Расширенный линейный ряд

Чтобы заполнить ряд для линейного тренда, сделайте следующее:

1. Выделите не менее двух ячеек, содержащих начальные значения для тренда. Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.
2. Перетащите его в нужном направлении. Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.

Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд(вкладкаГлавная, группа Редактирование, кнопка Заполнить).

Автоматическое заполнение ряда для тенденции роста

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

Начальное значение

Расширенный ряд роста

Чтобы заполнить ряд для тенденции роста, сделайте следующее:

1. Выделите не менее двух ячеек, содержащих начальные значения для тренда. Чтобы повысить точность ряда трендов, выберите дополнительные начальные значения.
2. Удерживая нажатой правую кнопку мыши, перетащите указатель заполнения в нужном направлении, отпустите кнопку мыши, а затем на ленте нажмите кнопку контекстное меню.

Например, если в ячейках C1:E1 выбраны начальные значения 3, 5 и 8, перетащите его вправо, чтобы заполнить значениями тенденций, или перетащите его влево, чтобы заполнить значениями убывания.

Совет: Чтобы вручную управлять тем, как создается ряд, или заполнить ряд с помощью клавиатуры, выберите команду Ряд(вкладкаГлавная, группа Редактирование, кнопка Заполнить).

Заполнение линейного тренда или роста значениями тренда вручную

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

• В линейном ряду начальные значения применяются к алгоритму наименьших квадратов (y=mx+b), который создает ряд.
• В рядах роста начальные значения применяются к алгоритму экспоненциальной кривой (y=b*m^x), который создает ряд.

В обоих случаях шаг игнорируется. Созданный ряд эквивалентен значениям, возвращенным функцией ТЕНДЕНЦИЯ или функцией РОСТ.

Чтобы заполнить значения вручную, сделайте следующее:

1. Вы выберите ячейку, в которой нужно начать ряд. Ячейка должна содержать первое значение ряда. При выборе команды Ряд итоговые ряды заменяют исходные выбранные значения. Если вы хотите сохранить исходные значения, скопируйте их в другую строку или столбец, а затем создайте ряд, выбирая скопированные значения.
2. На вкладке Главная в группе Редактирование нажмите кнопку Заполнить и выберите пункт Прогрессия.
3. Выполните одно из указанных ниже действий.
   • Чтобы заполнить ряд вниз по worksheet, щелкните Столбцы.
   • Чтобы заполнить ряд по всему ряду, щелкните Строки.
4. В поле Шаг введите значение, на которое вы хотите увеличить ряд.

Результат шага

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

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

1. В области Типвыберите линейный или Рост.
2. В поле Остановить значение введите значение, на которое нужно остановить ряд.

Примечание: Если ряд имеет несколько начальных значений и Excel создать тенденцию, выберите значение Тренд.

Расчет тенденций путем добавления линии тренда на диаграмму

Если у вас есть данные, для которых вы хотите спрогнозировать тенденцию, можно создать линия тренда на диаграмме. Например, если в Excel есть диаграмма с данными о продажах за первые несколько месяцев года, вы можете добавить на нее линию тренда, которая отображает общий тренд продаж (увеличение или уменьшение или снижение), а также прогнозируемый тренд на месяцы вперед.

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

1. Щелкните диаграмму.
2. Щелкните ряд данных, в который вы хотите добавить линия тренда или скользящее среднее.
3. На вкладке Макет в группе Анализ нажмите кнопку Линия тренда ивыберите нужный тип линии тренда или скользящего среднего.
4. Чтобы настроить параметры и отформатирование линии тренда или скользящего среднего, щелкните линию тренда правой кнопкой мыши и выберите в меню пункт Формат линии тренда.
5. Выберите нужные параметры линии тренда, линии и эффекты.
   • При выборе параметра Полиномиальная, введите в поле Порядок наивысшую мощность для независимой переменной.
   • Если выбрано значение Скользящегосреднего , введите в поле Период количество периодов, используемых для расчета лино-среднего.

• В поле На основе ряда перечислены все ряды данных на диаграмме, которые поддерживают линии тренда. Чтобы добавить линию тренда к другому ряду, щелкните имя в поле и выберите нужные параметры.
• При добавлении скользящего среднего на точечная диаграмма скользящие средние значения основаны на порядке, за исключением значений X, относящегося к диаграмме. Чтобы получить нужный результат, перед добавлением скользящего среднего может потребоваться отсортировать значения x.

Важно: Начиная с Excel 2005 г., Excel способ вычисления значения R2 для линейных линий тренда на диаграммах, где для перехваченной линии тренда установлено значение нуля (0). Эта корректировка исправит вычисления, которые дают неправильные значения R 2, и выровняет вычисление R2 с функцией LINEST. В результате на диаграммах, созданных в предыдущих версиях Excel, могут отображаться разные значения R2. Дополнительные сведения см. в таблице Изменения внутренних вычислений линейных линий тренда на диаграмме.

Выполнение регрессионного анализа с помощью надстройки «Надстройка «Надстройка анализа»

Если вам нужно выполнить более сложный регрессионный анализ, в том числе вычислить и отсчитывать остаточные данные, используйте средство регрессионного анализа в надстройке «Надстройка «Надстройка анализа». Дополнительные сведения см. в окне Загрузка средства анализа.

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

Вот как можно использовать его для создания линейного тренда чисел в Excel в Интернете:

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

Project значения с помощью функции

Использование функции ПРОГНОЗ Функция ПРЕДСПРОС вычисляет или предсказывает будущее значение с использованием существующих значений. Предсказываемое значение — это значение y, соответствующее заданному значению x. Значения x и y известны; новое значение предсказывается с использованием линейной регрессии. Эта функция используется для предсказания будущих продаж, требований к запасам и потребительских тенденций.

Использование функции ТЕНДЕНЦИЯ или ФУНКЦИИ РОСТ Функции ТЕНДЕНЦИЯ и РОСТ могут выполнять экстраполяцию будущих значений y,которые расширяют прямую или экспоненциальный кривую, наилучшим образом описывающую существующие данные. Они также могут возвращать только значения yна основе известных значений x-длянаиболее подходящих строк или кривой. Для отстройки линии или кривой, описывающую существующие данные, используйте существующие значения x-value и y-value,возвращаемые функцией ТЕНДЕНЦИЯ или РОСТ.

Использование функции ЛИНИИСТОЛ или ФУНКЦИИ ЛОГЕСТ Функцию ЛИННЕФ или LOGEST можно использовать для вычисления прямой или экспоненциальной кривой из существующих данных. Функции LINEST и LOGEST возвращают различные статистические данные о регрессии, включая наклон и отступ линии, которая лучше всего подходит.

В следующей таблице содержатся ссылки на дополнительные сведения об этих функциях.

Как рассчитать прогноз

2. Установите курсор мыши в ячейку, с которой хотите, чтобы программа рассчитала прогноз (в файле с примером ячейка выделена жёлтым цветом).

3. Зайдите в меню «Forecast»

4. Задайте период прогноза:

5. Нажмите кнопку «Рассчитать»:

Вы можете рассчтать прогноз по 1 ряду или по всем рядам одновременно:

Готово!

В продолжение ряда вы найдете рассчитанный прогноз на заданный период:

Период прогноза можно менять здесь:

Важно!
Программа начнет расчет прогноза с той ячейки с данными, в которую вы установите курсор. Например, если вы установите курсор не в январь, а, например, на март, то программа сделает расчет, начиная с марта.