Прогнозирование с использованием статистических функций

Лабораторная работа

ПРОГНОЗИРОВАНИЕ СРЕДСТВАМИ MS EXCEL

Понятие о прогнозировании и регрессионном анализе

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

Современное прогнозирование базируется на применении математико-статистических методов обработки объективных исходных данных.

Исходные данные, получаемые в результате сбора информации, часто представлены в виде дискретной (точечной) зависимости переменной Y от одной или нескольких независимых переменных X1, X2, …, Xn, причем число точек такой зависимости ограничено. В ходе исследования возникают следующие задачи, достаточно тесно связанные между собой:

1) нахождение промежуточных значений некоторой величины по имеющемуся набору известных значений − интерполяция, т. е. приближѐнное определение значений функции f(x) в точках, лежащих внутри отрезка [x0, xn] по еѐ значениям в точках x0 < x1 < ... < xn;

2) нахождение значений некоторой величины вне заданного интервала − экстраполяция, т. е. приближѐнное определение значений функции f(x) в точках, лежащих вне отрезка [x0,xn], по еѐ значениям в точках x0 < x1 < ... < xn;

3) замена исходной зависимости достаточно простой и легко вычисляемой функцией − аппроксимация, позволяющая исследовать числовые характеристики и качественные свойства объекта, сводя задачу к изучению более удобных объектов с известными или легко вычисляемыми характеристиками.

На настоящий момент основным статистическим аппаратом прогнозирования является многомерный регрессионный анализ, позволяющий:

1) оценить степень связи между переменными;

2) предсказать значения зависимой переменной с помощью независимых;

3) определить вклад отдельных независимых переменных в вариацию зависимой величины.

Регрессия.

Существует прогнозируемая переменная Y (зависимая переменная) и отобранный заранее комплект переменных, от которых она зависит – X₁, X₂, ..., X_N (независимые переменные). Природа независимых переменных может быть различной. Например, если предположить, что Y – уровень спроса на некоторый продукт в следующем месяце, то независимыми переменными могут быть уровень спроса на этот же продукт в прошлый и позапрошлый месяцы, затраты на рекламу, уровень платежеспособности населения, экономическая обстановка, деятельность конкурентов и многое другое. Главное - уметь формализовать все внешние факторы, от которых может зависеть уровень спроса, в числовую форму.

Модель множественной регрессии в общем случае описывается выражением:

Y = F(X₁, X₂, ..., X_N) + e (1)

Здесь – вид функции, показывающей зависимость зависимой переменной от независимых. В более простом варианте линейной регрессионной модели эта зависимость имеет вид:

Y = b₀ + b₁X₁ + b₂X₂ +, ...,+ b_NX_N + e, (2)

где b₀, b₁, b₂,..., b_N – подбираемые коэффициенты регрессии, e –
компонента ошибки. Предполагается, что все ошибки независимы и нормально распределены. С помощью таблицы значений прошлых наблюдений можно подобрать (например, методом наименьших квадратов) коэффициенты регрессии, настроив тем самым модель.

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

Прогнозирование с использованием статистических функций

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

 ЛИНЕЙН − построение линейного приближения;

 ЛГРФПРИБЛ − построение экспоненциального приближения;

 ТЕНДЕНЦИЯ − прогнозирование линейной зависимости;

 РОСТ − прогнозирование экспоненциальной зависимости;

 ПРЕДСКАЗ − прогнозирование значений.

Расчет коэффициентов m и b прямой линии (линейный тренд)

в случае нескольких диапазонов значений Х, аппроксимирующей исходные данные, производят с помощью функции

ЛИНЕЙН(известные_значения_y;известные_значения_x;конст;статистика)

Для расчета по существующим данным коэффициентов m и b экспоненциальной кривой

в случае нескольких диапазонов значений Х, используют функцию

ЛГРФПРИБЛ(известные_значения_y;известные_значения_x;конст;статистика)

Аргументы функций ЛИНЕЙН и ЛГРФПРИБЛ:

известные_значения_y− столбец (строка) известных значений зависи-

мой переменной (обязательный аргумент);

известные_значения_x − массив значений независимой переменной или

нескольких переменных, расположенных в столбцах (строках);

конст − логическое значение, которое указывает (если ЛОЖЬ), что b = 0

для функции ЛИНЕЙН или b = 1 для функции ЛГРФПРИБЛ;

статистика − логическое значение, которое указывает (если ИСТИНА),

что будет возвращена дополнительная регрессионная статистика.

Т е х н о л о г и я п р и м е н е н и я

1 Подготовить таблицу со статистическими данными.

2 Выделить диапазон ячеек, в котором планируется разместить коэффициенты mn, mn-1, …, m1, b (в указанном порядке).

3 Вызвать Мастер функцийи в категории Статистическиенайти

ЛИНЕЙН или ЛГРФПРИБЛ.

4 Заполнить окно выбранной функции аргументами.

5 Закончить ввод формулы массива следует комбинацией клавиш

Ctrl + Shift + Enter.

Примечание − При вводе массива констант в качестве аргумента вручную его

следует взять в фигурные скобки и использовать точку с запятой ";" для разделения

значений переменной.

 

Функция ТЕНДЕНЦИЯ(известные_значения_y;

известные_значения_x;новые_значения_x;конст)

рассчитывает прогнозируемое значение зависимой переменной Y для аргументов новые_значения_xв соответствии с прямой линией Y =mX +b (линейным трендом).

Функция

РОСТ(известные_значения_y; известные_значения_x;новые_значения_x;конст)

возвращает прогнозируемое значение зависимой переменной Y, рассчитанное

для аргументов новые_значения_xв соответствии с экспоненциальной зависимостью

Аргументы известные_значения_y, известные_значения_xи конст

функций ТЕНДЕНЦИЯ и РОСТ имеют то же значение, что и для функций

ЛИНЕЙН и ЛГРФПРИБЛ соответственно.

 

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

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

 x− точка данных, для которой предсказывается значение Y;

 известные_значения_y− массив значений зависимой переменной;

 известные_значения_x− массив значений независимой переменной.

 

Примечание − Массивы известные_значения_y и известные_значения_x должны содержать одинаковое количество точек данных.