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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Аппроксимируем функцию линейной функцией . Для определения коэффициентов и воспользуемся системой

Используя итоговые суммы таблицы 2, расположенные в ячейках A27, B27, C27 и D27, запишем систему в виде

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

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

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

Таблица 3

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

В таблице 3 в ячейках A37:B38 записана формула {=МОБР(A33:B34)}.

В ячейках D37:D38 записана формула {=МУМНОЖ(A37:B38;C33:C34)}.

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

Используя итоговые суммы таблицы 2,

расположенные в ячейках A27, B27, C27, D27, E27, F27 и G27 запишем систему в виде

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

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

.

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

Т
аблица 4

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

В таблице 4 в ячейках E38:G40 записана формула {=МОБР(E33:G35)}.

В ячейках I38:I40 записана формула {=МУМНОЖ(E38:G40;H33:H35)}.

Теперь аппроксимируем функцию экспоненциальной функцией . Для определения коэффициентов и прологарифмируем значения и используя итоговые суммы таблицы 2, расположенные в ячейках A27, C27, H27 и I27 получим систему

где .

Решив систему, найдем , .

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

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

.

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

Таблица 5

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

В таблице 5 в ячейках D45:E46 записана формула {=МОБР(D42:943)}.

В ячейках G45:G46 записана формула {=МУМНОЖ(D45:E46;F42:F43)}.

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

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

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

Таблица 6

В
ычисление средних значений X и Y.

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

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

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

Таблица 7

В
ычисление остаточных сумм.

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

Ячейки A2:A27 и B2:B27 уже заполнены (см. табл. 2).

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

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

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

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

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

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

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

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

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

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

=($I$38+$I$39*A2+$I$40*A2^2-B2)^2.

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

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

=($G$47*EXP($G$46*A2)-B2)^2.

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

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

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

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

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

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

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

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

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

(только для линейной аппроксимации)

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

Таблица 8

Р
езультаты расчета.

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

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

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

В ячейке D56 записана формула =1- O27/L27.

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

4. Построение графиков в Excel и использование функции ЛИНЕЙН.

Рассмотрим результаты эксперимента, приведенные в исследованном выше примере.

Исследуем характер зависимости в три этапа:

• Построим график зависимости.

• Построим линию тренда ( _{, ,} ).

• Получим числовые характеристики коэффициентов этого уравнения.

Рис.4.1. График зависимости y от x

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

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

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

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

Таблица 9

5. Программа на языке Pascal.

5

Аппроксимация функции y=f(x) линейной, квадратичной и экспоненциальной функциями

, , .

.1. Схема алгоритма.

Рис.5.1. Блок-схема

program Kramer;

uses CRT;

const

n=25;

type

TArrayXY = array[1..2,1..n] of real;

TArray = array[1..n] of real;

var

SumX,SumY,SumX2,SumXY,SumX3,SumX4,SumX2Y,SumLnY,SumXLnY: real;

OPRlin,OPRkvadr,OPRa1,OPRa2,OPRa3:real;

a1lin,a2lin,a1kvadr,a2kvadr,a3kvadr,a1exp,a2exp,cexp:real;

Xsr,Ysr,S1,S2,S3,Slin,Skvadr,Sexp:real;

Kkor,KdetLin,KdetKvadr,KdetExp:real;

i:byte;

const

ArrayXY:TArrayXY=((12.85,12.32,11.43,10.59,10.21,9.65,9.63,9.22,8.44,8.07,7.74,7.32,7.08,6.87,5.23,5.02,4.65,4.53,3.24,2.55,1.86,1.76,1.11,0.99,0.72) , (154.77

145.59,108.37,100.76,98.32,81.43,80.97,79.04,61.76,60.54,55.86,47.63,48.03,36.85,25.65,24.98,22.87,20.32,9.06,6.23,3.91,3.22,1.22,1.10,0.53));

begin

ClrScr;

SumX:=0.0;

SumY:=0.0;

SumXY:=0.0;

SumX2:=0.0;

SumX3:=0.0;

SumX4:=0.0;

SumX2Y:=0.0;

SumLnY:=0.0;

SumXLnY:=0.0;

{ Вычисление сумм x, y, x*y, x^2, x^3, x^4, (x^2)*y, Ln(y), x*Ln(y) }

for i:=1 to n do

begin

SumX:=SumX+ArrayXY[1,i];

SumY:=SumY+ArrayXY[2,i];

SumXY:=SumXY+ArrayXY[1,i]*ArrayXY[2,i];

SumX2:=SumX2+sqr(ArrayXY[1,i]);

SumX3:=SumX3+ArrayXY[1,i]*ArrayXY[1,i]*ArrayXY[1,i];

SumX4:=SumX4+sqr(ArrayXY[1,i])*sqr(ArrayXY[1,i]);

SumX2Y:=SumX2Y+sqr(ArrayXY[1,i])*ArrayXY[2,i];

SumLnY:=SumLnY+ln(ArrayXY[2,i]);

SumXLnY:=SumXLnY+ArrayXY[1,i]*ln(ArrayXY[2,i])

end;

{ Вычисление коэффициентов }

OPRlin:=0.0;

a1lin:=0.0;

a2lin:=0.0;

a1kvadr:=0.0;

OPRkvadr:=0.0;

a2kvadr:=0.0;

a2kvadr:=0.0;

a1exp:=0.0;

a2exp:=0.0;

OPRlin:=n*SumX2-SumX*SumX;

a1lin:=(SumX2*SumY-SumX*SumXY)/OPRlin;

a2lin:=(n*SumXY-SumX*SumY)/OPRlin;

OPRkvadr:=n*SumX2*SumX4+SumX*SumX3*SumX2+SumX2*SumX*SumX3- SumX2*SumX2*SumX2-n*SumX3*SumX3-SumX*SumX*SumX4;

a1kvadr:=(SumY*SumX2*SumX4+SumX*SumX2Y*SumX3+SumX2*SumXY*SumX3- SumX2*SumX2*SumX2Y-SumY*SumX3*SumX3-SumX*SumXY*SumX4)/OPRkvadr;

a2kvadr:=(n*SumXY*SumX4+SumY*SumX3*SumX2+SumX2*SumX*SumX2Y-SumX2*SumX2*SumXY-n*SumX3*SumX2Y-SumY*SumX*SumX4)/OPRkvadr;

a3kvadr:=(n*SumX2*SumX2Y+SumX*SumXY*SumX2+SumY*SumX*SumX3-SumY*SumX2*SumX2-n*SumXY*SumX3-SumX*SumX*SumX2Y)/OPrkvadr;

a2exp:=(n*SumXLnY-SumX*SumLnY)/OPRlin;

cexp:=(SumX2*SumLnY-SumX*SumXLnY)/OPRlin;

a1exp:=exp(cexp);

{ Вычисление средних арифметических x и y }

Xsr:=SumX/n;