Método de mínimos cuadrados en Excel. Análisis de regresión

El método de mínimos cuadrados (LSM) se refiere aAnálisis de la esfera de regresión. Tiene muchos usos, ya que permite una representación aproximada de una función dada por otras más simples. OLS puede ser extremadamente útil en el procesamiento de observaciones, y se usa activamente para estimar algunos valores a partir de los resultados de mediciones de otros que contienen errores aleatorios. En este artículo, aprenderá cómo implementar cálculos de mínimos cuadrados en Excel.

Declaración del problema en un ejemplo concreto

Supongamos que hay dos exponentes X e Y.Además, Y depende de X. Dado que las empresas multinacionales nos interesan desde el punto de vista del análisis de regresión (en Excel, sus métodos se implementan utilizando funciones integradas), debemos proceder inmediatamente a considerar un problema específico.

Entonces, que X sea el área de ventas de la tienda de comestibles, medida en metros cuadrados, y Y la facturación anual, definida en millones de rublos.

Se requiere hacer un pronóstico, qué rotación (Y)estará en la tienda si tiene uno u otro espacio comercial. Obviamente, la función Y = f (X) está aumentando, ya que el hipermercado vende más bienes que el puesto.

Algunas palabras sobre la exactitud de los datos fuente utilizados para la predicción

Supongamos que tenemos una tabla basada en datos para n tiendas.

Según las estadísticas matemáticas, los resultadosserá más o menos correcto si se examinan los datos de al menos 5-6 objetos. Además, no se pueden usar resultados "anormales". En particular, una pequeña boutique de élite puede tener una facturación muchas veces mayor que la facturación de los grandes puntos de venta minoristas de la clase "masmarket".

La esencia del método.

Los datos de la tabla se pueden representar en el plano cartesiano como puntos M₁ (con₁, y₁), ... M_Sr. (con_Sr., y_Sr.) Ahora la solución al problema se reduce a la selección de la función aproximada y = f (x), que tiene un gráfico que pasa lo más cerca posible de los puntos M_1, M_{2, ..}M_Sr..

Por supuesto, puedes usar el polinomio altogrados, pero esta opción no solo es difícil de implementar, sino simplemente incorrecta, ya que no reflejará la tendencia principal que debe detectarse. La solución más razonable es encontrar la línea recta y = ax + b, que se aproxima mejor a los datos experimentales y, más precisamente, los coeficientes ay b.

Calificación de precisión

En cualquier aproximación, la estimación de su precisión es de particular importancia. Denote por e_y diferencia (desviación) entre valores funcionales y experimentales para el punto x_yes decir, e_y = y_y - f (x_y)

Obviamente, para estimar la precisión de la aproximaciónse puede usar la suma de las desviaciones, es decir, al elegir una línea recta para una representación aproximada de la dependencia de X en Y, se debe dar preferencia a la que tiene el valor más pequeño de la suma e_y en todos los puntos bajo consideración. Sin embargo, no todo es tan simple, ya que junto con las desviaciones positivas, las negativas también estarán presentes.

Puedes resolver el problema usando los módulos de desviacióno sus cuadrados. El último método es el más utilizado. Se utiliza en muchas áreas, incluido el análisis de regresión (en Excel, se implementa utilizando dos funciones integradas), y se ha demostrado que es eficaz desde hace mucho tiempo.

Método de mínimos cuadrados

En Excel, como saben, hay una función incorporada.función autosum que le permite calcular los valores de todos los valores ubicados en el rango seleccionado. Por lo tanto, nada nos impedirá calcular el valor de la expresión (e₁² + e₂² + e₃²+ ... e_Sr.²)

En una notación matemática, esto tiene la forma:

Como se tomó la decisión inicial de aproximarse usando una línea recta, tenemos:

Por lo tanto, el problema de encontrar la línea que mejor describe la dependencia específica de las cantidades X e Y se reduce a calcular la función mínima de dos variables:

Para hacer esto, necesitamos establecer derivadas parciales con respecto a las nuevas variables ayb a cero, y resolver un sistema primitivo que consta de dos ecuaciones con 2 incógnitas de la forma:

Después de algunas transformaciones simples, que incluyen dividir entre 2 y manipular las sumas, obtenemos:

Resolviéndolo, por ejemplo, mediante el método de Cramer, obtenemos un punto estacionario con ciertos coeficientes a^* y b^*. Este es el mínimo, es decir, para predecir cuál será la facturación de la tienda para un área determinada, la línea recta y = a^*x + b^*, представляющая собой регрессионную модель для El ejemplo en cuestión. Por supuesto, no le permitirá encontrar el resultado exacto, pero le ayudará a tener una idea de si la compra pagará a crédito de la tienda de un área en particular.

Cómo implementar un método de mínimos cuadrados en Excel

"Excel" tiene una función para calcular el valorde acuerdo con OLS. Tiene la siguiente forma: "TENDENCIA" (valores conocidos de Y; valores conocidos de X; nuevos valores de X; const.). Aplicamos la fórmula para calcular OLS en Excel a nuestra tabla.

Para hacer esto, en la celda en la que debería haberel resultado del cálculo por el método de mínimos cuadrados se muestra en Excel, ingresamos el signo "=" y seleccionamos la función "TENDENCIA". En la ventana que se abre, complete los campos apropiados, resaltando:

• el rango de valores conocidos para Y (en este caso, datos de rotación);
• rango x₁, ... x_Sr., es decir, el tamaño del espacio comercial;
• valores conocidos y desconocidos de x, para los cuales debe averiguar el tamaño de la rotación (la información sobre su ubicación en la hoja de trabajo se encuentra a continuación).

Además, en la fórmula hay una variable lógica "Const". Si ingresa 1 en el campo correspondiente, esto significará que los cálculos deben llevarse a cabo, suponiendo que b = 0.

Si necesita conocer el pronóstico para más de un valor de x, luego de ingresar la fórmula, no debe hacer clic en "Enter", sino que debe escribir la combinación "Shift" + "Control" + "Enter" en el teclado.

Algunas características

El análisis de regresión puede incluso estar disponible.teteras La fórmula de Excel para predecir el valor de una matriz de variables desconocidas - "TENDENCIA" - puede ser utilizada incluso por aquellos que nunca han oído hablar del método de mínimos cuadrados. Solo conozco algunas de las características de su trabajo. En particular:

• Si coloca un rango de valores conocidosy variable en una fila o columna, entonces el programa percibirá cada fila (columna) con valores de x conocidos como una variable separada.
• Si en la ventana "TENDENCIA" el rango conconocido por x, entonces, en caso de usar la función en Excel, el programa la considerará como una matriz que consiste en enteros, cuyo número corresponde al rango con los valores dados de la variable y.
• Para obtener una matriz de valores "predichos" en la salida, la expresión para calcular la tendencia debe ingresarse como una fórmula de matriz.
• Si no se especifican nuevos valores de x, entonces la función"TENDENCIA" los considera igualmente famosos. Si no se especifican, la matriz 1 se toma como argumento; 2; 3; 4; ..., que es acorde con el rango con parámetros y ya establecidos.
• Диапазон, содержащий новые значения x должен consiste en la misma o más filas o columnas como un rango con valores de y dados. En otras palabras, debe ser una variable independiente proporcional.
• В массиве с известными значениями x может Contiene varias variables. Sin embargo, si estamos hablando solo de uno, entonces se requiere que los rangos con los valores dados de x e y sean proporcionales. En el caso de varias variables, es necesario que el rango con los valores dados de y encaje en una columna o en una fila.

Función de predicción

El análisis de regresión en Excel se implementa conusando varias funciones. Uno de ellos se llama "PREDICCIÓN". Es similar a "TENDENCIAS", es decir, produce el resultado de cálculos utilizando el método de mínimos cuadrados. Sin embargo, solo para una X para la que se desconoce el valor de Y.

Ahora conoce las fórmulas en Excel para dummies que le permiten predecir la magnitud del valor futuro de un indicador particular de acuerdo con una tendencia lineal.