Расчет аппроксимаций в табличном процессоре Excel

Для проведения расчетов целесообразно воспользоваться табличным процессором Microsoft Excel (смотри приложения 1-3).

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

– в ячейки А1:А25 значения x_i;

– в ячейки В1:B25 значения y_i;

– в ячейки C1 вводим формулу =А1^2 и копируем ее до ячейки C25;

– в ячейки D1 водим формулу =А1^B1 и копируем ее до ячейки D25;

– в ячейки E1 вводим формулу =А1^3 и копируем ее до ячейки E25;

– в ячейки F1 вводим формулу =А1^4 и копируем ее до ячейки F25;

– в ячейки G1 вводим формулу =C1*B1 и копируем ее до ячейки G25;

– в ячейки H1 вводим формулу =ln(B1) и копируем ее до ячейки H25;

– в ячейки I1 вводим формулу =A1*H1 и копируем ее до ячейки I25;

После этого считаем суммы получившихся значений (с помощью автосуммы ), для этого в ячейку А26 вводим формулу = СУММ(А1:А25), после чего копируем ее до ячейки I26.

Аппроксимируем функцию линейной функцией .

Для этого, используя итоговые суммы, расположенные в ячейках А28, В28, C28, D26 и G26, запишем систему в виде:

,

(3.1)

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

В ячейках A33:B34 записываем исходную матрицу. В ячейки A37:B38 записываем обратную матрицу от истинной матрицы соответственно. Для этого выделим ячейки А37:В38 и введем формулу = МОБР(А33:В34) и нажимаем комбинацию клавиш Ctrl+Shift+Enter. Для нахождения коэффициента а₁ и а₂ в ячейки Е37:Е38 вводим формулу =МУМНОЖ(А37:В38;С33:С34).

Таким образом, линейная аппроксимация имеет вид . Решение системы проводили, пользуясь средствами Microsoft Excel.

Далее аппроксимируем функцию показательной функцией . Для этого, используя итоговые суммы расположенные в ячейках А28, C28, H28 и I28, запишем систему в виде:

,

(3.2)

решив которую по теореме Крамера, получим и Для нахождения коэффициента c в ячейку E46 вводим формулу =МУМНОЖ(А55:В56;С51:С52).

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

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

Используя итоговые суммы расположенные в ячейках А28, В28, С28, D28, E28, F28 и G28 запишем систему в виде:

(3.4)

решив которую, получим, a₁= 319,0273611, a₂= -76,93063389, a₃= 4,952521116.

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

.

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

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

Ячейки A1:A26 и B1:B26 уже заполнены (см. табл. 2).

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

Шаг 1. В ячейку J1 вводим формулу =(A1-$B$54)*(B1-$B$55).

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

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

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

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

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

Шаг 7. В ячейку M1 вводим формулу =($E$32+$E$33*A1-B1)^2.

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

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

=($F$41+$F$42*A1+$F$43*A1^2-B1)^2.

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

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

=($E$51*EXP($E$50*A1)-B1)^2.

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

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

Шаг 13. В ячейку J26 вводим формулу =СУММ(J1:J25).

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

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

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

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

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

Наименьшая сумма квадратов отклонений эмпирических значений функции от теоретических значений q = 6839,72 соответствует квадратичной аппроксимации .