Формулы прогноза в экселе: Функция ПРЕДСКАЗ для прогнозирования будущих значений в Excel |

Posted:

Май 14, 2023

Comments:

Формулы прогноза в экселе: Функция ПРЕДСКАЗ для прогнозирования будущих значений в Excel

Содержание

Функция ПРЕДСКАЗ для прогнозирования будущих значений в Excel

Функция ПРЕДСКАЗ в Excel позволяет с некоторой степенью точности предсказать будущие значения на основе существующих числовых значений, и возвращает соответствующие величины. Например, некоторый объект характеризуется свойством, значение которого изменяется с течением времени. Такие изменения могут быть зафиксированы опытным путем, в результате чего будет составлена таблица известных значений x и соответствующих им значений y, где x – единица измерения времени, а y – количественная характеристика свойства. С помощью функции ПРЕДСКАЗ можно предположить последующие значения y для новых значений x.

Примеры использования функции ПРЕДСКАЗ в Excel

Функция ПРЕДСКАЗ использует метод линейной регрессии, а ее уравнение имеет вид y=ax+b, где:

Коэффициент a рассчитывается как Yср.-bXср. (Yср. и Xср. – среднее арифметическое чисел из выборок известных значений y и x соответственно).
Коэффициент b определяется по формуле:

Пример 1. В таблице приведены данные о ценах на бензин за 23 дня текущего месяца. Согласно прогнозам специалистов, средняя стоимость 1 л бензина в текущем месяце не превысит 41,5 рубля. Спрогнозировать стоимость бензина на оставшиеся дни месяца, сравнить рассчитанное среднее значение с предсказанным специалистами.

Вид исходной таблицы данных:

Чтобы определить предполагаемую стоимость бензина на оставшиеся дни используем следующую функцию (как формулу массива):

Описание аргументов:

A26:A33 – диапазон ячеек с номерами дней месяца, для которых данные о стоимости бензина еще не определены;
B3:B25 – диапазон ячеек, содержащих данные о стоимости бензина за последние 23 дня;
A3:A25 – диапазон ячеек с номерами дней, для которых уже известна стоимость бензина.

Результат расчетов:

Рассчитаем среднюю стоимость 1 л бензина на основании имеющихся и расчетных данных с помощью функции:

=СРЗНАЧ(B3:B33)

Результат:

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

Анализ прогноза спроса продукции в Excel по функции ПРЕДСКАЗ

Пример 2. Компания недавно представила новый продукт. С момента вывода на рынок ежедневно ведется учет количества клиентов, купивших этот продукт. Предположить, каким будет спрос на протяжении 5 последующих дней.

Вид исходной таблицы данных:

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

Рассчитаем значения логарифмического тренда с помощью функции ПРЕДСКАЗ следующим способом:

Как видно, в качестве первого аргумента представлен массив натуральных логарифмов последующих номеров дней. Таким образом получаем функцию логарифмического тренда, которая записывается как y=aln(x)+b.

Результат расчетов:

Для сравнения, произведем расчет с использованием функции линейного тренда:

И для визуального сравнительного анализа построим простой график.

Полученные результаты:

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

Прогнозирование будущих значений в Excel по условию

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

Вид таблицы данных:

Для расчета будущих значений Y без учета отрицательных значений (-5, -20 и -35) используем формулу:

C помощью функций ЕСЛИ выполняется перебор элементов диапазона B2:B11 и отброс отрицательных чисел. Так, получаем прогнозные данные на основании значений в строках с номерами 2,3,5,6,8-10. Для детального анализа формулы выберите инструмент «ФОРМУЛЫ»-«Зависимости формул»-«Вычислить формулу». Один из этапов вычислений формулы:

Полученные результаты:

Особенности использования функции ПРЕДСКАЗ в Excel

Функция имеет следующую синтаксическую запись:

=ПРЕДСКАЗ(x;известные_значения_y;известные_значения_x)

Описание аргументов:

x – обязательный для заполнения аргумент, характеризующий одно или несколько новых значений независимой переменной, для которых требуется предсказать значения y (зависимой переменной). Может принимать числовое значение, массив чисел, ссылку на одну ячейку или диапазон;
известные_значения_y – обязательный аргумент, характеризующий уже известные числовые значения зависимой переменной y. Может быть указан в виде массива чисел или ссылки на диапазон ячеек с числами;
известные_значения_x – обязательный аргумент, который характеризует уже известные значения независимой переменной x, для которой определены значения зависимой переменной y.

Примечания:

Второй и третий аргументы рассматриваемой функции должны принимать ссылки на непустые диапазоны ячеек или такие диапазоны, в которых число ячеек совпадает. Иначе функция ПРЕДСКАЗ вернет код ошибки #Н/Д.
Если одна или несколько ячеек из диапазона, ссылка на который передана в качестве аргумента x, содержит нечисловые данные или текстовую строку, которая не может быть преобразована в число, результатом выполнения функции ПРЕДСКАЗ для данных значений x будет код ошибки #ЗНАЧ!.
Статистическая дисперсия величин (можно рассчитать с помощью формул ДИСП.Г, ДИСП.В и др.), передаваемых в качестве аргумента известные_значения_x, не должна равняться 0 (нулю), иначе функция ПРЕДСКАЗ вернет код ошибки #ДЕЛ/0!.
Рассматриваемая функция игнорирует ячейки с нечисловыми данными, содержащиеся в диапазонах, которые переданы в качестве второго и третьего аргументов.
Функция ПРЕДСКАЗ была заменена функцией ПРЕДСКАЗ.ЛИНЕЙН в Excel версии 2016, но была оставлена для обеспечения совместимости с Excel 2013 и более старыми версиями.
Для предсказания только одного будущего значения на основании известного значения независимой переменной функция ПРЕДСКАЗ используется как обычная формула. Если требуется предсказать сразу несколько значений, в качестве первого аргумента следует передать массив или ссылку на диапазон ячеек со значениями независимой переменной, а функцию ПРЕДСКАЗ использовать в качестве формулы массива.

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

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, а будущее значение предсказывается с помощью линейной регрессии. Эти функции можно использовать для предсказания будущих продаж, требований к запасам или потребительских тенденций.

Синтаксис

ПРЕДСКАЗ.ЛИНЕЙН(x;известные_значения_y;известные_значения_x)

— или —

ПРЕДСКАЗ(известные_значения_y;известные_значения_x)

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

Аргумент

Обязательно

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

Да

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

Известные_значения_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
6	20
7	28
9	31
15	38
21	40
Формула	Описание	Результат
=ПРОГНОЗ. ЛИНЕЙНЫЙ(30;A2:A6;B2:B6)	Предсказывает значение y, соответствующее заданному значению x = 30	10,607253

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

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

Формула

Описание

Результат

=ПРОГНОЗ. ЛИНЕЙНЫЙ(30;A2:A6;B2:B6)

Предсказывает значение y, соответствующее заданному значению x = 30

10,607253

ПРОГНОЗ и ПРОГНОЗ.ЛИНЕЙНЫЕ функции — служба поддержки Майкрософт

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

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