Estadística avanzada de Excel: función LINEST

Descripción

La función LINEST calcula las estadísticas de una línea mediante el método de "mínimos cuadrados" para calcular la línea recta que mejor se ajusta a sus datos y luego devuelve una matriz que describe la línea.

También puede combinar LINEST con otras funciones para calcular las estadísticas de otros tipos de modelos que son lineales en los parámetros desconocidos, incluidas las series polinomiales, logarítmicas, exponenciales y de potencia.

Dado que esta función devuelve una matriz de valores, debe ingresarse como una fórmula de matriz.

Sintaxis

LINEST (known_y's, [known_x's], [const], [stats])

Argumentos

Argumento Descripción Requerido / Opcional
conocido_y

El conjunto de valores de y que ya conoce en la relación y = mx + b.

Si el rango de conocido_y está en una sola columna, cada columna de conocido_x se interpreta como una variable separada.

Si el rango de conocido_y está contenido en una sola fila, cada fila de conocido_x se interpreta como una variable separada.

 Necesario
conocido_x's

Un conjunto de valores de x que quizás ya conozcas en la relación y = mx + b.

El rango de conocido_x puede incluir uno o más conjuntos de variables.

Si solo se usa una variable, known_y y known_x pueden ser rangos de cualquier forma, siempre que tengan las mismas dimensiones.

Si se usa más de una variable, conocido_y debe ser un vector (es decir, un rango con una altura de una fila o un ancho de una columna).

Si se omite conocido_x, se asume que es la matriz {1,2,3, ...} que tiene el mismo tamaño que conocido_y.

 Opcional
constante

Un valor lógico que especifica si se debe forzar la constante b para que sea igual a 0.

Si const es TRUE o se omite, b se calcula normalmente.

Si const es FALSE, b se establece igual a 0 y los valores m se ajustan para ajustarse a y = mx.

 Opcional
estadisticas

Un valor lógico que especifica si se devuelven estadísticas de regresión adicionales.

Si las estadísticas son TRUE, LINEST devuelve las estadísticas de regresión adicionales. Como resultado, la matriz devuelta es {mn, mn-1, ..., m1, b; sen, sen-1, ..., se1, seb; r2, sey; F, df; ssreg, ssresid}.

Si las estadísticas son FALSE o se omiten, LINEST devuelve solo los mcoeficientes y la constante b. Las estadísticas de regresión adicionales se muestran en la Tabla siguiente.

 Opcional

Estadísticas de regresión adicionales

No Señor Estadística y descripción
1

se1,se2,...,sen

Los valores de error estándar para los coeficientes m1, m2, ..., mn.
2

seb

El valor de error estándar para la constante b (seb = # N / A cuando const es FALSE).
3

r2

El coeficiente de determinación. Compara los valores de y estimados y reales, y su valor varía de 0 a 1. Si es 1, hay una correlación perfecta en la muestra; no hay diferencia entre el valor de y estimado y el valor de y real. En el otro extremo, si el coeficiente de determinación es 0, la ecuación de regresión no es útil para predecir un valor de y. Para obtener información sobre cómo se calcula r2, consulte las Notas a continuación.
4

sey

El error estándar para la estimación y.
5

F

El estadístico F o el valor F observado. Utilice el estadístico F para determinar si la relación observada entre las variables dependientes e independientes se produce por azar.
6

df

Los grados de libertad. Utilice los grados de libertad para encontrar valores críticos de F en una tabla estadística. Compare los valores que encuentre en la tabla con la estadística F devuelta por LINEST para determinar un nivel de confianza para el modelo. Para obtener información sobre cómo se calcula df, consulte las notas siguientes.
7

ssreg

La suma de cuadrados de la regresión.
8

ssreg

La suma residual de cuadrados. Para obtener información sobre cómo se calculan ssreg y ssresid, consulte las notas a continuación.

Notas

  • La ecuación de la línea es:

    y = mx + b

    o

    y = m1x1 + m2x2 + ... + b

  • Si hay varios rangos de valores de x, donde los valores de y dependientes son una función de los valores de x independientes, entonces -

    • Los valores m son coeficientes correspondientes a cada valor x, y b es un valor constante.

    • Tenga en cuenta que y, xym pueden ser vectores.

  • La matriz que devuelve la función LINEST es {mn, mn-1… m1, b}.

  • LINEST también puede devolver estadísticas de regresión adicionales

  • Puede describir cualquier línea recta con la pendiente y la intersección con el eje y:

    • Slope(m) -

      Para encontrar la pendiente de una línea, a menudo escrita como m, toma dos puntos en la línea, (x1, y1) y (x2, y2). La pendiente es igual a

      (–2 - y1) / (- 2 - x1).

    • Y-intercept(b) -

      La intersección con el eje y de una línea, a menudo escrita como b, es el valor de y en el punto donde la línea cruza el eje y.

  • La ecuación de una línea recta es y = mx + b. Una vez que conozca los valores de myb, puede calcular cualquier punto de la línea conectando el valor y o el valor x en esa ecuación. También puede utilizar la función TENDENCIA.

  • Cuando solo tiene una variable x independiente, puede obtener los valores de la pendiente y la intersección y directamente usando las siguientes fórmulas:

    • Slope -

      = INDICE (LINEST (conocido_y, conocido_x), 1)

    • Y-intercept -

      = INDICE (LINEST (conocido_y, conocido_x), 2)

  • La precisión de la línea calculada por la función LINEST depende del grado de dispersión en sus datos. Cuanto más lineales sean los datos, más preciso será el modelo LINEST.

  • LINEST utiliza el método de mínimos cuadrados para determinar el mejor ajuste para los datos. Cuando solo tiene una variable x independiente, los cálculos para myb se basan en las siguientes fórmulas:

    $$ m = \ frac {\ sum \ left (x- \ bar {x} \ right) \ left (y- \ bar {y} \ right)} {\ sum \ left (x- \ bar {x} \ derecha) ^ 2} $$

    Donde xey son medias muestrales. es decir

    x = PROMEDIO (x conocidas)

    y = PROMEDIO (conocido_y)

  • Las funciones de ajuste de línea y curva LINEST y LOGEST pueden calcular la mejor línea recta o curva exponencial que se ajuste a sus datos. Sin embargo, debe decidir cuál de los dos resultados se ajusta mejor a sus datos. Puede calcular la TENDENCIA (conocida_y, conocida_x) para una línea recta, o CRECIMIENTO (conocida_y, conocida_x) para una curva exponencial. Estas funciones, sin omitir el argumento de known_x, devuelven una matriz de valores y predichos a lo largo de esa línea o curva en los puntos de datos reales. A continuación, puede comparar los valores predichos con los valores reales. Es posible que desee graficar ambos para una comparación visual.

  • En el análisis de regresión, Excel calcula para cada punto la diferencia al cuadrado entre el valor de y estimado para ese punto y su valor de y real. La suma de estas diferencias al cuadrado se denomina suma de cuadrados residual, ssresid. Excel luego calcula la suma total de cuadrados, sstotal. Cuando el argumento constante = VERDADERO o se omite, la suma total de cuadrados es la suma de las diferencias cuadradas entre los valores de y reales y el promedio de los valores de y.

  • Cuando el argumento constante = FALSO, la suma total de cuadrados es la suma de los cuadrados de los valores de y reales (sin restar el valor de y promedio de cada valor de y individual). Luego, la suma de regresión de cuadrados, ssreg, se puede encontrar en: ssreg = sstotal - ssresid. Cuanto menor sea la suma de cuadrados residual, en comparación con la suma total de cuadrados, mayor será el valor del coeficiente de determinación, r2, que es un indicador de qué tan bien la ecuación resultante del análisis de regresión explica la relación entre las variables. El valor de r2 es igual a ssreg / sstotal.

  • En algunos casos, una o más de las columnas X (suponga que las Y y las X están en las columnas) pueden no tener un valor predictivo adicional en presencia de las otras X columnas. es decir, eliminar una o más columnas X podría conducir a valores Y predichos que son igualmente precisos. En ese caso, estas columnas X redundantes deben omitirse del modelo de regresión. Este fenómeno se denomina "colinealidad" porque cualquier columna X redundante puede expresarse como una suma de múltiplos de las columnas X no redundantes.

  • La función LINEST comprueba la colinealidad y elimina las columnas X redundantes del modelo de regresión cuando las identifica. Las columnas X eliminadas se pueden reconocer en la salida LINEST por tener 0 coeficientes además de 0 valores de se. Si una o más columnas se eliminan como redundantes, df se ve afectado porque df depende del número de columnas X que se utilizan realmente para fines predictivos.

  • Si df cambia porque se eliminan las columnas X redundantes, los valores de sey y F también se ven afectados. La colinealidad debería ser relativamente rara en la práctica. Sin embargo, un caso en el que es más probable que surja es cuando algunas columnas X contienen solo valores 0 y 1 como indicadores de si un sujeto en un experimento es o no miembro de un grupo en particular. Si const = TRUE o se omite, la función LINEST inserta efectivamente una columna X adicional de todos los valores 1 para modelar la intersección

  • El valor de df se calcula de la siguiente manera, cuando hay k columnas de conocido_x y no se eliminan columnas X del modelo debido a la colinealidad:

    • Si const = TRUE o se omite, gl = n - k - 1

    • Si const = FALSE, df = n - k

    En ambos casos, cada columna X que se eliminó debido a la colinealidad aumenta el valor de gl en 1.

  • Cuando ingrese una constante de matriz (como conocida_x) como argumento, use comas para separar los valores que están contenidos en la misma fila y punto y coma para separar las filas. Los caracteres separadores pueden ser diferentes según la configuración regional.

  • Tenga en cuenta que los valores de y predichos por la ecuación de regresión pueden no ser válidos si están fuera del rango de los valores de y que utilizó para determinar la ecuación.

  • El algoritmo subyacente utilizado en la función LINEST es diferente del algoritmo subyacente utilizado en las funciones SLOPE e INTERCEPT. La diferencia entre estos algoritmos puede conducir a resultados diferentes cuando los datos son indeterminados y colineales.

  • Además de usar LOGEST para calcular estadísticas para otros tipos de regresión, puede usar LINEST para calcular un rango de otros tipos de regresión ingresando funciones de las variables xey como las series xey para LINEST. Por ejemplo, la siguiente fórmula:

    = LINEST (valores y, valores x ^ COLUMNA ($ A: $ C))

    Funciona cuando tiene una sola columna de valores y y una sola columna de valores x para calcular la aproximación cúbica (polinomio de orden 3) del -

    y = m1 * x + m2 * x ^ 2 + m3 * x * 3 + b

    Puede ajustar esta fórmula para calcular otros tipos de regresión, pero en algunos casos requiere el ajuste de los valores de salida y otras estadísticas.

  • El valor de la prueba F devuelto por la función ESTIMACIÓN DE LÍNEA difiere del valor de la prueba F devuelto por la función PRUEBA PF. LINEST devuelve la estadística F, mientras que FTEST devuelve la probabilidad.

  • Si la matriz de known_x's no tiene la misma longitud que la matriz de known_y, LINEST devuelve #REF! valor de error.

  • Si alguno de los valores en las matrices de conocido_x o conocido_y suministrados no es numérico (esto puede incluir representaciones de texto de números, ya que la función ESTIMACIÓN DE LÍNEA no los reconoce como números), ESTIMACIÓN DE LÍNEA devuelve el #VALOR valor de error.

  • Si cualquiera de los argumentos const o stats no se puede evaluar como VERDADERO o FALSO, ESTIMACIÓN DE LÍNEA devuelve el # ¡VALOR! valor de error.

Aplicabilidad

Excel 2007, Excel 2010, Excel 2013, Excel 2016

Ejemplo