Berechnung vum Bestëmmungskoeffizient a Microsoft Excel

Ee vun den Indikatoren, déi d'Qualitéit vum konstruéiere Modell an der Statistik beschreiwen, ass den Determinatiounskoeffizient (R ^ 2), wat och den Upassungsvertraue Wäert genannt gëtt. Mat et kënnt Dir den Niveau vun der Genauegkeet vun der Prognose bestëmmen. Loosst eis erausfannen wéi Dir dësen Indikator mat verschiddene Excel Tools kënnt berechnen.

Berechnung vun der Bestëmmungskoeffizient

Ofhängeg vum Niveau vum Bestëmmungskoeffizient, ass et üblech, d'Modeller an dräi Gruppen opzedeelen:

• 0,8 - 1 - Modell vu gudder Qualitéit;
• 0,5 - 0,8 - Modell vun akzeptabler Qualitéit;
• 0 - 0.5 - schlecht Qualitéit Modell.

Am leschte Fall weist d'Qualitéit vum Model déi Onméiglechkeet vu senger Benotzung fir Prévisiounen ze weisen.

D'Wiel vun dem spezifizéierte Wäert an Excel ze berechnen hänkt ofhängeg ob d'Regressioun linear ass oder net. Am éischte Fall kënnt Dir d'Funktioun benotzen KVPIRSON, an an der zweeter musst Dir e speziellt Tool aus dem Analysepaket benotzen.

Method 1: Berechnung vum Koeffizient vun der Determinatioun mat enger linearer Funktioun

Als alleréischt wäerte mer erausfannen wéi een de Bestëmmungskoeffizient fir eng linear Funktioun fënnt. An dësem Fall wäert dësen Indikator dem Quadrat vum Korrelatiounskoeffizient gläich sinn. Mir berechnen et mat der agebauter Excel Funktioun op dem Beispill vun enger spezifescher Tabelle, déi hei ënnendrënner gëtt.

1. Wielt déi Zell wou de Bestëmmungskoeffizient no hirer Berechnung ugewise gëtt, a klickt op d'Ikon "Füügt Funktioun".



2. Fänkt un Feature WizardAn. Plënneren op seng Kategorie "Statistesch" a markéiert den Numm KVPIRSONAn. Nächst klickt op de Knäppchen "OK".



3. D'Funktiounsargumentfenster fänkt un. KVPIRSONAn. Dëse Bedreiwer aus der statistescher Grupp ass entwéckelt fir de Quadrat vun der Korrelatiounskoeffizient vun der Pearson Funktioun ze berechnen, also eng linear Funktioun. A wéi mir sech drun erënneren, mat enger linearer Funktioun, ass de Bestëmmungskoeffizient exakt d'selwecht wéi dem Quadrat vum Korrelatiounskoeffizient.

   D'Syntax fir dës Ausso ass:

   = KVPIRSON (bekannt_wäerter_; bekannte_x Wäerter)

   Also, eng Funktioun huet zwee Betreiber, vun deenen engen eng Lëscht vu Funktiounswäerter ass, an déi zweet en Argument. Opérateure kënnen esou direkt als Wäerter representéiert ginn duerch e Semikolon vertruede sinn (;), an an der Form vu Linken op d'Ränge wou se stinn. Et ass déi lescht Optioun déi vun eis an dësem Beispill benotzt gëtt.

   Setzt de Cursor am Feld Bekannt y WäerterAn. Mir halen de lénksen Maustast an wielt den Inhalt vun der Kolonn "Y" Dëscher. Wéi Dir kënnt gesinn, gëtt d'Adress vun der spezifizéierter Datarray direkt an der Fënster ugewisen.

   Am selwechte Wee fëllt d'Feld aus Bekannt x WäerterAn. Setzt de Cursor an dësem Feld, awer dës Kéier wielt d'Kolonnewäerter "X".

   Nodeems all d'Donnéeën an der Argumentfenster gewise ginn KVPIRSONklickt op de Knäppchen "OK"op senger ganz ënnen läit.



4. Wéi Dir kënnt gesinn, no dësem Programm rechent de Bestëmmungskoeffizient aus a weist d'Resultat an der Zell déi gewielt gouf souguer virum Uruff Funktioun ZaubererAn. An eisem Beispill huet de Wäert vum berechent Indikator als 1. Dëst bedeit datt de präsentéierte Modell absolut zouverléisseg ass, dat heescht, et eliminéiert de Feeler.

Lektioun: Feature Wizard an Microsoft Excel

Method 2: Berechnung vum Koeffizient vun der Determinatioun bei netlinearen Funktiounen

Awer déi uewe genannte Optioun fir de gewënschte Wäert ze berechnen kann nëmmen op linear Funktioune applizéiert ginn. Wat ze maachen fir et an enger netlinearer Funktioun ze berechnen? An Excel gëtt et sou eng Chance. Et kann mam Tool gemaach ginn. "Regressioun"deen Deel vum Package ass "Dateanalyse".

1. Awer ier Dir dat spezifizéiert Tool benotzt, musst Dir et selwer aktivéieren Analyse Package, deen als Standard an Excel behënnert ass. Plënnert op den Tab Dateian da gitt op "Optiounen".



2. An der Fënster déi opmaacht, réckelen op d'Sektioun Add-ons andeems Dir op de lénksen vertikalen Menu navigéiert. Um ënnen vun der rietser Fenster vun der Fënster ass e Feld "Management"An. Vun der Lëscht vun de Sektiounen, déi do verfügbar sinn, wielt den Numm "Excel Add-Ins ..."an da klickt op de Knäppchen "Gitt ..."op der rietser vum Feld lokaliséiert.



3. D'Address-Fenster ass gestart. An sengem zentrale Deel ass eng Lëscht vu verfügbare Add-ons. Setzt d'Kontrollbox nieft der Positioun Analyse PackageAn. No dësem klickt op de Knäppchen "OK" op der rietser Säit vun der Fensteren Interface.



4. Tool Package "Dateanalyse" an der aktueller Instanz vun Excel wäert aktivéiert ginn. Zougang zu et ass um Bändchen an der Tab "Donnéeën"An. Mir réckelen op déi spezifizéiert Tab a klickt op de Knäppchen "Dateanalyse" an der Astellungsgrupp "Analyse".



5. D'Fenster ass ageschalt "Dateanalyse" mat enger Lëscht vu spezialiséierten Informatiounsveraarbechtungstools. Wielt Element aus dëser Lëscht "Regressioun" a klickt op de Knäppchen "OK".



6. Da geet den Toolfenster op "Regressioun"An. Den éischte Block vun Astellungen ass "Input"An. Hei an zwee Felder musst Dir d'Adressen vun de Reegelen spezifizéieren, wou d'Wäerter vum Argument a Funktioun sinn. Stellt de Cursor am Feld "Input Interval Y" a wielt d'Inhalter vun der Kolonn op der Plack "Y"An. Nom Adresse gëtt an der Fënster ugewisen "Regressioun"set de Cursor am Feld "Input Interval Y" a wielt d'Kolonnenzellen op genau déiselwecht Manéier "X".

   Iwwer Parameteren "Label" an Konstant Zero setzen net Fändelen. D'Checkbox kann nieft dem Parameter gesat ginn. "Niveau vun der Zouverlässegkeet" an am Feld Géigendeel, wielt de gewënschte Wäert vum entspriechende Indikator (95% par défaut).

   Am Grupp Ausgaboptiounen musst Dir präziséieren an a wéi engem Beräich d'Berechnungsresultat ugewise gëtt. Et ginn dräi Optiounen:

   • D'Gebitt op der aktueller Blat;
   • En anert Blat;
   • En anert Buch (nei Datei).

   Loosst eis déi éischt Optioun wiele fir datt d'Quellendaten an d'Resultat op demselwechten Aarbechtsblat plazéiert ginn. Mir setzen de Schalter nieft dem Parameter "Ausgangsintervall"An. Am Feld vis-à-vis vun dësem Artikel setzt de Cursor. Lénks klickt op eidel Element op der Plack, dat ass entwéckelt fir déi iewescht lénks Zell vun der Berechnungsausgangstabell ze ginn. D'Adress vun dësem Element sollt am Fensterfeld ugewise ginn "Regressioun".

   Parameter Gruppen "Reschter" an "Normal Probabilitéit" Ignoréieren, well se net wichteg si fir d'Aufgab ze léisen. Duerno klickt op de Knäppchen "OK"an der oberer rechter Ecke vun der Fënster geluecht "Regressioun".



7. De Programm rechent op Basis vu virdrun aginnten Donnéeën a weist d'Resultat am spezifizéierte Beräich. Wéi Dir kënnt gesinn, weist dëst Tool eng zimlech grouss Zuel vu Resultater iwwer verschidde Parameteren op engem Blat. Awer am Kontext vun der aktueller Lektioun interesséiert eis den Indikator R-QuadratAn. An dësem Fall ass et gläich wéi 0,947664, wat de gewielte Modell als Modell vu gudder Qualitéit charakteriséiert.

Method 3: Bestëmmungskoeffizient fir d'Trendlinn

Zousätzlech zu den uewe genannten Optiounen, kann de Bestëmmungskoeffizient direkt fir d'Trendlinn ugewise ginn an enger Grafik, gebaut op engem Excel Worksheet. Mir wäerten erausfannen wéi dëst mat engem spezifesche Beispill gemaach ka ginn.

1. Mir hunn eng Grafik baséiert op enger Tabell vun Argumenter a Funktiounswäerter déi fir dat viregt Beispill benotzt goufen. Mir bauen eng Trendlinn derbäi. Mir klickt op all Plaz vum Bauberäich, op deem d'Graf gesat ass, mat der lénker Maus Knäppchen. Zur selwechter Zäit erschéngt eng zousätzlech Set vun Tabs um Band - "Schaffe mat Charts"An. Gitt op den Tab "Layout"An. Klickt op de Knäppchen Trend Linndéi am Toolblock steet "Analyse"An. E Menü erschéngt mat enger Wiel vun der Zort Trendline. Mir stoppen de Choix vum Typ dee mat enger spezifescher Aufgab entsprécht. Loosst eis eng Optioun fir eis Beispill wielen "Exponentiell Approximatioun".



2. Excel baut eng Trendlinn an der Form vun engem zousätzleche schwaarze Fluch direkt op der Grafik.



3. Elo ass eis Aufgab de Bestëmmungskoeffizient selwer ze weisen. Riets-klickt op d'Trendlinn. De Kontextmenü ass ageschalt. Mir stoppen d'Auswiel an der um "D'Format vun der Trendline ...".

   

   Fir den Iwwergank zu der Fenster Trendlinn Format ze maachen, kënnt Dir eng alternativ Aktioun ausféieren. Wielt d'Trendlinn andeems Dir mat der lénker Maus Knäppchen klickt. Plënnert op den Tab "Layout"An. Klickt op de Knäppchen Trend Linn a Spär "Analyse"An. An der Lëscht déi opmaacht, klickt op de leschten Element an der Lëscht vun den Aktiounen - "Zousätzlech Trend Line Parameter ...".



4. No entweder vun den uewe genannten zwou Aktiounen, gëtt eng Formatfenster gestart an där Dir zousätzlech Astellunge maache kënnt. Besonnesch fir eis Aufgab fäerdeg ze maachen, ass et noutwendeg dës Këscht niewendrun ze préift "Setzt den Upassungsvertrauen Wäert (R ^ 2) op dem Diagramm"An. Et ass ganz um Enn vun der Fënster. Dat ass, an dëser Aart a Weis maache mir den Affichage vum Bestëmmungskoeffizient am Bauberäich z'erméiglechen. Da vergiesst net op de Knäppchen ze klicken Zoumaachen um ënnen vun der aktueller Fënster.



5. De Wäert vun der Zouverlässegkeet vun der Upassung, also de Wäert vum Bestëmmungskoeffizient, gëtt op engem Blat am Bauberäich ugewisen. An dësem Fall ass dëse Wäert, sou wéi mer gesinn, 0,9242, wat d'Urnachung als e Modell vu gudder Qualitéit charakteriséiert.



6. Absolut genau an dëser Aart a Weis kënnt Dir den Affichage vum Bestëmmungskoeffizient fir all aner Zort Trendline astellen. Dir kënnt den Typ vun der Trendline änneren andeems Dir en Iwwergang duerch de Knäppchen um Band oder de Kontextmenü an der Fënster vu senge Parameteren mécht, wéi uewen gewisen. Dann an der Fënster selwer an der Grupp "Trend Trend bauen" Dir kënnt op eng aner Aart wiesselen. Zur selwechter Zäit vergiesst net ze kontrolléieren dat ronderëm de Punkt "Setzt de approximativ Vertrauenswäert op dem Diagramm" ass d'Këscht gepréift. Nodeems Dir déi uewe genannte Schrëtt gemaach hutt, klickt op de Knäppchen Zoumaachen am ënneschten rietsen Eck vun der Fënster.



7. Mat der linearer Zort huet d'Tendenzlinn schonn e Vertrauenswäert vun ongeféier Unzuel bei 0,9477, wat dëse Modell als nach méi zouverléisseg charakteriséiert wéi d'Trendlinn vun der exponentiell Aart ugesinn vun eis virdrun.



8. Also, wiesselt tëscht verschiddenen Typen vun Trendlinnen a vergläicht hir Approximatiounsvertraue Wäerter (Bestëmmungskoeffizient), kënne mir d'Optioun fannen, deem säi Modell déi präsentéierter Graf am beschten beschreift. D'Optioun mat dem héchste Koeffizient vu Bestëmmungskoeffizient ass déi zouverlässegst. Op der Basis kënnt Dir déi genaust Prognose bauen.

   Zum Beispill, fir eise Fall war et experimentell méiglech ze etabléieren datt d'polynomial Zort vun Trendlinn vum zweete Grad den héchsten Niveau vu Vertrauen huet. Den Determinatiounskoeffizient an dësem Fall ass 1. Dëst weist datt den ugewisenen Modell absolut zouverlässeg ass, wat heescht déi komplett Ausgrenzung vu Feeler.

   

   Awer gläichzäiteg bedeit dëst guer net datt fir eng aner Chart dës Zort Trendline och déi verlässlechst ass. Déi optimal Wiel vun der Zort vun der Trendline hänkt vun der Funktiounstyp of, op där d'Basis gebaut gouf. Wann de Benotzer net genuch Wëssen huet fir déi bescht Qualitéitsvariante duerch Auge ze schätzen, dann ass deen eenzege Wee fir déi bescht Prognose ze bestëmmen d'Determinatiounskoeffizienten ze vergläichen, wéi am Beispill hei uewen gewisen.

Liest och:
Bauen eng Trendline an Excel
Approximatioun an Excel

Et ginn zwou Haaptméiglechkeeten fir de Bestëmmungskoeffizient an Excel ze berechnen: de Bedreiwer benotzt KVPIRSON an Handwierksgeschir "Regressioun" aus der Toolbox "Dateanalyse"An. Ausserdeem ass déi éischt vun dësen Optiounen nëmme fir d'Veraarbechtung vun enger linearer Funktioun geduecht, an déi aner Optioun kann a bal all Situatiounen benotzt ginn. Ausserdeem ass et méiglech de Bestëmmungskoeffizient fir d'Tendenzlinn vun den Charts als e Wäert vun der Zouverlässegkeet vun der Approximatioun ze weisen. Mat Hëllef vun dësem Indikator ass et méiglech den Typ vun Trendlinn ze bestëmmen déi deen héchste Vertrauensniveau fir eng bestëmmte Funktioun huet.