Реализация задания на компьютере с помощью ППП Ехсеl. Здесь так же, как и в парной регрессии необходимо выполнить задание в двух вариантах:

Здесь так же, как и в парной регрессии необходимо выполнить задание в двух вариантах:

ВНИМАНИЕ ! Каждый студент должен выполнить индивидуальное задание с использованием компьютера в двух вариантах:

3) Реализовать формулы (2.1) – (2.19) с помощью одиночных функций ППП Ехсеl.

4) Использовать «комплексные» функции, выходом которых являются не только коэффициенты регрессии, но и дополнительная регрессионная статистика (среднеквадратические отклонения, коэффициент детерминации и т.д.).

3) Реализация регрессионных формул (2.1) – (2.19) с помощью одиночных функций.

В первую очередь необходимо представить данные наблюдений в матричной форме (см. рис.2.1). Затем используя матричные функции из Мастер функций: МОБР, МУМНОЖ, ТРАНСП реализуем формулу (2.8), результатом которой будет вектор оценок коэффициентов регрессии В.

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

Для вычисления дисперсий необходимо вычислить S² в соответствие с формулой (2.10). На основании Т-статистик делается вывод о значимости коэффициентов регрессии их доверительные интервалы. Значения t_кр можно получить, используя статистическую функцию СТЬЮДРАСПОБР. По соответствующим формулам вычисляются коэффициент детерминации R² и F – критерий, на основании которых делается вывод о значимости уравнения регрессии в целом. Для нахождения критической точки f_кр нужно воспользоваться функцией FРАСПОБР.

Проверка соответствия предпосылкам МНК осуществляется по критерию Дарбина – Уотсона. Критические значения распределения определяются из таблицы (электронного варианта таблицы нет).

Примерный вид реализации задачи на компьютере представлен на рис.2.2.

Значение Y Матрица Х

Рис.2.1

Рис.2.2

Для графической иллюстрации приближения корреляционной функции и выборочных данных y_i воспользуемся Мастером диаграмм (График) (см. рис.2.3).

Значение Y Yмод

22,48852

23,7304085

31,009917

28,6979627

33,4936941

37,0475369

39,531314

38,4612482

45,7407567

51,7783766

53,0202652

Рис.2.3

На рис.2.2 в ячейке с названием «S(Yp)» была вычислена стандартная ошибка прогноза объясняемой переменной по формуле:

S(Y_р) = S ,

которую необходимо использовать для определения интервальной оценки среднего значения предсказания.

4) Использование «Комплексных» функций.

В качестве такой функции может быть использована встроенная статистическая функция ЛИНЕЙН.

Дополнительная регрессионная статистика (в случае ее инициализации) будет выводиться в порядке, указанном на рис.2.4.

Рис.2.4.

Обозначения на рисунке следующие: b – свободный коэффициент линейной регрессии; m_i – коэффициенты при х_i ; Se – стандартные ошибки коэффициентов регрессии; r2 - коэффициент детерминации; Sey - стандартная ошибка для оценки у; F – F- статистика; d_f – количество степеней свободы; Ssрег – регрессионная сумма квадратов; Ssост – остаточная сумма квадратов.

Для лучшей наглядности можно нужные значения из этой таблицы выбирать индивидуально и размещать в нужных форматах документа. Для этого можно воспользоваться функцией ИНДЕКС из категории Ссылки и массивы. Выделите ячейку, в которую хотите поместить отдельный элемент массива и введите формулу, например: Индекс(Линейн(Y;Х;1;1);1;2). В результате в данную ячейку будет записан элемент (1,2) регрессионной таблицы. Таким образом, можно создать более наглядную таблицу.

Пример решения задания на компьютере с использованием функции ЛИНЕЙНпредставлен на рисунках 2.5, 2.6.

Значения X1 Значения X2 Значения Y

Результаты вычислений параметров модели

y = b0+b1*x1+b2*x2

b0 b1 b2 Sb0 Sb1 Sb2

2,9619489 0,1241889 3,5538428 1,89297977 0,021231 1,01465

Sy r2 F -статист Кол.ст.св Ss рег Ss ост

1,7407109 0,9777126 175,47354 1063,396 24,2406

Определение Ттабл и Fтабл

Al = 0,05 Выводы:1)Если Тbi > Tтабл,

то коэффициент bi - статис-

тически значим.

Fтабл Ттабл 2)Если F-статист > Fтабл,

то коэффициент детерми-

4,4589683 2,3060056 нации r2- cтатистически зна-

чим. Общее качество моде-

ли высокое.

Рис.2.5

№ изм. Yфакт Yмод

22,48852

23,73041

31,00992

28,69796

33,49369

37,04754

39,53131

38,46125

45,74076

51,77838

53,02027

Определение Т-стат. для коэффициентов bi

и доверительных интервалов

b0 b1 b2

Т-статистика 1,56470182 5,84952 3,5025331

Нижн.гран.дов.инт. -1,4032731 0,075231 1,2140558

Верх.гран.дов.инт. 7,32717092 0,173147 5,8936299

Рис.2.6

Так же, как и в парной регрессии для оценки коэффициентов множественной регрессии и получения дополнительной статистики кроме функции Линейнможно воспользоваться Статистическим пакетом анализа данных.

Установка пакета анализа достаточно подробно описана в п. 1.2. В диалоговом окне Анализ данных в списке Инструменты анализа выберите строку Регрессияи заполните диалоговое окно ввода данных и параметров вывода. Результаты регрессионного анализа для данных выше использованного примерапредставлены на рис.2.7.

Рис.2.7

2.3. Контрольные задания

Задача 1. Предполагается, что объем Q предложения некоторого блага для функционирующей в условиях конкуренции фирмы зависит линейно от цены Р данного блага и заработной платы W сотрудников фирмы, производящих данное благо:

Q = ₀ + ₁ Р + ₂ W + .

Статистические данные, собранные за 12 месяцев, занесены в таблицу.

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2;

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b _i , i =0, 1, 2;

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных P и W на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Q_прогн для прогнозных значений Р_прогн , W_прогн и определить доверительный интервал для Q_прогн;

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

Вариант 1.1

Q

P

W

Р_прогн = 60, W_прогн = 2, = 0,01.

Вариант 1.2

Q

P

W

Р_прогн = 75, W_прогн = 1, = 0,02.

Вариант 1.3

Q

P

W

Р_прогн = 53, W_прогн = 4, = 0,03.

Вариант 1.4

Q

P

W

Р_прогн = 28, W_прогн = 9, = 0,04.

Вариант 1.5

Q

P

W

Р_прогн = 25, W_прогн = 12, = 0,05.

Задача 2. Для объяснения изменения ВНП за 10 лет строится регрессионная модель с объясняющими переменными – потреблением (С) и инвестициями (I):

ВНП = ₀ + ₁ С + ₂ I + .

Статистические данные приведены в таблице.

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2;

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2;

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных С и I на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной ВНП _прогн для прогнозных значений С _прогн , I _прогн и определить доверительный интервал для ВНП_прогн;

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

Вариант 2.1

С, млрд $ 8,0 9,5 11,0 12,0 13,0 14,0 15,0 16,5 17,0 18,0

I, млрд $ 1,65 1,8 2,0 2,1 2,2 2,4 2,65 2,85 3,2 3,55

ВНП, млрд $ 14,0 16,0 18,0 20,0 23,0 23,5 25,0 26,5 28,5 30,5

С _прогн = 20, I _прогн = 4, = 0,02.

Вариант 2.2

С, млрд $ 5,0 6,5 6,9 7,5 8,0 10,0 11,0 12,0 14,0 17,0

I, млрд $ 1,2 1,4 1,6 1,9 2,0 2,4 2,5 2,7 3,0 3,4

ВНП, млрд $ 10,0 10,5 11,0 13,0 14,0 15,0 18,0 21,0 24,0 28,0

С _прогн = 20, I _прогн = 4, = 0,03.

Вариант 2.3

С, млрд $ 7,0 8,0 9,0 10,5 11,0 12,5 13,0 14,0 15,0 16,0

I, млрд $ 1,2 1,4 1,5 1,6 1,8 2,0 2,2 2,4 2,7 2,9

ВНП, млрд $ 12,0 14,0 15,0 16,0 18,0 22,0 23,0 23,5 25,0 26,0

С _прогн = 14,5, I _прогн = 3, = 0,04.

Вариант 2.4

С, млрд $ 6,0 7,5 9,0 10,0 11,0 12,0 13,0 14,0 15,6 16,8

I, млрд $ 1,4 1,5 1,7 1,8 2,0 2,1 2,2 2,4 2,6 3,0

ВНП, млрд $ 11,0 13,0 15,0 16,0 18,0 20,0 23,0 23,5 25,0 26,5

С _прогн = 18,0, I _прогн = 3,2, = 0,05.

Вариант 2.5

С, млрд $ 9,0 10,5 12,0 13,0 14,0 15,0 16,5 17,5 18,0 18,5

I, млрд $ 1,4 1,6 2,1 2,2 2,4 2,7 2,9 3,3 3,6 4,0

ВНП, млрд $ 13,0 16,0 20,0 23,0 23,5 25,0 27,0 29,0 31,0 33,0

С _прогн = 19,0, I _прогн = 4,0, = 0,06.

Задача 3. В таблице даны статистические данные о расходах на питание Y, душевом доходе Х₁ и размере семьи Х₂ для девяти групп семей. Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х₂ _прогн и определить доверительный интервал для Y_прогн;

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

Вариант 3.1

№ Расход на питание Y, у.е. Душевой доход Х₁, у.е. Размер семей Х₂

1,4

2,0

2,5

3,1

3,2

3,4

3,6

3,9

3,6

Х_{1 прогн} = 8000, Х₂ _прогн = 4,0, = 0,01.

Вариант 3.2

№ Расход на питание Y, у.е. Душевой доход Х₁, у.е. Размер семей Х₂

1,2

2,0

2,4

2,4

3,0

3,2

3,4

3,6

3,6

Х_{1 прогн} = 10000, Х₂ _прогн =3,0, = 0,02.

Вариант 3.3

№ Расход на питание Y, у.е. Душевой доход Х₁, у.е. Размер семей Х₂

1,0

1,6

2,0

2,0

2,3

2,8

3,0

3,2

3,6

Х_{1 прогн} = 8000, Х₂ _прогн =3,0, = 0,03.

Вариант 3.4

№ Расход на питание Y, у.е. Душевой доход Х₁, у.е. Размер семей Х₂

1,1

1,8

2,0

2,1

2,4

2,4

2,6

№ Расход на питание Y, у.е. Душевой доход Х₁, у.е. Размер семей Х₂

3,0

3,0

Х_{1 прогн} = 13000, Х₂ _прогн =3,2, = 0,04.

Вариант 3.5

№ Расход на питание Y, у.е. Душевой доход Х₁, у.е. Размер семей Х₂

1,7

2,0

2,4

2,8

2,9

3,0

3,1

3,0

3,5

Х_{1 прогн} = 11000, Х₂ _прогн =3,0, = 0,05.

Задача 4. Изучается влияние стоимости основных и оборотных средств на величину валового дохода торговых предприятий. Для этого по 12 торговым предприятиям были получены данные приведенные в таблице. Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i = 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х₂ _прогн и определить доверительный интервал для Y_прогн;

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

Вариант 4.1

Номер предприятия Валовый доход за год, млн.руб. Среднегодовая стоимость, млн.руб.

Основных фондов Оборотных средств

Х_{1 прогн} = 140, Х₂ _прогн =58, = 0,01.

Вариант 4.2

Номер предприятия Валовый доход за год, млн.руб. Среднегодовая стоимость, млн.руб.

Основных фондов Оборотных средств

Х_{1 прогн} = 150, Х₂ _прогн =100, = 0,02.

Вариант 4.3

Номер предприятия Валовый доход за год, млн.руб. Среднегодовая стоимость, млн.руб.

Основных фондов Оборотных средств

Х_{1 прогн} = 100, Х₂ _прогн =80, = 0,03.

Вариант 4.4

Номер предприятия Валовый доход за год, млн.руб. Среднегодовая стоимость, млн.руб.

Основных фондов Оборотных средств

Х_{1 прогн} = 90, Х₂ _прогн =50, = 0,04.

Вариант 4.5

Номер предприятия Валовый доход за год, млн.руб. Среднегодовая стоимость, млн.руб.

Основных фондов Оборотных средств

Х_{1 прогн} = 50, Х₂ _прогн =60, = 0,05.

Задача 5. Имеются данные по странам за 2005 год. Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i= 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х₂ _прогн и определить доверительный интервал для Y_прогн;

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

Вариант 5.1

Страна Индекс человеческого развития, Y Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х₁ Суточная калорийность питания населения, ккал на душу, Х₂

Австрия 0,904 77,0

Австралия 0,922 78,2

Аргентина 0,827 72,9

Белоруссия 0,763 68,0

Бельгия 0,923 77,2

Бразилия 0,739 66,8

Великобритания 0,918 77,2

Венгрия 0,795 70,9

Германия 0,906 77,2

Х_{1 прогн} = 80, Х₂ _прогн =3500, = 0,01.

Вариант 5.2

Страна Индекс человеческого развития, Y Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х₁ Суточная калорийность питания населения, ккал на душу, Х₂

Греция 0,867 78,1

Дания 0,905 75,7

Египет 0,616 66,3

Израиль 0,883 77,8

Индия 0,545 62,6

Испания 0.894 78,0

Италия 0,900 78,2

Канада 0,932 79,0

Казахстан 0,740 67,7

Х_{1 прогн} = 75, Х₂ _прогн =3000, = 0,02.

Вариант 5.3

Страна Индекс человеческого развития, Y Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х₁ Суточная калорийность питания населения, ккал на душу, Х₂

Китай 0,701 69,8

Латвия 0,744 68,4

Нидерланды 0,921 77,9

Норвегия 0,927 78,1

Польша 0,802 72,5

Корея 0,852 72,4

Россия 0,747 66,6

Румыния 0,752 69,9

США 0,927 76,6

Х_{1 прогн} = 72, Х₂ _прогн =3500, = 0,03.

Вариант 5.4

Страна Индекс человеческого развития, Y Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х₁ Суточная калорийность питания населения, ккал на душу, Х₂

Турция 0,728 69,0

Украина 0,721 68,8

Финляндия 0,913 76,8

Франция 0,918 78,1

Чехия 0,833 73,9

Швейцария 0,914 78,6

Швеция 0,923 78,5

ЮАР 0,695 64,1

Япония 0,924 80,0

Х_{1 прогн} = 76, Х₂ _прогн =3100, = 0,04.

Вариант 5.5

Страна Индекс человеческого развития, Y Ожидаемая продолжительность жизни при рождении 2005 г., лет, Х₁ Суточная калорийность питания населения, ккал на душу, Х₂

Австрия 0,904 77,0

Белоруссия 0,763 68,0

Греция 0,867 78,1

Казахстан 0,740 67,7

Китай 0,701 69,8

США 0,927 76,6

Турция 0,728 69,0

Франция 0,918 78,1

ЮАР 0,695 64,1

Х_{1 прогн} = 73, Х₂ _прогн =3300, = 0,05.

Задача 6. Изучается зависимость средней ожидаемой продолжительности Y (лет) от ВВП в паритетах покупательной способности Х₁ и коэффициента младенческой смертности Х₂ (%). Построить регрессионную модель:

Y= ₀ + ₁ Х₁ + ₂ Х₂ + .

Задание.

1. По МНК оценить коэффициенты линейной регрессии _i , i= 0, 1, 2.

2. Оценить статистическую значимость найденных эмпирических коэффициентов регрессии b_i , i =0, 1, 2.

3. В соответствие с заданным значением построить доверительные интервалы для найденных коэффициентов;

4. Вычислить коэффициент детерминации R² и оценить его статистическую значимость при заданном значении ;

5. Определить какой процент разброса зависимой переменной объясняется данной регрессией;

6. Сравнить коэффициент детерминации R² со скорректированным коэффициентом детерминации;

7. Вычислить статистику DW Дарбина-Уотсона и оценить наличие автокорреляции;

8. Посредством коэффициентов b_i , i = 1, 2, оценить в % отношении влияние объясняющих переменных Х₁ и Х₂ на изменение объясняемой переменной;

9. Спрогнозировать значение объясняемой переменной Y_прогн для прогнозных значений Х_{1 прогн} , Х₂ _прогн и определить доверительный интервал для Y_прогн;

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

Вариант 6.1

Страна Y Х₁ Х₂

Мозамбик 3,0

Индия 5,2

Бенин 6,5

Шри-Ланка 12,1

Египет 14,2

Тунис 18,5

Белоруссия 15,6

Бразилия 20,0

Тринидад 31,9

Австралия 70,2

Италия 73,7

Швейцария 95,9

Х_{1 прогн} = 80, Х₂ _прогн =5, = 0,01.

Вариант 6.2

Страна Y Х₁ Х₂

Бурунди 2,3

Того 4,2

Никарагуа 7,4

Конго 7,6

Индонезия 14,1

Парагвай 13,5

Перу 14,0

Мавритания 49,0

Малайзия 33,4

Израиль 61,1

Канада 78,3

Япония 82,0

Х_{1 прогн} = 76, Х₂ _прогн =10, = 0,02.

Вариант 6.3

Страна Y Х₁ Х₂

Чад 2,6

Кения 5,1

Гана 7,4

Камерун 7,8

Страна Y Х₁ Х₂

Филиппины 10,6

Алжир 19,6

Таиланд 28,0

Мексика 23,7

Чили 35,3

Ирландия 58,1

Финляндия 65,8

Дания 78,7

Х_{1 прогн} = 81, Х₂ _прогн =4, = 0,03.

Вариант 6.4

Страна Y Х₁ Х₂

Непал 4,3

Нигерия 4,5

Ангола 4,9

Китай 10,8

Марокко 12,4

Ямайка 13,1

Панама 22,2

ЮАР 18,6

Уругвай 24,6

Нов. Зеландия 60,6

Гонконг 85,1

СЩА 100,0

Х_{1 прогн} = 93, Х₂ _прогн =7, = 0,04.

Вариант 6.5

Страна Y Х₁ Х₂

Буркина-Фасо 2,9

Мали 2,0

Пакистан 8,3

Гондурас 7,0

Новая Гвинея 9,0

Доминик. Респ. 14,3

Турция 20,7

Венесуэла 29,3

Страна Y Х₁ Х₂

Аргентина 30,8

Испания 53,8

Швеция 68,7

Австрия 78,8

Х_{1 прогн} = 84, Х₂ _прогн =5, = 0,05.

Задача 7. Данные о деятельности крупнейших компаний США представлены в таблице. Построить регрессионную модель:
⇐ Назад
1
2
3
456
7
8
9
10
11
12
13
14
15
16
Далее ⇒