Bestemmelseskoeffisient i Excel

En av indikatorene som beskriver kvaliteten på den konstruerte modellen i statistikk, er bestemmelseskoeffisienten (R ^ 2), som også kalles verdien av nøyaktigheten av tilnærmingen. Med det kan du bestemme nivået på presisjonsforutsetningen. La oss lære å beregne denne indikatoren ved hjelp av ulike Excel-verktøy.

Beregning av bestemmelseskoeffisienten

Avhengig av nivået på bestemmelseskoeffisienten, er det vanlig å dele modeller i tre grupper:

• 0,8 - 1 - En modell av god kvalitet;
• 0,5 - 0,8 - Modell av akseptabel kvalitet;
• 0 - 0.5 - En modell av dårlig kvalitet.

I sistnevnte tilfelle indikerer kvaliteten på modellen umuligheten av bruken for prognosen.

Valget av en metode for beregning av den angitte verdien i Excel avhenger av om regresjon er lineær eller ikke. I det første tilfellet kan du bruke Firson-funksjonen, og i det andre må du bruke et spesielt verktøy fra analysepakken.

Metode 1: Beregning av bestemmelseskoeffisienten med en lineær funksjon

Først av alt, finn ut hvordan du finner bestemmelseskoeffisienten med en lineær funksjon. I dette tilfellet vil denne indikatoren være lik kvadratet av korrelasjonskoeffisienten. Vi vil gjøre det beregnet ved hjelp av den innebygde Excel-funksjonen på eksempelet på et bestemt bord, som er vist nedenfor.

1. Vi fremhever cellen der bestemmelseskoeffisienten utføres etter beregningen, og klikker på ikonet "Sett inn funksjon".



2. Funksjonene Master starter. Vi flytter til sin kategori "Statistisk" og noterer navnet "Kvgirson". Neste, leire på "OK" -knappen.



3. Quilinson-funksjonen Arguments-vinduet starter. Denne operatøren fra den statistiske gruppen er utformet for å beregne firkanten av korrelasjonskoeffisienten til Pearson-funksjonen, det vil si en lineær funksjon. Og når vi husker, med en lineær funksjon, er bestemmelseskoeffisienten like lik kvadratet av korrelasjonskoeffisienten.

   Syntaxen til denne operatøren er:

   = Cvgirson (known_stations_y; kjent_name_x)

   Dermed har funksjonen to operatører, hvorav en er en liste over funksjonsverdier, og de andre argumentene. Operatører kan representeres som direkte som verdiene som er oppført gjennom et semikolon (;) og i form av referanser til områdene hvor de befinner seg. Det er det siste alternativet og vil bli brukt av oss i dette eksemplet.

   Installer markøren i feltet "Kjente V-verdier". Vi utfører klemmen på venstre museknapp og produserer innholdet i "Y" -kolonnen i tabellen. Som du kan se, vises adressen til den angitte dataproduktet umiddelbart i vinduet.

   På samme måte fyll det "kjente X" -feltet. Vi setter markøren på dette feltet, men denne gangen allokerer verdien av kolonnen "X".

   Etter at alle dataene ble vist i CVGIRSON-argumenter-vinduet, er leire på "OK" -knappen plassert i bunnen.



4. Som du kan se, etter dette, gjør programmet å beregne bestemmelseskoeffisienten og gir resultatet i cellen som har blitt fremhevet før du ringer veiviseren av funksjoner. I vårt eksempel viste verdien av den beregnede indikatoren for å være lik 1. Dette betyr at den presenterte modellen er helt pålitelig, det vil si, eliminerer feilen.

Leksjon: Master of Funksjoner i Microsoft Excel

Metode 2: Beregning av bestemmelseskoeffisienten i ikke-lineære funksjoner

Men ovennevnte alternativ for beregning av ønsket verdi kan bare brukes til lineære funksjoner. Hva å gjøre for å gjøre beregningen i en ikke-lineær funksjon? I Excel er det en mulighet. Det kan gjøres ved hjelp av "regresjonsverktøyet", som er en integrert del av dataanalysepakken.

1. Men før du bruker det angitte verktøyet, bør "Analysepakken" selv aktiveres, som er deaktivert som standard i Excele. Vi flytter til "Fil" -fanen, og deretter går du gjennom "parametere" -elementet.



2. I vinduet som åpnes, gjør du flyttet til "Add-in" -delen ved hjelp av navigasjonen på venstre vertikal meny. På bunnen av det høyre vinduet er kontrollfeltet plassert. Fra listen over delene som er tilgjengelige der, velger du navnet på "Add-in Excel ...", og klikker deretter på knappen "Go ...", som ligger til høyre for feltet.



3. Vinduet lanseres. I sin sentrale del er det en liste over tilgjengelige tilleggsutstyr. Installer avkrysningsruten i nærheten av posisjonen "Analysispakke". Etter dette må du klikke på "OK" -knappen på høyre side av vinduets grensesnitt.



4. Dataanalyseverktøyspakken i dagens Excel-forekomst vil bli aktivert. Tilgang til den er plassert på båndet i kategorien Data. Flytt inn i den angitte kategorien og leire på "Dataanalyse" -knappen i "Analyse" Innstillinger-gruppen.



5. Vinduet "Dataanalyse" aktiveres med en liste over informasjonsbehandlingsverktøy. Vi allokerer fra denne listen elementet "regresjon" og leire på "OK" -knappen.



6. Deretter åpner "Regression" verktøyvinduet. Den første innstillingene blokken er "input data". Her i to felt må du spesifisere adressen til områdene der verdiene av argumentet og funksjon er plassert. Vi setter markøren i "Input intervallet Y" felt og fordele innholdet i "Y" kolonne på arket. Etter adresse i matrisen vises i regresjonen vinduet, setter vi markøren i "Input intervallet Y" felt og fordele cellene i "X" kolonnen på samme måte.

   Nær parametre "Tag" og "Constant-Zero" flagg ikke satt. Den ruten kan installeres i nærheten av "Reliability" parameter, og i den motsatte felt angir den ønskede verdi av den tilsvarende indikator (som standard 95%).

   I "Output Parameters" gruppe, må du angi i hvilket område vises resultatet av beregningen. Det er tre alternativer:

   • Område på gjeldende ark;
   • Et annet ark;
   • En annen bok (ny fil).

   Etablere ditt valg i den første utførelse, slik at kildedata og resultatet er plassert på en arbeidsarket. Vi setter bryteren nærheten av "output intervall" parameter. I feltet motsatt dette elementet, setter vi markøren. Klikk på venstre museknapp på den tomme elementet på platen, som er utformet for å bli den venstre øvre celle i utgangstabellen for beregningen. Adressen til dette elementet skal være uthevet i "Regression" -vinduet.

   Grupper av parametre "rester" og "normal sannsynlighets" overse, så det er ikke viktig for å løse oppgaven. Etter det er vi leire på "OK" -knappen, som er plassert i øvre høyre hjørne av "Regression" -vinduet.



7. Programmet gjør en beregning basert på tidligere innlastede data og viser resultatet til det angitte området. Som du kan se, viser dette verktøyet et ganske stort antall resultater på ulike parametre. Men i sammenheng med dagens leksjon, er vi interessert i R-square indikator. I dette tilfellet, er det lik 0.947664, som karakteriserer den valgte modellen som en modell av god kvalitet.

Metode 3: Bestemmelse Coefficient for Trend Linje

I tillegg til de ovennevnte alternativer, kan bestemmelsen koeffisienten fremvist direkte for trendlinjen i grafen bygget på Excel-arket. Finn ut hvordan dette kan gjøres på et konkret eksempel.

1. Vi har en tidsplan bygget på grunnlag av argumenttabellen og funksjonene til funksjonen som ble brukt til det forrige eksempelet. Vi vil bygge en trendlinje til den. Klikk på et hvilket som helst nettsted av byggeplassen som tidsplanen er tilgjengelig for venstre-klikk. Samtidig vises et ekstra sett med faner på båndet - "Arbeid med diagrammer". Gå til fanen "Layout". Leire på "Trend Line" -knappen, som er plassert i verktøylinjen "Analyse". En meny vises med et valg av typen trendlinje. Start valget på typen som samsvarer med den spesifikke oppgaven. La oss velge alternativet "Eksponentiell tilnærming" for vårt eksempel.



2. Excel bygger direkte på flyet med å bygge en trendlinje i form av en ekstra svart kurve.



3. Nå er vår oppgave å vise den faktiske bestemmelseskoeffisienten. Klikk Høyreklikk langs trendlinjen. Kontekstmenyen er aktivert. Start valget i det på Trend Line Format Point.

   

   For å utføre en overgang til Trend Line Format-vinduet, kan du lage en alternativ handling. Vi markerer trendlinjen med venstre museknapp på den. Flytt inn i fanen "Layout". Leire på "Trend Line" -knappen i "Analysis" -blokken. I listen over leilisten på det beste punktet til handlingslisten - "Ekstra trendlinjeparametere ...".



4. Etter noen av de to overtakene er formatvinduet lansert der du kan produsere flere innstillinger. Spesielt for å oppfylle oppgaven vår er det nødvendig å etablere en avkrysningsboks motsatt elementet "Legg verdien av nøyaktigheten av tilnærming (R ^ 2)". Den ligger på bunnen av vinduet. Det vil si, så vi inkluderer visning av bestemmelseskoeffisienten på byggeplassen. Deretter glemmer du ikke å klikke på "Lukk" -knappen nederst i det aktuelle vinduet.



5. Verdien av nøyaktigheten av tilnærming, det vil si verdien av bestemmelseskoeffisienten vil bli vist på et blad i byggeplassen. I dette tilfellet er denne verdien synlig, lik 0,9242, som karakteriserer tilnærming som en modell av god kvalitet.



6. Absolutt på denne måten kan du etablere et besluttsomhetskoeffisient show for enhver annen type trendlinje. Du kan endre trendlinjen-typen ved å gjøre en overgang via knappen på båndet eller kontekstmenyen i vinduet i parametrene, som vist ovenfor. Deretter i vinduet i vinduet "Build Trend Line", kan du bytte til en annen type. Vi glemmer ikke å kontrollere det for å "plassere verdien av nøyaktigheten av tilnærming på diagrammet over verdien av tilnærming". Etter å ha fullført de ovennevnte handlingene, klikker du på knappen "Lukk" i nedre høyre hjørne av vinduet.



7. Med en lineær type har trendlinjen allerede verdien av nøyaktigheten av tilnærming lik 0,9477, som karakteriserer denne modellen, som enda mer pålitelig enn linjen i trenden i den eksponensielle typen som vurderes av oss.



8. Således bytter mellom ulike typer trendlinjer og sammenligner deres tilnærmet pålitelighetsverdier (bestemmelseskoeffisient), kan du finne det alternativet at modellen mest nøyaktig beskriver den innsendte tidsplanen. Et alternativ med den høyeste bestemmelseshastigheten vil være den mest pålitelige. På grunnlag kan du bygge den mest nøyaktige prognosen.

   For eksempel, for vårt tilfelle var det mulig å fastslå at det høyeste nivået av pålitelighet har en polynom type av en andre grad trend. Bestemmelseskoeffisienten i dette tilfellet er 1. Dette antyder at den angitte modellen er helt pålitelig, noe som betyr det komplette unntaket av feil.

   

   Men samtidig betyr dette ikke at for en annen graf, vil denne typen trendlinje også være den mest pålitelige. Det optimale valget av typen trendlinje avhenger av typen funksjon, på grunnlag av hvilken tidsplanen ble bygget. Hvis brukeren ikke har nok kunnskap til "på øyet" for å estimere alternativet Høyeste kvalitet, er den eneste utgangen av å bestemme den beste prognosen bare en sammenligning av bestemmelseskoeffisientene, som vist i eksemplet ovenfor.

Se også:

Bygge en trendlinje i Excel

Tilnærming i Excel.

I Excel er det to hovedalternativer for beregning av bestemmelseskoeffisienten: bruken av operatøren av firsonen og anvendelsen av regresjonsverktøyet fra dataanalyseverktøyspakken. Samtidig er den første av disse alternativene beregnet for bruk bare under behandlingen av en lineær funksjon, og et annet alternativ kan brukes i nesten alle situasjoner. I tillegg er det mulig å vise bestemmelseskoeffisienten for linjen med grafer av grafer som verdien av nøyaktigheten av tilnærming. Med denne indikatoren er det mulig å bestemme typen trendlinje, som har det høyeste nivået av pålitelighet for en bestemt funksjon.