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

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

Declaración del problema en un ejemplo concreto

Supongamos que hay dos indicadores de X e Y. Y Y depende de X. Como OLS nos interesa desde el punto de vista del análisis de regresión (en Excel, sus métodos se implementan con la ayuda de funciones incorporadas), entonces vale la pena recurrir inmediatamente a la consideración de un problema específico.

Por lo tanto, deje X - el área de comercio 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é volumen de negocios (Y)estará en la tienda, si tiene una u otra área de ventas. Obviamente, la función Y = f (X) está aumentando, ya que el hipermercado vende más bienes que el puesto.

Algunas palabras sobre la corrección de los datos iniciales utilizados para la predicción

Digamos que tenemos una tabla construida según los datos de n tiendas.

Según las estadísticas matemáticas, los resultadosserá más o menos correcto si se investigan los datos de al menos 5-6 objetos. Además, no puede usar resultados "anómalos". En particular, una pequeña boutique de élite puede tener un volumen de negocios a veces mayor que la rotación de grandes tiendas de la clase "masmarket".

La esencia del método

Los datos de la tabla se pueden representar en el plano cartesiano en forma de puntos M₁ (x₁, y₁), ... M_n (x_n, y_n) Ahora la solución del 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_n.

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

Estimación de precisión

Para cualquier aproximación, la estimación de su precisión se vuelve particularmente importante. Denotamos por e_yo la diferencia (desviación) entre los valores funcionales y experimentales para el punto x_yo, es decir, e_yo = y_yo - f (x_yo)

Obviamente, para estimar la precisión de la aproximaciónuno puede usar la suma de las desviaciones, es decir, al elegir una línea para una representación aproximada de la dependencia de X en Y, se debe dar preferencia a la que tenga el menor valor de la suma e_yo en todos los puntos considerados. Sin embargo, no todo es tan simple, ya que junto con las desviaciones positivas prácticamente serán negativas.

Puede resolver el problema utilizando módulos de desviacióno sus cuadrados. El último método fue el más ampliamente utilizado. Se usa en muchas áreas, incluido el análisis de regresión (en Excel, se implementa utilizando dos funciones incorporadas), y se ha demostrado su eficacia desde hace tiempo.

Método de mínimos cuadrados

En Excel, como saben, hay un built-inFunción suma automática que permite calcular los valores de todos los valores ubicados en el rango seleccionado. Por lo tanto, nada nos impide calcular el valor de la expresión (e₁² + e₂² + e₃²+ ... e_n²)

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

Dado que inicialmente se tomó la decisión de aproximar con una línea recta, tenemos:

Por lo tanto, el problema de encontrar una línea que mejor describa la dependencia específica de las cantidades X e Y se reduce al cálculo del mínimo de una función de dos variables:

Para hacer esto, necesitamos igualar a cero las derivadas parciales con respecto a las nuevas variables a y b, y resolver un sistema primitivo que consiste en dos ecuaciones con 2 incógnitas:

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

Resolviéndolo, por ejemplo, mediante el método de Cramer, obtenemos un punto estacionario con algunos coeficientes a^* yb^*. Este es el mínimo, es decir, para predecir qué volumen de negocios habrá en la tienda para un área determinada, la línea recta y = a^*x + b^*, que es un modelo de regresión parael ejemplo en cuestión. Por supuesto, no le permitirá encontrar el resultado exacto, pero le ayudará a tener una idea de si la compra de un área en particular dará resultado con el préstamo.

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

En "Excel" hay una función para calcular el valorpor las multinacionales. Tiene la siguiente forma: "TENDENCIAS" (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 esto, la celda en la quese muestra el resultado del cálculo por el método de mínimos cuadrados en Excel, ingresamos el signo "=" y elegimos la función "TENDENCIAS". En la ventana abierta rellene los campos correspondientes, resaltando:

• rango de valores conocidos para Y (en este caso, datos para facturación);
• rango x₁, ... x_n, es decir, el tamaño del espacio comercial;
• y valores conocidos y desconocidos de x, para los cuales necesita conocer el tamaño de la facturación (para obtener información sobre su ubicación en la hoja de trabajo, consulte a continuación).

Además, la fórmula contiene la variable lógica "Konst". Si ingresa 1 en el campo correspondiente, significa que debe realizar cálculos, suponiendo que b = 0.

Si necesita conocer el pronóstico de más de un valor de x, luego de ingresar la fórmula, no debe presionar "Enter", pero debe ingresar "Shift" + "Control" + "Enter" en el teclado.

Algunas características

Se puede acceder al análisis de regresión inclusoteteras. La fórmula de Excel para predecir el valor de una matriz de variables desconocidas, "TENDENCIAS", puede ser utilizada incluso por aquellos que nunca han oído hablar del método de mínimos cuadrados. Es suficiente con conocer algunas de las características de su trabajo. En particular:

• Si organiza un rango de valores conocidosvariable y en una fila o columna, entonces cada fila (columna) con valores conocidos de x será percibida por el programa como una variable separada.
• Si el rango no está especificado en la ventana "TENDENCIAS"conocido x, entonces en el caso de usar la función en Excel, el programa lo tratará como una matriz que consta de números enteros, cuyo número corresponde al rango con los valores dados de la variable y.
• Para obtener una matriz de valores "pronosticados" 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"TENDENCIAS" considera que son iguales a conocidos. Si no se especifican, entonces el conjunto 1 se toma como argumento; 2; 3; 4; ..., que es proporcional al rango con los parámetros ya dados y.
• El rango que contiene los nuevos valores de x debeconsiste en la misma o más filas o columnas como un rango con valores dados de y. En otras palabras, debe ser proporcional a las variables independientes.
• En una matriz con valores conocidos de x,contiene varias variables. Sin embargo, si es solo uno, se requiere que los rangos con valores dados de xey sean proporcionales. En el caso de varias variables, es necesario que el rango con valores dados de y esté contenido en una columna o en una línea.

Función "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, arroja el resultado de cálculos usando el método de mínimos cuadrados. Sin embargo, solo para una X, para la cual el valor de Y es desconocido.

Ahora conoce las fórmulas en Excel para Dummies, lo que le permite predecir el valor del valor futuro de un indicador dado según una tendencia lineal.