Berekening van die bepalingskoëffisiënt in Microsoft Excel

Een van die aanwysers wat die kwaliteit van die gekonstrueerde model in statistiek beskryf, is die bepalingskoëffisiënt (R ^ 2), wat ook die benaderingsvertrouewaarde genoem word. Hiermee kan jy die vlak van akkuraatheid van die voorspelling bepaal. Kom ons uitvind hoe jy hierdie aanwyser kan bereken deur gebruik te maak van verskeie Excel-gereedskap.

Berekening van die bepalingskoëffisiënt

Afhangende van die vlak van die bepalingskoëffisiënt, is dit gebruikelik om modelle in drie groepe te verdeel:

  • 0,8 - 1 - 'n model van goeie gehalte;
  • 0,5 - 0,8 - 'n model van aanvaarbare gehalte;
  • 0 - 0,5 - 'n model van swak gehalte.

In die laasgenoemde geval dui die gehalte van die model aan die onmoontlikheid van die gebruik daarvan vir die voorspelling.

Die keuse van hoe om die gespesifiseerde waarde in Excel te bereken, hang af of die regressie lineêr is of nie. In die eerste geval kan u die funksie gebruik RSQ, en in die tweede moet jy 'n spesiale hulpmiddel uit die ontledingspakket gebruik.

Metode 1: Berekening van die bepalingskoëffisiënt met 'n lineêre funksie

Eerstens, vind uit hoe om die bepalingskoëffisiënt vir 'n lineêre funksie te vind. In hierdie geval sal hierdie aanwyser gelyk wees aan die vierkant van die korrelasiekoëffisiënt. Ons sal dit bereken deur die ingeboude Excel-funksie te gebruik deur die voorbeeld van 'n spesifieke tabel te gebruik, wat hieronder getoon word.

  1. Kies die sel waar die bepalingskoëffisiënt na die berekening vertoon sal word en klik op die ikoon "Voeg funksie in".
  2. begin Funksie Wizard. Skuif na sy kategorie "Statistiek" en merk die naam "RSQ". Klik dan op die knoppie "OK".
  3. Die funksie argument venster begin. RSQ. Hierdie operateur van die statistiese groep is ontwerp om die vierkant van die korrelasiekoëffisiënt van die Pearson-funksie te bereken, dit is 'n lineêre funksie. En soos ons onthou, met 'n lineêre funksie, is die bepalingskoëffisiënt net gelyk aan die vierkant van die korrelasiekoëffisiënt.

    Die sintaksis vir hierdie stelling is:

    = KVPIRSON (bekende_y; bekende_x)

    So, 'n funksie het twee operateurs, waarvan een 'n lys van waardes van die funksie is, en die tweede is 'n argument. Operateurs kan so direk verteenwoordig word as waardes wat deur 'n semikolon gelys word (;), en in die vorm van skakels na die reekse waar hulle geleë is. Dit is die laaste opsie wat deur ons in hierdie voorbeeld gebruik sal word.

    Stel die wyser in die veld "Bekende Y-waardes". Ons doen die klem van die linker muisknop en kies die inhoud van die kolom. "Y" tafel. Soos u kan sien, word die adres van die gespesifiseerde datarray onmiddellik in die venster vertoon.

    Vul ook die veld op "Bekende x". Plaas die wyser in hierdie veld, maar hierdie keer kies die kolomwaardes "X".

    Na al die data is vertoon in die argument venster RSQKlik op die knoppie "OK"geleë op sy heel onderkant.

  4. Soos u kan sien, bereken die program die bepalingskoëffisiënt en gee die resultaat terug na die sel wat voor die oproep gekies is. Funksie Meesters. In ons voorbeeld het die waarde van die berekende aanwyser 1 geword. Dit beteken dat die aangebied model absoluut betroubaar is, dit maak die fout uit.

Les: Funksie Wizard in Microsoft Excel

Metode 2: Berekening van die bepalingskoeffisiënt in nie-lineêre funksies

Maar die bogenoemde opsie om die verlangde waarde te bereken kan slegs op lineêre funksies toegepas word. Wat om te doen om sy berekening in 'n nie-lineêre funksie te produseer? In Excel is daar so 'n geleentheid. Dit kan met 'n instrument gedoen word. "Regressie"wat deel van die pakket is "Data-analise".

  1. Maar voordat u hierdie instrument gebruik, moet u dit self aktiveer. "Analise Pakket"wat by verstek in Excel afgeskakel is. Skuif na oortjie "Lêer"en gaan dan deur die item "Parameters".
  2. In die oop venster beweeg ons na die afdeling. "Byvoegings" deur te gaan deur die linker vertikale spyskaart. Aan die onderkant van die regter venster is 'n veld "Bestuur". Kies die naam uit die lys van beskikbare onderafdelings "Excel invoegtoepassings ..."en dan op die knoppie "Gaan ..."geleë aan die regterkant van die veld.
  3. Die byvoegingsvenster begin. In die sentrale deel is 'n lys van beskikbare invoegtoepassings. Merk die blokkie langs die posisie "Analise Pakket". Klik hierna op die knoppie. "OK" aan die regterkant van die koppelvlak venster.
  4. Gereedskap pakket "Data-analise" In die huidige geval sal Excel geaktiveer word. Toegang tot dit is op die lint in die blad "Data". Skuif na die gespesifiseerde oortjie en klik op die knoppie. "Data-analise" in die instellings groep "Ontleding".
  5. Geaktiveerde venster "Data-analise" met 'n lys van gespesialiseerde inligting verwerkingsinstrumente. Kies uit hierdie lysitem "Regressie" en klik op die knoppie "OK".
  6. Dan word die gereedskap venster oop. "Regressie". Die eerste blok van instellings - "Input". Hier in die twee velde moet u die adresse van die reekse spesifiseer waar die argumentwaardes en funksies geleë is. Plaas die wyser in die veld "Invoer interval Y" en kies die inhoud van die kolom op die vel "Y". Na die skikking adres word vertoon in die venster "Regressie"plaas die wyser in die veld "Invoer interval Y" en presies dieselfde manier kies die kolomme selle "X".

    Oor parameters "Label" en "Die konstante-nul" boksse is nie ingestel nie. Die boks kan naby die parameter gestel word "Betroubaarheidsvlak" en in die teenoorgestelde veld dui die verlangde waarde van die ooreenstemmende aanwyser aan (gewoonlik 95%).

    In groep "Uitvoer Opsies" jy moet spesifiseer in watter gebied die resultaat van die berekening vertoon sal word. Daar is drie opsies:

    • Oppervlakte op die huidige blad;
    • Nog 'n laken;
    • Nog 'n boek (nuwe lêer).

    Kom ons stop die keuse op die eerste opsie dat die aanvanklike data en resultaat op een werkblad geplaas is. Plaas die skakelaar naby die parameter "Output Spacing". In die veld teenoor hierdie item, plaas die wyser. Ons kliek op die linker muisknop op die leë element op die blad, wat bedoel is om die linker boonste sel van die tabel van die resultate van die berekening te word. Die adres van hierdie element moet in die venster vertoon word "Regressie".

    Parameter groepe "Bly" en "Normale waarskynlikheid" ignoreer, aangesien dit nie belangrik is om die probleem op te los nie. Daarna klik ons ​​op die knoppie. "OK"wat in die regter boonste hoek van die venster geleë is "Regressie".

  7. Die program bereken op grond van voorheen ingevoerde data en vertoon die resultaat in die gespesifiseerde reeks. Soos u kan sien, wys hierdie instrument op die blad 'n redelike groot aantal resultate op verskillende parameters. Maar in die konteks van die huidige les is ons geïnteresseerd in die aanwyser "R-kwadraat". In hierdie geval is dit gelyk aan 0.947664, wat die gekose model as 'n model van goeie gehalte kenmerk.

Metode 3: die bepalingskoëffisiënt vir die tendenslyn

Benewens bogenoemde opsies kan die bepalingskoëffisiënt direk vertoon word vir die tendenslyn in 'n grafiek wat op 'n Excel-blad gebou is. Ons sal uitvind hoe dit met 'n konkrete voorbeeld gedoen kan word.

  1. Ons het 'n grafiek gebaseer op die tabel van argumente en waardes van die funksie wat vir die vorige voorbeeld gebruik is. Kom ons maak 'n tendens lyn daaraan. Ons kliek op enige plek in die konstruksie area waarop die grafiek met die linker muisknop geplaas word. Terselfdertyd verskyn 'n bykomende stel tabbladen op die lint - "Werk met kaarte". Gaan na die oortjie "Uitleg". Ons kliek op die knoppie "Trend line"wat in die gereedskapblok geleë is "Ontleding". 'N Spyskaart verskyn met 'n keuse van trendlyn tipe. Ons stop die keuse op die tipe wat ooreenstem met 'n spesifieke taak. Vir ons voorbeeld, laat ons kies "Eksponensiële benadering".
  2. Excel bou 'n tendenslyn in die vorm van 'n ekstra swart kurwe reg op die kaartvliegtuig.
  3. Nou is ons taak om self die koëffisiënt van vasberadenheid te vertoon. Ons kliek met die rechtermuisknop op die trendlyn. Die konteks kieslys word geaktiveer. Stop die keuse daarin op die item "Trend line format ...".

    As u 'n oorgang na die trend line formaat venster wil maak, kan u 'n alternatiewe aksie uitvoer. Kies die tendenslyn deur daarop te klik met die linkermuisknop. Skuif na oortjie "Uitleg". Ons kliek op die knoppie "Trend line" in blok "Ontleding". In die lys wat oopmaak, klik ons ​​op die heel laaste item in die lys aksies - "Gevorderde neigingslyn opsies ...".

  4. Na enige van die bogenoemde twee aksies word 'n formaatvenster geloods waarin u bykomende instellings kan maak. In die besonder, om ons taak uit te voer, is dit nodig om die blokkie langsaan te merk "Sit die grafiek op die waarde van die akkuraatheid van die benadering (R ^ 2)". Dit is geleë onderaan die venster. Op hierdie manier sluit ons die vertoning van die bepalingskoëffisiënt op die konstruksie area in. Moenie vergeet om die knoppie te druk nie "Close" onderaan die huidige venster.
  5. Die vertroue waarde van die benadering, dit wil sê die waarde van die bepalingskoëffisiënt, sal op die vel in die plotgebied vertoon word. In hierdie geval is hierdie waarde, soos ons sien, gelyk aan 0.9242, wat die benadering kenmerk, as 'n goeie gehalte model.
  6. Absoluut presies, sodat jy die vertoning van die bepalingskoëffisiënt vir enige ander tipe lyn kan stel. Jy kan die tipe tendenslyn verander deur 'n oorgang te maak deur die knoppie op die lint of die konteks kieslys in sy parameters venster, soos hierbo getoon. Dan al in die venster in die groep "Die bou van 'n tendenslyn" kan oorskakel na 'n ander tipe. Moenie vergeet om so naby die punt te beheer nie "Plaas op die grafiek die waarde van die akkuraatheid van die benadering" is nagegaan. Nadat u bogenoemde stappe voltooi het, klik op die knoppie. "Close" in die onderste regterhoek van die venster.
  7. In die geval van 'n lineêre tipe het die tendenslyn reeds 'n benaderingsvertrouewaarde van 0.9477, wat hierdie model as selfs meer betroubaar kenmerk as die eksponensiële tipe tendenslyn wat ons vroeër oorweeg het.
  8. Dus, wissel tussen verskillende tipes tendenslyne en vergelyk hul waardes van benaderingsvertroue (bepalingskoëffisiënt), jy kan die variant vind, waarvan die model die grafiek die akkuraatste beskryf. Die variant met die hoogste indeks van bepaling sal die betroubaarste wees. Op grond daarvan kan jy die mees akkurate voorspelling bou.

    Byvoorbeeld, vir ons geval, deur eksperiment, het ons daarin geslaag om vas te stel dat die hoogste vlak van vertroue is van die polinoom tipe van die tendenslyn van die tweede graad. Die bepalingskoëffisiënt in hierdie geval is gelyk aan 1. Dit dui daarop dat hierdie model absoluut betroubaar is, wat die volledige eliminering van foute beteken.

    Maar terselfdertyd beteken dit glad nie dat hierdie tipe tendenslyn ook die betroubaarste vir 'n ander grafiek sal wees nie. Die optimale keuse van die tipe tendenslyn hang af van die tipe funksie waarop die grafiek gebou is. As die gebruiker nie genoeg kennis het om die hoogste gehalte opsie te skat nie, is die enigste manier om die beste voorspelling te bepaal, net 'n vergelyking van die vasstellingskoëffisiënte, soos in die voorbeeld hierbo getoon.

Sien ook:
Bou tendenslyne in Excel
Excel Approximation

In Excel is daar twee hoofopsies vir die berekening van die bepalingskoëffisiënt: die gebruik van die operateur RSQ en aansoek instrument "Regressie" uit die pakket gereedskap "Data-analise". In hierdie geval is die eerste van hierdie opsies slegs vir gebruik in die verwerking van 'n lineêre funksie, en 'n ander opsie kan in bykans alle situasies gebruik word. Daarbenewens is dit moontlik om die bepalingskoëffisiënt vir die tendenslyn van die grafieke te vertoon as 'n benaderingsvertroue waarde. Deur hierdie aanwyser te gebruik, is dit moontlik om die tipe tendenslyn te bepaal wat die hoogste vertrouevlak vir 'n bepaalde funksie het.