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

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

Cálculo do coeficiente de determinación

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

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

Neste último caso, a calidade do modelo indica a imposibilidade do seu 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 usar a función KVPIRSONe no segundo tes que usar unha ferramenta especial do paquete de análise.

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

Primeiro de todo, descubriremos 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. Calcularémolo usando a función Excel incorporada no exemplo dunha táboa específica, que se detalla a continuación.

1. Seleccione a cela onde se amosará o coeficiente de determinación despois do seu cálculo e faga clic na icona "Función de inserción".



2. Comeza Asistente de características. Pasando á súa categoría "Estatística" e marca o nome KVPIRSON. A continuación faga clic no botón "Está ben".



3. Iníciase a xanela de argumentos de 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 é exactamente igual ao cadrado do coeficiente de correlación.

   A sintaxis desta afirmación é:

   = KVPIRSON (valores coñecidos_y_; valores coñecidos_x)

   Así, unha función ten dous operadores, un dos cales é unha lista de valores de función, e o segundo é un argumento. Os operadores pódense representar tan directamente como valores enumerados a través dun punto e coma (;), e en forma de ligazóns aos intervalos onde se atopan. É a última opción que empregaremos este exemplo.

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

   Do mesmo xeito, cubra o campo Valores x coñecidos. Pon o cursor neste campo, pero esta vez seleccione os valores da columna "X".

   Despois de todos os datos amosáronse na xanela de argumentos KVPIRSONfai clic no botón "Está ben"situado na súa parte inferior.



4. Como podes ver, despois disto o programa calcula o coeficiente de determinación e mostra o resultado na cela que foi seleccionada incluso antes da chamada Asistentes de funcións. No noso exemplo, o valor do indicador calculado resultou ser 1. Isto significa que o modelo presentado é absolutamente fiable, é dicir, elimina o erro.

Lección: Asistente de recursos en Microsoft Excel

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

Pero a opción anterior para calcular o valor desexado só se pode aplicar a funcións lineais. Que facer para calculalo nunha función non lineal? En Excel hai tal oportunidade. Pódese facer coa ferramenta. "Regresión"que forma parte do paquete "Análise de datos".

1. Pero antes de usar a ferramenta especificada, debes activala ti mesmo Paquete de análise, que está desactivado de xeito predeterminado en Excel. Mover á pestana Arquivoe logo vai a "Opcións".



2. Na xanela que se abre, desprácese á sección "Complementos" navegando no menú vertical esquerdo. Na parte inferior do panel dereito da xanela hai un campo "Xestión". Na lista de subseccións dispoñibles, seleccione o nome "Complementos de Excel ..."e logo faga clic no botón "Vaia ..."situado á dereita do campo.



3. A ventá de complementos lanzouse. Na súa parte central está unha lista de complementos dispoñibles. Estableza a caixa de verificación xunto á posición Paquete de análise. Tras isto, fai clic no botón "Está ben" no lado dereito da interface da xanela.



4. Paquete de ferramentas "Análise de datos" na actual instancia de Excel activarase. O acceso a ela está situado na cinta da lapela "Datos". Pasamos á pestana especificada e facemos clic no botón "Análise de datos" no grupo de configuración "Análise".



5. A xanela está activada "Análise de datos" cunha lista de ferramentas especializadas de procesamento de información. Seleccione elemento desta lista "Regresión" e fai clic no botón "Está ben".



6. A continuación ábrese a xanela da ferramenta "Regresión". O primeiro bloque de configuracións é "Entrada". Aquí en dous campos é preciso especificar os enderezos dos intervalos onde están situados os valores do argumento e da función. Pon o cursor no campo "Intervalo de entrada Y" e selecciona o contido da columna na folla "Y". Despois de que o enderezo da matriz apareza na xanela "Regresión"coloca o cursor no campo "Intervalo de entrada Y" e seleccione as celas de columna exactamente do mesmo xeito "X".

   Sobre parámetros "Etiqueta" e Constant Cero non poñas bandeiras. A caixa de verificación pode configurarse xunto ao parámetro. "Nivel de fiabilidade" e no campo oposto, indique o valor desexado do indicador correspondente (por defecto o 95%).

   No grupo Opcións de saída ten que especificar en que área se mostrará o resultado do cálculo. Existen tres opcións:

   • A área da folla actual;
   • Outra folla;
   • Outro libro (novo ficheiro).

   Escollemos a primeira opción para que os datos de orixe e o resultado se coloquen na mesma folla de traballo. Poñemos o interruptor preto do parámetro "Intervalo de saída". No campo enfronte a este elemento, coloque o cursor. Faga clic co botón esquerdo do rato sobre un elemento baleiro da folla, deseñado para converterse na cela superior esquerda da táboa de saída de cálculo. O enderezo deste elemento debería mostrarse no campo da xanela "Regresión".

   Grupos de parámetros "Sobras" e "Probabilidade normal" Ignora porque non son importantes para resolver a tarefa. Despois diso, faga clic no botón "Está ben"situado na esquina superior dereita da xanela "Regresión".



7. O programa calcúlase en base a datos introducidos anteriormente e mostra o resultado no rango especificado. Como podes ver, esta ferramenta mostra un número bastante grande de resultados en varios parámetros nunha folla. Pero no contexto da lección actual, interésanos o indicador Cadrado R. Neste caso, é igual a 0,947664, o que caracteriza o modelo seleccionado como un modelo de boa calidade.

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

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

1. Temos unha gráfica baseada nunha táboa de argumentos e valores de función que se empregou para o exemplo anterior. Construiremos unha liña de tendencias para ela. Facemos clic en calquera lugar da área de construción sobre o 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". Vai á lapela "Maquetación". Fai clic no botón Liña de tendenciasque está situado no bloque de ferramentas "Análise". Aparece un menú coa elección do tipo de liña de tendencia. Detemos a elección do tipo que corresponde a unha tarefa específica. Escollemos unha opción para o noso exemplo "Aproximación exponencial".



2. Excel crea unha liña de tendencia en forma de curva negra adicional na gráfica.



3. Agora a nosa tarefa é amosar o propio coeficiente de determinación. Fai clic co botón dereito do rato na liña de tendencias. O menú contextual está activado. Paramos a selección nel "O formato da liña de tendencias ...".

   

   Para realizar a transición á xanela do formato de liña de tendencia, pode realizar unha acción alternativa. Seleccione a liña de tendencias facendo clic sobre ela co botón esquerdo do rato. Mover á pestana "Maquetación". Fai clic no botón Liña de tendencias en bloque "Análise". Na lista que se abre, faga clic no último elemento da lista de accións. "Parámetros adicionais da liña de tendencia ...".



4. Despois dunha das dúas accións anteriores, ábrese unha xanela de formato na que pode facer axustes adicionais. En concreto, para completar a nosa tarefa é necesario marcar a caixa xunto "Pon o valor de confianza aproximado (R ^ 2) no diagrama". Está situado na parte inferior da xanela. É dicir, deste xeito habilitamos a exhibición do coeficiente de determinación na área de construción. Entón non esquezas facer clic no botón Pechar na parte inferior da xanela actual.



5. O valor da fiabilidade da aproximación, é dicir, o valor do coeficiente de determinación, mostrarase nunha folla na área de construción. Neste caso, este valor, como vemos, é 0,9242, o que caracteriza a aproximación como un modelo de boa calidade.



6. Exactamente deste xeito, pode configurar a visualización do coeficiente de determinación para calquera outro tipo de liña de tendencia. Pode cambiar o tipo da liña de tendencia facendo unha transición a través do botón da cinta ou do menú contextual na xanela dos seus parámetros, como se mostra arriba. A continuación, na propia xanela do grupo "Construíndo unha liña de tendencias" Podes cambiar a outro tipo. Ao mesmo tempo, non esquezas controlar iso ao redor do punto "Pon o valor de confianza aproximado no diagrama" marcouse a caixa de verificación. Despois de cumprir os pasos anteriores, faga clic no botón Pechar na esquina inferior dereita da xanela.



7. Co tipo lineal, a liña de tendencias xa ten un valor de confianza aproximado igual a 0,9477, o que caracteriza este modelo como aínda máis fiable que a liña de tendencia do tipo exponencial considerada por nós anteriormente.



8. Así, cambiando entre diferentes tipos de liñas de tendencia e comparando os seus valores de confianza aproximados (coeficiente de determinación), pódese atopar a opción cuxo modelo describe con máis precisión o gráfico presentado. A opción con maior coeficiente de determinación será a máis fiable. Con base niso, pode crear a previsión máis precisa.

   Por exemplo, para o noso caso foi posible establecer que o tipo polinomial de liña de tendencias do segundo grao ten o maior nivel de confianza. O coeficiente de determinación neste caso é 1. Isto suxire que este modelo é absolutamente fiable, o que significa a exclusión completa de erros.

   

   Pero, ao mesmo tempo, isto non significa en absoluto que para outro gráfico este tipo de liña de tendencias tamén sexan as máis fiables. A elección óptima do tipo da liña de tendencias depende do tipo de función na base da cal se creou o gráfico. Se o usuario non ten coñecementos suficientes para estimar a opción de mellor calidade, entón o único xeito de determinar a mellor previsión é comparar os coeficientes de determinación, como se amosou no exemplo anterior.

Lea tamén:
Construír unha liña de tendencias en Excel
Aproximación en Excel

Hai dúas opcións principais para calcular o coeficiente de determinación en Excel: usar o operador KVPIRSON e uso de ferramentas "Regresión" dende a caixa de ferramentas "Análise de datos". Ademais, a primeira destas opcións está deseñada para o seu uso só no procesado dunha función lineal, ea outra opción pódese empregar en case todas as situacións. Ademais, é posible mostrar o coeficiente de determinación da liña de tendencia dos gráficos como un valor da fiabilidade da aproximación. Usando este indicador, é posible determinar o tipo de liña de tendencia que ten o maior nivel de confianza para unha determinada función.