13.10.2021
El método de mínimos cuadrados en Excel es una función de potencia. El método de mínimos cuadrados y encontrar una solución en Excel. Algunas palabras sobre la exactitud de los datos iniciales utilizados para la predicción.
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 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 cualesquiera ecuaciones linealmente independientes (si existen) y aplicando la fórmula X=A -1 CV, es decir, reduciendo el problema al previamente resuelto. En este caso, la solución resultante siempre satisfará las m - n ecuaciones restantes. Sin embargo, cuando se utiliza una computadora, es más conveniente utilizar un enfoque más general: el método de mínimos cuadrados. El método algebraico de mínimos cuadrados se entiende como un método para la resolución de sistemas de ecuaciones lineales. minimizando la norma euclidiana ¿Hacha? ¿b? >inf. (1.2) 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 mediciones se realizan con un error conocido. En estos casos, la ley del cambio de temperatura Q(t) se busca utilizando algún polinomio P(t) = + + + ... +, determinando los coeficientes desconocidos, ..., a partir de aquellas consideraciones de que el valor E(, ...,), definido por la igualdad aproximación algebraica de Gauss en Exel Tomó el valor mínimo. Dado que la suma de 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 Defina 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..., metro; 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 el i -ésimo 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. Desde el principio fue posible, utilizando la norma euclidiana de los vectores, escribir el problema en 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 a x en este punto deben ser iguales a cero. Las derivadas de esta función son 2A T B + 2A T Hacha 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 simétrica cuadrada. Además, tiene la propiedad de precisión positiva en el sentido de que (A>Ax, x) = (Ax, Ax)? 0. Comentario. A veces, una 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) según el método de mínimos cuadrados. El problema de mínimos cuadrados se puede interpretar gráficamente como una minimización de las distancias verticales desde los puntos de datos hasta la curva del modelo (ver Figura 1.1). Esta idea se basa en el supuesto 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 entre los datos y el modelo. El siguiente algoritmo para implementar OLS en Excel supone que ya se conocen todos los datos iniciales. Multiplicamos ambas partes de la ecuación matricial AЧX=B del sistema de la izquierda por la matriz transpuesta del sistema А Т: A T AX \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. Que sea necesario resolver el sistema. En Excel, la hoja de solución en modo de visualización de fórmulas para este problema se ve así: Resultados del cálculo: El vector deseado X se encuentra en el rango E11:E12. Al resolver un sistema dado de ecuaciones lineales, se utilizaron las siguientes funciones: 1. MINUTO: devuelve la inversa de una matriz almacenada en una matriz. 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 la matriz1 y la misma cantidad de columnas que la matriz2. Sintaxis: MULT(matriz1, matriz2). Array1, array2: 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 un número de filas igual al número de columnas de la matriz original y un número de columnas igual al número de filas de la matriz inicial. El método de mínimos cuadrados es un procedimiento matemático para construir una ecuación lineal que se acerque más a 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 utilizar para aplicar este método en los cálculos. Veamos cómo se hace. Usando el método en Excel o Habilitar el complemento Solver o Condiciones de la tarea o decisión Usando un método en Excel El método de mínimos cuadrados (LSM) es una descripción matemática de la dependencia de una variable de otra. Se puede utilizar para realizar previsiones. Habilite el complemento Solver Para utilizar OLS en Excel, debe habilitar el complemento "Buscar una solución", que está deshabilitado de forma predeterminada. 1. Ir a la pestaña "Archivo". 2. Haga 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 "Ir...". 5. Se abre una pequeña ventana. Pon una marca de verificación junto a la opción "Buscar una solución". Haga clic en el botón DE ACUERDO. Ahora la función Encontrar una solución en Excel se activa 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 imagen siguiente. 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 puede describirse mediante la dependencia y=nx. Tenemos que encontrar la suma mínima de cuadrados de la diferencia. Solución Procedamos 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 que el enlace al campo con el coeficiente sea 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 siguiente. 4. En una celda aparte calculamos la suma de las diferencias de los cuadrados de los valores. y Y nx. Para hacer esto, haga clic en el botón "Función de inserción". 5. Al aire libre "Asistente de funciones" buscando una entrada "SUMMKVRAZN". Selecciónelo y haga clic en el botón DE ACUERDO. 6. Se abre la ventana de argumentos. en el campo "matriz_x" y. en el campo "matriz_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 DE ACUERDO. 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 el campo "Optimizar la función objetivo" especifique 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 el campo "Cambiando de celdas" especifique 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 coeficientes. 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 DE ACUERDO en una ventana adicional. Como puedes 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 sencillo, 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 línea recta (2), podemos tomar la suma de cuadrados Las condiciones mínimas para S serán Las ecuaciones (6) y (7) se pueden escribir de la siguiente forma: A partir de las ecuaciones (8) y (9) es fácil encontrar a y b a partir de los valores experimentales x i y y i. La recta (2) definida por las ecuaciones (8) y (9) se llama recta obtenida por el método de mínimos cuadrados (este nombre enfatiza que la suma de 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 sencilla 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. Multiplica las partes izquierda y derecha de cada una de estas ecuaciones por el coeficiente de 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 sumar 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: Encuentra 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 4.1. Usando funciones integradas cálculo coeficientes de regresión llevado a cabo utilizando la función ESTILO LINEAL(Valores_y; Valores_x; Konst; Estadísticas), Valores_y- matriz de valores y, Valores_x- matriz opcional de valores X si matriz X omitido, se supone que se trata de una matriz (1;2;3;...) del mismo tamaño que Valores_y, Konst- un valor booleano que indica si se requiere la constante b era igual a 0. Si Konst tiene el significado VERDADERO u omitido, entonces b calculado de la forma habitual. Si el argumento Konst es FALSO, entonces b se supone que es 0 y los valores a se eligen de manera que la relación y=ax. Estadísticas- un valor booleano que indica si es necesario devolver estadísticas de regresión adicionales. Si el argumento Estadísticas tiene el significado VERDADERO, entonces la función ESTILO LINEAL devuelve estadísticas de regresión adicionales. Si el argumento Estadísticas tiene el significado MENTIR u omitido, entonces la función ESTILO LINEAL devuelve sólo el coeficiente a y permanente b. Hay que recordar que el resultado de las funciones. ESTIMACIÓN LINEAL() es un conjunto de valores: una matriz. Para el cálculo coeficiente de correlación se utiliza la función CORREL(Matriz1;matriz2), devolviendo los valores del coeficiente de correlación, donde Matriz1- conjunto de valores y, matriz2- conjunto de valores X. Matriz1 Y matriz2 debe ser del mismo tamaño. EJEMPLO 1. Adiccion y(X) se presenta en la tabla. Construir línea de regresión y calcular coeficiente de correlación. Ingresemos una tabla de valores en una hoja de MS Excel y construyamos un diagrama de dispersión. La hoja de trabajo tomará la forma que se muestra en la Fig. 2. Para calcular los valores de los coeficientes de regresión. A Y b seleccionar celdas A7:B7, pasemos al asistente de funciones y en la categoría Estadístico elige una función ESTILO LINEAL. Complete el cuadro de diálogo que aparece como se muestra en la Fig. 3 y presione DE ACUERDO. Como resultado, el valor calculado aparecerá solo en la celda. A6(Figura 4). Para que aparezca un valor en una celda B6 necesitas ingresar al modo de edición (tecla F2) y luego presione la combinación de teclas CTRL+MAYÚS+ENTRAR. Para calcular el valor del coeficiente de correlación por celda. C6 Se introdujo la siguiente fórmula: C7=CORRECCIÓN(B3:J3;B2:J2). Conociendo los coeficientes de regresión A Y b calcular los valores de la función y=hacha+b por dado X. Para ello introducimos la fórmula B5=$A$7*B2+$B$7 y copiarlo al rango С5:J5(Figura 5). Tracemos la línea de regresión en el diagrama. Seleccione los puntos experimentales en el gráfico, haga clic derecho y seleccione el comando Datos iniciales. En el cuadro de diálogo que aparece (Fig.5), seleccione la pestaña Fila y haga clic en el botón Agregar. Complete los campos de entrada, como se muestra en la Fig. 6 y presione el botón DE ACUERDO. Se agregará una línea de regresión al gráfico de datos experimentales. De forma predeterminada, su gráfico se mostrará como puntos no conectados mediante líneas suavizadas. Para cambiar la apariencia de la línea de regresión, realice los siguientes pasos. Haga clic derecho en los puntos que representan el gráfico de líneas, seleccione el comando Tipo de gráfico y establezca el tipo de diagrama de dispersión, como se muestra en la Fig. 7. El tipo de línea, el color y el grosor se pueden cambiar de la siguiente manera. Seleccione la línea en el diagrama, presione el botón derecho del mouse y seleccione el comando en el menú contextual Formato de serie de datos… A continuación, realice los ajustes, por ejemplo, como se muestra en la Fig. 8. Como resultado de todas las transformaciones, obtenemos una gráfica de datos experimentales y una línea de regresión en un área gráfica (Fig. 9). 4.2. Utilizando una línea de tendencia. La construcción de varias dependencias aproximadas en MS Excel se implementa como una propiedad del gráfico: línea de tendencia. EJEMPLO 2. Como resultado del experimento, se determinó cierta dependencia tabular. Seleccione y construya una dependencia aproximada. Construya gráficos de dependencias analíticas tabulares y ajustadas. La solución del problema se puede dividir en las siguientes etapas: entrada de datos iniciales, construcción de un diagrama de dispersión y adición de una línea de tendencia a este gráfico. Consideremos este proceso en detalle. Ingresemos los datos iniciales en la hoja de trabajo y tracemos los datos experimentales. A continuación, seleccione los puntos experimentales en el gráfico, haga clic derecho y use el comando Agregar yo línea de tendencia(Figura 10). El cuadro de diálogo que aparece le permite construir una dependencia aproximada. La primera pestaña (Fig. 11) de esta ventana indica el tipo de dependencia aproximada. El segundo (Fig. 12) define los parámetros constructivos: el nombre de la dependencia próxima; Pronóstico hacia adelante (hacia atrás) en norte unidades (este parámetro determina cuántas unidades hacia adelante (hacia atrás) son necesarias para extender la línea de tendencia); si se debe mostrar el punto de intersección de la curva con la línea y=constante; si mostrar o no la función de aproximación en el diagrama (muestre la ecuación en el parámetro del diagrama); Si colocar el valor de la desviación estándar en el diagrama o no (el parámetro puso el valor de la confiabilidad de la aproximación en el diagrama). Elijamos un polinomio de segundo grado como dependencia aproximada (Fig. 11) y derivemos una ecuación que describa este polinomio en la gráfica (Fig. 12). El diagrama resultante se muestra en la fig. 13. De manera similar, con líneas de tendencia puede elegir los parámetros de dependencias como lineal y=a∙x+b, logarítmico y=un en(X)+b, exponencial y=a∙eb, fuerza y=a x b, polinomio y=a∙x 2 +b∙x+C, y=a∙x 3 +b∙x 2 +c∙x+d y así sucesivamente, hasta el polinomio de sexto grado inclusive, Filtrado lineal. 4.3. Usando el decisor De considerable interés es la implementación en MS Excel de la selección de parámetros mediante el método de mínimos cuadrados utilizando un bloque de decisión. Esta técnica le permite elegir los parámetros de una función de cualquier tipo. Consideremos esta posibilidad en el ejemplo del siguiente problema. EJEMPLO 3. Como resultado del experimento, la dependencia z(t) presentada en la tabla Seleccionar coeficientes de dependencia Z(t)=En 4 +Bt 3 +Ct 2 +Dt+K por el método de mínimos cuadrados. Este problema es equivalente al problema de encontrar el mínimo de una función de cinco variables. Considere el proceso de resolución del problema de optimización (Fig. 14). Deja que los valores A, EN, CON, D Y A almacenado en células A7:E7. Calcular los valores teóricos de la función. z(t)=At4+Bt3+Ct2+Dt+K por dado t(B2:J2).
Para hacer esto, en la celda. B4 ingrese el valor de la función en el primer punto (celda B2): B4=$A$7*B2^4+$B$7*B2^3+$C$7*B2^2+$D$7*B2+$E$7. Copie esta fórmula en el rango С4:J4 y obtener el valor esperado de la función en puntos cuyas abscisas se almacenan en celdas B2:J2. a la celda B5 Introducimos una fórmula que calcula el cuadrado de la diferencia entre los puntos experimentales y calculados: B5=(B4-B3)^2, y copiarlo al rango С5:J5. en una celda F7 Almacenaremos el error cuadrático total (10). Para ello introducimos la fórmula: F7 = SUMA(B5:J5). Usemos el comando Servicio®Buscar una solución y resolver el problema de optimización sin restricciones. Complete los campos de entrada apropiados en el cuadro de diálogo que se muestra en la Fig. 14 y presione el botón Correr. Si se encuentra una solución, aparecerá la ventana que se muestra en la Fig. 15. El resultado del bloque de decisión será la salida a las celdas. A7:E7valores paramétricos funciones z(t)=At4+Bt3+Ct2+Dt+K. en las celdas B4:J4 obtenemos valor esperado de la función en los puntos de partida. en una celda F7 se mantendrá error cuadrático total. Puede visualizar los puntos experimentales y la línea ajustada en la misma área gráfica si selecciona el rango B2:J4, llamar Asistente para gráficos y luego formatee la apariencia de los gráficos resultantes. Arroz. 17 muestra la hoja de cálculo de MS Excel una vez realizados los cálculos. 5. REFERENCIAS 1. Alekseev E.R., Chesnokova O.V., Resolución de problemas de matemáticas computacionales en los paquetes Mathcad12, MATLAB7, Maple9. – NT Press, 2006.–596s. :enfermo. - (Tutorial) 2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, resolución de problemas matemáticos y de ingeniería. –M., BINOM, 2008.–260s. 3. I. S. Berezin y N. P. Zhidkov, Computational Methods, Moscú: Nauka, 1966. 4. Garnaev A.Yu., El uso de MS EXCEL y VBA en economía y finanzas. - San Petersburgo: BHV - Petersburgo, 1999.-332p. 5. B. P. Demidovich, I. A. Maron y V. Z. Shuvalova, Métodos numéricos de análisis.–M.: Nauka, 1967.–368p. 6. Korn G., Korn T., Manual de matemáticas para científicos e ingenieros.–M., 1970, 720p. 7. Alekseev E.R., Chesnokova O.V. Pautas para la realización de trabajos de laboratorio en MS EXCEL. Para estudiantes de todas las especialidades. Donetsk, DonNTU, 2004. 112 p. Tiene muchas aplicaciones, ya que permite una representación aproximada de una función determinada mediante otras más sencillas. LSM puede ser extremadamente útil en el procesamiento de observaciones y se utiliza activamente para estimar algunas cantidades a partir de los resultados de mediciones de otras que contienen errores aleatorios. En este artículo, aprenderá cómo implementar cálculos de mínimos cuadrados en Excel. 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 utilizando funciones integradas), debemos proceder de inmediato. para considerar un problema específico. Entonces, sea X el área de venta de una tienda de comestibles, medida en metros cuadrados, e Y sea 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 espacio comercial. Obviamente, la función Y = f (X) es creciente, ya que el hipermercado vende más productos que el puesto. Digamos que tenemos una tabla creada 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 o 6 objetos. Además, no se pueden utilizar resultados "anómalos". En particular, una pequeña boutique de élite puede tener un volumen de negocios muchas veces mayor que el de los grandes establecimientos de la clase "masmarket". Los datos de la tabla se pueden representar 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 selección de una función aproximada 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, se puede utilizar un polinomio de alto grado, pero esta opción no sólo 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 ay b. Para cualquier aproximación, la evaluación de su precisión es de particular importancia. Denota 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, se puede utilizar la suma de desviaciones, es decir, al elegir una línea recta para una representación aproximada de la dependencia de X de Y, se debe dar preferencia a la que tenga el valor más pequeño 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á negativas. Puedes resolver el problema utilizando 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 durante mucho tiempo su eficacia. 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: Así, la tarea de encontrar la línea recta que mejor describa 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, incluida la división por 2 y la manipulación de las sumas, obtenemos: Resolviendolo, 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 qué facturación tendrá la tienda en un área determinada, es adecuada la línea recta y = a * x + b *, que es un modelo de regresión para el ejemplo en cuestión. Por supuesto, no le permitirá encontrar el resultado exacto, pero le ayudará a tener una idea de si valdrá la pena comprar una tienda a crédito para un área en particular. 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 mediante el método de mínimos cuadrados en Excel, ingresa el signo “=" y selecciona la función “TENDENCIA”. En la ventana que se abre, complete los campos correspondientes, resaltando: Además, en la fórmula existe una variable lógica "Const". Si ingresa 1 en el campo correspondiente, esto significará que los cálculos deben realizarse asumiendo que b = 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. El análisis de regresión puede ser accesible incluso para los principiantes. La fórmula de Excel para predecir el valor de una serie 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: Se implementa mediante varias funciones. Uno de ellos se llama "PREDICCIÓN". Es similar a TENDENCIA, es decir, da el resultado de los cálculos utilizando el método de mínimos cuadrados. Sin embargo, sólo para una X, cuyo valor de Y se desconoce. Ahora conoces las fórmulas de Excel para tontos que te permiten predecir el valor futuro de un indicador según una tendencia lineal. El método de mínimos cuadrados es un procedimiento matemático para construir una ecuación lineal que se acerque más a 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 utilizar para aplicar este método en los cálculos. Veamos cómo se hace. El método de mínimos cuadrados (LSM) es una descripción matemática de la dependencia de una variable de otra. Se puede utilizar para realizar previsiones. Para utilizar OLS en Excel, debe habilitar el complemento "Buscar una solución", que está deshabilitado de forma predeterminada. Ahora la función Encontrar una solución en Excel se activa y sus herramientas aparecen en la cinta. 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 imagen siguiente. 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 puede describirse mediante la dependencia y=nx
. Tenemos que encontrar la suma mínima de cuadrados de la diferencia. Procedamos a la descripción de la aplicación directa del método. Como puedes 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 sencillo, 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.Mínimos cuadrados algebraicos
Análisis de datos experimentales
MCO en Excel
(6)
(7)
(8)
(9)
y 1 \u003d hacha 1 +b,
y2=ax2+b, ...
(10)
yn=axn+b,
a=0,98b=4,3.y
0.5
1.5
2.5
3.5
X
2.39
2.81
3.25
3.75
4.11
4.45
4.85
5.25
Arroz. 6
0.15
0.16
0.17
0.18
0.19
0.20
4.4817
4.4930
5.4739
6.0496
6.6859
7.3891
0,66
0,9
1,17
1,47
1,7
1,74
2,08
2,63
3,12
38,9
68,8
64,4
66,5
64,95
59,36
82,6
90,63
113,5
Planteamiento del problema en un ejemplo específico.
Algunas palabras sobre la exactitud de los datos iniciales utilizados para la predicción.
La esencia del método.
Puntuación de precisión
método de mínimos cuadrados
Cómo implementar el método de mínimos cuadrados en Excel
Algunas caracteristicas
Función PRONÓSTICO
Habilite el complemento Solver
Condiciones del problema
Solución
