Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel
Режим работы "Регрессия" служит для расчета параметров уравнения линейной регрессии и проверки его адекватности исследуемому процессу.
Для решения задачи регрессионного анализа в MS Excel выбираем в меню Сервис командуАнализ данных и инструмент анализа "Регрессия".
В появившемся диалоговом окне задаем следующие параметры:
1. Входной интервал Y - это диапазон данных по результативному признаку. Он должен состоять из одного столбца.
2. Входной интервал X - это диапазон ячеек, содержащих значения факторов (независимых переменных). Число входных диапазонов (столбцов) должно быть не больше 16.
3. Флажок Метки, устанавливается втом случае, если в первой строке диапазона стоит заголовок.
4. Флажок Уровень надежности активизируется, если в поле, находящееся рядом с ним необходимо ввести уровень надежности, отличный от установленного по умолчанию. Используется для проверки значимости коэффициента детерминации R2 и коэффициентов регрессии.
5. Константа ноль. Данный флажок необходимо установить, если линия регрессии должна пройти через начало координат (а0=0).
6. Выходной интервал/ Новый рабочий лист/ Новая рабочая книга – указать адрес верхней левой ячейки выходного диапазона.
7. Флажкив группе Остатки устанавливаются, если необходимо включить в выходной диапазон соответствующие столбцы или графики.
8. Флажок График нормальной вероятности необходимо сделать активным, если требуется вывести на лист точечный график зависимости наблюдаемых значений Y от автоматически формируемых интервалов персентилей.
После нажатия кнопки ОК в выходном диапазоне получаем отчет.
Пример выполнения лабораторной работы
Задача: Некоторая фирма занимается поставками различных грузов на короткие расстояния внутри города. Оценить стоимость таких услуг, зависящую от затрачиваемого на поставку времени. В качестве наиболее важного фактора, влияющего на время поставки, выбрано пройденное расстояние. Были собраны исходные данные о десяти поставках (таблица 1)
Таблица 1
| Расстояние, миль | 3,5 | 2,4 | 4,9 | 4,2 | 3,0 | 1,3 | 1,0 | 3,0 | 1,5 | 4,1 |
| Время, мин |
Определите характер зависимости между расстоянием и затраченным временем, используя мастер диаграмм MS Еxcel, проанализируйте применимость метода наименьших квадратов, постройте уравнение регрессии, используя МНК, проанализируйте силу регрессионной связи. Проведите регрессионный анализ, используя режим работы "Регрессия" в MS Еxcel и сравните с результатами, полученными ранее. Сделайте прогноз времени поездки на 2 мили. Посчитать и построить графически меру ошибки регрессионной модели используя табличный процессор Excel.
Решение
На графике строим исходные данные по десяти поездкам.
Рис.4. График исходных данных и предполагаемая линия регрессии
Помимо расстояния на время поставки влияют пробки на дорогах, время суток, дорожные работы, погода, квалификация водителя, вид транспорта. Построенные точки не находятся точно на линии, что обусловлено описанными выше факторами. Но эти точки собраны вокруг прямой линии, поэтому можно предположить линейную связь между параметрами. Все исходные точки равномерно распределены вдоль предполагаемой прямой линии, что позволяет применить метод наименьших квадратов.
Вычислим суммы, необходимые для расчета коэффициентов уравнения линейной регрессии и коэффициента детерминации R2 с помощью вспомогательной таблицы (таблица 2).
Таблица 2
| 
| 
| 
| 
| 
| 
|
| 3,5 | 12,25 | 56,00 | 15,223 | 2,634129 | 5,76 | |
| 2,4 | 5,76 | 31,2 | 12,297 | 1,697809 | 0,36 | |
| 4,9 | 24,01 | 93,1 | 18,947 | 28,59041 | 29,16 | |
| 4,2 | 17,64 | 75,60 | 17,085 | 12,14523 | 19,36 | |
| 3,0 | 9,00 | 36,00 | 13,893 | 0,085849 | 2,56 | |
| 1,3 | 1,69 | 14,30 | 9,371 | 17,88444 | 6,76 | |
| 1,0 | 1,00 | 8,00 | 8,573 | 25,27073 | 31,36 | |
| 3,0 | 9,00 | 42,00 | 13,893 | 0,085849 | 0,16 | |
| 1,5 | 2,25 | 13,50 | 9,903 | 13,66781 | 21,16 | |
| 4,1 | 16,81 | 65,60 | 16,819 | 10,36196 | 5,76 | |
 28,9
| 136
| 99,41
| 435,30
| – | 112,4242 | 122,4 |
.
Вычислим коэффициенты линейной регрессии по формулам (1) и (2):
Таким образом, искомая регрессионная зависимость имеет вид:
|
Наклон линии регрессии 
2,66 минут на милю – это количество минут, приходящееся на одну милю расстояния. Координата точки пересечения прямой с осью Y 
5,913 минут – это время, которое не зависит от пройденного расстояния, а обуславливается всеми остальными возможными факторами, явно не учтенными при анализе.
Вычислим коэффициент детерминации:
или 91,8%.
Проведем регрессионный анализ с использованием режима Регрессия MS Excel. Значения параметров, установленных в одноименном диалоговом окне, представлены на рис.6.
Рис. 6.
Сгенерируются результаты по регрессионной статистике, представленные в таблице 3.
Таблица 3.
| ВЫВОД ИТОГОВ | |
| Регрессионная статистика | |
| Множественный R | 0,958275757 |
| R-квадрат | 0,918292427 |
| Нормированный R-квадрат | 0,90807898 |
| Стандартная ошибка | 1,11809028 |
| Наблюдения |
Рассмотрим представленную в таблице 3 регрессионную статистику.
Величина R-квадрат, называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала [0;1]. В нашем примере мера определенности равна 0,91829, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным и совпадает с коэффициентом детерминации R2, вычисленным по формуле.
Таким образом, линейная модель объясняет 91,8% вариации времени доставки, что означает правильность выбора фактора (расстояния). Не объясняется 
вариации времени поездки, которые обусловлены остальными факторами, влияющими на время поставки, но не включенными в линейную модель регрессии.
Рассчитанный уровень значимости αр=1,26E-05<0,05(показатель значимость F в таблице Дисперсионный анализ) подтверждает значимость R2.
Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y) и равен квадратному корню из коэффициента детерминации, эта величина принимает значения в интервале от нуля до единицы. В простом линейном регрессионном анализе множественный R равен коэффициенту корреляции Пирсона. Действительно, множественный R в нашем случае равен коэффициенту корреляции Пирсона (0,95827), который вычисляется по формуле:
Теперь рассмотрим среднюю часть расчетов, представленную в таблице 4 (приведена в сокращенном варианте). Здесь даны коэффициент регрессии а1 (2,65970168) и смещение по оси ординат, т.е. константа a0 (5,913462144).
Таблица 4
| Коэффициенты | Стандартная ошибка | t-статистика | P-Значение | |
| Y-пересечение | 5,913462144 | 0,884389599 | 6,686489927 | 0,00015485 |
| Переменная X 1 | 2,65970168 | 0,280497238 | 9,482095791 | 1,26072E-05 |
Исходя из расчетов, можем записать уравнение регрессии таким образом:
(*) Видим, что это уравнение, совпадает с уравнением, полученным нами при расчете по МНК вручную с точностью до ошибки округления.
Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициента регрессии (коэффициента а1). В нашем случае знак коэффициента регрессии положительный, следовательно, связь также является положительной.
Далее проверим значимость коэффициентов регрессии: а0 и а1.Сравнивая попарно значения столбцов Коэффициенты и Стандартная ошибка в таблице 4, видим, что абсолютные значения коэффициентов больше чем их стандартные ошибки. К тому же эти коэффициенты являются значимыми, о чем можно судить по значениям показателя Р-значение в таблице 4, которые меньше заданного уровня значимости α=0,05.
Таблица 5
| ВЫВОД ОСТАТКА | |||
| Наблюдение | Предсказанное Y | Остатки | Стандартные остатки |
| 15,22241803 | 0,777581975 | 0,737641894 | |
| 12,29674618 | 0,703253823 | 0,667131568 | |
| 18,94600038 | 0,053999622 | 0,051225961 | |
| 17,0842092 | 0,915790799 | 0,868751695 | |
| 13,89256718 | -1,892567185 | -1,795356486 | |
| 9,371074328 | 1,628925672 | 1,545256778 | |
| 8,573163824 | -0,573163824 | -0,543723571 | |
| 13,89256718 | 0,107432815 | 0,101914586 | |
| 9,903014664 | -0,903014664 | -0,8566318 | |
| 16,81823903 | -0,818239033 | -0,776210624 |
В таблице 5. представлены результаты вывода остатков. При помощи этой части отчета мы можем видеть отклонения каждой точки от построенной линии регрессии. Наибольшее абсолютное значение остатка в нашем случае - 1,89256, наименьшее - 0,05399. Для лучшей интерпретации этих данных воспользуемся графиком исходных данных и построенной линией регрессии, представленными на рис. 7. Как видим, линия регрессии хорошо "подогнана" под значения исходных данных.
Рис.7.
Приблизительным, но самым простым и наглядным способом проверки удовлетворительности регрессионной модели является графическое представление отклонений: 
Рис. 8. График отклонений
 |
Отложим отклонения
по оси Y, для каждого значения 
. Если регрессионная модель близка к реальной зависимости, то отклонения будут носить случайный характер и их
Рис.8.
сумма будет близка к нулю. В рассмотренном примере 
.
Обычно мерой ошибки регрессионной модели служит среднее квадратическое отклонение
Для нормально распределенных процессов приблизительно 67% точек находится в пределах одного отклонения 
от линии регрессии и 95% - в пределах 
(на рисунке 8 трубки А и В соответственно).
Решим задачу прогнозирования. Поскольку коэффициент детерминации R2 имеет достаточно высокое значение и расстояние 2 мили, для которого надо сделать прогноз, находится в пределах диапазона исходных данных (таблица 1), то мы можем использовать полученное уравнение линейной регрессии для прогнозирования
минут.
При прогнозах на расстояния, не входящие в диапазон исходных данных, нельзя гарантировать справедливость полученной модели. Это объясняется тем, что связь между временем и расстоянием может изменяться по мере увеличения расстояния. На время дальних перевозок могут влиять новые факторы такие, как использование скоростных шоссе, остановки на отдых, обед и т.п.
Таким образом, в результате использования регрессионного анализа в пакете Microsoft Excel мы:
- построили уравнение регрессии;
- установили форму зависимости и направление связи между переменными - положительная линейная регрессия, которая выражается в равномерном росте функции;
- установили направление связи между переменными;
- оценили качество полученной регрессионной прямой;
- смогли увидеть отклонения расчетных данных от данных исходного набора;
- предсказали будущее значение зависимой переменной.
Варианты задач для самостоятельного решения
Задача №1
Постройте регрессионную модель (линейную) для исходных данных приведенных в таблице. Для облегчения расчетов исходные данные содержат только четыре пары значений 
.
Исходные данные задачи №1
| № варианта | Координаты | Точки | x* | |||
| X | 1.6 | |||||
| Y | ? | |||||
| X | 2.3 | |||||
| Y | ||||||
| X | 2.9 | |||||
| Y | ? | |||||
| X | 2.6 | |||||
| Y | 0.5 | ? | ||||
| X | ||||||
| Y | ? | |||||
| X | 2.5 | |||||
| Y | 8.5 | 7.5 | ? | |||
| X | 7.5 | |||||
| Y | 8.5 | 6.5 | ? | |||
| X | ||||||
| Y | ? | |||||
| X | 4.5 | |||||
| Y | ? | |||||
| X | 1.5 | |||||
| Y | 7.5 | 3.5 | ? | |||
| X | 3.6 | |||||
| Y | ? | |||||
| X | ||||||
| Y | 7.5 | 6.5 | 3.5 | ? | ||
| X | 7.8 | |||||
| Y | ? | |||||
| X | 5.6 | 14.7 | ||||
| Y | 7.5 | 3.5 | ? | |||
| X | 5.7 | |||||
| Y | ? | |||||
| X | ||||||
| Y | 7.5 | 6.5 | ? | |||
| X | 7.5 | |||||
| Y | ? | |||||
| X | 10.5 | |||||
| Y | ? | |||||
| X | 3.5 | |||||
| Y | 4.5 | ? | ||||
| X | 13.6 | |||||
| Y | 7.6 | 6.5 | 4.2 | ? | ||
| X | 6.5 | |||||
| Y | 4.5 | ? | ||||
| X | 12.5 | |||||
| Y | 6.5 | 4.2 | ? | |||
| X | 9.6 | |||||
| Y | 4.2 | ? | ||||
| X | 1.5 | 2.5 | 3.5 | 4.5 | 3.9 | |
| Y | 4.5 | ? | ||||
| X | 3.9 | |||||
| Y | ? | |||||
| X | 1.5 | 2.4 | 3.8 | 6.9 | 4.1 | |
| Y | 5.5 | 5.5 | 4.8 | 1.1 | ? | |
| X | 3.6 | |||||
| Y | ? | |||||
| X | 2.8 | |||||
| Y | 5.5 | 4.8 | 1.1 | ? | ||
| X | 14.1 | |||||
| Y | 0.25 | 0.19 | 5.2 | ? | ||
| X | 3.4 | |||||
| Y | ? |
Исследуйте модель с помощью режима Регрессия в MS Excel и сделайте прогноз для x*.
Задача № 2
Для исходных данных, представленных в таблице, были построены следующие регрессионные модели:
· 
;
· 
;
· 
.
Исходные данные задачи №2
| X | ||||||||||
| Y |
С помощью графика отклонений выберите удовлетворительную модель и проверьте свой выбор с помощью соответствующего расчета.
Задача №3
В таблице представлены данные о ценах на комплектующие для ПЭВМ. Комплектующие производятся различными компаниями-производителями и разбиты на группы по своим функциональным возможностям.
Исходные данные задачи №3
| Группа | ||||||||||
| Цена, $ | ||||||||||
| Группа | ||||||||||
| Цена, $ |
Постройте график исходных данных и с его помощью проанализируйте применимость метода наименьших квадратов. Подтвердите свои выводы с помощью расчета (для линейной модели). Прокомментируйте экономические причины полученного результата.