Mažiausių kvadratų metodas (LSM) priklauso regresinės analizės sričiai. Jis turi daugybę programų, nes leidžia apytiksliai pateikti tam tikrą funkciją kitomis paprastesnėmis. LSM gali būti labai naudingas apdorojant stebėjimus, ir jis aktyviai naudojamas kai kuriems dydžiams įvertinti pagal kitų matavimų rezultatus, kuriuose yra atsitiktinių klaidų. Šiame straipsnyje sužinosite, kaip „Excel“ įdiegti mažiausiųjų kvadratų skaičiavimus.
Problemos išdėstymas konkrečiu pavyzdžiu
Tarkime, kad yra du rodikliai X ir Y. Be to, Y priklauso nuo X. Kadangi OLS mus domina regresinės analizės požiūriu (Excel jos metodai realizuojami naudojant įmontuotas funkcijas), turėtume nedelsiant tęsti apsvarstyti konkrečią problemą.
Taigi, tegul X yra bakalėjos parduotuvės pardavimo plotas, matuojamas kvadratiniais metrais, o Y yra metinė apyvarta, apibrėžta milijonais rublių.
Būtina numatyti, kokią apyvartą (Y) turės parduotuvė, jei joje bus vienokių ar kitokių prekybinių patalpų. Akivaizdu, kad funkcija Y = f (X) didėja, nes prekybos centre parduodama daugiau prekių nei kioske.
Keletas žodžių apie pradinių duomenų, naudojamų prognozavimui, teisingumą
Tarkime, kad turime lentelę su n parduotuvių duomenimis.
Matematinės statistikos duomenimis, rezultatai bus daugmaž teisingi, jei bus išnagrinėti bent 5-6 objektų duomenys. Be to, negalima naudoti „anomalių“ rezultatų. Visų pirma, elitinio mažo butiko apyvarta gali būti daug kartų didesnė nei didelių „masmarket“ klasės parduotuvių apyvarta.
Metodo esmė
Lentelės duomenys gali būti rodomi Dekarto plokštumoje kaip taškai M 1 (x 1, y 1), ... M n (x n, y n). Dabar uždavinio sprendimas bus sumažintas iki aproksimacinės funkcijos y = f (x) parinkimo, kurios grafikas eina kuo arčiau taškų M 1, M 2, .. M n .
Žinoma, galite naudoti aukšto laipsnio daugianarį, tačiau šią parinktį ne tik sunku įgyvendinti, bet ir tiesiog neteisinga, nes ji neatspindės pagrindinės tendencijos, kurią reikia aptikti. Paprasčiausias sprendimas yra ieškoti tiesės y = ax + b, kuri geriausiai aproksimuotų eksperimentinius duomenis, o tiksliau, koeficientus - a ir b.
Tikslumo balas
Bet kokiam aproksimavimui ypač svarbu įvertinti jo tikslumą. Pažymėkite e i skirtumą (nuokrypį) tarp taško x i funkcinių ir eksperimentinių verčių, ty e i = y i - f (x i).
Akivaizdu, kad norint įvertinti aproksimacijos tikslumą, galite naudoti nuokrypių sumą, t. suma e i visuose nagrinėjamuose taškuose. Tačiau ne viskas taip paprasta, nes kartu su teigiamais nukrypimais praktiškai atsiras ir neigiamų.
Problemą galite išspręsti naudodami nuokrypių modulius arba jų kvadratus. Pastarasis metodas yra plačiausiai naudojamas. Jis naudojamas daugelyje sričių, įskaitant regresinę analizę (programoje „Excel“ jos įgyvendinimas atliekamas naudojant dvi integruotas funkcijas), ir jau seniai įrodyta, kad yra veiksminga.
Mažiausio kvadrato metodas
Kaip žinote, „Excel“ yra įmontuota automatinio sumavimo funkcija, leidžianti apskaičiuoti visų reikšmių, esančių pasirinktame diapazone, reikšmes. Taigi niekas netrukdys mums apskaičiuoti išraiškos reikšmės (e 1 2 + e 2 2 + e 3 2 + ... e n 2).
Matematiniu žymėjimu tai atrodo taip:
Kadangi iš pradžių buvo nuspręsta apytiksliai naudoti tiesią liniją, turime: 
Taigi, užduotis rasti tiesę, kuri geriausiai apibūdina konkretų ryšį tarp X ir Y, prilygsta dviejų kintamųjų funkcijos minimumo apskaičiavimui:
Tam reikia prilyginti nuliui dalinių išvestinių naujų kintamųjų a ir b atžvilgiu ir išspręsti primityvią sistemą, susidedančią iš dviejų lygčių su 2 formos nežinomaisiais:
Po paprastų transformacijų, įskaitant padalijimą iš 2 ir manipuliavimą sumomis, gauname:
Ją išspręsdami, pavyzdžiui, Cramerio metodu, gauname stacionarų tašką su tam tikrais koeficientais a * ir b * . Tai yra minimumas, t.y., norint nuspėti, kokia bus parduotuvės apyvarta tam tikrame plote, tinka tiesė y = a * x + b *, kuri yra nagrinėjamo pavyzdžio regresijos modelis. Žinoma, tai neleis jums rasti tikslaus rezultato, tačiau tai padės susidaryti supratimą, ar apsipirkti kreditine parduotuve tam tikrai sričiai apsipirks.
Kaip įdiegti mažiausiųjų kvadratų metodą „Excel“.
„Excel“ turi funkciją, skirtą mažiausiųjų kvadratų vertei apskaičiuoti. Jis turi tokią formą: TREND (žinomos Y reikšmės; žinomos X reikšmės; naujos X reikšmės; konstanta). Taikykime formulę, skirtą OLS skaičiavimui programoje „Excel“, savo lentelei.
Norėdami tai padaryti, langelyje, kuriame turėtų būti rodomas „Excel“ skaičiavimo, naudojant mažiausiųjų kvadratų metodą, rezultatas, įveskite „=“ ženklą ir pasirinkite funkciją „TREND“. Atsidariusiame lange užpildykite atitinkamus laukus, pažymėdami:
- žinomų Y verčių diapazonas (šiuo atveju apyvartos duomenys);
- diapazonas x 1 , …x n , t. y. prekybos ploto dydis;
- ir žinomos bei nežinomos x reikšmės, kurioms reikia sužinoti apyvartos dydį (informaciją apie jų vietą darbalapyje rasite žemiau).
Be to, formulėje yra loginis kintamasis „Const“. Jei jį atitinkančiame lauke įvesite 1, tai reikš, kad reikia atlikti skaičiavimus, darant prielaidą, kad b \u003d 0.
Jei reikia žinoti prognozę daugiau nei vienai x reikšmei, tada įvedus formulę nereikėtų spausti „Enter“, o reikia įvesti kombinaciją „Shift“ + „Control“ + „Enter“ („Enter“). ) klaviatūroje.
Kai kurios funkcijos
Regresinė analizė gali būti prieinama net manekenams. Excel formule, skirta nuspėti nežinomų kintamųjų masyvo reikšmę – „TREND“ – gali naudoti net tie, kurie apie mažiausių kvadratų metodą nėra girdėję. Pakanka tik žinoti kai kurias jo darbo ypatybes. Visų pirma:
- Jei vienoje eilutėje ar stulpelyje išdėstysite žinomų kintamojo y reikšmių diapazoną, kiekviena eilutė (stulpelis) su žinomomis x reikšmėmis bus suvokiama kaip atskiras kintamasis.
- Jei diapazonas su žinomu x nenurodytas lange TREND, tada, naudojant funkciją Excel programoje, programa jį laikys masyvu, susidedančiu iš sveikųjų skaičių, kurių skaičius atitinka diapazoną su nurodytomis reikšmėmis. iš kintamojo y.
- Norint išvesti „numatytų“ reikšmių masyvą, tendencijos išraiška turi būti įvesta kaip masyvo formulė.
- Jei nenurodomos naujos x reikšmės, funkcija TREND laiko jas lygiomis žinomoms. Jei jie nenurodyti, 1 masyvas laikomas argumentu; 2; 3; 4;…, kuris yra proporcingas diapazonui su jau pateiktais parametrais y.
- Diapazonas, kuriame yra naujos x reikšmės, turi turėti tokias pačias ar daugiau eilučių arba stulpelių kaip ir diapazonas su nurodytomis y reikšmėmis. Kitaip tariant, jis turi būti proporcingas nepriklausomiems kintamiesiems.
- Masyve su žinomomis x reikšmėmis gali būti keli kintamieji. Tačiau, jei mes kalbame tik apie vieną, tada reikalaujama, kad diapazonai su nurodytomis x ir y reikšmėmis būtų proporcingi. Jei yra keli kintamieji, būtina, kad diapazonas su nurodytomis y reikšmėmis tilptų į vieną stulpelį arba vieną eilutę.
PROGNOZĖS funkcija
Regresinė analizė programoje Excel įgyvendinama naudojant kelias funkcijas. Vienas iš jų vadinasi „PROGNAVIMAS“. Jis panašus į TREND, ty pateikia skaičiavimų, naudojant mažiausių kvadratų metodą, rezultatą. Tačiau tik vienam X, kurio Y reikšmė nežinoma.
Dabar žinote „Excel“ formules, skirtas manekenams, kurios leidžia numatyti būsimo rodiklio reikšmę pagal tiesinę tendenciją.
kuris randa daugiausiai platus pritaikymasįvairiose mokslo srityse ir praktinė veikla. Tai gali būti fizika, chemija, biologija, ekonomika, sociologija, psichologija ir t. t. ir taip toliau. Likimo valia man dažnai tenka susidurti su ekonomika, todėl šiandien pasirūpinsiu jums bilietu į nuostabią šalį, vadinamą Ekonometrija=) ... Kaip tu to nenori?! Ten labai gerai – tereikia apsispręsti! …Tačiau tikriausiai tikrai norite išmokti spręsti problemas mažiausių kvadratų. O ypač stropūs skaitytojai išmoks juos išspręsti ne tik tiksliai, bet ir LABAI GREITAI ;-) Bet pirmiausia bendras problemos išdėstymas+ susijęs pavyzdys:
Tegul rodikliai tiriami kokioje nors dalykinėje srityje, kuri turi kiekybinę išraišką. Tuo pačiu yra pagrindo manyti, kad rodiklis priklauso nuo rodiklio. Ši prielaida gali būti ir mokslinė hipotezė, ir pagrįsta elementariu sveiku protu. Tačiau palikime mokslą nuošalyje ir tyrinėkime patrauklesnes sritis – būtent bakalėjos parduotuves. Pažymėti taip:
– maisto prekių parduotuvės prekybos plotas, kv.m,
- maisto prekių parduotuvės metinė apyvarta, milijonai rublių.
Visiškai aišku, ką daugiau ploto parduotuvę, tuo jos apyvarta daugeliu atvejų didesnė.
Tarkime, atlikę stebėjimus / eksperimentus / skaičiavimus / šokius su tamburinu, turime skaitinius duomenis: 
Su bakalėjos parduotuvėmis, manau, viskas aišku: - tai 1-os parduotuvės plotas, - jos metinė apyvarta, - 2-osios parduotuvės plotas, - jos metinė apyvarta ir t.t. Beje, prieiti prie įslaptintos medžiagos visai nebūtina – gana tikslų apyvartos įvertinimą galima gauti naudojant matematinė statistika. Tačiau nesiblaškykite, komercinio šnipinėjimo kursas jau mokamas =)
Lentelinius duomenis taip pat galima rašyti taškų forma ir pavaizduoti mums įprastu būdu. Dekarto sistema .
Atsakykime į svarbų klausimą: kiek balų reikia kokybiniam tyrimui?
Kuo didesnis, tuo geriau. Minimalus leistinas rinkinys susideda iš 5-6 balų. Be to, esant nedideliam duomenų kiekiui, „nenormalūs“ rezultatai neturėtų būti įtraukti į imtį. Taigi, pavyzdžiui, maža elitinė parduotuvė gali padėti daug daugiau nei „jų kolegos“ ir taip iškreipti bendrą modelį, kurį reikia rasti!
Jei tai gana paprasta, turime pasirinkti funkciją, tvarkaraštį kuri eina kuo arčiau taškų 
. Tokia funkcija vadinama apytikslis
(apytikslis - apytikslis) arba teorinė funkcija
. Paprastai tariant, čia iš karto atsiranda akivaizdus „pretendentas“ – aukšto laipsnio daugianario, kurio grafikas eina per VISUS taškus. Tačiau ši parinktis yra sudėtinga ir dažnai tiesiog neteisinga. (nes diagrama visą laiką „vėjo“ ir prastai atspindės pagrindinę tendenciją).
Taigi norima funkcija turi būti pakankamai paprasta ir tuo pačiu adekvačiai atspindėti priklausomybę. Kaip jau galima spėti, vienas iš būdų rasti tokias funkcijas vadinamas mažiausių kvadratų. Pirmiausia bendrai panagrinėkime jo esmę. Tegul kuri nors funkcija apytiksliai atitinka eksperimentinius duomenis: 
Kaip įvertinti šio aproksimavimo tikslumą? Taip pat apskaičiuokime skirtumus (nukrypimus) tarp eksperimentinių ir funkcinių verčių (mes studijuojame piešinį). Pirma mintis, kuri ateina į galvą, yra įvertinti, kokia suma yra didelė, tačiau problema ta, kad skirtumai gali būti neigiami. (pavyzdžiui, 
)
ir nukrypimai dėl tokio sumavimo panaikins vienas kitą. Todėl, kaip aproksimacijos tikslumo įvertinimą, ji siūlo paimti sumą moduliai nukrypimai:
arba sulankstyta forma: (staiga, kas nežino: yra sumos piktograma ir yra pagalbinis kintamasis - "skaitiklis", kuris užima reikšmes nuo 1 iki ).
Apytiksliai eksperimentinius taškus su įvairiomis funkcijomis gausime skirtingos reikšmės, ir akivaizdu, kad kai ši suma mažesnė, ta funkcija tikslesnė.
Toks metodas egzistuoja ir vadinamas mažiausio modulio metodas. Tačiau praktikoje jis tapo daug plačiau paplitęs. mažiausių kvadratų metodas, kuriame galimos neigiamos reikšmės pašalinamos ne pagal modulį, o padalijus nuokrypius kvadratu:
, po kurio pastangos nukreipiamos į tokios funkcijos parinkimą, kad kvadratinių nuokrypių suma 
buvo kuo mažesnis. Tiesą sakant, iš čia ir kilo metodo pavadinimas.
O dabar grįžtame prie kito svarbus punktas: kaip minėta aukščiau, pasirinkta funkcija turėtų būti gana paprasta, tačiau yra ir daug tokių funkcijų: linijinis , hiperbolinis, eksponentinis, logaritminis, kvadratinis ir tt Ir, žinoma, čia iš karto norėčiau „sumažinti veiklos sritį“. Kokią funkcijų klasę pasirinkti tyrimui? Primityvi, bet efektyvi technika:
- Lengviausias būdas traukti taškus 
brėžinyje ir išanalizuokite jų vietą. Jei jie linkę būti tiesia linija, tuomet turėtumėte ieškoti tiesios linijos lygtis 
su optimaliomis reikšmėmis ir . Kitaip tariant, užduotis yra rasti TOKIUS koeficientus – kad kvadratinių nuokrypių suma būtų mažiausia.
Jei taškai yra, pavyzdžiui, išilgai hiperbolė, tada aišku, kad tiesinė funkcija duos prastą aproksimaciją. Šiuo atveju mes ieškome „palankiausių“ hiperbolės lygties koeficientų 
- tie, kurie duoda mažiausią kvadratų sumą 
.
Dabar atkreipkite dėmesį, kad abiem atvejais kalbame apie dviejų kintamųjų funkcijos, kurio argumentai yra ieškojo priklausomybės parinkčių:
O iš esmės reikia išspręsti standartinę problemą – surasti mažiausiai dviejų kintamųjų funkcijos.
Prisiminkite mūsų pavyzdį: tarkime, kad „parduotuvės“ taškai paprastai yra tiesioje linijoje ir yra pagrindo manyti, kad yra tiesinė priklausomybė apyvartos iš prekybos zonos. Raskime TOKIUS koeficientus "a" ir "būti", kad kvadratinių nuokrypių suma 
buvo mažiausias. Viskas kaip įprasta – pirma I eilės daliniai vediniai. Pagal tiesiškumo taisyklė galite atskirti tiesiai po sumos piktograma: 
Jei norite šią informaciją panaudoti rašiniui ar kursiniam darbui, būsiu labai dėkingas už nuorodą šaltinių sąraše, tokių detalių skaičiavimų niekur nerasite: 
Kurkime standartinė sistema:
Kiekvieną lygtį sumažiname „dviem“ ir, be to, „išskaidome“ sumas: 
Pastaba
: savarankiškai analizuokite, kodėl „a“ ir „be“ galima išimti iš sumos piktogramos. Beje, formaliai tai galima padaryti su suma
Perrašykime sistemą „taikoma“ forma: 
po kurio pradedamas brėžti mūsų problemos sprendimo algoritmas:
Ar žinome taškų koordinates? Mes žinome. Sumos 
ar galime rasti? Lengvai. Mes sudarome paprasčiausią dviejų tiesinių lygčių su dviem nežinomaisiais sistema(„a“ ir „beh“). Mes išsprendžiame sistemą, pvz. Cramerio metodas, todėl susidaro stacionarus taškas . Tikrinama pakankama sąlyga ekstremumui, galime patikrinti, ar šiuo metu funkcija 
pasiekia tiksliai minimumas. Patikrinimas yra susijęs su papildomais skaičiavimais, todėl paliksime jį užkulisiuose. (jei reikia, trūkstamą kadrą galima peržiūrėti). Padarome galutinę išvadą:
Funkcija 
geriausias būdas (bent jau lyginant su bet kuria kita tiesine funkcija) priartina eksperimentinius taškus 
. Grubiai tariant, jo grafikas eina kuo arčiau šių taškų. Pagal tradiciją ekonometrija taip pat vadinama gauta aproksimacinė funkcija suporuota tiesinės regresijos lygtis
.
Nagrinėjama problema turi didelę praktinę reikšmę. Mūsų pavyzdyje – lygtis 
leidžia numatyti, kokia apyvarta ("yig") bus parduotuvėje su vienokia ar kitokia pardavimo ploto verte (viena ar kita "x" reikšmė). Taip, gauta prognozė bus tik prognozė, tačiau daugeliu atvejų ji pasirodys gana tiksli.
Išanalizuosiu tik vieną problemą su „tikraisiais“ skaičiais, nes joje nėra jokių sunkumų - visi skaičiavimai yra 7-8 klasių mokyklos programos lygiu. 95 procentais atvejų jūsų bus paprašyta rasti tiesiog tiesinę funkciją, tačiau pačioje straipsnio pabaigoje parodysiu, kad optimalios hiperbolės, eksponento ir kai kurių kitų funkcijų lygtis nėra sunkiau.
Tiesą sakant, belieka išdalinti žadėtas gėrybes – kad išmoktumėte tokius pavyzdžius išspręsti ne tik tiksliai, bet ir greitai. Atidžiai studijuojame standartą:
Užduotis
Ištyrus ryšį tarp dviejų rodiklių, gautos šios skaičių poros: 
Naudodami mažiausių kvadratų metodą, raskite tiesinę funkciją, kuri geriausiai atitinka empirinę funkciją (Patyręs) duomenis. Padarykite brėžinį, kuriame Dekarto stačiakampėje koordinačių sistemoje nubraižykite eksperimentinius taškus ir aproksimacinės funkcijos grafiką 
. Raskite kvadratinių nuokrypių tarp empirinių ir teorinių verčių sumą. Sužinokite, ar funkcija geresnė (pagal mažiausiųjų kvadratų metodą) apytiksliai eksperimentiniai taškai.
Atkreipkite dėmesį, kad „x“ reikšmės yra natūralios vertybės, ir tai turi būdingą prasmingą reikšmę, apie kurią pakalbėsiu šiek tiek vėliau; bet jie, žinoma, gali būti trupmeniniai. Be to, priklausomai nuo konkrečios užduoties turinio, „X“ ir „G“ reikšmės gali būti visiškai arba iš dalies neigiamos. Na, mes gavome „beveidę“ užduotį, ir mes ją pradedame sprendimas:
Kaip sistemos sprendimą randame optimalios funkcijos koeficientus: 
Siekiant kompaktiškesnio žymėjimo, kintamojo „skaitiklis“ galima praleisti, nes jau aišku, kad sumavimas atliekamas nuo 1 iki .
Patogiau reikiamas sumas apskaičiuoti lentelės forma: 
Skaičiavimai gali būti atliekami naudojant mikroskaičiuotuvą, tačiau daug geriau naudoti „Excel“ - tiek greičiau, tiek be klaidų; žiūrėkite trumpą vaizdo įrašą:
Taigi gauname štai ką sistema:
Čia galite padauginti antrą lygtį iš 3 ir iš 1-osios lygties atimkite 2-ąjį dėmenį. Bet tai yra sėkmė – praktikoje sistemos dažnai nėra padovanotos, ir tokiais atvejais tai gelbsti Cramerio metodas:
, todėl sistema turi unikalų sprendimą.
Patikrinkime. Suprantu, kad nenoriu, bet kam praleisti klaidas ten, kur jų tikrai negalima praleisti? Rastą sprendimą pakeiskite kiekvienos sistemos lygties kairėje pusėje:
Gaunamos tinkamos atitinkamų lygčių dalys, o tai reiškia, kad sistema išspręsta teisingai.
Taigi norima aproksimacinė funkcija: – nuo visos tiesinės funkcijos eksperimentinius duomenis geriausiai atitinka jis.
Skirtingai nei tiesiai
parduotuvės apyvartos priklausomybė nuo jos ploto, nustatyta priklausomybė yra atvirkščiai
(principas "kuo daugiau - tuo mažiau"), ir šį faktą iš karto atskleidžia neigiamas kampo koeficientas. Funkcija 
informuoja, kad padidėjus tam tikram rodikliui 1 vienetu, priklausomo rodiklio reikšmė mažėja vidutinis 0,65 vnt. Kaip sakoma, kuo didesnė grikių kaina, tuo mažiau parduodama.
Norėdami nubraižyti apytikslę funkciją, randame dvi jos reikšmes:
ir atlikite piešinį: 
Sukonstruota linija vadinama tendencijų linija
(būtent linijinė tendencijos linija, t. y. bendruoju atveju tendencija nebūtinai yra tiesi linija). Visi žino posakį „būti tendencijoje“, ir manau, kad šiam terminui papildomų komentarų nereikia.
Apskaičiuokite kvadratinių nuokrypių sumą 
tarp empirinių ir teorinių vertybių. Geometriškai tai yra „raudonųjų“ atkarpų ilgių kvadratų suma (iš kurių du tokie maži, kad net nesimatote).
Apibendrinkime skaičiavimus lentelėje: 
Jie vėl gali būti atliekami rankiniu būdu, tik tuo atveju, jei pateiksiu 1 punkto pavyzdį: 
bet daug efektyviau daryti jau žinomu būdu:
Pakartokime: kokia rezultato prasmė? Nuo visos tiesinės funkcijos funkcija 
eksponentas yra mažiausias, tai yra, jis yra geriausias aproksimacija savo šeimoje. Ir čia, beje, galutinis problemos klausimas neatsitiktinis: o jeigu siūloma eksponentinė funkcija 
ar bus geriau apytiksliai eksperimento taškus?
Raskime atitinkamą kvadratinių nuokrypių sumą – kad juos atskirčiau, pažymėsiu raide „epsilon“. Technika lygiai tokia pati: 
Ir dar kartą kiekvienam gaisro skaičiavimui 1 taškui: 
Programoje „Excel“ naudojame standartinę funkciją EXP (Sintaksę galite rasti „Excel“ žinyne).
Išvada: , todėl eksponentinė funkcija eksperimentinius taškus aproksimuoja blogiau nei tiesė 
.
Bet čia reikia pažymėti, kad „blogiau“ yra dar nereiškia, kas blogai. Dabar sukūriau šios eksponentinės funkcijos grafiką – ji taip pat praeina arti taškų 
- tiek, kad be analitinio tyrimo sunku pasakyti, kuri funkcija tikslesnė.
Tai užbaigia sprendimą, ir aš grįžtu prie ginčo gamtinių vertybių klausimo. Įvairiuose tyrimuose, kaip taisyklė, ekonominiai ar sociologiniai mėnesiai, metai ar kiti vienodi laiko intervalai numeruojami natūraliu „X“. Apsvarstykite, pavyzdžiui, tokią problemą.
4.1. Naudojant integruotas funkcijas
skaičiavimas regresijos koeficientai atliekama naudojant funkciją
LINIJA(Vertybės_y; Vertybės_x; Konst; statistika),
Vertybės_y- y verčių masyvas,
Vertybės_x- pasirenkamas reikšmių masyvas x jei masyvas X praleistas, daroma prielaida, kad tai yra tokio pat dydžio masyvas (1;2;3;...) Vertybės_y,
Konst- Būlio reikšmė, nurodanti, ar konstanta reikalinga b buvo lygus 0. Jei Konst turi prasmę TIESA arba praleistas, tada b apskaičiuojamas įprastu būdu. Jei argumentas Konst tada yra NETIESA b laikoma 0 ir reikšmės a yra parinkti taip, kad santykis y = kirvis.
Statistika- Būlio reikšmė, nurodanti, ar reikia pateikti papildomą regresijos statistiką. Jei argumentas Statistika turi prasmę TIESA, tada funkcija LINIJA grąžina papildomą regresijos statistiką. Jei argumentas Statistika turi prasmę NETEISINGA arba praleista, tada funkcija LINIJA grąžina tik koeficientą a ir nuolatinis b.
Reikia atsiminti, kad funkcijų rezultatas LINEST() yra reikšmių rinkinys – masyvas.
Skaičiavimui koreliacijos koeficientas funkcija naudojama
CORREL(Masyvas1;Masyvas2),
grąžinant koreliacijos koeficiento reikšmes, kur Masyvas1- vertybių masyvas y, Masyvas2- vertybių masyvas x. Masyvas1 ir Masyvas2 turi būti tokio pat dydžio.
1 PAVYZDYS. Priklausomybė y(x) pateikta lentelėje. Sukurti regresijos linija ir paskaičiuoti koreliacijos koeficientas.
| y | 0.5 | 1.5 | 2.5 | 3.5 | |||||
| x | 2.39 | 2.81 | 3.25 | 3.75 | 4.11 | 4.45 | 4.85 | 5.25 |
Įveskime reikšmių lentelę į MS Excel lapą ir sukurkime sklaidos diagramą. Darbalapis bus tokios formos, kaip parodyta pav. 2.
Norint apskaičiuoti regresijos koeficientų reikšmes a ir b pasirinkite ląsteles A7:B7, pereikime prie funkcijų vedlio ir kategorijoje Statistiniai pasirinkti funkciją LINIJA. Užpildykite pasirodžiusį dialogo langą, kaip parodyta pav. 3 ir paspauskite Gerai.
Dėl to apskaičiuota vertė bus rodoma tik langelyje A6(4 pav.). Kad reikšmė būtų rodoma langelyje B6 reikia įjungti redagavimo režimą (klavišas F2) ir tada paspauskite klavišų kombinaciją CTRL + SHIFT + ENTER.
Apskaičiuoti koreliacijos koeficiento reikšmę vienai ląstelei C6 buvo įvesta ši formulė:
C7=KORREL(B3:J3;B2:J2).
Žinodami regresijos koeficientus a ir b apskaičiuokite funkcijos reikšmes y=kirvis+b už duotą x. Norėdami tai padaryti, pristatome formulę
B5 = $ 7 * B2 + $ B $ 7
ir nukopijuokite jį į diapazoną С5:J5(5 pav.).
Nubraižykime regresijos tiesę diagramoje. Diagramoje pasirinkite eksperimentinius taškus, dešiniuoju pelės mygtuku spustelėkite ir pasirinkite komandą Pradiniai duomenys. Pasirodžiusiame dialogo lange (5 pav.) pasirinkite skirtuką Eilė ir spustelėkite mygtuką Papildyti. Užpildykite įvesties laukus, kaip parodyta pav. 6 ir paspauskite mygtuką Gerai. Prie eksperimentinių duomenų diagramos bus pridėta regresijos linija. Pagal numatytuosius nustatymus jo grafikas bus rodomas kaip taškai, nesujungti lyginančiomis linijomis.
Ryžiai. 6
Norėdami pakeisti regresijos linijos išvaizdą, atlikite šiuos veiksmus. Dešiniuoju pelės mygtuku spustelėkite taškus, vaizduojančius linijinę grafiką, pasirinkite komandą Diagramos tipas ir nustatykite sklaidos diagramos tipą, kaip parodyta Fig. 7.
Linijos tipą, spalvą ir storį galima keisti taip. Diagramoje pasirinkite eilutę, paspauskite dešinį pelės mygtuką ir kontekstiniame meniu pasirinkite komandą Duomenų serijos formatas… Tada atlikite nustatymus, pavyzdžiui, kaip parodyta pav. aštuoni.
Visų transformacijų rezultate gauname eksperimentinių duomenų grafiką ir regresijos tiesę vienoje grafinėje srityje (9 pav.).
4.2. Naudojant tendencijų liniją.
Įvairių apytikslių priklausomybių konstravimas MS Excel yra įgyvendintas kaip diagramos ypatybė - tendencijų linija.
2 PAVYZDYS. Atlikus eksperimentą, buvo nustatyta tam tikra lentelės priklausomybė.
| 0.15 | 0.16 | 0.17 | 0.18 | 0.19 | 0.20 |
| 4.4817 | 4.4930 | 5.4739 | 6.0496 | 6.6859 | 7.3891 |
Pasirinkite ir sukurkite apytikslę priklausomybę. Sukurkite lentelių ir pritaikytų analitinių priklausomybių grafikus.
Uždavinio sprendimą galima suskirstyti į šiuos etapus: pradinių duomenų įvedimas, sklaidos diagramos sudarymas ir tendencijos linijos pridėjimas prie šio grafiko.
Apsvarstykime šį procesą išsamiai. Suveskime pradinius duomenis į darbalapį ir pavaizduokime eksperimentinius duomenis. Tada diagramoje pasirinkite eksperimentinius taškus, dešiniuoju pelės mygtuku spustelėkite ir naudokite komandą Papildyti l tendencijų linija(10 pav.).
Pasirodžiusiame dialogo lange galite sukurti apytikslę priklausomybę.
Pirmajame šio lango skirtuke (11 pav.) nurodytas aproksimacinės priklausomybės tipas.
Antrasis (12 pav.) apibrėžia konstrukcijos parametrus:
aproksimacinės priklausomybės pavadinimas;
Prognozė pirmyn (atgal) įjungta n units (šis parametras nustato, kiek vienetų į priekį (atgal) reikia pratęsti tendencijos liniją);
ar rodyti kreivės susikirtimo su linija tašką y=konst;
ar diagramoje rodyti apytikslę funkciją, ar ne (parodykite lygtį diagramos parametre);
Ar dėti diagramoje standartinio nuokrypio reikšmę, ar ne (parametras, skirtas diagramoje pateikti aproksimacijos patikimumo reikšmę).
Parinkime antrojo laipsnio daugianarį kaip aproksimuojančią priklausomybę (11 pav.) ir išveskime grafike šį daugianarį apibūdinančią lygtį (12 pav.). Gauta diagrama parodyta fig. 13.
Panašiai ir su tendencijų linijos galite pasirinkti tokių priklausomybių parametrus kaip
linijinis y=a∙x+b,
logaritminis y=a ln(x)+b,
eksponentinis y=a∙eb,
galia y=a x b,
daugianario y=a∙x 2 +b∙x+c, y=a∙x 3 +b∙x 2 +c∙x+d ir taip toliau, iki 6-ojo laipsnio daugianario imtinai,
Linijinis filtravimas.
4.3. Nusprendėjo naudojimas
Labai įdomus yra parametrų parinkimo mažiausių kvadratų metodu, naudojant sprendimų bloką, įdiegimas MS Excel. Ši technika leidžia pasirinkti bet kokios rūšies funkcijos parametrus. Panagrinėkime šią galimybę šios problemos pavyzdžiu.
3 PAVYZDYS. Eksperimento rezultate lentelėje pateikta priklausomybė z(t).
| 0,66 | 0,9 | 1,17 | 1,47 | 1,7 | 1,74 | 2,08 | 2,63 | 3,12 |
| 38,9 | 68,8 | 64,4 | 66,5 | 64,95 | 59,36 | 82,6 | 90,63 | 113,5 |
Pasirinkite priklausomybės koeficientus Z(t) = Prie 4 +Bt 3 +Ct 2 +Dt+K mažiausių kvadratų metodu.
Ši problema yra lygiavertė penkių kintamųjų funkcijos minimumo suradimo problemai
Apsvarstykite optimizavimo uždavinio sprendimo procesą (14 pav.).
Tegul vertybės BET, AT, NUO, D ir Į saugomi ląstelėse A7:E7. Apskaičiuokite teorines funkcijos reikšmes Z(t)=At4+Bt3+Ct2+Dt+K už duotą t(B2:J2). Norėdami tai padaryti, ląstelėje B4įveskite funkcijos reikšmę pirmame taške (ląstelėje B2):
B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$7.
Nukopijuokite šią formulę į diapazoną С4:J4 ir gauti numatomą funkcijos reikšmę taškuose, kurių abscisės saugomos ląstelėse B2:J2.
Į ląstelę B5 pristatome formulę, kuri apskaičiuoja skirtumo tarp eksperimentinio ir apskaičiuoto taškų kvadratą:
B5=(B4-B3)^2,
ir nukopijuokite jį į diapazoną С5:J5. Ląstelėje F7 išsaugosime bendrą kvadratinę paklaidą (10). Norėdami tai padaryti, pateikiame formulę:
F7 = SUMMA(B5:J5).
Naudokime komandą Service®Ieškokite sprendimo ir be apribojimų išspręskite optimizavimo problemą. Dialogo lange, parodytame Fig., užpildykite atitinkamus įvesties laukus. 14 ir paspauskite mygtuką Bėk. Jei sprendimas randamas, langas, parodytas pav. penkiolika.
Sprendimo bloko rezultatas bus išvestis į ląsteles A7:E7parametrų reikšmės funkcijas Z(t)=At4+Bt3+Ct2+Dt+K. Ląstelėse B4:J4 mes gauname numatoma funkcijos vertė pradžios taškuose. Ląstelėje F7 bus saugomi bendra kvadrato paklaida.
Pasirinkę diapazoną, galite rodyti eksperimentinius taškus ir pritaikytą liniją toje pačioje grafinėje srityje B2:J4, skambinti Diagramos vedlys, tada suformatuokite gautų grafikų išvaizdą.
Ryžiai. 17, atlikus skaičiavimus, rodomas MS Excel darbalapis.
5. NUORODOS
1. Alekseev E.R., Chesnokova O.V., Skaičiavimo matematikos uždavinių sprendimas paketuose Mathcad12, MATLAB7, Maple9. – NT Spauda, 2006.–596s. :nesveikas. – (Pamoka)
2. Aleksejevas E.R., Česnokova O.V., E.A. Rudchenko, Scilab, sprendžiant inžinerines ir matematines problemas. –M., BINOM, 2008.–260 m.
3. I. S. Berezin ir N. P. Zhidkov, Methods of Computation, Maskva: Nauka, 1966 m.
4. Garnaev A.Yu., MS EXCEL ir VBA naudojimas ekonomikoje ir finansuose. - Sankt Peterburgas: BHV - Peterburgas, 1999.-332p.
5. B. P. Demidovičius, I. A. Maronas ir V. Z. Šuvalova, Skaitiniai analizės metodai.–M.: Nauka, 1967.–368p.
6. Korn G., Korn T., Matematikos žinynas mokslininkams ir inžinieriams.–M., 1970, 720p.
7. Aleksejevas E.R., Česnokova O.V. Gairės atlikti laboratorinius darbus MS EXCEL. Visų specialybių studentams. Doneckas, DonNTU, 2004. 112 p.
Mažiausių kvadratų metodas (LSM) pagrįstas pasirinktos funkcijos kvadratinių nuokrypių nuo tiriamų duomenų sumos sumažinimu. Šiame straipsnyje mes apytiksliai įvertiname turimus duomenis naudodami tiesinę funkcijąy = a x + b .
Mažiausio kvadrato metodas(Anglų) Įprasta Mažiausiai Kvadratai , OLS) yra vienas iš pagrindinių regresinės analizės metodų nežinomų parametrų įvertinimo požiūriu regresijos modeliai pagal pavyzdinius duomenis.
Apsvarstykite aproksimaciją pagal funkcijas, priklausančias tik nuo vieno kintamojo:
- Linijinis: y=ax+b (šis straipsnis)
- : y=a*Ln(x)+b
- : y=a*x m
- : y=a*EXP(b*x)+c
- : y=ax 2 +bx+c
Pastaba: Šiame straipsnyje nagrinėjami atvejai, kai aproksimuojamas daugianario nuo 3 iki 6 laipsnio. Čia atsižvelgiama į aproksimaciją trigonometriniu polinomu.
Linijinė priklausomybė
Mus domina 2 kintamųjų ryšys X ir y. Yra prielaida, kad y priklauso nuo X pagal tiesinį dėsnį y = kirvis + b. Siekdamas nustatyti šio ryšio parametrus, tyrėjas atliko stebėjimus: kiekvienai x i reikšmei buvo atliktas y i matavimas (žr. pavyzdinę bylą). Atitinkamai, tegul būna 20 porų reikšmių (х i ; y i).
Pastaba: Jei keičiasi žingsnis po žingsnio X yra pastovus, tada statyti sklaidos galima naudoti, jei ne, tuomet reikia naudoti diagramos tipą taškuotas .
Iš diagramos matyti, kad ryšys tarp kintamųjų yra artimas tiesiniam. Norint suprasti, kuri iš daugelio tiesių „teisingiausiai“ apibūdina ryšį tarp kintamųjų, reikia nustatyti kriterijų, pagal kurį linijos bus lyginamos.
Kaip tokį kriterijų naudojame posakį:
kur ŷ i = a * x i + b ; n – reikšmių porų skaičius (mūsų atveju n=20)
Aukščiau pateikta išraiška yra kvadratinių atstumų tarp stebimų y i ir ŷ i reikšmių suma ir dažnai žymima kaip SSE ( suma apie kvadratu Klaidos (Likučiai), klaidų kvadratų suma (likučiai)) .
Mažiausio kvadrato metodas yra pasirinkti tokią eilutę ŷ = kirvis + b, kuriai aukščiau pateikta išraiška turi mažiausią reikšmę.
Pastaba: Bet kuri linija dvimatėje erdvėje yra vienareikšmiškai nustatoma pagal 2 parametrų reikšmes: a (nuolydis) ir b (pamaina).
Manoma, kad kuo mažesnė atstumų kvadratų suma, tuo geriau atitinkama linija apytiksliai atitinka turimus duomenis ir gali būti toliau naudojama nuspėti y reikšmes iš kintamojo x. Akivaizdu, kad net jei iš tikrųjų nėra ryšio tarp kintamųjų arba ryšys yra netiesinis, mažiausias kvadratas vis tiek pasirinks „geriausią“ eilutę. Taigi LSM nieko nesako apie realaus kintamųjų ryšio buvimą, metodas tiesiog leidžia pasirinkti tokius funkcijos parametrus a ir b , kuriai aukščiau pateikta išraiška yra minimali.
Atlikę ne itin sudėtingas matematines operacijas (plačiau žr.), galite apskaičiuoti parametrus a ir b :
Kaip matyti iš formulės, parametras a yra kovariacijos santykis ir , todėl MS EXCEL parametrui apskaičiuoti a Galite naudoti šias formules (žr pavyzdinis failo lapas Linijinis):
= COVAR(B26:B45;C26:C45) / VAR.G(B26:B45) arba
= COVARIATION.B(B26:B45;C26:C45) / VAR.B(B26:B45)
Taip pat norint apskaičiuoti parametrą a galite naudoti formulę = SLOPE(C26:C45;B26:B45). Dėl parametro b naudokite formulę = INTERCUT(C26:C45;B26:B45) .
Ir galiausiai, funkcija LINEST() leidžia apskaičiuoti abu parametrus vienu metu. Norėdami įvesti formulę LINEST(C26:C45;B26:B45) pasirinkite 2 langelius iš eilės ir paspauskite CTRL + SHIFT + ENTER(žr. straipsnį apie). Kairysis langelis grąžins vertę a , dešinėje b .
Pastaba: Kad nesimaišytumėte su įvestimi masyvo formules papildomai reikės naudoti funkciją INDEX(). Formulė = INDEX(LINEST(C26:C45,B26:B45),1) arba tiesiog = LINEST(C26:C45;B26:B45) grąžins parametrą, atsakingą už linijos nuolydį, t.y. a . Formulė = RODYKLĖ(LINIJA(C26:C45,B26:B45),2) grąžins parametrą, atsakingą už tiesės susikirtimą su Y ašimi, t.y. b .
Apskaičiavus parametrus, sklaida galima nubrėžti liniją.
Kitas būdas nubrėžti tiesią liniją naudojant mažiausių kvadratų metodą yra diagramos įrankis tendencijų linija. Norėdami tai padaryti, pasirinkite diagramą, pasirinkite iš meniu Išdėstymo skirtukas, in grupės analizė spustelėkite tendencijų linija, tada Tiesinė aproksimacija .
Dialogo lange pažymėdami langelį „rodyti lygtį diagramoje“, galite įsitikinti, kad aukščiau rasti parametrai atitinka diagramos reikšmes.
Pastaba: Kad parametrai atitiktų, diagramos tipas turi būti . Faktas yra tas, kad kuriant diagramą Tvarkaraštis x ašies verčių vartotojas negali nustatyti (vartotojas gali nurodyti tik etiketes, kurios neturi įtakos taškų vietai). Vietoj X reikšmių naudojama seka 1; 2; 3; … (kategorijų numeravimui). Todėl, jei pastatas tendencijų linija tipo diagramoje Tvarkaraštis, tada šios sekos reikšmės bus naudojamos vietoj faktinių X reikšmių, todėl bus gautas neteisingas rezultatas (nebent, žinoma, tikrosios X reikšmės nesutampa su seka 1; 2 ; 3; ...).
Mažiausių kvadratų metodas (LSM)
Sistema m tiesines lygtis su n nežinomaisiais turi formą:
Galimi trys atvejai: m Jei m>n ir sistema yra nuosekli, tai matrica A turi bent m - n tiesiškai priklausomų eilučių. Čia sprendimą galima gauti pasirinkus n bet kokių tiesiškai nepriklausomų lygčių (jei jos yra) ir pritaikius formulę X=A -1 CV, tai yra redukuojant uždavinį į anksčiau išspręstą. Tokiu atveju gautas sprendimas visada tenkins likusias m - n lygtis. Tačiau naudojantis kompiuteriu patogiau taikyti bendresnį požiūrį – mažiausių kvadratų metodą. Algebrinis mažiausių kvadratų metodas suprantamas kaip tiesinių lygčių sistemų sprendimo metodas sumažinus Euklido normą Kirvis? b? > inf . (1.2) Panagrinėkime kai kuriuos eksperimentus, kurių metu laiko akimirkomis pavyzdžiui, matuojama temperatūra Q(t). Tegu matavimo rezultatus pateikia masyvas Tarkime, kad eksperimento sąlygos yra tokios, kad matavimai atliekami su žinoma paklaida. Šiais atvejais temperatūros kitimo Q(t) dėsnio ieškoma naudojant kokį nors daugianarį P(t) = + + + ... +, nustatant nežinomus koeficientus, ..., atsižvelgiant į tai, kad reikšmė E(, ...,) apibrėžta lygybe Gauso algebrinė exel aproksimacija paėmė mažiausią vertę. Kadangi kvadratų suma yra sumažinta, šis metodas vadinamas mažiausiais kvadratais, tinkančiais duomenims. Jei P(t) pakeisime jo išraiška, gausime Iškelkime užduotį apibrėžti masyvą taip, kad reikšmė būtų minimali, t.y. apibrėžkite masyvą naudodami mažiausių kvadratų metodą. Norėdami tai padaryti, dalines išvestis prilyginsime nuliui: Jei įvesite m × n matricą A = (), i = 1, 2..., m; j = 1, 2, ..., n, kur I = 1, 2..., m; j = 1, 2, ..., n, tada rašytinė lygybė įgauna formą Perrašykime rašytinę lygybę operacijomis su matricomis. Pagal apibrėžimą mes turime matricos dauginimą iš stulpelio Perkeltoje matricoje panašus santykis atrodo taip Įvedame tokį žymėjimą: pažymėsime i-ąjį vektoriaus Ax komponentą Pagal užrašytas matricos lygybes turėsime Matricos formoje ši lygybė gali būti perrašyta kaip A T x = A T B (1,3) Čia A yra stačiakampė m × n matrica. Be to, duomenų aproksimavimo problemose, kaip taisyklė, m > n. Lygtis (1.3) vadinama normaliąja lygtimi. Nuo pat pradžių, naudojant euklido vektorių normą, buvo galima parašyti uždavinį lygiaverte matricos forma: Mūsų tikslas yra sumažinti šią funkciją x. Kad sprendinio taške būtų pasiektas minimumas, pirmosios išvestinės x atžvilgiu šiame taške turi būti lygios nuliui. Šios funkcijos išvestiniai yra 2A T B + 2A T Ax ir todėl sprendimas turi tenkinti tiesinių lygčių sistemą (A T A)x = (A T B). Šios lygtys vadinamos normaliosiomis lygtimis. Jei A yra m × n matrica, tai A>A - n × n yra matrica, t.y. normalios lygties matrica visada yra kvadratinė simetrinė matrica. Be to, jis turi teigiamo apibrėžtumo savybę ta prasme, kad (A>Ax, x) = (Ax, Ax) ? 0. komentuoti. Kartais (1.3) formos lygties sprendinys vadinamas sistemos Ax = B sprendiniu, kur A yra stačiakampė m × n (m > n) matrica mažiausiųjų kvadratų metodu. Mažiausių kvadratų uždavinys gali būti grafiškai interpretuojamas kaip vertikalių atstumų nuo duomenų taškų iki modelio kreivės sumažinimas (žr. 1.1 pav.). Ši idėja pagrįsta prielaida, kad visos aproksimacijos paklaidos atitinka stebėjimo klaidas. Jei aiškinamuosiuose kintamuosiuose taip pat yra klaidų, gali būti tikslingiau sumažinti euklidinį atstumą nuo duomenų iki modelio. Toliau pateiktas OLS diegimo „Excel“ algoritmas daro prielaidą, kad visi pradiniai duomenys jau žinomi. Abi sistemos matricinės lygties AЧX=B dalis iš kairės padauginame iš transponuotos sistemos А Т matricos: A T AX \u003d A T B Tada abi lygties dalis kairėje padauginame iš matricos (A T A) -1. Jei ši matrica egzistuoja, tada sistema yra apibrėžta. Atsižvelgiant į tai, kad (A T A) -1 * (AT A) \u003d E, gauname X \u003d (A T A) -1 A T B. Gauta matricinė lygtis yra m tiesinių lygčių sistemos su n nežinomųjų, kai m>n, sprendimas. Apsvarstykite aukščiau pateikto algoritmo taikymą konkrečiame pavyzdyje. Pavyzdys. Tegul reikia išspręsti sistemą Programoje „Excel“ šios problemos sprendimo lapas formulės rodymo režimu atrodo taip: Skaičiavimo rezultatai: Norimas vektorius X yra diapazone E11:E12. Sprendžiant tam tikrą tiesinių lygčių sistemą buvo naudojamos šios funkcijos: 1. MINUTĖ – grąžina atvirkštinę matricos, saugomos masyve, vertę. Sintaksė: NBR (masyvas). Masyvas yra skaitinis masyvas, turintis vienodą eilučių ir stulpelių skaičių. 2. MULTIP – grąžina matricų sandaugą (matricos saugomos masyvuose). Rezultatas yra masyvas, turintis tiek pat eilučių kaip masyvas1 ir stulpelių skaičius kaip masyvas2. Sintaksė: MULT(masyvas1, masyvas2). Masyvas1, masyvas2 – padauginti masyvai. Įvedę funkciją viršutiniame kairiajame masyvo diapazono langelyje, pasirinkite masyvą, pradėdami nuo langelio, kuriame yra formulė, paspauskite klavišą F2, tada paspauskite klavišus CTRL+SHIFT+ENTER. 3. TRANSPOSE – vertikalią langelių rinkinį paverčia horizontaliuoju arba atvirkščiai. Šios funkcijos naudojimo rezultatas yra masyvas, kurio eilučių skaičius yra lygus pradinio masyvo stulpelių skaičiui, o stulpelių skaičius yra lygus pradinio masyvo eilučių skaičiui.Algebriniai mažiausi kvadratai
Eksperimentinė duomenų analizė
OLS programoje Excel
