Лабораторная работа № 5. Часть 2. Регрессионный анализ.(Практическая часть).
Пример 5.2. Провести регрессионный анализ влияния содержания Pb на содержание Au (табл. 5.1), построив уравнения линейной и квадратичной корреляции, проверив их значимость и сравнить их эффективность.
Решение. Скопируем данные на новый лист Excel. Выделим массив E2:F6 и введем формулу
=ЛИНЕЙН(C2:C55;B2:B55;1;1), завершив ввод нажатием CTRL+SHIFT+ENTER.
В ячейке Е4 содержится коэффициент детерминации. Его значение крайне низко, что не позволяет использовать полученное уравнение регрессии для прогнозирования. Низкое значение F-статистики, в свою очередь, указывает на незначимость уравнения регрессии.
Построим уравнение квадратичной регрессии. Для этого в столбце А вычислим квадраты данных столбца В и в массиве E9:G13 применим функцию ЛИНЕЙН
Коэффициент детерминации повысился, хотя остался довольно низким, а значение F-статистики выросло довольно значительно. Проверим значимость полученного уравнения, вычислив критическое значение в ячейке Н12:
Как видим, значение F-статистики выше критического при уровне значимости 0,05, так что полученное уравнение
(Au)=-0,23126(Pb)+1,138285(Pb2)+0,848124
статистически значимо с доверительной вероятностью 0,95.
Решение задач корреляционно-регрессионного анализа полезно визуализировать, строя точечные диаграммы. Сделаем это для нашего примера: выделим два столбца исходных данных и выберем на вкладке «Вставка»: «график»-«все типы диаграмм»-«точечная»:
Как видим, поле точек в целом слишком широко и визуально не прослеживается даже криволинейная корреляция. Но также можно заметить, что оно как бы распадается на две части – более узкое поле для бедных по свинцу образцов (<2,5) явно даст значимую линейную корреляцию. В целях наиболее полного решения задачи следует поэтому выделить из всех данных выборку бедных по свинцу образцов и провести корреляционно-регрессионный анализ для нее.
Однако вернемся пока к построенной диаграмме. На ней Excel позволяет построить линию регрессии выбранного типа (линейную, полиномиальную, степенную, логарифмическую, экспоненциальную). Для этого следует подвести курсор к одной из точек поля, нажать левую клавишу мыши и выбрать опцию «добавить линию тренда». Можно даже при этом поместить на диаграмму коэффициент детерминации (аппроксимации) этой линии регрессии:
Построим на диаграмме обе линии регрессии (линейной и квадратичной):
Задание 1. Построить другие полиномиальные уравнения регрессии для примера 5.2. и выбрать наилучшее с точки зрения значимости и точности.
Задание 2. Выбрать данные бедных по свинцу руд и провести для них регрессионный анализ.
