LSM för en funktion av två variabler. Approximation av experimentella data. Minsta kvadratiska metod. Praktisk implementering av minsta kvadrater för linjärt beroende av en icke programmerbar miniräknare

Exempel.

Experimentella data om värdena på variabler X Och på anges i tabellen.

Som ett resultat av deras inriktning erhålls funktionen

Använder sig av minsta kvadratmetoden, approximera dessa data genom ett linjärt beroende y=ax+b(hitta parametrar A Och b). Ta reda på vilken av de två linjerna som bäst anpassar experimentdatan (i betydelsen av minsta kvadratmetoden). Gör en ritning.

Kärnan i minsta kvadratmetoden (LSM).

Uppgiften är att hitta de linjära beroendekoefficienterna där funktionen av två variabler A Och b tar det minsta värdet. Det vill säga givet A Och b summan av kvadrerade avvikelser för experimentdata från den hittade räta linjen kommer att vara den minsta. Detta är hela poängen med minsta kvadratmetoden.

Att lösa exemplet handlar alltså om att hitta extremumet för en funktion av två variabler.

Härleda formler för att hitta koefficienter.

Ett system med två ekvationer med två okända kompileras och löses. Att hitta partiella derivator av en funktion med avseende på variabler A Och b likställer vi dessa derivator till noll.

Vi löser det resulterande ekvationssystemet med vilken metod som helst (till exempel genom substitutionsmetod eller ) och få formler för att hitta koefficienter med minsta kvadratmetoden (LSM).

Given A Och b fungera tar det minsta värdet. Beviset för detta faktum ges.

Det är hela metoden med minsta kvadrater. Formel för att hitta parametern a innehåller summorna , , och parametern n- mängd experimentella data. Vi rekommenderar att du beräknar värdena för dessa belopp separat. Koefficient b hittas efter beräkning a.

Det är dags att komma ihåg det ursprungliga exemplet.

Lösning.

I vårt exempel n=5. Vi fyller i tabellen för att underlätta beräkningen av beloppen som ingår i formlerna för de nödvändiga koefficienterna.

Värdena i den fjärde raden i tabellen erhålls genom att multiplicera värdena på den andra raden med värdena på den 3:e raden för varje nummer i.

Värdena i den femte raden i tabellen erhålls genom att kvadrera värdena i den andra raden för varje nummer i.

Värdena i den sista kolumnen i tabellen är summan av värdena över raderna.

Vi använder minsta kvadratmetodens formler för att hitta koefficienterna A Och b. Vi ersätter motsvarande värden från den sista kolumnen i tabellen i dem:

Därav, y = 0,165x+2,184- den önskade ungefärliga räta linjen.

Det återstår att ta reda på vilken av raderna y = 0,165x+2,184 eller bättre approximerar originaldata, det vill säga gör en uppskattning med minsta kvadratmetoden.

Feluppskattning av minsta kvadratmetoden.

För att göra detta måste du beräkna summan av kvadrerade avvikelser av originaldata från dessa linjer Och , motsvarar ett mindre värde en linje som bättre approximerar originaldata i betydelsen minsta kvadratmetoden.

Sedan , då rakt y = 0,165x+2,184 bättre approximerar originaldata.

Grafisk illustration av minsta kvadratmetoden (LS).

Allt syns tydligt på graferna. Den röda linjen är den hittade räta linjen y = 0,165x+2,184, är den blå linjen , rosa prickar är originaldata.

Varför behövs detta, varför alla dessa uppskattningar?

Jag använder den personligen för att lösa problem med datautjämning, interpolation och extrapolationsproblem (i det ursprungliga exemplet kan de bli ombedda att hitta värdet av ett observerat värde y på x=3 eller när x=6 med minsta kvadratmetoden). Men vi kommer att prata mer om detta senare i en annan del av webbplatsen.

Bevis.

Så att när den hittas A Och b funktionen tar det minsta värdet, är det nödvändigt att vid denna punkt matrisen för kvadratformen av andra ordningens differential för funktionen var definitivt positivt. Låt oss visa det.

Den har många applikationer, eftersom den tillåter en ungefärlig representation av en given funktion med andra enklare. LSM kan vara extremt användbar vid bearbetning av observationer, och den används aktivt för att uppskatta vissa kvantiteter baserat på resultaten av mätningar av andra som innehåller slumpmässiga fel. I den här artikeln kommer du att lära dig hur du implementerar minsta kvadratberäkningar i Excel.

Förklaring av problemet med ett specifikt exempel

Anta att det finns två indikatorer X och Y. Dessutom beror Y på X. Eftersom OLS intresserar oss ur regressionsanalyssynpunkt (i Excel implementeras dess metoder med inbyggda funktioner), bör vi omedelbart gå vidare till att överväga en specifikt problem.

Så låt X vara butiksytan för en livsmedelsbutik, mätt i kvadratmeter, och Y vara den årliga omsättningen, mätt i miljoner rubel.

Det krävs att man gör en prognos på vilken omsättning (Y) butiken kommer att ha om den har den eller den butiksytan. Uppenbarligen ökar funktionen Y = f (X), eftersom stormarknaden säljer fler varor än ståndet.

Några ord om riktigheten av de initiala data som används för förutsägelse

Låt oss säga att vi har en tabell byggd med hjälp av data för n butiker.

Enligt matematisk statistik blir resultaten mer eller mindre korrekta om data på minst 5-6 föremål undersöks. Dessutom kan "anomala" resultat inte användas. I synnerhet kan en liten elitbutik ha en omsättning som är flera gånger större än omsättningen för stora butiker i klassen "masmarket".

Kärnan i metoden

Tabelldata kan avbildas på ett kartesiskt plan i form av punkterna M 1 (x 1, y 1), ... M n (x n, y n). Nu kommer lösningen på problemet att reduceras till valet av en approximativ funktion y = f (x), som har en graf som går så nära punkterna M 1, M 2, .. M n som möjligt.

Naturligtvis kan du använda ett höggradigt polynom, men det här alternativet är inte bara svårt att implementera, utan också helt enkelt felaktigt, eftersom det inte kommer att spegla huvudtrenden som måste upptäckas. Den mest rimliga lösningen är att söka efter den räta linjen y = ax + b, som bäst approximerar experimentdata, eller mer exakt, koefficienterna a och b.

Noggrannhetsbedömning

Med någon approximation är det av särskild vikt att bedöma dess noggrannhet. Låt oss beteckna med e i skillnaden (avvikelsen) mellan de funktionella och experimentella värdena för punkt x i, dvs e i = y i - f (xi).

För att bedöma approximationens noggrannhet kan du självklart använda summan av avvikelser, d.v.s. när du väljer en rät linje för en ungefärlig representation av beroendet av X på Y, bör du ge företräde åt den med det minsta värdet av summa e i på alla punkter under övervägande. Men allt är inte så enkelt, eftersom det tillsammans med positiva avvikelser också kommer att finnas negativa.

Problemet kan lösas med avvikelsemoduler eller deras kvadrater. Den sista metoden är den mest använda. Den används inom många områden, inklusive regressionsanalys (implementerad i Excel med två inbyggda funktioner), och har länge bevisat sin effektivitet.

Minsta kvadratiska metod

Excel, som du vet, har en inbyggd AutoSum-funktion som låter dig beräkna värdena för alla värden som finns i det valda intervallet. Inget kommer alltså att hindra oss från att beräkna uttryckets värde (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

I matematisk notation ser detta ut så här:

Eftersom beslutet från början togs att approximera med en rät linje, har vi:

Uppgiften att hitta den räta linje som bäst beskriver det specifika beroendet av storheterna X och Y går ut på att beräkna minimum av en funktion av två variabler:

För att göra detta måste du likställa de partiella derivatorna med avseende på de nya variablerna a och b till noll, och lösa ett primitivt system som består av två ekvationer med 2 okända av formen:

Efter några enkla transformationer, inklusive division med 2 och manipulering av summor, får vi:

Om vi löser det, till exempel med Cramers metod, får vi en stationär punkt med vissa koefficienter a * och b *. Detta är minimum, det vill säga för att förutsäga vilken omsättning en butik kommer att ha för ett visst område, är den räta linjen y = a * x + b * lämplig, vilket är en regressionsmodell för exemplet i fråga. Naturligtvis kommer det inte att tillåta dig att hitta det exakta resultatet, men det hjälper dig att få en uppfattning om huruvida att köpa ett specifikt område på butikskredit kommer att löna sig.

Hur man implementerar minsta kvadrater i Excel

Excel har en funktion för att beräkna värden med hjälp av minsta kvadrater. Den har följande form: "TREND" (kända Y-värden; kända X-värden; nya X-värden; konstant). Låt oss tillämpa formeln för att beräkna OLS i Excel på vår tabell.

För att göra detta, skriv in "="-tecknet i cellen där resultatet av beräkningen med minsta kvadratmetoden i Excel ska visas och välj funktionen "TREND". I fönstret som öppnas fyller du i lämpliga fält och markerar:

intervall av kända värden för Y (i detta fall data för handelsomsättning);
intervall x 1 , …x n , dvs storleken på butiksytan;
både kända och okända värden på x, för vilka du måste ta reda på storleken på omsättningen (för information om deras plats på kalkylbladet, se nedan).

Dessutom innehåller formeln den logiska variabeln "Const". Om du anger 1 i motsvarande fält betyder det att du ska utföra beräkningarna, förutsatt att b = 0.

Om du behöver ta reda på prognosen för mer än ett x-värde, efter att ha angett formeln ska du inte trycka på "Enter", utan du måste skriva kombinationen "Shift" + "Control" + "Enter" på tangentbordet.

Vissa funktioner

Regressionsanalys kan vara tillgänglig även för dummies. Excel-formeln för att förutsäga värdet av en rad okända variabler – TREND – kan användas även av dem som aldrig har hört talas om minsta kvadrater. Det räcker med att bara känna till några av funktionerna i dess arbete. Särskilt:

Om du ordnar intervallet av kända värden för variabeln y i en rad eller kolumn, kommer varje rad (kolumn) med kända värden på x att uppfattas av programmet som en separat variabel.
Om ett intervall med känt x inte anges i TREND-fönstret, när du använder funktionen i Excel, kommer programmet att behandla det som en matris bestående av heltal, vars antal motsvarar intervallet med de givna värdena för variabel y.
För att mata ut en matris med "förutspådda" värden måste uttrycket för att beräkna trenden anges som en matrisformel.
Om nya värden på x inte anges, anser TREND-funktionen dem vara lika med de kända. Om de inte är specificerade, tas array 1 som ett argument; 2; 3; 4;…, vilket är proportionerligt med intervallet med redan specificerade parametrar y.
Området som innehåller de nya x-värdena måste ha samma eller flera rader eller kolumner som intervallet som innehåller de givna y-värdena. Den måste med andra ord vara proportionell mot de oberoende variablerna.
En matris med kända x-värden kan innehålla flera variabler. Men om vi bara talar om en, så krävs det att intervallen med de givna värdena för x och y är proportionella. I fallet med flera variabler är det nödvändigt att intervallet med de givna y-värdena passar i en kolumn eller en rad.

PREDICTION funktion

Implementerad med flera funktioner. En av dem heter "PREDICTION". Det liknar "TREND", det vill säga det ger resultatet av beräkningar med minsta kvadratmetoden. Dock endast för ett X, för vilket värdet på Y är okänt.

Nu vet du formler i Excel för dummies som låter dig förutsäga det framtida värdet av en viss indikator enligt en linjär trend.

Minsta kvadratmetoden är en av de vanligaste och mest utvecklade på grund av sin enkelhet och effektivitet av metoder för att uppskatta parametrar för linjär. Samtidigt bör en viss försiktighet iakttas när du använder det, eftersom modeller som konstruerats med det kanske inte uppfyller ett antal krav på kvaliteten på deras parametrar och som ett resultat inte speglar mönstren för processutveckling "väl" tillräckligt.

Låt oss överväga proceduren för att uppskatta parametrarna för en linjär ekonometrisk modell med hjälp av minsta kvadratmetoden mer i detalj. En sådan modell i allmänhet kan representeras av ekvation (1.2):

y t = a 0 + a 1 x 1 t +...+ a n x nt + ε t.

De initiala data vid uppskattning av parametrarna a 0 , a 1 ,..., a n är en vektor av värden för den beroende variabeln y= (y 1 , y 2 , ... , y T)" och matrisen av värden för oberoende variabler

där den första kolumnen, bestående av ettor, motsvarar modellkoefficienten.

Minsta kvadratmetoden fick sitt namn baserat på den grundläggande principen att parameteruppskattningarna som erhålls på grundval av den måste uppfylla: summan av kvadrater av modellfelet bör vara minimal.

Exempel på att lösa problem med minsta kvadratmetoden

Exempel 2.1. Handelsföretaget har ett nätverk av 12 butiker, information om vars verksamhet presenteras i tabellen. 2.1.

Företagsledningen skulle vilja veta hur det årliga beloppet beror på butikens butiksyta.

Tabell 2.1

Butiksnummer	Årlig omsättning, miljoner rubel.	Butiksyta, tusen m2

Minsta kvadraters lösning. Låt oss beteckna butikens årliga omsättning, miljoner rubel; — Butikens butiksyta, tusen m2.

Fig.2.1. Scatterplot för exempel 2.1

För att bestämma formen för det funktionella sambandet mellan variablerna kommer vi att konstruera ett spridningsdiagram (Fig. 2.1).

Baserat på spridningsdiagrammet kan vi dra slutsatsen att den årliga omsättningen är positivt beroende av butiksyta (dvs y kommer att öka med ökande ). Den lämpligaste formen av funktionell anslutning är linjär.

Information för ytterligare beräkningar presenteras i tabell. 2.2. Med hjälp av minsta kvadratmetoden uppskattar vi parametrarna för en linjär enfaktors ekonometrisk modell

Tabell 2.2

Således,

Därför, med en ökning av butiksytan med 1 tusen m2, allt annat lika, ökar den genomsnittliga årliga omsättningen med 67,8871 miljoner rubel.

Exempel 2.2. Företagets ledning noterade att den årliga omsättningen inte bara beror på butikens försäljningsområde (se exempel 2.1), utan även på det genomsnittliga antalet besökare. Den relevanta informationen presenteras i tabellen. 2.3.

Tabell 2.3

Lösning. Låt oss beteckna det genomsnittliga antalet besökare till den e butiken per dag, tusen personer.

För att bestämma formen för det funktionella sambandet mellan variablerna kommer vi att konstruera ett spridningsdiagram (Fig. 2.2).

Baserat på spridningsdiagrammet kan vi dra slutsatsen att den årliga omsättningen är positivt beroende av det genomsnittliga antalet besökare per dag (dvs y kommer att öka med ökande ). Formen av funktionellt beroende är linjär.

Ris. 2.2. Scatterplot för exempel 2.2

Tabell 2.4

I allmänhet är det nödvändigt att bestämma parametrarna för en tvåfaktors ekonometrisk modell

y t = a 0 + a 1 x 1 t + a 2 x 2 t + ε t

Den information som krävs för ytterligare beräkningar presenteras i tabell. 2.4.

Låt oss uppskatta parametrarna för en linjär tvåfaktors ekonometrisk modell med hjälp av minsta kvadratmetoden.

Således,

Uppskattning av koefficienten =61,6583 visar att, allt annat lika, med en ökning av butiksytan med 1 tusen m 2, kommer den årliga omsättningen att öka med i genomsnitt 61,6583 miljoner rubel.

Minsta kvadratiska metod

Minsta kvadratmetod ( OLS, OLS, Vanliga minsta kvadrater) - en av de grundläggande metoderna för regressionsanalys för att uppskatta okända parametrar för regressionsmodeller med hjälp av provdata. Metoden bygger på att minimera summan av kvadrater av regressionsresidualer.

Det bör noteras att metoden med minsta kvadrater i sig kan kallas en metod för att lösa ett problem inom vilket område som helst om lösningen ligger i eller uppfyller något kriterium för att minimera kvadratsumman av vissa funktioner av de nödvändiga variablerna. Därför kan minsta kvadratmetoden också användas för en ungefärlig representation (approximation) av en given funktion med andra (enklare) funktioner, när man hittar en uppsättning kvantiteter som uppfyller ekvationer eller begränsningar, vars antal överstiger antalet av dessa storheter , etc.

Kärnan i MNC

Låt någon (parametrisk) modell av ett probabilistiskt (regression) förhållande mellan den (förklarade) variabeln ges y och många faktorer (förklarande variabler) x

var är vektorn för okända modellparametrar

- slumpmässigt modellfel.

Låt det också finnas provobservationer av värdena för dessa variabler. Låt vara observationsnumret (). Sedan är värdena för variablerna i den e observationen. Sedan, för givna värden av parametrar b, är det möjligt att beräkna de teoretiska (modell) värdena för den förklarade variabeln y:

Storleken på resterna beror på parametrarnas värden b.

Kärnan i minsta kvadratmetoden (vanlig, klassisk) är att hitta parametrar b för vilka summan av kvadraterna av residualerna (eng. Restsumma av kvadrater) kommer att vara minimal:

I det allmänna fallet kan detta problem lösas med numeriska optimeringsmetoder (minimering). I det här fallet talar de om olinjära minsta kvadrater(NLS eller NLLS - engelska) Icke-linjära minsta kvadrater). I många fall är det möjligt att få en analytisk lösning. För att lösa minimeringsproblemet är det nödvändigt att hitta stationära punkter för funktionen genom att differentiera den med avseende på de okända parametrarna b, likställa derivatorna till noll och lösa det resulterande ekvationssystemet:

Om modellens slumpmässiga fel är normalfördelade, har samma varians och okorrelerade, är OLS-parameteruppskattningar desamma som maximala sannolikhetsuppskattningar (MLM).

OLS när det gäller en linjär modell

Låt regressionsberoendet vara linjärt:

Låta yär en kolumnvektor av observationer av den förklarade variabeln och är en matris av faktorobservationer (raderna i matrisen är vektorerna av faktorvärden i en given observation, kolumnerna är vektorn av värden för en given faktor i alla observationer). Matrisrepresentationen av den linjära modellen är:

Då kommer uppskattningsvektorn för den förklarade variabeln och vektorn för regressionsresterna att vara lika

Följaktligen kommer summan av kvadraterna av regressionsresterna att vara lika med

Genom att differentiera denna funktion med avseende på parametrarnas vektor och likställa derivatorna till noll, får vi ett ekvationssystem (i matrisform):

Lösningen av detta ekvationssystem ger den allmänna formeln för minsta kvadraters uppskattningar för en linjär modell:

För analytiska ändamål är den senare representationen av denna formel användbar. Om i en regressionsmodell data centrerad, då i denna representation har den första matrisen betydelsen av en samvariationsmatris av faktorer, och den andra är en vektor av kovarianser av faktorer med den beroende variabeln. Om dessutom uppgifterna också är normaliserats till MSE (det vill säga i slutändan standardiserad), så har den första matrisen betydelsen av en provkorrelationsmatris av faktorer, den andra vektorn - en vektor av provkorrelationer av faktorer med den beroende variabeln.

En viktig egenskap hos OLS-uppskattningar för modeller med konstant- linjen för den konstruerade regressionen passerar genom provdatas tyngdpunkt, det vill säga att likheten är uppfylld:

I synnerhet, i extremfallet, när den enda regressorn är en konstant, finner vi att OLS-uppskattningen av den enda parametern (konstanten i sig) är lika med medelvärdet för den förklarade variabeln. Det vill säga, det aritmetiska medelvärdet, känt för sina goda egenskaper från lagarna för stora tal, är också en minsta kvadratuppskattning - det uppfyller kriteriet för den minsta summan av kvadrerade avvikelser från det.

Exempel: enklaste (parvis) regression

I fallet med parad linjär regression förenklas beräkningsformlerna (du kan klara dig utan matrisalgebra):

Egenskaper för OLS-uppskattare

Först och främst noterar vi att för linjära modeller är OLS-uppskattningar linjära uppskattningar, enligt ovanstående formel. För opartiska OLS-uppskattningar är det nödvändigt och tillräckligt för att uppfylla det viktigaste villkoret för regressionsanalys: den matematiska förväntningen på ett slumpmässigt fel, beroende på faktorerna, måste vara lika med noll. Detta villkor är särskilt uppfyllt om

den matematiska förväntan på slumpmässiga fel är noll, och
faktorer och slumpmässiga fel är oberoende slumpvariabler.

Det andra villkoret - villkoret för exogenitet av faktorer - är grundläggande. Om den här egenskapen inte uppfylls kan vi anta att nästan alla uppskattningar kommer att vara extremt otillfredsställande: de kommer inte ens att vara konsekventa (det vill säga inte ens en mycket stor mängd data tillåter oss att få högkvalitativa uppskattningar i detta fall ). I det klassiska fallet görs ett starkare antagande om faktorernas determinism, till skillnad från ett slumpmässigt fel, vilket automatiskt innebär att exogenitetsvillkoret är uppfyllt. I det allmänna fallet, för konsekvensen av uppskattningarna, är det tillräckligt att uppfylla exogenitetsvillkoret tillsammans med konvergensen av matrisen till någon icke-singular matris när urvalsstorleken ökar till oändlighet.

För att, förutom konsekvens och opartiskhet, även uppskattningar av (vanliga) minsta kvadrater ska vara effektiva (bäst i klassen av linjära opartiska skattningar), måste ytterligare egenskaper för slumpmässiga fel uppfyllas:

Dessa antaganden kan formuleras för kovariansmatrisen för slumpfelsvektorn

En linjär modell som uppfyller dessa villkor kallas klassisk. OLS-uppskattningar för klassisk linjär regression är opartiska, konsekventa och de mest effektiva uppskattningarna i klassen av alla linjära opartiska uppskattningar (i den engelska litteraturen används ibland förkortningen BLÅ (Bästa linjära unbased estimator) - den bästa linjära opartiska skattningen; i rysk litteratur citeras Gauss-Markovs sats oftare). Som det är lätt att visa kommer kovariansmatrisen för vektorn för koefficientuppskattningar att vara lika med:

Generaliserad OLS

Minsta kvadratmetoden möjliggör bred generalisering. Istället för att minimera summan av kvadraterna av residualerna, kan man minimera någon positiv bestämd kvadratisk form av vektorn av residualer, där är någon symmetrisk positiv bestämd viktmatris. Konventionella minsta kvadrater är ett specialfall av detta tillvägagångssätt, där viktmatrisen är proportionell mot identitetsmatrisen. Som är känt från teorin om symmetriska matriser (eller operatorer), för sådana matriser sker en nedbrytning. Följaktligen kan den specificerade funktionalen representeras enligt följande, det vill säga denna funktion kan representeras som summan av kvadraterna av några transformerade "rester". Således kan vi urskilja en klass av minsta kvadratmetoder - LS-metoder (Minsta kvadrater).

Det har bevisats (Aitkens teorem) att för en generaliserad linjär regressionsmodell (där inga restriktioner är införda på kovariansmatrisen av slumpmässiga fel), är de mest effektiva (i klassen linjära opartiska skattningar) de så kallade uppskattningarna. generaliserade minsta kvadrater (GLS – Generaliserade minsta kvadrater)- LS-metod med en viktmatris lika med den inversa kovariansmatrisen för slumpmässiga fel: .

Det kan visas att formeln för GLS-uppskattningar av parametrarna för en linjär modell har formen

Kovariansmatrisen för dessa uppskattningar kommer följaktligen att vara lika med

Faktum är att kärnan i OLS ligger i en viss (linjär) transformation (P) av originaldata och tillämpningen av vanlig OLS på de transformerade data. Syftet med denna transformation är att för de transformerade data, de slumpmässiga felen redan uppfyller de klassiska antagandena.

Viktad OLS

När det gäller en diagonal viktmatris (och därför en kovariansmatris av slumpmässiga fel) har vi de så kallade viktade minsta kvadraterna (WLS). I detta fall minimeras den viktade kvadratsumman av modellresidualerna, det vill säga varje observation får en "vikt" som är omvänt proportionell mot variansen av det slumpmässiga felet i denna observation: . Faktum är att data transformeras genom viktning av observationerna (dividering med ett belopp som är proportionellt mot den uppskattade standardavvikelsen för de slumpmässiga felen), och vanlig OLS tillämpas på den viktade datan.

Några speciella fall av att använda MNC i praktiken

Approximation av linjärt beroende

Låt oss överväga fallet när, som ett resultat av att studera beroendet av en viss skalär kvantitet av en viss skalär kvantitet (Detta kan till exempel vara spänningens beroende av strömstyrkan: , där är ett konstant värde, resistansen av ledaren), mätningar av dessa kvantiteter utfördes, vilket resulterade i värdena och deras motsvarande värden. Mätdata ska registreras i en tabell.

Tabell. Mätresultat.

Mått nr.
1
2
3
4
5
6

Frågan är: vilket värde på koefficienten kan väljas för att bäst beskriva beroendet? Enligt minsta kvadratmetoden bör detta värde vara sådant att summan av de kvadrerade avvikelserna för värdena från värdena

var minimal

Summan av kvadrerade avvikelser har ett extremum - ett minimum, vilket gör att vi kan använda denna formel. Låt oss ta reda på värdet av koefficienten från denna formel. För att göra detta omvandlar vi dess vänstra sida enligt följande:

Den sista formeln låter oss hitta värdet på koefficienten, vilket är vad som krävdes i problemet.

Berättelse

Fram till början av 1800-talet. vetenskapsmän hade inte vissa regler för att lösa ett ekvationssystem där antalet okända är mindre än antalet ekvationer; Fram till den tiden användes privata tekniker som berodde på typen av ekvationer och på räknarnas kvickhet, och därför kom olika räknare, baserade på samma observationsdata, till olika slutsatser. Gauss (1795) var den första som använde metoden, och Legendre (1805) upptäckte och publicerade den självständigt under sitt moderna namn (franska. Méthode des moindres quarrés ) . Laplace relaterade metoden till sannolikhetsteori, och den amerikanske matematikern Adrain (1808) övervägde dess sannolikhetsteoretiska tillämpningar. Metoden fick stor spridning och förbättrades genom ytterligare forskning av Encke, Bessel, Hansen och andra.

Alternativ användning av OLS

Idén med minsta kvadratmetoden kan också användas i andra fall som inte är direkt relaterade till regressionsanalys. Faktum är att summan av kvadrater är ett av de vanligaste närhetsmåtten för vektorer (euklidisk metrik i finita dimensionella rum).

En tillämpning är "lösningen" av system av linjära ekvationer där antalet ekvationer är större än antalet variabler

där matrisen inte är kvadratisk, utan rektangulär.

Ett sådant ekvationssystem har i det allmänna fallet ingen lösning (om rangordningen faktiskt är större än antalet variabler). Därför kan detta system endast "lösas" i den meningen att man väljer en sådan vektor för att minimera "avståndet" mellan vektorerna och . För att göra detta kan du tillämpa kriteriet att minimera summan av kvadrater av skillnaderna mellan vänster och höger sida av systemekvationerna, det vill säga. Det är lätt att visa att att lösa detta minimeringsproblem leder till att lösa följande ekvationssystem

Exempel.

Experimentella data om värdena på variabler X Och på anges i tabellen.

Som ett resultat av deras inriktning erhålls funktionen

Kärnan i minsta kvadratmetoden (LSM).

Att lösa exemplet handlar alltså om att hitta extremumet för en funktion av två variabler.

Härleda formler för att hitta koefficienter.

Ett system med två ekvationer med två okända kompileras och löses. Hitta de partiella derivatorna av en funktion genom variabler A Och b likställer vi dessa derivator till noll.

Vi löser det resulterande ekvationssystemet med vilken metod som helst (till exempel genom substitutionsmetod eller Cramers metod) och få formler för att hitta koefficienter med minsta kvadratmetoden (LSM).

Given A Och b fungera tar det minsta värdet. Beviset för detta faktum ges nedan i texten i slutet av sidan.

Det är hela metoden med minsta kvadrater. Formel för att hitta parametern a innehåller summorna ,,, och parametern n- mängd experimentella data. Vi rekommenderar att du beräknar värdena för dessa belopp separat. Koefficient b hittas efter beräkning a.

Det är dags att komma ihåg det ursprungliga exemplet.

Lösning.

I vårt exempel n=5. Vi fyller i tabellen för att underlätta beräkningen av beloppen som ingår i formlerna för de nödvändiga koefficienterna.

Värdena i den fjärde raden i tabellen erhålls genom att multiplicera värdena på den andra raden med värdena på den 3:e raden för varje nummer i.

Värdena i den femte raden i tabellen erhålls genom att kvadrera värdena i den andra raden för varje nummer i.

Värdena i den sista kolumnen i tabellen är summan av värdena över raderna.

Vi använder minsta kvadratmetodens formler för att hitta koefficienterna A Och b. Vi ersätter motsvarande värden från den sista kolumnen i tabellen i dem:

Därav, y = 0,165x+2,184- den önskade ungefärliga räta linjen.

Det återstår att ta reda på vilken av raderna y = 0,165x+2,184 eller bättre approximerar originaldata, det vill säga gör en uppskattning med minsta kvadratmetoden.

Feluppskattning av minsta kvadratmetoden.

Sedan , då rakt y = 0,165x+2,184 bättre approximerar originaldata.

Grafisk illustration av minsta kvadratmetoden (LS).

Allt syns tydligt på graferna. Den röda linjen är den hittade räta linjen y = 0,165x+2,184, är den blå linjen , rosa prickar är originaldata.

I praktiken, vid modellering av olika processer - särskilt ekonomiska, fysiska, tekniska, sociala - används en eller annan metod för att beräkna ungefärliga värden på funktioner från deras kända värden vid vissa fasta punkter.

Denna typ av funktionsapproximationsproblem uppstår ofta:

när man konstruerar ungefärliga formler för beräkning av värdena för karakteristiska kvantiteter för processen som studeras med hjälp av tabelldata som erhållits som ett resultat av experimentet;

i numerisk integration, differentiering, lösning av differentialekvationer, etc.;

vid behov, beräkna värdena för funktioner vid mellanliggande punkter i det övervägda intervallet;

när man bestämmer värdena för karakteristiska kvantiteter för en process utanför det övervägda intervallet, särskilt vid prognos.

Om vi, för att modellera en viss process specificerad av en tabell, konstruerar en funktion som ungefär beskriver denna process utifrån minsta kvadratmetoden, kommer den att kallas en approximerande funktion (regression), och själva uppgiften att konstruera approximerande funktioner kommer att kallas ett approximationsproblem.

Den här artikeln diskuterar MS Excel-paketets möjligheter för att lösa den här typen av problem, dessutom tillhandahåller den metoder och tekniker för att konstruera (skapa) regressioner för tabellerade funktioner (vilket är grunden för regressionsanalys).

Excel har två alternativ för att bygga regressioner.

Lägga till utvalda regressioner (trendlinjer) till ett diagram byggt på basis av en datatabell för processkaraktäristiken som studeras (endast tillgängligt om ett diagram har konstruerats);

Använda de inbyggda statistiska funktionerna i Excel-kalkylbladet, så att du kan få regressioner (trendlinjer) direkt från källdatatabellen.

Lägga till trendlinjer i ett diagram

För en tabell med data som beskriver en process och representeras av ett diagram, har Excel ett effektivt regressionsanalysverktyg som låter dig:

bygga utifrån minsta kvadratmetoden och lägga till fem typer av regressioner till diagrammet, vilka modellerar processen som studeras med varierande grad av noggrannhet;

lägg till den konstruerade regressionsekvationen till diagrammet;

bestäm graden av överensstämmelse mellan den valda regressionen och data som visas i diagrammet.

Baserat på diagramdata låter Excel dig få linjära, polynomiska, logaritmiska, potenser, exponentiella typer av regressioner, som specificeras av ekvationen:

y = y(x)

där x är en oberoende variabel som ofta tar värdena av en sekvens av naturliga tal (1; 2; 3; ...) och producerar till exempel en nedräkning av tiden för processen som studeras (egenskaper).

1 . Linjär regression är bra för modellering av egenskaper vars värden ökar eller minskar med en konstant hastighet. Detta är den enklaste modellen att konstruera för processen som studeras. Den är konstruerad i enlighet med ekvationen:

y = mx + b

där m är tangenten för den linjära regressionslutningen till x-axeln; b - koordinat för skärningspunkten för linjär regression med ordinataaxeln.

2 . En polynomtrendlinje är användbar för att beskriva egenskaper som har flera distinkta ytterligheter (maxima och minima). Valet av polynomgrad bestäms av antalet extrema för den egenskap som studeras. Således kan ett andragradspolynom väl beskriva en process som bara har ett maximum eller minimum; polynom av tredje graden - inte mer än två extrema; polynom av fjärde graden - inte mer än tre extrema, etc.

I detta fall konstrueras trendlinjen i enlighet med ekvationen:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

där koefficienterna c0, c1, c2,...c6 är konstanter vars värden bestäms under konstruktionen.

3 . Den logaritmiska trendlinjen används framgångsrikt vid modellering av egenskaper vars värden initialt ändras snabbt och sedan gradvis stabiliseras.

y = c ln(x) + b

4 . En makträttslig trendlinje ger goda resultat om värdena i relationen som studeras kännetecknas av en konstant förändring i tillväxttakten. Ett exempel på ett sådant beroende är grafen för en bils likformigt accelererade rörelse. Om det finns noll eller negativa värden i data kan du inte använda en effekttrendlinje.

Konstruerad i enlighet med ekvationen:

y = c xb

där koefficienterna b, c är konstanter.

5 . En exponentiell trendlinje bör användas när förändringshastigheten i data kontinuerligt ökar. För data som innehåller noll eller negativa värden är denna typ av approximation inte heller tillämplig.

Konstruerad i enlighet med ekvationen:

y = c ebx

där koefficienterna b, c är konstanter.

När du väljer en trendlinje beräknar Excel automatiskt värdet på R2, vilket kännetecknar tillförlitligheten av approximationen: ju närmare R2-värdet är enhet, desto mer tillförlitligt approximerar trendlinjen processen som studeras. Vid behov kan R2-värdet alltid visas på diagrammet.

Bestäms av formeln:

Så här lägger du till en trendlinje till en dataserie:

aktivera ett diagram baserat på en serie data, dvs klicka inom diagramområdet. Objektet Diagram kommer att visas i huvudmenyn;

efter att ha klickat på det här alternativet kommer en meny att visas på skärmen där du ska välja kommandot Lägg till trendlinje.

Samma åtgärder kan enkelt implementeras genom att flytta muspekaren över grafen som motsvarar en av dataserierna och högerklicka; I snabbmenyn som visas väljer du kommandot Lägg till trendlinje. Dialogrutan Trendlinje visas på skärmen med fliken Typ öppen (Fig. 1).

Efter detta behöver du:

Välj önskad trendlinjetyp på fliken Typ (den Linjära typen är vald som standard). För typen Polynom, i fältet Grad, ange graden av det valda polynomet.

1 . Fältet Byggd på serie listar alla dataserier i diagrammet i fråga. För att lägga till en trendlinje till en specifik dataserie, välj dess namn i fältet Byggd på serie.

Om det behövs, genom att gå till fliken Parametrar (Fig. 2), kan du ställa in följande parametrar för trendlinjen:

ändra namnet på trendlinjen i fältet Namn på den approximativa (utjämnade) kurvan.

ställ in antalet perioder (framåt eller bakåt) för prognosen i fältet Prognos;

visa ekvationen för trendlinjen i diagramområdet, för vilket du bör aktivera kryssrutan visa ekvation på diagrammet;

visa approximationstillförlitlighetsvärdet R2 i diagramområdet, för vilket du bör aktivera kryssrutan Placera approximationstillförlitlighetsvärdet på diagrammet (R^2);

ställ in skärningspunkten för trendlinjen med Y-axeln, för vilken du bör aktivera kryssrutan för skärningen av kurvan med Y-axeln vid en punkt;

Klicka på OK-knappen för att stänga dialogrutan.

För att börja redigera en redan ritad trendlinje finns det tre sätt:

använd kommandot Vald trendlinje från Format-menyn efter att ha valt trendlinjen tidigare;

välj kommandot Formatera trendlinje från snabbmenyn, som öppnas genom att högerklicka på trendlinjen;

dubbelklicka på trendlinjen.

Dialogrutan Trendlinjeformat visas på skärmen (Fig. 3), som innehåller tre flikar: Visa, Typ, Parametrar och innehållet i de två sista sammanfaller helt med liknande flikar i dialogrutan Trendlinje (Fig. 1) -2). På fliken Visa kan du ställa in linjetyp, dess färg och tjocklek.

För att radera en trendlinje som redan har ritats, välj den trendlinje som ska raderas och tryck på Delete-tangenten.

Fördelarna med det övervägda regressionsanalysverktyget är:

den relativa lättheten att konstruera en trendlinje på diagram utan att skapa en datatabell för den;

en ganska bred lista över typer av föreslagna trendlinjer, och denna lista inkluderar de vanligaste typerna av regression;

förmågan att förutsäga beteendet hos den process som studeras genom ett godtyckligt (inom gränserna för sunt förnuft) antal steg framåt och även bakåt;

förmågan att erhålla trendlinjeekvationen i analytisk form;

möjligheten att vid behov få en bedömning av tillförlitligheten av approximationen.

Nackdelarna inkluderar följande:

konstruktionen av en trendlinje utförs endast om det finns ett diagram byggt på en serie data;

processen att generera dataserier för egenskapen som studeras baserat på trendlinjeekvationerna som erhållits för den är något rörig: de nödvändiga regressionsekvationerna uppdateras med varje förändring av värdena för den ursprungliga dataserien, men bara inom diagramområdet medan dataserien bildad på basis av den gamla linjeekvationstrenden förblir oförändrad;

I pivotdiagramrapporter bevaras inte befintliga trendlinjer genom att ändra vyn för ett diagram eller tillhörande pivottabellsrapport, vilket innebär att innan du ritar trendlinjer eller på annat sätt formaterar en pivotdiagramrapport bör du se till att rapportlayouten uppfyller de krav som krävs.

Trendlinjer kan användas för att komplettera dataserier som presenteras på diagram som graf, histogram, platta icke-standardiserade områdesdiagram, stapeldiagram, punktdiagram, bubbeldiagram och aktiediagram.

Du kan inte lägga till trendlinjer till dataserier i 3D-, normaliserade, radar-, cirkel- och munkdiagram.

Använder Excels inbyggda funktioner

Excel har också ett regressionsanalysverktyg för att rita trendlinjer utanför diagramområdet. Det finns ett antal statistiska kalkylbladsfunktioner du kan använda för detta ändamål, men alla låter dig bara bygga linjära eller exponentiella regressioner.

Excel har flera funktioner för att konstruera linjär regression, särskilt:

TREND;

SLUTNING och SNITT.

Samt flera funktioner för att konstruera en exponentiell trendlinje, i synnerhet:

LGRFPRIBL.

Det bör noteras att teknikerna för att konstruera regressioner med funktionerna TREND och GROWTH är nästan desamma. Detsamma kan sägas om funktionsparet LINEST och LGRFPRIBL. För dessa fyra funktioner använder du Excel-funktioner för att skapa en värdetabell, som arrayformler, som något rörig upp processen att bygga regressioner. Observera också att konstruktionen av linjär regression, enligt vår åsikt, är lättast att utföra med hjälp av funktionerna SLOPE och INTERCEPT, där den första av dem bestämmer lutningen för den linjära regressionen, och den andra bestämmer segmentet som fångas upp av regressionen på y -axel.

Fördelarna med det inbyggda funktionsverktyget för regressionsanalys är:

en ganska enkel, enhetlig process för att generera dataserier av egenskapen som studeras för alla inbyggda statistiska funktioner som definierar trendlinjer;

standardmetodik för att konstruera trendlinjer baserat på genererade dataserier;

förmågan att förutsäga beteendet hos den process som studeras genom det erforderliga antalet steg framåt eller bakåt.

Nackdelarna är bland annat att Excel inte har inbyggda funktioner för att skapa andra (förutom linjära och exponentiella) typer av trendlinjer. Denna omständighet tillåter ofta inte att välja en tillräckligt exakt modell av processen som studeras, samt att få prognoser som ligger nära verkligheten. Dessutom, när du använder funktionerna TREND och GROWTH, är ekvationerna för trendlinjerna inte kända.

Det bör noteras att författarna inte hade för avsikt att presentera förloppet av regressionsanalys med någon grad av fullständighet. Dess huvudsakliga uppgift är att visa, med hjälp av specifika exempel, Excel-paketets kapacitet när man löser approximationsproblem; visa vilka effektiva verktyg Excel har för att bygga regressioner och prognoser; illustrera hur sådana problem relativt enkelt kan lösas även av en användare som inte har stor kunskap om regressionsanalys.

Exempel på att lösa specifika problem

Låt oss titta på att lösa specifika problem med hjälp av de listade Excel-verktygen.

Problem 1

Med en tabell med uppgifter om vinsten för ett biltransportföretag 1995-2002. du måste göra följande:

Bygg ett diagram.

Lägg till linjära och polynomiska (kvadratiska och kubiska) trendlinjer till diagrammet.

Använd trendlinjeekvationerna för att få tabelldata om företagsvinster för varje trendlinje för 1995-2004.

Gör en prognos för företagets vinst för 2003 och 2004.

Lösningen på problemet

I intervallet av celler A4:C11 i Excel-kalkylbladet anger du kalkylbladet som visas i Fig. 4.

Efter att ha valt cellområdet B4:C11 bygger vi ett diagram.

Vi aktiverar det konstruerade diagrammet och, enligt metoden som beskrivs ovan, efter att ha valt typ av trendlinje i dialogrutan Trendlinje (se fig. 1), lägger vi omväxlande till linjära, kvadratiska och kubiska trendlinjer till diagrammet. I samma dialogruta öppnar du fliken Parametrar (se fig. 2), i fältet Namn på den approximativa (utjämnade) kurvan anger du namnet på trenden som läggs till och i fältet Prognos framåt för: perioder anger du värde 2, eftersom man planerar att göra en vinstprognos för två år framåt. För att visa regressionsekvationen och approximationstillförlitlighetsvärdet R2 i diagramområdet, aktivera kryssrutorna för visa ekvation på skärmen och placera approximationstillförlitlighetsvärdet (R^2) på diagrammet. För bättre visuell uppfattning ändrar vi typ, färg och tjocklek på de konstruerade trendlinjerna, för vilket vi använder fliken Visa i dialogrutan Trendlinjeformat (se fig. 3). Det resulterande diagrammet med tillagda trendlinjer visas i fig. 5.

För att få tabelldata om företagsvinster för varje trendlinje för 1995-2004. Låt oss använda trendlinjeekvationerna som presenteras i fig. 5. För att göra detta, i cellerna i området D3:F3, skriv in textinformation om typen av den valda trendlinjen: Linjär trend, Kvadratisk trend, Kubisk trend. Ange sedan den linjära regressionsformeln i cell D4 och kopiera denna formel med relativa referenser till cellområdet D5:D13 med hjälp av fyllningsmarkören. Det bör noteras att varje cell med en linjär regressionsformel från cellområdet D4:D13 har som argument en motsvarande cell från området A4:A13. På samma sätt, för kvadratisk regression, fyll cellområdet E4:E13, och för kubisk regression, fyll cellområdet F4:F13. Därmed har en prognos för företagets vinst för 2003 och 2004 sammanställts. med hjälp av tre trender. Den resulterande värdetabellen visas i fig. 6.

Problem 2

Bygg ett diagram.

Lägg till logaritmiska, potens- och exponentiella trendlinjer i diagrammet.

Härled ekvationerna för de erhållna trendlinjerna, såväl som tillförlitlighetsvärdena för approximationen R2 för var och en av dem.

Använd trendlinjeekvationerna för att få tabelldata om företagets vinst för varje trendlinje för 1995-2002.

Gör en prognos över företagets vinst för 2003 och 2004 med hjälp av dessa trendlinjer.

Lösningen på problemet

Genom att följa den metod som ges för att lösa problem 1 får vi ett diagram med logaritmiska, potens- och exponentiella trendlinjer tillagda (fig. 7). Därefter, med hjälp av de erhållna trendlinjeekvationerna, fyller vi i en värdetabell för företagets vinst, inklusive de förutsagda värdena för 2003 och 2004. (Fig. 8).

I fig. 5 och fig. det kan ses att modellen med en logaritmisk trend motsvarar det lägsta värdet av approximationstillförlitlighet

R2 = 0,8659

De högsta värdena på R2 motsvarar modeller med en polynomtrend: kvadratisk (R2 = 0,9263) och kubisk (R2 = 0,933).

Problem 3

Med tabellen med uppgifter om vinsten för ett biltransportföretag för 1995-2002, som ges i uppgift 1, måste du utföra följande steg.

Skaffa dataserier för linjära och exponentiella trendlinjer med TREND- och GROW-funktionerna.

Gör en prognos över företagets vinst för 2003 och 2004 med hjälp av TREND- och GROWTH-funktionerna.

Konstruera ett diagram för originaldata och den resulterande dataserien.

Lösningen på problemet

Låt oss använda arbetsbladet för uppgift 1 (se fig. 4). Låt oss börja med TREND-funktionen:

välj intervallet av celler D4:D11, som ska fyllas med värdena för TREND-funktionen som motsvarar de kända uppgifterna om företagets vinst;

Anropa kommandot Funktion från Infoga-menyn. I dialogrutan Function Wizard som visas väljer du TREND-funktionen från kategorin Statistical och klickar sedan på OK-knappen. Samma operation kan utföras genom att klicka på knappen (Infoga funktion) på standardverktygsfältet.

I dialogrutan Funktionsargument som visas anger du cellintervallet C4:C11 i fältet Known_values_y; i fältet Known_values_x - cellintervallet B4:B11;

För att få den angivna formeln att bli en matrisformel, använd tangentkombinationen + + .

Formeln vi skrev in i formelfältet kommer att se ut så här: =(TREND(C4:C11,B4:B11)).

Som ett resultat fylls intervallet av celler D4:D11 med motsvarande värden för TREND-funktionen (fig. 9).

Att göra en prognos över företagets vinst för 2003 och 2004. nödvändig:

välj intervallet av celler D12:D13 där värdena som förutspås av TREND-funktionen kommer att anges.

anrop TREND-funktionen och i dialogrutan Funktionsargument som visas anger du i fältet Known_values_y - cellintervallet C4:C11; i fältet Known_values_x - cellintervallet B4:B11; och i fältet New_values_x - cellintervallet B12:B13.

förvandla den här formeln till en matrisformel med tangentkombinationen Ctrl + Shift + Enter.

Den angivna formeln kommer att se ut som: =(TREND(C4:C11;B4:B11;B12:B13)), och cellintervallet D12:D13 kommer att fyllas med de förutsagda värdena för TREND-funktionen (se fig. 9).

Dataserien fylls på liknande sätt i med funktionen GROWTH, som används i analysen av olinjära beroenden och fungerar på exakt samma sätt som dess linjära motsvarighet TREND.

Figur 10 visar tabellen i formelvisningsläge.

För de initiala data och den erhållna dataserien, diagrammet som visas i fig. elva.

Problem 4

Med tabellen över data om mottagandet av ansökningar om tjänster från en biltransportföretags utskickstjänst för perioden från den 1:a till den 11:e i innevarande månad, måste du utföra följande åtgärder.

Hämta dataserier för linjär regression: med funktionerna SLOPE och INTERCEPT; med hjälp av LINEST-funktionen.

Få en serie data för exponentiell regression med hjälp av LGRFPRIBL-funktionen.

Med hjälp av ovanstående funktioner, gör en prognos om mottagandet av ansökningar till leveranstjänsten för perioden från den 12:e till den 14:e i innevarande månad.

Skapa ett diagram för den ursprungliga och mottagna dataserien.

Lösningen på problemet

Observera att, till skillnad från TREND- och GROWTH-funktionerna, är ingen av funktionerna listade ovan (SLOPE, INTERCEPT, LINEST, LGRFPRIB) regression. Dessa funktioner spelar bara en stödjande roll och bestämmer de nödvändiga regressionsparametrarna.

För linjära och exponentiella regressioner byggda med funktionerna SLOPE, INTERCEPT, LINEST, LGRFPRIB, är utseendet på deras ekvationer alltid känt, i motsats till linjära och exponentiella regressioner som motsvarar funktionerna TREND och GROWTH.

1 . Låt oss bygga en linjär regression med ekvationen:

y = mx+b

med funktionerna SLOPE och INTERCEPT, varvid regressionslutningen m bestäms av SLOPE-funktionen och den fria termen b av INTERCEPT-funktionen.

För att göra detta utför vi följande åtgärder:

skriv in den ursprungliga tabellen i cellområdet A4:B14;

värdet på parametern m kommer att bestämmas i cell C19. Välj lutningsfunktionen från kategorin Statistisk; ange intervallet för celler B4:B14 i fältet kända_värden_y och intervallet för celler A4:A14 i fältet kända_värden_x. Formeln kommer att matas in i cell C19: =SLUTNING(B4:B14,A4:A14);

Med en liknande teknik bestäms värdet av parameter b i cell D19. Och dess innehåll kommer att se ut så här: =SEGMENT(B4:B14,A4:A14). Således kommer värdena på parametrarna m och b som krävs för att konstruera en linjär regression att lagras i cellerna C19, D19, respektive;

Ange sedan den linjära regressionsformeln i cell C4 i formen: =$C*A4+$D. I den här formeln skrivs cellerna C19 och D19 med absoluta referenser (celladressen ska inte ändras vid eventuell kopiering). Det absoluta referenstecknet $ kan skrivas antingen från tangentbordet eller med F4-tangenten, efter att ha placerat markören på celladressen. Använd fyllhandtaget och kopiera den här formeln till cellområdet C4:C17. Vi erhåller den erforderliga dataserien (Fig. 12). På grund av att antalet förfrågningar är ett heltal bör du ställa in talformatet med antalet decimaler till 0 på Nummerfliken i fönstret Cellformat.

2 . Låt oss nu bygga en linjär regression som ges av ekvationen:

y = mx+b

med hjälp av LINEST-funktionen.

För detta:

Ange LINEST-funktionen som en matrisformel i cellområdet C20:D20: =(LINEST(B4:B14,A4:A14)). Som ett resultat får vi värdet av parametern m i cell C20, och värdet av parametern b i cell D20;

ange formeln i cell D4: =$C*A4+$D;

kopiera denna formel med fyllningsmarkören till cellområdet D4:D17 och få önskad dataserie.

3 . Vi bygger en exponentiell regression med ekvationen:

med LGRFPRIBL-funktionen utförs det på liknande sätt:

I cellområdet C21:D21 anger vi LGRFPRIBL-funktionen som en matrisformel: =( LGRFPRIBL (B4:B14,A4:A14)). I detta fall kommer värdet på parametern m att bestämmas i cell C21, och värdet på parameter b kommer att bestämmas i cell D21;

formeln skrivs in i cell E4: =$D*$C^A4;

med fyllningsmarkören kopieras denna formel till cellområdet E4:E17, där dataserien för exponentiell regression kommer att finnas (se fig. 12).

I fig. Figur 13 visar en tabell där du kan se de funktioner vi använder med de nödvändiga cellområdena, samt formler.

Magnitud R 2 kallad determinationskoefficient.

Uppgiften att konstruera ett regressionsberoende är att hitta vektorn av koefficienter m för modell (1) vid vilken koefficienten R får maximalt värde.

För att bedöma signifikansen av R används Fishers F-test, beräknat med formeln

Var n- provstorlek (antal experiment);

k är antalet modellkoefficienter.

Om F överskrider något kritiskt värde för data n Och k och den accepterade konfidenssannolikheten, då anses värdet på R vara signifikant. Tabeller med kritiska värden för F ges i referensböcker om matematisk statistik.

Således bestäms betydelsen av R inte bara av dess värde, utan också av förhållandet mellan antalet experiment och antalet koefficienter (parametrar) i modellen. Faktum är att korrelationsförhållandet för n=2 för en enkel linjär modell är lika med 1 (en enda rät linje kan alltid dras genom 2 punkter på ett plan). Men om experimentdata är slumpvariabler bör ett sådant värde på R litas på med stor försiktighet. Vanligtvis, för att erhålla signifikant R och tillförlitlig regression, strävar de efter att säkerställa att antalet experiment avsevärt överstiger antalet modellkoefficienter (n>k).

För att bygga en linjär regressionsmodell behöver du:

1) förbered en lista med n rader och m kolumner som innehåller experimentella data (kolumn som innehåller utdatavärdet Y måste vara antingen först eller sist i listan); Låt oss till exempel ta data från föregående uppgift, lägga till en kolumn som heter "Periodnummer", numrera periodnumren från 1 till 12. (detta kommer att vara värdena X)

2) gå till menyn Data/Dataanalys/Regression

Om alternativet "Dataanalys" i menyn "Verktyg" saknas, bör du gå till "Tillägg" i samma meny och kryssa i kryssrutan "Analyspaket".

3) i dialogrutan "Regression", ställ in:

· inmatningsintervall Y;

· inmatningsintervall X;

· utdataintervall - den övre vänstra cellen i intervallet där beräkningsresultaten kommer att placeras (det rekommenderas att placera dem på ett nytt kalkylblad);