Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа 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.

Решение

На графике строим исходные данные по десяти поездкам.

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Рис.4. График исходных данных и предполагаемая линия регрессии

Помимо расстояния на время поставки влияют пробки на дорогах, время суток, дорожные работы, погода, квалификация водителя, вид транспорта. Построенные точки не находятся точно на линии, что обусловлено описанными выше факторами. Но эти точки собраны вокруг прямой линии, поэтому можно предположить линейную связь между параметрами. Все исходные точки равномерно распределены вдоль предполагаемой прямой линии, что позволяет применить метод наименьших квадратов.

Вычислим суммы, необходимые для расчета коэффициентов уравнения линейной регрессии и коэффициента детерминации R2 с помощью вспомогательной таблицы (таблица 2).

Таблица 2

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru
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
Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru 28,9 Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru 136 Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru 99,41 Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru 435,30 112,4242 122,4

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru .

Вычислим коэффициенты линейной регрессии по формулам (1) и (2):

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Таким образом, искомая регрессионная зависимость имеет вид:

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Наклон линии регрессии Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru 2,66 минут на милю – это количество минут, приходящееся на одну милю расстояния. Координата точки пересечения прямой с осью Y Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru 5,913 минут – это время, которое не зависит от пройденного расстояния, а обуславливается всеми остальными возможными факторами, явно не учтенными при анализе.

Вычислим коэффициент детерминации:

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru или 91,8%.

Проведем регрессионный анализ с использованием режима Регрессия MS Excel. Значения параметров, установленных в одноименном диалоговом окне, представлены на рис.6.

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Рис. 6.

Сгенерируются результаты по регрессионной статистике, представленные в таблице 3.

Таблица 3.

ВЫВОД ИТОГОВ  
   
Регрессионная статистика
Множественный R 0,958275757
R-квадрат 0,918292427
Нормированный R-квадрат 0,90807898
Стандартная ошибка 1,11809028
Наблюдения

Рассмотрим представленную в таблице 3 регрессионную статистику.
Величина R-квадрат, называемая также мерой определенности, характеризует качество полученной регрессионной прямой. Это качество выражается степенью соответствия между исходными данными и регрессионной моделью (расчетными данными). Мера определенности всегда находится в пределах интервала [0;1]. В нашем примере мера определенности равна 0,91829, что говорит об очень хорошей подгонке регрессионной прямой к исходным данным и совпадает с коэффициентом детерминации R2, вычисленным по формуле.

Таким образом, линейная модель объясняет 91,8% вариации времени доставки, что означает правильность выбора фактора (расстояния). Не объясняется Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru вариации времени поездки, которые обусловлены остальными факторами, влияющими на время поставки, но не включенными в линейную модель регрессии.

Рассчитанный уровень значимости αр=1,26E-05<0,05(показатель значимость F в таблице Дисперсионный анализ) подтверждает значимость R2.

Множественный R - коэффициент множественной корреляции R - выражает степень зависимости независимых переменных (X) и зависимой переменной (Y) и равен квадратному корню из коэффициента детерминации, эта величина принимает значения в интервале от нуля до единицы. В простом линейном регрессионном анализе множественный R равен коэффициенту корреляции Пирсона. Действительно, множественный R в нашем случае равен коэффициенту корреляции Пирсона (0,95827), который вычисляется по формуле:

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Теперь рассмотрим среднюю часть расчетов, представленную в таблице 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

Исходя из расчетов, можем записать уравнение регрессии таким образом:

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru (*)

Видим, что это уравнение, совпадает с уравнением, полученным нами при расчете по МНК вручную с точностью до ошибки округления.

Направление связи между переменными определяется на основании знаков (отрицательный или положительный) коэффициента регрессии (коэффициента а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. Как видим, линия регрессии хорошо "подогнана" под значения исходных данных.

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Рис.7.

Приблизительным, но самым простым и наглядным способом проверки удовлетворительности регрессионной модели является графическое представление отклонений: Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Рис. 8. График отклонений

 
  Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Отложим отклонения Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru по оси Y, для каждого значения Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru . Если регрессионная модель близка к реальной зависимости, то отклонения будут носить случайный характер и их

Рис.8.

сумма будет близка к нулю. В рассмотренном примере Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru .

Обычно мерой ошибки регрессионной модели служит среднее квадратическое отклонение

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru

Для нормально распределенных процессов приблизительно 67% точек находится в пределах одного отклонения Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru от линии регрессии и 95% - в пределах Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru (на рисунке 8 трубки А и В соответственно).

Решим задачу прогнозирования. Поскольку коэффициент детерминации R2 имеет достаточно высокое значение и расстояние 2 мили, для которого надо сделать прогноз, находится в пределах диапазона исходных данных (таблица 1), то мы можем использовать полученное уравнение линейной регрессии для прогнозирования

Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru минут.

При прогнозах на расстояния, не входящие в диапазон исходных данных, нельзя гарантировать справедливость полученной модели. Это объясняется тем, что связь между временем и расстоянием может изменяться по мере увеличения расстояния. На время дальних перевозок могут влиять новые факторы такие, как использование скоростных шоссе, остановки на отдых, обед и т.п.

Таким образом, в результате использования регрессионного анализа в пакете Microsoft Excel мы:

  • построили уравнение регрессии;
  • установили форму зависимости и направление связи между переменными - положительная линейная регрессия, которая выражается в равномерном росте функции;
  • установили направление связи между переменными;
  • оценили качество полученной регрессионной прямой;
  • смогли увидеть отклонения расчетных данных от данных исходного набора;
  • предсказали будущее значение зависимой переменной.

Варианты задач для самостоятельного решения

Задача №1

Постройте регрессионную модель (линейную) для исходных данных приведенных в таблице. Для облегчения расчетов исходные данные содержат только четыре пары значений Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru .

Исходные данные задачи №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

Для исходных данных, представленных в таблице, были построены следующие регрессионные модели:

· Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru ;

· Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru ;

· Справочная информацця по технологии работы с режимом "Регрессия" надстройки Пакет анализа MS Excel - student2.ru .

Исходные данные задачи №2

X
Y

С помощью графика отклонений выберите удовлетворительную модель и проверьте свой выбор с помощью соответствующего расчета.

Задача №3

В таблице представлены данные о ценах на комплектующие для ПЭВМ. Комплектующие производятся различными компаниями-производителями и разбиты на группы по своим функциональным возможностям.

Исходные данные задачи №3

Группа
Цена, $
Группа
Цена, $

Постройте график исходных данных и с его помощью проанализируйте применимость метода наименьших квадратов. Подтвердите свои выводы с помощью расчета (для линейной модели). Прокомментируйте экономические причины полученного результата.

Наши рекомендации