2. Стандартную (среднюю квадратическую) ошибку модели.

(9),

где - фактические значения результативного признака, полученные по данным наблюдений;

- рассчитанные значения результативного признака;

n - объем выборки;

m – число параметров в уравнении регрессии.

Рис. 7 Виды зависимостей линии регрессии

Задание 3. Построение модели линейной регрессии

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

3.1 Расчет параметров уравнения линейной регрессии с использованием функции линейн

Откройте новую книгу MS Excel и создайте таблицу согласно рис. 15, сохраните в своей папке под именем Регрессионный_Анализ.xls. (Данные можно также скопировать из файла по заданию №1).

Рис.8. Данные для задачи №3.

1. Для получения коэффициентов а и b линейного уравнения регрессии y=b*x+ a, описывающего зависимость количества привлеченных туристов от затрат на рекламу воспользуемся статистической функцией ЛИНЕЙН. Для этого выделите две ячейки C26:D26 и выполните вставку функции ЛИНЕЙН с аргументами согласно рис.9. Здесь Известные_значения_y – диапазон значений Количество туристов, Известные_значения_x – диапазон значений Затраты на рекламу. Нажмите комбинацию клавиш SHIFT+CTRL+ENTER.

Рис. 9. Аргументы функции ЛИНЕЙН.

2. В ячейку C27 введите уравнение регрессии y=b*x+ a, (вместо b и a подставьте полученные коэффициенты линейной регрессии).

3. Рассчитайте стандартную (среднюю квадратическую) ошибку модели по формуле (7). Для этого:

• В диапазоне ячеек D2:D21 рассчитайте значения результативного признака путем подстановки значений независимого признака-регрессора Х в уравнение линейной регрессии.

• В диапазоне ячеек E2:E21 рассчитайте отклонения фактических значений результативного признака от рассчитанных значений .

• В диапазоне ячеек F2:F21 рассчитайте квадраты отклонений .

• В ячейке G2 рассчитайте сумму квадратов отклонений .

• В ячейке Н2 рассчитайте стандартную ошибку модели по формуле 9.

• Проанализируйте величину ошибки. Для этого рассчитайте среднее значение фактического результативного признака в ячейке I2 и найденное значение подставьте в формулу .

4. Сделайте вывод об адекватности линейной модели.

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

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

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

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

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

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

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

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