WorksheetFunction.LinEst(Object, Object, Object, Object) Método

public object LinEst(object Arg1, object Arg2, object Arg3, object Arg4);

Public Function LinEst (Arg1 As Object, Optional Arg2 As Object, Optional Arg3 As Object, Optional Arg4 As Object) As Object

Parámetros

Devoluciones

Comentarios

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

y = mx + b o

y = m1x1 + m2x2 + ... + b (si hay varios rangos de valores x)

donde el valor de y dependiente es una función de los valores de x independientes. Los valores de m son coeficientes que corresponden a cada valor de x y b es un valor constante. Tenga en cuenta que y, x y m pueden ser vectores. La matriz que devuelve LinEst es {mn,mn-1,...,m1,b}. LinEst también puede devolver estadísticas de regresión adicionales.

Si la matriz de valores conocidos de y se encuentra en una sola columna, cada columna de valores conocidos de x se interpreta como una variable independiente.

Si la matriz de valores conocidos de y está en una sola fila, cada fila de valores conocidos de x se interpreta como una variable independiente.

La matriz de valores conocidos de x puede incluir uno o varios conjuntos de variables. Si solo se usa una variable, los valores conocidos de y y de x pueden ser rangos de cualquier forma, siempre y cuando tengan las mismas dimensiones. Si se usa más de una variable, el argumento de valores conocidos de y tiene que ser un vector (es decir, un rango compuesto por una fila o por una columna).

Si se omite el argumento de valores conocidos de x, se supone que ésta es la matriz {1,2,3,...} que tiene el mismo tamaño que la de los valores conocidos de y.

Si const es verdadero u omitido, 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.

Si stats es trueuue, LinEst devuelve las estadísticas de regresión adicionales, por lo que la matriz devuelta es {mn,mn-1,...,m1,b; sen,sen-1,...,SE1,SEB; r2,sey; F,df; ssreg,ssresid}.

Si stats es falso u omitido, LinEst devuelve solo los coeficientes m y la constante b.

Las estadísticas de regresión adicionales son las siguientes:

En la siguiente ilustración se muestra el orden en que se devuelven las estadísticas de regresión adicionales.

Figura 1: Hoja de cálculo

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

Pendiente (m):

Para encontrar la pendiente de una línea, a menudo escrita como m, tome dos puntos en la línea, (x1,y1) y (x2,y2); La pendiente es igual a (Y2 - Y1)/(X2 - X1).

Intercepción Y (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 recta es y = mx + b. Cuando se conocen los valores de m y b, se puede calcular cualquier punto de la línea rellenando el valor de y o de x en la ecuación. También puedes usar la Trend(Object, Object, Object, Object) función.

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

Pendiente:

=INDICE(ÍNDICE(known_y's,known_x's),1)

Intercepción con Y:

=INDICE(ÍNDICE(known_y's,known_x's),2)

La precisión de la línea calculada por LineEst depende del grado de dispersión en sus datos. Cuanto más lineales sean los datos, más preciso será el modelo LineEst . LineEst 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 m y b se basan en las siguientes fórmulas:

Figura 2: Ecuación

Figura 3: Ecuación

donde x e y son medias de muestra, es decir, x = PROMEDIO (x conocidas) e y = PROMEDIO (known_y).

Las funciones de ajuste de líneas y curvas LineEst y LogEst(Object, Object, Object, Object) pueden calcular la mejor línea recta o curva exponencial que se ajuste a sus datos. Sin embargo, tendrá que decidir cuál de los dos resultados se adapta mejor a los datos. Puede calcular TREND(valores conocidos de y, valores conocidos de x) para una línea recta, o GROWTH(valores conocidos de y, valores conocidos de x) para una curva exponencial. Estas funciones, sin el argumento de valores nuevos de x, devuelven una matriz de valores y previstos a lo largo de la línea o curva en los puntos de datos reales. A continuación, puede comparar los valores previstos con los valores reales. Puede ser conveniente dibujarlos en un gráfico para poder compararlos visualmente.

En el análisis de regresión, Microsoft Excel calcula para cada punto la diferencia al cuadrado entre el valor previsto de y para ese punto y su valor real de y. La suma de estas diferencias al cuadrado se denomina suma residual de los cuadrados, ssresid. Microsoft Excel calcula, a continuación, la suma total de los cuadrados, sstotal. Cuando const = true u omitido, la suma total de los cuadrados es la suma de las diferencias al cuadrado entre los valores de y reales y el promedio de los valores de y. Cuando const = false, 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). Por tanto, la suma de regresión de los cuadrados, ssreg, se puede hallar a partir de: ssreg = sstotal - ssresid. Cuanto menor sea la suma residual de los cuadrados, comparada con la suma total de los cuadrados, mayor será el valor del coeficiente de determinación, r2, que es un indicador de cómo la ecuación resultante del análisis de regresión explica la relación entre las variables. r2 es igual a ssreg/sstotal.

En algunos casos, una o más de las columnas X (suponga que Y y X están en columnas) pueden no tener ningún valor predictivo adicional en presencia de las otras columnas X. En otras palabras, eliminar una o más columnas X podría dar lugar a valores Y previstos que sean igualmente precisos. En ese caso, estas columnas X redundantes deben omitirse del modelo de regresión. Este fenómeno se llama "colinealidad" porque cualquier columna X redundante se puede expresar como una suma de múltiplos de las columnas X no redundantes. LinEst comprueba la colinealidad y quita las columnas X redundantes del modelo de regresión cuando las identifica. Las columnas X eliminadas se pueden reconocer en la salida de LinEst como si tuvieran 0 coeficientes y 0 se. Si una o más columnas se quitan como redundantes, df se ve afectado porque df depende del número de X columnas realmente usadas con fines predictivos. Si df se cambia porque se quitan las columnas X redundantes, los valores de sey y F también se verán 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 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, LinEst inserta de forma eficaz una columna X adicional de todos los 1 para modelar la intersección. Si tiene una columna con un 1 para cada asignatura si es hombre, o 0 si no, y también tiene una columna con un 1 para cada asignatura si es mujer, o 0 si no, esta última columna es redundante porque las entradas en ella se pueden obtener restando la entrada en la columna "indicador masculino" de la entrada en la columna adicional de todos los 1 agregados por LineEst.

df se calcula de la siguiente manera cuando no se eliminan columnas X del modelo debido a la colinealidad: si hay k columnas de known_x y const = true u omitido, entonces df = n – k – 1. Si const = false, entonces df = n - k. En ambos casos, cada columna X se quita debido a que la colinealidad aumenta en 1 el valor de df.

Las fórmulas que devuelven matrices deben escribirse como fórmulas de matriz. Cuando especifique como argumento una constante matricial, como valores conocidos de x, utilice comas para separar los valores de una misma fila y signos de punto y coma para separar las filas. Los caracteres separadores pueden ser diferentes según la configuración local definida en Configuración regional y de idioma en el Panel de control.

Tenga en cuenta que los valores de y previstos por la ecuación de regresión puede que no sean válidos si quedan fuera del rango de los valores de y utilizados para determinar la ecuación. El algoritmo subyacente utilizado en la función LinEst es diferente del algoritmo subyacente utilizado en las Slope(Object, Object) funciones y Intercept(Object, Object) . La diferencia entre estos algoritmos puede producir resultados distintos cuando los datos son indeterminados y colineales. Por ejemplo, si los puntos de datos del argumento de valores conocidos de y son 0 y los puntos de datos del argumento de valores conocidos de x son 1:

LineEst devuelve el valor 0. El algoritmo LinEst está diseñado para devolver resultados razonables para datos colineales y, en este caso, se puede encontrar al menos una respuesta. Slope(Object, Object) y Intercept(Object, Object) devolver un #DIV/0! error. El Slope(Object, Object) algoritmo and Intercept(Object, Object) está diseñado para buscar una y solo una respuesta, y en este caso puede haber más de una respuesta.