МетодМГСУ_ИС в экономике_2001_ч2_Прогнозирование данных
3. Статистический анализ и прогноз рядов наблюдений
Excel позволяет Пользователю использовать более 70 функций Рабочего листа [3] (практически готовых решений) для оценки статистических характеристик ряда наблюдений. Используя их различные сочетания можно решить практически любую задачу Стат. Анализа, используя в качестве помощника Мастер функций.
Ниже будут рассмотрены только функции, которые могут быть
полезны для целей построения линии (уравнения) тренда и прогноза и, прежде
всего, функции линейного и экспоненциального сглаживания рядов наблюдений,
наиболее часто встречающиеся в практике анализа хозяйственной деятельности
торговых Предприятий.[4]
3.1. Линейная и экспоненциальная аппроксимация данных
Для вычислений коэффициентов уравнения прямой линии, которая наилучшим образом аппроксимирует данные наблюдений используется функция ЛИНЕЙН(). Уравнение для прямой линии имеет вид
Y=m1*x1+m2*x2+.....b или Y=m*x+b
Для вычисления коэффициентов уравнения экспоненциальной кривой, используемой для сглаживания данных наблюдения используется функция ЛГРФПРИБЛ(). Уравнение кривой имеет вид
Y = ( b*(m1^x1)*(m2^x2)*.....) или Y =b*m^x
где Y (зависимое значение) является функцией независимого значения х.
Значения m- это коэффициенты, соответствующие каждой независимой переменной х, а b - это постоянная величина (кривая может не проходить через начало координат)
Функции аппроксимации ЛИНЕЙН() и ЛГРФПРИБЛ() позволяют вычислить прямую или экспоненциальную кривую, наилучшим образом описывающую Ваши данные наблюдений. Однако, Вам самим необходимо решить, какой из полученных результатов Вас удовлетворяет в большей степени.
Проводя регрессионный анализ, Excel вычисляет для каждой точки
· квадрат разности между рассчитанным и фактическим значением Y. Сумма квадратов этих разностей называется остаточной суммой квадратов.
· сумму квадратов разностей между фактическими значениями Y и средним значением Y, которая называется общей суммой квадратов ( регрессионная сумма квадратов + остаточная сумма квадратов)
FЧем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности ( корреляции) R2, который показывает насколько полученное регрессионное уравнение соответствует фактическим наблюдениям ( насколько тесно связаны переменные)
функция ЛИНЕЙН()
Рассчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.
Создание формулы массива[5]
· Выделите диапазон ячеек, в которые следует ввести формулу.
· Наберите формулу.
· Нажмите клавиши CTRL+SHIFT+ENTER.
Уравнение для прямой линии имеет следующий вид:
y = mx + b или
y = m1x1 + m2x2 + ... + b (в случае нескольких диапазонов значений x),
где зависимое значение y — функция независимого значения x, значения m — коэффициенты, соответствующие каждой независимой переменной x, а b — постоянная. Заметим, что y, x и m могут быть векторами. Функция ЛИНЕЙН() возвращает массив {mn;mn-1;...;m1;b}. Функция ЛИНЕЙН() может также возвращать дополнительную регрессионную статистику
Синтаксис функций
ЛИНЕЙН(известные значения Y; известные значения х; константа; статистика)
ЛГРФПРИБЛ(известные значения Y; известные значения х; константа; статистика)
Агрументы
«Известные значения Y» - это множество уже известных (заданных) значений Y
Если массив «известные значения Y» имеет один столбец (строку), то каждый столбец (строка) массива «Известные значения х» интерпретируется как отдельная переменная
«Известные значения х» - это необязательное[6] множество уже известных (заданных) значений х
Массив данных «Известные значения х» может содержать одно или несколько множеств переменных, В том случае, если используется только одна переменная ( зависимость вида Y=b+mx или Y=b*m^x), то массивы «Известные значения Y» и «Известные значения X» могут иметь любую форму (но обязательно одну размерность). Если же в уравнение входят несколько переменных, то массив «Известные значения Y» должен быть представлен в виде вектора (занимать интервал ячеек в одной строке или столбце)
Константа- это логическое значение, которое указывает, требуется ли чтобы константа b была равна нулю
FЕсли «константа» имеет значение «1» (истина), то b вычисляется обычным образом.
Если «константа» имеет значение «0» (ложь), то b приравнивается нулю и рассчитываются коэффициенты для уравнения кривой, проходящей через начало координат.
Статистика - это логическое значение, которое указывает, требуется ли вывести ( вернуть) на рабочем листе Excel дополнительную статистику по уравнению регрессии ( линии тренда)
FЕсли «Статистика» имеет значение «1» (истина), то на рабочем листе ( в выделенном диапазоне ячеек) выводится дополнительная статистика по уравнению регрессии в виде массива (mn; mn-1;...m1; b;sen;sen-1;....se1;seb;r2;sey;F;df;ssreg;ssresid)
Если «Статистика» имеет значение «0» (ложь) или опущена, рассчитываются только коэффициенты m и b
Дополнительная статистика
m1; m2;…mn;b |
Коэффициенты уравнения регрессии |
se1;se2,.....sen |
Стандартные значения ошибок для коэффициентов m1;m2;...mn |
seb |
Стандартное отклонение ошибки для постоянной b (если «константа» имеет значение «Ложь», то выводится признак ошибки «#Н.Д» |
r2 |
Коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т. е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y. |
see |
Стандартная ошибка для оценки Y |
F |
F - статистика ( Критерий Фишера). Используется для оценки того, являются ли связь между зависимой и независимой переменными случайной или нет. Оценка производится найденной величины F с табличным значением при заданном количестве степеней свободы |
df |
Степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН |
ssreg |
Регрессионная сумма квадратов |
ssresid |
Остаточная сумма квадратов |
Дополнительная регрессионная статистика выводится в виде таблицы в предварительно выделенном интервале ячеек.
mn |
mn |
.... |
m2 |
m1 |
b |
|
sen |
sen-1 |
.... |
se2 |
se1 |
seb |
|
r |
sey |
|
|
|
|
|
F |
df |
|
|
|
|
|
ssrtg |
ssresid |
|
|
|
|
|
F Важно! Методы, которые используются для проверки уравнений, полученных с помощью функции ЛГРФПРИБЛ(), такие же, как и для функции ЛИНЕЙН(). Однако, дополнительная статистика, которую возвращает функция ЛГРФПРИБЛ(), основана на следующей линейной модели:
ln y = х1* ln (m1) + ... + xт *ln(mn) + ln(b)
Это следует помнить при оценке дополнительной статистики, особенно значений sei и seb, которые следует сравнивать с ln(mi) и ln (b), а не с mi и b. Для получения более подробной информации, см. любой справочник по математической статистике.
Замечания:
Формулы, которые возвращают массивы, должны быть введены как формулы массивов.[7]
Создание формулы массива[8]
· Выделите диапазон ячеек, в которые следует ввести формулу.
· Наберите формулу.
· Нажмите клавиши CTRL+SHIFT+ENTER.
Пример 1
В таблице приведены данные по продаже электробытовых товаров ( холодильники, пылесосы, обогреватели и др.)1978 - 1985гг., полученные на основе опроса населения
Необходимо
найти уравнение регрессии наилучшим образом описывающее данные опроса, на 1990 и 1995 годы полагая, что процессы определяющие спрос населения на электробытовые товары остается неизменным в течение всего последующего времени.
Внимание: В том случае, если в качестве независимой
переменной используется дата, то введите дополнительную
строку с номером этой даты ( в нашем примере ячейки A2:I2) и используйте ее в дальнейшем в качестве независимой
переменной. Дело в том, что русифицированные версии Excel
очень часто дают ошибку при использовании даты в качестве независимой
переменной.
· На рабочем листе Excel выделите область ячеек A6:B10,
·
Вызовите мастер функций ЛИНЕЙН()
· В открывшемся диалоговом окне:
o В окно «Известные значения Y» введите адреса ячеек, содержащих эти значения (B4:I4)
o В окно «Известные значения Х» введите адреса ячеек, содержащих эти значения (B2:I2);
o В окно «статистика» введите 1;
· Нажмите клавиши CTRL+SHIFT+ENTER
· введем формулу =ЛИНЕЙН(B3:I3;B2:I2;1;1) и нажмем клавиши «Ctrl+Shift+Enter»
FВ результате выполненных действий, в выделенный интервал ячеек будут возвращены значения статистики уравнения линейной регрессии.
Уравнение линейной регрессии для нашего примера будет иметь вид
Y = 0.998*X + 1.041
R2 = 0,93
Подобным же образом может быть найдено уравнение логарифмического приближения (функция ЛГРФПРИБЛ()).
Рис. 4. Рабочий лист Excel с
примером расчета функции ЛИНЕЙН()