Среди различных методов прогнозирования нельзя не выделить аппроксимацию. С её помощью можно производить приблизительные подсчеты и вычислять планируемые показатели, путем замены исходных объектов на более простые. В Экселе тоже существует возможность использования данного метода для прогнозирования и анализа. Давайте рассмотрим, как этот метод можно применить в указанной программе встроенными инструментами.
Наименование данного метода происходит от латинского слова proxima – «ближайшая» Именно приближение путем упрощения и сглаживания известных показателей, выстраивание их в тенденцию и является его основой. Но данный метод можно использовать не только для прогнозирования, но и для исследования уже имеющихся результатов. Ведь аппроксимация является, по сути, упрощением исходных данных, а упрощенный вариант исследовать легче.
Главный инструмент, с помощью которого проводится сглаживания в Excel – это построение линии тренда. Суть состоит в том, что на основе уже имеющихся показателей достраивается график функции на будущие периоды. Основное предназначение линии тренда, как не трудно догадаться, это составление прогнозов или выявление общей тенденции.
Но она может быть построена с применением одного из пяти видов аппроксимации:
- Линейной;
- Экспоненциальной;
- Логарифмической;
- Полиномиальной;
- Степенной.
Рассмотрим каждый из вариантов более подробно в отдельности.
Способ 1: линейное сглаживание
Прежде всего, давайте рассмотрим самый простой вариант аппроксимации, а именно с помощью линейной функции. На нем мы остановимся подробнее всего, так как изложим общие моменты характерные и для других способов, а именно построение графика и некоторые другие нюансы, на которых при рассмотрении последующих вариантов уже останавливаться не будем.
Прежде всего, построим график, на основании которого будем проводить процедуру сглаживания. Для построения графика возьмем таблицу, в которой помесячно указана себестоимость единицы продукции, производимой предприятием, и соответствующая прибыль в данном периоде. Графическая функция, которую мы построим, будет отображать зависимость увеличения прибыли от уменьшения себестоимости продукции.
Сглаживание, которое используется в данном случае, описывается следующей формулой:
В конкретно нашем случае формула принимает такой вид:
y=-0,1156x+72,255
Величина достоверности аппроксимации у нас равна 0,9418 , что является довольно приемлемым итогом, характеризующим сглаживание, как достоверное.
Способ 2: экспоненциальная аппроксимация
Теперь давайте рассмотрим экспоненциальный тип аппроксимации в Эксель.
Общий вид функции сглаживания при этом такой:
где e – это основание натурального логарифма.
В конкретно нашем случае формула приняла следующую форму:
y=6282,7*e^(-0,012*x)
Способ 3: логарифмическое сглаживание
Теперь настала очередь рассмотреть метод логарифмической аппроксимации.
В общем виде формула сглаживания выглядит так:
где ln – это величина натурального логарифма. Отсюда и наименование метода.
В нашем случае формула принимает следующий вид:
y=-62,81ln(x)+404,96
Способ 4: полиномиальное сглаживание
Настал черед рассмотреть метод полиномиального сглаживания.
Формула, которая описывает данный тип сглаживания, приняла следующий вид:
y=8E-08x^6-0,0003x^5+0,3725x^4-269,33x^3+109525x^2-2E+07x+2E+09
Способ 5: степенное сглаживание
В завершении рассмотрим метод степенной аппроксимации в Excel.
Данный способ эффективно используется в случаях интенсивного изменения данных функции. Важно учесть, что этот вариант применим только при условии, что функция и аргумент не принимают отрицательных или нулевых значений.
Общая формула, описывающая данный метод имеет такой вид:
В конкретно нашем случае она выглядит так:
y = 6E+18x^(-6,512)
Как видим, при использовании конкретных данных, которые мы применяли для примера, наибольший уровень достоверности показал метод полиномиальной аппроксимации с полиномом в шестой степени (0,9844 ), наименьший уровень достоверности у линейного метода (0,9418 ). Но это совсем не значит, что такая же тенденция будет при использовании других примеров. Нет, уровень эффективности у приведенных выше методов может значительно отличаться, в зависимости от конкретного вида функции, для которой будет строиться линия тренда. Поэтому, если для этой функции выбранный метод наиболее эффективен, то это совсем не означает, что он также будет оптимальным и в другой ситуации.
Если вы пока не можете сразу определить, основываясь на вышеприведенных рекомендациях, какой вид аппроксимации подойдет конкретно в вашем случае, то есть смысл попробовать все методы. После построения линии тренда и просмотра её уровня достоверности можно будет выбрать оптимальный вариант.
Эмпирические коэффициенты регрессии b 0 , b 1 будем определять с помощью инструмента «Регрессия» надстройки «Анализ данных» табличного процессораMS Excel.
Алгоритм определения коэффициентов состоит в следующем.
1. Вводимисходные данные в табличный процессор MS Excel.
2. Вызываемнадстройку Анализ данных(рисунок 2).
3.Выбираем инструмент анализа Регрессия(рисунок 3).
4. Заполняем соответствующие позиции окна Регрессия (рисунок 4).
5. Нажимаем кнопку ОК окна Регрессия и получаем протокол решения задачи (рисунок 5)
Рисунок 3 – Выбор инструмента Регрессия
 |
Рисунок 4 – Окно Регрессия
Рисунок 5 – Протокол решения задачи
Из рисунка 5 видно, что эмпирические коэффициенты регрессии соответственно равны
b 0 = 223,
b 1 = 0, 0088.
Тогда уравнение парной линейной регрессии, связывающая величину ежемесячной пенсии у с величиной прожиточного минимумахимеет вид
.(3.2)
Далее, в соответствии с заданием необходимо оценить тесноту статистической связи между величиной прожиточного минимума х и величиной ежемесячной пенсии у. Эту оценку можно сделать с помощью коэффициента корреляции . Величина этого коэффициента на рисунке 5 обозначена как множественный R и соответственно равна 0,038. Поскольку теоретически величина данного коэффициента находится в пределахот –1 до +1, то можно сделать вывод о не существенности статистической связимежду величиной прожиточного минимума х и величиной ежемесячной пенсии у.
Параметр «R – квадрат», представленныйна рисунке 5 представляет собой квадрат коэффициента корреляции и называется коэффициентом детерминации. Величина данного коэффициента характеризует долю дисперсии зависимой переменной у, объясненную регрессией (объясняющей переменной х). Соответственно величина 1- характеризует долю дисперсии переменной у, вызванную влиянием всех остальных, неучтенных в эконометрической модели объясняющих переменных. Из рисунка 5 видно, что доля всех неучтенных в полученной эконометрической модели объясняющих переменных приблизительно составляет 1- 0,00145 = 0,998 или 99,8%.
На следующем этапе, в соответствии с заданием необходимо определить степень связи объясняющей переменной х с зависимой переменной у, используя коэффициент эластичности. Коэффициент эластичности для модели парной линейной регрессии определяется в виде:
Следовательно, при изменении прожиточного минимума на 1% величина ежемесячной пенсии изменяется на 0,000758%.
. (3.4)
Для этого исходную таблицу 1 дополняем двумя колонками, в которых определяем значения, рассчитанные с использованием зависимости (3.2) и значения разности .
Таблица 3.2. Расчет средней ошибки аппроксимации.
Тогда средняя ошибка аппроксимации равна
.
Из практики известно, что значение средней ошибки аппроксимации не должно превышать (12…15)%
На последнем этапе выполним оценкустатистической надежности моделирования спомощью F – критерия Фишера. Для этого выполним проверку нулевой гипотезы Н 0 о статистической не значимости полученного уравнения регрессиипо условию:
если при заданном уровне значимости a = 0,05 теоретическое (расчетное) значение F-критерия больше его критического значения F крит (табличного), то нулевая гипотеза отвергается, и полученное уравнение регрессии принимается значимым.
Из рисунка 5 следует, что F расч = 0,0058. Критическое значение F-критерия определяем с помощью использования статистической функции FРАСПОБР (рисунок 6). Входными параметрами функции является уровень значимости (вероятность) и число степеней свободы 1 и 2. Для модели парной регрессии число степеней свободы соответственно равно 1 (одна объясняющая переменная) и n-2 = 6-2=4.
 |
Рисунок 6 – Окно статистической функции FРАСПОБР
Из рисунка 6 видно, что критическое значение F-критерия равно 7,71.
Так как F расч < F крит, то нулевая гипотеза не отвергается и полученное регрессионное уравнение статистически незначимо.
13. Построение модели множественной регрессии с использованием EXCEL.
В соответствии с вариантом задания, используя статистический материал, необходимо.
1. Построить линейное уравнение множественной регрессии пояснить экономический смысл его параметров.
2. Дать сравнительную оценку тесноты связи факторов с результативным признаком с помощью средних (общих) коэффициентов эластичности.
3. Оценить статистическую значимость коэффициентов регрессии с помощью t-критерия Стьюдента и нулевую гипотезу о значимости уравнения с помощью F-критерия.
4. Оценить качество уравнения посредством определения средней ошибки аппроксимации.
Исходные данные для построения модели парной регрессии приведены в таблице 3.3.
Таблица 3.3. Исходные данные.
| Чистый доход, млн. долларов США у | Оборот капитала, мл. долл. США, х 1 | Использованный капитал, мл. долл. США, х 2 |
| 6,6 | 6,9 | 83,6 |
| 2,7 | 93,6 | 25,4 |
| 1,6 | 10,0 | 6,4 |
| 2,4 | 31,5 | 12,5 |
| 3,3 | 36,7 | 14,3 |
| 1,8 | 13,8 | 6,5 |
| 2,4 | 64,8 | 22,7 |
| 1,6 | 30,4 | 15,8 |
| 1,4 | 12,1 | 9,3 |
| 0,9 | 31,3 | 18,9 |
Технология построения уравнения регрессии аналогична алгоритму, изложенному в пункте 3.1. Протокол построения уравнения регрессии показан на рисунке 7.
| ВЫВОД ИТОГОВ | |||
| Регрессионная статистика | |||
| Множественный R | 0,901759207 | ||
| R-квадрат | 0,813169667 | ||
| Нормированный R-квадрат | 0,759789572 | ||
| Стандартная ошибка | 0,789962026 | ||
| Наблюдения | |||
| Дисперсионный анализ | |||
| df | MS | F | |
| Регрессия | 9,50635999 | 15,23357468 | |
| Остаток | 0,624040003 | ||
| Итого | |||
| Коэффициенты | t-статистика | ||
| Y-пересечение | 1,113140304 | 2,270238114 | |
| Переменная X 1 | -0,000592199 | -0,061275574 | |
| Переменная X 2 | 0,063902851 | 5,496523193 |
Рисунок 7. Вывод итогов.
Министерство сельского хозяйства РФ
Федеральное государственное бюджетное образовательное
учреждение высшего профессионального образования
«Пермская государственная сельскохозяйственная академия
имени академика Д.Н.Прянишникова»
Кафедра финансов, кредита и экономического анализа
Контрольная работа по дисциплине «Эконометрика» Вариант - 10
Задание 1………………………………………………………………11
Задание 2……………………………………………….……………...19
Ошибки аппроксимации и ее определение………………………………….3
Аналитический способ выравнивания временного ряда и используемые при этом функции……………………………………………………………..4
Практическая часть……………………………………………………….....11
Список использованной литературы……………………………………….....25
Ошибки аппроксимации и ее определение.
Средняя ошибка аппроксимации – это среднее отклонение расчетных данных от фактических. Она определяется в процентах по модулю.
Фактические значения результативного признака отличаются от теоретических. Чем меньше это отличие, тем ближе теоретические значения подходят к эмпирическим данным, это лучшее качество модели. Величина отклонений фактических и расчетных значений результативного признака по каждому наблюдению представляет собой ошибку аппроксимации. Их число соответствует объему совокупности. В отдельных случаях ошибка апроксимации может оказаться равной нулю. Для сравнения используются величины отклонений, выраженные в процентах к фактическим значениям.
Поскольку может быть как величиной положительной, так и отрицательной, то ошибки аппроксимации для каждого наблюдения принято определять в процентах по модулю. Отклонения можно рассматривать как абсолютную ошибку аппроксимации, и как относительную ошибку аппроксимации. Чтоб иметь общее суждение о качестве модели из относительных отклонений по каждому наблюдению, определяют среднюю ошибку аппроксимации как среднюю арифметическую простую.
Среднюю ошибку аппроксимации рассчитают по формуле:
Возможно и иное определение средней ошибки аппроксимации:
Если А£10-12%, то можно говорить о хорошем качестве модели.
Аналитический способ выравнивания временного ряда и используемые при этом функции.
Более совершенным приемом выявления основной тенденции развития в рядах динамики является аналитическое выравнивание. При изучении общей тенденции методом аналитического выравнивания исходят из того, что изменения уровней ряда динамики могут быть с той или иной степенью точности приближения выражены определенными математическими функциями. Вид уравнения определяется характером динамики развития конкретного явления. На практике по имеющемуся временному ряду задают вид и находят параметры функции y=f(t), а затем анализируют поведение отклонений от тенденции. Чаще всего при выравнивании используются следующие зависимости: линейная, параболическая и экспоненциальная. Во многих случаях моделирование рядов динамики с помощью полиномов или экспоненциальной функции не дает удовлетворительных результатов, так как в рядах динамики содержатся заметные периодические колебания вокруг общей тенденции. В таких случаях следует использовать гармонический анализ (гармоники ряда Фурье). Применение, именно, этого метода предпочтительно, поскольку он определяет закон, по которому можно достаточно точно спрогнозировать значения уровней ряда.
Целью же аналитического выравнивания динамического ряда является определение аналитической или графической зависимости y=f(t). Функцию y=f(t) выбирают таким образом, чтобы она давала содержательное объяснение изучаемого процесса. Это могут быть различные функции.
Системы уравнений вида y=f(t) для оценки параметров полиномов по МНК
(кликабельно)
Графическое представление полиномов n-порядка
1. Если изменение уровней ряда характеризуется равномерным увеличением (уменьшением) уровней, когда абсолютные цепные приросты близки по величине, тенденцию развития характеризует уравнение прямой линии.
2. Если в результате анализа типа тенденции динамики установлена криволинейная зависимость, примерно с постоянным ускорением, то форма тенденции выражается уравнением параболы второго порядка.
3. Если рост уровней ряда динамики происходит в геометрической прогрессии, т.е. цепные коэффициенты роста более или менее постоянны, выравнивание ряда динамики ведется по показательной функции.
После выбора вида уравнения необходимо определить параметры уравнения. Самый распространенный способ определения параметров уравнения - это метод наименьших квадратов, в котором в качестве решения принимается точка минимума суммы квадратов отклонений между теоретическими (выравненными по выбранному уравнению) и эмпирическими уровнями.
Выравнивание по прямой (определение линии тренда) имеет выражение: yt=a0+a1t
t-условное обозначение времени;
а 0 и a1-параметры искомой прямой.
Параметры прямой находятся из решения системы уравнений:
Система уравнений упрощается, если значения t подобрать так, чтобы их сумма равнялась Σt = 0, т. е. начало отсчета времени перенести в середину рассматриваемого периода. Если до переноса точки отсчета t = 1, 2, 3, 4…, то после переноса:
если число уровней ряда нечетное t = -4 -3 -2 -1 0 +1 +2 +3 +4
если число уровней ряда четное t = -7 -5 -3 -1 +1 +3 +5 +7
Таким образом, ∑t в нечетной степени всегда будет равна нулю.
Аналогично находятся параметры параболы 2-го порядка из решения системы уравнений:
Выравнивание по среднему абсолютному приросту или среднему коэффициенту роста:
Δ-средний абсолютный прирост;
К-средний коэффициент роста;
У0-начальный уровень ряда;
Уn-конечный уровень ряда;
t-порядковый номер уровня, начиная с нуля.
Построив уравнение регрессии, проводят оценку его надежности. Значимость выбранного уравнения регрессии, параметров уравнения и коэффициента корреляции следует оценить, применив критические методы оценки:
F-критерий Фишера, t–критерий Стьюдента, при этом, расчетные значения критериев сравниваются с табличными (критическими) при заданном уровне значимости и числе степеней свободы. Fфакт > Fтеор - уравнение регрессии адекватно.
n - число наблюдений (уровней ряда), m - число параметров уравнения (модели) регрессии.
Проверка адекватности уравнения регрессии (качества модели в целом) осуществляется с помощью средней ошибки аппроксимации, величина которой не должна превышать 10-12% (рекомендовано).
Курсовая работа
по дисциплине «Эконометрика»
«Комплексный анализ взаимосвязи финансово-экономических показателей деятельности предприятий»
Вариант № 12
Выполнил:
студент группы ЭЭТ-312
Логунов Н.Ю.
Проверила:
доц. Ишханян М.В.
Москва 2015
Постановка задачи
1. Составление корреляционной матрицы. Отбор факторов
2. Построение уравнения множественной линейной регрессии. Интерпретация параметров уравнения
3. Коэффициент детерминации, множественный коэффициент корреляции
4.Оценка качества уравнения множественной линейной регрессии
4.1.Средняя относительная ошибка аппроксимации
4.2.Проверка статистической значимости уравнения множественной регрессии в целом с помощью F-критерия Фишера
4.3.Проверка статистической значимости параметров уравнения множественной регрессии. Интервальные оценки параметров
5.Применение регрессионной модели
5.1.Точечный прогноз
5.2.Частные коэффициенты эластичности и средние частные коэффициенты эластичности
6.Анализ остатков регрессионной модели (проверка предпосылок теоремы Гаусса-Маркова)
6.1.Оценки математического ожидания остатков
6.2.Проверка наличия автокорреляции в остатках
7.Критерий Грегори Чоу
Постановка задачи
Заданы значения 6 показателей, характеризующих экономическую деятельность 53 предприятий. Требуется:
1. Составить корреляционную матрицу. Скорректировать набор независимых переменных (отобрать 2 фактора).
4.2. Проверить статистическую значимость уравнения множественной регрессии в целом с помощью F-критерия Фишера. Сделать выводы
4.3. Проверить статистическую значимость параметров уравнения множественной регрессии. Построить интервальные оценки параметров. Сделать выводы.
5. Применение регрессионной модели:
5.1. Используя построенное уравнение, дать точечный прогноз. Найти значение исследуемого параметра y, если значение первого фактора (наиболее тесно связанного с у) составит 110% от его среднего значения, значение второго фактора составит 80% от его среднего значения. Дать экономическую интерпретацию результата.
5.2. Найти частные коэффициенты эластичности и средние частные коэффициенты эластичности. Интерпретировать результаты. Сделать выводы.
6. Провести анализ остатков регрессионной модели (проверить требования теоремы Гаусса-Маркова):
6.1. Найти оценки математического ожидания остатков.
6.2. Проверить наличие автокорреляции в остатках. Сделать вывод.
7. Разделите выборку на две равные части. Рассматривая первые и последние наблюдения как независимые выборки, проверить гипотезу о возможности объединения их в единую выборку по критерию Грегори-Чоу.
Составление корреляционной матрицы. Отбор факторов
| № предприятия | Y3 | X10 | X12 | X5 | X7 | X13 | |
| 13,26 | 1,45 | 167,69 | 0,78 | 1,37 | |||
| 10,16 | 1,3 | 186,1 | 0,75 | 1,49 | |||
| 13,72 | 1,37 | 220,45 | 0,68 | 1,44 | |||
| 12,85 | 1,65 | 169,3 | 0,7 | 1,42 | |||
| 10,63 | 1,91 | 39,53 | 0,62 | 1,35 | |||
| 9,12 | 1,68 | 40,41 | 0,76 | 1,39 | |||
| 25,83 | 1,94 | 102,96 | 0,73 | 1,16 | |||
| 23,39 | 1,89 | 37,02 | 0,71 | 1,27 | |||
| 14,68 | 1,94 | 45,74 | 0,69 | 1,16 | |||
| 10,05 | 2,06 | 40,07 | 0,73 | 1,25 | |||
| 13,99 | 1,96 | 45,44 | 0,68 | 1,13 | |||
| 9,68 | 1,02 | 41,08 | 0,74 | 1,1 | |||
| 10,03 | 1,85 | 136,14 | 0,66 | 1,15 | |||
| 9,13 | 0,88 | 42,39 | 0,72 | 1,23 | |||
| 5,37 | 0,62 | 37,39 | 0,68 | 1,39 | |||
| 9,86 | 1,09 | 101,78 | 0,77 | 1,38 | |||
| 12,62 | 1,6 | 47,55 | 0,78 | 1,35 | |||
| 5,02 | 1,53 | 32,61 | 0,78 | 1,42 | |||
| 21,18 | 1,4 | 103,25 | 0,81 | 1,37 | |||
| 25,17 | 2,22 | 38,95 | 0,79 | 1,41 | |||
| 19,4 | 1,32 | 81,32 | 0,77 | 1,35 | |||
| 1,48 | 67,26 | 0,78 | 1,48 | ||||
| 6,57 | 0,68 | 59,92 | 0,72 | 1,24 | |||
| 14,19 | 2,3 | 107,34 | 0,79 | 1,40 | |||
| 15,81 | 1,37 | 512,6 | 0,77 | 1,45 | |||
| 5,23 | 1,51 | 53,81 | 0,8 | 1,4 | |||
| 7,99 | 1,43 | 80,83 | 0,71 | 1,28 | |||
| 17,5 | 1,82 | 59,42 | 0,79 | 1,33 | |||
| 17,16 | 2,62 | 36,96 | 0,76 | 1,22 | |||
| 14,54 | 1,75 | 91,43 | 0,78 | 1,28 | |||
| 6,24 | 1,54 | 17,16 | 0,62 | 1,47 | |||
| 12,08 | 2,25 | 27,29 | 0,75 | 1,27 | |||
| 9,49 | 1,07 | 184,33 | 0,71 | 1,51 | |||
| 9,28 | 1,44 | 58,42 | 0,74 | 1,46 | |||
| 11,42 | 1,4 | 59,4 | 0,65 | 1,27 | |||
| 10,31 | 1,31 | 49,63 | 0,66 | 1,43 | |||
| 8,65 | 1,12 | 391,27 | 0,84 | 1,5 | |||
| 10,94 | 1,16 | 258,62 | 0,74 | 1,35 | |||
| 9,87 | 0,88 | 75,66 | 0,75 | 1,41 | |||
| 6,14 | 1,07 | 123,68 | 0,75 | 1,47 | |||
| 12,93 | 1,24 | 37,21 | 0,79 | 1,35 | |||
| 9,78 | 1,49 | 53,37 | 0,72 | 1,4 | |||
| 13,22 | 2,03 | 32,87 | 0,7 | 1,2 | |||
| 17,29 | 1,84 | 45,63 | 0,66 | 1,15 | |||
| 7,11 | 1,22 | 48,41 | 0,69 | 1,09 | |||
| 22,49 | 1,72 | 13,58 | 0,71 | 1,26 | |||
| 12,14 | 1,75 | 63,99 | 0,73 | 1,36 | |||
| 15,25 | 1,46 | 104,55 | 0,65 | 1,15 | |||
| 31,34 | 1,6 | 222,11 | 0,82 | 1,87 | |||
| 11,56 | 1,47 | 25,76 | 0,8 | 1,17 | |||
| 30,14 | 1,38 | 29,52 | 0,83 | 1,61 | |||
| 19,71 | 1,41 | 41,99 | 0,7 | 1,34 | |||
| 23,56 | 1,39 | 78,11 | 0,74 | 1,22 |
1.Составить корреляционную матрицу. Скорректировать набор независимых переменных (отобрать 2 фактора).
Рассмотрим результативный признак Y3 и факторные признаки Х10, X12, Х5, Х7, Х13 .
Составим корреляционную матрицу с помощью опции «Анализ данных→Корреляция» в MS Excel:
| Y3 | X10 | X12 | X5 | X7 | X13 | |
| Y3 | 1,0000 | 0,3653 | 0,0185 | 0,2891 | 0,1736 | 0,0828 |
| X10 | 0,3653 | 1,0000 | -0,2198 | -0,0166 | -0,2061 | -0,0627 |
| X12 | 0,0185 | -0,2198 | 1,0000 | 0,2392 | 0,3796 | 0,6308 |
| X5 | 0,2891 | -0,0166 | 0,2392 | 1,0000 | 0,4147 | 0,0883 |
| X7 | 0,1736 | -0,2061 | 0,3796 | 0,4147 | 1,0000 | 0,1939 |
| X13 | 0,0828 | -0,0627 | 0,6308 | 0,0883 | 0,1939 | 1,0000 |
Отбираем 2 фактора по критериям:
1) связь Y и X должна быть максимальной
2) связь между Xми должна быть наименьшей
Таким образом, в следующих пунктах работа будет производиться с факторами X10 , X5.
Построение уравнения множественной линейной регрессии. Интерпретация параметров уравнения.
2. Построить уравнение множественной линейной регрессии. Дать интерпретацию параметров уравнения.
Составим регрессионную модель с помощью пакета анализа «Анализ данных→Регрессия» в MS Excel:
| Коэффициенты | |
| Y | -20,7163 |
| X 10 | 5,7169 |
| X 5 | 34,9321 |
Уравнение регрессии будет выглядеть следующим образом:
ŷ = b 0 + b 10 * x 10 + b 5 * x 5
ŷ = -20,7163-5,7169* x 10 +34,9321* x 5
1) b10 положительный;
2) b5 положительный;
Коэффициент детерминации, множественный коэффициент корреляции
3. Найти коэффициент детерминации, множественный коэффициент корреляции. Сделать выводы.
В регрессионном анализе, выполненном с помощью пакета анализа «Анализ данных→Регрессия» в MS Excel, найдём таблицу «Регрессионная статистика»:
Множественный R-связь между Y3 и X10,X5 слабая
R-квадрат-22,05% вариации признака Y объясняется вариацией признаков X10 и X5
Оценка качества уравнения множественной линейной регрессии
4. Оценить качество уравнения множественной линейной регрессии:
Средняя относительная ошибка аппроксимации
4.1. Найти среднюю относительную ошибку аппроксимации. Сделать выводы.
Рассчитаем прогнозные значения для каждого наблюдения или воспользуемся столбцом «Предсказанное У» в таблице «Вывод остатка» в регрессионном анализе, выполненном с помощью пакета анализа «Анализ данных→Регрессия» в MS Excel)
Вычислим относительные ошибки для каждого наблюдения по формуле:
Вычислим среднюю относительную ошибку аппроксимации по формуле:
Вывод: 20% < А < 50%, качество уравнения среднее (удовлетворительное).
Ошибка аппроксимации - один из наиболее часто возникающих вопросов при применении тех или иных методов аппроксимации исходных данных. Есть разного рода ошибки аппроксимации:
Ошибки, связанные с погрешностями исходных данных;
Ошибки, связанные с несоответствием аппроксимирующей модели структуре аппроксимируемых данных.
В Excel есть хорошо разработанная функция Линейн, предназначенная для обработки данных и аппроксимаций, в которой задействован отлаженный математический аппарат. Для того, чтобы иметь о ней представление, обратимся (через F1) к описательной части этой разработки, которую приводим с сокращениями и некоторыми изменениями обозначений.
Расчитывает статистику для ряда с применением метода наименьших квадратов, чтобы вычислить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные. Функция возвращает массив, который описывает полученную прямую. Поскольку возвращается массив значений, функция должна задаваться в виде формулы массива.
Уравнение для прямой линии имеет следующий вид:
y=a+b1*x1+b2*x2+...bn*xn
Синтаксис:
ЛИНЕЙН(y;x;конст;статистика)
Массив y - известные значения y.
Массив x - известные значеня x. Массив x может содержать одно или несколько множеств переменных.
Конст - это логическое значение, которое указывает, требуется ли, чтобы свободный член a был равен 0.
Если аргумент конст имеет значение ИСТИНА, 1 или опущено, то a вычисляется обычным образом. Если аргумент конст имеет значение ЛОЖЬ или 0, то a полагается равным 0.
Статистика - это логическое значение, которое указывает, требуется ли вернуть дополнительную статистику по регрессии. Если аргумент статистика имеет значение ИСТИНА или 1, то функция ЛИНЕЙН возвращает дополнительную регрессионную статистику. Если аргумент статистика имеет значение ЛОЖЬ, 0 или опущена, то функция ЛИНЕЙН возвращает только коэффициенты и свободный член.
Дополнительная регрессионая статистика:
se1,se2,...,sen - стандартные значения ошибок для коэффициентов b1,b2,...,bn.
sea - стандартное значение ошибки для постоянной a (sea = #Н/Д, если конст имеет значение ЛОЖЬ).
r2 - коэффициент детерминированности. Сравниваются фактические значения y и значения, получаемые из уравнения прямой; по результатам сравнения вычисляется коэффициент детерминированности, нормированный от 0 до 1. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y. Для получения информации о том, как вычисляется r2, см. "Замечания" в конце данного раздела.
sey - стандартная ошибка для оценки y.
F-статистика, или F-наблюдаемое значение. F-статистика используется для определения того, является ли наблюдаемая взаимосвязь между зависимой и независимой переменными случайной или нет.
df - степени свободы. Степени свободы полезны для нахождения F-критических значений в статистической таблице. Для определения уровня надежности модели нужно сравнить значения в таблице с F-статистикой, возвращаемой функцией ЛИНЕЙН.
ssreg - регрессионая сумма квадратов.
ssresid - остаточная сумма квадратов.
На приведенном ниже рисунке показано, в каком порядке возвращается дополнительная регрессионная статистика.
Замечания
Выборочную информацию из функции можно получить через функцию ИHДЕКС, например:
Y-пересечение (свободный член):
ИНДЕКС(ЛИНЕЙН(y;x);2)
Точность аппроксимации с помощью прямой, вычисленной функцией ЛИНЕЙН, зависит от степени разброса данных. Чем ближе данные к прямой, тем более точной является модель, используемая функцией ЛИНЕЙН. Функция ЛИНЕЙН использует метод наименьших квадратов для определения наилучшей аппроксимации данных.
Проводя регрессионный анализ, Microsoft Excel вычисляет для каждой точки квадрат разности между прогнозируемым значением y и фактическим значением y. Сумма этих квадратов разностей называется остаточной суммой квадратов. Затем Microsoft Excel подсчитывает сумму квадратов разностей между фактическими значениями y и средним значением y, которая называется общей суммой квадратов (регрессионая сумма квадратов + остаточная сумма квадратов). Чем меньше остаточная сумма квадратов по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности r2, который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными.
Заметьте, что значения y, предсказанные с помощью уравнения регрессии, возможно не будут правильными, если они располагаются вне интервала значений y, которые использовались для определения уравнения.
Пример 1 Наклон и Y-пересечение
ЛИНЕЙН({1;9;5;7};{0;4;2;3}) равняется {2;1}, наклон = 2 и y-пересечение = 1.
Использование статистик F и R2
Можно использовать F-статистику, чтобы определить, является ли результат с высоким значение r2 случайным. Если F-наблюдаемое больше, чем F-критическое, то взаимосвязь между переменными имеется. F-критическое можно получить из таблицы F-критических значений в любом справочнике по математической статистике. Для того, чтобы найти это значение, используя односторонний тест, положим величину Альфа (величина Альфа используется для обозначения вероятности ошибочного вывода о том, что имеется сильная взаимозависимость) равной 0,05, а для числа степеней свободы (обозначаемых обычно v1 и v2), положим v1 = k = 4 и v2 = n - (k + 1) = 11 - (4 + 1) = 6, где k - это число переменных, а n - число точек данных. Из таблицы справочника F-критическое равно 4,53. Наблюдаемое F-значение равно 459,753674 (это значение получено в опущенном нами примере), что заметно больше чем F-критическое значение 4,53. Следовательно, полученное регрессионное уравнение полезно для предсказания искомого результата.
