Minste kvadratmetode i Excel. Regresjonsanalyse

Minste kvadraters metode (OLS) refererer tilområdet for regresjonsanalyse. Den har mange applikasjoner, siden den tillater en omtrentlig representasjon av en gitt funksjon av andre enklere. OLS kan være ekstremt nyttig i behandlingen av observasjoner, og det brukes aktivt til å estimere noen mengder fra resultatene av målinger av andre som inneholder tilfeldige feil. Denne artikkelen viser deg hvordan du implementerer beregninger av minst kvadrater i Excel.

Erklæring om problemet ved hjelp av et spesifikt eksempel

Anta at det er to eksponenter X og Y.Videre er Y avhengig av X. Siden OLS er av interesse for oss med tanke på regresjonsanalyse (i Excel implementeres metodene ved hjelp av innebygde funksjoner), så bør du umiddelbart gå videre til å vurdere et spesifikt problem.

Så la X være butikklokalet i en matbutikk, målt i kvadratmeter, og Y - den årlige omsetningen, målt i millioner rubler.

Det er nødvendig å lage en prognose for hvilken omsetning (Y)vil være i butikken hvis den har en eller annen butikkplass. Tydeligvis øker funksjonen Y = f (X) siden hypermarkedet selger flere varer enn boden.

Noen få ord om korrektheten av de første dataene som ble brukt for prediksjon

La oss si at vi har en tabell bygget på data for n -butikker.

Ifølge matematisk statistikk, resultatenevil være mer eller mindre korrekt hvis data om minst 5-6 objekter blir undersøkt. I tillegg kan du ikke bruke "unormale" resultater. Spesielt kan en liten elitebutikk ha en omsetning som er mange ganger større enn omsetningen til store butikker i klassen "masmarket".

Essensen i metoden

Tabelldata kan plottes på det kartesiske planet som punkt M₁ (x₁, y₁),… M_n (x_n, y_n). Nå vil løsningen på problemet bli redusert til valg av en tilnærmingsfunksjon y = f (x) med en graf som går så nært som mulig til punktene M_1, M_{2, ..}M_n.

Selvfølgelig kan du bruke det høye polynometgrad, men dette alternativet er ikke bare vanskelig å implementere, men også ganske enkelt feil, siden det ikke vil gjenspeile hovedtrenden som må oppdages. Den rimeligste løsningen er å finne den rette linjen y = ax + b, som best tilnærmer eksperimentelle data, eller rettere sagt koeffisientene a og b.

Nøyaktighetsvurdering

For enhver tilnærming er en vurdering av nøyaktigheten av spesiell betydning. Vi betegner med e_og forskjell (avvik) mellom funksjonelle og eksperimentelle verdier for punkt x_og, dvs. e_og = y_og - f (x_og).

Åpenbart, for å estimere nøyaktigheten av tilnærmingensummen av avvik kan brukes, det vil si at når du velger en rett linje for en omtrentlig representasjon av avhengigheten til X på Y, bør det foretrekkes den med den minste verdien av summen e_og på alle punkter vurdert. Imidlertid er ikke alt så enkelt, siden sammen med positive avvik vil negative praktisk talt være til stede.

Du kan løse problemet ved å bruke avviksmodulereller rutene deres. Den siste metoden er den mest brukte. Den brukes på mange områder, inkludert regresjonsanalyse (Excel implementerer den med to innebygde funksjoner), og har lenge bevist sin verdi.

Minste kvadratmetode

Excel er kjent for å ha en innebygdautosum -funksjon som beregner verdiene til alle verdiene i det valgte området. Dermed hindrer ingenting oss i å beregne verdien av uttrykket (f₁² + e₂² + e₃²+ ... e_n²).

I matematisk notasjon ser det slik ut:

Siden beslutningen først ble tatt om å benytte en rett linje, har vi:

Dermed reduseres problemet med å finne den rette linjen som best beskriver den spesifikke avhengigheten av mengdene X og Y til å beregne minimum av en funksjon av to variabler:

Dette krever likhet med null de partielle derivatene med hensyn til de nye variablene a og b, og å løse et primitivt system bestående av to ligninger med 2 ukjente av formen:

Etter noen enkle transformasjoner, inkludert å dele med 2 og manipulere summene, får vi:

Ved å løse det, for eksempel etter Cramers metode, får vi et stasjonært punkt med noen koeffisienter a^* og b^*... Dette er minimum, det vil si for å forutsi hvilken omsetning butikken vil ha for et bestemt område, er den rette linjen y = a egnet^*x + b^*, som er en regresjonsmodell fordet aktuelle eksemplet. Selvfølgelig vil det ikke tillate deg å finne et eksakt resultat, men det vil hjelpe deg med å få en ide om hvorvidt det vil lønne seg å kjøpe en butikk på kreditt for et bestemt område.

Slik implementerer du minst kvadratmetoden i Excel

Excel har en funksjon for å beregne verdienav OLS. Den har følgende form: "TREND" (kjente Y -verdier; kjente X -verdier; nye X -verdier; konst.). La oss bruke formelen for å beregne OLS i Excel i tabellen vår.

For å gjøre dette, i cellen der det skal væreresultatet av beregningen med metoden for minst kvadrater i Excel vises, skriv inn "=" -tegnet og velg "TREND" -funksjonen. Fyll ut de aktuelle feltene i vinduet som åpnes, og merk:

• rekke kjente verdier for Y (i dette tilfellet data for omsetning);
• område x₁,… X_n, dvs. størrelsen på butikklokalet;
• både kjente og ukjente verdier av x, som du trenger for å finne ut størrelsen på omsetningen (for informasjon om plasseringen i regnearket, se nedenfor).

I tillegg inneholder formelen den boolske variabelen "Const". Hvis du angir 1 i det tilsvarende feltet, vil dette bety at beregninger bør utføres, forutsatt at b = 0.

Hvis du trenger å vite prognosen for mer enn én verdi av x, bør du ikke trykke "Enter" etter å ha angitt formelen, men du må skrive kombinasjonen "Shift" + "Control" + "Enter" på tastaturet ("Tast inn").

Noen funksjoner

Regresjonsanalyse kan til og med være tilgjengeligtekanner. Excel -formelen for å forutsi verdien av en rekke ukjente variabler - "TREND" - kan brukes selv av de som aldri har hørt om metoden med minst kvadrater. Det er nok bare å kjenne noen av funksjonene i arbeidet hennes. Spesielt:

• Hvis du plasserer området med kjente verdiervariabel y i en rad eller kolonne, vil hver rad (kolonne) med kjente x -verdier bli oppfattet av programmet som en egen variabel.
• Hvis "TREND" -vinduet ikke inneholder et område medkjent x, og når du bruker funksjonen i Excel, vil programmet betrakte det som en matrise som består av heltall, hvis tall tilsvarer området med de gitte verdiene til variabelen y.
• For å få en rekke "forutsagte" verdier som en utgang, må trenduttrykket legges inn som en matriseformel.
• Hvis ingen nye verdier av x er spesifisert, så er funksjonen"TREND" anser dem som like kjente. Hvis de ikke er spesifisert, blir matrise 1 tatt som et argument; 2; 3; 4;…, som står i forhold til området med de allerede angitte parameterne y.
• Området som inneholder de nye x -verdiene måbestår av de samme eller flere rader eller kolonner som området med de gitte y -verdiene. Med andre ord bør det stå i samsvar med de uavhengige variablene.
• I en matrise med kjente x -verdier kaninneholder flere variabler. Imidlertid, hvis vi bare snakker om en, er det nødvendig at områdene med de gitte verdiene x og y står i forhold til. Når det gjelder flere variabler, vil du at området med de angitte y -verdiene skal passe i en kolonne eller én rad.

FORECAST -funksjon

Regresjonsanalyse i Excel implementeres medbruker flere funksjoner. En av dem kalles "PROGNOS". Det ligner på "TREND", det vil si at det gir resultatet av beregninger ved bruk av metoden for minst kvadrater. Imidlertid bare for ett X, som Y -verdien er ukjent for.

Nå kjenner du formlene i Excel for dummies som lar deg forutsi fremtidig verdi av en gitt indikator i henhold til en lineær trend.