Tiene muchas aplicaciones, ya que permite una representación aproximada de una función dada por otras más sencillas. LSM puede ser extremadamente útil en el procesamiento de observaciones y se usa activamente para estimar algunas cantidades a partir de los resultados de las mediciones de otras que contienen errores aleatorios. En este artículo, aprenderá cómo implementar cálculos de mínimos cuadrados en Excel.

Planteamiento del problema sobre un ejemplo concreto

Supongamos que hay dos indicadores X e Y. Además, Y depende de X. Dado que OLS nos interesa desde el punto de vista del análisis de regresión (en Excel, sus métodos se implementan mediante funciones integradas), debemos proceder de inmediato considerar un problema específico.

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

Se requiere hacer una previsión de qué facturación (Y) tendrá la tienda si cuenta con uno u otro local comercial. Obviamente, la función Y = f (X) es creciente, ya que el hipermercado vende más bienes que el puesto.

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

Digamos que tenemos una tabla construida con datos para n tiendas.

Según las estadísticas matemáticas, los resultados serán más o menos correctos si se examinan los datos de al menos 5-6 objetos. Además, no se pueden utilizar resultados "anómalos". 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 de la clase "masmarket".

La esencia del método.

Los datos de la tabla se pueden mostrar en el plano cartesiano como puntos M 1 (x 1, y 1), ... M n (x n, y n). Ahora la solución del problema se reducirá a la elección de una función de aproximación y = f (x), que tenga una gráfica que pase lo más cerca posible de los puntos M 1, M 2, .. M n .

Por supuesto, puede usar un polinomio de alto grado, 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 buscar una línea recta y = ax + b, que mejor se aproxime a los datos experimentales y, más precisamente, a los coeficientes - a y b.

Puntuación de precisión

Para cualquier aproximación, la evaluación de su precisión es de particular importancia. Denote por e i la diferencia (desviación) entre los valores funcionales y experimentales para el punto x i , es decir, e i = y i - f (x i).

Obviamente, para evaluar la precisión de la aproximación, 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 menor valor de la suma e i en todos los puntos considerados. Sin embargo, no todo es tan sencillo, ya que junto a las desviaciones positivas, prácticamente habrá otras negativas.

Puedes resolver el problema usando los módulos de desviación o sus cuadrados. Este último método es el más utilizado. Se utiliza en muchas áreas, incluido el análisis de regresión (en Excel, su implementación se lleva a cabo mediante dos funciones integradas) y ha demostrado su eficacia durante mucho tiempo.

Método de mínimos cuadrados

En Excel, como sabe, hay una función de autosuma incorporada que le permite calcular los valores de todos los valores ubicados en el rango seleccionado. Así, nada nos impedirá calcular el valor de la expresión (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

En notación matemática, esto se ve así:

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

Por lo tanto, la tarea de encontrar una línea recta que describa mejor una relación específica entre X e Y equivale a calcular el mínimo de una función de dos variables:

Esto requiere igualar a cero derivadas parciales con respecto a las nuevas variables a y b, y resolver un sistema primitivo que consta de dos ecuaciones con 2 incógnitas de la forma:

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

Resolviéndolo, por ejemplo, por el método de Cramer, obtenemos un punto estacionario con ciertos coeficientes a* y b*. Este es el mínimo, es decir, para predecir qué facturación tendrá la tienda para una determinada área, la línea recta y = a * x + b * es adecuada, que es un modelo de regresión para el ejemplo en cuestión. Por supuesto, no le permitirá encontrar el resultado exacto, pero ayudará a tener una idea de si comprar una tienda a crédito para un área en particular valdrá la pena.

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

Excel tiene una función para calcular el valor de los mínimos cuadrados. Tiene la siguiente forma: TENDENCIA (valores Y conocidos; valores X conocidos; valores X nuevos; constante). Apliquemos la fórmula para calcular el MCO en Excel a nuestra tabla.

Para ello, en la celda en la que se debe mostrar el resultado del cálculo por el método de mínimos cuadrados en Excel, ingrese el signo “=” y seleccione la función “TENDENCIA”. En la ventana que se abre, complete los campos correspondientes, resaltando:

• rango de valores conocidos para Y (en este caso datos de facturación);
• rango x 1 , …x n , es decir, el tamaño del espacio comercial;
• y valores conocidos y desconocidos de x, para los cuales debe averiguar 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, hay una variable lógica "Const" en la fórmula. Si ingresa 1 en el campo correspondiente, esto significará que se deben realizar los cálculos, suponiendo que b \u003d 0.

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

Algunas caracteristicas

El análisis de regresión puede ser accesible incluso para tontos. 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. Basta con conocer algunas características de su trabajo. En particular:

• Si organiza el rango de valores conocidos de la variable y en una fila o columna, el programa percibirá cada fila (columna) con valores conocidos de x como una variable separada.
• Si el rango con x conocido no se especifica en la ventana TENDENCIA, entonces, en el caso de usar la función en Excel, el programa lo considerará como una matriz que consta de números enteros, cuyo número corresponde al rango con los valores dados. de la variable y.
• Para generar una matriz de valores "predichos", la expresión de tendencia debe ingresarse como una fórmula de matriz.
• Si no se especifican nuevos valores de x, la función TENDENCIA los considera iguales a los conocidos. Si no se especifican, la matriz 1 se toma como argumento; 2; 3; 4;…, lo cual es proporcional al rango con los parámetros ya dados y.
• El rango que contiene los nuevos valores de x debe tener las mismas o más filas o columnas que el rango con los valores de y dados. En otras palabras, debe ser proporcional a las variables independientes.
• Una matriz con valores x conocidos puede contener múltiples variables. Sin embargo, si estamos hablando de uno solo, 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 y dados quepa en una columna o una fila.

función PRONÓSTICO

Se implementa usando varias funciones. Uno de ellos se llama "PREDICCIÓN". Es similar a TREND, es decir, da el resultado de los cálculos usando el método de mínimos cuadrados. Sin embargo, solo para una X, para la cual se desconoce el valor de Y.

Ahora ya conoces las fórmulas de Excel para dummies que te permiten predecir el valor del valor futuro de un indicador según una tendencia lineal.

Bueno, en el trabajo informaron a la inspección, el artículo se escribió en casa para la conferencia; ahora puede escribir en el blog. Mientras procesaba mis datos, me di cuenta de que no podía evitar escribir sobre un complemento muy bueno y necesario en Excel, que se llama . Por lo tanto, el artículo estará dedicado a este complemento en particular, y hablaré de él usando un ejemplo del uso método de mínimos cuadrados(LSM) para buscar coeficientes desconocidos de la ecuación en la descripción de los datos experimentales.

Cómo habilitar el complemento "buscar una solución"

Primero, averigüemos cómo habilitar este complemento.

1. Vaya al menú "Archivo" y seleccione "Opciones de Excel"

2. En la ventana que aparece, seleccione "Buscar una solución" y haga clic en "ir".

3. En la siguiente ventana, coloque una marca de verificación frente al elemento "buscar una solución" y haga clic en "Aceptar".

4. El complemento está activado; ahora se puede encontrar en el elemento de menú "Datos".

Método de mínimos cuadrados

Ahora brevemente sobre método de mínimos cuadrados (LSM) y donde se puede aplicar.

Digamos que tenemos un conjunto de datos después de haber realizado algún experimento donde estudiamos los efectos del valor X en el valor Y.

Queremos describir matemáticamente esta influencia, para que luego podamos usar esta fórmula y saber que si cambiamos tanto el valor de X, obtendremos el valor de Y tal y tal ...

Tomemos un ejemplo súper simple (ver imagen).

No hay duda de que los puntos están ubicados uno tras otro como si estuvieran en línea recta y, por lo tanto, asumimos con seguridad que nuestra dependencia está descrita por una función lineal y = kx + b. Al mismo tiempo, estamos seguros de que cuando X es igual a cero, el valor de Y también es igual a cero. Esto significa que la función que describe la dependencia será aún más simple: y=kx (recuerda el currículo escolar).

En general, tenemos que encontrar el coeficiente k. Esto es lo que haremos con multinacional utilizando el complemento "buscar una solución".

El método es (aquí - atención: debe pensarlo) la suma de las diferencias al cuadrado entre los valores obtenidos experimentalmente y los valores calculados correspondientes fue mínima. Es decir, cuando X1=1 el valor real medido Y1=4.6, y el calculado y1=f (x1) es 4, el cuadrado de la diferencia será (y1-Y1)^2=(4-4.6)^2= 0,36 . Lo mismo con lo siguiente: cuando X2=2, el valor real medido Y2=8.1, y el y2 calculado es 8, el cuadrado de la diferencia será (y2-Y2)^2=(8-8.1)^2=0.01. Y la suma de todos estos cuadrados debe ser lo más pequeña posible.

Entonces, comencemos a capacitarnos en el uso de LSM y Complementos de Excel "buscar solución" .

Aplicación de solución de búsqueda de complemento

1. Si no habilitó el complemento "buscar una solución", vuelva al paso Cómo habilitar el complemento "buscar una solución" y habilitar 🙂

2. En la celda A1, ingrese el valor "1". Esta unidad será la primera aproximación al valor real del coeficiente (k) de nuestra dependencia funcional y=kx.

3. En la columna B tenemos los valores del parámetro X, en la columna C - los valores del parámetro Y. En las celdas de la columna D ingresamos la fórmula: "coeficiente k multiplicado por el valor X". Por ejemplo, en la celda D1, ingrese "=A1*B1", en la celda D2, ingrese "=A1*B2", y así sucesivamente.

4. Creemos que el coeficiente k es igual a uno y la función f (x) \u003d y \u003d 1 * x es la primera aproximación a nuestra solución. Podemos calcular la suma de las diferencias al cuadrado entre los valores medidos de Y y los calculados mediante la fórmula y=1*x. Podemos hacer todo esto manualmente colocando las referencias de celda apropiadas en la fórmula: "=(D2-C2)^2+(D3-C3)^2+(D4-C4)^2... etc. Al final, están equivocados y entienden que hemos perdido mucho tiempo. En Excel, para calcular la suma de las diferencias al cuadrado, existe una fórmula especial, "SUMQDIFF", que hará todo por nosotros. Ingresémoslo en la celda A2 y configuremos el datos iniciales: el rango de valores medidos Y (columna C) y el rango de valores Y calculados (columna D).

4. Se calculó la suma de las diferencias de los cuadrados: ahora vaya a la pestaña "Datos" y seleccione "Buscar una solución".

5. En el menú que aparece, seleccione la celda A1 como la celda a cambiar (la que tiene el coeficiente k).

6. Como objetivo, seleccione la celda A2 y establezca la condición "establecer igual al valor mínimo". Recuerda que esta es la celda donde calculamos la suma de las diferencias al cuadrado entre los valores calculados y medidos, y esta cantidad debe ser mínima. Presionamos "ejecutar".

7. Se selecciona el coeficiente k. Ahora se puede ver que los valores calculados ahora están muy cerca de los medidos.

PD

En general, por supuesto, para la aproximación de datos experimentales en Excel, existen herramientas especiales que le permiten describir los datos utilizando una función lineal, exponencial, de potencia y polinomial, por lo que a menudo puede prescindir de complementos "Buscar una solución". Hablé sobre todos estos métodos de aproximación en mi artículo, así que si estás interesado, échale un vistazo. Pero cuando se trata de alguna función exótica con un coeficiente desconocido o problemas de optimización, entonces aquí superestructura tan bien como sea posible.

Complemento "buscar una solución" se puede usar para otras tareas, lo principal es comprender la esencia: hay una celda donde seleccionamos un valor, y hay una celda objetivo en la que se establece una condición para seleccionar un parámetro desconocido.
¡Eso es todo! En el próximo artículo contaré un cuento de hadas sobre unas vacaciones, así que para no perderse el lanzamiento del artículo,

Método de mínimos cuadrados se utiliza para estimar los parámetros de la ecuación de regresión.

Uno de los métodos para estudiar las relaciones estocásticas entre características es el análisis de regresión.
El análisis de regresión es la derivación de una ecuación de regresión, que se utiliza para encontrar el valor promedio de una variable aleatoria (característica-resultado), si se conoce el valor de otra (u otras) variables (característica-factores). Incluye los siguientes pasos:

1. elección de la forma de conexión (tipo de ecuación de regresión analítica);
2. estimación de parámetros de ecuaciones;
3. evaluación de la calidad de la ecuación de regresión analítica.

Muy a menudo, se utiliza una forma lineal para describir la relación estadística de las características. La atención a la relación lineal se explica por una clara interpretación económica de sus parámetros, limitada por la variación de variables y el hecho de que en la mayoría de los casos las formas no lineales de la relación se convierten (tomando un logaritmo o cambiando variables) en una forma lineal. para realizar cálculos.
En el caso de una relación de par lineal, la ecuación de regresión tomará la forma: y i =a+b·x i +u i . Los parámetros de esta ecuación ayb se estiman a partir de los datos de observación estadística xey. El resultado de tal evaluación es la ecuación: , donde , - estimaciones de los parámetros a y b , - el valor de la característica efectiva (variable) obtenido por la ecuación de regresión (valor calculado).

El más utilizado para la estimación de parámetros es método de mínimos cuadrados (LSM).
El método de mínimos cuadrados proporciona las mejores estimaciones (coherentes, eficientes e imparciales) de los parámetros de la ecuación de regresión. Pero solo si se cumplen ciertos supuestos sobre el término aleatorio (u) y la variable independiente (x) (ver supuestos de MCO).

El problema de estimar los parámetros de una ecuación de par lineal por el método de mínimos cuadrados consiste en lo siguiente: para obtener tales estimaciones de los parámetros, en los que la suma de las desviaciones al cuadrado de los valores reales de la característica efectiva, y i de los valores calculados, es mínima.
Formalmente criterio MCO se puede escribir asi: .

Clasificación de los métodos de mínimos cuadrados

1. Método de mínimos cuadrados.
2. Método de máxima verosimilitud (para un modelo de regresión lineal clásico normal, se postula la normalidad de los residuos de regresión).
3. El método de mínimos cuadrados generalizados de GLSM se utiliza en el caso de autocorrelación de errores y en el caso de heteroscedasticidad.
4. Método de mínimos cuadrados ponderados (un caso especial de GLSM con residuos heteroscedásticos).

Ilustrar la esencia el método clásico de mínimos cuadrados gráficamente. Para hacer esto, construiremos un diagrama de puntos de acuerdo con los datos de observación (x i , y i , i=1;n) en un sistema de coordenadas rectangulares (tal diagrama de puntos se llama campo de correlación). Tratemos de encontrar una línea recta que esté más cerca de los puntos del campo de correlación. De acuerdo con el método de los mínimos cuadrados, la línea se elige de modo que la suma de las distancias verticales al cuadrado entre los puntos del campo de correlación y esta línea sea mínima.

Notación matemática de este problema: .
Los valores de y i y x i =1...n nos son conocidos, estos son datos observacionales. En la función S son constantes. Las variables en esta función son las estimaciones requeridas de los parámetros - , . Para encontrar el mínimo de una función de 2 variables, es necesario calcular las derivadas parciales de esta función con respecto a cada uno de los parámetros e igualarlas a cero, es decir .
Como resultado, obtenemos un sistema de 2 ecuaciones lineales normales:
Resolviendo este sistema, encontramos las estimaciones de parámetros requeridas:

La exactitud del cálculo de los parámetros de la ecuación de regresión se puede verificar comparando las sumas (es posible que haya alguna discrepancia debido al redondeo de los cálculos).
Para calcular estimaciones de parámetros, puede crear la Tabla 1.
El signo del coeficiente de regresión b indica la dirección de la relación (si b > 0, la relación es directa, si b<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalmente, el valor del parámetro a es el valor medio de y para x igual a cero. Si el factor de signo no tiene y no puede tener un valor cero, entonces la interpretación anterior del parámetro a no tiene sentido.

Evaluación de la rigidez de la relación entre las características se lleva a cabo utilizando el coeficiente de correlación de pares lineales - r x,y . Se puede calcular usando la fórmula: . Además, el coeficiente de correlación de pares lineales se puede determinar en términos del coeficiente de regresión b: .
El rango de valores admisibles del coeficiente lineal de correlación de pares es de –1 a +1. El signo del coeficiente de correlación indica la dirección de la relación. Si r x, y >0, entonces la conexión es directa; si r x, y<0, то связь обратная.
Si este coeficiente está cerca de la unidad en el módulo, entonces la relación entre las características puede interpretarse como una relación lineal bastante cercana. Si su módulo es igual a uno ê r x , y ê =1, entonces la relación entre las características es lineal funcional. Si las características x e y son linealmente independientes, entonces r x,y está cerca de 0.
La Tabla 1 también se puede utilizar para calcular r x,y.

Para evaluar la calidad de la ecuación de regresión obtenida, se calcula el coeficiente de determinación teórico - R 2 yx:

,
donde d 2 es la varianza y explicada por la ecuación de regresión;
e 2 - varianza residual (no explicada por la ecuación de regresión) y ;
s 2 y - varianza total (total) y .
El coeficiente de determinación caracteriza la proporción de variación (dispersión) de la característica resultante y, explicada por regresión (y, en consecuencia, el factor x), en la variación total (dispersión) y. El coeficiente de determinación R 2 yx toma valores de 0 a 1. En consecuencia, el valor 1-R 2 yx caracteriza la proporción de varianza y causada por la influencia de otros factores no tomados en cuenta en el modelo y errores de especificación.
Con regresión lineal pareada R 2 yx =r 2 yx .

Método de mínimos cuadrados (LSM)

El sistema de m ecuaciones lineales con n incógnitas tiene la forma:

Son posibles tres casos: m norte. El caso cuando m=n fue considerado en los párrafos anteriores. Forma

Si m>n y el sistema es consistente, entonces la matriz A tiene al menos m - n filas linealmente dependientes. Aquí, la solución se puede obtener seleccionando n ecuaciones linealmente independientes cualesquiera (si existen) y aplicando la fórmula X=A -1 CV, es decir, reduciendo el problema al resuelto anteriormente. En este caso, la solución resultante siempre satisfará las m - n ecuaciones restantes.

Sin embargo, cuando se usa una computadora, es más conveniente usar un enfoque más general: el método de los mínimos cuadrados.

Mínimos cuadrados algebraicos

El método algebraico de mínimos cuadrados se entiende como un método para resolver sistemas de ecuaciones lineales

minimizando la norma euclidiana

¿Hacha? ¿b? >inf. (1.2)

Análisis de datos experimentales

Consideremos algún experimento, durante el cual en los instantes de tiempo

por ejemplo, se mide la temperatura Q(t). Deje que los resultados de la medición estén dados por una matriz

Supongamos que las condiciones del experimento son tales que las medidas se realizan con un error conocido. En estos casos, la ley de cambio de temperatura Q(t) se busca usando algún polinomio

P(t) = + + + ... +,

determinando los coeficientes desconocidos, ..., a partir de las consideraciones de que el valor E(, ...,) definido por la igualdad

aproximación algebraica exel de gauss

tomó el valor mínimo. Dado que la suma de los cuadrados se minimiza, este método se denomina ajuste de mínimos cuadrados a los datos.

Si reemplazamos P(t) con su expresión, obtenemos

Establezcamos la tarea de definir una matriz de tal manera que el valor sea mínimo, es decir definir una matriz utilizando el método de mínimos cuadrados. Para ello, igualamos las derivadas parciales a cero:

Si ingresa la matriz m × n A = (), i = 1, 2..., m; j = 1, 2, ..., n, donde

yo = 1, 2..., m; j = 1, 2, ..., norte,

entonces la igualdad escrita toma la forma

Reescribamos la igualdad escrita en términos de operaciones con matrices. Por definición, tenemos la multiplicación de una matriz por una columna

Para una matriz transpuesta, una relación similar se ve así

Introducimos la siguiente notación: denotaremos la i -ésima componente del vector Ax De acuerdo con las igualdades matriciales escritas, tendremos

En forma matricial, esta igualdad se puede reescribir como

A T x = A T B (1.3)

Aquí A es una matriz rectangular de m × n. Además, en problemas de aproximación de datos, por regla general, m > n. La ecuación (1.3) se llama ecuación normal.

Fue posible desde el principio, utilizando la norma euclidiana de los vectores, escribir el problema en una forma matricial equivalente:

Nuestro objetivo es minimizar esta función en x. Para que se alcance un mínimo en el punto de solución, las primeras derivadas con respecto ax en este punto deben ser iguales a cero. Las derivadas de esta función son

2A T B + 2A T Eje

y por lo tanto la solución debe satisfacer el sistema de ecuaciones lineales

(A T A)x = (A T B).

Estas ecuaciones se llaman ecuaciones normales. Si A es una matriz m × n, entonces A>A - n × n es una matriz, es decir la matriz de ecuación normal es siempre una matriz cuadrada simétrica. Además, tiene la propiedad de definición positiva en el sentido de que (A>Ax, x) = (Ax, Ax) ? 0.

Comentario. A veces, la solución de una ecuación de la forma (1.3) se denomina solución del sistema Ax = B, donde A es una matriz rectangular de m × n (m > n) por el método de los mínimos cuadrados.

El problema de los mínimos cuadrados se puede interpretar gráficamente como la minimización de las distancias verticales desde los puntos de datos hasta la curva del modelo (consulte la Figura 1.1). Esta idea se basa en la suposición de que todos los errores de aproximación corresponden a errores de observación. Si también hay errores en las variables explicativas, entonces puede ser más apropiado minimizar la distancia euclidiana de los datos al modelo.

MCO en Excel

El algoritmo para implementar OLS en Excel a continuación asume que ya se conocen todos los datos iniciales. Multiplicamos ambas partes de la ecuación matricial A×X=B del sistema desde la izquierda por la matriz transpuesta del sistema А Т:

UN IMPUESTO \u003d A T B

Luego multiplicamos ambas partes de la ecuación de la izquierda por la matriz (A T A) -1. Si esta matriz existe, entonces el sistema está definido. Teniendo en cuenta el hecho de que

(A T A) -1 * (A T A) \u003d E, obtenemos

X \u003d (A T A) -1 A T B.

La ecuación matricial resultante es una solución a un sistema de m ecuaciones lineales con n incógnitas para m>n.

Considere la aplicación del algoritmo anterior en un ejemplo específico.

Ejemplo. Sea necesario resolver el sistema

En Excel, la hoja de solución en el modo de visualización de fórmulas para este problema se ve así:

Resultados del cálculo:

El vector X deseado se encuentra en el rango E11:E12.

Al resolver un sistema dado de ecuaciones lineales, se utilizaron las siguientes funciones:

1. MINUTO - Devuelve el inverso de una matriz almacenada en un arreglo.

Sintaxis: NBR (matriz).

Una matriz es una matriz numérica con el mismo número de filas y columnas.

2. MULTIP: devuelve el producto de matrices (las matrices se almacenan en matrices). El resultado es una matriz con el mismo número de filas que matriz1 y el mismo número de columnas que matriz2.

Sintaxis: MULT(matriz1, matriz2).

Matriz1, matriz2: matrices multiplicadas.

Después de ingresar la función en la celda superior izquierda del rango de la matriz, seleccione la matriz, comenzando desde la celda que contiene la fórmula, presione la tecla F2 y luego presione las teclas CTRL+SHIFT+ENTER.

3. TRANSPONER: convierte un conjunto vertical de celdas en uno horizontal, o viceversa. El resultado de usar esta función es una matriz con el número de filas igual al número de columnas en la matriz original y el número de columnas igual al número de filas en la matriz inicial.

El método de los mínimos cuadrados es un procedimiento matemático para construir una ecuación lineal que coincida lo más posible con un conjunto de dos series de números. El propósito de este método es minimizar el error cuadrático total. Excel tiene herramientas que se pueden usar para aplicar este método en los cálculos. Veamos cómo se hace.

Usando el método en Excel

o Habilitación del complemento Solver

o Condiciones de la tarea

o Decisión

Usando un método en Excel

El método de los mínimos cuadrados (LSM) es una descripción matemática de la dependencia de una variable con respecto a otra. Se puede utilizar para pronósticos.

Habilitar el complemento Solver

Para usar OLS en Excel, debe habilitar el complemento "Buscar una solución", que está deshabilitado por defecto.

1. Ir a la pestaña "Expediente".

2. Haz clic en el nombre de la sección "Opciones".

3. En la ventana que se abre, detenga la selección en la subsección "Complementos".

4. En el bloque "Control", que se encuentra en la parte inferior de la ventana, coloque el interruptor en la posición "Complementos de Excel"(si tiene un valor diferente) y haga clic en el botón "Vamos...".

5. Se abre una pequeña ventana. Ponga una marca de verificación junto a la opción "Buscar una solución". Haga clic en el botón OK.

Ahora la función Encontrar una solución en Excel está activado y sus herramientas aparecen en la cinta.

Lección: Encontrar una solución en Excel

Condiciones del problema

Describamos la aplicación de LSM en un ejemplo específico. Tenemos dos filas de números. X y y, cuya secuencia se muestra en la siguiente imagen.

Esta dependencia se puede describir con mayor precisión mediante la función:

Al mismo tiempo, se sabe que x=0 y también igual 0 . Por lo tanto, esta ecuación se puede describir mediante la dependencia y=nx.

Tenemos que encontrar la suma mínima de cuadrados de la diferencia.

Solución

Pasemos a la descripción de la aplicación directa del método.

1. A la izquierda del primer valor X pon un numero 1 . Este será el valor aproximado del primer valor del coeficiente norte.

2. A la derecha de la columna y agregar otra columna nx. En la primera celda de esta columna escribimos la fórmula para multiplicar el coeficiente norte a la celda de la primera variable X. Al mismo tiempo, hacemos el enlace al campo con el coeficiente absoluto, ya que este valor no cambiará. Hacemos clic en el botón Ingresar.

3. Usando el controlador de relleno, copie esta fórmula en todo el rango de la tabla en la columna a continuación.

4. En una celda separada, calculamos la suma de las diferencias de los cuadrados de los valores. y y nx. Para ello, haga clic en el botón "Función de inserción".

5. En lo abierto "Asistente de funciones" buscando una entrada "SUMMKVRAZN". Selecciónelo y haga clic en el botón OK.

6. Se abre la ventana de argumentos. en campo "matriz_x" y. en campo "Array_y" ingrese un rango de celdas de columna nx. Para ingresar valores, simplemente coloque el cursor en el campo y seleccione el rango apropiado en la hoja. Después de ingresar, haga clic en el botón OK.

7. Ir a la pestaña "Datos". En la cinta de la caja de herramientas "Análisis" haga clic en el botón "Buscar una solución".

8. Se abre la ventana de parámetros de la herramienta. en campo "Optimizar función objetivo" especificar la dirección de la celda con la fórmula "SUMMKVRAZN". en parámetro "Antes" asegúrese de colocar el interruptor en la posición "Mínimo". en campo "Cambio de celdas" especificar la dirección con el valor del coeficiente norte. Haga clic en el botón "Encuentra una solución".

9. La solución se mostrará en la celda de coeficiente norte. Es este valor el que será el mínimo cuadrado de la función. Si el resultado satisface al usuario, haga clic en el botón OK en una ventana adicional.

Como puede ver, la aplicación del método de mínimos cuadrados es un procedimiento matemático bastante complicado. Lo hemos mostrado en acción con el ejemplo más simple, pero hay casos mucho más complejos. Sin embargo, el kit de herramientas de Microsoft Excel está diseñado para simplificar los cálculos tanto como sea posible.

http://multitest.semico.ru/mnk.htm

Provisiones generales

Cuanto menor sea el número en valor absoluto, mejor se elige la línea recta (2). Como característica de la precisión de la selección de una recta (2), podemos tomar la suma de cuadrados

Las condiciones mínimas para S serán

	(6)
	(7)

Las ecuaciones (6) y (7) se pueden escribir de la siguiente forma:

	(8)
	(9)

A partir de las ecuaciones (8) y (9) es fácil encontrar a y b a partir de los valores experimentales x i e y i . La línea (2) definida por las ecuaciones (8) y (9) se llama la línea obtenida por el método de los mínimos cuadrados (este nombre enfatiza que la suma de los cuadrados S tiene un mínimo). Las ecuaciones (8) y (9), a partir de las cuales se determina la recta (2), se denominan ecuaciones normales.

Es posible indicar una forma simple y general de compilar ecuaciones normales. Usando los puntos experimentales (1) y la ecuación (2), podemos escribir el sistema de ecuaciones para a y b

y 1 \u003d hacha 1 +b,
y2=ax2+b, ...		(10)
yn=axn+b,

Multiplica las partes izquierda y derecha de cada una de estas ecuaciones por el coeficiente en la primera incógnita a (es decir, x 1 , x 2 , ..., x n) y suma las ecuaciones resultantes, lo que da como resultado la primera ecuación normal (8).

Multiplicamos los lados izquierdo y derecho de cada una de estas ecuaciones por el coeficiente de la segunda incógnita b, es decir por 1, y sume las ecuaciones resultantes, dando como resultado la segunda ecuación normal (9).

Este método de obtención de ecuaciones normales es general: es adecuado, por ejemplo, para la función

es un valor constante y debe determinarse a partir de datos experimentales (1).

El sistema de ecuaciones para k se puede escribir:

Encuentre la línea (2) usando el método de mínimos cuadrados.

Solución. Encontramos:

X i = 21, y i = 46,3, x i 2 = 91, x i y i = 179,1.

Escribimos las ecuaciones (8) y (9)91a+21b=179.1,

21a+6b=46.3, de aquí encontramos
a=0,98 b=4,3.