Cálculo do coeficiente de determinación en Microsoft Excel

Un dos indicadores que describen a calidade do modelo construído nas estatísticas é o coeficiente de determinación (R ^ 2), que tamén se denomina valor de confianza de aproximación. Con el, pode determinar o nivel de precisión da previsión. Descubrimos como pode calcular este indicador utilizando varias ferramentas de Excel.

Cálculo do coeficiente de determinación

Dependendo do nivel do coeficiente de determinación, é habitual dividir modelos en tres grupos:

• 0,8 - 1 - un modelo de boa calidade;
• 0,5 - 0,8 - un modelo de calidade aceptable;
• 0 - 0,5 - un modelo de mala calidade.

Neste último caso, a calidade do modelo indica a imposibilidade de uso para a previsión.

A elección de como calcular o valor especificado en Excel depende de se a regresión é lineal ou non. No primeiro caso, pode utilizar a función KVPIRSON, e no segundo terá que usar unha ferramenta especial no paquete de análise.

Método 1: cálculo do coeficiente de determinación cunha función lineal

Primeiro de todo, descubrir como atopar o coeficiente de determinación para unha función lineal. Neste caso, este indicador será igual ao cadrado do coeficiente de correlación. O calcularemos usando a función de Excel integrada empregando o exemplo dunha táboa específica, que se mostra a continuación.

1. Seleccione a cela onde o coeficiente de determinación aparecerá despois do seu cálculo e faga clic na icona "Inserir función".



2. Inicia Asistente de funcións. Mover á súa categoría "Estatística" e marque o nome KVPIRSON. A continuación, fai clic no botón "OK".



3. Iníciase a ventá dos argumentos da función. KVPIRSON. Este operador do grupo estatístico está deseñado para calcular o cadrado do coeficiente de correlación da función Pearson, é dicir, unha función lineal. E como recordamos, cunha función lineal, o coeficiente de determinación é igual ao cadrado do coeficiente de correlación.

   A sintaxe desta afirmación é:

   = KVPIRSON (coñecido; ben coñecido_x)

   Así, unha función ten dous operadores, un dos cales é unha lista de valores da función, eo segundo é un argumento. Os operadores poden representarse directamente como os valores listados a través de punto e coma (;), e en forma de ligazóns ás gamas onde se atopan. É a última opción que usaremos neste exemplo.

   Sitúe o cursor no campo "Valores coñecidos Y". Realizamos a sujeción do botón esquerdo do rato e seleccionamos o contido da columna. "Y" táboas. Como podes ver, a dirección da matriz de datos especificada móstrase inmediatamente na xanela.

   Enche de xeito semellante o campo "Coñecido x". Coloca o cursor neste campo, pero esta vez selecciona os valores da columna "X".

   Despois de mostrar todos os datos na xanela de argumentos KVPIRSONfaga clic no botón "OK"situado no seu fondo.



4. Como podes ver, despois diso, o programa calcula o coeficiente de determinación e devolve o resultado á cela seleccionada antes da chamada Mestría de funcións. No noso exemplo, o valor do indicador calculado resultou ser 1. Isto significa que o modelo presentado é absolutamente fiable, é dicir, exclúe o erro.

Lección: Asistente de funcións en Microsoft Excel

Método 2: Cálculo do coeficiente de determinación en funcións non lineares

Pero a opción anterior de calcular o valor desexado só pode aplicarse ás funcións lineares. Que facer para producir o seu cálculo nunha función non lineal? En Excel existe tal oportunidade. Pódese facer cunha ferramenta. "Regresión"que é parte do paquete "Análise de datos".

1. Pero antes de usar esta ferramenta, debes activalo só. "Paquete de análise"que por defecto está desactivado en Excel. Mover á pestana "Ficheiro"e despois percorrer o ítem "Opcións".



2. Na ventá aberta móvese á sección. Complementos navegando polo menú vertical esquerdo. Na parte inferior do panel dereito hai un campo "Xestión". Na lista de subseções dispoñibles escolla o nome "Complementos de Excel ..."e logo faga clic no botón "Vaia ..."situado á dereita do campo.



3. Iníciase a ventá de complementos. Na parte central hai unha lista dos complementos dispoñibles. Marque a caixa situada ao lado da posición "Paquete de análise". Despois, faga clic no botón. "OK" no lado dereito da ventá da interface.



4. Paquete de ferramentas "Análise de datos" na instancia actual de Excel activarase. O acceso a el está situado na cinta da pestana "Datos". Mova á pestana especificada e fai clic no botón. "Análise de datos" no grupo de opcións "Análise".



5. Xanela activada "Análise de datos" cunha lista de ferramentas de procesamento de información especializadas. Seleccione deste elemento da lista "Regresión" e prema no botón "OK".



6. Despois ábrese a ventá da ferramenta. "Regresión". O primeiro bloque de opcións - "Entrada". Aquí nos dous campos cómpre especificar os enderezos dos intervalos nos que se atopan os valores e funcións do argumento. Coloca o cursor no campo "Intervalo de entrada Y" e selecciona o contido da columna na folla "Y". Despois de que se mostre a dirección da matriz na xanela "Regresión"poñer o cursor no campo "Intervalo de entrada Y" e exactamente do mesmo xeito selecciona as celas da columna "X".

   Acerca dos parámetros "Etiqueta" e "Constant-zero" as caixas de verificación non están definidas. A caixa de verificación pódese axustar preto do parámetro "Nivel de fiabilidade" e no campo oposto, indique o valor desexado do indicador correspondente (por defecto 95%).

   En grupo "Opcións de saída" cómpre especificar en que área se amosará o resultado do cálculo. Hai tres opcións:

   • Área na folla actual;
   • Outra folla;
   • Outro libro (novo ficheiro).

   Deixemos a elección na primeira opción de que os datos e resultados iniciais fosen colocados nunha folla de traballo. Coloca o interruptor preto do parámetro "Espazo de saída". No campo oposto a este elemento coloque o cursor. Facemos clic co botón esquerdo do rato sobre o elemento baleiro da folla, o cal pretende converterse na cela superior esquerda da táboa dos resultados do cálculo. O enderezo deste elemento debería mostrarse na xanela "Regresión".

   Grupos de parámetros "Restos" e "Probabilidade normal" Ignorar, xa que non son importantes para resolver o problema. Despois faremos clic no botón. "OK"que se atopa na esquina superior dereita da xanela "Regresión".



7. O programa calcula con base nos datos introducidos previamente e mostra o resultado no intervalo especificado. Como podes ver, esta ferramenta mostra na folla un número bastante grande de resultados en varios parámetros. Pero no contexto da lección actual interésanos o indicador "R-cadrado". Neste caso, é igual a 0,947664, que caracteriza o modelo seleccionado como un modelo de boa calidade.

Método 3: o coeficiente de determinación da liña de tendencia

Ademais das opcións anteriores, o coeficiente de determinación pódese mostrar directamente para a liña de tendencia nun gráfico construído nunha folla de Excel. Descubriremos como se pode facer isto cun exemplo concreto.

1. Temos un gráfico baseado na táboa de argumentos e valores da función que se usou para o exemplo anterior. Fagamos unha liña de tendencia. Facemos clic en calquera lugar da área de construción na que se coloca o gráfico co botón esquerdo do rato. Ao mesmo tempo, aparece un conxunto adicional de pestanas na cinta: "Traballar con gráficos". Vaia á pestana "Disposición". Facemos clic no botón "Liña de tendencia"que se atopa no bloque de ferramentas "Análise". Aparece un menú cunha selección de tipo de liña de tendencia. Paramos a elección do tipo que corresponde a unha tarefa específica. Para o noso exemplo, imos elixir "Aproximación exponencial".



2. Excel está a construír unha liña de tendencia en forma de curva negra adicional xusto no plano gráfico.



3. Agora a nosa tarefa é mostrar o coeficiente de determinación en si. Fai clic co botón dereito na liña de tendencia. O menú de contexto está activado. Pare a selección nel no elemento "Formato de liña de tendencia ...".

   

   Para facer unha transición á xanela de formato de liña de tendencia, pode realizar unha acción alternativa. Seleccione a liña de tendencia facendo clic co botón esquerdo do rato. Mover á pestana "Disposición". Facemos clic no botón "Liña de tendencia" en bloque "Análise". Na lista que se abre, facemos clic no último elemento da lista de accións - "Opcións avanzadas de liña de tendencia ...".



4. Despois de calquera das dúas accións anteriores, lanza unha ventá de formato na que pode facer axustes adicionais. En particular, para realizar a nosa tarefa, cómpre marcar a caixa situada ao lado "Pon no gráfico o valor da precisión da aproximación (R ^ 2)". Está situado na parte inferior da xanela. É dicir, deste xeito inclúese a visualización do coeficiente de determinación na área da construción. Non esqueza premer o botón "Pechar" na parte inferior da xanela actual.



5. O valor de confianza da aproximación, é dicir, o valor do coeficiente de determinación, aparecerá na folla na área da trama. Neste caso, este valor, como vemos, é igual a 0,9242, que caracteriza a aproximación, como modelo de boa calidade.



6. Absolutamente exactamente así pode configurar a visualización do coeficiente de determinación para calquera outro tipo de liña de tendencia. Pode cambiar o tipo de liña de tendencia realizando unha transición a través do botón da cinta ou do menú de contexto na súa ventá de parámetros, como se mostra arriba. Entón xa na ventá do grupo "Construír unha liña de tendencia" pode cambiar a outro tipo. Non esqueza controlar de xeito que case o punto "Coloca no gráfico o valor da precisión da aproximación" comprobouse. Despois de completar os pasos anteriores, prema no botón. "Pechar" na esquina inferior dereita da ventá.



7. No caso dun tipo lineal, a liña de tendencia xa ten un valor de confianza de aproximación de 0.9477, que caracteriza a este modelo como máis fiable que a liña de tendencia de tipo exponencial que consideramos anteriormente.



8. Así, ao cambiar entre diferentes tipos de liñas de tendencia e comparar os seus valores de confianza de aproximación (coeficiente de determinación), pode atopar a variante, cuxo modelo describe con máis precisión o gráfico presentado. A variante con maior índice de determinación será a máis fiable. Na súa base, pode construír a previsión máis precisa.

   Por exemplo, para o noso caso, por experimento, conseguimos establecer que o nivel máis alto de confianza é do tipo polinomial da liña de tendencia do segundo grao. O coeficiente de determinación neste caso é igual a 1. Isto suxire que este modelo é absolutamente fiable, o que significa a eliminación completa dos erros.

   

   Pero ao mesmo tempo, isto non significa en absoluto que este tipo de liña de tendencia tamén sexa o máis fiable para outro gráfico. A elección óptima do tipo de liña de tendencia depende do tipo de función na que se construíu o gráfico. Se o usuario non ten coñecemento suficiente para estimar a opción de maior calidade, entón o único xeito de determinar a mellor previsión é só unha comparación dos coeficientes de determinación, como se mostra no exemplo anterior.

Vexa tamén:
Construír liñas de tendencia en Excel
Aproximación de Excel

En Excel hai dúas opcións principais para calcular o coeficiente de determinación: usar o operador KVPIRSON e ferramenta de aplicación "Regresión" do paquete de ferramentas "Análise de datos". Neste caso, a primeira destas opcións está destinada só ao tratamento dunha función lineal e a outra opción pode usarse en case todas as situacións. Ademais, é posible mostrar o coeficiente de determinación para a liña de tendencia dos gráficos como un valor de confianza de aproximación. Usando este indicador, é posible determinar o tipo de liña de tendencia que ten o maior nivel de confianza para unha función determinada.