Вывод: в среднем расчетные значения отклоняются от фактических на 5,75%

5. Построим табл. 3 для расчета дисперсий:

Рис. 10. Заголовок таблицы для расчета дисперсий

Скопируем слагаемые из формул (10), (11), (12) на все значения . Подсчитаем суммы элементов столбцов и разделим на объем выборки. Результаты приведены на рис. 11.

 

Рис. 11. Результаты расчета дисперсий

Рассчитаем коэффициент детерминации по формуле (14). Результат приведен на рис. 12.

Рис. 12. Результат расчета коэффициента детерминации

Для проверки значимости коэффициента детерминации вычислим наблюдаемое значение критерия F_факт по формуле (15). Критическое значение распределения Фишера найдем, используя Статистическую функцию FРАСПОБР, заполнение окон которой показано на рис. 13.

 

Рис. 13. Заполнение полей аргументов функции FРАСПОБР

Для сравнения наблюдаемого и критического значений используем Логическую функцию ЕСЛИ, заполнение полей аргументов которой приведено на рис. 14.

 

Рис. 14. Заполнение полей аргументов функции ЕСЛИ

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

Рис. 15. Результат проверки значимости

Вывод: коэффициент детерминации значимо отличается от нуля, и полученное уравнение регрессии может использоваться для объяснения изменения фактора y под влиянием изменения фактора x.

6. Проверим гипотезу о статистической значимости параметров регрессии. Сначала рассчитаем остаточную дисперсию по формуле (18) и стандартную ошибку регрессии . Результаты вычислений приведены на рис. 16.

 

Рис. 16. Результат вычисления остаточной дисперсии
и стандартной ошибки регрессии

Затем вычислим по формулам (17) стандартные ошибки коэффициентов регрессии. По формулам (16) рассчитаем наблюдаемые значения критерия.

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

 

Рис. 17. Заполнение полей диалогового окна функции СТЬЮДРАСПОБР

Для сравнения наблюдаемого и критического значений критерия используем Логическую функцию ЕСЛИ.

Границы доверительных интервалов для коэффициентов найдем по формулам (19). Результаты представлены на рис. 18.

 

Рис. 18. Проверка значимости и доверительные интервалы
для коэффициентов регрессии

Вывод: коэффициенты модели статистически значимы при уровне значимости 0, 05. Коэффициенты а и b, находясь в указанных границах, не принимают нулевых значений, т.е. значимо отличаются от нуля.

7. Для построения эмпирической линии регрессии воспользуемся диаграммой, построенной в пункте 1. Выделим точки, щелкнув по ним левой кнопкой мыши. Выделение обозначается светлыми маркерами. Нажав правую кнопку мыши, выведем контекстное меню (рис. 19). Выберем опцию Добавить линию тренда.

 

Рис. 19. Контекстное меню при выделении точек

В диалоговом окне Линия тренда на вкладке «Тип» выберем «Линейная» (рис. 20).

 

Рис. 20. Вкладка «Тип» диалогового окна «Линия тренда»

На вкладке Параметры выберем «Показывать уравнение на диаграмме» и «Поместить на диаграмме величину достоверности аппроксимации R²». На рис. 21 приведены эмпирическая линия регрессии, уравнение регрессии и коэффициент детерминации.

Рис. 21. Эмпирическая линия регрессии

8. По исходным данным табл. 1 построим три диаграммы, как в пункте 1, и, установив последовательно типы линий тренда «Степенная», «Экспоненциальная» и «Логарифмическая», выведем на графике урав-
нение регрессии и коэффициент детерминации так же, как в пункте 7 (рис. 22, 23, 24).

 

Рис. 22. Степенная регрессия

 

Рис. 23. Экспоненциальная регрессия

 

Рис. 24. Логарифмическая регрессия

 

 

9. Для проверки адекватности полученных моделей рассчитаем средние ошибки аппроксимации по формуле (13). Построим табл. 4, табл. 5 и табл. 6 для расчёта средней ошибки аппроксимации (рис. 25, 26, 27).

 

Рис. 25. Результаты расчета коэффициента аппроксимации
для степенной регрессии

Рис. 26. Результаты расчета коэффициента аппроксимации
для экспоненциальной регрессии

Рис. 27. Результаты расчета коэффициента аппроксимации
для логарифмической регрессии

10. Для того чтобы выбрать уравнение, наилучшим образом соответствующее данным наблюдений, составим табл. 7:

 

Рис. 28. Сводная таблица коэффициентов детерминации
и средних ошибок аппроксимации

Сравнивая коэффициенты детерминации с единицей и ошибки аппроксимации с нормой (не более 8 – 10%), в качестве лучшего уравнения выберем линейное уравнение, так как коэффициент детерминации для этого уравнения принимает наибольшее значение, а ошибка аппроксимации — наименьшее.