Betydningen av f i excel-regresjon. regresjonsanalyse i excel

MS Excel-pakken gir mulighet for konstruksjon av en lineær regresjonsligning mest gjør jobben veldig raskt. Det er viktig å forstå hvordan man tolker resultatene.

Krever tillegg for å fungere Analysepakke, som må være aktivert i menyelementet Tjeneste\Tillegg

I Excel 2007, for å aktivere analysepakken, klikk Gå til blokk Excel-alternativer ved å trykke på knappen til venstre øvre hjørne, og deretter knappen Excel-alternativer» nederst i vinduet:

For å bygge en regresjonsmodell, velg elementet Tjeneste\Dataanalyse\Regresjon. (I Excel 2007 er denne modusen i Data/Data Analyse/Regresjon). Det vises en dialogboks som må fylles ut:

1) Inndataintervall Y¾ inneholder en lenke til celler som inneholder verdiene til det resulterende attributtet y. Verdier må være i en kolonne;

2) Inndataintervall X¾ inneholder en lenke til celler som inneholder verdiene til faktorene. Verdier må være i kolonner;

3) Signer Tagger angi om de første cellene inneholder forklarende tekst (dataetiketter);

4) Pålitelighetsnivå¾ er konfidensnivået, som antas å være 95 % som standard. Hvis denne verdien ikke passer deg, må du aktivere denne funksjonen og angi den nødvendige verdien;

5) Signer Null konstant er inkludert hvis det er nødvendig å konstruere en ligning der den frie variabelen ;

6) Utgangsalternativer bestemme hvor resultatene skal plasseres. Standard byggemodus Nytt arbeidsark;

7) Blokker Rester lar deg inkludere produksjonen av rester og konstruksjonen av grafene deres.

Resultatet er informasjon som inneholder alle nødvendig informasjon og gruppert i tre blokker: Regresjonsstatistikk , Analyse av varianter , Saldouttak. La oss vurdere dem mer detaljert.

1. Regresjonsstatistikk:

flere R er definert av formelen ( Pearson korrelasjonskoeffisient);

R (bestemmelseskoeffisient);

Normalisert R-kvadrat beregnes av formelen (brukes til multippel regresjon);

standard feil S beregnet med formelen ;

Observasjoner ¾ er mengden data n.

2. Analyse av varianter, linje Regresjon:

Parameter df er lik m(antall sett med faktorer x);

Parameter SS bestemmes av formelen;

Parameter MS bestemmes av formelen;

Statistikk F bestemmes av formelen;

Betydning F. Hvis det resulterende tallet overstiger , er hypotesen akseptert (ingen lineær sammenheng), ellers aksepteres hypotesen (det er en lineær sammenheng).

3. Analyse av varianter, linje Rest:

Parameter df er lik ;

Parameter SS bestemmes av formelen ;

Parameter MS bestemmes av formelen.

4. Analyse av varianter, linje Total inneholder summen av de to første kolonnene.

5. Analyse av varianter, linje Y-kryss inneholder verdien av koeffisient , standardfeil og t-statistikk.

P-verdi ¾ er verdien av signifikansnivåer som tilsvarer det beregnede t- statistikere. Bestemt av STUDIST( t-statistikk; ). Hvis en P-verdi overstiger , da er den tilsvarende variabelen statistisk insignifikant og kan ekskluderes fra modellen.

nederste 95 % og Topp 95 %¾ er den nedre og øvre grensen på 95 prosent konfidensintervaller for koeffisienter teoretisk ligning lineær regresjon. Hvis du blokkerer verdien i dataregistreringen selvtillitsnivå ble forlatt som standard, vil de to siste kolonnene duplisere de forrige. Hvis brukeren har angitt en egendefinert konfidensverdi, inneholder de to siste kolonnene de nedre og øvre grenseverdiene for det angitte konfidensnivået.

6. Analyse av varianter, radene inneholder verdiene til koeffisientene, standard feil, t- statistiker, P-verdier og konfidensintervaller for de tilsvarende .

7. Blokker Saldouttak inneholder verdiene til det spådde y(i vår notasjon er det ) og rester .

28 okt

God ettermiddag, kjære blogglesere! I dag skal vi snakke om ikke-lineære regresjoner. Løsning lineære regresjoner kan sees HER.

Denne metoden brukes hovedsakelig i økonomisk modellering og prognoser. Formålet er å observere og identifisere forholdet mellom to indikatorer.

Hovedtyper ikke-lineære regresjoner er:

polynom (kvadratisk, kubisk);
hyperbolsk;
makt;
demonstrasjon;
logaritmisk.

Forskjellige kombinasjoner kan også brukes. For eksempel, for tidsserieanalyser innen bank, forsikring, demografiske studier, brukes Gompzer-kurven, som er en type logaritmisk regresjon.

Ved prognoser ved bruk av ikke-lineære regresjoner er hovedsaken å finne ut korrelasjonskoeffisienten, som vil vise oss om det er nært forhold honning med to parametere eller ikke. Som regel, hvis korrelasjonskoeffisienten er nær 1, er det en sammenheng, og prognosen vil være ganske nøyaktig. en til viktig element ikke-lineær regresjon er gjennomsnittet relativ feil (MEN ) hvis det er i intervallet<8…10%, значит модель достаточно точна.

På dette vil vi kanskje avslutte den teoretiske blokken og gå videre til praktiske beregninger.

Vi har en tabell over bilsalg for en periode på 15 år (la oss betegne det som X), antall måletrinn vil være argumentet n, vi har også inntekter for disse periodene (la oss betegne det Y), vi må forutsi hva inntektene vil være i fremtiden. La oss bygge følgende tabell:

For studien må vi løse ligningen (avhengigheten av Y på X): y=ax 2 +bx+c+e. Dette er en par-kvadratisk regresjon. I dette tilfellet bruker vi metoden med minste kvadrater for å finne ut de ukjente argumentene - a, b, c. Det vil føre til et system med algebraiske ligninger av formen:

For å løse dette systemet bruker vi for eksempel Cramer-metoden. Vi ser at summene som inngår i systemet er koeffisientene til de ukjente. For å beregne dem legger vi til flere kolonner i tabellen (D, E, F, G, H) og signerer dem i henhold til betydningen av beregningene - i kolonne D kvadrerer vi x, i E til en kube, i F til 4. potens, i G ganger vi indikatorene x og y, i H kvadrater vi x og ganger med y.

Det vil vise seg en tabell med skjemaet fylt med de som er nødvendige for å løse ligningen.

La oss lage en matrise EN et system som består av koeffisienter for ukjente på venstre side av ligningene. La oss legge den i celle A22 og kalle den " A=". Vi følger ligningssystemet som vi har valgt for å løse regresjonen.

Det vil si at i celle B21 må vi plassere summen av kolonnen der vi hevet indikatoren X til fjerde potens - F17. La oss bare referere til cellen - "=F17". Deretter trenger vi summen av kolonnen der X ble kubert - E17, så går vi strengt i henhold til systemet. Derfor må vi fylle ut hele matrisen.

I samsvar med Cramers algoritme vil vi samle en matrise A1, lik A, der i stedet for elementene i den første kolonnen, skal elementene i de høyre delene av systemets likninger plasseres. Det vil si, summen av kolonne X i kvadrat ganger Y, summen av kolonne XY og summen av kolonne Y.

Vi vil også trenge to matriser til - la oss kalle dem A2 og A3 der andre og tredje kolonne vil bestå av koeffisientene til høyresiden av ligningene. Bildet blir slik.

Etter den valgte algoritmen må vi beregne verdiene til determinantene (determinantene, D) til de oppnådde matrisene. La oss bruke MOPRED-formelen. Resultatene vil bli plassert i cellene J21:K24.

Vi vil beregne koeffisientene til ligningen i henhold til Cramer i cellene overfor de tilsvarende determinantene i henhold til formelen: en(i celle M22) - "=K22/K21"; b(i celle M23) — "=K23/K21"; Med(i celle M24) - "=K24 / K21".

Vi får vår ønskede kvadratiske regresjonsligning:

y=-0,074x2 +2,151x+6,523

La oss estimere stramheten til det lineære forholdet ved hjelp av korrelasjonsindeksen.

For å beregne, legg til en ekstra kolonne J i tabellen (la oss kalle den y*). Beregningen vil være som følger (i henhold til regresjonsligningen vi mottok) - "=$m$22*B2*B2+$M$23*B2+$M$24". La oss sette den i celle J2. Alt som gjenstår er å dra autofyll-markøren ned til celle J16.

For å beregne summene (Y-Y gjennomsnitt) 2, legg til kolonnene K og L i tabellen med de tilsvarende formlene. Vi beregner gjennomsnittet for Y-kolonnen ved å bruke AVERAGE-funksjonen.

I celle K25 plasserer vi formelen for beregning av korrelasjonsindeksen - "=ROOT(1-(K17/L17))".

Vi ser at verdien på 0,959 er veldig nær 1, noe som betyr at det er et nært ikke-lineært forhold mellom salg og år.

Det gjenstår å evaluere kvaliteten på tilpasningen av den oppnådde kvadratiske regresjonsligningen (bestemmelsesindeks). Det beregnes ved formelen til kvadratet av korrelasjonsindeksen. Det vil si at formelen i celle K26 vil være veldig enkel - "=K25*K25".

Koeffisienten på 0,920 er nær 1, noe som indikerer en god passform.

Det siste trinnet er å beregne den relative feilen. La oss legge til en kolonne og skrive inn formelen der: “=ABS((C2-J2)/C2), ABS — modul, absolutt verdi. La oss dra markøren ned og i celle M18 vil vi vise gjennomsnittsverdien (GJENNOMSNITT), tilordne prosentformatet til cellene. Resultatet oppnådd - 7,79 % er innenfor akseptable feilverdier<8…10%. Значит вычисления достаточно точны.

Om nødvendig kan vi bygge en graf basert på de oppnådde verdiene.

En eksempelfil er vedlagt - LINK!

Kategorier:// datert 28. oktober 2017

Regresjonsanalyse er en av de mest populære metodene for statistisk forskning. Den kan brukes til å bestemme graden av påvirkning av uavhengige variabler på den avhengige variabelen. Funksjonaliteten til Microsoft Excel har verktøy utviklet for å utføre denne typen analyser. La oss ta en titt på hva de er og hvordan du bruker dem.

Koble til analysepakken

Men for å bruke funksjonen som lar deg utføre regresjonsanalyse, må du først og fremst aktivere analysepakken. Først da vil verktøyene som er nødvendige for denne prosedyren vises på Excel-båndet.

Flytt til "Fil"-fanen.
Gå til "Innstillinger"-delen.
Vinduet for Excel-alternativer åpnes. Gå til underseksjonen "Tillegg".
Helt nederst i vinduet som åpnes, omorganiserer vi bryteren i «Management»-blokken til «Excel Add-ins»-posisjonen, hvis den er i en annen posisjon. Klikk på "Go"-knappen.
Excel-tilleggsvinduet åpnes. Kryss av i boksen ved siden av "Analysepakke". Klikk på "OK"-knappen.

Nå, når vi går til fanen "Data", på båndet i verktøyblokken "Analyse", vil vi se en ny knapp - "Dataanalyse".

Typer regresjonsanalyse

Det finnes flere typer regresjoner:

parabolsk;
makt;
logaritmisk;
eksponentiell;
demonstrasjon;
hyperbolsk;
lineær regresjon.

Vi vil snakke mer detaljert om implementeringen av den siste typen regresjonsanalyse i Excel senere.

Lineær regresjon i Excel

Nedenfor er som eksempel en tabell som viser gjennomsnittlig daglig lufttemperatur på gaten, og antall butikkkunder for tilsvarende arbeidsdag. La oss finne ut ved hjelp av regresjonsanalyse nøyaktig hvordan værforhold i form av lufttemperatur kan påvirke oppmøtet til en detaljhandel.

Den generelle lineære regresjonsligningen ser slik ut: Y = a0 + a1x1 + ... + axk. I denne formelen betyr Y variabelen som vi prøver å studere påvirkning av faktorer. I vårt tilfelle er dette antallet kjøpere. Verdien av x er de ulike faktorene som påvirker variabelen. a-parametrene er regresjonskoeffisientene. Det vil si at de bestemmer betydningen av en bestemt faktor. Indeks k angir det totale antallet av de samme faktorene.

Analyseresultater analyse

Resultatene av regresjonsanalysen vises i form av en tabell på stedet angitt i innstillingene.

En av hovedindikatorene er R-torget. Det indikerer kvaliteten på modellen. I vårt tilfelle er denne koeffisienten 0,705 eller omtrent 70,5%. Dette er et akseptabelt kvalitetsnivå. Et forhold mindre enn 0,5 er dårlig.

En annen viktig indikator er plassert i cellen i skjæringspunktet mellom "Y-skjæringslinjen" og kolonnen "Koeffisienter". Her er det angitt hvilken verdi Y vil ha, og i vårt tilfelle er dette antall kjøpere, med alle andre faktorer lik null. I denne tabellen er denne verdien 58,04.

Verdien i skjæringspunktet mellom kolonnen "Variabel X1" og "Koeffisienter" viser nivået av avhengighet av Y på X. I vårt tilfelle er dette nivået av avhengighet av antall butikkkunder på temperatur. En koeffisient på 1,31 regnes som en ganske høy indikator på innflytelse.

Som du kan se, er det ganske enkelt å lage en regresjonsanalysetabell ved hjelp av Microsoft Excel. Men bare en trent person kan jobbe med dataene som er oppnådd ved utgangen, og forstå essensen deres.

Vi er glade for at vi kunne hjelpe deg med å løse problemet.

Still spørsmålet ditt i kommentarene, og beskriv i detalj essensen av problemet. Ekspertene våre vil prøve å svare så raskt som mulig.

Har denne artikkelen hjulpet deg?

Den lineære regresjonsmetoden lar oss beskrive en rett linje som passer best til en rekke ordnede par (x, y). Ligningen for en rett linje, kjent som den lineære ligningen, er gitt nedenfor:

ŷ er den forventede verdien av y for en gitt verdi av x,

x - uavhengig variabel,

a - segment på y-aksen for en rett linje,

b er helningen til den rette linjen.

I figuren nedenfor er dette konseptet representert grafisk:

Figuren over viser en linje beskrevet av ligningen ŷ =2+0,5x. Segmentet på y-aksen er skjæringspunktet for linjen med y-aksen; i vårt tilfelle er a = 2. Helningen til linjen, b, forholdet mellom linjestigning og linjelengde, har en verdi på 0,5. En positiv helning betyr at linjen stiger fra venstre til høyre. Hvis b = 0, er linjen horisontal, noe som betyr at det ikke er noen sammenheng mellom de avhengige og uavhengige variablene. Med andre ord, endring av verdien av x påvirker ikke verdien av y.

ŷ og y er ofte forvekslet. Grafen viser 6 ordnede par med punkter og en linje, i henhold til den gitte ligningen

Denne figuren viser punktet som tilsvarer det ordnede paret x = 2 og y = 4. Merk at forventet verdi av y i henhold til linjen ved X= 2 er ŷ. Vi kan bekrefte dette med følgende ligning:

ŷ = 2 + 0,5х =2 +0,5(2) =3.

Y-verdien er det faktiske punktet, og ŷ-verdien er den forventede y-verdien ved å bruke en lineær ligning for en gitt x-verdi.

Neste trinn er å bestemme den lineære ligningen som passer best med settet med ordnede par, vi snakket om dette i forrige artikkel, hvor vi bestemte formen til ligningen ved hjelp av minste kvadraters metode.

Bruke Excel til å definere lineær regresjon

For å bruke regresjonsanalyseverktøyet innebygd i Excel, må du aktivere tillegget Analysepakke. Du finner den ved å klikke på fanen Fil –> Alternativer(2007+), i dialogboksen som vises Alternativerutmerke gå til fanen Tillegg. I felt Styre velge tilleggutmerke og klikk Gå. I vinduet som vises, merk av i boksen ved siden av analysepakke, klikk OK.

I fanen Data i en gruppe Analyse en ny knapp vises Dataanalyse.

For å demonstrere hvordan tillegget fungerer, la oss bruke dataene fra forrige artikkel, der en fyr og en jente deler et bord på badet. Skriv inn dataene for vårt badeksempel i kolonne A og B på et blankt ark.

Gå til fanen Data, i en gruppe Analyse klikk Dataanalyse. I vinduet som vises Dataanalyseå velge Regresjon som vist i figuren og klikk OK.

Angi de nødvendige regresjonsparametrene i vinduet Regresjon, som det vises på bildet:

Klikk OK. Figuren nedenfor viser resultatene som er oppnådd:

Disse resultatene er i samsvar med de vi oppnådde ved uavhengige beregninger i forrige artikkel.

Regresjonsanalyse er en statistisk forskningsmetode som lar deg vise en parameters avhengighet av en eller flere uavhengige variabler. I pre-datamaskinen var bruken ganske vanskelig, spesielt når det gjaldt store datamengder. I dag, etter å ha lært hvordan du bygger en regresjon i Excel, kan du løse komplekse statistiske problemer på bare et par minutter. Nedenfor er spesifikke eksempler fra økonomifeltet.

Typer regresjon

Selve konseptet ble introdusert i matematikk av Francis Galton i 1886. Regresjon skjer:

lineær;
parabolsk;
makt;
eksponentiell;
hyperbolsk;
demonstrativ;
logaritmisk.

Eksempel 1

Vurder problemet med å bestemme avhengigheten av antall pensjonerte teammedlemmer av gjennomsnittslønnen ved 6 industribedrifter.

En oppgave. Ved seks virksomheter analyserte vi gjennomsnittlig månedslønn og antall ansatte som sluttet av egen vilje. I tabellform har vi:

For problemet med å bestemme avhengigheten av antall permitterte arbeidere av gjennomsnittslønnen ved 6 bedrifter, har regresjonsmodellen formen av ligningen Y = a0 + a1 × 1 + ... + akxk, hvor хi er påvirkende variabler, ai er regresjonskoeffisientene, og k er antall faktorer.

For denne oppgaven er Y indikatoren på ansatte som sluttet, og den påvirkningsfaktoren er lønnen, som vi betegner med X.

Bruke egenskapene til regnearket "Excel"

Regresjonsanalyse i Excel må innledes med bruk av innebygde funksjoner på tilgjengelige tabelldata. For disse formålene er det imidlertid bedre å bruke det svært nyttige tillegget "Analysis Toolkit". For å aktivere den trenger du:

fra "Fil"-fanen, gå til "Alternativer"-delen;
i vinduet som åpnes, velg linjen "Tillegg";
klikk på "Go"-knappen nederst, til høyre for "Management"-linjen;
merk av i boksen ved siden av navnet "Analysepakke" og bekreft handlingene dine ved å klikke "OK".

Hvis alt er gjort riktig, vil den ønskede knappen vises på høyre side av fanen Data, som ligger over Excel-regnearket.

Lineær regresjon i Excel

Nå som vi har for hånden alle nødvendige virtuelle verktøy for å utføre økonometriske beregninger, kan vi begynne å løse problemet vårt. For dette:

klikk på "Dataanalyse"-knappen;
i vinduet som åpnes, klikk på "Regresjon" -knappen;
i fanen som vises, skriv inn verdiområdet for Y (antall ansatte som slutter) og for X (lønnen deres);
Vi bekrefter handlingene våre ved å trykke på "Ok"-knappen.

Som et resultat vil programmet automatisk fylle ut et nytt ark av regnearket med regresjonsanalysedata. Merk! Excel har muligheten til å manuelt angi plasseringen du foretrekker for dette formålet. For eksempel kan det være det samme arket der Y- og X-verdiene er, eller til og med en ny arbeidsbok som er spesielt utviklet for å lagre slike data.

Analyse av regresjonsresultater for R-kvadrat

I Excel ser dataene innhentet under behandlingen av dataene i det vurderte eksemplet slik ut:

Først av alt bør du ta hensyn til verdien av R-kvadrat. Det er bestemmelseskoeffisienten. I dette eksemplet er R-kvadrat = 0,755 (75,5 %), dvs. de beregnede parametrene til modellen forklarer forholdet mellom de vurderte parametrene med 75,5 %. Jo høyere verdi av bestemmelseskoeffisienten er, jo mer anvendelig er den valgte modellen for en bestemt oppgave. Det antas at den korrekt beskriver den virkelige situasjonen med en R-kvadratverdi over 0,8. Hvis R-kvadraten er tcr, forkastes hypotesen om ubetydeligheten til frileddet til den lineære ligningen.

I oppgaven under vurdering for det frie medlemmet, ved bruk av Excel-verktøyene, ble det oppnådd at t = 169.20903, og p = 2.89E-12, dvs. vi har null sannsynlighet for at den korrekte hypotesen om ubetydeligheten til det frie medlemmet vil være avvist. For koeffisienten ved ukjent t=5,79405, og p=0,001158. Sannsynligheten for at den korrekte hypotesen om koeffisientens insignifikans for det ukjente forkastes er med andre ord 0,12 %.

Dermed kan det hevdes at den resulterende lineære regresjonsligningen er tilstrekkelig.

Problemet med hensiktsmessigheten av å kjøpe en aksjeblokk

Multippel regresjon i Excel utføres ved hjelp av det samme dataanalyseverktøyet. Vurder et spesifikt anvendt problem.

Ledelsen i NNN må ta en beslutning om det er tilrådelig å kjøpe en 20 % eierandel i MMM SA. Kostnaden for pakken (JV) er 70 millioner amerikanske dollar. NNN-spesialister samlet inn data om lignende transaksjoner. Det ble besluttet å evaluere verdien av aksjeblokken i henhold til slike parametere, uttrykt i millioner av amerikanske dollar, som:

leverandørgjeld (VK);
årlig omsetning (VO);
kundefordringer (VD);
kostnad for anleggsmidler (SOF).

I tillegg brukes parameteren lønnsrestanser til bedriften (V3 P) i tusenvis av amerikanske dollar.

Løsning ved hjelp av Excel-regneark

Først av alt må du lage en tabell med innledende data. Det ser slik ut:

kall opp vinduet "Dataanalyse";
velg "Regresjon"-delen;
i boksen "Input interval Y" skriv inn verdiområdet for avhengige variabler fra kolonne G;
klikk på ikonet med en rød pil til høyre for "Input interval X"-vinduet og velg området for alle verdier fra kolonnene B, C, D, F på arket.

Velg "Nytt regneark" og klikk "OK".

Få regresjonsanalysen for det gitte problemet.

Gjennomgang av resultater og konklusjoner

"Vi samler inn" fra de avrundede dataene presentert ovenfor på Excel-regnearket, regresjonsligningen:

SP \u003d 0,103 * SOF + 0,541 * VO - 0,031 * VK + 0,405 * VD + 0,691 * VZP - 265,844.

I en mer kjent matematisk form kan det skrives som:

y = 0,103*x1 + 0,541*x2 - 0,031*x3 +0,405*x4 +0,691*x5 - 265,844

Data for JSC "MMM" er presentert i tabellen:

Setter de inn i regresjonsligningen, får de et tall på 64,72 millioner amerikanske dollar. Dette betyr at aksjene i JSC MMM ikke bør kjøpes, siden verdien på 70 millioner amerikanske dollar er ganske overvurdert.

Som du kan se, gjorde bruken av Excel-regnearket og regresjonsligningen det mulig å ta en informert beslutning om gjennomførbarheten av en veldig spesifikk transaksjon.

Nå vet du hva regresjon er. Eksemplene i Excel diskutert ovenfor vil hjelpe deg med å løse praktiske problemer fra økonometrifeltet.

MS Excel-pakken lar deg gjøre det meste av arbeidet veldig raskt når du konstruerer en lineær regresjonsligning. Det er viktig å forstå hvordan man tolker resultatene. For å bygge en regresjonsmodell, velg Verktøy\Dataanalyse\Regresjon (i Excel 2007 er denne modusen plassert i delen Data/Dataanalyse/Regresjon). Kopier deretter de oppnådde resultatene til en blokk for analyse.

Viser påvirkningen av noen verdier (uavhengig, uavhengig) på den avhengige variabelen. For eksempel hvordan antallet økonomisk aktive befolkning avhenger av antall bedrifter, lønn og andre parametere. Eller: hvordan påvirker utenlandske investeringer, energipriser osv. nivået på BNP.

Resultatet av analysen lar deg prioritere. Og basert på hovedfaktorene, for å forutsi, planlegge utviklingen av prioriterte områder, ta ledelsesbeslutninger.

Regresjon skjer:

lineær (y = a + bx);

parabolsk (y = a + bx + cx 2);

eksponentiell (y = a * exp(bx));

Potens (y = a*x^b);

hyperbolsk (y = b/x + a);

logaritmisk (y = b * 1n(x) + a);

eksponentiell (y = a * b^x).

Tenk på eksempelet med å bygge en regresjonsmodell i Excel og tolke resultatene. La oss ta en lineær type regresjon.

En oppgave. Ved 6 virksomheter ble gjennomsnittlig månedslønn og antall ansatte som sluttet analysert. Det er nødvendig å bestemme avhengigheten av antall pensjonerte ansatte på gjennomsnittslønnen.

Den lineære regresjonsmodellen har følgende form:

Y \u003d a 0 + a 1 x 1 + ... + a k x k.

Der a er regresjonskoeffisientene, x er de påvirkende variablene, og k er antall faktorer.

I vårt eksempel er Y indikatoren for sluttarbeidere. Påvirkningsfaktoren er lønn (x).

Excel har innebygde funksjoner som kan brukes til å beregne parametrene til en lineær regresjonsmodell. Men Analysis ToolPak-tillegget vil gjøre det raskere.

Aktiver et kraftig analyseverktøy:

1. Klikk på "Office"-knappen og gå til fanen "Excel-alternativer". "Tillegg".

2. Nedenfor, under rullegardinlisten, i feltet "Administrasjon" vil det være en inskripsjon "Excel-tillegg" (hvis den ikke er der, klikk på avkrysningsboksen til høyre og velg). Og en Go-knapp. Klikk.

3. En liste over tilgjengelige tillegg åpnes. Velg "Analysepakke" og klikk OK.

Når det er aktivert, vil tillegget være tilgjengelig under fanen Data.

Nå skal vi behandle regresjonsanalysen direkte.

1. Åpne menyen til dataanalyseverktøyet. Velg "Regresjon".

2. En meny åpnes for å velge inngangsverdier og utdataalternativer (hvor resultatet skal vises). I feltene for de første dataene angir vi rekkevidden til den beskrevne parameteren (Y) og faktoren som påvirker den (X). Resten kan eller ikke kan fullføres.

3. Etter å ha klikket OK, vil programmet vise beregningene på et nytt ark (du kan velge intervallet som skal vises på gjeldende ark eller tilordne utdataene til en ny arbeidsbok).

Først av alt tar vi hensyn til R-kvadraten og koeffisientene.

R-kvadrat er bestemmelseskoeffisienten. I vårt eksempel er det 0,755, eller 75,5 %. Dette betyr at de beregnede parameterne til modellen forklarer sammenhengen mellom de studerte parameterne med 75,5 %. Jo høyere bestemmelseskoeffisient, jo bedre modell. Bra - over 0,8. Dårlig - mindre enn 0,5 (en slik analyse kan neppe anses som rimelig). I vårt eksempel - "ikke dårlig".

Koeffisienten 64.1428 viser hva Y vil være dersom alle variablene i modellen som vurderes er lik 0. Det vil si at andre faktorer som ikke er beskrevet i modellen også påvirker verdien av den analyserte parameteren.

Koeffisienten -0,16285 viser vekten av variabelen X på Y. Det vil si at gjennomsnittlig månedslønn innenfor denne modellen påvirker antall sluttere med en vekt på -0,16285 (dette er en liten grad av påvirkning). "-"-tegnet indikerer en negativ innvirkning: jo høyere lønn, jo mindre slutter. Som er rettferdig.