Функция ПРЕДСКАЗ для прогнозирования будущих значений в 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 и более старыми версиями.
- Для предсказания только одного будущего значения на основании известного значения независимой переменной функция ПРЕДСКАЗ используется как обычная формула. Если требуется предсказать сразу несколько значений, в качестве первого аргумента следует передать массив или ссылку на диапазон ячеек со значениями независимой переменной, а функцию ПРЕДСКАЗ использовать в качестве формулы массива.