Funciones estadísticas de Excel: LOGEST

Resumen

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

LOGEST se evalúa llamando a la función relacionada, LINEST. Se resumen amplios cambios en LINEST para Excel 2003 y para versiones posteriores de Excel, y se indican sus implicaciones para LOGEST.

Información de Microsoft Excel 2004 para Macintosh

Las funciones estadísticas de Microsoft Excel 2004 para Macintosh se actualizaron con los mismos algoritmos que Excel 2003 y versiones posteriores de Excel. Cualquier información de este artículo que describa cómo funciona una función o que describe cómo se modificó una función para Excel 2003 y para versiones posteriores de Excel también se aplica a Excel 2004 para Macintosh.

Más información

La función LOGEST(known_y, known_x, constant, statistics) se usa para realizar la regresión donde se ajusta una curva exponencial. Se usa un criterio de mínimos cuadrados y LOGEST intenta encontrar el mejor ajuste bajo ese criterio. Known_y representan datos en la variable dependiente y los datos de known_x representan datos en una o varias variables independientes. El segundo argumento es opcional. Si se omite, se supone que es una matriz del mismo tamaño que known_y que contiene los valores (1, 2, 3, ...).

Suponiendo que haya variables de predictor p, LOGEST se ajusta a una ecuación de la siguiente forma:

y = b * (m1^x1) * (m2^x2) * … * (mp^xp)

Se determinan los valores de los coeficientes, b, m1, m2, ..., mp que mejor se ajusten a los datos y.

El último argumento de LOGEST se establece en TRUE si desea estadísticas adicionales, incluidas varias sumas de cuadrados, r cuadrado, estadística f y errores estándar de los coeficientes de regresión. En este caso, LOGEST debe especificarse como una fórmula de matriz. El último argumento es opcional. Si se omite, se interpreta como FALSE. Las dimensiones de la matriz son cinco filas por un número de columnas igual al número de variables independientes más una si el tercer argumento está establecido en TRUE (más 0 si el tercer argumento no está establecido en TRUE).

Si el tercer argumento "constante" se establece en TRUE, quiere que el modelo de regresión incluya la constante b en la ecuación anterior en su modelo. Si se establece en FALSE, la constante multiplicativa b se excluye del modelo de regresión forzándola a una. El tercer argumento es opcional. Si se omite, se interpreta como TRUE.

En este artículo, suponga que los datos están organizados en columnas de modo que known_y es una columna de datos y y known_x es una o varias columnas de datos x. Las dimensiones o longitudes de cada una de estas columnas deben ser iguales. Todas las observaciones siguientes son igualmente verdaderas si los datos no están organizados en columnas, pero es más fácil analizar el caso que se usa con más frecuencia. Además, suponga que el último argumento de LOGEST siempre es TRUE y que siempre le interesa una salida detallada. Esta suposición ayuda a revelar problemas numéricos en versiones anteriores de Excel. Algunos problemas numéricos siguen presentes si usa FALSE para este argumento.

En este artículo se usan los ejemplos siguientes para mostrar cómo LOGEST se relaciona con LINEST y para señalar problemas con LINEST en versiones anteriores de Excel que se traducen en problemas con LOGEST. Aunque el código de LOGEST no se ha reescrito para Excel 2003 y para versiones posteriores de Excel, se han realizado grandes cambios y mejoras en el código para LINEST. LOGEST llama eficazmente a LINEST, ejecuta LINEST, modifica la salida linest y la presenta. Por lo tanto, debe conocer los problemas en la ejecución de LINEST. Para obtener información adicional sobre LINEST, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

828533 Descripción de la función LINEST en Excel 2003 y en versiones posteriores de Excel

El archivo de ayuda linest también se ha revisado para Excel 2003 y para versiones posteriores de Excel, y es útil para obtener más información. Dado que el foco de este artículo se centra en problemas numéricos en versiones anteriores de Microsoft Excel, este artículo no incluye muchos ejemplos prácticos de LOGEST. El archivo de ayuda de LOGEST contiene dos ejemplos útiles.

Sintaxis

LOGEST(known_y's, known_x's, constant, statistics)

Los argumentos, known_y y known_x, deben ser matrices o rangos de celdas que tengan dimensiones relacionadas. Si known_y es una columna por filas m, las de known_x deben ser columnas c por filas m donde c es mayor o igual que una. C es el número de variables de predictor; m es el número de puntos de datos. Las relaciones similares deben mantenerse si la de known_y está establecida en una sola fila; en ese caso, los known_x deben estar en filas r. (R debe ser mayor o igual que 1). Constantes y estadísticas son argumentos lógicos que deben establecerse en TRUE o FALSE. En Excel, estos argumentos deben establecerse en 0 o 1 y Excel los interpreta como FALSE o TRUE, respectivamente. Los tres últimos argumentos de LOGEST son opcionales. Si omite el segundo argumento, LOGEST supone un único predictor que contiene las entradas {1, 2, 3, ...}. Si omite el tercer argumento, se interpreta como TRUE. Si omite el cuarto argumento, se interpreta como FALSE.

El uso más común de LOGEST incluye dos rangos de celdas que contienen los datos, como LOGEST(A1:A100, B1:F100, TRUE, TRUE). Dado que normalmente hay más de una variable de predictor, el segundo argumento de este ejemplo contiene varias columnas. En este ejemplo, hay 100 asuntos, un valor de variable dependiente, known_y, para cada sujeto, y cinco valores de variable dependientes, known_x, para cada sujeto.

Ejemplo de uso

Se proporcionan dos ejemplos de hoja de cálculo de Excel independientes para ilustrar los siguientes conceptos clave:

• Interacción de LOGEST con LINEST
• Problemas en Microsoft Excel 2002 y en versiones anteriores de Excel cuando el tercer argumento de LOGEST o LINEST se establece en FALSE o se omite.
• Problemas debidos a colinear known_x en LOGEST o LINEST en Excel 2002 y versiones anteriores.

Para obtener información adicional sobre el segundo y tercer concepto de esta lista, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

828533 Descripción de la función LINEST en Excel 2003 y en versiones posteriores de Excel

Para ilustrar LOGEST con el tercer argumento establecido en FALSE, siga estos pasos:

1. Cree una hoja de cálculo de Excel en blanco y copie la tabla más adelante en esta sección.

2. Haga clic en la celda A1 en la hoja de cálculo de Excel en blanco. A continuación, pegue las entradas para que la tabla rellene las celdas A1:H17 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 Opciones de pegadoy, a continuación, haga clic en Coincidir formato de destino.

4. Aunque el rango pegado todavía está 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 y en versiones anteriores de Excel, seleccione Columna en el menú Formato y, a continuación, haga clic en Autoajustar selección.

Los datos de LOGEST están en las celdas A4:B6. Los resultados detallados para Excel 2002 y para versiones anteriores de Excel y para Excel 2003 y para versiones posteriores de Excel se presentan en las celdas D4:E8 y las celdas G4:H8, respectivamente. Las celdas A11:B13 muestran los mismos known_x, pero los known_y de las celdas A4:A6 se han transformado tomando su logaritmo natural mediante la función LN de Excel. A continuación, se llama a LINEST en estos datos transformados y los resultados se muestran en las celdas G11:H15. Observe que los resultados en las celdas G12:H15 son los mismos que los resultados de LOGEST en las celdas G5:H8. Los coeficientes LINEST de las celdas G11:H11 se transforman mediante exponenciación. Es decir, los coeficientes LINEST de las celdas se transforman utilizando esencialmente la función EXP de Excel para calcular los coeficientes LOGEST en las celdas G4:H4. Puede comprobar esta relación mediante la función EXP en las celdas G17:H17. Para resumir cómo interactúan LOGEST y LINEST, observe la siguiente secuencia de pasos:

1. Llama a LOGEST(known_y, known_x, constant, TRUE).
2. LOGEST llama a LINEST(LN de known_y, known_x, constante, TRUE).
3. LOGEST recibe la tabla de resultados de esta llamada a LINEST.
4. LOGEST modifica los coeficientes LINEST en la primera fila de la tabla de resultados LINEST por exponente. Por ejemplo, reemplace cada coeficiente LINEST, m, por EXP(m).
5. LOGEST devuelve esta tabla de resultados LINEST modificada como tabla de resultados de LOGEST.

Si LOGEST devuelve los resultados adecuados, LINEST debe generar los resultados adecuados en el paso 3. Examine las celdas D13:D15. La celda D13 contiene un valor r cuadrado, la celda D14 contiene una estadística f y la celda D15 contiene la suma de regresión LINEST de cuadrados.

En Excel 2002 y en versiones anteriores de Excel, cuando se llama a LINEST con su tercer argumento establecido en FALSE, siempre calcula una suma de regresión de cuadrados que no es correcta porque usa una fórmula que no es correcta. Este problema se ha corregido en Excel 2003 y en versiones posteriores de Excel. Observe que los valores de las dos primeras filas de la tabla de salida no se ven afectados por este problema. En Excel 2002 y en versiones anteriores de Excel, el artículo LINEST describe una solución alternativa para generar los valores adecuados en las tres últimas filas de la tabla de salida LINEST. Si usa una versión anterior de Excel y desea establecer el tercer argumento en LOGEST en FALSE, se recomienda ejecutar explícitamente los pasos 2 y 3 del procedimiento anterior y, a continuación, usar la solución alternativa en el artículo LINEST para modificar las tres últimas filas de la tabla de salida LINEST.

Los problemas se producen debido a colinear known_x en LOGEST o LINEST en Excel 2002 y en versiones anteriores de Excel. Las columnas de predictor, known_x son colinear si al menos una columna, c, se puede expresar como una suma de múltiplos de otras (c1, c2 y otros valores). La columna c se denomina redundante porque la información que contiene se puede construir a partir de las columnas (c1, c2 y otros valores). El principio fundamental en presencia de colinearidad es que los resultados no deben verse afectados por si una columna redundante se incluye en los datos originales o se quita de los datos originales. Dado que la versión de LINEST en Excel 2002 y en versiones anteriores de Excel no buscaba la colinearidad, este principio se violó fácilmente. Las columnas de predictor son casi colinear si al menos una columna, c, se puede expresar como casi igual a una suma de múltiplos de otras (c1, c2 y otros valores). En este caso, "casi igual" significa una suma muy pequeña de desviaciones cuadradas de entradas en c de las entradas correspondientes en la suma ponderada de c1, c2 y otros valores. Por ejemplo, "muy pequeño" podría ser menor que 10^(-12).

Para ilustrar la colinearidad de LOGEST, siga estos pasos:

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

2. Haga clic en la celda A1 en la hoja de cálculo de Excel en blanco. A continuación, pegue las entradas para que la tabla rellene las celdas A1:N27 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 Opciones de pegadoy, a continuación, haga clic en Coincidir formato de destino.

4. Aunque el rango pegado todavía está seleccionado, use uno de los procedimientos siguientes, según corresponda para la versión de Excel que está ejecutando:

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

Los datos se incluyen en las celdas A1:C6. Las entradas de las celdas D2:D6 no forman parte de los datos. Estas entradas se usan para ilustrar en el párrafo siguiente. Los resultados de dos llamadas diferentes a LOGEST se muestran para Excel 2002 y para versiones anteriores de Excel en las celdas E8:G20, y para Excel 2003 y para versiones posteriores de Excel en las celdas I8:K20.

El primer modelo, en las filas 8 a 13, usa las columnas B y C como predictores y solicita a Excel que modele la constante donde el tercer argumento se establece en TRUE. Excel inserta de forma eficaz una columna de predictor adicional que se parece a las celdas D2:D6. Observe que las entradas de la columna C de las filas 2 a 6 son exactamente iguales a la suma de las entradas correspondientes en las columnas B y D. Por lo tanto, la colinearidad se produce porque la columna C es una suma de múltiplos de la columna B y se inserta la columna adicional de Excel 1 porque se omitió el tercer argumento de LOGEST o ES TRUE, que es el caso "normal". Esta colinearidad provoca problemas numéricos y Excel 2002 y versiones anteriores de Excel no pueden calcular los resultados y la tabla de salida de LOGEST se rellena con #NUM!.

Cualquier versión de Excel puede controlar el segundo modelo en las filas 15 a 20. La colinearidad no se produce con este modelo y el usuario solicita a Excel que modele la constante. Este ejemplo se incluye aquí por los dos motivos siguientes:

• Este ejemplo es el más típico de los casos prácticos: no existe ninguna colinearidad y el tercer argumento de LOGEST es TRUE o se omite. Si tiene una versión anterior de Excel, no es probable que se produzcan problemas numéricos en el caso práctico más común.
• En segundo lugar, este ejemplo se usa para comparar el comportamiento de Excel 2003 y de versiones posteriores de Excel en los dos modelos. La mayoría de los paquetes estadísticos principales analizan la colinearidad, quitan una columna que es una suma de múltiplos de otros del modelo y le avisan con un mensaje como "La columna C depende linealmente de otras columnas de predictor y se ha quitado del análisis".

En Excel 2003 y en versiones posteriores de Excel, recibirá la información en la tabla de salida DE LOGEST, no en un mensaje ni en una cadena de texto. Un coeficiente de regresión que es uno y cuyo error estándar es "cero" corresponde a un coeficiente de una columna que se ha quitado del modelo. (Vea las entradas de las celdas I9:I10 para obtener un ejemplo). En este caso, LOGEST quita la columna C (los coeficientes de las celdas I9, J9 y K9 corresponden a las columnas C, B y en la columna constante de Excel, respectivamente). Cuando se produce la colinearidad, se puede quitar cualquiera de las columnas implicadas.

En el segundo modelo de las filas 16 a 20, no se produce la colinearidad y no se quita ninguna de las columnas. Los valores y predichos son los mismos en ambos modelos porque la eliminación de una columna redundante que es una suma de múltiplos de otros (el primer y el segundo modelo) no reduce la bondad del ajuste del modelo resultante. Estas columnas se quitan precisamente porque no representan ningún valor agregado al intentar encontrar el mejor ajuste mínimo de cuadrados. Además, en la salida de Excel 2003 y de versiones posteriores de Excel en las celdas I8:K20, las tres últimas filas de las tablas de salida son las mismas y las entradas de las celdas I16:J17 y las celdas J9:K10 coinciden. Esto muestra que se obtienen los mismos resultados cuando se incluye la columna C en el modelo, pero se detecta que es redundante (salida en I9:K13) como cuando se eliminó la columna C antes de que se ejecutara LOGEST (salida en I16:J20). Esta salida satisface el principio fundamental en presencia de colinearidad.

La colinearidad se identifica en LINEST en Excel 2003 y en versiones posteriores de Excel mediante un enfoque completamente diferente, Descomposición QR, para resolver los coeficientes de regresión. En el artículo linest se describe un tutorial del algoritmo de descomposición QR para un pequeño ejemplo.

Resumen de resultados en versiones anteriores de Excel

Los resultados de LOGEST se ven afectados negativamente en Excel 2002 y en versiones anteriores de Excel por los resultados en LINEST que no son precisos.

LINEST usó una fórmula que no es correcta para la suma total de cuadrados cuando el tercer argumento de LINEST se establece en FALSE. Esta fórmula produjo valores de la suma de regresión de los cuadrados que no son correctos. Además, los valores que dependen de la suma de regresión de los cuadrados, r al cuadrado y la estadística f, no son correctos. (Consulte la solución alternativa en el artículo LINEST si usa una versión anterior de Excel). Por lo tanto, los usuarios de LOGEST deben usar esta solución alternativa cuando llamen a LOGEST con el tercer argumento establecido en FALSE.

Independientemente del valor del tercer argumento, LINEST se calculó mediante un enfoque que no solucionaba problemas de colinearidad. La colinearidad provocaba errores de redondeo, errores estándar de coeficientes de regresión que no eran adecuados y grados de libertad que no eran adecuados. En algunos casos, los errores de redondeo eran suficientemente graves que la tabla de salida LINEST se rellenaba con #NUM!. LINEST generalmente proporciona resultados aceptables si se cumplen las condiciones siguientes:

• Los usuarios están seguros de que las columnas del predictor no son colinear (o casi colinear).
• El tercer argumento de LINEST es TRUE o se omite.

Por lo tanto, LOGEST generalmente proporciona resultados aceptables si las columnas del predictor no son colinear (o casi colinear) y si el tercer argumento de LOGEST es TRUE o se omite.

Resumen de resultados en Excel 2003 y en versiones posteriores de Excel

Se han realizado las siguientes mejoras en LINEST:

• Se corrigió la fórmula para la suma total de cuadrados donde el tercer argumento de LINEST se establece en FALSE.
• El método Descomposición QR se usa para determinar los coeficientes de regresión.

La descomposición QR tiene dos ventajas:

• Mejor estabilidad numérica (o, por lo general, más pequeña redondee los errores).
• Análisis de problemas de colinearidad.

Todos los problemas con Excel 2002 y con versiones anteriores de Excel que se tratan en este artículo se han corregido en Excel 2003 y en versiones posteriores de Excel.

Conclusiones

Se ha mejorado el rendimiento de LOGEST porque LINEST se ha mejorado considerablemente en Excel 2003 y en versiones posteriores de Excel. Si usa una versión anterior de Excel, compruebe que las columnas del predictor no sean colinear antes de usar LOGEST. Además, tenga cuidado de usar la solución alternativa que se presenta en el artículo LINEST cuando el tercer argumento de LOGEST se establece en FALSE. Aunque esta información de este artículo y del artículo LINEST puede parecer alarmante para los usuarios de Excel 2002 y de versiones anteriores de Excel, la colinearidad es un problema en un pequeño porcentaje de casos. Las llamadas a LOGEST con el tercer argumento establecido en FALSE probablemente también sean relativamente raras en la práctica. Las versiones anteriores de Excel proporcionan resultados LOGEST aceptables cuando no hay colinearidad y cuando el tercer argumento de LOGEST es TRUE o se omite.