Субботина Ю.А.
Практическая работа №12
"ПОСТРОЕНИЕ ВНУТРЕННЕ ЛИНЕЙНЫХ РЕГРЕССИОННЫХ МОДЕЛЕЙ С ИСПОЛЬЗОВАНИЕМ «ПАКЕТА АНАЛИЗА»"
Задание:
Экспериментальные данные (920 записей) находятся в файле base1.xls.
В работе используется разделение этих данных на обучающую и контрольную выборки, выполненное на предыдущем занятии (п.1).
Используя эти данные:
1. по обучающей выборке найти регрессионные коэффициенты и оценить адекватность, значимость факторов и стандартную ошибку следующих прогностических моделей прочности бетона P в зависимости от возраста Т:
a) In(p)=a0+a1*1/√T
b) T/P = a0+a1T
c) 1/P= a0+a11/T
2. Выбрать из этих моделей наилучшую и для нее рассчитать стандартную ошибку по контрольной выборке.
Этапы выполнения работы:
1. Переход от заданных моделей к линейным моделям y = a0+a1x путем замены переменных.
Для модели а) y=ln(P), x=1/√T. Для расчета этих величин использовать столбцы K и L: в 1-й строке задать заголовки столбцов, далее рассчитать значения x и y.
2. Использование программы Регрессия из пакета Анализ данных для расчета характеристик регрессионных моделей.
2.1. Переименовать лист Обуч в Обуч1. Перейти на лист Обуч1. Вызвать пакет программ Анализ данных (вкладка Данные-Анализ). Из списка доступных программ выбрать программу Регрессия. В открывшемся окне задать параметры регрессионной модели:
Входной интервал Y – отклик, данные из столбца L (значения y)
Входной интервал Х – фактор, данные из столбца K (значения х)
Метки – столбцы имеют заголовки
Параметры вывода – новый рабочий лист с именем Модель4
2.2. Перейти на лист Модель4. Изменить ширину столбцов так, чтобы были видны заголовки.
Выделить цветом характеристики регрессионной модели:
Синим – коэффициенты модели
Желтым – значение нормированного R-квадрат
2.3. Рассчитать значение стандартной ошибки для обучающей выборки.
Перейти на лист Обуч1. Для расчетных значений y использовать столбец M: в ячейке M1 задать заголовок столбца Расчет y, далее вычислить значения y, используя коэффициенты модели, полученные на листе Модель4. Формула в ячейке M2 такова:
=Модель4!$B$17+Модель4!$B$18*K2
Далее эту формулу скопировать в оставшуюся часть столбца M.
Для расчетных значений прочности Р использовать столбец N: в ячейке N1 задать заголовок столбца Расчет Р, далее вычислить значения Р по формуле:
P=ey
В ячейке N702 вычислить значение стандартной ошибки для обучающей выборки по формуле:
=КОРЕНЬ(СУММКВРАЗН(B2:B701;N2:N701)/700)
Таблица на листе Обуч1 будет выглядеть примерно так (строки с 4 по 218 скрыты):
2.4. Рассчитать значение стандартной ошибки для контрольной выборки.
Переименовать лист Контр в Контр1. Скопировать с листа Обуч1 на лист Контр1 заголовки и формулы для добавленных столбцов K-N. На листе Контр1 скопировать формулы на весь диапазон записей (до 221 строки включительно). В ячейке N222 вычислить значение стандартной ошибки для контрольной выборки по формуле
=КОРЕНЬ(СУММКВРАЗН(B2:B221;N2:N221)/220)
2.5. Выполнить п.1-2 для второй и третьей моделей.
Ниже представлены модели №4, №5, №6 соответственно:
Экспериментальные данные (920 записей) находятся в файле base1.xls.
В работе используется разделение этих данных на обучающую и контрольную выборки, выполненное на предыдущем занятии (п.1).
Используя эти данные:
1. по обучающей выборке найти регрессионные коэффициенты и оценить адекватность, значимость факторов и стандартную ошибку следующих прогностических моделей прочности бетона P в зависимости от возраста Т:
a) In(p)=a0+a1*1/√T
b) T/P = a0+a1T
c) 1/P= a0+a11/T
2. Выбрать из этих моделей наилучшую и для нее рассчитать стандартную ошибку по контрольной выборке.
Этапы выполнения работы:
1. Переход от заданных моделей к линейным моделям y = a0+a1x путем замены переменных.
Для модели а) y=ln(P), x=1/√T. Для расчета этих величин использовать столбцы K и L: в 1-й строке задать заголовки столбцов, далее рассчитать значения x и y.
2. Использование программы Регрессия из пакета Анализ данных для расчета характеристик регрессионных моделей.
2.1. Переименовать лист Обуч в Обуч1. Перейти на лист Обуч1. Вызвать пакет программ Анализ данных (вкладка Данные-Анализ). Из списка доступных программ выбрать программу Регрессия. В открывшемся окне задать параметры регрессионной модели:
Входной интервал Y – отклик, данные из столбца L (значения y)
Входной интервал Х – фактор, данные из столбца K (значения х)
Метки – столбцы имеют заголовки
Параметры вывода – новый рабочий лист с именем Модель4
2.2. Перейти на лист Модель4. Изменить ширину столбцов так, чтобы были видны заголовки.
Выделить цветом характеристики регрессионной модели:
Синим – коэффициенты модели
Желтым – значение нормированного R-квадрат
2.3. Рассчитать значение стандартной ошибки для обучающей выборки.
Перейти на лист Обуч1. Для расчетных значений y использовать столбец M: в ячейке M1 задать заголовок столбца Расчет y, далее вычислить значения y, используя коэффициенты модели, полученные на листе Модель4. Формула в ячейке M2 такова:
=Модель4!$B$17+Модель4!$B$18*K2
Далее эту формулу скопировать в оставшуюся часть столбца M.
Для расчетных значений прочности Р использовать столбец N: в ячейке N1 задать заголовок столбца Расчет Р, далее вычислить значения Р по формуле:
P=ey
В ячейке N702 вычислить значение стандартной ошибки для обучающей выборки по формуле:
=КОРЕНЬ(СУММКВРАЗН(B2:B701;N2:N701)/700)
Таблица на листе Обуч1 будет выглядеть примерно так (строки с 4 по 218 скрыты):
2.4. Рассчитать значение стандартной ошибки для контрольной выборки.
Переименовать лист Контр в Контр1. Скопировать с листа Обуч1 на лист Контр1 заголовки и формулы для добавленных столбцов K-N. На листе Контр1 скопировать формулы на весь диапазон записей (до 221 строки включительно). В ячейке N222 вычислить значение стандартной ошибки для контрольной выборки по формуле
=КОРЕНЬ(СУММКВРАЗН(B2:B221;N2:N221)/220)
2.5. Выполнить п.1-2 для второй и третьей моделей.
Ниже представлены модели №4, №5, №6 соответственно:
Вывод:
Теоретические основы
Предположим, что переменная y (отклик) зависит от нескольких переменных x1, …, xn (факторов) линейно. Многофакторная линейная регрессионная модель будет иметь вид
y=A0 + A1X1 + … +AnXn.
Коэффициенты этой модели могут быть определены с использованием метода наименьших
квадратов путем минимизации функции
Сумма(y - A0 - A1X1 - … - AnXn)^(2) --> min
Адекватность модели позволяет установить, насколько хорошо модель описывает
экспериментальные данные. Для проверки адекватности линейной регрессионной модели
используют
1)коэффициент детерминированности R^(2), который изменяется в пределах от 0 до 1. Если он равен 1, то выбранная модель абсолютно адекватна экспериментальным данным; если он равен 0, то никакой связи между экспериментом и выбранной моделью нет.
2) Критерий Фишера, основанный на сравнении расчетного значения
F=Sy^(2)/Sост^(2)
где Sy^(2) – дисперсия относительно среднего значения,
Sост^(2) – остаточная дисперсия с табличным значением Fтабл.
Проверка значимости регрессионных коэффициентов позволяет установить, влияет ли выбранный фактор на значения отклика. Для проверки используется критерий Стьюдента. Пакет анализа, наряду со значениями критерия выдает величину вероятности, с которой регрессионный коэффициент является значимым (P-значение). Если эта вероятность больше заданного значения(обычно 0,05), коэффициент считается значимым.
Стандартная ошибка регрессионной модели вычисляется по формуле
D=((1/n)*Сумма(y - A0 - A1X1 - … - AnXn)^(2))^(0,5)
Теоретические основы
Предположим, что переменная y (отклик) зависит от нескольких переменных x1, …, xn (факторов) линейно. Многофакторная линейная регрессионная модель будет иметь вид
y=A0 + A1X1 + … +AnXn.
Коэффициенты этой модели могут быть определены с использованием метода наименьших
квадратов путем минимизации функции
Сумма(y - A0 - A1X1 - … - AnXn)^(2) --> min
Адекватность модели позволяет установить, насколько хорошо модель описывает
экспериментальные данные. Для проверки адекватности линейной регрессионной модели
используют
1)коэффициент детерминированности R^(2), который изменяется в пределах от 0 до 1. Если он равен 1, то выбранная модель абсолютно адекватна экспериментальным данным; если он равен 0, то никакой связи между экспериментом и выбранной моделью нет.
2) Критерий Фишера, основанный на сравнении расчетного значения
F=Sy^(2)/Sост^(2)
где Sy^(2) – дисперсия относительно среднего значения,
Sост^(2) – остаточная дисперсия с табличным значением Fтабл.
Проверка значимости регрессионных коэффициентов позволяет установить, влияет ли выбранный фактор на значения отклика. Для проверки используется критерий Стьюдента. Пакет анализа, наряду со значениями критерия выдает величину вероятности, с которой регрессионный коэффициент является значимым (P-значение). Если эта вероятность больше заданного значения(обычно 0,05), коэффициент считается значимым.
Стандартная ошибка регрессионной модели вычисляется по формуле
D=((1/n)*Сумма(y - A0 - A1X1 - … - AnXn)^(2))^(0,5)
