Задачи по Статистике 4 (стр. 3 из 3)
10) Обоснуйте с помощью коэффициента детерминации R2, какое уравнение более точно описывает зависимость между изучаемыми показателями.
Решение:
1. Для расчета линейного коэффициента корреляции воспользуемся стандартной функцией ExcelКОРРЕЛ().
r=0,87208
Для расчета коэффициента ранговой корреляции Спирмена составим вспомогательную расчетную таблицу:
| X | Y | Rx | Ry | d=Rx-Ry | d2 |
| 157 | 107 | 2 | 1 | 1 | 1 |
| 273 | 182 | 8 | 7 | 1 | 1 |
| 150 | 151 | 1 | 4 | -3 | 9 |
| 301 | 307 | 11,5 | 14 | -2,5 | 6,25 |
| 539 | 321 | 22 | 15 | 7 | 49 |
| 520 | 347 | 21 | 18 | 3 | 9 |
| 584 | 396 | 24 | 23 | 1 | 1 |
| 600 | 390 | 25 | 22 | 3 | 9 |
| 332 | 354 | 14 | 19 | -5 | 25 |
| 262 | 211 | 7 | 9 | -2 | 4 |
| 331 | 330 | 13 | 17 | -4 | 16 |
| 397 | 328 | 15 | 16 | -1 | 1 |
| 424 | 390 | 17 | 22 | -5 | 25 |
| 168 | 169 | 3 | 6 | -3 | 9 |
| 413 | 261 | 16 | 12 | 4 | 16 |
| 448 | 364 | 20 | 20 | 0 | 0 |
| 430 | 403 | 18 | 24 | -6 | 36 |
| 572 | 406 | 23 | 25 | -2 | 4 |
| 285 | 152 | 10 | 5 | 5 | 25 |
| 246 | 227 | 6 | 10 | -4 | 16 |
| 445 | 298 | 19 | 13 | 6 | 36 |
| 183 | 131 | 5 | 2 | 3 | 9 |
| 301 | 237 | 11,5 | 11 | 0,5 | 0,25 |
| 283 | 188 | 9 | 8 | 1 | 1 |
| 170 | 141 | 4 | 3 | 1 | 1 |
| Итого: | 309,5 |
Тогда коэффициент ранговой корреляции будет равен:
2. Рассчитаем доверительный интервал для коэффициента корреляции при доверительной вероятности 0,90.
Доверительный интервал определяем как:
,где t – квантиль распределения Стьюдента.
0,97694=
, Þrmin=0,751741,7065=
, Þrmax=0,93621Таким образом, интервальная оценка для истинного значения коэффициента корреляции будет:
0,75174£r£0,93621
3. Проверим значимость коэффициента корреляции. Находим фактическое значение t-статистики Стьюдента:
Критическое значение tкр(0,9;23)=1,7109
Т.к.
, то гипотеза о равенстве нулю коэффициентов корреляции отвергается. Из этого следует, что связь между показателями значима и является тесной.4. Найдем оценки коэффициентов уравнения регрессии. Для этого воспользуемся функцией Excel ЛИНЕЙН(). Получим следующие значения коэффициентов:
a0=55,328, a1=0,61355.
Таким образом, уравнение регрессии будет иметь вид:
5. Построим диаграмму рассеяния и отобразим на ней уравнение регрессии:
6. Выполним точечный прогноз себестоимости, если планируемый объем товарной продукции в следующем году 500.
млн. руб.Тогда прибыль составит ПР=500-362,1=137,9 млн. руб., а рентабельность
или 38,083%.7. Найдем доверительный интервал для себестоимости, используя формулу:
, где 
Значение ошибки регрессии находим с помощью функции СТОШYX():
s=49,583.
Сумму квадратов отклонений считаем с помощью функции КВАДРОТКЛ():
=477032,16Тогда
14,504.Находим доверительный интервал:
337,29
386,928. Представим зависимость между себестоимостью и стоимостью товарной продукции с помощью:
а) уравнения параболы:
б) степенной функции
в) логарифмической функции
г) экспоненциальной функции
д) уравнения гиперболы
Для этого сделаем замену
. Тогда уравнение примет вид: 
. Определим параметры уравнения регрессии с помощью надстройки Excel Сервис/Анализ данных/Регрессия.Получим следующий отчет работы надстройки:
| ВЫВОД ИТОГОВ | ||||||||
| Регрессионная статистика | ||||||||
| Множественный R | 0,865643 | |||||||
| R-квадрат | 0,749338 | |||||||
| Нормированный R-квадрат | 0,73844 | |||||||
| Стандартная ошибка | 50,72761 | |||||||
| Наблюдения | 25 | |||||||
| Дисперсионный анализ | ||||||||
| df | SS | MS | F | Значимость F | ||||
| Регрессия | 1 | 176932,1 | 176932,1 | 68,75715 | 2,31E-08 | |||
| Остаток | 23 | 59185,67 | 2573,29 | |||||
| Итого | 24 | 236117,8 | ||||||
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | Нижние 95% | Верхние 95% | Нижние 95,0% | Верхние 95,0% | |
| Y-пересечение | 459,7745 | 24,85374 | 18,49921 | 2,63E-15 | 408,3606 | 511,1883 | 408,3606 | 511,1883 |
| Переменная X 1 | -55589,1 | 6703,949 | -8,29199 | 2,31E-08 | -69457,3 | -41720,9 | -69457,3 | -41720,9 |
Получим следующее уравнение:
R2=0,74934
9) Коэффициенты детерминации R2 для каждого варианта аппроксимации рассчитаны на графиках. Для этого нужно при добавлении тренда в параметрах указать «поместить на диаграмму величину достоверности аппроксимации».
Для гиперболической зависимости его величину можно взять из итоговой таблицы работы надстройки.
10) Обоснуйте с помощью коэффициента детерминации R2, какое уравнение более точно описывает зависимость между изучаемыми показателями.
Поскольку максимальное значение коэффициента детерминации, равное 0,8012, имеет степенной тренд, то это уравнение более точно описывает зависимость между изучаемыми показателями.
Список литературы:
1. Практикум по теории статистики: Учеб. пособие для экон. спец. вузов [Р.А. Шмойлова, А.Б. Гусынин, В.Г. Минашкин, Н.А. Садовникова]; Под ред. Р.А. Шмойловой. – М.: Финансы и статистика, 2004.
2. Теория статистики / Под ред. Шмойловой Р.А. - М., 2003.
3. Статистика: Учеб. пособие / [Л.П. Харченко, В.Г. Долженкова, В.Г. Ионин и др.]; НГАЭиУ. - 2-е изд., перераб. и доп. - М.: ИНФРА-М, 2006.