Descripción de los efectos de las funciones estadísticas mejoradas de las Herramientas para análisis de Excel

Seleccione idioma Seleccione idioma
Id. de artículo: 829208 - Ver los productos a los que se aplica este artículo
Expandir todo | Contraer todo

En esta página

Resumen

En este artículo se describe el efecto de las mejoras numéricas realizadas en las funciones estadísticas de Microsoft Office Excel 2003 y de las versiones posteriores de Excel en las herramientas contenidas en Herramientas para análisis (ATP). La mayoría de las herramientas de ATP llaman a funciones estadísticas de Excel durante el proceso de calcular resultados. En muchos casos, este artículo actúa como puntero a artículos sobre las funciones estadísticas individuales de Excel. Además, en el caso de algunas herramientas de ATP se describen mejoras futuras que son útiles.

Información de Microsoft Excel 2004 para Mac

Las funciones estadísticas de Excel 2004 para Mac se actualizaron utilizando los mismos algoritmos empleados para actualizar las funciones estadísticas en Microsoft Office Excel 2003 y en versiones posteriores de Excel. Toda la información de este artículo que describe cómo actúa una función o cómo se ha modificado una función para Excel 2003 y para las versiones posteriores de Excel también se aplica a Excel 2004 para Mac.

Más información

El código de ATP no se ha modificado directamente excepto para introducir mejoras en las tres herramientas ANOVA de ATP.

En el caso de varias herramientas de ATP, se ha mejorado el rendimiento numérico para Excel 2003 y para las versiones posteriores de Excel porque la herramienta llama a una función estadística de Excel que se ha mejorado para Excel 2003 y para las versiones posteriores de Excel. En aquellos casos donde los resultados son diferentes para las versiones anteriores y las versiones posteriores de Excel, los valores para Excel 2003 y para las versiones posteriores de Excel son más precisos.

La mayoría de los usuarios no observarán ninguna diferencia en los resultados entre las distintas versiones de Excel. Esto se debe a que las diferencias suelen deberse a errores de redondeo que solamente son significativos en casos extremos. Sin embargo, este artículo debe señalar primero un caso en el que se producen diferencias debido a una fórmula equivocada en Microsoft Excel 2002 y en las versiones anteriores de Excel. Evite usar la herramienta en esas versiones.

Un segundo ejemplo se trata de una fórmula incorrecta en Excel 2002 y en las versiones anteriores de Excel que sigue existiendo en Excel 2003 y en las versiones posteriores de Excel. Evite usar esta herramienta de ATP para todas las versiones de Excel.

En primer lugar, evite utilizar la herramienta Regression cuando tenga que hacer clic para activar la casilla Constant is Zero. Se ha corregido en Excel 2003 y en las versiones posteriores de Excel. No tiene que evitar el uso de la herramienta de regresión cuando la casilla Constant is Zero está desactivada (el caso más típico en la práctica).

En segundo lugar, los usuarios de todas las versiones de Excel deben evitar el uso de la herramienta de ATP t-Test: Paired Two Sample for Means a menos que pueda garantizar que no falta ninguna observación de datos. La herramienta proporciona respuestas incorrectas (o no devuelve ninguna respuesta) si faltan una o más observaciones.

Para obtener más información acerca de la herramienta Matched Pairs Two Sample t-Test de ATP, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
829252 Puede obtener resultados incorrectos y etiquetas confusas cuando utiliza la prueba t de las Herramientas para análisis de Excel
Si desea utilizar esta herramienta y faltan datos (o incluso si existe la posibilidad de que falten datos), la función PRUEBA.T de Excel realizará correctamente el cálculo.

Más adelante en este artículo se ofrecen secciones independientes para las herramientas individuales de ATP. Las herramientas que no se muestran no se han visto afectadas por ninguna mejora en Excel 2003 ni en versiones posteriores de Excel.

ANOVA: Single Factor, Two-Factor with Replication y Two-Factor without Replication

Se ha vuelto a escribir cada una de estas tres herramientas ANOVA para actualizar el procedimiento de cálculo a un algoritmo de dos pasos que es más robusto numéricamente. Estas mejoras son similares a las realizadas en funciones estadísticas que calculan sumas de desviaciones cuadráticas sobre una media (por ejemplo: VAR, DESVEST, PENDIENTE, PEARSON).

Para obtener más información acerca de las herramientas ANOVA de ATP, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
829215 Descripción de la mejora numérica en las herramientas ANOVA de las Herramientas para análisis de Excel

Correlación

Esta herramienta no se ha modificado. Sin embargo, hay una pequeña diferencia entre las herramientas Correlación y Covarianza que se mantiene en todas las versiones de Excel. La herramienta Correlación devuelve una tabla de correlación triangular menor con números 1 en la diagonal y correlaciones fuera de la diagonal. La herramienta utiliza COEF.DE.CORREL para calcular las entradas fuera de la diagonal y rellena esas entradas con el valor devuelto por COEF.DE.CORREL. (Por tanto, si cambia alguna entrada de datos, no se modifica ninguna entrada de la tabla. Compare este comportamiento con el de Covarianza.)

Covarianza

Esta herramienta devuelve una tabla de covarianzas triangular menor con varianzas en la diagonal y covarianzas fuera de la diagonal. Las celdas de la diagonal contienen una fórmula "=VARP(...)" de forma que si una entrada de datos cambia, el resultado de la tabla también cambia. VARP se ha mejorado para Office Excel 2003 y para las versiones posteriores de Excel.

Para obtener más información acerca de VARP, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
826393 Funciones estadísticas de Excel: VARP
La herramienta Covarianza utiliza COVAR para calcular las entradas fuera de la diagonal y rellena esas entradas con el valor devuelto por COVAR. Por tanto, si una entrada de datos cambia, las entradas fuera de la diagonal no cambian.

Estadísticas descriptivas

Esta herramienta llama a funciones estadísticas de Excel para todo lo que calcula. Puesto que VAR y DESVEST se han mejorado para Excel 2003 y para las versiones posteriores de Excel, es posible obtener valores diferentes debido a errores de redondeo en casos extremos.

Para obtener más información acerca de VAR, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
826112 Funciones estadísticas de Excel: VAR

F-Test Two-Sample for Variances

Al igual que la herramienta Estadísticas descriptivas, esta herramienta llama a VAR. De nuevo, es posible obtener valores diferentes debido a errores de redondeo en casos extremos.

Generación de números aleatorios

Esta herramienta llena un rango con observaciones aleatorias. Los valores de estas observaciones se colocan directamente en las celdas, de forma que estos valores de celda no se vuelvan a calcular y se reemplacen con observaciones nuevas cuando se actualice la hoja. Por otra parte, la función integrada ALEATORIO de Excel reemplaza los números aleatorios existentes con otros nuevos cada vez que se actualiza la hoja. Puede utilizar ALEATORIO para conservar valores. Para ello, copie los resultados de un rango y utilice el comando Pegado especial para pegar los valores en el mismo rango.

La herramienta Generación de números aleatorios (RNG) también genera observaciones aleatorias a partir de varias distribuciones de probabilidad mientras que ALEATORIO corresponde a la única opción de la herramienta: Uniform con rango entre 0 y 1. En este artículo se describe cómo combinar ALEATORIO con las funciones estadísticas de Excel para generar esas observaciones.

Por tanto, en lo que se refiere a la funcionalidad, puede emular la herramienta de números aleatorios de ATP utilizando ALEATORIO y un poco de ingenio. A veces esto es útil, especialmente cuando se desean muchos números aleatorios.

En Excel 2002 y en versiones anteriores, se sabe que el generador de números aleatorios de ATP y ALEATORIO tienen un rendimiento bajo en las pruebas estándar de aleatoriedad. Este bajo rendimiento se debe a que la longitud de un ciclo antes de que la secuencia de números pseudoaleatorios empiece a repetirse era demasiado corta. Esto sólo supone un problema cuando se requieren muchos números aleatorios.

ALEATORIO se ha mejorado en Excel 2003 y en las versiones posteriores de Excel, por lo que ahora supera todas esas pruebas estándar. La secuencia de números aleatorios de ALEATORIO empezará a repetirse a sí misma una vez generados más de 1 billón de números.

Para obtener más información acerca de ALEATORIO, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
828795 Descripción de la función ALEATORIO en Excel 2007 y en Excel 2003
Sin embargo, el generador de números aleatorios independiente de ATP no se ha actualizado. Como ocurre en la versión de ALEATORIO incluida en Excel 2002 y en versiones anteriores de Excel, se sabe que el generador de números aleatorios independiente de ATP tiene un rendimiento bajo en las pruebas estándar de aleatoriedad y un ciclo de repetición corto. Esto tiene implicaciones negativas solamente si necesita una secuencia muy larga de números aleatorios (por ejemplo, 1 millón).

La herramienta RNG proporciona observaciones aleatorias a partir de varias distribuciones de probabilidad y Uniform[0,1], la distribución utilizada para generar números aleatorios mediante ALEATORIO. La herramienta de ATP extrae primero un número aleatorio Uniform[0,1] (o más de uno de esos números) y, a continuación, convierte la respuesta en una observación a partir de una de las distribuciones específicas siguientes. Para aquellos que prefieran utilizar ALEATORIO porque vayan a generar muchas observaciones, en este artículo se sugieren fórmulas que utilizan ALEATORIO en la tabla siguiente. A continuación de la tabla se ofrecen algunos comentarios cautelares sobre la distribución normal de las Herramientas para análisis.
Contraer esta tablaAmpliar esta tabla
DistribuciónFórmula de Excel utilizando ALEATORIO()
Bernoulli(p)=SI(ALEATORIO() <= p, 1, 0)
Binomial(n,p)=BINOM.CRIT(n, p, ALEATORIO())
DiscreteVer abajo
Normal(mu, sigma)=DISTR.NORM.INV(ALEATORIO(), mu, sigma)
PatternedNo realmente aleatorio
Poisson(mean)Ver abajo
Uniform(low, high)= low + (high ? low) * ALEATORIO()
Hay dos razones por las que podría preferir el uso de ALEATORIO y la fórmula de esta tabla en lugar de la herramienta de números aleatorios de ATP en el caso Normal(mu, sigma). En primer lugar, ALEATORIO es un generador de números aleatorios Uniform[0,1] mejor que la herramienta de ATP. En segundo lugar, la herramienta de ATP no llama a la función DISTR.NORM.INV de Excel, sino que tiene su propia versión integrada de la distribución normal inversa. Esto no es tan preciso como la versión de DISTR.NORM.INV incluida en Excel 2003 y en las versiones posteriores de Excel. Es peor tanto en cuanto a la precisión de la aproximación de la distribución normal que utiliza (Excel utiliza la función DISTR.NORM.INV mucho más mejorada) como en lo que respecto al refinamiento de la búsqueda binaria (Excel lo lleva mucho más allá para garantizar un valor más cercano al argumento de probabilidad de DISTR.NORM.INV). En resumen, el uso de ATP en este caso no aprovecha las mejoras realizadas para Excel 2003 y para las versiones posteriores de Excel en las funciones DISTR.NORM.INV, DISTR.NORM.ESTAND y ALEATORIO.

Para las observaciones de una distribución discreta, suponga que los valores están en la columna B y que sus probabilidades están en la columna C. Se desea rellenar cada fila de la columna A con la probabilidad de observar un valor estrictamente menor que el valor de la columna B de esa fila. Suponiendo que haya 10 valores, suponga que estos datos están en las celdas A1:C10. Entonces, puesto que A1 contiene la probabilidad de observar un valor estrictamente menor que el primer valor, se debe establecer en 0. Puede utilizar BUSCARV(ALEATORIO(), A1:C10, 2); el cuarto argumento de BUSCARV es opcional y se debe omitir o establecer en TRUE. El "2" significa que desea devolver el valor en la segunda columna (la columna B en este ejemplo).

La herramienta de ATP utiliza una adaptación del método de generación de observaciones de Poisson descrito en Press, W.H., S.A. Teukolsky, W. T. Vetterling y B.P. Flannery, Numerical Recipes in C, The Art of Scientific Computing, 2nd ed., Cambridge University Press, 1992, pp. 293-295. Hay dos métodos de aprovechar fácilmente las funciones existentes de Excel.

El primero utiliza la observación de que una variable aleatoria de POISSON con una media m tiene una distribución a la que se aproxima bien BINOMIAL(n, m/n) para n grande. Puede llamar a continuación a BINOM.CRIT(n, m/n, ALEATORIO()). La elección de n depende de m; n mayor que 1000 veces m debería ser suficientemente grande.

El segundo relaciona la distribución de POISSON con el exponencial. Si se producen eventos según un proceso de POISSON a una velocidad m por unidad de tiempo, el tiempo que transcurre entre los eventos tiene una distribución exponencial con una media de 1/m. Para una observación de POISSON puede tomar una secuencia de observaciones de esta distribución exponencial y contar cuántos de ellos se producen antes de que su suma supere 1. Para obtener una observación a partir de esta distribución exponencial, utilice DISTR.GAMMA.INV(ALEATORIO(), 1, 1/m). Este método es adecuado cuando m sea relativamente cercano a 0.

Regresión

La herramienta de regresión llama a la función ESTIMACION.LINEAL de Excel. En el artículo dedicado a ESTIMACION.LINEAL se describen las numerosas mejoras realizadas en esta función para Excel 2003 y para las versiones posteriores de Excel.

Para obtener más información acerca de ESTIMACION.LINEAL, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:
828533 Descripción de la función ESTIMACION.LINEAL en Excel
Si utiliza Excel 2002 o una versión anterior de Excel, debe tener en cuenta las mismas dos limitaciones de la herramienta de regresión de ATP que las de ESTIMACION.LINEAL:
  • Los valores estadísticos de suma de los cuadrados, r cuadrado y f de la herramienta de regresión siempre son incorrectos cuando la regresión se fuerza a través del origen.

    En el caso de ESTIMACION.LINEAL, esto significa que el "tercer argumento se establece en FALSE en lugar de en TRUE o se omite". En el caso de la herramienta de ATP, significa "la casilla Constant is Zero está activada".
  • ESTIMACION.LINEAL y la herramienta de ATP son insensibles a los problemas de multicolinealidad. En el artículo dedicado a ESTIMACION.LINEAL se describe el método de cálculo de dicha función en Excel 2003 y en versiones posteriores de Excel que está diseñado para buscar la multicolinealidad o la multicolinealidad cercana cuando existe y actuar en consecuencia.
Ambas limitaciones de ESTIMACION.LINEAL se han resuelto en Excel 2003 y en las versiones posteriores de Excel. El rendimiento de la herramienta de regresión de ATP se mejorará del mismo modo. No se ha realizado ningún cambio en el código de la herramienta; se ha mejorado llamando a una función mejorada de Excel. Este autor considera que la mejora de ESTIMACION.LINEAL es la más importante de las mejoras realizadas a las funciones estadísticas.

En la tabla siguiente se muestra el resultado de la herramienta de regresión para las versiones anteriores de Excel y para las versiones posteriores de Excel con la casilla Constant is Zero activada. Ilustra la primera limitación mencionada anteriormente. En las versiones anteriores de Excel, la suma de los cuadrados de la herramienta de regresión es negativa, al igual que el valor R cuadrado.
Contraer esta tablaAmpliar esta tabla
XY
111
212
313
Excel 2002 y versiones anteriores
RESUMEN DEL RESULTADO
Estadísticas de regresión
R múltiple65535
R cuadrado-20.4285714
R cuadrado ajustado-20.9285714
Error típico4.629100499
Observaciones3
ANOVA
dfSSMSFSignificancia F
Regresión1-40.85714286-40.85714286-1.90666667¡#NUM!
Residual242.8571428621.42857143
Total32
Excel 2003 y versiones posteriores de Excel
RESUMEN DEL RESULTADO
Estadísticas de regresión
R múltiple0.949342311
R cuadrado0.901250823
R cuadrado ajustado0.401250823
Error típico4.629100499
Observaciones3
ANOVA
dfSSMSFSignificancia F
Regresión1391.1428571391.142857118.253333330.14637279
Residual242.8571428621.42857143
Total3434

t-Test: Paired Two Sample for Means

Como se ha mencionado anteriormente, evite el uso de esta herramienta si hay alguna posibilidad de que falten uno o más valores de datos. La aplicación prototipo de esta prueba es un experimento con medidas en sujetos Antes y Después de un tratamiento (como los pesos Antes y Después de una dieta de 60 días). Si no falta ninguna observación, la herramienta se comportará correctamente. Si faltan números diferentes de observaciones Antes y Después, recibirá un mensaje de error y la herramienta no calculará nada. Si faltan observaciones y los números de observaciones Antes y Después son iguales, la herramienta devolverá respuestas que contendrán varios errores.

El procedimiento estándar consiste en quitar un sujeto de los datos si falta la medida Antes o Después y analizar los datos que contienen solamente aquellos sujetos que tienen ambas medidas Antes y Después. La función PRUEBA.T de Excel trata los datos que faltan según este procedimiento estándar.

Las otras dos herramientas de prueba t, Two-Sample Assuming Equal Variances y Two-Sample Assuming Unequal Variances, no comparten este defecto.

z-Test: Two Sample for Means

En este artículo se ha indicado que el caso de la distribución normal de la herramienta de generación de números aleatorios no llama a la función DISTR.NORM.ESTAND.INV (o, en concreto, DISTR.NORM.INV llama a DISTR.NORM.ESTAND.INV) pero tiene su propio procedimiento inferior para buscar los valores inversos normales.

La herramienta de la prueba z llama a la función DISTR.NORM.ESTAND.INV y aprovecha las mejoras realizadas en ella para Excel 2003 y para las versiones posteriores de Excel.

Resultados en versiones anteriores de Excel

Hay herramientas de ATP cuyo rendimiento se ha mejorado en Excel 2003 y en las versiones posteriores de Excel porque llaman a funciones estadísticas de Excel mejoradas para Excel 2003 y para las versiones posteriores de Excel. Una de estas mejoras realizadas en ESTIMACION.LINEAL, cuando su tercer argumento se establece en FALSE, implica que la herramienta de regresión de ATP devuelve resultados incorrectos en Excel 2002 y en versiones anteriores de Excel cuando la casilla Constant is Zero está activada. En otros casos donde se han mejorado las funciones de Excel, no es probable que los usuarios de versiones anteriores observen diferencias (la mayoría de estas diferencias están relacionadas con errores de redondeo en situaciones extremas).

Las tres herramientas ANOVA de ATP se han mejorado editando el código de ATP para sustituir un algoritmo más robusto numéricamente (con el mismo espíritu que la mejora de la función VAR de Excel). Es probable que los usuarios de estas herramientas en versiones anteriores de Excel solamente observen diferencias en situaciones extremas.

Advertencia para usuarios de todas las versiones: evite la herramienta t-Test: Paired Two Sample for Means si existe la más mínima posibilidad de que falten datos.

Resultados en Excel 2003 y en versiones posteriores de Excel

Se han realizado importantes mejoras en las funciones estadísticas de Excel. Esto se traduce en mejoras en muchas herramientas de ATP que llaman a estas funciones. Hay una herramienta de ATP, el generador de números aleatorios, que no aprovecha una función ALEATORIO mejorada (porque se implementa de manera autocontenida y no llama a ALEATORIO). Es desafortunado, pero más desafortunado es aún el caso especial de las observaciones aleatorias distribuidas normalmente. La distribución normal inversa también se implementa de manera autocontenida y no llama a la función DISTR.NORM.ESTAND.INV mucho más mejorada.

En la tabla siguiente se muestran herramientas de ATP y las funciones de Excel a las que llaman y que se han mejorado para Excel 2003 y para las versiones posteriores de Excel. Se remite a los lectores a artículos independientes sobre cada función de Excel a la que se llama.
Contraer esta tablaAmpliar esta tabla
Herramienta de ATPFunciones de Excel a las que se llama
ANOVA: Single FactorVAR, DISTR.F.INV
ANOVA: Two-Factor With ReplicationVAR, DISTR.F.INV
ANOVA: Two-Factor Without ReplicationVAR, DISTR.F.INV
Correlación
Covarianza
Estadísticas descriptivasDESVEST, DISTR.T.INV, VAR
Suavizado exponencial
F-Test Two-Sample for VariancesVAR, DISTR.F.INV
Análisis de Fourier
Histograma
Media móvil
Generación de números aleatorios
Rango y percentil
RegresiónESTIMACION.LINEAL
MuestreoALEATORIO
t-Test: Paired Two Sample for MeansVAR, PEARSON, DISTR.T.INV
t-Test: Two-Sample Assuming Equal VariancesVAR, DISTR.T.INV
t-Test: Two-Sample Assuming Unequal VariancesVAR, DISTR.T.INV
z-Test: Two Sample for MeansDISTR.NORM.ESTAND, DISTR.NORM.ESTAND.INV
En el caso de todas las funciones que aparecen en esta tabla aparte de ESTIMACION.LINEAL y ALEATORIO, es probable que sólo vea diferencias entre las versiones anteriores de Excel y las versiones posteriores de Excel debido a errores de redondeo en situaciones extremas. Se ha mejorado mucho ESTIMACION.LINEAL, como se ha descrito anteriormente. También se ha mejorado ALEATORIO. Curiosamente, la herramienta de muestreo llama a ALEATORIO, pero la herramienta RNG utiliza un generador autocontenido que obtendrá un rendimiento inferior cuando se requiera una secuencia larga de observaciones aleatorias.

Conclusiones

Excepto los cambios realizados en el código de ATP para las tres herramientas ANOVA, no se ha vuelto a escribir el código de ATP. Sin embargo, se beneficia de llamar a funciones de Excel mejoradas como se muestra en la tabla de herramientas de ATP. Los errores en la prueba t-Test: Paired Two Sample for Means no se han corregido para Excel 2003 ni para las versiones posteriores de Excel. Es probable que las mejoras más destacables estén en la herramienta de regresión, donde ESTIMACION.LINEAL ya no devuelve resultados incorrectos cuando la casilla Constant is Zero está activada y donde ESTIMACION.LINEAL está diseñada para tratar correctamente la multicolinealidad.

Propiedades

Id. de artículo: 829208 - Última revisión: viernes, 25 de enero de 2008 - Versión: 4.0
La información de este artículo se refiere a:
  • Microsoft Office Excel 2007
  • Microsoft Office Excel 2003
  • Microsoft Excel 2004 for Mac
Palabras clave: 
kbinfo kbformula kbfunctions kbfuncstat kbexpertisebeginner KB829208

Enviar comentarios

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com