Процесс решения поставленной задачи с помощью табличного процессора Microsoft Excel

 

Функция y = f (x) задана таблицей 1:

Таблица 1.

 

аргумент	функция	аргумент	функция	аргумент	функция	аргумент	функция	аргумент	функция
0.76	4.67	7.12	48.54	11.54	79.98	16.75	112.34	21.45	149.43
1.23	8.56	7.97	55.89	12.23	85.91	17.45	119.05	22.23	154.45
3.84	25.78	8.55	57.76	13.86	91.25	18.81	125.87	23.45	161.54
5.43	38.65	9.65	64.86	14.53	100.56	19.64	135.45	24.67	168.54
5.94	40.76	10.78	71.45	15.48	105.43	20.35	140.76	25.78	180.65

 

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

 

Решение:

Поскольку в данном примере каждая пара значений ( ) встречается один раз, то корреляционная таблица примет вид единичной матрицы. Значит, условные средние значения совпадают со значениями . Отсюда следует, что корреляционное отношение равно 1 и, следовательно, между y и x существует функциональная зависимость.

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

Таблица 2.

 

 

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

Шаг 1. В ячейки A1:A25 заносим значения .

Шаг 2. В ячейки B1:B25 заносим значения .

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

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

Шаг 5. В ячейку D1 вводим формулу =A1*B1.

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

Шаг 7. В ячейку F1 вводим формулу =A1^4.

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

Шаг 9. В ячейку G1 вводим формулу =A1^2*B1.

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

Шаг 11. В ячейку H1 вводим формулу =LN(B1).

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

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

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

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

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

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

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

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

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

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

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

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

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

 

Аппроксимируем функцию y = f (x) линейной функцией . Для определения коэффициентов и воспользуемся системой (4). Используя итоговые суммы таблицы 2, расположенные в ячейках A26, B26, C26 и D26, запишем систему (4) в виде

(11)

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

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

(12)

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

Таблица 3.

 

 

В ячейках A34:B35 записана формула {=МОБР(A30:B31)}.

В E34:E35 записана формула {=МУМНОЖ(A34:B35,C30:C31)}.

 

Далее аппроксимируем функцию y = f(x) квадратичной функцией . Для определения коэффициентов воспользуемся системой (5). Используя итоговые суммы таблицы 2, расположенные в ячейках A26, B26, C26, D26, E26, F26 и G26 запишем систему (5) в виде

 

(13)

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

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

(14)

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

Таблица 4.

 

 

В ячейках A43:C45 записана формула {=МОБР(A38:C40)}.

В F43:F45 записана формула {=МУМНОЖ(A43:C45,D38:D40)}.

 

Теперь аппроксимируем функцию y = f(x) экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и, используя итоговые суммы таблицы 2, расположенные в ячейках A26, C26, H26 и I26 получим систему

(15)

где .

Решив систему (10) найдем и .

После потенцирования получим

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

(16)

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

Таблица 5.

 

В ячейках A52:B53 записана формула {=МОБР(A48:B49)}.

В E51:E53 записана формула {=МУМНОЖ(A52:B53,C48:C49)}.

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

 

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

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

Таблица 6.

 

 

В ячейке B56 записана формула =A26/25.

В ячейке B57 записана формула =B26/25.

 

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

Таблица 7.

 

 

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

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

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

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

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

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

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

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

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

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

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

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

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

Шаг 11. В ячейку O1 ввели формулу =($E$53*EXP($E$52*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).

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

Таблица 8.

 

 

В таблице 8 в ячейке B59 записана формула =J26/(K26*L26)^(1/2).

В ячейке B61 записана формула =1- M26/L26.

В ячейке B63 записана формула =1- N26/L26.

В ячейке B65 записана формула =1- O26/L26.

 

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