Funciones estadísticas de Excel: RSQ

Resumen

En este artículo se describe la función RSQ en Microsoft Office Excel 2003 y en versiones posteriores de Excel. En este artículo se describe cómo se usa la función y se comparan los resultados de RSQ en estas versiones posteriores de Excel con los resultados de RSQ en versiones anteriores de Excel.

Más información

La función RSQ(array1, array2) devuelve el cuadrado de Pearson Product-Moment coeficiente de correlación entre dos matrices de datos.

Sintaxis

RSQ(array1, array2)

Los argumentos, array1 y array2, deben ser números o nombres, constantes de matriz o referencias que contengan números.

El uso más común de RSQ incluye dos rangos de celdas que contienen los datos, como RSQ(A1:A100, B1:B100).

Ejemplo de uso

Para ilustrar la función RSQ, siga estos pasos:

1. Cree una hoja de cálculo de Excel en blanco y copie la tabla siguiente.

   A	B	C	D
   1		= 3 + 10^$D$2	Potencia de 10 para agregar a los datos
   2	=4 + 10^$D$2		0
   3	=2 + 10^$D$2
   4	=5 + 10^$D$2
   5	=4+10^$D$2
   6	=7+10^$D$2		antes de Excel 2003
   =RSQ(A1:A6,B1:B6)			cuando D2 = 7,5
   =PEARSON(A1:A6,B1:B6)^2		RSQ = PEARSON^2	0.492857142857143
   =CORREL(A1:A6,B1:B6)^2		CORREL^2	0.509470304975923
   			cuando D2 = 8
   		RSQ = PEARSON^2	#DIV/0!
   		CORREL^2	0.509470304975923

2. Seleccione la celda A1 en la hoja de cálculo de Excel en blanco y pegue las entradas para que la tabla rellene las celdas A1:D13 en la hoja de cálculo.

3. Después de pegar la tabla en la nueva hoja de cálculo de Excel, haga clic en el botón Opciones de pegado y, a continuación, haga clic en Coincidir formato de destino. Con el rango pegado aún seleccionado, use uno de los procedimientos siguientes, según corresponda para la versión de Excel que está ejecutando:

   • En Microsoft Office Excel 2007, haga clic en la pestaña Inicio , haga clic en Formato en el grupo Celdas y, a continuación, haga clic en Ajustar automáticamente el ancho de columna.
   • En Excel 2003, seleccione Columna en el menú Formato y, a continuación, haga clic en Autoajustar selección.

Las celdas A1:A6 y B1:B6 contienen las dos matrices de datos que se usan en este ejemplo para llamar a RSQ, PEARSON y CORREL en las celdas A8:A10. RSQ se calcula básicamente calculando PEARSON y cuadrando el resultado. Dado que PEARSON y CORREL calculan el coeficiente de correlación Product-Moment Pearson, sus resultados deben estar de acuerdo. RSQ podría haberse implementado (pero no) como esencialmente calcular CORREL y cuadrar el resultado.

En las versiones de Excel anteriores a Excel 2003, PEARSON puede presentar errores de redondeo. Este comportamiento conduce a errores de redondeo en RSQ. El comportamiento de PEARSON y, por tanto, de RSQ, se ha mejorado para Excel 2003 y para versiones posteriores de Excel. CORREL siempre se ha implementado mediante el procedimiento mejorado que se encuentra en Excel 2003 y en versiones posteriores de Excel. Por lo tanto, una alternativa a RSQ para una versión anterior de Excel es usar CORREL en su lugar y, a continuación, cuadrar el resultado.

En las versiones de Excel anteriores a Excel 2003, puede usar la hoja de cálculo de este artículo para ejecutar un experimento y detectar cuándo se producen errores de redondeo. Si agrega una constante a cada una de las observaciones de B1:B6, los valores de RSQ, PEARSON^2 y CORREL^2 en las celdas A7:A9 no deben verse afectados. Si aumenta el valor en D2, se agrega una constante mayor a B1:B6. Si D2 <= 7, no hay errores de redondeo que aparezcan en A7:A9. Ahora cambie el valor de 7,25, 7,5, 7,75 y, a continuación, 8. CORREL^2 en A9 no se ve afectado, pero RSQ y PEARSON^2 ( (estas expresiones siempre coinciden entre sí) muestran errores de redondeo en A7:A8. D6:D13 muestra los valores de RSQ = PEARSON^2 y CORREL^2 cuando D2 = 7,5 y 8, respectivamente.

Tenga en cuenta que CORREL todavía se comporta bien, pero los errores de redondeo en PEARSON se han vuelto tan graves que la división por 0 se produce en RSQ y PEARSON^2 cuando D2 = 8.

Las versiones anteriores de Excel muestran respuestas incorrectas en estos casos porque los efectos de los errores de redondeo son más profundos con la fórmula de cálculo que usan estas versiones de Excel. Aun así, los casos que se usan en este experimento pueden considerarse extremos.

Si tiene Excel 2003 o una versión posterior de Excel, no verá ningún cambio en los valores de RSQ y PEARSON^2 si prueba el experimento. Sin embargo, las celdas D6:D13 muestran errores de redondeo que habría obtenido con versiones anteriores de Excel.

Resultados en versiones anteriores de Excel

Si asigna un nombre a las dos matrices de datos X y Y, las versiones anteriores de Excel usaron un único paso a través de los datos para calcular la suma de cuadrados de X, la suma de cuadrados de Y, la suma de X, la suma de Y, la suma de Y, la suma de XY y el recuento del número de observaciones en cada matriz. Estas cantidades se combinaron a continuación en la fórmula de cálculo que se da en el archivo de Ayuda en versiones anteriores de Excel. El archivo de ayuda de RSQ muestra la fórmula del coeficiente de correlación Product-Moment Pearson. Este resultado se cuadrado para obtener RSQ.

Resultados en Excel 2003 y en versiones posteriores de Excel

El procedimiento que se usa en Excel 2003 y en versiones posteriores de Excel usa un proceso de dos pasos a través de los datos. En primer lugar, se calculan las sumas de X y Y y el recuento del número de observaciones en cada matriz, y a partir de ellos se pueden calcular los medios (promedios) de las observaciones X e Y. A continuación, en el segundo paso, se encuentra la diferencia cuadrada entre cada X y la media X, y estas diferencias cuadradas se suman. Se encuentra la diferencia cuadrada entre cada Y y la media Y, y estas diferencias cuadradas se resumen. Además, los productos (media X – X) * (Y – media Y) se encuentran para cada par de puntos de datos y se suman. Estas tres sumas se combinan en la fórmula para PEARSON. Observe que ninguna de las tres sumas se ve afectada si agrega una constante a cada valor de la matriz Y (o en la matriz X). Este comportamiento se produce porque ese mismo valor se agrega a la media Y (o a la media X). En los ejemplos numéricos, incluso con una potencia alta de 10 en la celda D12, estas tres sumas no se ven afectadas y los resultados del segundo paso son independientes de la entrada de la celda D2. Por lo tanto, los resultados en Excel 2003 y en versiones posteriores de Excel son más estables numéricamente.

Conclusiones

Reemplazar un enfoque de un solo paso por un enfoque de dos pasos garantiza un mejor rendimiento numérico de PEARSON y, por tanto, RSQ, en Excel 2003 y en versiones posteriores de Excel. Los resultados que obtenga en Excel 2003 y en versiones posteriores de Excel nunca serán menos precisos que los que obtuvo en versiones anteriores de Excel.

En la mayoría de los ejemplos prácticos, no es probable que vea una diferencia entre los resultados en versiones posteriores de Excel y los resultados en versiones anteriores de Excel. Este comportamiento se produce porque es poco probable que los datos típicos muestren el tipo de comportamiento inusual que muestra este experimento. Es más probable que la inestabilidad numérica aparezca en versiones anteriores de Excel cuando los datos contienen un gran número de dígitos significativos combinados con relativamente poca variación entre los valores de datos.

El procedimiento de búsqueda de la suma de las desviaciones cuadradas sobre una media de muestra mediante la búsqueda de la media de la muestra, calculando cada desviación cuadrada y sumando las desviaciones cuadradas es más preciso que el procedimiento alternativo. Este procedimiento alternativo se denominaba con frecuencia "fórmula de calculadora" porque era adecuado para el uso de una calculadora en un pequeño número de puntos de datos. El procedimiento alternativo utilizó el siguiente procedimiento:

• Se encontró la suma de cuadrados de todas las observaciones, el tamaño de la muestra y la suma de todas las observaciones.
• Calculó la suma de cuadrados de todas las observaciones menos ([suma de todas las observaciones]^2)/tamaño de la muestra).

Hay muchas otras funciones que se han mejorado para Excel 2003 y para versiones posteriores de Excel. Estas funciones se mejoran porque las versiones posteriores de Excel reemplazan el procedimiento de paso único por el procedimiento de dos pasos que encuentra la media de muestra en el primer paso y, a continuación, calcula la suma de las desviaciones cuadradas sobre la media de la muestra en el segundo paso.

La lista siguiente es una lista de estas funciones:

• VAR
• VARP
• STDEV
• STDEVP
• DVAR
• DVARP
• DSTDEV
• DSTDEVP
• PREVISIÓN
• PENDIENTE
• INTERCEPTAR
• PEARSON
• LRQ
• STEYX

Se realizaron mejoras similares en cada una de las tres herramientas De análisis de varianza del ToolPak de análisis.