Un dels indicadors que descriuen la qualitat del model construït en les estadístiques és el coeficient de determinació (R ^ 2), que també s'anomena valor de confiança d'aproximació. Amb ell, podeu determinar el nivell de precisió de la previsió. Descobrim com es pot calcular aquest indicador mitjançant diverses eines d'Excel.
Càlcul del coeficient de determinació
Depenent del nivell del coeficient de determinació, és habitual dividir els models en tres grups:
- 0,8 - 1 - un model de bona qualitat;
- 0,5-0,8 - un model de qualitat acceptable;
- 0 - 0,5 - un model de mala qualitat.
En aquest últim cas, la qualitat del model indica la impossibilitat d'utilitzar-lo per a la previsió.
L'elecció de com calcular el valor especificat a Excel depèn de si la regressió és lineal o no. En el primer cas, podeu utilitzar la funció KVPIRSON, i en el segon, hauràs d’utilitzar una eina especial del paquet d’anàlisi.
Mètode 1: càlcul del coeficient de determinació amb una funció lineal
En primer lloc, descobriu com es pot trobar el coeficient de determinació per a una funció lineal. En aquest cas, aquest indicador serà igual al quadrat del coeficient de correlació. El calcularem mitjançant la funció d’Excel integrada utilitzant l’exemple d’una taula específica, que es mostra a continuació.
- Seleccioneu la cel·la on es mostrarà el coeficient de determinació després del seu càlcul i feu clic a la icona "Insereix la funció".
- Comença Auxiliar de funcions. Vés a la seva categoria "Estadística" i marqueu el nom KVPIRSON. A continuació, feu clic al botó "D'acord".
- S'inicia la finestra de arguments de funció. KVPIRSON. Aquest operador del grup estadístic està dissenyat per calcular el quadrat del coeficient de correlació de la funció Pearson, és a dir, una funció lineal. I com recordem, amb una funció lineal, el coeficient de determinació és igual al quadrat del coeficient de correlació.
La sintaxi d'aquesta declaració és:
= KVPIRSON (conegut_x; ben conegut_x)Per tant, una funció té dos operadors, un dels quals és una llista de valors de la funció i el segon és un argument. Els operadors es poden representar directament com a valors llistats a través d’un punt i coma (;), i en forma d’enllaços als rangs on es troben. És l’última opció que usareu en aquest exemple.
Situeu el cursor al camp "Valors Y coneguts". Realitzem el tancament del botó esquerre del ratolí i seleccionem el contingut de la columna. "I" taules. Com podeu veure, l’adreça de la matriu de dades especificada es mostra immediatament a la finestra.
Ompliu el camp de la mateixa manera "Conegut x". Poseu el cursor en aquest camp, però aquesta vegada seleccioneu els valors de la columna "X".
Després de mostrar totes les dades a la finestra d’arguments KVPIRSONfeu clic al botó "D'acord"situat a la seva part inferior.
- Com podeu veure, després d’aquest moment, el programa calcula el coeficient de determinació i retorna el resultat a la cel·la que s’ha seleccionat abans de la trucada. Màsters de funcions. En el nostre exemple, el valor de l’indicador calculat va resultar ser 1. Això significa que el model presentat és absolutament fiable, és a dir, elimina l’error.
Lliçó: Auxiliar de funcions a Microsoft Excel
Mètode 2: Càlcul del coeficient de determinació en funcions no lineals
Però l’opció anterior de calcular el valor desitjat només es pot aplicar a funcions lineals. Què fer per produir el seu càlcul en una funció no lineal? A Excel hi ha aquesta oportunitat. Es pot fer amb una eina. "Regressió"que és part del paquet "Anàlisi de dades".
- Però abans d’utilitzar aquesta eina, l’haureu d’activar. "Paquet d’anàlisi"que per defecte està desactivat a Excel. Aneu a la pestanya "Fitxer"i després passem per l’element "Opcions".
- A la finestra oberta ens desplacem a la secció. Complements navegar pel menú vertical esquerre. A la part inferior del panell dret hi ha un camp "Gestió". Des de la llista de subseccions disponibles, escolliu el nom "Complements d'Excel ..."i després feu clic al botó "Aneu ..."situat a la dreta del camp.
- S'inicia la finestra de complements. A la part central hi ha una llista dels complements disponibles. Marqueu la casella situada al costat de la posició "Paquet d’anàlisi". Després, feu clic al botó. "D'acord" al costat dret de la finestra de la interfície.
- Paquet d’eina "Anàlisi de dades" en l’actual instància d’Excel s’activarà. L’accés al mateix es troba a la cinta de la pestanya "Dades". Vés a la pestanya especificada i fes clic al botó. "Anàlisi de dades" al grup de configuració "Anàlisi".
- Finestra activada "Anàlisi de dades" amb una llista d’eines de processament d'informació especialitzades. Seleccioneu aquest element de la llista "Regressió" i feu clic al botó "D'acord".
- A continuació, s'obrirà la finestra de l'eina. "Regressió". El primer bloc de configuracions - "Entrada". Aquí, als dos camps, heu d’especificar les adreces dels intervals on es troben els valors i les funcions de l’argument. Poseu el cursor al camp "Interval d’entrada Y" i seleccioneu el contingut de la columna al full "I". Després de mostrar l’adreça de la matriu a la finestra "Regressió"posa el cursor en el camp "Interval d’entrada Y" i exactament de la mateixa manera seleccioneu les cel·les de la columna "X".
Quant als paràmetres "Etiqueta" i "Constant-zero" les caselles de selecció no estan definides. La casella de selecció es pot establir a prop del paràmetre "Nivell de fiabilitat" i al camp oposat, indiqueu el valor desitjat de l’indicador corresponent (per defecte, el 95%).
En grup "Opcions de sortida" heu d’especificar en quina àrea es mostrarà el resultat del càlcul. Hi ha tres opcions:
- Àrea del full actual;
- Un altre full;
- Un altre llibre (fitxer nou).
Aturem l’elecció de la primera opció que les dades inicials i el resultat s’han col·locat en un full de treball. Poseu l'interruptor a prop del paràmetre "Espaiat de sortida". Al camp oposat a aquest ítem, poseu el cursor. Feu clic al botó esquerre del ratolí sobre l’element buit del full, que es vol convertir en la cel·la superior esquerra de la taula dels resultats del càlcul. L’adreça d’aquest element s’ha de mostrar a la finestra "Regressió".
Grups de paràmetres "Restes" i "Probabilitat normal" ignora, ja que no són importants per resoldre el problema. Després, feu clic al botó. "D'acord"que es troba a la cantonada superior dreta de la finestra "Regressió".
- El programa calcula sobre la base de les dades introduïdes anteriorment i mostra el resultat en el rang especificat. Com podeu veure, aquesta eina mostra en el full un nombre relativament gran de resultats en diversos paràmetres. Però, en el context de la lliçó actual, ens interessa l’indicador "R-square". En aquest cas, és igual a 0,947664, que caracteritza el model seleccionat com a model de bona qualitat.
Mètode 3: el coeficient de determinació de la línia de tendència
A més de les opcions anteriors, el coeficient de determinació es pot mostrar directament per a la línia de tendència en un gràfic construït en un full d'Excel. Esbrinarà com es pot fer això amb un exemple concret.
- Tenim un gràfic basat en la taula d’arguments i valors de la funció que s’utilitza per l’exemple anterior. Fem una línia de tendència. Feu clic a qualsevol lloc de l'àrea de construcció on es col·loca el gràfic amb el botó esquerre del ratolí. Al mateix temps, apareix un conjunt de pestanyes addicionals a la cinta: "Treballar amb gràfics". Aneu a la pestanya "Disposició". Fem clic al botó "Línia de tendència"que es troba al bloc d'eines "Anàlisi". Apareix un menú amb un tipus de línia de tendència. Aturem l’elecció del tipus que correspon a una tasca específica. Per al nostre exemple, escollim "Aproximació exponencial".
- Excel construeix una línia de tendència en forma de corba negra addicional a la dreta del pla de cartografia.
- Ara la nostra tasca és mostrar el propi coeficient de determinació. Fent clic dret sobre la línia de tendència. El menú contextual està activat. Atureu-ne la selecció a l’article "Format de línia de tendència ...".
Per fer una transició a la finestra del format de línia de tendència, podeu realitzar una acció alternativa. Seleccioneu la línia de tendència fent-hi clic amb el botó esquerre del ratolí. Aneu a la pestanya "Disposició". Fem clic al botó "Línia de tendència" en bloc "Anàlisi". A la llista que s'obre, fem clic a l’últim element de la llista d’actes: "Opcions de línia de tendències avançades ...".
- Després de qualsevol de les dues accions anteriors, es llança una finestra de format en la qual podeu fer configuracions addicionals. En particular, per dur a terme la nostra tasca, cal marcar la casella al costat de "Posa en el gràfic el valor de la precisió de l’aproximació (R ^ 2)". Es troba a la part inferior de la finestra. És a dir, d'aquesta manera s'inclou la visualització del coeficient de determinació en l'àrea de construcció. A continuació, no us oblideu de prémer el botó "Tanca" a la part inferior de la finestra actual.
- El valor de confiança de l’aproximació, és a dir, el valor del coeficient de determinació, es mostrarà al full al àrea de la trama. En aquest cas, aquest valor, com veiem, és igual a 0,9242, que caracteritza l’aproximació, com a model de bona qualitat.
- Absolutament exactament, per tant, podeu establir la visualització del coeficient de determinació per a qualsevol altre tipus de línia de tendència. Podeu canviar el tipus de línia de tendència fent una transició a través del botó de la cinta o del menú contextual a la finestra de paràmetres, tal com es mostra a dalt. A continuació, ja a la finestra del grup "Construint una línia de tendència" pot canviar a un altre tipus. No us oblideu de controlar de manera que sigui a prop del punt "Col·loqueu al gràfic el valor de la precisió de l’aproximació" s'ha comprovat. Després de completar els passos anteriors, feu clic al botó. "Tanca" a la part inferior dreta de la finestra.
- En el cas d'un tipus lineal, la línia de tendència ja té un valor de confiança d'aproximació de 0,9477, que caracteritza aquest model com més fiable que la línia de tendència de tipus exponencial que vam considerar anteriorment.
- Per tant, canviar entre diferents tipus de línies de tendència i comparar els seus valors de confiança d'aproximació (coeficient de determinació), es pot trobar la variant, el model del qual descriu amb més precisió el gràfic presentat. La variant amb el màxim índex de determinació serà la més fiable. En base a això, podeu construir la previsió més precisa.
Per exemple, per al nostre cas, per experiment, hem aconseguit establir que el nivell més alt de confiança és del tipus polinòmic de la línia de tendència del segon grau. El coeficient de determinació en aquest cas és igual a 1. Això suggereix que aquest model és absolutament fiable, el que significa l'eliminació completa dels errors.
Però al mateix temps, això no vol dir que aquest tipus de línia de tendència sigui també la més fiable per a un altre gràfic. L'elecció òptima del tipus de línia de tendència depèn del tipus de funció sobre la base de la qual es va construir el gràfic. Si l’usuari no té prou coneixements per estimar l’opció de més alta qualitat, l’única manera de determinar la millor predicció és només una comparació dels coeficients de determinació, com es mostra a l’exemple anterior.
Vegeu també:
Construir línies de tendència a Excel
Aproximació d’Excel
A Excel hi ha dues opcions principals per calcular el coeficient de determinació: utilitzar l’operador KVPIRSON i eina d’aplicació "Regressió" del paquet d’eines "Anàlisi de dades". En aquest cas, la primera d’aquestes opcions s’utilitza només en el processament d’una funció lineal i es pot utilitzar una altra opció en gairebé totes les situacions. A més, és possible mostrar el coeficient de determinació de la línia de tendència dels gràfics com a valor de confiança d'aproximació. Mitjançant aquest indicador, és possible determinar el tipus de línia de tendència que té el nivell de confiança més alt per a una funció determinada.
