Коэффициент корреляции. Коэффициенты детерминированности

Вычислим качество полученных уравнений, т.е. найдем значение коэффициента корреляции и коэффициентов детерминированности (по формулам 12 и 11), используя средства MS Excel.

Рис. 7. Фрагмент рабочего листа в режиме отображения данных с результатами расчета коэффициента детерминированности

Пояснения к рис. 7:

Ячейки В2:В27 и С2:С27 уже заполнены (см. рис.2).

Далее делаем следующие шаги:

Шаг 1. В ячейку К2 вводим формулу =(B2-$B$56)*(C2-$B$57).

Шаг 2. В ячейки К3:К26 эта формула копируется.

Шаг 3. В ячейку L2 вводим формулу =(B2-$B$56)^2.

Шаг 4. В ячейки L3:L26 эта формула копируется.

Шаг 5. В ячейку М2 вводим формулу =(C2-$B$57)^2.

Шаг 6. В ячейки М3:М26 эта формула копируется.

Шаг 7. В ячейку N2 вводим формулу =($E$34+$E$35*B2-C2)^2.

Шаг 8. В ячейки N3:N26 эта формула копируется.

Шаг 9. В ячейку О2 вводим формулу =($F$43+$F$44*B2+$F$45*B2^2-C2)^2.

Шаг 10. В ячейки О3:О26 эта формула копируется.

Шаг 11. В ячейку Р2 вводим формулу =($E$53*EXP($E$52*B2)-C2)^2.

Шаг 12. В ячейки Р3:Р26 эта формула копируется.

Последующие шаги делаем с помощь автосуммирования Σ.

Шаг 13. В ячейку К27 вводим формулу =СУММ(К2:К26).

Шаг 14. В ячейку L27 вводим формулу =СУММ(L2:L26).

Шаг 15. В ячейку М27 вводим формулу =СУММ(М2:М26) / вычисляем Sполн

Шаг 16. В ячейку N27 вводим формулу =СУММ(N2:N26) /вычисляем Sостат/лин

Шаг 17. В ячейку О27 вводим формулу =СУММ(О2:О26) /вычисляем Sостат/квадр

Шаг 18. В ячейку Р27 вводим формулу =СУММ(Р2:Р26) /вычисляем Sостат/экпонен

Теперь проведем расчеты значении коэффициента корреляции и коэффициентов детерминированности (по формулам 12 и 11)

Рис. 8. Фрагмент рабочего листа в режиме отображения результатов и расчетов

На рис. 8. В ячейке Е60 записана формула =К27/(L27*M27)^1/2.

В ячейке Е62 записана формула =1-N27/M27.

В ячейке Е64 записана формула =1-О27/М27.

В ячейке Е66 записана формула =1-Р27/М27.

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

Линейная аппроксимация наилучшим образом описывает экспериментальные данные, так как имеет самый высокий коэффициент детерминированности (0,9960).

 

Графический способ в Excel

Представим графические результаты расчетов, полученные выше. Исследуем характер зависимости x и y в три этапа с помощью «Мастера диаграмм» в MS Excel:

1) Построить график зависимости

2) Построить линию тренда

3) Получить числовые характеристики коэффициентов уравнения.

1) Построение графика зависимости:

1. Выделяем интервал В2:В26.

2. Вызываем Мастер диаграмм, нажав соответствующую кнопку на панели инструментов.

3. Используя мышь, выделим область для встроенной диаграммы.

4. выбираем тип диаграммы – точечная.

5. Далее в окне «Название диаграммы» введем заголовок «Линейная аппроксимация»; в окне «Категория [Х]» введем «х», в окне «Значения [Y]» введем значения «y».

2) Построение линии тренда:

Для построения линии тренда выполним следующую последовательность действий:

1. Дважды щелкнем по диаграмме. Диаграмма активизируется.

2. Щелкнем по графику непосредственно в одну из изображенных точек, сам график активизируется его окраска измениться.

3. Вставляем линию тренда, воспользуемся меню Вставка/Линия тренда.

4. Выберем на вкладке «Тип» линейный тип и перейдем к вкладке «Параметры».

5. На вкладке “Параметры” потребуем показывать уравнение тренда на диаграмме и показывать значение . Нажмем кнопку ОК (рис. 9)[2].

Рис. 9. График зависимости линии тренда для линейной аппроксимации

Для построения квадратичной аппроксимации на четвертом шаге в диалоговом окне “Линия тренда” выберем на вкладке “Тип” полиномиальный тип степень 2. Результат представлен на рис.10.

Рис 10. График зависимости линии тренда для квадратичной аппроксимации

Для построения экспоненциальной аппроксимации на четвертом шаге в диалоговом окне “Линия тренда” выберем на вкладке “Тип” экспоненциальный тип. Результат представлен на рис.11.

Рис. 11. График зависимости линии тренда для экспоненциальной аппроксимации

Построение линий тренда в MS Excel показало совпадение значений величин, полученных на графиках, с величинами, рассчитанными по формулам.