Funciones estadísticas de Excel: STEYX

Resumen

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

Más información

La función STEYX(known_y, known_x) devuelve el error estándar de Y dado X para una línea de regresión lineal de mínimos cuadrados que se usa para predecir valores y a partir de valores x.

Sintaxis

STEYX(known_y's,known_x's)

Los argumentos, known_y y known_x, deben ser matrices o intervalos de celdas que contengan el mismo número de valores de datos numéricos.

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

Ejemplo de uso

Para ilustrar la función STEYX, cree una hoja de cálculo de Excel en blanco, copie la tabla siguiente, 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:D12 en la hoja de cálculo.

A B C D
y-values valores x
1 = 3 + 10^$D$3 Potencia de 10 para agregar a los datos
2 =4 + 10^$D$3 0
3 =2 + 10^$D$3
4 =5 + 10^$D$3
5 =4+10^$D$3
6 =7+10^$D$3 antes de Excel 2003
cuando D3 = 7,5
=STEYX(A2:A7,B2:B7) 1.48954691097662
cuando D3 = 8
#DIV/0!

Después de pegar esta 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 Selección de ajuste automático.

Es posible que desee dar formato a las celdas B2:B7 como Número con 0 posiciones decimales y la celda A9:D9 como Número con seis posiciones decimales.

Las celdas A2:A7 y B2:B7 contienen los valores y y y x que se usan para llamar a STEYX en la celda A9.

Si tiene una versión de Excel anterior a Excel 2003, debe saber que STEYX puede presentar errores de redondeo. El comportamiento de STEYX se ha mejorado para Excel 2003 y para versiones posteriores de Excel.

Si tiene una versión anterior de Excel, la hoja de cálculo le ofrece la oportunidad de ejecutar un experimento y detectar cuándo se producen errores de redondeo. Agregar una constante positiva a cada una de las observaciones de B2:B7 no debe afectar al valor de STEYX. Si trazara pares x,y con x en el eje horizontal y y en el eje vertical, agregar una constante positiva a cada valor x desplazaría los datos a la derecha. La línea de regresión de mejor ajuste seguiría teniendo la misma pendiente y bondad de ajuste y debería tener el mismo valor de STEYX.

Al aumentar el valor de D3, se agrega una constante más grande a B2:B7. Si D2 <= 7, no hay errores de redondeo que aparezcan en las seis primeras posiciones decimales de STEYX. Pero luego pruebe 7.25, 7.5, 7.75 y 8. D7:D12 muestra los valores de STEYX cuando D2 = 7,5 y 8 respectivamente. Los errores de redondeo se han vuelto tan graves que la división por 0 se produce cuando D3 = 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. Aun así, los casos que se usan en este experimento se pueden ver como bastante extremos.

Si tiene Excel 2003 o una versión posterior de Excel, no verá ningún cambio en los valores de STEYX si prueba el experimento descrito anteriormente. Sin embargo, las celdas D7:D12 muestran errores de redondeo que habría obtenido mediante versiones anteriores de Excel.

Resultados en versiones anteriores de Excel

Si llama 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 proporciona en el archivo de Ayuda en versiones anteriores de Excel.

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 e Y y el recuento del número de observaciones de 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 pase,

  • 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 suman, y
  • los productos (X – X mean) * (Y – Y mean) se encuentran para cada par de puntos de datos y se suman.

A continuación, STEYX se calcula mediante la fórmula del archivo de Ayuda para STEYX en Excel 2003 y en versiones posteriores de Excel. Observe que ninguna de estas tres sumas se ve afectada al agregar una constante a cada valor X, ya que ese mismo valor se agrega a la media X. En los ejemplos numéricos, incluso con una potencia alta de 10 en la celda D3, estas tres sumas no se ven afectadas y los resultados del segundo paso son independientes de la entrada en la celda D3. 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 STEYX en Excel 2003 y en versiones posteriores de Excel. Los resultados en Excel 2003 y en versiones posteriores de Excel nunca serán menos precisos que los resultados de versiones anteriores.

Sin embargo, 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. Esto se debe a que 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.

Procedimiento para encontrar la suma de las desviaciones cuadradas sobre la media de una muestra

  • encontrar la media de ejemplo,
  • calcular cada desviación cuadrada, y
  • sumando las desviaciones cuadradas

es más preciso que el procedimiento alternativo. Este procedimiento se denomina 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. La fórmula de calculadora usa los procedimientos siguientes:

  • Busque la suma de cuadrados de todas las observaciones, el tamaño de la muestra y la suma de todas las observaciones.
  • Calcule 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 mejoraron reemplazando el procedimiento de paso único por el procedimiento de dos pasos que encuentra la media de muestra en el primer paso y calcula la suma de las desviaciones cuadradas sobre la media de la muestra en el segundo paso.

Las funciones que se han mejorado de esta manera para Excel 2003 y para versiones posteriores de Excel incluyen las siguientes 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.