Las funciones estadísticas de Excel: tendencias

Resumen

Este artículo describe la función tendencia en Microsoft Office Excel 2003 y en versiones posteriores de Excel, muestra cómo se utiliza 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 tendencia en las versiones anteriores de Excel.

TENDENCIA se evalúa mediante una llamada a la función relacionada, ESTIMACION. Grandes cambios en ESTIMACION en Excel 2003 y en versiones posteriores de Excel aparecen resumidos y sus implicaciones para tendencia se indican.

Microsoft Excel 2004 para Macintosh información

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

Más información

La función tendencia (conocido_y, conocido_x, nueva_matriz_x, constante) se utiliza para realizar la regresión lineal. Se utiliza un criterio de mínimos cuadrados y tendencia intenta encontrar el mejor ajuste en ese criterio. Conocido_y representan los datos en la variable"dependiente" y conocido_x representan datos en uno o más "variables independientes". El archivo de Ayuda de tendencia describe casos raros, donde se puede omitir el segundo o tercer argumento.

Si el último argumento "constante" se establece en TRUE, desea que el modelo de regresión para incluir un coeficiente que representa la intersección en el modelo de regresión. Si el último argumento se establece en FALSE, ningún término de intercepción se incluye; la regresión ajustada se ve obligada a pasar por el origen. El último argumento es opcional; Si se omite, se interpreta como TRUE.

Para facilitar la exposición en el resto de este artículo, se asume que los datos están organizados en columnas para que conocido_y ocupa una columna de datos y y conocido_x es una o más columnas de datos de x. Por supuesto, las dimensiones (longitud) de cada una de estas columnas deben ser iguales. Nueva_matriz_x también se supone que se organicen en columnas y debe haber el mismo número de columnas para nueva_matriz_x como para conocido_x. Todas las observaciones en este artículo son igualmente true si los datos no están organizados en columnas, pero es más sencillo discutir este único caso (más frecuente).

Tras calcular el mejor modelo de regresión (mediante una llamada a función de ESTIMACION de Excel), tendencia devuelve valores previstos asociados con nueva_matriz_x.

En este artículo se utiliza ejemplos para mostrar cómo se relaciona la tendencia a LINEST y señalar problemas con ESTIMACION en Microsoft Excel 2002 y en versiones anteriores de Excel. Estos problemas se traducen en problemas con la tendencia. Mientras el código de tendencia no se ha reescrito para Excel 2003 y versiones posteriores de Excel, se realizaron grandes cambios (y mejoras) en código de ESTIMACION.

TENDENCIA eficazmente llama ESTIMACION, ejecuta ESTIMACION, utiliza coeficientes de regresión en la salida ESTIMACION en el cálculo de valores de y pronosticado asociados a cada fila de nueva_matriz_x y presenta esta columna de valores de y pronosticado. Por lo tanto, debe saber acerca de los problemas en la ejecución de ESTIMACION.

Como complemento a este artículo, se recomienda el siguiente artículo acerca de ESTIMACION. Contiene varios ejemplos y documentos problemas con ESTIMACION en Excel 2002 y en versiones anteriores de Excel.

Para obtener información adicional, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

828533 descripción de la función ESTIMACION en Excel 2003 y en Excel 2004 para Mac



Dado que el enfoque aquí es sobre problemas numéricos en Excel 2002 y en versiones anteriores de Excel, este artículo no tiene muchos ejemplos prácticos de cómo utilizar tendencia. Archivo de Ayuda de TREND contiene ejemplos útiles.

Sintaxis

TREND(known_y's, known_x's, new_x's, constant)
Los argumentos, conocido_y, conocido_x y nueva_matriz_x deben ser matrices o rangos de celdas con dimensiones relacionadas. Si conocido_y ocupa una columna por m filas, valores conocidos de x son las columnas c y m filas donde c es mayor que o igual a uno. Tenga en cuenta que c es el número de variables de predictor; m es el número de puntos de datos. Nueva_matriz_x deberá ser c columnas por filas r donde se es mayor que o igual a uno. (Relaciones similares en dimensiones deben mantener si los datos se presentan en filas en lugar de columnas). Constante es un argumento lógico que debe establecerse en TRUE o FALSE (o 0 o 1 que Excel interpreta como FALSE o TRUE, respectivamente). Los tres últimos argumentos de tendencia son opcionales; consulte el archivo de Ayuda de TREND para opciones de omitir el segundo argumento, el tercer argumento o ambos. Si se omite el cuarto argumento se interpreta como TRUE.

El uso más común de tendencia incluye dos rangos de celdas que contienen los datos, como la tendencia (a1: A100, B1:F100, B101:F108, TRUE). Tenga en cuenta que porque normalmente hay más de una variable de predictor, el segundo argumento en este ejemplo contiene varias columnas. En este ejemplo, hay temas de cien, el valor de una variable dependiente (valores conocidos de y) para cada tema y cinco valores de variable dependiente (conocido_x) para cada tema. Hay ocho asuntos hipotéticos adicionales donde desee utilizar tendencia para calcular los valores de y pronosticado.

Ejemplo de uso

Se proporciona un ejemplo de hoja de cálculo de Excel para ilustrar los conceptos siguientes:
  • Cómo interactúa la tendencia con ESTIMACION.
  • Problemas que se producen debido a colineales conocido_x tendencia (o ESTIMACION) para Excel 2002 y en versiones anteriores de Excel
Amplia discusión del segundo punto de viñeta en el contexto de ESTIMACION se proporciona en el artículo de ESTIMACION.

Para ilustrar la colinealidad tendencias, crear 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 de forma que la tabla siguiente rellene A1:K35 de celdas en la hoja de cálculo.
y:x:
1121
2341
3451
4671
5781
x nuevo:911
1214
TENDENCIA mediante cols B, C:antes de Excel 2003 valores:Valores en Excel 2003 y en versiones posteriores de Excel:
=TREND(A2:A6,B2:C6,B7:C8,TRUE)#NUM!6.15789473684211
=TREND(A2:A6,B2:C6,B7:C8,TRUE)#NUM!8.13157894736842
TENDENCIA mediante col B
=TREND(A2:A6,B2:B6,B7:B8,TRUE)6.15789473684216.15789473684211
=TREND(A2:A6,B2:B6,B7:B8,TRUE)8.131578947368428.13157894736842
Equipado de valores en Excel 2003 y en versiones posteriores de Excel ESTIMACION resultados
Utilizando cols B, CUsando Col B
= K24*1 + J24*B7 + I24*C7=J31*1+I31*B7
=K24*1 + J24*B8 + I24*C8=J31*1 +I31*B8
ESTIMACION mediante cols B, C:antes de Excel 2003 valores:Valores en Excel 2003 y en versiones posteriores de Excel:
=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!00.6578947368421050.236842105263158
=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!00.0438596491228070.206652964726136
=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!0.9868421052631580.209426954145848#N/A
=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!2253#N/A
=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)=ESTIMACION.LINEAL(A2:A6,B2:C6,TRUE,TRUE)#NUM!#NUM!#NUM!9.868421052631580.131578947368421#N/A
LINEST usando col B
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.6578947368421050.2368421052631590.6578947368421050.236842105263158
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.04385964912280710.2066529647261360.0438596491228070.206652964726136
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)0.9868421052631580.2094269541458480.9868421052631580.209426954145848
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)224.99999999999932253
=LINEST(A2:A6,B2:B6,TRUE,TRUE)=LINEST(A2:A6,B2:B6,TRUE,TRUE)9.868421052631580.1315789473684219.868421052631580.131578947368421
Después de pegar esta tabla en la nueva hoja de cálculo de Excel, haga clic en
Opciones de pegadoy, a continuación, haga clic en Coincidir con formato de destino. Con el rango pegado todavía seleccionado, utilice uno de los procedimientos siguientes, según la versión de Excel que esté ejecutando:
  • En Microsoft Office Excel 2007 y 2010, haga clic en la ficha Inicio , haga clic en formato , en el grupo de celdas y, a continuación, haga clic en Autoajustar ancho de columna.
  • En Excel 2003, seleccione
    Columna en el menú formato y, a continuación, haga clic en
    Autoajustar a la selección.
Datos de tendencia están en las celdas A1:C8. (Las entradas de celdas D2: D6 no forman parte de los datos, pero se utilizan para la ilustración más adelante en este artículo.) Resultados de tendencias para dos modelos diferentes para ambas versiones anteriores de Excel y versiones posteriores de Excel se presentan en las células E10:E16 y I10:116 de las células, respectivamente. Resultados en las celdas A10:A16 corresponderá a la versión de Excel que esté utilizando. Por ahora, en este artículo se centra en los resultados en Excel 2003 y en versiones posteriores de Excel al investigar cómo tendencia llama ESTIMACION y cómo las tendencias utilización ESTIMACION de resultados.

TENDENCIAS y LINEST pueden verse como interactuar como sigue:
  1. Se llama a tendencia (conocido_y, conocido_x, nueva_matriz_x, constante).
  2. TENDENCIA llama LINEST (conocido_y, conocido_x, constante, TRUE).
  3. Se obtienen los coeficientes de regresión de esta llamada a LINEST; Estos coeficientes aparecen en la primera fila de la tabla de resultados de ESTIMACION.
  4. Para fila cada nueva_matriz_x, el valor de y previsto se calcula basándose en valores de la nueva_matriz_x en esa fila y estos coeficientes ESTIMACION.
  5. En la celda apropiada para la salida de tendencia correspondiente a fila ese nueva_matriz_x, se devuelve el valor calculado en el paso 4.
Si la tendencia es devolver los resultados apropiados, ESTIMACION mejor tenía generar resultados apropiados de en el paso 3. Problemas aquí provienen de las columnas de predicción colineales.

Columnas de predicción (conocido_x) son colineales si al menos una columna de c, se puede expresar como una suma de múltiplos de otros, c1, c2 y otras columnas. Columna c con frecuencia se denomina redundante porque la información que contiene se puede construirse a partir las columnas c1, c2 y otras columnas. El principio fundamental en presencia de colinealidad es que los resultados deben ser afectados por incluir o quitar una columna redundante de los datos originales. Porque la colinealidad no buscar ESTIMACION en Excel 2002 y en versiones anteriores de Excel, este principio se ha infringido fácilmente. Columnas de predicción son colineales casi si al menos una columna de c, se puede expresar como casi igual a una suma de múltiplos de otros, c1, c2 y otras columnas. En este caso "casi igual" significa una suma muy pequeña de las desviaciones cuadradas de entradas de c de las entradas correspondientes en la suma ponderada de c1, c2 y otras columnas; "muy pequeño" puede ser menor que 10^(-12) por ejemplo.

El primer modelo, en las filas 10 a 12, utiliza las columnas B y C como una predicción y solicitudes de Excel para modelar la constante (último argumento establecido en TRUE). A continuación, Excel inserta una columna adicional predictor apariencia de las celdas D2: D6. Es fácil apreciar que las entradas en la columna C en las filas 2 a 6 son exactamente iguales a la suma de las entradas correspondientes en las columnas B y D. Por lo tanto, hay colinealidad presente porque la columna C es una suma de múltiplos de:
  • Columna B
  • Columna adicional de 1s que se inserta porque se ha omitido el tercer argumento para ESTIMACION (igual que el cuarto argumento de tendencia) de Excel o TRUE (el caso "normal")
Esto hace que tales problemas numéricos que Excel 2002 y versiones anteriores de Excel no pueden calcular resultados y se rellena la tabla de resultados de tendencia con #NUM!.

El segundo modelo, en las filas 14 a 16, es que cualquier versión de Excel puede controlar correctamente. No hay ningún colinealidad y puede solicitar otra vez Excel para modelar la constante. Este modelo se incluye aquí por dos razones.

En primer lugar, tal vez es más habitual de casos prácticos: no colinealidad presente. Estos casos se controlan bien en todas las versiones de Excel. Es tranquilizador saber que no es probable que se producen en el caso práctico más habitual, si tiene una versión anterior de Excel problemas numéricos.

En segundo lugar, en este ejemplo se utiliza para comparar el comportamiento de Excel 2003 y de versiones posteriores de Excel en los dos modelos. La mayoría de los principales paquetes de estadísticos analizan colinealidad, quitar una columna que es una suma de múltiplos de otros usuarios del modelo y avisen con un mensaje como "columna C linealmente depende de otras columnas de predicción y se ha quitado de los análisis".

En Excel 2003 y en versiones posteriores de Excel, dicho mensaje se transmite no en una alerta o una cadena de texto, pero en la tabla de salida ESTIMACION. TENDENCIA dispone de ningún mecanismo para entregar este mensaje a usted. En la tabla de salida ESTIMACION, un coeficiente de regresión que es cero y cuyo error estándar es cero corresponde a un coeficiente de una columna que se ha quitado del modelo. En filas 23 a 35 correspondiente a la salida de la tendencia en las filas 10 a 16 se incluyen tablas de salida ESTIMACION. Las entradas de las celdas I24:I25 mostrar una columna de predictor redundantes eliminados. En este caso, ESTIMACION decidió quitar la columna C (coeficientes en las celdas I24, J24, K24 corresponden a las columnas C, B y de Excel constante columna, respectivamente). Cuando hay colinealidad presente, se puede quitar cualquiera de las columnas implicadas y la elección es arbitraria.

En el segundo modelo filas 30 a 35, hay no colinealidad y ninguna columna eliminada. Puede ver que los valores de y pronosticado son el mismo en ambos modelos. Este problema se produce porque quitar una columna redundante que es una suma de múltiplos de otros no reduce el valor de ajuste del modelo resultante. Estas columnas se quitan precisamente porque no representan ningún valor agregado en intentar encontrar el mejor ajuste de mínimos cuadrados.

Además, si examina la colinealidad en Excel 2003 y en versiones posteriores de Excel en las celdas I23:K35, observará que las tres últimas filas de las tablas de resultados son los mismos y que coinciden las entradas de las celdas I31:J32 y J24:K25 de las células. Esto demuestra que se obtienen los mismos resultados cuando la columna C se incluye en el modelo pero se encontró redundantes (resultados en las celdas I24:K28) como cuando se eliminó la columna C antes ESTIMACION ejecutada (salida en las células I31:J35). Esto cumple el principio fundamental en presencia de colinealidad.

En las celdas A18:C21, este artículo utiliza datos para Excel 2003 y versiones posteriores de Excel para ilustrar cómo tendencia toma la colinealidad y calcula los pertinentes valores y pronosticados. Examinando las fórmulas en las celdas A20:A21 y C20:C21 de celdas, puede ver cómo se combinan los coeficientes ESTIMACION con datos nueva_matriz_x en celdas B7:C8 para cada uno de los dos modelos (mediante columnas B, C como una predicción; utilizando sólo la columna B como un objeto predictor).

Colinealidad se identifica en el tema ESTIMACION en Excel 2003 y en versiones posteriores de Excel debido a un enfoque completamente diferente para solucionar para los coeficientes de regresión. Este enfoque se denomina descomposición QR. El artículo de ESTIMACION describe un tutorial sobre el algoritmo de descomposición QR para un pequeño ejemplo.

Resumen de los resultados en versiones anteriores de Excel

Resultados de tendencias se ven perjudicadas por resultados inexactos en ESTIMACION en Excel 2002 y en versiones anteriores de Excel.

Se calculó mediante un enfoque que no prestaron atención a los problemas de colinealidad ESTIMACION. La existencia de colinealidad causa errores de redondeo, inadecuado error típico de coeficientes de regresión e inadecuados de grados de libertad. A veces redondeo problemas eran suficientemente graves que LINEST llena su tabla de salida con #NUM!.

Si, como en la gran mayoría de los casos, en la práctica, puede estar seguro de que no hay columnas de predicción colineales (o casi colineales), LINEST generalmente proporciona resultados aceptables. Por lo tanto, si utiliza tendencia, puede asimismo satisface si está seguro de que no hay columnas de predicción colineales (o casi colineales).

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

Mejoras en el tema ESTIMACION incluyen conmutación al método QR descomposición de determinar los coeficientes de regresión. QR descomposición tiene las siguientes ventajas:
  • Mejor estabilidad numérico (generalmente más pequeños errores de redondeo)
  • Análisis de los problemas de colinealidad
Todos los problemas con Excel 2002 ni con las versiones anteriores de Excel que se muestran en este artículo se han corregido para Excel 2003 y versiones posteriores de Excel.

Conclusiones

Se ha mejorado el rendimiento de TREND porque ESTIMACION se ha mejorado considerablemente para Excel 2003 y versiones posteriores de Excel. Si utiliza una versión anterior de Excel, compruebe que las columnas de predicción no son colineales antes de utilizar la tendencia.

Gran parte del material que se presenta en este artículo y en el artículo ESTIMACION al principio parece alarmante a los usuarios de Excel 2002 y versiones anteriores de Excel. Sin embargo, tenga en cuenta que la colinealidad es un problema en un pequeño porcentaje de casos. Las versiones anteriores de Excel proporcionan resultados aceptables de tendencia cuando no hay ningún colinealidad.

Afortunadamente, las mejoras en el tema ESTIMACION también influyen de herramienta de las herramientas de análisis regresión lineal (Esto llama ESTIMACION.) y otros dos relacionados con las funciones de Excel: LOGEST y crecimiento.


Palabras clave: Fórmula tendencia crecimiento Logest ESTIMACION XL2003 XL2007 XL2010



Propiedades

Id. de artículo: 828801 - Última revisión: 17 ene. 2017 - Revisión: 1

Comentarios