¡Date prisa! ¿Cómo usar LINEST para encontrar múltiples ecuaciones de regresión en Excel? Excel requiere que las fórmulas se ingresen en forma de fórmulas matricial. ¿Cómo acceder a ellos?

La función de la función ESTIMACIÓN LINEAL es utilizar el método de mínimos cuadrados para calcular la ecuación lineal que mejor se ajuste a los datos conocidos y devolver una matriz que describe este modelo lineal. Debido a que el valor devuelto por esta función es una matriz, debe ingresarse como una fórmula matricial.

La fórmula de la función es

= ESTIMACIÓN LINEAL (valor y conocido, valor x conocido, constante, valor estadístico)

El siguiente ejemplo ilustra la aplicación de ESTIMACIÓN LINEAL función.

1. Análisis de regresión lineal simple

La función LINEST se puede utilizar para análisis de regresión lineal simple, análisis de regresión lineal múltiple y análisis autorregresivo de series temporales.

Cuando solo hay una variable independiente (LINEST(conocido_y, conocido_x),1,1 o index(LINEST(conocido_y,conocido_x),1);

intercept:INDEX(LINEST(known_y's,known_x's),1,2); o index(LINEST(known_y's,known_x's),2)

Coeficiente de correlación: índice (LINEST (conocido_y's) , conocido_x, verdadero, verdadero), 3, 1).

Ejemplo 4-1 Los costos totales, horas de mano de obra y horas de máquina de una determinada empresa de junio a septiembre se muestran en la Figura 4-1. Suponiendo que existe una relación lineal entre el costo total y las horas de mano de obra, inserte la fórmula "= índice (ESTIMACIÓN LINEAL (B2: B10, D2: D10, 2)" en la celda B14 e inserte la fórmula "= índice (ESTIMACIÓN LINEAL () en la celda B14 B2: B668, 2)". 1)", inserte la fórmula "= index (LINEST (B2: B10, D2: D10, true, true), 3, 1)" en la celda B15, es decir, el costo total y horas de trabajo La ecuación del análisis de regresión lineal de una variable es

Figura 4-1 Análisis de regresión lineal de una variable

2. Aún usando los datos del Ejemplo 4-1. Por ejemplo, primero seleccione el rango de celdas A17:D21 y luego ingrese la fórmula "=ESTIMACIÓN LINEAL (B2: B10, C2: D10, verdadero, verdadero)" en forma de fórmula matricial. para obtener regresión lineal binaria

Figura 4-2 Análisis de regresión lineal binaria

Ecuación de regresión: y = 471,4366+3,6165 x 1+3,4323 x2

Coeficiente de correlación. : R2 =0.9990. >Desviación estándar: Sey =11.7792.

4.3.2 Función de prueba logarítmica

La función de la función LOGEST es calcular la curva de ajuste de regresión exponencial que mejor se ajuste. el conjunto de datos observados en el análisis de regresión y devuelve Matriz que describe el modelo exponencial Debido a que esta función devuelve una matriz, debe ingresarse como una fórmula matricial

La fórmula para la función LOGEST es

Ejemplo 4-2 Los datos relevantes de la producción (X) y el costo de producción (Y) de un determinado producto de una empresa en diciembre se muestran en la Figura 4-3. La relación entre m=0.8887.

Figura 4-3 Regresión exponencial

Los coeficientes y los coeficientes de correlación de la ecuación de regresión también se pueden calcular. directamente usando la siguiente fórmula:

Parámetro m: índice (logest (C2: c13, B2: b13), 1) = 0,8887.

Parámetro b: índice (Logest (C2: C13, B2: B13), 1, 2) = 1791.7729.

Coeficiente de correlación R2: = índice (logest (C2: C13, B2: B13, true, true), 31) = 0,95885.

Función de tendencia

La función de la función TENDENCIA es devolver el valor de ordenadas (valor y) de un conjunto de regresión lineal que se ajusta a rectas, es decir, encontrar la recta adecuado para las matrices dadas conocido_y y conocido_x (usando el método de mínimos cuadrados), devuelve el valor de y correspondiente al valor de la matriz especificada nuevo_x en una línea recta.

La fórmula de la función de tendencia es

=tendencia (valor de y conocido, valor de x conocido, valor de x nuevo, constante)

donde nuevo _ x ' s: necesita la función TENDENCIA para devolver el nuevo valor de x correspondiente al valor de y. New_x es lo mismo que conocido_x, cada variable independiente debe ser una fila (o columna) separada. Por lo tanto, si conocido_y es una sola columna, conocido_x y nuevo_x deben tener el mismo número de columnas; si conocido_y es una sola columna, conocido_x y nuevo_x deben tener el mismo número de filas; Si se omite new_x, se supondrá que es el mismo que conocido_x.

Ejemplo 4-3 Las ventas de una determinada empresa en el último año son los siguientes datos: {300, 356, 374, 465, 438+00, 453, 487, 5065, 438+0, 534 , 572, 626, 5438+0, 650, 670}, guardado en la celda A 60. Luego, los pasos del pronóstico de ventas para enero, febrero y marzo del próximo año son los siguientes: seleccione el rango de celdas B1:B3 e ingrese la fórmula "= tendencia (A1: A12, {13; 14; 15}) (entrada de fórmula matricial ), es decir, el volumen de ventas en enero, febrero y marzo del próximo año es 710, 743 y 777 respectivamente. Esta fórmula por defecto es {1;2;3;4;5;6;7;8;9; 10;11;12} como parámetro deknown_x, por lo que la matriz {13;14;15} corresponde a los siguientes tres meses

Función de crecimiento

La función CRECIMIENTO devuelve. el valor de crecimiento exponencial previsto en función de x y Dado un valor conocido de y, la función CRECIMIENTO devuelve un conjunto de nuevos valores de y correspondientes a x. Puede utilizar la función de hoja de cálculo CRECIMIENTO para ajustar una curva de crecimiento exponencial para x dada. y valores de y.

La fórmula de la función es

= CRECIMIENTO(valor de y conocido, valor de x conocido, valor de x nuevo, constante)

El significado. de cada parámetro es el mismo que el de la función de tendencia. Sin embargo, cabe señalar que si algún número enknown_y es cero o negativo, la función CRECIMIENTO devolverá el valor de error #NUM! -4 toma los datos del Ejemplo 4-3 como ejemplo. Utilice la función de crecimiento para predecir las ventas en enero, febrero y marzo del próximo año. Los pasos de predicción son los siguientes: Seleccione el rango de celdas B1:B3 e ingrese el. fórmula "= crecimiento (A1: A12, {13; 14; 15}) (fórmula matricial). Entrada), es decir, el volumen de ventas en enero, febrero y marzo del próximo año será 756, 811 y 870 respectivamente. Esta fórmula también tiene por defecto {1;2;3;4;5;6;7;8;9;10;11;12} como parámetro deknown_x, por lo que la matriz {13;14;15} corresponde a la siguiente tres meses.

4.3.5 Función de predicción

La función de la función de predicción es calcular o predecir valores futuros en función de unos datos dados. Este valor predicho es el valor de y derivado de una serie de valores de x conocidos. Dado un valor de x y un valor de y como una matriz o rango de datos, devuelve el valor predicho de regresión lineal basado en x. La fórmula de la función de predicción es a+bx.

Entre ellos,;.

La fórmula de la función de predicción es

= PRONÓSTICO(x, y conocida, x conocida)

donde x se refiere al punto de datos que se va a predecir.

Cabe señalar que:

Si x no es un número, la función PRONÓSTICO devuelve el valor de error #¡VALOR! .

Si conocido_y y conocido_x están vacíos o contienen diferentes números de puntos de datos, la función PRONÓSTICO devolverá el valor de error # n/a.

Si la varianza deknown_x es cero, la función PRONÓSTICO devuelve el valor de error #DIV/0. .

Por ejemplo: previsión (30, {6, 7, 9, 15, 21}, {20, 28, 31, 38, 40}) = 10,60725.

Función Pendiente

La función de la función PENDIENTE es devolver la pendiente de la línea de regresión lineal ajustada en función de los puntos de datos en conocido_y y conocido_x. La pendiente es la perpendicular a cualquier. dos puntos en la recta. La relación entre la distancia y la distancia horizontal es la tasa de cambio de la recta de regresión.

La fórmula de la función pendiente es

=pendiente (valor y conocido, valor x conocido)

Descripción: Los parámetros pueden ser números o un nombre relacionado , matriz o referencia a un número. Si una matriz o parámetro de referencia contiene texto, valores lógicos o celdas en blanco, estos valores se ignoran. Sin embargo, se contarán las celdas que contengan valores cero. Si conocido_y y conocido_x están vacíos o sus puntos de datos son diferentes, la función PENDIENTE devuelve el valor de error #n/a.

Por ejemplo: pendiente ({2, 3, 9, 1, 8, 7, 5}, {6, 5, 11, 7, 5, 4}) = 0,305556.

Función de intersección

La función de intersección consiste en utilizar el valor x y el valor y conocidos para calcular la intersección de la línea recta y el eje y. La intersección es la intersección de la línea de regresión lineal que pasa por los puntos de datos de x e Y conocidos con el eje Y.

La fórmula es

=intersección (valor y conocido, valor x conocido)

Por ejemplo: intersección ({2, 3, 9, 1, 8 }, {6, 5, 11, 7, 5}) = 0,04871.

Ejemplo de aplicación de función Excel: pronóstico de ventas

Supongamos que las ventas diarias de un supermercado de lunes a domingo son 13, 17, 16, 15, 19, 21 y 22 (millones) respectivamente), el director general necesita predecir las ventas diarias más altas y más bajas para la próxima semana. Se pueden realizar las siguientes predicciones:

(1) Descomposición de funciones

La función TENDENCIA devuelve el valor de la línea de ajuste de regresión lineal. Es decir, encuentre una línea recta adecuada para las matrices conocidas conocidas_y y conocidas_x (usando el método de mínimos cuadrados) y devuelva el valor Y correspondiente a la matriz especificada new_x en la línea recta.

Sintaxis: Tendencia (conocida, conocida, nueva, constante)

Known_y's es un conjunto de valores y conocidos en la expresión relacional y=mx+b Known_x's es un conjunto; de valores x opcionales conocidos en la expresión relacional y=mx+b; devuelve el nuevo valor x correspondiente al valor y de la tendencia de función requerida. Const es un valor lógico utilizado para especificar si la constante b es obligatoria Establecer en; 0.

(2) Estudio de caso

Primero abra una hoja de trabajo en blanco, ingrese "ventas diarias" en la celda A1 y luego ingrese los datos anteriores en las celdas A2 y A3 en secuencia hasta A8. Luego seleccione el área de B2 a B8, ingrese la fórmula "=TENDENCIA (A2:A8)" en el campo de edición de Excel, presione Entrar y obtenga 7 resultados para el área de B2 a B8, entre los cuales el mayor volumen de ventas es 216,400 Yuan, el volumen de ventas más bajo es de 13.500 yuanes.

La función de tendencia, al igual que la función Coulter, se puede utilizar para predecir picos y valles en la inscripción de estudiantes en las estadísticas educativas y picos y valles del flujo de pasajeros en el transporte ferroviario.