Аппроксимация функции методом наименьших квадратов

Отметим, что  для аппроксимирующей функции, линейной относительно параметров, верно:

Относительная ошибка аппроксимации есть отношение  .

Величина 

                                                                                         (23)

называется коэффициентом  детерминированности и характеризует  меру точности аппроксимации табличных  данных. Если h² = 1, то ошибка аппроксимации равна 0 и теоретические значения совпадают с эмпирическими.

 

3. Расчет с помощью таблиц, выполненных  средствами Microsoft Excel

Для проведения расчетов, данные целесообразно  расположить в виде таблицы 3.1, используя  средства табличного процессора Microsoft Excel.

 

Таблица 3.1

 

Поясним, как  составляется Таблица 3.1:

Шаг 1. В ячейки A2:A28 заносим значения у_i.

Шаг 2. В ячейки B2:B28 заносим значения x_i.

Шаг 3. В ячейку C2 вводим формулу =B2^2.

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

Шаг 5. В ячейку D2 вводим формулу =B2*A2.

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

Шаг 7. В ячейку E2 вводим формулу =B2^3.

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

Шаг 9.В ячейку F2 вводим формулу =B2^4.

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

Шаг 11. В ячейку G2 вводим формулу =C2*A2.

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

Шаг 13. В ячейку H2 вводим формулу =LN(A2).

Шаг 14. В ячейки H3:H28 эта формула  копируется.

Шаг 15. В ячейку I2 вводим формулу =B2*LN(A2).

Шаг 16. В ячейки I3:I28 эта формула  копируется.

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

Шаг 17. В ячейку A28 вводим формулу =СУММ(A2:A28).

Шаг 18. В ячейку B28 вводим формулу =СУММ(B2:B28).

Шаг 19. В ячейку C28 вводим формулу =СУММ(C2:C28).

Шаг 20. В ячейку D28 вводим формулу =СУММ(D2:D28).

Шаг 21. В ячейку E28 вводим формулу =СУММ(E2:E28).

Шаг 22. В ячейку F28 вводим формулу =СУММ(F2:F28).

Шаг 23. В ячейку G28 вводим формулу =СУММ(G2:G28).

Шаг 24. В ячейку H28вводим формулу =СУММ(H2:H28).

Шаг 25. В ячейку I28 вводим формулу =СУММ(I2:I28).

 

Аппроксимируем функцию  линейной функцией . Для определения коэффициентов и воспользуемся системой (4). Используя итоговые суммы таблицы 3.1, расположенные в ячейках А29, В29, С29, D29 запишем систему (4) в виде

                                        (24)

 

решив которую, получим  и , т.е. линейная аппроксимация имеет вид

                                                                                      (25)

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

 

Таблица 3.2

Здесь в ячейках A34:B35 записана формула {=МОБР(A30:B31)}. Выделим область результата - ячейки E34:E35 и введем формулу: {=МУМНОЖ(A34:B35;C30:31)}. В ячейках E34:G35 получены параметры линейной аппроксимирующей функции.

Далее аппроксимирующую функцию (1) представим квадратичной функцией . Для определения коэффициентов воспользуемся системой (5). Используя итоговые суммы таблицы 3.1, расположенные в ячейках A29, B29, C29, D29, E29, F29, G29, запишем систему (5) в виде

                           (26)

 

Решив которую, получим  , и .

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

                                                                 (27)

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

Результаты представлены в таблице 3.3.

 

Таблица 3.3.

 

Здесь в ячейках А42:С44 записана формула {=МОБР(А37:С39)}.

В ячейках F42:F43 записана формула {=МУМНОЖ(А42:С44;D37:D49)}.

Теперь аппроксимируем функцию (1) экспоненциальной функцией . Для определения коэффициентов , и воспользуемся системой (10), используя итоговые суммы таблицы 3.1, расположенные в ячейках B29, С29, H29, I29, запишем систему (10) в виде:

                                                         (28)

решив которую, получим  , , .

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

                                                                                (29)

Решение системы (10) проводим, пользуясь  средствами Microsoft Excel. Результаты представлены в таблице 3.4.

Таблица 3.4

 

Здесь в ячейках А50:B51 записана формула {=МОБР(A46:B47)}.

В ячейках Е49:Е50 записана формула {=МУМНОЖ(A50:B51;C46:C47)}.

В ячейке Е51 записана формула =EXP(E49).

 

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

; .

Результаты расчета  и средствами Microsoft Excel представлены в таблице 3.5.

Таблица 3.5.

Таблица 3.5

 

Для того чтобы рассчитать коэффициент  корреляции и коэффициент детерминированности, данные целесообразно расположить в виде таблицы 4.1, которая является продолжением таблицы 3.1.

Таблица 4.1

 

Поясним, как таблица 4.1 составляется.

Шаг 1. В ячейку J2 вводим формулу =(B2-$B$53)*(A2-$B$54).

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

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

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

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

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

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

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

Шаг 9. В ячейку N2 вводим формулу

=(A2-($F$42+$F$43*B2+$F$44*B2^2))^2.

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

Шаг 11. В ячейку O2 вводим формулу =(A2-$E$51*EXP($E$50*B2))^2.

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

Шаг 13. В ячейку P2 вводим формулу =LN(A2).

Шаг 14. В ячейки P3:P28 эта формула  копируется.

Шаг 15. В ячейки J29 вводим формулу =СУММ(J2:J28)

Шаг 16. В ячейки K29 вводим формулу =СУММ(K2:K28)

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

Шаг 18. В ячейки M29 вводим формулу =СУММ(M2:M28)

Шаг 19. В ячейки N29 вводим формулу =СУММ(N2:N28)

Шаг 20. В ячейки O29 вводим формулу =СУММ(O2:O28)

Шаг 21. В ячейки P29 вводим формулу =СУММ(P2:P28)

Шаг 22. В ячейки P30 вводим формулу  =СУММ(P2:P28)/27

Шаг 23. В ячейку Q2 вводим формулу =(P2-$P$30)^2

Шаг 24. В ячейки Q3:Q28 эта формула  копируется.

Шаг 25. В ячейку R2 вводим формулу=(P2-LN($E$51*EXP($E$50*B2)))^2.

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

Шаг 27. В ячейки Q29 вводим формулу =СУММ(Q2:Q28)

Шаг 28. В ячейки R29 вводим формулу =СУММ(R2:R28)

 

Теперь проведем расчеты коэффициента корреляции по формуле () (только для  линейной аппроксимации) и коэффициента детерминированности по формуле (). Результаты расчетов средствами Microsoft Excel представлены в таблице 3.6.

Таблица 3.6

 

 

Здесь в ячейке B57 записана формула =J29/(K29*L29)^(1/2).

В ячейке B58 записана формула =1-M29/L29.

В ячейке B59 записана формула =1-N29/L29.

В ячейке B60 записана формула =1-O29/L29.

Из таблицы 3.6 видно, что коэффициент детерминированности квадратичной аппроксимации наиболее близок к 1. Таким образом анализ результатов расчетов показывает, что квадратичная аппроксимация наилучшим образом описывает экспериментальные данные.

4. Построение графиков

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

4.1. Построение прямой линии тренда

Строим график исходной эмпирической функции.

1. Выделяем диапазон A2:B28.
2. Вызовем Мастер диаграмм, нажав кнопку на панели инструментов и строим точечную диаграмму.
3. На вкладке Стандартные выберем тип диаграммы точечная.
4. На вкладке вид выберем .
5. Нажмем Далее.
6. Нажмем кнопку Ряд
7. В окне “значения X” введем =Лист1!$B$2:$B$28
8. В окне “значения Y” введем =Лист1!$А$2:$А$28
9. Нажмем Далее.

В окне “Название диаграммы” введем заголовок “Линейная аппроксимация”.

В окне ”Ось Х категорий” введем “х”

В окне “Ось Y значений” введем “y”.

Нажмем Готово.

Для построения линии тренда выполним следующие действия: выделяем на диаграмме ряд полученных точек и правой кнопкой мыши вызываем контекстное меню, выбираем команду – Добавить линию тренда. В диалоговом окне команды выбираем тип Линейная и параметры: Показывать уравнение на диаграмме и поместить на диаграмму величину достоверности аппроксимации (R^2).

 

Рис.1. График линейной аппроксимации

4.2. Построение квадратичной  линии тренда

Выполняется аналогично пункту 4.1, но на вкладке Тип выбираем тип Полиномиальная и степень аппроксимирующего  полинома равной 2.

Рис.2. График квадратичной аппроксимации.

4.3 Построение экспоненциальной  линии тренда

Выполняется аналогично пункту 1, но на вкладке Тип выбираем тип Экспоненциальная. Полученное при  построении линии тренда значение коэффициента детерминированности для экспоненциальной зависимости R² = 0,8913 (сосчитано ранее в ручную и представлено в таблице 4.2) не совпадает с истинным значением R² = 0,9111, т.к. при преобразовании экспериментальной зависимости в линейную форму рассчитывается линейный коэффициент корреляции между переменной x и lny. Вычисление уточненного коэффициента детерминированности для экспоненциальной зависимости приведено в таблице 4.1.

Рис. 3. График экспоненциальной аппроксимаци.

Таблица 4.1

 

В ячейке B61 введена формула =1-R29/Q29.

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

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

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

1. Выделим область А65:B69.
2. Вызовем Мастер функции.
3. Выберем функцию Линейн.
4. Определим аргументы функции
• В качестве изв_знач_у укажем A2:A28.
• В качестве изв_знач_х укажем B2:B28.
• Третье поле Константа оставим пустым.
• В четвертом поле стат наберем истина.
5. Нажмем кнопку ОК.
6. Установим курсор в строку формул.

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

В результате получим таблицу 4.2, где  в ячейках A65:B69 введена формула  {=ЛИНЕЙН(A2:A28;B2:B28;ИСТИНА;ИСТИНА)}.

Таблица 5.1

5.2. Оценка значимости линейной  аппроксимации

 

Оценку значимости коэффициента детерминированности R²_лин проводим по F-критерию Фишера, а оценку параметров a₁ и a₂ по t-критерию Стьюдента. Результаты представлены в таблице 5.2.

Таблица 5.2

 

В ячейке E65 введена формула: =L29/26;

В ячейке E66 введена формула: =(L29-M29)/1;

В ячейке E67 введена формула: =M29/25;

В ячейке G64 введено число 4,24 (определено по α=0,05 и df=25);

В ячейке G65 введена формула: =B58/(1-B58)*(27-2);

В ячейке G66 введена формула: =((E67*C29)/(27*K29))^(1/2);

В ячейке G67 введена формула: =(E67/K29)^(1/2)

В ячейке I65 введено число 2.0555 (определено по α=0,05 и df=26);

В ячейке I66 введена формула: =ABS(E34)/G66;

В ячейке I67 введена формула: =ABS(E35)/G67;

В итоге имеем следующие неравенства:

F_лин = 5157,33>F_табл = 4,24. Значит нулевая гипотеза Н₀: D_факт = D_ост справедлива, т.е. коэффициент детерминированности R²_лин значим.

= 5,40> = 2,0555. Значит нулевая гипотеза Н₀: а₁=0 отвергается, т.е. коэффициент а₁ значим.

= 71,81> = 2,0555. Значит нулевая гипотеза Н₀: а₂=0 отвергается, т.е. коэффициент а₂ значим.

5.3. Оценка значимости квадратичной  аппроксимации.

Для построения числовых характеристик  квадратичной аппроксимации необходимо создать табличную формулу, которая  будет содержать 5 строк и 3 столбца. Для этого выполним следующую  последовательность действий:

1. Выделим область А71:С75.
2. Вызовем Мастер функции.
3. Выберем функцию Линейн.
4. Определим аргументы функции
• В качестве изв_знач_у укажем A2:A28.
• В качестве изв_знач_х укажем B2:С28.
• Третье поле Константа оставим пустым.
• В четвертом поле стат наберем истина.
5. Нажмем кнопку ОК.
6. Установим курсор в строку формул.

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

В результате получим таблицу 5.3, где  в ячейках A71:С75 введена формула {=ЛИНЕЙН(A2:A28;B2:C28;ИСТИНА;ИСТИНА)}.

 

Таблица 5.3

Оценку значимости коэффициента детерминированности  R²_квадр проводим по F-критерию Фишера, а оценку параметров a₁ , a₂ и а₃ по t-критерию Стьюдента. Результаты представлены в таблице 5.4.

 

Таблица 5.4

В ячейке E72 введена формула: =L29/26;

В ячейке E73 введена формула: =(L29-N29)/2;

В ячейке E74 введена формула: =N29/24;

В ячейке E71 введено число 4,26 (определено по α=0,05 и df=24);