С помощью ППП Ехсеl. Для повышения эффективности решения задачи необходимо воспользоваться возможностями ППП Ехсеl, а именно опцией Мастер функций

Для повышения эффективности решения задачи необходимо воспользоваться возможностями ППП Ехсеl, а именно опцией Мастер функций. В основном будет востребована категория Статистические и некоторые функции из категорий Математические и Ссылки и массивы. Перечень этих функций и краткое описание представлены в Приложении «Стандартные функции».

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

1) Реализовать формулы (1.1) – (1.15) с помощью ППП Ехсеl.

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

1. Реализация регрессионных формул (1.1) – (1.15)

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

Рис. 1.1

С помощью функций ППП Ехсеl определить оценки коэффициентов регрессии b₀ , b₁ , реализуя формулы (1.7), (1.8), например , вычисляются с помощью функции СРЗНАЧ,а с помощью функции СУММПРОИЗВ( )/ n. Для вычисления можно воспользоваться соотношением СУММКВ(число1;число2; ...) /n. Однако составляющие коэффициента b₁ можно вычислить проще, через Статистические функции КОВАР(массив1; массив2) Cov (X,Y) и ДИСПР S_x² или СТАНДОТКЛОНП S_x².

По соответствующим формулам вычисляются дисперсии , и на основании Т-статистик делается вывод о значимости коэффициентов регрессии и определяются их доверительные интервалы. Значения t_кр можно получить, используя статистическую функцию СТЬЮДРАСПОБР.

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

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

Параметры линейной регрессии можно рассчитать и сразу. Для этого в Ехсеl существуют функции Наклон и Отрезок. Функция Наклонслужит для определения углового коэффициента связи (b₁), а функция Отрезок – для определения свободного члена уравнения (b₀). В качестве аргументов этих функций вводятся массивы Х и Y.

Рис. 1.2

Рис. 1.3

Кроме перечисленных возможностей существует еще и следующая возможность. Построим график по имеющимся данным. Чтобы ось Х отражала фактические данные, выберем тип диаграммы Точечная. На построенной диаграмме выделим график функции, щелкнув по ней левой кнопкой мыши. Затем нажмем правую кнопку мыши, выведем контекстное зависимое меню, в котором выберем опцию Добавить линию тренда. В панели линии тренда во вкладке Тип надо выбрать тип функции (по умолчанию выбирается Линейная). Во вкладке Параметры введем название тренда (теоретической кривой) и установим флажки «Показывать уравнение на диаграмме» и «Поместить на диаграмму величину достоверности аппроксимации (R^2)». В результате появится график вида (рис. 1.4.).

Рис. 1.4

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

Одной из таких функций является встроенная статистическая функция ЛИНЕЙН(описание функции и ее аргументов приведено в приложении «Стандартные функции»).

Дополнительная регрессионная статистика (в случае ее инициализации) будет выводиться в порядке, указанном в следующей схеме:

Для данных из вышерассмотренного примера результат вычисления функции ЛИНЕЙН представлен на рис. 1.5.

Рис. 1.5

Примечание. Функция ЛИНЕЙН должна быть введена, как формула массива в интервал с необходимым количеством строк и столбцов. Перед использованием функции ЛИНЕЙН выделяем ячейку (1,1) (1-ая строка, 1-ый столбец) массива, в который будет занесен результат вычисления функции, затем инициализируем Мастер функций, выбираем категорию Статистические и функцию Линейн. Щелкните по кнопке ОК. После заполнения аргументов в ячейке (1,1) появится первый элемент итоговой таблицы. Чтобы раскрыть всю таблицу, выделите массив нужной размерности, включая и ячейку (1,1) (в нашем примере 5 строк, 2 столбца), нажмите на клавишу <F2>, а затем – на комбинацию клавиш <CTRL>+<SHIFT>+<ENTER>.

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

В результате в данную ячейку будет записан элемент (1,2) регрессионной таблицы, т.е. значение b₀. Таким образом можно создать более наглядную таблицу (рис. 1.6).

Рис. 1.6

Кроме функции Линейнможно также воспользоваться Статистическим пакетом анализа данных. Чтобы установить пакет Анализа данных в меню Сервис, выберите команду Надстройки и установите флажок Пакет анализа. Диалоговое окно данной опции приведено на рис. 1.7.

Рис. 1.7

Чтобы запустить пакет анализа в меню Сервис, выберите команду Анализ данных. В диалоговом окне Анализ данных в списке Инструменты анализа выберите строку Регрессия (рис. 1.8).

Рис. 1.8

Примечание. В Ехсеl-2007 установка пакета осуществляется по схеме: «Officе» Параметры Ехсеl Неактивные надстройки приложений Пакет анализа в окне Надстройки (рис. 1.7) установить флажок Пакет анализа ОК. Запускается пакет анализа в меню Данные.

Заполните диалоговое окно ввода данных и параметров вывода (рис. 1.9):

Входной интервал Y – диапазон, содержащий данные объясняемой переменной;

Входной интервал Х – диапазон, содержащий данные объясняющей переменной;

Метки – флажок, который указывает, содержит ли первая строка названия столбцов или нет;

Константа – ноль – флажок, указывающий на наличие или отсутствие свободного члена в уравнении;

Выходной интервал – достаточно указать левую верхнюю ячейку будущего диапазона;

Новый рабочий лист – можно задать произвольное имя нового листа.

Рис. 1.9

Если необходимо получить информацию и графики остатков, установите соответствующие флажки в диалоговом окне. Щелкните по кнопке ОК.

Результаты регрессионного анализа представлены на рис. 1.10.

Рис. 1.10

Поясним некоторые обозначения результатов регрессионного анализа:

Множественный R - коэффициент корреляции r_xy;

R-квадрат – коэффициент детерминации;

Стандартная ошибка – СКО объясняемой переменной y;

df - количество степеней свободы;

SS (Регрессия) - объясненная сумма квадратов k_i².

SS (Остаток) - остаточная сумма квадратов e_i² ;

Коэффициенты (Y-пересечение) – b₀;

Коэффициенты (минимум( Х)) – b₁;

Стандартная ошибка(Y-пересечение) – ;

Стандартная ошибка(минимум( Х)) – ;

Нижние (Верхние) – соответствующие границы доверительных интервалов для оценок b₀ и b₁.

Как видно функции Линейн и особенно опция Регрессия выводят большинство показателей, которые до этого были рассчитаны с помощью одиночных функций.

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

Задача 1. В выборке представлены данные по цене (Р) некоторого блага и количеству (Q) данного блага, приобретенному домохозяйством в течение года.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Р и Q.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Р и Q.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз количества приобретаемого блага и доверительный интервал для него при значении Р = Р_прогн..

Вариант 1.1

Р_прогн= 50, уровень значимости = 0,01.

Вариант 1.2

Р_прогн= 100, уровень значимости = 0,02.

Вариант 1.3

Р_прогн= 65, уровень значимости = 0,03.

Вариант 1.4

Р_прогн= 30, уровень значимости = 0,04.

Вариант 1.5

Р_прогн= 40, уровень значимости = 0,05.

Задача 2. Имеются данные об уровне механизации работ Х (%) и производительности труда Y (т/ч) для 14 предприятий.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Х и Y.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Х и Y.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз производительности труда и доверительный интервал для нее при значении Х = Х_прогн..

Вариант 2.1

Х_прогн= 80, уровень значимости = 0,02.

Вариант 2.2

Х_прогн= 80, уровень значимости = 0,03.

Вариант 2.3

Х_прогн= 75, уровень значимости = 0,04.

Вариант 2.4

Х_прогн= 85, уровень значимости = 0,05.

Вариант 2.5

Х_прогн= 90, уровень значимости = 0,06.

Задача 3. По территориям региона приведены данные в таблице.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Х и Y.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Х и Y.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз среднедневной заработной платы и доверительный интервал для нее при значении Х = Х_прогн..

Вариант 3.1

Х_прогн= 900, уровень значимости = 0,03.

Вариант 3.2

Х_прогн= 1000, уровень значимости = 0,04.

Вариант 3.3

Х_прогн= 950, уровень значимости = 0,05.

Вариант 3.4

Х_прогн= 980, уровень значимости = 0,06.

Вариант 3.5

Х_прогн= 820, уровень значимости = 0,07.

Задача 4. По территориям региона известны данные за 2006 год.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Х и Y.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Х и Y.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз доли денежных доходов, направленных на прирост сбережений и доверительный интервал для нее при значении Х = Х_прогн..

Вариант 4.1 (Центральный регион)

Х_прогн= 13, уровень значимости = 0,04.

Вариант 4.2 (Волго-Вятский регион)

Х_прогн= 12, уровень значимости = 0,05.

Вариант 4.3 (Поволжский регион)

Х_прогн= 8,5, уровень значимости = 0,06.

Вариант 4.4 (Северный и Северозападный регионы)

Х_прогн= 15, уровень значимости = 0,07.

Вариант 4.5 (Дальневосточный регион)

Х_прогн= 18, уровень значимости = 0,08.

Задача 5. По территориям региона известны данные за 2006 год.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Х и Y.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Х и Y.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз размера пенсий при значении прожиточного минимума Х = Х_прогн..

Вариант 5.1 (Центральный регион)

Х_прогн= 3, уровень значимости = 0,01.

Вариант 5.2 (Волго-Вятский регион)

Х_прогн= 3, уровень значимости = 0,03.

Вариант 5.3 (Поволжский регион)

Х_прогн= 3, уровень значимости = 0,05.

Вариант 5.4 (Северный и Северозападный регионы)

Х_прогн= 3, уровень значимости = 0,07.

Вариант 5.5 (Дальневосточный регион)

Х_прогн= 4, уровень значимости = 0,09.

Задача 6. По территориям региона известны данные за 2006 год.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Х и Y.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Х и Y.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз потребительских расходов при средней заработной плате и выплатах социального характера Х = Х_прогн..

Вариант 6.1 (Центральный регион)

Х_прогн= 13,5, уровень значимости = 0,01.

Вариант 6.2 (Волго-Вятский регион)

Х_прогн= 13, уровень значимости = 0,02.

Вариант 6.3 (Поволжский регион)

Х_прогн= 10, уровень значимости = 0,03.

Вариант 6.4 (Северный и Северозападный регионы)

Х_прогн= 16, уровень значимости = 0,04.

Вариант 6.5 (Дальневосточный регион)

Х_прогн= 18, уровень значимости = 0,05.

Задача 7. По территориям региона известны данные за 2006 год.

Задание

1. Постройте корреляционное поле и по его виду определите форму зависимости между Х и Y.

2. Оцените по МНК параметры уравнения линейной регрессии.

3. Оцените выборочный коэффициент корреляции и сделайте предварительный вывод о силе линейной взаимосвязи параметров Х и Y.

4. Проверьте качество уравнения регрессии:

- значимость коэффициентов регрессии;

- интервальные оценки коэффициентов регрессии;

- значимость уравнения регрессии в целом.

5. Проинтерпретируйте результаты.

6. Сделайте прогноз потребительских расходов при денежном доходе на душу населения Х = Х_прогн..

Вариант 7.1 (Центральный регион)

Х_прогн= 15,5, уровень значимости = 0,02.

Вариант 7.2 (Волго-Вятский регион)

Х_прогн= 14,5, уровень значимости = 0,03.

Вариант 7.3 (Поволжский регион)

Х_прогн= 12, уровень значимости = 0,04.

Вариант 7.4 (Северный и Северозападный регионы)

Х_прогн= 18, уровень значимости = 0,05.

Вариант 7.5 (Дальневосточный регион)

Х_прогн= 20, уровень значимости = 0,06.

Вопросы для подготовки к защите индивидуального задания

1. Что такое функция регрессии?

2. Назовите основные причины наличия в регрессионной модели случайного отклонения.

3. Что понимается под спецификацией модели, и как она осуществляется?

4. В чем состоит различие между теоретическими и эмпирическими уравнениями регрессии?

5. В чем суть метода наименьших квадратов (МНК)?

6. Приведите формулы расчета коэффициентов эмпирического парного линейного уравнения регрессии по МНК.

7. Как связаны эмпирические коэффициенты линейной регрессии с выборочным коэффициентом корреляции?

8. Проинтерпретируйте коэффициенты эмпирического парного линейного уравнения регрессии.

9. Как определяются стандартные ошибки регрессии и коэффициентов регрессии?

10. Опишите схему проверки гипотез о величине коэффициентов регрессии.

11. В чем суть статистической значимости коэффициентов регрессии?

12. Приведите схему определения интервальных оценок коэффициентов регрессии.

13. Как строится и что позволяет определить доверительный интервал для условного математического ожидания зависимой переменной?

14. В чем суть предсказания индивидуальных значений зависимой переменной?

15. Объясните суть коэффициента детерминации.

16. В каких пределах изменяется коэффициент детерминации?