Инструмент анализа данных «Регрессия»
Для проверки значимости функции используется расчет статистики Фишера (F-статистики). В Excel расчет F-статистики проводится в полном анализе уравнения линейной регрессии проводится с помощью инструмента анализа «Регрессия» (закладка «Данные», «Анализа данных»). Инструменты анализа данных представлены на Рис. 4. Выбираем инструмент «Регрессия».
Рис. 4 Выбор инструмента анализа «Регрессия»
На Рис. 5 показано заполнение полей инструмента «Регрессия». Ставим галочку в поле «Метки» и берем данные вместе с заголовками столбцов. Если не ставить галочку в поле «Метки», нужно брать только данные без заголовков столбцов. Заполняем входные интервалы Y и X.
Рис. 5 Заполнение данных в инструменте «Регрессия»
На Рис. 6 показан вывод итогов инструмента регрессия. Вывод итогов состоит из трех таблиц. Дадим интерпретацию информации в таблице «Регрессионная статистика».
Рис. 6 Вывод итогов инструмента «Регрессия»
Множественный R – коэффициент корреляции – равен 0,968. Коэффициент корреляции близок к 1, следовательно, между х и у существует тесная линейная зависимость. Для грубой оценки тесноты связи между переменными можно использовать следующую информацию: если │r│≤0,4 – связь отсутствует, 0,4<│r│≤0,6 – связь слабая, 0,6<│r│≤0,8 – связь средняя, │r│>0,8 – связь сильная.
R-квадрат – коэффициент детерминации, он характеризует долю дисперсии результативного признака, объясняемого регрессией в общей дисперсии результативного признака. Данный коэффициент используется для оценки качества соответствия модели данным и учитывается при выборе наилучшей модели из нескольких.
Следующая таблица «Дисперсионный анализ» служит для расчета F-статистики и проверки значимости уравнения регрессии.
Дополним точечный прогноз расчетом стандартной ошибки 
и затем построим интервальную оценку прогнозного значения ( 
)
(3)
Стандартная ошибка имеет выражение
, где 
. (4)
Для данного примера имеет выражение
(5)
При 
=50.714, стандартная ошибка 
,
При 
60, стандартная ошибка 
0,6712, для остальных значений х результаты приведены на Рис. 5
Рис. 7 Стандартная ошибка прогнозного значения y
Как видно из формулы, стандартная ошибка возрастает при увеличении отклонения х от среднего значения. Требуется построить 90%-ный доверительный интервал. По таблице критических значений t-критерия Стьюдента найдем критическое значение для уровня значимости 0,1 (100%-90%=10%, переведем в доли единицы) и числа степеней свободы 12=14-2. Критическое значение tкр(0.1; 12)=1.7823.
Рис. 8 Расчет доверительного интервала для прогнозных значений
На Рис. 7 представлены графики линии парной регрессии, и нижней и верхней границ доверительного интервала. Значения доверительного интервала рассчитаны для исходных значений х. На графике доверительные границы для y^ представляют собой гиперболы, расположенные по обе стороны от линии регрессии.
Рис. 9 График доверительного интервала
1. Найти оценки дисперсий оценок коэффициентов регрессии
Рассчитаем соответствующие стандартные ошибки (среднеквадратичные отклонения)
Для коэффициента b стандартная ошибка рассчитывается по формуле 
. И она равна 
0.0402
Для коэффициента a стандартная ошибка рассчитывается по формуле 
. И она равна 
2.1143
2. Найти доверительные интервалы для коэффициентов регрессии с доверительной вероятностью 0.9
Для построения доверительных интервалов коэффициентов регрессии, воспользуемся рассчитанными ранее соответствующими стандартными ошибками. Соответствующее критическое значение t(0.1;14-2)=1.7823 
Доверительный интервал для коэффициента b определяется по формуле 
0.5435-1.7823*0.0402≤b≤0.5435+1.7823*0.0402
0.4718≤b≤0.6152
Доверительный интервал для коэффициента а определяется по формуле 
6.5796-1.7823*2.1143≤a≤6.5796 +1.7823*2.1143
2.8111≤a≤10.3480
3. Проверить гипотезы о равенстве отдельных коэффициентов нулю (при альтернативе не равно), т.е. рассчитать уровни значимости
Для оценки существенности коэффициента регрессии его величина сравнивается с его стандартной ошибкой, т.е. рассчитывается фактическое значение t-критерия Стьюдента 
, которое затем сравнивается с табличным значением при определенном уровне значимости α и числе степеней свободы (n-2).
В нашем случае 
Проверим значимость коэффициента регрессии b нулю.
Формулируем гипотезу 
(b=0), при альтернативной гипотезе 
.
При уровне значимости α=0,05 и количестве степеней свободы 14-2=12 табличное значение 
2.1788. Так как фактическое значение превышает табличное, гипотезу о равенстве нулю коэффициента регрессии 
отвергаем, гипотезу 
принимаем. Вывод – коэффициент регрессии b существенно отличается от нуля.
4. Найти коэффициент детерминации и на уровне значимости 0.05 проверить значимость линейной функции регрессии
Уравнение регрессии всегда дополняется показателем тесноты связи. При использовании линейной регрессии в качестве такого показателя выступает линейный коэффициент корреляции, который рассчитывается по формуле 
. Линейный коэффициент корреляции изменяется в пределах от -1 до 1. В нашем случае 
, так как коэффициент корреляции близок к 1, можно сделать вывод, что связь между х и у тесная.
Проверим значимость линейного коэффициента корреляции. Найдем по таблице критические значения корреляции для уровня значимости 0,05 и количестве степеней свободы 14-2=12. 
=0,5324. Так как фактическое значение превышает табличное, делаем вывод, что коэффициент корреляции существенно отличается от нуля, другими словами он значим.
Рассчитаем коэффициент детерминации, который равен квадрату коэффициента корреляции. Коэффициент детерминации характеризует долю дисперсии результативного признака у, объясняемую регрессией, в общей дисперсии результативного признака.
5. Найти точечное и интервальное (с надежностью 0.9) предсказания зависимой переменной при значении объясняющей, равной максимальному наблюденному ее значению, увеличенному на 10%.
Найдем точечное предсказание зависимой переменной при значении объясняющей, равной максимальному наблюденному значению, увеличенному на 10%.
Найдем точечный прогноз y(82.5)=6.579+0.543*82.5=51,4184.
Для построения доверительного интервала прогноза, рассчитаем стандартную ошибку 
1,3946. Будем строить доверительный интервал с доверительной вероятностью 0.9. Критическое значение, найденное по таблице t(0.1;14-2)=1.7823
Доверительный интервал прогноза определяется по формуле 
.
51,4184-1.7823*1,3946≤y≤51,4184+1.7823*1,3946
48,9327≤y≤53,9041
6. Найти средний коэффициент эластичности зависимой переменной по независимой
Средний коэффициент эластичности 
показывает, на сколько процентов в среднем по совокупности изменится результат у от своей средней величины при изменении фактора х на 1% своего среднего значения:
=0,5435*50,71/34,14=0,8073.
Получается, что при изменении фактора х на 1% своего среднего значения, результат у от своей средней величины изменится на 0,8073% в среднем по совокупности.
7. По критерию Дарбина-Уотсона проверить гипотезу об автокоррелированности остатков
Упорядочим x по возрастанию. Рассчитаем остатки 
. Результаты приведены в таблице
Рис. 10 Таблица остатков
Рассчитаем величину d для использования критерия Дарбина-Уотсона.
. Обычно величину d рассчитывают через коэффициент автокорреляции первого порядка:
=2*(1-(-0,1825))=2,3651
Алгоритм выявления автокорреляции остатков на основе критерия Дарбина-Уотсона следующий. Выдвигается гипотеза H0 об отсутствии автокорреляции остатков. Альтернативные гипотезы H1 и H1* состоят, соответственно, в наличии положительной или отрицательной автокорреляции остатков. Далее по специальной таблице определяются критические значения критерия Дарбина-Уотсона dLи dU для заданного числа наблюдений n, числа независимых переменных модели k и уровня значимости α. По этим значениям числовой промежуток [0; 4] разбивают на пять отрезков.
[0; dL] Есть положительная автокорреляция остатков. Гипотеза H0 отклоняется, с вероятностью (1-α) принимается гипотеза H1.
[dL; dU] Зона неопределенности.
[dU; 4-dU] Нет оснований отклонять H0 (автокорреляция остатков отсутствует).
[4-dU; 4-dL] Зона неопределенности.
[4-dL; 4] Есть отрицательная автокорреляция остатков. Гипотеза H0 отклоняется, с вероятностью (1-α) принимается H1*.
Находим по таблице для заданного числа наблюдений n=14, числа независимых переменных модели k=1 и уровня значимости α=0,05 dL =1,05 и dU =1,35
В нашем случае интервалы будут иметь вид.
[0; 1,05] Есть положительная автокорреляция остатков. Гипотеза H0 отклоняется, с вероятностью (1-α) принимается гипотеза H1.
[1,05; 1,35] Зона неопределенности.
[1,35; 2,65] Нет оснований отклонять H0 (автокорреляция остатков отсутствует).
[2,65; 2,95] Зона неопределенности.
[2,95; 4] Есть отрицательная автокорреляция остатков. H0 отклоняется, с вероятностью (1-α) принимается H1*.
Наше фактическое значение d=2,3651 попадает в третий интервал, в котором нет оснований отклонять H0 (автокорреляция остатков отсутствует).
Варианты
Вариант 1
Вариант 1
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Средний размер назначенных месячных пенсий, руб. |
| y | x | ||
| Республика Башкортостан | 6819.7 | 2353.6 | |
| Республика Марий Эл | 3349.0 | 2270.8 | |
| Республика Мордовия | 4111.0 | 2304.0 | |
| Республика Татарстан | 7251.0 | 2412.5 | |
| Удмуртская Республика | 4618.2 | 2472.5 | |
| Чувашская Республика | 3905.2 | 2266.9 | |
| Пермский край | 8134.2 | 2490.0 | |
| Нижегородская область | 6062.0 | 2506.3 | |
| Оренбургская область | 4984.7 | 2340.8 | |
| Пензенская область | 4311.8 | 2375.5 | |
| Самарская область | 9273.9 | 2481.3 | |
| Саратовская область | 4948.2 | 2384.9 | |
| Ульяновская область | 4514.7 | 2367.4 |
Вариант 2
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Среднемесячная номинальная начисленная заработная плата, руб. |
| y | x | ||
| Белгородская область | 5247.9 | 6775.4 | |
| Брянская область | 4768.3 | 5235.3 | |
| Владимирская область | 4131.9 | 6066.8 | |
| Ивановская область | 3468.0 | 5143.7 | |
| Калужская область | 5357.9 | 7066.4 | |
| Орловская область | 4794.8 | 5430.6 | |
| Рязанская область | 4741.8 | 6149.7 | |
| Смоленская область | 5555.3 | 6190.6 | |
| Тверская область | 5610.4 | 6486.3 | |
| Тульская область | 5002.6 | 6412.4 | |
| Ярославская область | 6194.3 | 7366.2 |
Вариант 3
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Среднемесячная номинальная начисленная заработная плата, руб. |
| y | x | ||
| Республика Башкортостан | 6819.7 | 6612.0 | |
| Республика Марий Эл | 3349.0 | 4938.2 | |
| Республика Мордовия | 4111.0 | 5060.7 | |
| Республика Татарстан | 7251.0 | 7067.8 | |
| Удмуртская Республика | 4618.2 | 6373.3 | |
| Чувашская Республика | 3905.2 | 5073.1 | |
| Пермский край | 8134.2 | 7748.9 | |
| Кировская область | 4543.4 | 5695.8 | |
| Нижегородская область | 6062.0 | 6533.4 | |
| Оренбургская область | 4984.7 | 6163.5 | |
| Пензенская область | 4311.8 | 5206.8 | |
| Самарская область | 9273.9 | 7764.9 | |
| Саратовская область | 4948.2 | 5439.3 | |
| Ульяновская область | 4514.7 | 5343.8 |
Вариант 4
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Прожиточный минимум, р. |
| y | x | ||
| Республика Башкортостан | 6819.7 | 2338.0 | |
| Республика Марий Эл | 3349.0 | 2377.0 | |
| Республика Мордовия | 4111.0 | 2518.0 | |
| Республика Татарстан | 7251.0 | 2488.0 | |
| Удмуртская Республика | 4618.2 | 2483.0 | |
| Чувашская Республика | 3905.2 | 2294.0 | |
| Пермский край | 8134.2 | 2880.0 | |
| Кировская область | 4543.4 | 2673.0 | |
| Нижегородская область | 6062.0 | 2782.0 | |
| Оренбургская область | 4984.7 | 2473.0 | |
| Пензенская область | 4311.8 | 2465.0 | |
| Самарская область | 9273.9 | 3085.0 | |
| Саратовская область | 4948.2 | 2650.0 | |
| Ульяновская область | 4514.7 | 2503.0 |
Вариант 5
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Прожиточный минимум, р. |
| y | x | ||
| Белгородская область | 5247.9 | 2397.0 | |
| Брянская область | 4768.3 | 2247.0 | |
| Воронежская область | 5456.8 | 2499.0 | |
| Калужская область | 5357.9 | 2651.0 | |
| Орловская область | 4794.8 | 2367.0 | |
| Рязанская область | 4741.8 | 2518.0 | |
| Смоленская область | 5555.3 | 2554.0 | |
| Тверская область | 5610.4 | 2787.0 | |
| Тульская область | 5002.6 | 2512.0 | |
| Ярославская область | 6194.3 | 2620.0 |
Вариант 6
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Основные фонды по субъектам РФ, млн. руб. |
| y | x | ||
| Республика Башкортостан | 6819.7 | 868425.0 | |
| Республика Марий Эл | 3349.0 | 133723.0 | |
| Республика Мордовия | 4111.0 | 183836.0 | |
| Республика Татарстан | 7251.0 | 1090879.0 | |
| Удмуртская Республика | 4618.2 | 367407.0 | |
| Чувашская Республика | 3905.2 | 252802.0 | |
| Пермский край | 8134.2 | 961938.0 | |
| Кировская область | 4543.4 | 322973.0 | |
| Нижегородская область | 6062.0 | 682846.0 | |
| Оренбургская область | 4984.7 | 480318.0 | |
| Пензенская область | 4311.8 | 280178.0 | |
| Самарская область | 9273.9 | 1056261.0 | |
| Саратовская область | 4948.2 | 556180.0 | |
| Ульяновская область | 4514.7 | 234805.0 |
Вариант 7
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Основные фонды по субъектам РФ, млн. руб. |
| y | x | ||
| Белгородская область | 5247.9 | 295572.0 | |
| Брянская область | 4768.3 | 218523.0 | |
| Владимирская область | 4131.9 | 218683.0 | |
| Воронежская область | 5456.8 | 428766.0 | |
| Ивановская область | 3468.0 | 146922.0 | |
| Калужская область | 5357.9 | 202305.0 | |
| Орловская область | 4794.8 | 135761.0 | |
| Рязанская область | 4741.8 | 292579.0 | |
| Смоленская область | 5555.3 | 276228.0 | |
| Тамбовская область | 5141.6 | 243824.0 | |
| Тверская область | 5610.4 | 368001.0 | |
| Ярославская область | 6194.3 | 480740.0 |
Вариант 8
Дана зависимость y от x
| i | Регион | Валовой региональный продукт, млн. р. | Основные фонды по субъектам РФ, млн. руб. |
| y | x | ||
| Республика Башкортостан | 381431.0 | 868425.0 | |
| Республика Марий Эл | 34149.5 | 133723.0 | |
| Республика Мордовия | 46000.3 | 183836.0 | |
| Республика Татарстан | 488609.1 | 1090879.0 | |
| Удмуртская Республика | 140539.0 | 367407.0 | |
| Чувашская Республика | 69498.3 | 252802.0 | |
| Пермский край | 338915.7 | 961938.0 | |
| Кировская область | 80046.6 | 322973.0 | |
| Нижегородская область | 296929.7 | 682846.0 | |
| Оренбургская область | 214252.6 | 480318.0 | |
| Пензенскаяобласть | 73504.7 | 280178.0 | |
| Самарская область | 402308.6 | 1056261.0 | |
| Саратовская область | 169148.5 | 556180.0 | |
| Ульяновская область | 82534.1 | 234805.0 |
Вариант 9
Дана зависимость y от x
| i | Регион | Валовой региональный продукт, млн. р. | Основные фонды по субъектам РФ, млн. руб. |
| y | x | ||
| Белгородская область | 147184.8 | 295572.0 | |
| Брянская область | 66825.1 | 218523.0 | |
| Владимирская область | 87840.6 | 218683.0 | |
| Воронежская область | 136152.7 | 428766.0 | |
| Ивановская область | 45981.7 | 146922.0 | |
| Калужская область | 74506.7 | 202305.0 | |
| Рязанская область | 84849.8 | 292579.0 | |
| Смоленская область | 68383.1 | 276228.0 | |
| Тамбовская область | 64538.7 | 243824.0 | |
| Тверская область | 94860.0 | 368001.0 |
Вариант 10
Дана зависимость y от x
| i | Регион | Среднедушевые денежные доходы (в месяц), руб. | Валовой региональный продукт, млн. р. |
| y | x | ||
| Республика Башкортостан | 6819.7 | 381431.0 | |
| Республика Марий Эл | 3349.0 | 34149.5 | |
| Республика Мордовия | 4111.0 | 46000.3 | |
| Республика Татарстан | 7251.0 | 488609.1 | |
| Удмуртская Республика | 4618.2 | 140539.0 | |
| Чувашская Республика | 3905.2 | 69498.3 | |
| Пермский край | 8134.2 | 338915.7 | |
| Кировская область | 4543.4 | 80046.6 | |
| Нижегородская область | 6062.0 | 296929.7 | |
| Оренбургская область | 4984.7 | 214252.6 | |
| Пензенскаяобласть | 4311.8 | 73504.7 | |
| Самарская область | 9273.9 | 402308.6 | |
| Саратовская область | 4948.2 | 169148.5 | |
| Ульяновская область | 4514.7 | 82534.1 |