3.2 Нахождение уравнения линейной регрессии графическим методом.

1. Для получения уравнения регрессии построим корреляционное поле переменных X (затраты на рекламу) и Y (количество туристов).

2. Выделите диапазон ячеек В2:С21, запустите мастера диаграмм и выберите тип диаграммы – Точечная. Задайте для диаграммы имя – Корреляционное поле, ось Х – Затраты на рекламу, ось Y – Количество туристов. На последнем шаге мастера укажите место расположения – отдельный лист.

3. Добавьте линию тренда на точечный график. Для этого необходимо выделить диаграмму и выполнить команду меню Диаграмма /Добавить линию тренда, либо выполнить данную команду из контекстного меню, щелкнув по любой точке графика. Линия тренда – графическое представление направления изменения ряда данных

4. Выберите тип тренда Линейный, который используется для аппроксимации данных по методу наименьших квадратов в соответствии с уравнением y=b*x+ a.

5. На вкладке Параметры установите флажки Показать уравнение на диаграмме и Поместить на диаграмму величину достоверности аппроксимации . Щелкните по кнопке ОК. - это число от 0 до 1, которое отражает близость линии тренда к фактическим данным. Линия тренда наиболее соответствует действительности, когда значение близко к 1.

6. Сравните уравнение регрессии, полученное графическим методом (рис. 17), с уравнением, рассчитанным с помощью функции ЛИНЕЙН.

Рис.17. Модель линейной регрессии.

3.3 Построение модели линейной регрессии с помощью инструмента «Регрессия».

1. Сначала убедитесь, что был активизирован Пакет анализа, т.е. в меню Сервис есть команда Анализ данных. Если нет, то выполните команду Сервис/Надстройки. В диалоговом окне Надстройки установите флажок Пакет анализа и щелкните по кнопке ОК.

2. Далее выполните команду Сервис/Анализ данных. Выберите инструмент анализа Регрессия из списка Инструменты анализа. Щелкните по кнопке ОК.

3. На экране появится диалоговое окно Регрессия (рис.18).

• в текстовом поле ВХОДНОЙ ИНТЕРВАЛ Y введите диапазон со значениями зависимой переменной $C$2:$C$21.

• в текстовом поле ВХОДНОЙ ИНТЕРВАЛ Х введите диапазон со значениями независимых переменных $В$2:$В$21.

• Убедитесь, что в поле Уровень надежности введено 95 % и переключатель Параметры вывода установлен в положении Новый рабочий лист.

• Щелкните по кнопке ОК.

Рис. 18. Диалоговое окно инструмента анализа Регрессия.

4. В результате на новом листе будет отображены результаты использования инструмента Регрессия (рис.19).

Рис.19. Вывод итогов инструмента Регрессия.

5. В области Регрессионная статистика получили:

   • Множественный R=0,952 (коэффициент корреляции по Пирсону) Сравните этот результат с коэффициентом корреляции, полученным в задании №1.

• R-квадрат = 0,906. Эта величина носит название коэффициента детерминации – это частное от суммы квадратов, обусловленных регрессией и остаточной суммы квадратов. Эта величина характеризует качество регрессионной прямой, то есть степень соответствия между регрессионной моделью и исходными данными. Мера определённости всегда лежит в диапазоне от 0 до 1. В нашем случае R² = 0,907 – это значит, что регрессионной моделью описано 90,7 % случаев. В простом линейном регрессионном анализе квадратный корень из коэффициента детерминации, равен коэффициенту корреляции Пирсона. Сравните полученную величину с величиной достоверности аппроксимации, полученной при построении модели линейной регрессии графическим способом.

• Стандартная ошибка = 37,73 – оценка адекватности линейной модели. Сравните этот результат с величиной , полученной в задании №4 (по формуле (9)).