Аппроксимация функции методом наименьших квадратов с помощью MathCAD и электронных таблиц Microsoft EXCEL

Коэффициент корреляции вычисляется  по формуле:

                        (8)

где  , , и - среднее арифметическое значение соответственно х и у.

Коэффициент корреляции между случайными величинами по абсолютной величине не превосходит 1. Чем ближе |р| к 1, тем теснее линейная связь между х и у.

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

Корреляционное отношение вычисляется  по формуле:

 

                                                    (9)

 

 

где n_i = ,  n_f = ,   а числитель характеризует рассеяние условных средних у, около безусловного среднего y .

Всегда . Равенство = 0 соответствует некоррелированным случайным величинам; = 1 тогда и только тогда, когда имеется точная функциональная связь междуy и x. В случае линейной зависимости y от x корреляционное отношение совпадает с квадратом коэффициента корреляции. Величина - ρ ² используется в качестве индикатора отклонения регрессии от линейной.

Корреляционное отношение является мерой корреляционной связи y с x в какой угодно форме, но не может дать представления о степени приближенности эмпирических данных к специальной форме. Чтобы выяснить насколько точно построенная кривая отражает эмпирические данные вводится еще одна характеристика — коэффициент детерминированности.

Для его описания рассмотрим следующие  величины. - полная сумма квадратов, где среднее значение .

Можно доказать следующее равенство 

Первое слагаемое равно S_{ост =} и называется остаточной суммой квадратов. Оно характеризует отклонение экспериментальных от теоритических.

Второе слагаемое равно S_{регр =}   и называется регрессионной суммой

 квадратов и оно характеризует  разброс данных.

Очевидно, что справедливо следующее  равенство S_{полн =} S_{ост +} S_регр.

Коэффициент детерминированности  определяется по формуле:

                                                                                                       (10)

 

Чем меньше остаточная сумма квадратов  по сравнению с общей суммой квадратов, тем больше значение коэффициента детерминированности r² , который показывает, насколько хорошо уравнение, полученное с помощью регрессионного анализа, объясняет взаимосвязи между переменными. Если он равен 1, то имеет место полная корреляция с моделью, т.е. нет различия между фактическим и оценочным значениями y. В противоположном случае, если коэффициент детерминированности равен 0, то уравнение регрессии неудачно для предсказания значений y

Коэффициент детерминированности  всегда не превосходит корреляционное отношение. В случае когда выполняется  равенство r² = то можно считать, что построенная эмпирическая формула наиболее точно отражает эмпирические данные.

3. Исходные данные

 

Вариант 12. Функция  задана  рисунком 1.

 

 

                                                            

 

                                                                       рис. 1.

 

 

4. Расчет аппроксимаций в Excel

Для проведения расчетов воспользуемся табличным процессором Microsoft Excel. И данные расположить как показано на рисунке 2.

 

 

 

Рис 2

 

Для этого заносим:

• в ячейки A6:A30 заносим значения x_i.
• в ячейки B6:B30 заносим значения у_i.
• в ячейку C6 вводим формулу =А6^{^}2.
• в ячейки C7:C30 эта формула копируется.
• в ячейку D6 вводим формулу =А6*В6.
• в ячейки D7:D30 эта формула копируется.
• в ячейку F6 вводим формулу =А6^4.
• в  ячейки F7:F30 эта формула копируется.
• в ячейку G6 вводим формулу =А6^2*В6.
• в ячейки G7:G30 эта формула копируется.
• в ячейку H6 вводим формулу =LN(B6).
• в ячейки H7:H30 эта формула копируется.
• в ячейку I6 вводим формулу =A6*LN(B6).
• в ячейки I7:I30 эта формула копируется.
• в ячейку А33 вводим формулу =СУММ(А6:А30).
• в ячейку B33 вводим формулу =СУММ(В6:В30).
• в ячейку C33 вводим формулу =СУММ(С6:С30).
• в ячейку D33 вводим формулу =СУММ(D6:D30).
• в ячейку E33 вводим формулу =СУММ(E6:E30).
• в ячейку F33 вводим формулу =СУММ(F6:F30).
• в ячейку G33 вводим формулу =СУММ(G6:G30).
• в ячейку H33 вводим формулу =СУММ(H6:H30).
• в ячейку I33 вводим формулу =СУММ(I6:I30).

Аппроксимируем функцию y = f (x) линейной функцией y = a₁ + a₂x . Для определения коэффициентов a₁ и a₂ воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A33, B33, C33 и D33, запишем систему (4) в виде

              (11) 
решив которую, получим  a₁ = -252,141  и a₂ = 122,7882

Таким образом, линейная аппроксимация  имеет вид y= -252,141  + 122,7882х (12)

Решение системы (11) проводили, пользуясь  средствами Microsoft Excel. Результаты  на рисунке 3:

     

Рис 3

В таблице в ячейках B38:C39 формула {=МОБР(A35:B36)}. В ячейках C41:C42 формула {=МУМНОЖ(B38:C39,C35:C36)}.

 

Далее аппроксимируем функцию y = f (x) квадратичной функцией y = a₁ + a₂x + a₃x². Для определения коэффициентов a₁, a₂ и a₃ воспользуемся системой (5). Используя итоговые суммы таблицы 2, расположенные в ячейках A33, B33, C33, D33, E33, F33 и G33 запишем систему (5) в виде:

                (13)

 

Решив которую, получим  a₁ = -32,53488, a₂ = -5,948689 и a₃ = 12,79611     (14)

Таким образом, квадратичная аппроксимация  имеет вид:

у = -32,53488 - 5,948689 х + 12,79611 х²  

Решение системы (13) проводили, пользуясь  средствами Microsoft Excel. Результаты на рисунке 4.

Рис.4

В таблице в ячейках A50:C52 формула {=МОБР(A45:C47)}. В ячейках F50:F52 формула {=МУМНОЖ(A50:C52,D45:D47)}.

 

Теперь аппроксимируем функцию y = f (х) экспоненциальной функцией y = a₁e^a2x. Для определения коэффициентов a₁ и a₂ прологарифмируем значения y_i и используя итоговые суммы таблицы 2, расположенные в ячейках A33, C33, H33 и I33 получим систему:

       (15)

 

где с = ln(a₁).

Решив систему (10) найдем с = 1,372 , a₂ = 0,67 .

После потенцирования получим a₁ = 3,943 .

Таким образом, экспоненциальная аппроксимация  имеет вид y = 3,943*e^0,67x

Решение системы (15) проводили, пользуясь  средствами Microsoft Excel. Результаты представлены  на рисунке 5.

 

                   

Рис. 5

В таблице в ячейках A57:B59 записана формула {=МОБР(A54:B55)}. В ячейках E57:E58 записана формула {=МУМНОЖ(A58:B59,С54:С55)}. В ячейке E59 записана формула =EXP(E57).

 

Вычислим среднее арифметическое x и у по формулам:

; 

Результаты расчета x и y средствами Microsoft Excel представлены на рисунке 6.

                                         

Рис. 6

В ячейке B61 записана формула    =A33/25. В ячейке B62 записана формула    =B33/25. Для того, чтобы рассчитать коэффициент корреляции и коэффициент детерминированности данные целесообразно расположить в виде таблице 2.

Рис.7

 

Поясним как таблица на рисунке 7 составляется.

Ячейки A6 :A33 и B6 :B33 уже заполнены (см. рис. 2).

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

• в ячейку J6 вводим формулу =(A6-$B$61)*(B6-$B$62).
• в ячейки J7:J30 эта формула копируется.
• в ячейку K6 вводим формулу =(А6-$В$61)^{^}2.
• в ячейки K7:K30 эта формула копируется.
• в ячейку L6 вводим формулу =(В1-$В$62)^2.
• в ячейки L7:L30 эта формула копируется.
• в ячейку M6 вводим формулу =($C$41+$Е$42*А6-В6)^2.
• в ячейки M7:M30 эта формула копируется.
• в ячейку N6 вводим формулу =($F$50 +$F$51*A6 +$F$52*A6 -В6)^2.
• в ячейки N7:N30 эта формула копируется.
• в ячейку O6 вводим формулу =($Е$59*ЕХР($Е$58*А6)-В6)^2.
• в ячейки O7:O30 эта формула копируется..
• в ячейку J33 вводим формулу =CУMM(J6:J30).
• в ячейку K33 вводим формулу =СУММ(К6:К30).
• в ячейку L33 вводим формулу =CУMM(L6:L30).
• в ячейку M33 вводим формулу =СУММ(М6:М30).
• в ячейку N33 вводим формулу =СУММ(N6:N30).
• в ячейку O33 вводим формулу =СУММ(06:030).

 

Теперь проведем расчеты коэффициента корреляции по формуле (8) (только для  линейной аппроксимации) и коэффициента детерминированности по формуле (10). Результаты расчетов средствами Microsoft Ехcеl представлены на рисунке 8.

Рис.8

В таблице  в ячейке B64аписана формула =J33/(K33*L33)^(1/2). В ячейке B65записана формула =1- M33/L33. В ячейке B66записана формула =1- N33/L33. В ячейке B67записана формула =1- O33/L33.

 

Получение числовых характеристик  линейной зависимости.

 

  _{Для построения  числовых характеристик  необходимо создать  табличную форму,  которая будет  занимать 5 строк  и 2 столбца. В  этот интервал  требуется ввести  функцию ЛИНЕЙН. Для  этого выполняем следующую последовательность действий:}

1. _{Выделим область А75:В79.}
2. _{Вызовем функцию ЛИНЕЙН.}
3. _{Определим аргументы функции}
• _{В качестве изв_знач_у укажем B6:B30.}
• _{В качестве изв_знач_х укажем A6:A30.}
• _{Третье поле Константа оставим пустым.}
• _{В четвертом поле стат наберем истина.}

 

      _{4. Нажмем кнопку ОК.}

 

      _{5. Установим курсор в строку формул.}

  _{Нажмем комбинацию  клавиш Ctrl+Shift+Enter, это обеспечит ввод табличной формулы.}

  _{В результате  должны заполниться  все ячейки интервала  А75:В79 (рис.9)}

 

                      

_Рис.9

                                                                                      

_{В ячейках А75:В79 введена формула {=ЛИНЕЙН(B6:B30;A6:A30;ИСТИНА;ИСТИНА)}.}

_{Пояснения к табл. 8 :}

_{А77- коэффициент детерминированности}

_{А78- F-наблюдаемое значение.}

_{В78- число степеней свободы.}

_{А79- факторная сумма квадратов.}

_{В79- остаточная сумма квадратов.}

 _{Рассмотрим назначение функции ЛИНЕЙН.}

 _{Эта функция использует метод наименьших квадратов, чтобы определить прямую линию, которая наилучшим образом аппроксимирует имеющиеся данные.}

 

Получение числовых характеристик  экспоненциальной зависимости.

1. _{Выделим область А88:В92.}
2. _{Вызовем Мастер функций.}
3. _{Выберем функцию ЛГРФПРИБЛ.}
4. _{Определим аргументы функции}
• _{В качестве изв_знач_у укажем B6:B30.}
• _{В качестве изв_знач_х укажем A6:A30.}
• _{Третье поле Константа оставим пустым.}
• _{В четвертом поле стат наберем истина.}
5. _{Нажмем кнопку ОК.}
6. _{Установим курсор в строку формулу.}