Метод најмали квадрати(LSM) се заснова на минимизирање на збирот на квадратни отстапувања на избраната функција од податоците што се испитуваат. Во оваа статија ќе ги приближиме достапните податоци користејќи линеарна функцијаy = а x + б .

Метод на најмал квадрат(Англиски) Обичен Најмалку Квадрати , О.Л.С.) е еден од основните методи на регресивна анализа во смисла на проценка на непознати параметри регресивни моделиспоред податоците од примерокот.

Да го разгледаме приближувањето по функции кои зависат само од една променлива:

• Линеарно: y=ax+b (оваа статија)
• : y=a*Ln(x)+b
• : y=a*x m
• : y=a*EXP(b*x)+с
• : y=ax 2 +bx+c

Забелешка: Во овој член се разгледуваат случаи на приближување со полином од 3 до 6 степен. Овде се разгледува приближувањето со тригонометриски полином.

Линеарна зависност

Ние сме заинтересирани за врската помеѓу 2 променливи XИ y. Постои претпоставка дека yзависи од Xспоред линеарниот закон y = секира + б. За да ги одреди параметрите на оваа врска, истражувачот направи набљудувања: за секоја вредност на x i, беше направено мерење на y i (види пример датотека). Според тоа, нека има 20 пара вредности (x i; y i).

Забелешка:Ако чекорот за промена е X е константна, потоа да се изгради расфрлаат парцелиможе да се користи, ако не, тогаш треба да го користите типот на графиконот Место .

Од дијаграмот е очигледно дека врската помеѓу променливите е блиску до линеарна. За да се разбере која од многуте прави линии „најправилно“ ја опишува врската помеѓу променливите, неопходно е да се одреди критериумот според кој линиите ќе се споредуваат.

Како таков критериум го користиме изразот:

Каде ŷ јас = а * x i + б ; n – број на парови вредности (во нашиот случај n=20)

Горенаведениот израз е збир на квадратните растојанија помеѓу набљудуваните вредности на y i и ŷ i и често се означува како SSE ( Збир на На квадрат Грешки (Остатоци), збир на квадратни грешки (остатоци)) .

Метод на најмал квадрате да се избере таква линија ŷ = секира + б, за што горенаведениот израз ја зема минималната вредност.

Забелешка:Секоја линија во дводимензионален простор е уникатно одредена од вредностите на 2 параметри: а (наклон) и б (смена).

Се верува дека колку е помал збирот на квадратни растојанија, толку подобро соодветната линија ги приближува достапните податоци и може дополнително да се користи за предвидување на вредностите на y од променливата x. Јасно е дека дури и ако во реалноста нема врска помеѓу променливите или врската е нелинеарна, тогаш OLS сепак ќе ја избере „најдобрата“ линија. Така, методот на најмали квадрати не кажува ништо за присуството на вистинска врска помеѓу променливите; методот едноставно ви овозможува да изберете такви параметри на функцијата а И б , за што горенаведениот израз е минимален.

Со извршување на не многу сложени математички операции (за повеќе детали, видете), можете да ги пресметате параметрите а И б :

Како што може да се види од формулата, параметарот а го претставува односот на коваријанса и затоа во MS EXCEL да се пресмета параметарот А Можете да ги користите следните формули (види Линеарен лист пример датотека):

= KOVAR(B26:B45;C26:C45)/ DISP.G(B26:B45)или

= КОВАРИЈАНЦА.B(B26:B45;C26:C45)/DISP.B(B26:B45)

Исто така да се пресмета параметарот А можете да ја користите формулата = TILT(C26:C45;B26:B45). За параметар б користете ја формулата = LEG(C26:C45;B26:B45) .

Конечно, функцијата LINEST() ви овозможува да ги пресметате двата параметри одеднаш. За да внесете формула LINEST(C26:C45;B26:B45)Треба да изберете 2 ќелии по ред и да кликнете CTRL + SHIFT + ENTER(види статија за). Вредноста ќе се врати во левата ќелија А , на десно - б .

Забелешка: За да избегнете мешање со влезот формули на низаќе треба дополнително да ја користите функцијата INDEX(). Формула = ИНДЕКС(LINEST(C26:C45,B26:B45),1)или само = LINEST(C26:C45;B26:B45)ќе го врати параметарот одговорен за наклонот на линијата, т.е. А . Формула = ИНДЕКС(LINEST(C26:C45,B26:B45),2)ќе го врати параметарот одговорен за пресекот на линијата со оската Y, т.е. б .

Откако ги пресметавме параметрите, дијаграм на расејувањеможете да ја нацртате соодветната линија.

Друг начин за исцртување права линија со користење на методот на најмали квадрати е алатката график Тренд линија. За да го направите ова, изберете го дијаграмот, изберете од менито Јазиче за распоред, В групна анализакликнете Тренд линија, тогаш Линеарна апроксимација .

Со штиклирање на полето „Прикажи ја равенката во дијаграм“ во полето за дијалог, можете да бидете сигурни дека параметрите пронајдени погоре се совпаѓаат со вредностите на дијаграмот.

Забелешка: За да се поклопат параметрите, типот на дијаграмот мора да биде . Поентата е дека кога се конструира дијаграм РаспоредВредностите на X-оската не можат да бидат специфицирани од корисникот (корисникот може да наведе само етикети кои не влијаат на локацијата на точките). Наместо X вредности, се користи низата 1; 2; 3; ... (за категориите за нумерирање). Затоа, ако се изгради линија на трендна типски дијаграм Распоред, тогаш наместо вистинските вредности на X ќе се користат вредностите на оваа низа, што ќе доведе до неточен резултат (освен, се разбира, реалните вредности X не се совпаѓаат со низата 1; 2; 3; ...).

Има многу намени бидејќи овозможува приближно претставување дадена функцијадруги се поедноставни. LSM може да биде исклучително корисен во обработката на набљудувањата и активно се користи за проценка на некои количини врз основа на резултатите од мерењата на други кои содржат случајни грешки. Во оваа статија, ќе научите како да ги имплементирате пресметките на најмалите квадрати во Excel.

Изјава за проблемот користејќи конкретен пример

Да претпоставиме дека постојат два индикатори X и Y. Покрај тоа, Y зависи од X. Бидејќи OLS нè интересира од гледна точка на регресивна анализа (во Excel неговите методи се имплементираат со помош на вградени функции), веднаш треба да преминеме на разгледување на специфичен проблем.

Значи, нека X е малопродажен простор на самопослуга, мерено во квадратни метри, а Y е годишниот промет, утврден во милиони рубли.

Потребно е да се направи прогноза каков промет (Y) ќе има продавницата доколку има овој или оној малопродажен простор. Очигледно, функцијата Y = f (X) се зголемува, бидејќи хипермаркетот продава повеќе стоки од штандот.

Неколку зборови за исправноста на првичните податоци што се користат за предвидување

Да речеме дека имаме табела изградена со помош на податоци за n продавници.

Според математичка статистика, резултатите ќе бидат повеќе или помалку точни доколку се испитаат податоци за најмалку 5-6 објекти. Покрај тоа, не може да се користат „аномални“ резултати. Конкретно, елитен мал бутик може да има промет кој е неколку пати поголем од прометот на големите малопродажни места од класата „масмаркет“.

Суштината на методот

Податоците од табелата може да се прикажат на Декартовска рамнина во форма на точки M 1 (x 1, y 1), ... M n (x n, y n). Сега решението на проблемот ќе се сведе на избор на приближна функција y = f (x), која има график што поминува што е можно поблиску до точките M 1, M 2, .. M n.

Се разбира, можете да користите полином висок степен, но оваа опција не само што е тешка за имплементација, туку и едноставно неточна, бидејќи нема да го одразува главниот тренд што треба да се открие. Најразумно решение е да се бара правата y = ax + b, што најдобро ги приближува експерименталните податоци, или поточно, коефициентите a и b.

Проценка на точноста

Со секое приближување, оценката на нејзината точност е од особено значење. Да ја означиме со e i разликата (отстапување) помеѓу функционалните и експерименталните вредности за точката x i, т.е. e i = y i - f (x i).

Очигледно, за да се процени точноста на приближувањето, можете да го користите збирот на отстапувањата, т.е., при изборот на права линија за приближно претставување на зависноста на X од Y, треба да му дадете предност на онаа со најмала вредност на сума e i во сите точки што се разгледуваат. Сепак, не е сè толку едноставно, бидејќи заедно со позитивните отстапувања ќе има и негативни.

Проблемот може да се реши со помош на модули за отстапување или нивните квадрати. Последниот метод е најшироко користен. Се користи во многу области вклучувајќи регресивна анализа(во Excel неговата имплементација се врши со користење на две вградени функции) и долго време ја докажа својата ефикасност.

Метод на најмал квадрат

Excel, како што знаете, има вградена функција AutoSum која ви овозможува да ги пресметате вредностите на сите вредности лоцирани во избраниот опсег. Така, ништо нема да не спречи да ја пресметаме вредноста на изразот (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

Во математичка нотација ова изгледа вака:

Бидејќи првично беше донесена одлука да се приближи користејќи права линија, имаме:

Така, задачата да се најде права линија која најдобро ја опишува специфичната зависност на величините X и Y се сведува на пресметување на минимумот на функција од две променливи:

За да го направите ова, треба да ги изедначите парцијалните деривати во однос на новите променливи a и b на нула и да решите примитивен систем кој се состои од две равенки со 2 непознати од формата:

По неколку едноставни трансформации, вклучувајќи делење со 2 и манипулација со збирови, добиваме:

Решавајќи го, на пример, користејќи го методот на Крамер, добиваме стационарна точка со одредени коефициенти a * и b *. Ова е минимумот, односно за да се предвиди каков промет ќе има продавница за одредена област, погодна е правата линија y = a * x + b *, што е регресивен модел за предметниот пример. Се разбира, тоа нема да ви дозволи да го најдете точниот резултат, но ќе ви помогне да добиете идеја дали ќе се исплати купувањето на одредена област со кредит во продавницата.

Како да се имплементираат најмали квадрати во Excel

Excel има функција за пресметување на вредности со користење на најмали квадрати. Ја има следната форма: „TREND“ (познати Y вредности; познати X вредности; нови X вредности; константа). Ајде да ја примениме формулата за пресметување на OLS во Excel на нашата табела.

За да го направите ова, внесете го знакот „=“ во ќелијата во која треба да се прикаже резултатот од пресметката со методот на најмали квадрати во Excel и изберете ја функцијата „TREND“. Во прозорецот што се отвора, пополнете ги соодветните полиња, означувајќи:

• опсег на познати вредности за Y (во во овој случајподатоци за трговскиот промет);
• опсег x 1, …x n, т.е. големината на малопродажниот простор;
• и познати и непознати вредности на x, за кои треба да ја дознаете големината на прометот (за информации за нивната локација на работниот лист, видете подолу).

Дополнително, формулата ја содржи логичката променлива „Const“. Ако внесете 1 во соодветното поле, тоа ќе значи дека треба да ги извршите пресметките, под претпоставка дека b = 0.

Ако треба да ја дознаете прогнозата за повеќе од една x вредност, тогаш откако ќе ја внесете формулата не треба да притиснете „Enter“, туку треба да ја напишете комбинацијата „Shift“ + „Control“ + „Enter“ на тастатурата.

Некои карактеристики

Анализата на регресија може да биде достапна дури и за кукли. Формулата Excel за предвидување на вредноста на низа непознати променливи - TREND - може да се користи дури и од оние кои никогаш не слушнале за најмали квадрати. Доволно е само да се знаат некои од карактеристиките на неговата работа. Особено:

• Ако го распоредите опсегот на познати вредности на променливата y во еден ред или колона, тогаш секој ред (колона) со познати вредности на x ќе биде перципиран од програмата како посебна променлива.
• Ако опсегот со познато x не е наведен во прозорецот TREND, тогаш кога се користи функцијата во Excel, програмата ќе го третира како низа составена од цели броеви, чиј број одговара на опсегот со дадените вредности на променлива y.
• За да излезе низа од „предвидени“ вредности, изразот за пресметување на трендот мора да се внесе како формула за низа.
• Ако новите вредности на x не се наведени, тогаш функцијата TREND ги смета за еднакви на познатите. Ако тие не се наведени, тогаш низата 1 се зема како аргумент; 2; 3; 4;…, што е пропорционално на опсегот со веќе дадени параметри y.
• Опсегот што ги содржи новите x вредности мора да има исти или повеќе редови или колони како опсегот што ги содржи дадените вредности y. Со други зборови, тој мора да биде пропорционален на независните променливи.
• Низа со познати x вредности може да содржи повеќе променливи. Меѓутоа, ако зборуваме само за еден, тогаш се бара опсезите со дадените вредности на x и y да бидат пропорционални. Во случај на неколку променливи, потребно е опсегот со дадените y вредности да се вклопи во една колона или еден ред.

Функција ПРЕДВИДУВАЊЕ

Имплементиран со користење на неколку функции. Еден од нив се нарекува „ПРЕДИКЦИЈА“. Слично е на „TREND“, т.е. го дава резултатот од пресметките користејќи го методот на најмали квадрати. Сепак, само за еден X, за кој вредноста на Y е непозната.

Сега знаете формули во Excel за кукли кои ви дозволуваат да ја предвидите идната вредност на одреден индикатор според линеарен тренд.

Метод на најмали квадрати (LSM)

Систем m линеарни равенкисо n непознати има форма:

Можни се три случаи: м n. Случајот кога m=n беше разгледан во претходните ставови. Кога м

Ако m>n и системот е конзистентен, тогаш матрицата А има најмалку m - n линеарно зависни редови. Овде решението може да се добие со избирање на n кои било линеарно независни равенки (ако постојат) и примена на формулата X = A -1 CV, односно сведувајќи го проблемот на претходно решен. Во овој случај, добиеното решение секогаш ќе ги задоволи преостанатите m - n равенки.

Меѓутоа, кога користите компјутер, попогодно е да се користи поопшт пристап - метод на најмали квадрати.

Метод на алгебарски најмали квадрати

Методот на алгебарски најмали квадрати е метод за решавање системи на линеарни равенки

со минимизирање на Евклидовата норма

Секира? б? >инф. (1.2)

Анализа на експериментални податоци

Да разгледаме некој експеримент во текот на кој во моменти од времето

На пример, се мери температурата Q(t). Резултатите од мерењето нека бидат специфицирани со низа

Да претпоставиме дека експерименталните услови се такви што мерењата се вршат со позната грешка. Во овие случаи, законот за промена на температурата Q(t) се бара со користење на одреден полином

P(t) = + + + ... +,

одредување на непознатите коефициенти, ..., од согледувањата дека вредноста Е(, ...,), дефинирана со еднаквоста

Гаусова алгебарска екселна апроксимација

ја зеде минималната вредност. Бидејќи збирот на квадрати е минимизиран, овој метод се нарекува приближување на најмалите квадрати до податоците.

Ако го замениме P(t) со неговиот израз, добиваме

Да ја поставиме задачата да дефинираме низа така што вредноста да биде минимална, т.е. Ајде да ја дефинираме низата користејќи го методот на најмали квадрати. За да го направите ова, ги изедначуваме парцијалните деривати на нула:

Ако ја внесете m × n матрицата A = (), i = 1, 2..., m; j = 1, 2, ..., n, каде

I = 1, 2..., m; j = 1, 2, ..., n,

тогаш писмената еднаквост ќе добие форма

Да ја преработиме напишаната еднаквост во однос на операциите со матрици. Според дефиницијата за множење матрица со колона, имаме

За транспонирана матрица, слична врска изгледа вака

Да ја воведеме ознаката: ќе ја означиме i-тата компонента на векторот Ax Во согласност со напишаните матрични еднаквости, ќе имаме

Во форма на матрица оваа еднаквост може да се препише како

A T x=A T B (1.3)

Овде A е правоаголна m×n матрица. Притоа, кај проблемите со приближување на податоците, по правило, m > n. Равенката (1.3) се нарекува нормална равенка.

Беше можно уште од самиот почеток, користејќи ја Евклидовата норма на вектори, да се напише проблемот во еквивалентна матрица:

Нашата цел е да ја минимизираме оваа функција во x. За да се постигне минимум во точка на решение, првите изводи во однос на x во оваа точка мора да бидат еднакви на нула. Дериватите на оваа функција се

2A T B + 2A T Ax

и затоа решението мора да го задоволува системот на линеарни равенки

(A T A)x = (A T B).

Овие равенки се нарекуваат нормални равенки. Ако A е m× n матрица, тогаш A>A - n × n е матрица, т.е. Матрицата на нормална равенка е секогаш квадратна симетрична матрица. Освен тоа, има својство на позитивна определеност во смисла дека (A>Ax, x) = (Ax, Ax) ? 0.

Коментар. Понекогаш решението на равенката од формата (1.3) се нарекува решение на системот Ax = B, каде што A е правоаголна m × n (m > n) матрица користејќи го методот на најмали квадрати.

Проблемот со најмалите квадрати може графички да се толкува како минимизирање на вертикалните растојанија од податочните точки до кривата на моделот (види Слика 1.1). Оваа идеја се заснова на претпоставката дека сите грешки во приближувањето одговараат на грешките во набљудувањата. Ако има и грешки во независните променливи, тогаш можеби е посоодветно да се минимизира Евклидовата оддалеченост од податоците до моделот.

MNC во Excel

Алгоритмот подолу за имплементација на OLS во Excel претпоставува дека сите првични податоци се веќе познати. Ги множиме двете страни на матричната равенка AЧX=B на системот лево со транспонираната матрица на системот А Т:

A T AX=A T B

Потоа ги множиме двете страни на равенката лево со матрицата (A T A) -1. Ако оваа матрица постои, тогаш системот е дефиниран. Со оглед на тоа

(A T A) -1 *(A T A)=E, добиваме

X=(A T A) -1 A T B.

Добиената матрична равенка е решение за систем од m линеарни равенки со n непознати за m>n.

Да ја разгледаме примената на горенаведениот алгоритам на конкретен пример.

Пример. Нека биде неопходно да се реши системот

Во Excel, листот со решение во режимот на прикажување на формула за овој проблем изгледа вака:

Резултати од пресметката:

Потребниот вектор X се наоѓа во опсегот E11:E12.

При решавање на даден систем на линеарни равенки, користени се следните функции:

1. МОБР - се враќа инверзна матрицаза матрица зачувана во низа.

Синтакса: MOBR (низа).

Низа е нумеричка низа со еднаков број на редови и колони.

2. MULTIPULT - го враќа производот на матриците (матриците се складираат во низи). Резултатот е низа со ист број на редови како низата1 и ист број на колони како низата2.

Синтакса: MULTIPLE (низа1, низа2).

Array1, array2 се множители низи.

Откако ќе внесете функција во горната лева ќелија на опсегот на низата, изберете ја низата, почнувајќи од ќелијата што ја содржи формулата, притиснете F2, а потоа притиснете CTRL+SHIFT+ENTER.

3. ТРАНСПОРТ - претвора вертикален сет на ќелии во хоризонтален, или обратно. Како резултат на користењето на оваа функција, се појавува низа со број на редови еднаков на бројот на колони од оригиналната низа, а бројот на колони еднаков на бројот на редови од почетната низа.

4.1. Користење на вградени функции

Пресметка коефициенти на регресијасе врши со користење на функцијата

ЛИНСТ(Вредности_y; x-вредности; Конст; статистика),

Вредности_y- низа од y вредности,

x-вредности- изборна низа на вредности x, ако низа Xе испуштено, се претпоставува дека ова е низа (1;2;3;...) со иста големина како Вредности_y,

Конст- булова вредност што покажува дали е потребна константата ббеше еднаква на 0. Ако Констго има значењето ВИСТИНАили испуштени, тогаш бсе пресметува на вообичаен начин. Доколку аргументот Консте ЛАЖЕН, тогаш бсе претпоставува дека е 0 и вредностите асе избираат така што релацијата е исполнета y=ax.

Статистикае булова вредност која покажува дали се бара да се вратат дополнителни статистики за регресија. Доколку аргументот Статистикаго има значењето ВИСТИНА, потоа функцијата ЛИНСТвраќа дополнителна статистика за регресија. Доколку аргументот Статистикаго има значењето ЛАГАили испуштена, потоа функцијата ЛИНСТго враќа само коефициентот аи постојана б.

Мора да се запомни дека резултатот од функциите LINEST()е збир на вредности - низа.

За пресметка коефициент на корелацијасе користи функција

КОРЕЛ(Низа 1;Низа 2),

враќање на вредностите на коефициентот на корелација, каде Низа 1- низа вредности y, Низа 2- низа вредности x. Низа 1И Низа 2мора да биде со иста големина.

ПРИМЕР 1. Зависност y(x) е претставено во табелата. Изградба регресивна линијаи пресметај коефициент на корелација.

Ајде да внесеме табела со вредности во лист MS Excel и да изградиме заговор за расејување. Работниот лист ќе ја има формата прикажана на сл. 2.

Со цел да се пресметаат вредностите на коефициентите на регресија АИ бизберете ги ќелиите A7: B7,Ајде да одиме на волшебникот за функции и во категоријата Статистичкиизберете функција ЛИНСТ. Ајде да го пополниме полето за дијалог што се појавува како што е прикажано на сл. 3 и притиснете добро.

Како резултат на тоа, пресметаната вредност ќе се појави само во ќелијата А6(сл. 4). За да може вредноста да се појави во ќелијата Б6треба да го внесете режимот за уредување (клуч F2), а потоа притиснете ја комбинацијата на копчиња CTRL+SHIFT+ENTER.

Да се ​​пресмета вредноста на коефициентот на корелација во ќелија C6беше воведена следната формула:

C7=CORREL(B3:J3;B2:J2).

Познавање на коефициентите на регресија АИ бда ги пресметаме вредностите на функциите y=секира+бза дадено x. За да го направите ова, ја воведуваме формулата

B5=$A$7*B2+$B$7

и копирајте го во опсегот C5: J5(сл. 5).

Ајде да ја нацртаме регресивната линија на дијаграмот. Изберете ги експерименталните точки на графикот, кликнете со десното копче и изберете ја командата Почетни податоци. Во полето за дијалог што се појавува (сл. 5), изберете го табот Реди кликнете на копчето Додадете. Ајде да ги пополниме полињата за внесување како што е прикажано на сл. 6 и притиснете го копчето добро. Ќе се додаде регресивна линија на графикот за експериментални податоци. Стандардно, неговиот график ќе биде нацртан како точки кои не се поврзани со линии за измазнување.

За да го промените изгледот на линијата за регресија, направете ги следните чекори. Десен-клик на точките што го прикажуваат линискиот график и изберете ја командата Тип на графикони поставете го типот на дијаграм за расејување, како што е прикажано на сл. 7.

Типот, бојата и дебелината на линијата може да се променат на следниов начин. Изберете линија на дијаграмот, кликнете со десното копче и изберете ја командата во контекстното мени Формат на серии на податоци...Следно, направете поставки, на пример, како што е прикажано на сл. 8.

Како резултат на сите трансформации, добиваме график на експериментални податоци и регресивна линија во една графичка област (сл. 9).

4.2. Користење на линија на тренд.

Конструкцијата на различни приближни зависности во MS Excel е имплементирана како својство на графикон - линија на тренд.

ПРИМЕР 2. Како резултат на експериментот, беше утврдена одредена зависност од табелата.

Изберете и конструирајте приближна зависност. Конструирајте графикони на табеларни и избрани аналитички зависности.

Решавањето на проблемот може да се подели на следните фази: внесување првични податоци, конструирање на дијаграм за расејување и додавање линија на тренд на овој график.

Да го разгледаме овој процес подетално. Да ги внесеме почетните податоци во работниот лист и да ги нацртаме експерименталните податоци. Следно, изберете ги експерименталните точки на графикот, кликнете со десното копче и користете ја командата Додадетел линија на тренд(сл. 10).

Дијалошкото поле што се појавува ви овозможува да изградите приближна врска.

Првиот таб (сл. 11) од овој прозорец го означува типот на приближна зависност.

На вториот (сл. 12) се одредуваат конструктивните параметри:

· име на приближната зависност;

· прогноза напред (наназад) од nединици (овој параметар одредува колку единици напред (наназад) треба да се прошири линијата на трендот);

дали да се прикаже точката на пресек на крива со права линија y=конст;

· прикажување на приближната функција на дијаграмот или не (опција за прикажување на равенката на дијаграмот);

· дали да се постави вредноста на стандардното отстапување на дијаграмот или не (опција за поставување на вредноста на веродостојноста на приближувањето на дијаграмот).

Дозволете ни да избереме полином од втор степен како приближна зависност (сл. 11) и да ја прикажеме равенката што го опишува овој полином на графикон (сл. 12). Резултирачкиот дијаграм е прикажан на сл. 13.

Слично со користење тренд линииможете да ги изберете параметрите на таквите зависности како

линеарна y=a∙x+б,

логаритамски y=а∙ во(x)+б,

· експоненцијална y=a∙e b,

· седативно y=a∙x b,

полином y=a∙x 2 +b∙x+в, y=a∙x 3 +b∙x 2 +c∙x+dи така натаму, до полином од 6-ти степен заклучно,

· линеарна филтрација.

4.3. Користење на алатката за анализа на опции: наоѓање решение.

Од значителен интерес е имплементацијата во MS Excel за избор на параметри на функционална врска со користење на методот на најмали квадрати со помош на алатката за анализа на опции: Барај решение. Оваа техника ви овозможува да ги изберете параметрите на функцијата од кој било тип. Да ја разгледаме оваа можност користејќи го следниов проблем како пример.

ПРИМЕР 3. Како резултат на експериментот, добиена е зависноста z(t), претставена во табелата

Изберете коефициенти на зависност Z(t)=На 4 +Bt 3 +Ct 2 +Dt+Kметод на најмали квадрати.

Овој проблем е еквивалентен на проблемот со наоѓање на минимум на функција од пет променливи

Да го разгледаме процесот на решавање на проблемот со оптимизација (сл. 14).

Нека вредностите А, ВО, СО, ДИ ДОскладирани во клетките А7: Е7. Да ги пресметаме теоретските вредности на функцијата З(т)=На 4 +Bt 3 +Ct 2 +Dt+Kза дадено т(Б2: Ј2). За да го направите ова, во ќелијата Б4внесете ја вредноста на функцијата во првата точка (ќелија Б2):

B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7.

Ајде да ја копираме оваа формула во опсегот C4: J4и да се добие очекуваната вредност на функцијата во точките чии апсциси се зачувани во ќелиите Б2: Ј2.

Во ќелијата Б5Ајде да воведеме формула која го пресметува квадратот на разликата помеѓу експерименталните и пресметаните точки:

B5=(B4-B3)^2,

и копирајте го во опсегот C5: J5. Во ќелија F7ќе ја складираме вкупната квадратна грешка (10). За да го направите ова, внесете ја формулата:

F7 = SUM(B5:J5).

Ајде да ја користиме командата Service®Побарајте решениеи решете го проблемот со оптимизација без ограничувања. Ајде соодветно да ги пополниме полињата за внесување во дијалог прозорецот прикажан на сл. 14 и притиснете го копчето Изврши. Ако се најде решение, прозорецот прикажан на сл. 15.

Резултатот од блокот за одлучување ќе биде излез во ќелиите А7: Е7вредности на параметритефункции З(т)=На 4 +Bt 3 +Ct 2 +Dt+K. Во клетките Б4: Ј4добиваме очекуваната вредност на функцијатана почетните точки. Во ќелија F7ќе се складира вкупна квадратна грешка.

Може да прикажете експериментални точки и вградена линија во една графичка област со избирање опсег Б2: Ј4, јавете се Волшебник за графикони, а потоа форматирајте го изгледот на добиените графикони.

Ориз. 17 го прикажува работниот лист MS Excel откако ќе се извршат пресметките.