Aplicación del método de mínimos cuadrados en Excel. Método de mínimos cuadrados en Excel

El método de mínimos cuadrados (MCO) pertenece al campo del análisis de regresión. Tiene muchas aplicaciones, ya que permite una representación aproximada de una función dada por otras más simples. MCO 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. Este artículo le mostrará cómo implementar cálculos de mínimos cuadrados en Excel.

Declaración del problema utilizando un ejemplo específico

Suponga que hay dos indicadores X e Y. Además, Y depende de X. Dado que OLS es de interés para nosotros desde el punto de vista del análisis de regresión (en Excel, sus métodos se implementan utilizando funciones integradas), entonces debería inmediatamente continúe para considerar un problema específico.

Entonces, sea X el espacio comercial de una tienda de comestibles, medido en metros cuadrados, e Y es el volumen de negocios anual, definido en millones de rublos.

Se requiere hacer un pronóstico de la facturación (Y) que tendrá la tienda si tiene un espacio comercial en particular. Obviamente, la función Y = f (X) está aumentando, ya que el hipermercado vende más mercancías que el puesto.

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

Digamos que tenemos una tabla construida a partir de datos para n tiendas.

De acuerdo a estadística matemática, los resultados serán más o menos correctos si se examinan los datos de al menos 5-6 objetos. Además, no puede utilizar 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 establecimientos minoristas de la clase "masmarket".

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 selección de la función de aproximación 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, puedes usar el polinomio 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 encontrar la línea recta y = ax + b, que se aproxima mejor a los datos experimentales, o más bien, a los coeficientes - ay b.

Evaluación de la precisión

Para cualquier aproximación, una evaluación de su precisión es de particular importancia. Denotemos 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 estimar la precisión de la aproximación, se 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 sobre Y, se debe dar preferencia a aquella en la que valor más pequeño sumas e i en todos los puntos considerados. Sin embargo, no todo es tan sencillo, ya que junto a las desviaciones positivas, prácticamente estarán presentes las negativas.

El problema se puede resolver utilizando los módulos de desviaciones o sus cuadrados. El último método es el más utilizado. Se utiliza en muchas áreas, incluido el análisis de regresión (Excel lo implementa con dos funciones integradas), y ha demostrado su valía 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. Por tanto, nada nos impide calcular el valor de la expresión (e 1 2 + e 2 2 + e 3 2 + ... e n 2).

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

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

Así, el problema de encontrar la recta que mejor describe la dependencia específica de las cantidades X e Y se reduce a calcular el mínimo de una función de dos variables:

Esto requiere igualar a cero las derivadas parciales con respecto a las nuevas variables ayb, 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 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 un área determinada, 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 le ayudará a tener una idea de si comprar una tienda a crédito para un área en particular dará sus frutos.

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

Excel tiene una función para calcular el valor de MCO. Tiene la siguiente forma: "TENDENCIA" (valores de Y conocidos; valores de X conocidos; nuevos valores de X; const.). Apliquemos la fórmula para calcular 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 rotación);
rango x 1,… x n, es decir, el tamaño del espacio comercial;
y los valores conocidos y desconocidos de x, para los cuales debe averiguar el tamaño del volumen de negocios (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 booleana "Const". Si ingresa 1 en el campo correspondiente, esto significará que se deben realizar los cálculos, asumiendo 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 presionar "Enter", sino que debe escribir en el teclado la combinación "Shift" + "Control" + "Enter" ("Ingresar").

Algunas caracteristicas

Análisis de regresión incluso se puede acceder en 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. Basta con conocer algunas de las 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 x conocidos como una variable separada.
Si no se especifica un rango con una x conocida en la ventana "TENDENCIA", entonces si la función se usa en Excel, el programa la considerará como una matriz que consta de números enteros, cuyo número corresponde al rango con los valores especificados. De la variable y.
Para obtener una matriz de valores "predichos" como salida, 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;…, que es acorde con el rango con los parámetros y ya dados.
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 acorde con las variables independientes.
Una matriz con valores de x conocidos puede contener varias variables. Sin embargo, si estamos hablando solo de uno, entonces se requiere que los rangos con los valores dados de xey sean proporcionales. En el caso de múltiples variables, desea que el rango con los valores de y dados quepa en una columna o una fila.

Función PRONÓSTICO

El análisis de regresión en Excel se implementa utilizando varias funciones. Uno de ellos se llama "PRONÓSTICO". Es similar a "TENDENCIA", es decir, da el resultado de 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 conoce las fórmulas en Excel para dummies que le permiten predecir el valor futuro de un indicador dado de acuerdo con 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 interesante y necesario en Excel, que se llama. Así que el artículo estará dedicado a este complemento en particular, y te lo contaré usando un ejemplo de uso. método de mínimos cuadrados(LSM) para buscar coeficientes de ecuación desconocidos al describir datos experimentales.

Cómo habilitar el complemento Buscar solución

Primero, descubramos 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, marque la casilla "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 (MCO) y dónde se puede aplicar.

Digamos que tenemos un conjunto de datos después de que hicimos un experimento en el que estudiamos el efecto de X en 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, obtenemos el valor de Y así y así ...

Tomaré un ejemplo súper simple (ver fig.).

Está claro que los puntos están ubicados uno tras otro como si estuvieran en una 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 definitivamente 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 (recuerde el plan de estudios de la escuela).

En general, tenemos que encontrar el coeficiente k. Esto es lo que haremos con OLS mediante la búsqueda de un complemento de solución.

El método consiste en el hecho de que (aquí - atención: hay que pensarlo) la suma de los cuadrados de las diferencias entre los valores obtenidos experimentalmente y los correspondientes calculados fue mínima. Es decir, cuando X1 = 1 el valor medido realmente 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 ... Con lo siguiente lo mismo: cuando X2 = 2, el valor medido realmente 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 entrenar sobre el uso de OLS y Buscar complementos de Excel de solución .

Aplicar el complemento de búsqueda de soluciones

1. Si no ha habilitado el complemento "buscar una solución", volvemos al punto Cómo habilitar la búsqueda de un complemento de 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 de X ". Por ejemplo, en la celda D1 ingresamos "= A1 * B1", en la celda D2 ingresamos "= A1 * B2", y así sucesivamente.

4. Creemos que el coeficiente k es igual a uno y la función f (x) = y = 1 * x es la primera aproximación a nuestra solución. Podemos calcular la suma de los cuadrados de las diferencias entre los valores medidos de Y y los calculados por la fórmula y = 1 * x. Podemos hacer todo esto manualmente introduciendo las referencias de celda apropiadas en la fórmula: "= (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ... etc. Finalmente estamos equivocados y entendemos que hemos perdido mucho tiempo. En Excel para calcular la suma de cuadrados de diferencias hay una fórmula especial, "SUMKVRAZN", que hará todo por nosotros. Introdúcelo en la celda A2 y establece los datos iniciales: el rango de valores medidos Y (columna C) y el rango de valores Y calculados (columna D).

4. Se ha calculado la suma de las diferencias de los cuadrados - ahora vamos a la pestaña "Datos" y seleccionamos "Buscar una solución".

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

6. Seleccione la celda A2 como objetivo y establezca la condición "establecer igual al valor mínimo". Recuerde que esta es la celda donde calculamos la suma de los cuadrados de las diferencias entre los valores calculados y medidos, y esta suma debe ser mínima. Haga clic en "ejecutar".

7. Se selecciona el coeficiente k. Ahora puede verificar que los valores calculados están ahora 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 datos utilizando una función lineal, exponencial, de potencia y polinomial, por lo que a menudo puede prescindir de n complementos de búsqueda de soluciones... Hablé de todos estos métodos de aproximación en el mío, así que si estás interesado, echa un vistazo. Pero cuando se trata de alguna función exótica con un coeficiente desconocido o problemas de optimización, aquí superestructura muy oportunamente.

Busque un complemento de solución se puede usar para otras tareas, lo principal es entender la esencia: hay una celda donde seleccionamos un valor, y hay una celda de destino en la que se establece una condición para la selección de un parámetro desconocido.
¡Eso es todo! En el próximo artículo te contaré un cuento de hadas sobre las vacaciones, así que para no perderte el artículo,

4.1. Usar funciones integradas

Cálculo coeficientes de regresión llevado a cabo usando la función

LINEST(Values_y; Valores X; Konst; Estadísticas),

Values_y- una matriz de valores y,

Valores X- matriz opcional de valores X si matriz NS se omite, entonces se asume que es una matriz (1; 2; 3; ...) del mismo tamaño que Values_y,

Konst- un valor booleano que indica si la constante es necesaria B era igual a 0. Si Konst tiene el significado CIERTO u omitido, entonces B se calcula de la forma habitual. Si el argumento Konst es FALSO, entonces B se establece igual a 0 y los valores a se seleccionan de modo que la relación y = ax.

Estadísticas: un valor booleano que indica si se deben devolver estadísticas de regresión adicionales. Si el argumento Estadísticas tiene el significado CIERTO, luego la función LINEST devuelve estadísticas de regresión adicionales. Si el argumento Estadísticas tiene el significado MINTIENDO u omitido, entonces la función LINEST solo devuelve el coeficiente a y constante B.

Hay que recordar que el resultado de las funciones LINEST () es un conjunto de valores, una matriz.

Para el cálculo coeficiente de correlación se utiliza la función

CORREL(Array1;Array2),

devolviendo los valores del coeficiente de correlación, donde Array1- matriz de valores y, Array2- matriz de valores X. Array1 y Array2 debe ser de la misma dimensión.

EJEMPLO 1... Adiccion y(X) se presenta en la tabla. Construir línea de regresión y calcular coeficiente de correlación.

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

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.

Calcular los valores de los coeficientes de regresión a y B asignar celdas A7: B7, Vaya al asistente de funciones y en la categoría Estadístico elige la función LINEST... Complete el cuadro de diálogo que aparece como se muestra en la Fig. 3 y presione OK.

Como resultado, el valor calculado aparecerá solo en la celda A6(figura 4). Para que el valor aparezca en la celda B6 es necesario 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 = CORREL (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 hacer esto, introducimos la fórmula

B5 = $ A $ 7 * B2 + $ B $ 7

y copiarlo al rango C5: J5(figura 5).

Dibujemos la línea de regresión en el diagrama. Destaquemos puntos experimentales en el gráfico, haga clic con el botón derecho y seleccione el comando Datos iniciales... En el cuadro de diálogo que aparece (Fig.5), seleccione la pestaña Hilera 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 OK... Se agrega una línea de regresión a la gráfica de datos experimentales. De forma predeterminada, su gráfico se mostrará como puntos no conectados por líneas de suavizado.

Para cambiar la apariencia de la línea de regresión, siga estos pasos. Haga clic derecho en los puntos que representan el gráfico de líneas, seleccione el comando Tipo de gráfico y establezca la vista del diagrama de dispersión, como se muestra en la Fig. 7.

Puede cambiar el tipo de línea, el color y el grosor de la siguiente manera. Seleccione una línea en el diagrama, haga clic con el botón derecho y seleccione el comando Formato de serie de datos ... A continuación, realice los ajustes, por ejemplo, como se muestra en la Fig. ocho.

Como resultado de todas las transformaciones, recibiremos un gráfico de datos experimentales y una línea de regresión en un área gráfica (Fig. 9).

4.2. Usando una línea de tendencia.

La construcción de varias dependencias aproximadas en MS Excel se implementa como una propiedad de gráfico: línea de tendencia.

EJEMPLO 2... Como resultado del experimento, se determinó cierta dependencia tabular.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Seleccione y cree una dependencia aproximada. Construya gráficos de dependencia analítica tabular y seleccionada.

La solución al problema se puede dividir en las siguientes etapas: ingresar datos iniciales, construir un diagrama de puntos y agregar una línea de tendencia a este diagrama.

Consideremos este proceso en detalle. Ingresemos los datos sin procesar en la hoja de trabajo y grafiquemos los datos experimentales. A continuación, seleccione los puntos experimentales en el gráfico, haga clic con el botón derecho y use el comando Agregar l iniciativa 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 de construcción:

· El nombre de la dependencia aproximada;

Pronóstico hacia adelante (hacia atrás) por norte unidades (este parámetro determina cuántas unidades hacia adelante (hacia atrás) es necesario extender la línea de tendencia);

Si mostrar el punto de intersección de una curva con una línea recta y = constante;

· Mostrar la función de aproximación en el diagrama o no (la opción de mostrar la ecuación en el diagrama);

· Si colocar el valor de la desviación estándar en el diagrama o no (colocar el parámetro en el diagrama el valor de la fiabilidad de aproximación).

Elijamos un polinomio de segundo grado como dependencia aproximada (Fig. 11) y derivemos la ecuación que describe este polinomio en la gráfica (Fig. 12). El diagrama resultante se muestra en la Fig. 13.

Del mismo modo, usando líneas de tendencia puede elegir los parámetros de las dependencias como

Lineal y=a ∙ x+B,

Logarítmico y=a ∙ ln(X)+B,

Exponencial y=a ∙ e b,

Ley de potencia 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 un polinomio del sexto grado inclusive,

· Filtración lineal.

4.3. Uso de la herramienta de análisis de opciones: encontrar una solución.

De gran interés es la implementación en MS Excel de la selección de los parámetros de la dependencia funcional por el método de mínimos cuadrados utilizando la herramienta de análisis de opciones: Búsqueda de solución. Esta técnica le permite elegir los parámetros de una función de cualquier tipo. Consideremos esta posibilidad usando el ejemplo del siguiente problema.

EJEMPLO 3... Como resultado del experimento se obtuvo la dependencia z (t), presentada en la tabla

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

Seleccionar coeficientes de dependencia Z (t) = En 4 + Bt 3 + Ct 2 + Dt + K 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

Consideremos el proceso de resolución del problema de optimización (Fig. 14).

Deja que los valores A, V, CON, D y PARA almacenado en celdas A7: E7... Calculemos los valores teóricos de la función Z(t)=En 4 + Bt 3 + Ct 2 + 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.

Copiemos esta fórmula al rango C4: J4 y obtenga el valor esperado de la función en los puntos, cuyas abscisas se almacenan en las celdas B2: J2.

En 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 C5: J5... En una celda F7 almacenaremos el error cuadrático total (10). Para hacer esto, introducimos la fórmula:

F7 = SUMA (B5: J5).

Usemos el comando Service®Busque una solución y resolver el problema de optimización sin restricciones. Complete los campos de entrada en el cuadro de diálogo que se muestra en la Fig. 14 y presione el botón Ejecutar... Si se encuentra una solución, entonces la ventana que se muestra en la Fig. 15.

El resultado del trabajo del bloque de decisión se enviará a las celdas. A7: E7valores paramétricos funciones Z(t)=En 4 + Bt 3 + Ct 2 + Dt + K... En celdas B4: J4 obtener valor de función esperado en los puntos de partida. En una celda F7 se mantendrá error cuadrático total.

Puede dibujar puntos experimentales y una línea ajustada en la misma área de gráficos seleccionando un rango B2: J4, llama Asistente para gráficos y luego formatear apariencia los gráficos resultantes.

Arroz. 17 muestra la hoja de trabajo de MS Excel después de los cálculos.

4.1. Usar funciones integradas

Cálculo coeficientes de regresión llevado a cabo usando la función

LINEST(Values_y; Valores X; Konst; Estadísticas),

Values_y- una matriz de valores y,

Valores X- matriz opcional de valores X si matriz NS se omite, entonces se asume que es una matriz (1; 2; 3; ...) del mismo tamaño que Values_y,

Hay que recordar que el resultado de las funciones LINEST () es un conjunto de valores, una matriz.

Para el cálculo coeficiente de correlación se utiliza la función

CORREL(Array1;Array2),

devolviendo los valores del coeficiente de correlación, donde Array1- matriz de valores y, Array2- matriz de valores X. Array1 y Array2 debe ser de la misma dimensión.

EJEMPLO 1... Adiccion y(X) se presenta en la tabla. Construir línea de regresión y calcular coeficiente de correlación.

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

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 el valor del coeficiente de correlación por celda C6 se introdujo la siguiente fórmula:

C7 = CORREL (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 hacer esto, introducimos la fórmula

B5 = $ A $ 7 * B2 + $ B $ 7

y copiarlo al rango C5: J5(figura 5).

Dibujemos la línea de regresión en el diagrama. Seleccione los puntos experimentales en el gráfico, haga clic con el botón derecho y seleccione el comando Datos iniciales... En el cuadro de diálogo que aparece (Fig.5), seleccione la pestaña Hilera 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 OK... Se agrega una línea de regresión a la gráfica de datos experimentales. De forma predeterminada, su gráfico se mostrará como puntos no conectados por líneas de suavizado.

Arroz. 6

Como resultado de todas las transformaciones, recibiremos un gráfico de datos experimentales y una línea de regresión en un área gráfica (Fig. 9).

4.2. Usando una línea de tendencia.

La construcción de varias dependencias aproximadas en MS Excel se implementa como una propiedad de gráfico: línea de tendencia.

EJEMPLO 2... Como resultado del experimento, se determinó cierta dependencia tabular.

0.15	0.16	0.17	0.18	0.19	0.20
4.4817	4.4930	5.4739	6.0496	6.6859	7.3891

Seleccione y cree una dependencia aproximada. Construya gráficos de dependencia analítica tabular y seleccionada.

La solución al problema se puede dividir en las siguientes etapas: ingresar datos iniciales, construir un diagrama de puntos y agregar una línea de tendencia a este diagrama.

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 de construcción:

· El nombre de la dependencia aproximada;

Pronóstico hacia adelante (hacia atrás) por norte unidades (este parámetro determina cuántas unidades hacia adelante (hacia atrás) es necesario extender la línea de tendencia);

Si mostrar el punto de intersección de una curva con una línea recta y = constante;

· Mostrar la función de aproximación en el diagrama o no (la opción de mostrar la ecuación en el diagrama);

· Si colocar el valor de la desviación estándar en el diagrama o no (colocar el parámetro en el diagrama el valor de la fiabilidad de aproximación).

Del mismo modo, usando líneas de tendencia puede elegir los parámetros de las dependencias como

Lineal y=a ∙ x+B,

Logarítmico y=a ∙ ln(X)+B,

Exponencial y=a ∙ e b,

Ley de potencia 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 un polinomio del sexto grado inclusive,

· Filtración lineal.

4.3. Usando un bloque de decisión

Es de considerable interés la implementación en MS Excel de la selección de parámetros por 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 usando el ejemplo del siguiente problema.

EJEMPLO 3... Como resultado del experimento se obtuvo la dependencia z (t), presentada en la tabla

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

Seleccionar coeficientes de dependencia Z (t) = En 4 + Bt 3 + Ct 2 + Dt + K 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

Consideremos el proceso de resolución del problema de optimización (Fig. 14).

B4 = $ A $ 7 * B2 ^ 4 + $ B $ 7 * B2 ^ 3 + $ C $ 7 * B2 ^ 2 + $ D $ 7 * B2 + $ E $ 7.

Copiemos esta fórmula al rango C4: J4 y obtenga el valor esperado de la función en los puntos, cuyas abscisas se almacenan en las celdas B2: J2.

En 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 C5: J5... En una celda F7 almacenaremos el error cuadrático total (10). Para hacer esto, introducimos la fórmula:

F7 = SUMA (B5: J5).

Puede dibujar puntos experimentales y una línea ajustada en la misma área de gráficos seleccionando un rango B2: J4, llama Asistente para gráficos y luego formatee la apariencia de los gráficos resultantes.

Arroz. 17 muestra la hoja de trabajo de MS Excel después de los cálculos.

5. REFERENCIAS

1. Alekseev ER, Chesnokova OV, Solució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. Berezin IS, Zhidkov NP, Methods of computation.-Moscú: Nauka, 1966.-632p.

4. Garnaev A.Yu., Uso de MS EXCEL y VBA en economía y finanzas. - SPb.: BHV - Petersburgo, 1999. - 332s.

5. Demidovich BP, Maron I A., Shuvalova VZ, 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. Instrucciones metódicas al trabajo de laboratorio en MS EXCEL. Para estudiantes de todas las especialidades. Donetsk, DonNTU, 2004.112 p.

El método de mínimos cuadrados es un procedimiento matemático para construir ecuación lineal que coincidiría más estrechamente con el conjunto de dos filas de números. El propósito de este método es minimizar el error cuadrático total. Excel tiene herramientas que puede usar para aplicar este método al calcular. Veamos cómo se hace esto.

El método de mínimos cuadrados (MCO) es una descripción matemática de la dependencia de una variable de la otra. Se puede utilizar en la previsión.

Habilitación del complemento Solver

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

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

Condiciones del problema

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

La función puede describir con mayor precisión esta dependencia:

Además, se sabe que para x = 0 y también es igual 0 ... Es por eso ecuación dada puede ser descrito por dependencia y = nx .

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

Solución

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

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