Корреляционно-регрессионный анализ в Excel: инструкция выполнения. Исследование статистической зависимости изменения свойств коллектора и пластовых флюидов в результате разработки нефтяных месторождений Параболическая и полиномиальная регрессии
Имеются следующие данные разных стран об индексе розничных цен на продукты питания (х) и об индексе промышленного производства (у).
Индекс розничных цен на продукты питания (х) | Индекс промышленного производства (у) | |
---|---|---|
1 | 100 | 70 |
2 | 105 | 79 |
3 | 108 | 85 |
4 | 113 | 84 |
5 | 118 | 85 |
6 | 118 | 85 |
7 | 110 | 96 |
8 | 115 | 99 |
9 | 119 | 100 |
10 | 118 | 98 |
11 | 120 | 99 |
12 | 124 | 102 |
13 | 129 | 105 |
14 | 132 | 112 |
Требуется:
1. Для характеристики зависимости у от х рассчитать параметры следующих функций:
А) линейной;
Б) степенной;
В) равносторонней гиперболы.
3. Оценить статистическую значимость параметров регрессии и корреляции.
4. Выполнить прогноз значения индекса промышленного производства у при прогнозном значении индекса розничных цен на продукты питания х=138.
Решение:
1. Для расчёта параметров линейной регрессии
Решаем систему нормальных уравнений относительно a и b:
Построим таблицу расчётных данных, как показано в таблице 1.
Таблица 1 Расчетные данные для оценки линейной регрессии
№ п/п | х | у | ху | x 2 | y 2 | ||
---|---|---|---|---|---|---|---|
1 | 100 | 70 | 7000 | 10000 | 4900 | 74,26340 | 0,060906 |
2 | 105 | 79 | 8295 | 11025 | 6241 | 79,92527 | 0,011712 |
3 | 108 | 85 | 9180 | 11664 | 7225 | 83,32238 | 0,019737 |
4 | 113 | 84 | 9492 | 12769 | 7056 | 88,98425 | 0,059336 |
5 | 118 | 85 | 10030 | 13924 | 7225 | 94,64611 | 0,113484 |
6 | 118 | 85 | 10030 | 13924 | 7225 | 94,64611 | 0,113484 |
7 | 110 | 96 | 10560 | 12100 | 9216 | 85,58713 | 0,108467 |
8 | 115 | 99 | 11385 | 13225 | 9801 | 91,24900 | 0,078293 |
9 | 119 | 100 | 11900 | 14161 | 10000 | 95,77849 | 0,042215 |
10 | 118 | 98 | 11564 | 13924 | 9604 | 94,64611 | 0,034223 |
11 | 120 | 99 | 11880 | 14400 | 9801 | 96,91086 | 0,021102 |
12 | 124 | 102 | 12648 | 15376 | 10404 | 101,4404 | 0,005487 |
13 | 129 | 105 | 13545 | 16641 | 11025 | 107,1022 | 0,020021 |
14 | 132 | 112 | 14784 | 17424 | 12544 | 110,4993 | 0,013399 |
Итого: | 1629 | 1299 | 152293 | 190557 | 122267 | 1299,001 | 0,701866 |
Среднее значение: | 116,3571 | 92,78571 | 10878,07 | 13611,21 | 8733,357 | х | х |
8,4988 | 11,1431 | х | х | х | х | х | |
72,23 | 124,17 | х | х | х | х | х |
Среднее значение определим по формуле:
Cреднее квадратическое отклонение рассчитаем по формуле:
и занесём полученный результат в таблицу 1.
Возведя в квадрат полученное значение получим дисперсию:
Параметры уравнения можно определить также и по формулам:
Таким образом, уравнение регрессии:
Следовательно, с увеличением индекса розничных цен на продукты питания на 1, индекс промышленного производства увеличивается в среднем на 1,13.
Рассчитаем линейный коэффициент парной корреляции:
Связь прямая, достаточно тесная.
Определим коэффициент детерминации:
Вариация результата на 74,59% объясняется вариацией фактора х.
Подставляя в уравнение регрессии фактические значения х, определим теоретические (расчётные) значения .
следовательно, параметры уравнения определены правильно.
Рассчитаем среднюю ошибку аппроксимации - среднее отклонение расчётных значений от фактических:
В среднем расчётные значения отклоняются от фактических на 5,01%.
Оценку качества уравнения регрессии проведём с помощью F-теста.
F-тест состоит в проверке гипотезы Н 0 о статистической незначимости уравнения регрессии и показателя тесноты связи. Для этого выполняется сравнение фактического F факт и критического (табличного) F табл значений F-критерия Фишера.
F факт определяется по формуле:
где n - число единиц совокупности;
m - число параметров при переменных х.
Полученные оценки уравнения регрессии позволяют использовать его для прогноза.
Если прогнозное значение индекса розничных цен на продукты питания х = 138, тогда прогнозное значение индекса промышленного производства составит:
2. Степенная регрессия имеет вид:
Для определения параметров производят логарифмиро-вание степенной функции:
Для определения параметров логарифмической функции строят систему нормальных уравнений по способу наи-меньших квадратов:
Построим таблицу расчётных данных, как показано в таблице 2.
Таблица 2 Расчетные данные для оценки степенной регрессии
№п/п | х | у | lg x | lg y | lg x*lg y | (lg x) 2 | (lg y) 2 |
---|---|---|---|---|---|---|---|
1 | 100 | 70 | 2,000000 | 1,845098 | 3,690196 | 4,000000 | 3,404387 |
2 | 105 | 79 | 2,021189 | 1,897627 | 3,835464 | 4,085206 | 3,600989 |
3 | 108 | 85 | 2,033424 | 1,929419 | 3,923326 | 4,134812 | 3,722657 |
4 | 113 | 84 | 2,053078 | 1,924279 | 3,950696 | 4,215131 | 3,702851 |
5 | 118 | 85 | 2,071882 | 1,929419 | 3,997528 | 4,292695 | 3,722657 |
6 | 118 | 85 | 2,071882 | 1,929419 | 3,997528 | 4,292695 | 3,722657 |
7 | 110 | 96 | 2,041393 | 1,982271 | 4,046594 | 4,167284 | 3,929399 |
8 | 115 | 99 | 2,060698 | 1,995635 | 4,112401 | 4,246476 | 3,982560 |
9 | 119 | 100 | 2,075547 | 2,000000 | 4,151094 | 4,307895 | 4,000000 |
10 | 118 | 98 | 2,071882 | 1,991226 | 4,125585 | 4,292695 | 3,964981 |
11 | 120 | 99 | 2,079181 | 1,995635 | 4,149287 | 4,322995 | 3,982560 |
12 | 124 | 102 | 2,093422 | 2,008600 | 4,204847 | 4,382414 | 4,034475 |
13 | 129 | 105 | 2,110590 | 2,021189 | 4,265901 | 4,454589 | 4,085206 |
14 | 132 | 112 | 2,120574 | 2,049218 | 4,345518 | 4,496834 | 4,199295 |
Итого | 1629 | 1299 | 28,90474 | 27,49904 | 56,79597 | 59,69172 | 54,05467 |
Среднее значение | 116,3571 | 92,78571 | 2,064624 | 1,964217 | 4,056855 | 4,263694 | 3,861048 |
8,4988 | 11,1431 | 0,031945 | 0,053853 | х | х | х | |
72,23 | 124,17 | 0,001021 | 0,0029 | х | х | х |
Продолжение таблицы 2 Расчетные данные для оценки степенной регрессии
№п/п | х | у | ||||
---|---|---|---|---|---|---|
1 | 100 | 70 | 74,16448 | 17,34292 | 0,059493 | 519,1886 |
2 | 105 | 79 | 79,62057 | 0,385112 | 0,007855 | 190,0458 |
3 | 108 | 85 | 82,95180 | 4,195133 | 0,024096 | 60,61728 |
4 | 113 | 84 | 88,59768 | 21,13866 | 0,054734 | 77,1887 |
5 | 118 | 85 | 94,35840 | 87,57961 | 0,110099 | 60,61728 |
6 | 118 | 85 | 94,35840 | 87,57961 | 0,110099 | 60,61728 |
7 | 110 | 96 | 85,19619 | 116,7223 | 0,11254 | 10,33166 |
8 | 115 | 99 | 90,88834 | 65,79901 | 0,081936 | 38,6174 |
9 | 119 | 100 | 95,52408 | 20,03384 | 0,044759 | 52,04598 |
10 | 118 | 98 | 94,35840 | 13,26127 | 0,037159 | 27,18882 |
11 | 120 | 99 | 96,69423 | 5,316563 | 0,023291 | 38,6174 |
12 | 124 | 102 | 101,4191 | 0,337467 | 0,005695 | 84,90314 |
13 | 129 | 105 | 107,4232 | 5,872099 | 0,023078 | 149,1889 |
14 | 132 | 112 | 111,0772 | 0,85163 | 0,00824 | 369,1889 |
Итого | 1629 | 1299 | 1296,632 | 446,4152 | 0,703074 | 1738,357 |
Среднее значение | 116,3571 | 92,78571 | х | х | х | х |
8,4988 | 11,1431 | х | х | х | х | |
72,23 | 124,17 | х | х | х | х |
Решая систему нормальных уравнений, определяем параметры логарифмической функции.
Получим линейное уравнение:
Выполнив его потенцирование, получим:
Подставляя в данное уравнение фактические значения х, получаем теоретические значения результата . По ним рассчитаем показатели: тесноты связи - индекс корреляции и среднюю ошибку аппроксимации.
Связь достаточно тесная.
В среднем расчётные значения отклоняются от фактических на 5,02%.
Таким образом, Н 0 - гипотеза о случайной природе оцениваемых характеристик отклоняется и признаётся их статистическая значимость и надёжность.
Полученные оценки уравнения регрессии позволяют использовать его для прогноза. Если прогнозное значение индекса розничных цен на продукты питания х = 138, тогда прогнозное значение индекса промышленного производства составит:
Для определения параметров этого уравнения используется система нормальных уравнений:
Произведем замену переменных
и получим следующую систему нормальных уравнений:
Решая систему нормальных уравнений, определяем параметры гиперболы.
Составим таблицу расчётных данных, как показано в таблице 3.
Таблица 3 Расчетные данные для оценки гиперболической зависимости
№п/п | х | у | z | yz | ||
---|---|---|---|---|---|---|
1 | 100 | 70 | 0,010000000 | 0,700000 | 0,0001000 | 4900 |
2 | 105 | 79 | 0,009523810 | 0,752381 | 0,0000907 | 6241 |
3 | 108 | 85 | 0,009259259 | 0,787037 | 0,0000857 | 7225 |
4 | 113 | 84 | 0,008849558 | 0,743363 | 0,0000783 | 7056 |
5 | 118 | 85 | 0,008474576 | 0,720339 | 0,0000718 | 7225 |
6 | 118 | 85 | 0,008474576 | 0,720339 | 0,0000718 | 7225 |
7 | 110 | 96 | 0,009090909 | 0,872727 | 0,0000826 | 9216 |
8 | 115 | 99 | 0,008695652 | 0,860870 | 0,0000756 | 9801 |
9 | 119 | 100 | 0,008403361 | 0,840336 | 0,0000706 | 10000 |
10 | 118 | 98 | 0,008474576 | 0,830508 | 0,0000718 | 9604 |
11 | 120 | 99 | 0,008333333 | 0,825000 | 0,0000694 | 9801 |
12 | 124 | 102 | 0,008064516 | 0,822581 | 0,0000650 | 10404 |
13 | 129 | 105 | 0,007751938 | 0,813953 | 0,0000601 | 11025 |
14 | 132 | 112 | 0,007575758 | 0,848485 | 0,0000574 | 12544 |
Итого: | 1629 | 1299 | 0,120971823 | 11,13792 | 0,0010510 | 122267 |
Среднее значение: | 116,3571 | 92,78571 | 0,008640844 | 0,795566 | 0,0000751 | 8733,357 |
8,4988 | 11,1431 | 0,000640820 | х | х | х | |
72,23 | 124,17 | 0,000000411 | х | х | х |
Продолжение таблицы 3 Расчетные данные для оценки гиперболической зависимости
Ещё один вид однофакторной регрессии – аппроксимация степенными полиномами вида:
Естественно
желание получить как можно простую
зависимость, ограничиваясь степенным
полиномам второй степени, т.е. параболической
зависимостью:
(5.5.2)
Вычислим частные производные по коэффициентам b 0 , b 1 и b 2 :
(5.5.3)
Приравнивая производные нулю получим нормальных систему уравнений:
(5.5.4)
Решая систему
нормальных уравнений (5.5.2) для конкретного
случая значений x
i
*
,
y
i
*
;
получим
оптимальные
значения b
0
,
b
1
и b
2
.
Для
аппроксимации зависимостью (5.5.2) и тем
более (5.5.1) не получены простые формулы
для вычисления коэффициентов и как
правило их вычисление производят по
стандартным процедурам в матричном
виде:
(5.5.5)
На рис.5.5.1
приведён типовой пример аппроксимации
параболической
зависимостью:
9 (5;9)
(1;1)
1
1 2 3 4 5 х
Рис.5.5.1. Координаты экспериментальных точек и аппроксимиру-
щая их параболическая зависимость
Пример
5.1.
Провести
аппроксимацию результатов эксперимента,
приведённых в таблице 5.1.1, линейным
уравнением регрессии
.
Таблица 5.1.1
Построим экспериментальные точки по координатам, указанным в таблице 5.1.1 на графике, представленном на рис.5.1.1.
у
9
4
1 2 3 4 5 х
По рис.5.1.1, на котором для предварительной оценки проведём прямую линию, сделаем заключение, что в расположении экспериментальных точек имеется явно выраженная нелинейность, но она не очень значительная и поэтому имеет смысл провести их аппроксимацию линейной зависимостью. Отметим, что для получения корректно-математического заключения требуется построить прямую линию методом наименьших квадратов.
До проведения регрессионного анализа целесообразно вычислить
коэффициент линейной корреляции между переменными х и у :
Существенность корреляционной связи определяется по критическому значению коэффициента линейной корреляции, вычисляемого по формуле:
Критическое значение критерия Стьюдента t крит находится по статистическим таблицам для рекомендуемого уровня значимости α=0.05 и для n -2 степеней свободы. Если вычисленное значение r xy не меньше критического значения r крит , то корреляционная связь между переменными x и y считается сушественной. Произведём вычисления:
Ввиду того, что
делаем заключение, что корреляционная
связь между переменнымих
и у
является существенной и она может быть
линейной.
Вычислим коэффициенты уравнения регрессии:
Таким образом, получили линейное уравнение регрессии:
По уравнению регрессии проведём прямую линию на рис.5.1.2.
у (5;9.8)
9
4
(0;-0.2) 1 2 3 4 5 х
Рис.5.1.2. Координаты экспериментальных точек и аппроксимиру-
щая
их линейная зависимость
По уравнению регрессии вычислим значения функции по экспериментальным точкам таблицы 5.1.1 и разницу между экспериментальными и вычисленными значениями функции, которые представим в таблице 5.1.2.
Таблица 5.1.2
Вычислим среднюю квадратическую ошибку и её отношение к среднему значению:
По отношению стандартной ошибки к среднему значению получен неудовлетворительный результат, так как превышено рекомендуемое значение в 0.05.
Проведём оценку уровня значимости коэффициентов уравнения регрессии по критерию Стьюдента:
Из статистической таблицы для 3 степеней свободы выпишем строки с уровнем значимости -и значением критерия Стьюдента– t в таблицу 5.1.3.
Таблица 5.1.3
Уровень значимости коэффициентов уравнения регрессии:
Отметим, что по уровню значимости для коэффициента получен удовлетворительный результат, а для коэффициентанеудовлетворительный.
Проведём оценку качества полученного уравнения регрессии по показателям, вычисляемым на основе дисперсионного анализа:
Проверка:
Результат проверки – положительный, что свидетельствует о корректности проведённых вычислений.
Вычислим критерий Фишера:
при двух степенях
свободы:
По статистическим таблицам находим критические значения критерия Фишера для двух рекомендуемых градаций уровня значимости:
Так как вычисленное значение критерия Фишера превосходит критическое дл уровня значимости 0,01, то будем считать, что уровень значимости по критерию Фишера меньше 0,01, что будем считать удовлетворительным.
Вычислим коэффициент множественной детерминации:
для двух степеней свободы
По статистической
таблице для рекомендуемого уровня
значимости 0,05и двух найденных степеней
свободы находим критическое значение
коэффициента множественной детерминации:
Так
как вычисленное значение коэффициента
множественной детерминации превышает
критическое значение для уровня
значимости
,
то уровень значимости по коэффициенту
множественной детерминации
и полученный результат поданному
показателю будем считать удовлетворительным.
Таким образом, полученные расчётные параметры по отношению стандартной ошибки к среднему значению и уровню значимости по критерию Стьюдента являются неудовлетворительными, поэтому целесообразно для аппроксимации подобрать другую аппроксимирующую зависимость.
Пример 5.2.
Аппроксимация экспериментального
распределения случайных чисел
математической зависимостью
Экспериментальное распределение случайных чисел, приведённое в таблице 5.1.1, при аппроксимации линейной зависимостью, не привело к удовлетворительному результату, в т.ч. по незначимости коэффициента уравнения регрессии при свободном члене, поэтому для улучшения качества аппроксимации попробуем её провести линейной зависимостью без свободного члена:
Вычислим значение коэффициента уравнения регрессии:
Таким образом,
получили уравнение регрессии:
По полученному уравнению регрессии вычислим значения функции и разницу между экспериментальными и вычисленными значениями функции, которые представим в виде таблицы 5.2.1.
Таблица 5.2.1
x i | ||||
По уравнению
регрессии
на рис.5.2.1 проведём прямую линию.
у (5;9. 73 )
(0;0) 1 2 3 4 5 х
Рис.5.2.1. Координаты экспериментальных точек и аппроксимиру-
ющая
их линейная зависимость
Для оценки качества аппроксимации проведём вычисления показателей качества аналогично вычислениям, приведённым в примере 5.1.
(осталось старым);
с 4-мя степенями свободы;
для
По результатам проведённой аппроксимации отметим, что по уровню значимости коэффициента уравнения регрессии получен удовлетворительный результат; отношение стандартной ошибки к среднему значению улучшилось, но всё ещё осталось выше рекомендуемого значения 0.05, поэтому рекомендуется повторить аппроксимацию более сложной математической зависимостью.
Пример
5.3.
Для
улучшения качества аппроксимации
примеров 5.1 и 5.2 проведём нелинейную
аппроксимацию зависимостью
.
Для этого первоначально произведём
промежуточные вычисления и их результаты
поместим в таблицу 5.3.1.
Значения
Таблица 5.3.1
X 2 | ||||||
(lnX ) 2 | ||||||
lnX·lnY |
Дополнительно
вычислим:
Произведём
аппроксимацию зависимостью
.
По формулам (5.3.7), (5.3.8) вычислим коэффициентыb
0
и b
1
:
По формулам (5.3.11) вычислим коэффициенты A 0 и A 1 :
Для вычисления стандартной ошибки проведены промежуточные вычисления, представленные в таблице 5.3.2.
Таблица 5.3.2
Y i |
y i | ||
Сумма: 7,5968
Стандартная ошибка аппроксимации получилась намного больше, чем в двух предыдущих примерах, поэтому результаты аппроксимации признаем непригодными.
Пример
5.4.
Попробуем
провести аппроксимацию ещё одной
нелинейной зависимостью
.
По формулам (5.3.9), (5.3.10) по данным таблицы
5.3.1 вычислим коэффициентыb
0
и b
1
:
Получили промежуточную зависимость:
По формулам (5.3.13) вычислим коэффициенты C 0 и C 1 :
Получили окончательную зависимость:
Для вычисления стандартной ошибки проведём промежуточные вычисления и поместим их в таблицу 5.4.1.
Таблица 5.4.1
Y i |
y i | ||
Сумма: 21,83152
Вычислим стандартную ошибку:
Стандартная ошибка аппроксимации получилась намного больше, чем в предыдущем примере, поэтому результаты аппроксимации признаем непригодными.
Пример 5.5. Аппроксимация экспериментального распределения случайных чисел математической зависимостью y = b · lnx
Исходные данные как и в предыдущих примерах приведены в таблице 5.4.1 и на рис.5.4.1.
Таблица 5.4.1
На основании анализа рис.5.4.1 и таблицы 5.4.1 отметим, что при меньших значениях аргумента (в начале таблицы) функция изменяется сильнее, чем при больших (в конце таблицы) поэтому представляется целесообразным изменить масштаб аргумента и ввести в уравнение регрессии логарифмическую функцию от него и провести аппроксимацию следующей математической зависимостью:
. По формуле (5.4.3) вычислим коэффициент b :
Для оценки качества аппроксимации проведём промежуточные вычисления, представленные в таблице 5.4.2, по которым вычислим величину ошибки и отношение стандартной ошибки к среднему значению.
Таблица 5.4.2
Так как по отношению стандартной ошибки к среднему значению превышено рекомендуемое значение 0,05, то результат будем считать неудовлетворительным. В частности, отметим, что наибольшее отклонение даёт значение х=1, так как при этом значении lnx =0. Поэтому проведём аппроксимацию зависимстью y = b 0 +b 1 ·lnx
Вспомогательные вычисления представим в виде таблицы 5.4.3.
Таблица 5.4.3
По формулам (5.4.6) и (5.4.7) вычислим коэффициенты b 0 и b 1 :
9 (5;9.12)
4
1 (1;0.93)
1 2 3 4 5 х
Для оценки качества аппроксимации проведём вспомогательные вычисления и определим уровень значимости найденных коэффициентов и отношение стандартной ошибки к среднему значению.
Уровень
значимости
чуть выше рекомендованного значения
0,05 (
).
Ввиду того, что
по главному показателю – отношению
стандартной ошибки к среднему значению
получено почти двукратное превышение
рекомендуемого уровня 0,05 результаты
будем считать приемлемыми. Отметим, что
вычисленное значение критерия Стьюдента
t
b
0
=2,922
отличается от критического
сравнительно на небольшую величину.
Пример 5.6.
Проведём
аппроксимацию экспериментальных данных
примера 5.1 гиперболической зависимостью
. Для того, чтобы вычислить коэффициентовb
0
и
b
1
проведём
предварительные вычисления, приведённые
в таблице 5.6.1.
Таблица 5.6.1
X i |
x i =1/X i |
x i 2 |
x i y i | ||
По результатам таблицы 5.6.1 по формулам (5.4.8) и (5.4.9) вычислим коэффициенты b 0 и b 1 :
Таким образом, получено гиперболическое уравнение регрессии
.
Результаты вспомогательных вычислений для оценки качества аппроксимации приведены в таблице 5.6.2.
Таблица 5.6.2
X i | ||||
По результатам таблицы 5.6.2 вычислим стандартную ошибку и отношение стандартной ошибки к среднему значению:
Ввиду того, что отношение стандартной ошибки к среднему значению превышает рекомендуемое значение 0,05 делаем заключение о непригодности результатов аппроксимации.
Пример 5.7.
Для вычисления конкретных значений доходов от работы стреловых кранов в зависимости от времени проведения профилактических работ требуется получить параболическую зависимость .
Вычислим коэффициенты этой зависимости b 0 , b 1 , b 11 в матричном виде по формуле:
Нелинейные уравнения регрессии, связывающие результативный показатель с оптимальными значениями проведения профилактических работ башенных кранов, получены с помощью процедуры множественной регрессии пакета прикладных программ Statistica 6.0. Далее приведем результаты регрессионного анализа для результативного показателя эффективности по таблице 5.7.1.
Таблица 5.7.1
В таблице 5.7.2 приведены результаты нелинейной регрессии для результативного показателя эффективности и в таблице 5.7.3 результаты анализа остатков.
Таблица 5.7.2
Таблица 5.7.3
Рис. 3.7.36. Анализ остатков.
Таким образом,
получили уравнение множественной
регрессии для переменной
:
Отношение стандартной ошибки к среднему значению:
14780/1017890=0,0145 < 0,05.
Так как отношение стандартной ошибки к среднему значению не превышает рекомендуемого значения 0,05 то результаты аппроксимации можно считать приемлемыми. В качестве недостатка по таблице 5.7.2 следует отметить превышение рекомендуемого уровня значимости 0.05 всеми вычисленными коэффициентами.
Рассмотрим построение уравнения регрессии вида .
Составление системы нормальных уравнений для нахождения коэффициентов параболической регрессии осуществляется аналогично составлению нормальных уравнений линейной регрессии.
После преобразований получаем:
.
Решая систему нормальных уравнений, получают коэффициенты уравнения регрессии.
,
где , а .
Уравнение второй степени значимо лучше описывает экспериментальные данные, чем уравнение первой степени, если уменьшение дисперсии по сравнению с дисперсией линейной регрессии является значимым (неслучайным). Значимость различия между и оценивается критерием Фишера:
где число берется по справочным статистическим таблицам (приложение 1) соответственно степеням свободы и выбранного уровня значимости .
Порядок выполнения расчетной работы:
1. Ознакомиться с теоретическим материалом, изложенным в методических указаниях либо в дополнительной литературе.
2. Рассчитать коэффициенты линейного уравнения регрессии . Для этого необходимо вычислить суммы . Удобно сразу вычислить суммы , которые пригодятся для расчета коэффициентов параболического уравнения.
3. Вычислить расчетные значения выходного параметра по уравнению .
4. Вычислить общую и остаточную дисперсии , , а также критерий Фишера .
где – матрица, элементами которой являются коэффициенты системы нормальных уравнений;
– вектор, элементами которого являются неизвестные коэффициенты;
– матрица правых частей системы уравнений.
7. Вычислить расчетные значения выходного параметра по уравнению .
8. Вычислить остаточную дисперсию , а также критерий Фишера .
9. Сделать выводы.
10. Построить графики уравнений регрессии и исходных данных.
11. Оформить расчетную работу.
Пример расчета.
По экспериментальным данным зависимости плотности водяного пара от температуры получить уравнения регрессии вида и . Провести статистический анализ и сделать вывод о лучшей эмпирической зависимости.
0,0512 | 0,0687 | 0,081 | 0,1546 | 0,2516 | 0,3943 | 0,5977 | 0,8795 |
Обработка экспериментальных данных проведена в соответствии с рекомендациями к работе. Расчеты для определения параметров линейного уравнения приведены в таблице 1.
Таблица 1 – Нахождение параметров линейной зависимости вида | ||||||||
Плотность водяного пара на линии насыщения | ||||||||
№ | t i ,°C | , ом | t i 2 | расч. | ||||
0,0512 | 2,05 | -0,0403 | -0,0915 | 0,0084 | 0,0669 | |||
0,0687 | 3,16 | 0,0248 | -0,0439 | 0,0019 | 0,0582 | |||
0,0811 | 4,22 | 0,0899 | 0,0089 | 0,0001 | 0,0523 | |||
0,1546 | 9,9 | 0,2202 | 0,06565 | 0,0043 | 0,0241 | |||
0,2516 | 19,12 | 0,3505 | 0,09894 | 0,0098 | 0,0034 | |||
0,3943 | 34,70 | 0,4808 | 0,08654 | 0,0075 | 0,0071 | |||
0,5977 | 59,77 | 0,6111 | 0,01344 | 0,0002 | 0,0829 | |||
0,8795 | 98,50 | 0,7414 | -0,13807 | 0,0191 | 0,3245 | |||
сумма | 2,4786 | 231,41 | 0,0512 | 0,6194 | ||||
среднее | 72,25 | 0,3098 | 5822,5 | 28,93 | ||||
b 0 = | -0,4747 | D 1 ост 2 = | 0,0085 | |||||
b 1 = | 0,0109 | D y 2 = | 0,0885 | |||||
F = | 10,368 | |||||||
F T =3,87 F >F T модель адекватна |
.
Для определения параметров параболической регрессии вначале были определены элементы матрицы коэффициентов и матрицы правых частей системы нормальных уравнений. Затем расчет коэффициентов выполнен в среде MathCad:
Данные расчетов приведены в таблице 2.
Обозначения в таблице 2:
.
Выводы
Параболическое уравнение значимо лучше описывает экспериментальные данные зависимости плотности пара от температуры, так как расчетное значение критерия Фишера значительно превышает табличное равное 4,39. Следовательно, включение квадратичного члена в полиномиальное уравнение имеет смысл.
Полученные результаты представлены в графическом виде (рис.3).
Рисунок 3 – Графическая интерпретация результатов расчета.
Пунктирная линия – уравнение линейной регрессии; сплошная линия – параболической регрессии, точки на графике – экспериментальные значения.
Таблица 2. – Нахождение параметров зависимости вида y (t )=a 0 +a 1 ∙x+a 2 ∙x 2 | Плотность водяного пара на линии насыщения ρ= a 0 +a 1 ∙t+a 2 ∙t 2 | (ρ i –ρср) 2 | 0,0669 | 0,0582 | 0,0523 | 0,0241 | 0,0034 | 0,0071 | 0,0829 | 0,03245 | 0,6194 | |||||
(Δρ) 2 | 0,0001 | 0,0000 | 0,0000 | 0,0002 | 0,0000 | 0,0002 | 0,0002 | 0,0002 | 0,0010 | 0,0085 | 0,0002 | 0,0885 | 42,5 | |||
∆ρ i =ρ(t i )расч–ρ i | 0,01194 | –0,00446 | –0,00377 | –0,01524 | –0,00235 | 0,01270 | 0,011489 | –0,01348 | D 1 2 ост = | D 2 2 ост = | D 1 2 y = | F= | ||||
ρ(t i )расч. | 0,0631 | 0,0642 | 0,0773 | 0,1394- | 0,2493 | 0,4070 | 0,6126 | 0,8660 | 2,4788 | |||||||
t i 2ρ i | 81,84 | 145,33 | 219,21 | 633,24 | 1453,2 | 3053,4 | 5977,00 | 11032,45 | 22595,77 | |||||||
t i 4 | ||||||||||||||||
t i 3 | ||||||||||||||||
t i ρ i | 2,05 | 3,16 | 4,22 | 9,89 | 19,12 | 34,70 | 59,77 | 98,50 | 231,41 | |||||||
t i 2 | ||||||||||||||||
ρ, ом | 0,0512 | 0,0687 | 0,0811 | 0,1546 | 0,2516 | 0,3943 | 0,5977 | 0,8795 | 2,4786 | 0,3098 | ||||||
t i ,°C | 0,36129 | –0,0141 | 1,6613E-04 | |||||||||||||
№ | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | сумма | среднее | a 0 = | a 1 = | a 2 = |
Приложение 1
Таблица распределения Фишера при q = 0,05
f 2 | - | |||||||||
f 1 | ||||||||||
161,40 | 199,50 | 215,70 | 224,60 | 230,20 | 234,00 | 238,90 | 243,90 | 249,00 | 254,30 | |
18,51 | 19,00 | 19,16 | 19,25 | 19,30 | 19,33 | 19,37 | 19,41 | 19,45 | 19,50 | |
10,13 | 9,55 | 9,28 | 9,12 | 9,01 | 8,94 | 8,84 | 8,74 | 8,64 | 8,53 | |
7,71 | 6,94 | 6,59 | 6,39 | 6,76 | 6,16 | 6,04 | 5,91 | 5,77 | 5,63 | |
6,61 | 5,79 | 5,41 | 5,19 | 5,05 | 4,95 | 4,82 | 4,68 | 4,53 | 4,36 | |
5,99 | 5,14 | 4,76 | 4,53 | 4,39 | 4,28 | 4,15 | 4,00 | 3,84 | 3,67 | |
5,59 | 4,74 | 4,35 | 4,12 | 3,97 | 3,87 | 3,73 | 3,57 | 3,41 | 3,23 | |
5,32 | 4,46 | 4,07 | 3,84 | 3,69 | 3,58 | 3,44 | 3,28 | 3,12 | 2,93 | |
5,12 | 4,26 | 3,86 | 3,63 | 3,48 | 3,37 | 3,24 | 3,07 | 2,90 | 2,71 | |
4,96 | 4,10 | 3,71 | 3,48 | 3,33 | 3,22 | 3,07 | 2,91 | 2,74 | 2,54 | |
4,84 | 3,98 | 3,59 | 3,36 | 3,20 | 3,09 | 2,95 | 2,79 | 2,61 | 2,40 | |
4,75 | 3,88 | 3,49 | 3,26 | 3,11 | 3,00 | 2,85 | 2,69 | 2,50 | 2,30 | |
4,67 | 3,80 | 3,41 | 3,18 | 3,02 | 2,92 | 2,77 | 2,60 | 2,42 | 2,21 | |
4,60 | 3,74 | 3,34 | 3,11 | 2,96 | 2,85 | 2,70 | 2,53 | 2,35 | 2,13 | |
4,54 | 3,68 | 3,29 | 3,06 | 2,90 | 2,79 | 2,64 | 2,48 | 2,29 | 2,07 | |
4,49 | 3,63 | 3,24 | 3,01 | 2,82 | 2,74 | 2,59 | 2,42 | 2,24 | 2,01 | |
4,45 | 3,59 | 3,20 | 2,96 | 2,81 | 2,70 | 2,55 | 2,38 | 2,19 | 1,96 | |
4,41 | 3,55 | 3,16 | 2,93 | 2,77 | 2,66 | 2,51 | 2,34 | 2,15 | 1,92 | |
4,38 | 3,52 | 3,13 | 2,90 | 2,74 | 2,63 | 2,48 | 2,31 | 2,11 | 1,88 | |
4,35 | 3,49 | 3,10 | 2,87 | 2,71 | 2,60 | 2,45 | 2,28 | 2,08 | 1,84 | |
4,32 | 3,47 | 3,07 | 2,84 | 2,68 | 2,57 | 2,42 | 2,25 | 2,05 | 1,81 | |
4,30 | 3,44 | 3,05 | 2,82 | 2,66 | 2,55 | 2,40 | 2,23 | 2,03 | 1,78 | |
4,28 | 3,42 | 3,03 | 2,80 | 2,64 | 2,53 | 2,38 | 2,20 | 2,00 | 1,76 | |
4,26 | 3,40 | 3,01 | 2,78 | 2,62 | 2,51 | 2,36 | 2,18 | 1,98 | 1,73 | |
4,24 | 3,38 | 2,99 | 2,76 | 2,60 | 2,49 | 2,34 | 2,16 | 1,96 | 1,71 | |
4,22 | 3,37 | 2,98 | 2,74 | 2,59 | 2,47 | 2,32 | 2,15 | 1,95 | 1,69 | |
4,21 | 3,35 | 2,96 | 2,73 | 2,57 | 2,46 | 2,30 | 2,13 | 1,93 | 1,67 | |
4,20 | 3,34 | 2,95 | 2,71 | 2,56 | 2,44 | 2,29 | 2,12 | 1,91 | 1,65 | |
4,18 | 3,33 | 2,93 | 2,70 | 2,54 | 2,43 | 2,28 | 2,10 | 1,90 | 1,64 | |
4,17 | 3,32 | 2,92 | 2,69 | 2,53 | 2,42 | 2,27 | 2,09 | 1,89 | 1,62 | |
4,08 | 3,23 | 2,84 | 2,61 | 2,45 | 2,34 | 2,18 | 2,00 | 1,79 | 1,52 | |
4,00 | 3,15 | 2,76 | 2,52 | 2,37 | 2,25 | 2,10 | 1,92 | 1,70 | 1,39 | |
3,92 | 3,07 | 2,68 | 2,45 | 2,29 | 2,17 | 2,02 | 1,88 | 1,61 | 1,25 |
Регрессионный и корреляционный анализ – статистические методы исследования. Это наиболее распространенные способы показать зависимость какого-либо параметра от одной или нескольких независимых переменных.
Ниже на конкретных практических примерах рассмотрим эти два очень популярные в среде экономистов анализа. А также приведем пример получения результатов при их объединении.
Регрессионный анализ в Excel
Показывает влияние одних значений (самостоятельных, независимых) на зависимую переменную. К примеру, как зависит количество экономически активного населения от числа предприятий, величины заработной платы и др. параметров. Или: как влияют иностранные инвестиции, цены на энергоресурсы и др. на уровень ВВП.
Результат анализа позволяет выделять приоритеты. И основываясь на главных факторах, прогнозировать, планировать развитие приоритетных направлений, принимать управленческие решения.
Регрессия бывает:
- линейной (у = а + bx);
- параболической (y = a + bx + cx 2);
- экспоненциальной (y = a * exp(bx));
- степенной (y = a*x^b);
- гиперболической (y = b/x + a);
- логарифмической (y = b * 1n(x) + a);
- показательной (y = a * b^x).
Рассмотрим на примере построение регрессионной модели в Excel и интерпретацию результатов. Возьмем линейный тип регрессии.
Задача. На 6 предприятиях была проанализирована среднемесячная заработная плата и количество уволившихся сотрудников. Необходимо определить зависимость числа уволившихся сотрудников от средней зарплаты.
Модель линейной регрессии имеет следующий вид:
У = а 0 + а 1 х 1 +…+а к х к.
Где а – коэффициенты регрессии, х – влияющие переменные, к – число факторов.
В нашем примере в качестве У выступает показатель уволившихся работников. Влияющий фактор – заработная плата (х).
В Excel существуют встроенные функции, с помощью которых можно рассчитать параметры модели линейной регрессии. Но быстрее это сделает надстройка «Пакет анализа».
Активируем мощный аналитический инструмент:
После активации надстройка будет доступна на вкладке «Данные».
Теперь займемся непосредственно регрессионным анализом.
В первую очередь обращаем внимание на R-квадрат и коэффициенты.
R-квадрат – коэффициент детерминации. В нашем примере – 0,755, или 75,5%. Это означает, что расчетные параметры модели на 75,5% объясняют зависимость между изучаемыми параметрами. Чем выше коэффициент детерминации, тем качественнее модель. Хорошо – выше 0,8. Плохо – меньше 0,5 (такой анализ вряд ли можно считать резонным). В нашем примере – «неплохо».
Коэффициент 64,1428 показывает, каким будет Y, если все переменные в рассматриваемой модели будут равны 0. То есть на значение анализируемого параметра влияют и другие факторы, не описанные в модели.
Коэффициент -0,16285 показывает весомость переменной Х на Y. То есть среднемесячная заработная плата в пределах данной модели влияет на количество уволившихся с весом -0,16285 (это небольшая степень влияния). Знак «-» указывает на отрицательное влияние: чем больше зарплата, тем меньше уволившихся. Что справедливо.
Корреляционный анализ в Excel
Корреляционный анализ помогает установить, есть ли между показателями в одной или двух выборках связь. Например, между временем работы станка и стоимостью ремонта, ценой техники и продолжительностью эксплуатации, ростом и весом детей и т.д.
Если связь имеется, то влечет ли увеличение одного параметра повышение (положительная корреляция) либо уменьшение (отрицательная) другого. Корреляционный анализ помогает аналитику определиться, можно ли по величине одного показателя предсказать возможное значение другого.
Коэффициент корреляции обозначается r. Варьируется в пределах от +1 до -1. Классификация корреляционных связей для разных сфер будет отличаться. При значении коэффициента 0 линейной зависимости между выборками не существует.
Рассмотрим, как с помощью средств Excel найти коэффициент корреляции.
Для нахождения парных коэффициентов применяется функция КОРРЕЛ.
Задача: Определить, есть ли взаимосвязь между временем работы токарного станка и стоимостью его обслуживания.
Ставим курсор в любую ячейку и нажимаем кнопку fx.
- В категории «Статистические» выбираем функцию КОРРЕЛ.
- Аргумент «Массив 1» - первый диапазон значений – время работы станка: А2:А14.
- Аргумент «Массив 2» - второй диапазон значений – стоимость ремонта: В2:В14. Жмем ОК.
Чтобы определить тип связи, нужно посмотреть абсолютное число коэффициента (для каждой сферы деятельности есть своя шкала).
Для корреляционного анализа нескольких параметров (более 2) удобнее применять «Анализ данных» (надстройка «Пакет анализа»). В списке нужно выбрать корреляцию и обозначить массив. Все.
Полученные коэффициенты отобразятся в корреляционной матрице. Наподобие такой:
Корреляционно-регрессионный анализ
На практике эти две методики часто применяются вместе.
Пример:
Теперь стали видны и данные регрессионного анализа.
Рассмотрим парную линейную регрессионную модель взаимосвязи двух переменных, для которой функция регрессии φ(х) линейна. Обозначим черезy x условную среднюю признакаY в генеральной совокупности при фиксированном значенииx переменнойХ . Тогда уравнение регрессии будет иметь вид:
y x = ax + b , гдеa –коэффициент регрессии (показатель наклона линии линейной регрессии). Коэффициент регрессии показывает, на сколько единиц в среднем изменяется переменнаяY при изменении переменнойХ на одну единицу. С помощью метода наименьших квадратов получают формулы, по которым можно вычислять параметры линейной регрессии:
Таблица 1. Формулы для расчета параметров линейной регрессии
Свободный член b |
Коэффициент регрессии a |
Коэффициент детерминации |
Проверка гипотезы о значимости уравнения регрессии |
||
Н 0 : |
Н 1 : | |
, ,, Приложение 7 (для линейной регрессии р = 1) |
Направление связи между переменными определяется на основании знака коэффициента регрессии. Если знак при коэффициенте регрессии положительный, связь зависимой переменной с независимой будет положительной. Если знак при коэффициенте регрессии отрицательный, связь зависимой переменной с независимой является отрицательной (обратной).
Для анализа общего качества уравнения регрессии используют коэффициент детерминации R 2 , называемый также квадратом коэффициента множественной корреляции. Коэффициент детерминации (мера определенности) всегда находится в пределах интервала . Если значениеR 2 близко к единице, это означает, что построенная модель объясняет почти всю изменчивость соответствующих переменных. И наоборот, значениеR 2 близкое к нулю, означает плохое качество построенной модели.
Коэффициент детерминации R 2 показывает, на сколько процентовнайденная функция регрессии описывает связь между исходными значениямиY иХ . На рис. 3 показана– объясненная регрессионной моделью вариация и- общая вариация. Соответственно, величинапоказывает, сколько процентов вариации параметраY обусловлены факторами, не включенными в регрессионную модель.
При высоком значении коэффициента детерминации 75%) можно делать прогноздля конкретного значенияв пределах диапазона исходных данных. При прогнозах значений, не входящих в диапазон исходных данных, справедливость полученной модели гарантировать нельзя. Это объясняется тем, что может проявиться влияние новых факторов, которые модель не учитывает.
Оценка значимости уравнения регрессии осуществляется с помощью критерия Фишера (см. табл. 1). При условии справедливости нулевой гипотезы критерий имеет распределение Фишера с числом степеней свободы , (для парной линейной регрессиир = 1 ). Если нулевая гипотеза отклоняется, то уравнение регрессии считается статистически значимым. Если нулевая гипотеза не отклоняется, то признается статистическая незначимость или ненадежность уравнения регрессии.
Пример 1. В механическом цехе анализируется структура себестоимости продукции и доля покупных комплектующих. Было отмечено, что стоимость комплектующих зависит от времени их поставки. В качестве наиболее важного фактора, влияющего на время поставки, выбрано пройденное расстояние. Провести регрессионный анализ данных о поставках:
Расстояние, миль | ||||||||||
Время, мин |
Для проведения регрессионного анализа:
построить график исходных данных, приближенно определить характер зависимости;
выбрать вид функции регрессии и определить численные коэффициенты модели методом наименьших квадратов и направление связи;
оценить силу регрессионной зависимости с помощью коэффициента детерминации;
оценить значимость уравнения регрессии;
сделать прогноз (или вывод о невозможности прогнозирования) по принятой модели для расстояния 2 мили.
2. Вычислим суммы, необходимые для расчета коэффициентов уравнения линейной регрессии и коэффициента детерминации R 2 :
; ;;.
Искомая регрессионная зависимость имеет вид: . Определяем направление связи между переменными: знак коэффициента регрессии положительный, следовательно, связь также является положительной, что подтверждает графическое предположение.
3. Вычислим коэффициент детерминации: или 92%. Таким образом, линейная модель объясняет 92% вариации времени поставки, что означает правильность выбора фактора (расстояния). Не объясняется 8% вариации времени, которые обусловлены остальными факторами, влияющими на время поставки, но не включенными в линейную модель регрессии.
4. Проверим значимость уравнения регрессии:
Т.к. – уравнение регрессии (линейной модели) статистически значимо.
5. Решим задачу прогнозирования. Поскольку коэффициент детерминации R 2 имеет достаточно высокое значение и расстояние 2 мили, для которого надо сделать прогноз, находится в пределах диапазона исходных данных, то можно сделать прогноз:
Регрессионный анализ удобно проводить с помощью возможностей Exel . Режим работы "Регрессия" служит для расчета параметров уравнения линейной регрессии и проверки его адекватности исследуемому процессу. В диалоговом окне следует заполнить следующие параметры:
Пример 2. Выполнить задание примера 1 с помощью режима "Регрессия" Exel .
ВЫВОД ИТОГОВ | |||||
Регрессионная статистика |
|||||
Множественный R | |||||
R-квадрат | |||||
Нормированный R-квадрат | |||||
Стандартная ошибка | |||||
Наблюдения | |||||
Коэффициенты |
Стандартная ошибка |
t-статистика |
P-Значение |
||
Y-пересечение | |||||
Переменная X 1 |
Рассмотрим представленные в таблице результаты регрессионного анализа.
Величина R-квадрат , называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). В нашем примере мера определенности равна 0,91829, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным и совпадает с коэффициентом детерминации R 2 , вычисленным по формуле.
Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y) и равен квадратному корню из коэффициента детерминации. В простом линейном регрессионном анализе множественный коэффициент R равен линейному коэффициенту корреляции (r = 0,958).
Коэффициенты линейной модели: Y -пересечение выводит значение свободного члена b , а переменная Х1 – коэффициента регрессии а. Тогда уравнение линейной регрессии:
у = 2,6597 x + 5,9135 (что хорошо согласуется с результатами расчета в примере 1).
Далее проверим значимость коэффициентов регрессии: a и b . Сравнивая попарно значения столбцов Коэффициенты и Стандартная ошибка в таблице, видим, что абсолютные значения коэффициентов больше, чем их стандартные ошибки. К тому же эти коэффициенты являются значимыми, о чем можно судить по значениям показателя Р-значение, которые меньше заданного уровня значимости α=0,05.
Наблюдение |
Предсказанное Y |
Остатки |
Стандартные остатки | |
В таблице представлены результаты вывода остатков . При помощи этой части отчета мы можем видеть отклонения каждой точки от построенной линии регрессии. Наибольшее абсолютное значение остатка в данном случае - 1,89256, наименьшее - 0,05399. Для лучшей интерпретации этих данных строят график исходных данных и построенной линией регрессии. Как видно из построения, линия регрессии хорошо "подогнана" под значения исходных данных, а отклонения носят случайный характер.