Й способ (графический). Построение линии тренда

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

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

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

Линия тренда – графическое представление направления изменения ряда данных.

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

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

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

Рис.6.3.

 

3-й способ. Инструмент анализа Регрессия.

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

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

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

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

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

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

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

 

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

 

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

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

 

Среди полученных результатов после применения инструмента Регрессия есть столбец «Коэффициенты», содержащий значение b в строке «Y-пересечение», а – в строке «Переменная Х1».

Сравните полученные результаты с ранее рассчитанными коэффициентами a и b.

Обратите также внимание на следующие показатели:

Столбец df - число степеней свободы (используется при проверке адекватности модели по статистическим таблицам):

-в строке Регрессия находится – количество коэффициентов уравнения, не считая свободного члена b;

-в строке Остаток находится =n- -1, где n – количество исходных данных.

Столбец SS (сумма квадратов):

в строке Регрессия: SS = , где - модельные значения Y, полученные путем подстановки значений Х в построенную модель; - среднее значение Y;

в строке Остаток: .

 

Столбец MS - вспомогательные величины:

в строке Регрессия: ;

в строке Остаток: .

 

Столбец F - критерий Фишера. Используется для проверки адекватности модели:

.

Столбец Значимость F - оценка адекватности построенной модели. Находится по значениям F, и с помощью функции FРАСП. Если Значимость F меньше 0,05, то модель может считаться адекватной с вероятностью 0,95.

Стандартная ошибка, t-статистика - это вспомогательные величины, используемые для проверки значимости коэффициентов модели.

Р - величина - оценка значимости коэффициентов модели. Если Р - величина меньше 0,05, то с вероятностью 0,95 можно считать, что соответствующий коэффициент модели значим (т.е. его нельзя считать равным нулю и Y значимо зависит от соответствующего Х).

Нижние и верхние 95% - доверительные интервалы для коэффициентов модели.

 

Задание 3. Выбор наиболее точной модели связи.

Условие задачи. Исследуется зависимость дозы облучения от толщины слоя защитного материала. Имеются результаты 10 экспериментов (см. рис.8.6).

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

Y=A₀ + A₁*X (линейная модель);

Y=A₀* (степенная модель);

Y=A₀+A₁/X (гиперболическая модель).

Выберите наиболее точную модель и определите ее коэффициенты.

Рис.6.6 Исходные данные

 

Создайте на новом листе таблицу согласно рис. 6.6.

Постройте на этом же листе точечную диаграмму зависимости Y=f(X).

Нанесите на нее линейный и степенной тренды с уравнениями и величиной достоверности аппроксимации ().

Для построения гиперболической модели преобразуйте модель в линейную, получив в ячейках С2:С11 величину 1/Х. А в ячейку С1 введите заглавие: «Величина U=1/X».

Используя функцию ЛИНЕЙН, получите в ячейках А14:В14 коэффициенты уравнения m1 и b (т.е. уравнение Y= b+m1*U).

В ячейку A16 введите заголовок «Гиперболическая модель». В ячейку A17 введите уравнение Y= b+m*x (вместо b и m укажите конкретные числа).

Для построенной гиперболической модели найдите величину достоверности аппроксимации. Для этого найдите сначала среднее значение c помощью функции СРЗНАЧ в ячейке D2. В ячейку D1 введите заглавие «Ср. знач. Y».

В столбце E2:E11 получите модельные значения путем подстановки значений U из блока ячеек С2:С11 в построенную модель. Для этого в ячейку E2 введите формулу =$B$14+$A$14*C2. Скопируйте формулу вниз в смежные ячейки. В ячейку E1 введите заголовок: «Модельные значения Y».

Найдите сумму квадратов , скорректированную на среднее: . Для этого в столбце F2:F11 получите разность . В ячейку F1 введите заголовок: «Yi-Ycp.».

В столбце G2:G11 получите квадраты разностей, а в ячейку G1 введите заголовок: «(».

В ячейке Н2 получите итоговую сумму, а в ячейку Н1 введите заголовок: «SSy».

Аналогичным образом найдите сумму квадратов прогнозируемых (модельных) значений, скорректированную на среднее . Для этого используйте столбцы I, J, K.

Найдите величину достоверности аппроксимации: в ячейке L2.

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

Копия экрана Задания 3. приведена на рис. 6.7.

Рис. 6.7. Расчеты гиперболической модели

 

Проверьте правильность вычислений, воспользовавшись инструментом анализа Регрессия.