Вычисление статистических показателей с помощью пакета "Excel" (стр. 1 из 2)
Министерство образования и науки Украины
кафедра прикладной математики
КОНТРОЛЬНАЯ РАБОТА
по дисциплине "Эконометрия"
Харьков, 2008 г.
Задание № 1.
По заданным статистическим данным с помощью пакета "Excel":
построить диаграмму рассеивания и подтвердить гипотезу о линейной зависимости
Y = b0 + b1 * X;
определить параметры b0 и b1;
вычислить коэффициенты детерминации R2 и коэффициент корреляции r;
сделать прогноз Y в указанной точке Xр.
Решение:
1. Набираем исходные данные в таблицу 1:
Таблица 1
| X | Y |
| 3.11 | 10.65 |
| 3.15 | 11.87 |
| 3.85 | 12.69 |
| 4.84 | 13.40 |
| 4.62 | 15.12 |
| 4.87 | 16.03 |
| 6.09 | 16.29 |
| 7.06 | 18.07 |
| 6.23 | 18.40 |
| 6.83 | 19.53 |
| 8.01 | 20.48 |
| 8.26 | 21.72 |
| 9.37 | 23.17 |
| 9.02 | 23.57 |
| 9.76 | 24.41 |
2. На основе данных таблицы1 строим диаграмму рассеивания.
Визуально можно предположить, что между данными существует линейная зависимость, то есть их можно аппроксимировать линией.
Y = b0 + b1X
3. Найдем параметры b0 и b1.
Опишем полученный результат:
в первой строке находятся оценки параметров регрессии b1, b0;
во второй строке находятся средние квадратичные отклонения sb1, sb0.
в третьей строке в первой ячейке находится коэффициент детерминации R2, а во второй ячейке оценка среднего квадратичного отклонения показателя sе.
в четвертой строке в первой ячейке находится расчетное значение F - статистики, во второй ячейке находится k - число степеней свободы;
в пятой строке в первой ячейке находится сумма квадратов отклонений расчетных значений показателя от его среднего значения, а во второй ячейке - сумма квадратов остатков.
Полученные результаты заносим в таблицу 2.
Таблица 2.
| Результаты расчетов | |
| 1,958977 | 5,277335 |
| 0,10027 | 0,671183 |
| 0,967063 | 0,836194 |
| 381,6981 | 13 |
| 266,8909 | 9,089857 |
По данным таблицы 2 можем записать модель:
Y = 5,277335 + 1,958977Х
Коэффициент детерминации R2 = 0,967063 - близок к 1, следовательно, модель адекватна.
4. Найдем прогноз в заданной точке Xp = 10,1. Для этого подставим Xp в модель. Получим
Yp = 5,277335 + 1,958977 * 10,1 = 25,063.
Все полученные результаты запишем в таблицу 3.
Таблица 3.
| X | Y |
| 3.11 | 10.65 |
| 3.15 | 11.87 |
| 3.85 | 12.69 |
| 4.84 | 13.40 |
| 4.62 | 15.12 |
| 4.87 | 16.03 |
| 6.09 | 16.29 |
| 7.06 | 18.07 |
| 6.23 | 18.40 |
| 6.83 | 19.53 |
| 8.01 | 20.48 |
| 8.26 | 21.72 |
| 9.37 | 23.17 |
| 9.02 | 23.57 |
| 9.76 | 24.41 |
| 10,1 | 25,063 |
 |
5. Диаграмма примет вид:
6. Вычислим коэффициент корреляции r. В результате расчета получим коэффициент корреляции r = 0,9834.
r =
= √0,967063 = 0.9834Задание № 2.
По заданным статистическим данным с помощью пакета "Excel":
построить диаграмму рассеивания и подтвердить гипотезу о криволинейной связи между Х и Y;
произвести линеаризацию;
определить параметры a и b;
сделать прогноз в указанной точке;
Решение:
Набираем исходные данные в таблицу 1:
Таблица 1.
| X | Y |
| 1,03 | 0,44 |
| 1,63 | 0,33 |
| 2,16 | 0,25 |
| 2,71 | 0, 20 |
| 3,26 | 0,16 |
| 3,77 | 0,12 |
| 4,35 | 0,10 |
| 4,91 | 0,07 |
| 5,50 | 0,05 |
| 6,01 | 0,04 |
На основе данных таблицы 1 строим диаграмму рассеивания.
 |
Визуально можно предположить, что зависимость не линейная. Исходная модель имеет вид Y = beax. Делаем линеаризующую подстановку: V = Y, U = lnX.
Полученные данные заносим в таблицу 2.
Таблица 2.
| X | Y | V | U |
| 1,03 | 0,44 | 0,44 | 0.02956 |
| 1,63 | 0,33 | 0,33 | 0.48858 |
| 2,16 | 0,25 | 0,25 | 0.77011 |
| 2,71 | 0, 20 | 0, 20 | 0.99695 |
| 3,26 | 0,16 | 0,16 | 1.18173 |
| 3,77 | 0,12 | 0,12 | 1.32708 |
| 4,35 | 0,10 | 0,10 | 1.47018 |
| 4,91 | 0,07 | 0,07 | 1.59127 |
| 5,50 | 0,05 | 0,05 | 1.70475 |
| 6,01 | 0,04 | 0,04 | 1.79342 |
Строим корреляционное поле:
 |
Визуально можно предположить, что между данными существует линейная зависимость, то есть их можно аппроксимировать линией
Y = b1X + b0
Диаграмма примет вид:
3. Найдем параметры b0 и b1.
 |
Полученные результаты заносим в таблицу 3.
Таблица 3.
| Результаты расчета | |
| -0,2297 | 0,436791 |
| 0,005542 | 0,006967 |
| 0,995364 | 0,009454 |
| 1717,627 | 8 |
| 0,153525 | 0,000715 |
Параметры модели b0 = 0,436791, b1 = - 0,2297. Коэффициент детерминации R2 = 0,995364 - близок к 1, следовательно, модель адекватна.
Находим параметры исходной нелинейной модели:
а = еb1 = e-0,2297 = 0,79477
b = eb0 = e0,436791 = 1,54773
Исходная нелинейная модель примет вид: Y = 1,54773e0,79477X
5. Вычислим прогнозируемое Yp в то Xp = 6,5:
Yp = 1,54773e 0,79477*6,5 = 271,18
Задание № 3
По заданным статистическим данным с помощью пакета "Excel":
построить корреляционную матрицу;
по корреляционной матрице проверить факторы X1, X2, X3 на мультиколинеарность, и, если она есть, устранить ее, исключив один из факторов;
проверить гипотезу о наличии линейной связи между показателем Y и оставшимися факторами;
определить параметры линейной связи;
вычислить коэффициент детерминации;
сделать прогноз в указанной точке.
Решение:
Набираем исходные данные в таблицу 1:
Таблица 1.
| X1 | X2 | X3 | Y |
| 2,61 | 10,35 | 6,61 | 7,72 |
| 4,89 | 11,78 | 7,94 | 10,77 |
| 6,24 | 14,09 | 8,62 | 11,86 |
| 9,01 | 14,64 | 8,83 | 13,73 |
| 10,79 | 15,17 | 10,68 | 17,04 |
| 13,53 | 17,42 | 10,66 | 18,8 |
| 16,32 | 19,24 | 11,78 | 21,28 |
| 18,6 | 20,6 | 13,78 | 23,7 |
| 21,48 | 22,04 | 13,74 | 27,63 |
| 23,02 | 22,69 | 14,56 | 27,45 |
| 25,17 | 22,65 | 14,09 | 29,71 |
| 26,4 | 24,83 | 16,66 | 32,8 |
| 27,62 | 24,82 | 15,12 | 31,81 |
| 30, 19 | 25,17 | 15,42 | 25,22 |
| 32,25 | 26,22 | 15,77 | 37,26 |
| 33,76 | 27,72 | 17,4 | 39,2 |
| 35,97 | 29,15 | 17,77 |
2. По исходным данным строим корреляционную матрицу (таблица 2):
Таблица 2.
| X1 | X2 | X3 | Y | |
| X1 | 1 | 0,9921671 | 0,9741853 | 0,9656738 |
| X2 | 0,9921671 | 1 | 0,9864174 | 0,9700431 |
| X3 | 0,9741853 | 0,9864174 | 1 | 0,96548 |
| Y | 0,9656738 | 0,9700431 | 0,96548 | 1 |
Визуально можно предположить, что между данными X2 и X3 и X1 и X3 есть зависимость, значит, фактор X3 исключаем из модели, так как между ним и Y связь меньше, чем между Y и X2 (0,96548 < 0,9700431). Модель будет иметь вид:
Y = b0 + b1X1 + b2X2;
3. Строим график зависимости между X1, X2 и Y: визуально можно предположить, что зависимость между X1, X2 и Y линейная, коэффициент детерминации R2 = 0,9416518 - близок к 1, следовательно, модель адекватна.
4. Найдем параметры b0, b1 и b2. Полученные результаты заносим в таблицу 3:
Таблица 3.
| Результаты расчета | ||
| 1,344552 | 0, 1954415 | -7,0318824 |
| 0,9429349 | 0,5065553 | 9,4389862 |
| 0,9416518 | 2,4854573 | --- |
| 104,90023 | 13 | --- |
| 1296,0419 | 80,307473 | --- |
5. По данным таблицы можем записать модель:
Y = - 7,0318824 + 0, 1954415X1 + 1,344552X2;
Коэффициент детерминации R2 = 0,9416518 - близок к 1, следовательно, модель адекватна.
6. Найдем прогноз в заданной точке. Для этого достаточно подставить Xp в модель.
Yp = - 7,0318824 + 0, 1954415 * 35,97 + 1,344552 * 29,15 = 39, 19
Задание №4.
Предположим, что между показателем Y - объем выпущенной продукции и факторами X1 - трудовые затраты, X2 - объем основных фондов, существует зависимость типа