Mindste kvadrater metode i Excel. Regressionsanalyse

Least Squares (LSM) -metoden henviser tilregression analyse sfære. Det har mange anvendelser, da det tillader en omtrentlig repræsentation af en given funktion af andre enklere. OLS kan være yderst nyttig til behandling af observationer, og det bruges aktivt til at estimere nogle værdier fra resultaterne af målinger af andre, der indeholder tilfældige fejl. I denne artikel lærer du, hvordan du implementerer beregninger af mindst kvadrater i Excel.

Redegørelse for problemet på et konkret eksempel

Antag, at der er to eksponenter X og Y.Y afhænger desuden af ​​X. Da MNC'er er af interesse for os set ud fra regressionsanalyse (i Excel implementeres dens metoder ved hjælp af indbyggede funktioner), er det værd med det samme at overveje et specifikt problem.

Så lad X være butikspladsen i købmanden, målt i kvadratmeter, og Y den årlige omsætning, defineret i millioner rubler.

Det kræves at lave en prognose, hvilken omsætning (Y)vil være i butikken, hvis det har et eller andet detailrum. Naturligvis øges funktionen Y = f (X), da hypermarkedet sælger flere varer end båsen.

Et par ord om rigtigheden af ​​de kildedata, der er brugt til forudsigelse

Antag, at vi har en tabel bygget efter data til n butikker.

I henhold til matematisk statistik er resultaternevil være mere eller mindre korrekte, hvis dataene på mindst 5-6 objekter undersøges. Derudover kan "unormale" resultater ikke bruges. Især kan en elite-lille butik have en omsætning, der er mange gange større end omsætningen i store detailforretninger i klassen "masmarket".

Essensen af ​​metoden

Tabeldataene kan repræsenteres på det kartesiske plan som punkterne M₁ (med₁, y₁), ... M_n (med_n, y_n). Nu reduceres løsningen af ​​problemet til valg af tilnærmelsesfunktionen y = f (x), som har en graf, der passerer så tæt som muligt på punktene M_1, M_{2, ..}M_n.

Конечно, можно использовать многочлен высокой grader, men denne mulighed er ikke kun vanskelig at implementere, men simpelthen forkert, da den ikke afspejler den vigtigste tendens, der skal registreres. Den mest rimelige løsning er at finde den rette linje y = ax + b, der bedst tilnærmer de eksperimentelle data, eller rettere sagt koefficienterne - a og b.

Nøjagtighedsvurdering

I enhver tilnærmelse er estimeringen af ​​dens nøjagtighed af særlig betydning. Betegnes ved e_og forskel (afvigelse) mellem funktionelle og eksperimentelle værdier for punkt x_ogdvs. e_og = y_og - f (x_og).

Очевидно, что для оценки точности аппроксимации summen af ​​afvigelser kan anvendes, dvs. når man vælger en lige linje til en omtrentlig repræsentation af afhængigheden af ​​X af Y, skal man foretrække den, der har den mindste værdi af summen e_og på alle de betragtede punkter. Imidlertid er ikke alt så enkelt, da sammen med positive afvigelser praktisk talt negative også vil være til stede.

Du kan løse problemet ved hjælp af afvigelsesmodulerneeller deres firkanter. Den sidstnævnte metode blev mest anvendt. Det bruges på mange områder, inklusive regressionsanalyse (i Excel, det implementeres ved hjælp af to indbyggede funktioner), og det har længe vist sig at være effektivt.

Mindst kvadrater metode

I Excel er der som bekendt en indbyggetautosum-funktion, der giver dig mulighed for at beregne værdierne for alle værdier i det valgte interval. Således vil intet forhindre os i at beregne værdien af ​​udtrykket (f.eks₁² + e₂² + e₃²+ ... e_n²).

I en matematisk notation har dette formen:

Da beslutningen først blev truffet om tilnærmelse ved hjælp af en lige linje, har vi:

Problemet med at finde den linje, der bedst beskriver den specifikke afhængighed af mængderne X og Y, kommer således til at beregne minimumsfunktionen af ​​to variabler:

For at gøre dette er vi nødt til at indstille partielle derivater med hensyn til de nye variabler a og b til nul og løse et primitivt system bestående af to ligninger med 2 ukendte af formen:

Efter nogle enkle transformationer, herunder deling med 2 og manipulation af summerne, får vi:

Ved at løse den, for eksempel ved hjælp af Cramer-metoden, opnår vi et stationært punkt med visse koefficienter a^* og b^*. Dette er minimum, dvs. for at forudsige, hvad butikens omsætning vil være for et givet område, den rette linje y = a^*x + b^*repræsenterer en regressionsmodel fordet pågældende eksempel. Selvfølgelig vil det ikke give dig mulighed for at finde det nøjagtige resultat, men det vil hjælpe med at få en idé om, hvorvidt købet vil betale sig med kredit i butikken i et bestemt område.

Sådan implementeres en metode med mindst kvadrater i Excel

В "Эксель" имеется функция для расчета значения ifølge OLS. Det har følgende form: “TREND” (kendte værdier for Y; kendte værdier af X; nye værdier af X; konst.). Vi anvender formlen til beregning af OLS i Excel på vores tabel.

For at gøre dette i den celle, hvor der skal væreresultatet af beregningen efter metoden med de mindste firkanter vises i Excel, vi indtaster tegnet “=” og vælger funktionen “TREND”. Udfyld de relevante felter i det vindue, der åbnes, og fremhæv:

• intervallet af kendte værdier for Y (i dette tilfælde data for omsætning);
• område x₁,… X_ndvs. størrelsen på detailområdet;
• både kendte og ukendte værdier af x, som du har brug for at finde ud af størrelsen på omsætningen (information om deres placering på regnearket se nedenfor).

Desuden indeholder formlen den boolske variabel "Const". Hvis du indtaster 1 i det tilsvarende felt, betyder det, at beregninger skal udføres, forudsat at b = 0.

Hvis du har brug for at kende prognosen for mere end én værdi af x, skal du efter indtastning af formlen ikke trykke på "Enter", men du skal skrive kombinationen "Shift" + "Control" + "Enter" på tastaturet ("Gå ind").

Nogle funktioner

Regressionsanalyse kan endda være tilgængeligtekande. Excel -formlen til at forudsige værdien af ​​en vifte af ukendte variabler - "TREND" - kan bruges selv af dem, der aldrig har hørt om metoden med mindst kvadrater. Det er nok bare at kende nogle af funktionerne i hendes arbejde. I særdeleshed:

• Hvis du placerer området med kendte værdiervariabel y i en række eller kolonne, så vil hver række (kolonne) med kendte x -værdier blive opfattet af programmet som en separat variabel.
• Hvis vinduet "TREND" ikke indeholder et område medkendt x, så vil programmet i tilfælde af at bruge funktionen i Excel betragte det som en matrix bestående af heltal, hvis nummer svarer til intervallet med de givne værdier for variablen y.
• For at få en matrix med "forudsagte" værdier som output, skal trendudtrykket indtastes som en matrixformel.
• Hvis der ikke er angivet nye værdier for x, er funktionen"TREND" anser dem for lige kendte. Hvis de ikke er angivet, tages array 1 som et argument; 2; 3; 4;…, der står i rimeligt forhold til området med de allerede givne parametre y.
• Området indeholdende de nye x -værdier skalbestår af de samme eller flere rækker eller kolonner som området med de givne y -værdier. Med andre ord bør det stå i rimeligt forhold til de uafhængige variabler.
• I en matrix med kendte x -værdier kanindeholder flere variabler. Men hvis vi kun taler om en, er det påkrævet, at intervallerne med de givne værdier på x og y står i rimeligt forhold. I tilfælde af flere variabler, vil du have, at området med de givne y -værdier skal passe i en kolonne eller en række.

FORECAST funktion

Regressionsanalyse i Excel implementeres medved hjælp af flere funktioner. En af dem kaldes "FORSPECTS". Det ligner "TREND", det vil sige, det giver resultatet af beregninger ved hjælp af metoden med mindst kvadrater. Dog kun for et X, for hvilket Y -værdien er ukendt.

Nu kender du formlerne i Excel til dummies, der giver dig mulighed for at forudsige den fremtidige værdi af en given indikator i henhold til en lineær tendens.