Método de mínimos cuadrados y búsqueda de soluciones en Excel. Aplicación del método de mínimos cuadrados en el cálculo de Excel por el menor método de cuadrados XLS

Bueno, en el trabajo antes de que informara la inspección, se escribe el artículo en el hogar para la conferencia, ahora puede escribir al blog. Mientras estos datos procesados, me di cuenta de que no podía escribir sobre la superestructura muy fresca y necesaria en Excel, que se llama. Por lo que el artículo se dedicará a esta superestructura, y le informaré sobre el ejemplo del uso método de los cuadrados más pequeños. (MNC) para buscar coeficientes desconocidos de la ecuación al describir datos experimentales.

Cómo habilitar la "búsqueda de soluciones" complementaria

Primero entenderemos cómo encender esta superestructura.

1. Vaya al menú "Archivo" y seleccione el elemento de configuración de Excel.

2. En la ventana que aparece, seleccione "Solución de soluciones" y haga clic en "IR".

3. En la siguiente ventana, ponemos una marca frente al elemento "Búsqueda de soluciones" y haga clic en Aceptar.

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

Método mínimo cuadrado

Ahora brevemente O. método de mínimos cuadrados (MNC) Y sobre dónde se puede aplicar.

Supongamos que tenemos un conjunto de datos después de que hagamos algún experimento, donde estudiamos los efectos de los valores del valor de Y.

Queremos que este efecto describirá matemáticamente, para usar esta fórmula para usar y saber que si cambiamos la cantidad de X en tanto, obtenemos el valor de Y tales ...

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

Está claro que los puntos se ubican entre sí como en línea recta, y por lo tanto asumimos audazmente que nuestra dependencia es descrita por la función lineal y \u003d kx + b. Al mismo tiempo, estamos seguros de que con X es igual a cero, el valor de Y también es cero. Por lo tanto, una función que describe la adicción será aún más fácil: y \u003d kx (recuerde el programa escolar).

En general, tenemos que encontrar el coeficiente k. Eso es lo que haremos con MNA con el uso de la solución adicional para soluciones.

El método es que (aquí - Atención: debe pensar) la suma de los cuadrados de la diferencia en obtenidos experimentalmente y los valores de cálculo correspondientes fueron mínimos. Es decir, cuando x1 \u003d 1, el valor medido en realidad y1 \u003d 4.6, y el estimado y1 \u003d f (x1) es 4, el cuadrado de la diferencia (y1-y1) ^ 2 \u003d (4-4.6) ^ 2 \u003d 0.36. Con lo siguiente de la misma manera: cuando x2 \u003d 2, el valor medido en realidad y2 \u003d 8.1, y el U2 calculado es 8, el cuadrado de la diferencia será (Y2-Y2) ^ 2 \u003d (8-8,1) ^ 2 \u003d 0.01. Y la suma de todos estos cuadrados debe ser el mínimo posible.

Entonces, procederemos a entrenar sobre el uso de MNK y add-in Excel "Búsqueda de soluciones" .

Solicitud de búsqueda de soluciones adicionales

1. Si no encendió la "búsqueda de soluciones" complementaria, luego regresamos al artículo Cómo habilitar la "solución" de la solución complementaria y encender 🙂

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

3. En la columna B, resolvimos los valores del parámetro X, en la columna C: los valores del parámetro y. En las celdas de la columna, introducimos la fórmula: "CEFICIENT K multiplica a X Valor. Por ejemplo, en la celda D1, ingresamos "\u003d A1 * B1", en la celda D2, ingresamos "\u003d A1 * B2", etc.

4. Creemos que el coeficiente K es igual a una 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 los cuadrados de las diferencias entre los valores medidos del valor de Y y se calculan de acuerdo con la fórmula Y \u003d 1 * X. Podemos hacer todo esto manualmente, impulsado en la fórmula los enlaces correspondientes a las celdas: "\u003d (D2-C2) ^ 2 + (D3-C3) ^ 2 + (D4-C4) ^ 2 ..., etc. en El final estamos equivocados y entendemos que perdieron un montón de tiempo. En Excel, hay una fórmula especial para calcular la suma de cuadrados de diferencias, que se introducirá en una célula A2 y establecerá los datos de origen: el rango de medida Valores Y (columna C) y rango de valores calculados y (columna D).

4. Se calculó la suma de la diferencia en los cuadrados, ahora vamos a la pestaña "Datos" y eligimos la "Búsqueda de soluciones".

5. En el menú que aparece como una celda variable, seleccione la celda A1 (la que tiene el coeficiente K).

6. Como objetivo, seleccione la celda A2 y configure la condición para "Establecer valor mínimo igual". Recuerde que esta es una celda donde hacemos un cálculo de la suma de los cuadrados de las diferencias entre los valores calculados y medidos, y la cantidad debe ser mínima. Haga clic en "Ejecutar".

7. El coeficiente K está seleccionado. Ahora puede asegurarse de 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, hay herramientas especiales que le permiten describir los datos utilizando una función lineal, exponencial, potencia y polinomial, por lo que a menudo es posible hacer sin ayuntamiento de "solución de solución". Continuí sobre todos estos métodos de aproximación a mi manera, así que si me pregunto, mira. Pero cuando se trata de alguna función exótica. con un coeficiente desconocido o tareas de optimización, entonces aquí superestructura Como es imposible por cierto.

Add-in "Búsqueda de soluciones" También puede usarlo para otras tareas, lo principal es comprender la esencia: hay una celda donde seleccionamos el valor, pero hay una celda objetivo en la que se especifica una condición para la selección de un parámetro desconocido.
¡Eso es todo! En el siguiente artículo le informaré un cuento de hadas sobre las vacaciones, para que no proteja la producción del artículo,

Método mínimo cuadrado Se utiliza para estimar los parámetros, la ecuación de regresión.

Uno de los métodos para estudiar los vínculos estocásticos entre los signos es un análisis de regresión.
El análisis de regresión es la conclusión de la ecuación de regresión, con la que hay un valor promedio de una variable aleatoria (resultado de la señal), si se conoce el valor de otras variables (u otras) variables (factores). Incluye los siguientes pasos:

selección de formulario de comunicación (tipo de ecuación de regresión analítica);
estimación de los parámetros de la ecuación;
evaluación de la calidad de la ecuación de regresión analítica.

La mayoría de las veces, se usa una forma lineal para describir la conexión estadística de los signos. La advertencia a la comunicación lineal se debe a una clara interpretación económica de sus parámetros, limitada por variables por variables y en la mayoría de los casos, se transforman formas no lineales de comunicación para los cálculos (logration o reemplazando las variables) en una forma lineal.
En el caso de un enlace de pareja lineal, la ecuación de regresión tomará la forma: y i \u003d a + b · x i + u i. Los parámetros de esta ecuación A y B se estiman de acuerdo con la observación estadística X e Y. El resultado de una evaluación de este tipo es la ecuación:, donde - estimaciones de los parámetros A y B, el valor de la característica resultante (variable) obtenida por la ecuación de regresión (valor calculado).

La mayoría de las veces para estimar los parámetros. método de mínimos cuadrados (MNC).
El método de mínimos cuadrados da a las mejores estimaciones (ricas, eficientes y desbloqueadas) de los parámetros de la ecuación de regresión. Pero solo si se realizan ciertos requisitos previos en relación con un término aleatorio (U) y una variable independiente (X) (vea los fondos del MNC).

El problema de evaluar los parámetros de la ecuación de pares lineales por el método de mínimos cuadrados Consiste en lo siguiente: Para obtener dichas estimaciones de los parámetros, en las que la suma de los cuadrados de las desviaciones de los valores reales del signo efectivo - Y I en los valores calculados es mínimo.
Formalmente criterio MNK. Puedes escribir así: .

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

Método mínimo cuadrado.
El método sincero máximo (para un modelo de regresión lineal clásico normal, se pospone la normalidad de los residuos de regresión).
El método generalizado de cuadrados más pequeños de OMNA se utiliza en caso de autocorrelación de errores y en el caso de la heterossasticidad.
El método de los cuadrados más pequeños suspendidos (un caso especial de Omna con residuos HETER-VISASIC).

Ilustramos la esencia. método cuadrado más pequeño clásico gráficamente. Para hacer esto, construimos un calendario de puntos de acuerdo con las observaciones (x i, y i, i \u003d 1; n) en un sistema de coordenadas rectangular (dicho cuadro de punto se llama el campo de correlación). Intentaremos elegir una línea recta que esté más cerca de los puntos del campo de correlación. De acuerdo con el método de mínimos cuadrados, la línea se selecciona de modo que la suma de los cuadrados de las distancias verticales entre los puntos del campo de correlación y esta línea sería mínimo.

Registro matemático de esta tarea: .
Los valores de Y I y X I \u003d 1 ... n nos conocen, estos son datos observacionales. En la función S, son constantes. Las variables en esta característica son las estimaciones de parámetros deseadas,. Para encontrar un mínimo de las funciones de 2 variables, es necesario calcular los derivados privados de esta función para cada uno de los parámetros y equipararlos cero, es decir, .
Como resultado, obtenemos un sistema de 2 ecuaciones lineales normales:
Resolviendo este sistema, encontraremos las estimaciones de parámetros deseadas:

La exactitud del cálculo de los parámetros de la ecuación de regresión se puede probar comparando las cantidades (quizás alguna discrepancia debido a los cálculos de redondeo).
Para calcular las estimaciones de los parámetros, puede construir la Tabla 1.
El signo de coeficiente de regresión indica la dirección de la comunicación (si B\u003e 0, la línea es directa, si B<0, то связь обратная). Величина b показывает на сколько единиц изменится в среднем признак-результат -y при изменении признака-фактора - х на 1 единицу своего измерения.
Formalmente, el valor del parámetro A es el valor promedio de Y con X igual a cero. Si el signatador no tiene y no puede tener un valor cero, entonces la interpretación anterior del parámetro y no tiene sentido.

Estimación de la tensión de la comunicación entre los signos. Se lleva a cabo utilizando el coeficiente de correlación del par lineal - R x, Y. Puede ser calculado por la fórmula: . Además, el coeficiente de correlación de combinación lineal se puede determinar a través del coeficiente de regresión B: .
El área de valores permisibles del coeficiente lineal de correlación de pares de -1 a +1. El signo de coeficiente de correlación indica la dirección de la comunicación. Si R x, Y\u003e 0, entonces la conexión es recta; Si r x, y<0, то связь обратная.
Si este coeficiente está cerca de uno, la conexión entre las características se puede interpretar como un mínimo de lineal. Si su módulo es igual a una unidad ê r x, y ê \u003d 1, la conexión entre los signos es lineal funcional. Si los signos X e Y son linealmente independientes, entonces R x, Y está cerca de 0.
Para calcular R x, Y también puede usar la Tabla 1.

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 dispersión de y; explicada por la ecuación de regresión;
e 2 - Dispersión residual (ecuación de regresión inexplicable) Y;
s 2 y es una dispersión total (completa) Y.
El coeficiente de determinación caracteriza la proporción de variación (dispersión) del signo efectivo y, explicado por la regresión (y, en consecuencia, el factor X), en la variación general (dispersión) y. El coeficiente de determinación R 2 YX toma valores de 0 a 1. En consecuencia, el valor de 1-R 2 Yx caracteriza la fracción de la dispersión y causada por la influencia de otros factores no contabilizados en el modelo y los errores de especificación.
Con regresión lineal pareada R 2 yx \u003d R 2 yx.

4.1. Utilizando funciones incorporadas

Cálculo coeficientes de recesión realizado utilizando la función

Linne(Valores_y.; Valores_x.; Const; estadísticas),

Valores_y. - matriz de valores y

Valores_x.- Array opcional de valores. x.Si una matriz h. omitido, entonces se supone que esta matriz (1; 2; 3; ...) del mismo tamaño que Valores_y.,

Const- un valor lógico que indica si está obligado a constante b. fue igual a 0. Si Const tiene el significado CIERTO u omitido entonces b. Se calcula de la manera habitual. Si el argumento Const Importa una mentira, entonces b. Se basa igual a 0 y valores. uNA. seleccionado para que la proporción y \u003d hacha.

Estadísticas- un valor lógico que indica si desea devolver estadísticas de regresión adicionales. Si el argumento Estadísticas tiene el significado CIERTO, entonces función Linne Devuelve estadísticas de regresión adicionales. Si el argumento Estadísticas tiene el significado FALSO u omitido, entonces la función Linne Devuelve solo el coeficiente uNA. y constante b..

Debe recordarse que el resultado de funciones. Linene ()es un montón de valores: una matriz.

Para el cálculo coeficiente de correlación Función usada

Cornejo(Masivo1;Masivo2.),

varios valores del coeficiente de correlación donde Masivo1 - matriz de valores y, Masivo2. - matriz de valores x.. Masivo1 y Masivo2. Debe ser una dimensión.

Ejemplo 1.. Adiccion y(x.) Presentado 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

Presentamos la tabla de valores a la hoja de MS Excel y construimos un calendario de puntos. La lista de trabajo tomará la vista representada en la FIG. 2.

Para calcular los valores de los coeficientes de regresión. peroy b.reflejos A7: B7, Pasar a la maestría de funciones y en la categoría Estadístico Elige una característica Linne. Llene el cuadro de diálogo aparecido como se muestra en la FIG. 3 y haga clic está bien..

Como resultado, el valor calculado aparecerá solo en la celda. A6. (Fig.4). Para que aparezca el valor en la celda. B6. Necesita ingresar al modo de edición (clave F2)y luego presione la combinación de teclas Ctrl + Shift + Entrar.

Para calcular el valor del coeficiente de correlación en la celda. C6. Se introdujo la siguiente fórmula:

C7 \u003d Corvela (B3: J3; B2: J2).

Conociendo los coeficientes de regresión. peroy b. Calcular los valores de la función y=hACHA.+b. Para especificado x.. Para hacer esto, presentamos la fórmula.

B5 \u003d $ A $ 7 * B2 + $ B $ 7

y copiarlo en el rango C5: J5.(Fig. 5).

Mostraré la línea de regresión en el diagrama. Resalte 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 Fila y haga clic en el botón Agregar. Rellene los campos de entrada, como se muestra en la FIG. 6 y presiona el botón OK. La línea de regresión se agregará al calendario de datos experimentales. De forma predeterminada, su horario se representará en forma de puntos no conectados por líneas de alisado.

Higo. 6.

Para cambiar el tipo de línea de regresión, ejecute los siguientes pasos. Haga clic con el botón derecho en los puntos que muestren el programa de línea, seleccione el comando Tipo de diagramay establece el tipo de diagrama de punto, como se muestra en la FIG. 7.

Tipo de línea, su color y su grosor se pueden cambiar de la siguiente manera. Seleccione la línea en el diagrama, haga clic en el botón derecho del mouse y seleccione el comando en el menú contextual Formato de la serie de datos ... A continuación, haga instalaciones, por ejemplo, como se muestra en la FIG. ocho.

Como resultado de todas las transformaciones, obtenemos una gráfica de datos experimentales y 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 aproximantes en MS Excel se implementa como propiedades de la tabla. línea de tendencia.

Ejemplo 2.. Como resultado del experimento, se determinó alguna 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 construya la adicción aproximada. Construir gráficos tabular y seleccionada de dependencia analítica.

La resolución de problemas se puede dividir en los siguientes pasos: ingresar los datos de origen, construir un programa de punto y agregar una línea de tendencia a estos gráficos.

Considere este proceso en detalle. Presentamos los datos de origen en la hoja de trabajo y construimos una gráfica de datos experimentales. A continuación, seleccione los puntos experimentales en el gráfico, haga clic con el botón derecho y use el comando Agregarl. tendencia y tendencia (Fig. 10).

El cuadro de diálogo que aparece le permite construir una dependencia aproximada.

En la primera pestaña (Fig. 11) de esta ventana, se indica el tipo de dependencia de aproximación.

En la segunda (Fig. 12) define los parámetros de la construcción:

· El nombre de la dependencia aproximada;

· Pronóstico adelante (atrás) en nORTE. unidades (este parámetro determina para qué número de unidades por delante (ATRÁS) debe estar extendiendo la línea de tendencia);

· Muestra si el punto de intersección de la curva con una línea recta y \u003d const.;

· Muestre una función de aproximación en un diagrama o no (parámetro para mostrar la ecuación en el diagrama);

· ¿El diagrama de la desviación estándar o no (el parámetro se coloca en el diagrama del valor de la precisión de la aproximación)?

Elegimos un polinomio de segundo grado como una dependencia aproximadora (Fig. 11) y eliminamos la ecuación que describe este polinomio en la tabla (Fig. 12). El diagrama resultante se presenta en la FIG. 13.

Del mismo modo con la ayuda líneas de tendencia Puede elegir los parámetros de tales dependencias como

· Lineal y=a ∙ X.+b.,

· Logarítmico y=a ∙ ln.(x.)+b.,

· Exponencial y=a ∙ e b,

· Energía y=a ∙ x b,

· Polinomial y=a ∙ X. 2 +b ∙ X.+c., y=a ∙ X. 3 +b ∙ X. 2 +c ∙ X + D Y así sucesivamente, al 6º grado polinomial inclusivo,

· Filtrado lineal.

4.3. Usando un bloque decisivo

El interés significativo es la implementación de los parámetros en MS Excel utilizando los cuadrados más pequeños utilizando el bloque decisivo. Esta técnica le permite seleccionar los parámetros de la función de cualquier tipo. Considere esta función en el ejemplo de la siguiente tarea.

Ejemplo 3.. Como resultado del experimento, 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

Recoger los coeficientes de la adicción Z (t) \u003d a 4 + bt 3 + ct 2 + dt + k El método de mínimos cuadrados.

Esta tarea es equivalente a la tarea de encontrar una función mínima de cinco variables.

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

Dejar de que los significados PERO, EN, DE, D. y A Almacenado en celdas A7: E7.. Calcular los valores teóricos de la función. Z.(t.)=A 4 + BT 3 + CT 2 + DT + K Para especificado t.(B2: J2.). Para hacer esto en la celda B4. Ingresamos el valor de la función en el primer punto (Cell B2.):

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

Copia esta fórmula en el rango. C4: J4. y obtenemos el valor esperado de la función en los puntos, cuyos absclaciones se almacenan en las células B2: J2..

En la celda. B5. Presentamos una fórmula que calcula el cuadrado de la diferencia entre los puntos experimentales y calculados:

B5 \u003d (B4-B3) ^ 2,

y copiarlo en el rango C5: J5.. En celda F7. Guardaremos un error cuadrático total (10). Para hacer esto, presentamos la fórmula:

F7 \u003d CANTIDADES (B5: J5).

Utilizamos el equipo. Soluciones de Service®Poisk y resolver el problema de la optimización sin restricciones. Rellene los campos de entrada apropiados en el cuadro de diálogo que se muestra en la FIG. 14 y presiona el botón Llevar a cabo. Si se encuentra la solución, la ventana que se muestra en la FIG. quince.

El resultado del bloque decisivo será la conclusión en la célula. A7: E7.valores paramétricos Funciones Z.(t.)=A 4 + BT 3 + CT 2 + DT + K. En células B4: J4. Recibir función de valor esperado En los puntos de partida. En celda F7. será almacenado error cuadrático total.

Picta los puntos experimentales y la línea seleccionada en un área gráfica puede ser si se le asigna el rango B2: J4.Llamada Carta maestraY luego formatear la apariencia de los gráficos recibidos.

Higo. 17 Muestra la lista de trabajo de MS Excel después de que se realicen los cálculos.

5. Lista de referencias

1. Alekseev E.R., Chesnokova O.V., resolviendo problemas de las matemáticas informáticas en los paquetes de MathCAD12, MATLAB7, MAPLE9. - NT Press, 2006.-596C. : ILLINOIS. - (tutorial)

2. Alekseev E.R., Chesnokova O.V., E.A. Rudchenko, Scilab, Solución de ingeniería y tareas matemáticas. -M., Binom, 2008.-260S.

3. Berezin I.S., Lodine N.P., Métodos de cálculos. - M.: Ciencia, 1966.-632C.

4. Garnaev A.YU., utilizando MS Excel y VBA en economía y finanzas. - SPB.: BHV - PETERSBURG, 1999.-332C.

5. Demidovich B.P., Maron y A., Shuvalova v.Z., Métodos numéricos para analizar. - M.: Ciencia, 1967.-368С.

6. Maíz G., Korn T., directorio de matemáticas para científicos e ingenieros. - M., 1970, 720s.

7. Alekseev E.R., Chesnokova O.V. Instrucciones metódicas para realizar trabajos de laboratorio en MS Excel. Para estudiantes de todas las especialidades. Donetsk, Donntu, 2004. 112 p.

Tiene muchas aplicaciones, ya que le permite realizar una representación aproximada de la función especificada por otros más simples. La MNA puede ser extremadamente útil para procesar las observaciones, y se utiliza activamente para evaluar algunos valores de acuerdo con los resultados de las mediciones de otros errores aleatorios. Desde este artículo, aprenderá a implementar los cálculos utilizando el método de mínimos cuadrados en Excel.

Configuración de la tarea en un ejemplo específico

Supongamos que hay dos indicadores x e y. Y y depende de X. Dado que MNK está interesado en nosotros desde el punto de vista del análisis de regresión (en Excel, sus métodos se implementan utilizando funciones incorporadas), entonces es necesario ir de inmediato a La consideración de una tarea específica.

Por lo tanto, permita que X sea el área de comercio de la tienda de alimentos, medida en metros cuadrados y un volumen de negocios anual, determinado en millones de rublos.

Se requiere que haga un pronóstico que el comercio (Y) estará en la tienda, si tiene una u otra área de compras. Obviamente, la función Y \u003d F (x) está aumentando, ya que el hipermercado vende más productos que un puesto.

Unas pocas palabras sobre la exactitud de los datos de origen utilizados para predecir

Supongamos que tenemos una tabla construida de acuerdo con los datos de las tiendas de N.

Según las estadísticas matemáticas, los resultados serán más o menos correctos si se investigan los datos de al menos 5-6 objetos. Además, es imposible usar los resultados "anómalos". En particular, una boutique pequeña de élite puede tener un volumen de negocios a veces mayor que la facturación comercial de grandes puntos comerciales de la clase Masmarket.

La esencia del método.

Los datos de la tabla se pueden representar en un plano de Decartian en forma de puntos m 1 (x 1, y 1), ... m n (x n, y n). Ahora, la solución al problema se reduce a la selección de la función de aproximación y \u003d f (x), que tiene un gráfico, pasando lo más cerca posible de los puntos m 1, m 2, .. m n.

Por supuesto, es posible utilizar un polinomio de alto grado, pero esta opción no solo es más difícil, sino que es simplemente incorrecta, ya que no reflejará la tendencia principal que es necesario detectar. La solución más razonable es la búsqueda de un Y \u003d AX + B, que mejor brinda los datos experimentales y, más precisamente, los coeficientes, A y B.

Estimación de la precisión

En cualquier aproximación, adquiere particular importancia para evaluar su precisión. Denote por e i la diferencia (desviación) entre los valores funcionales y experimentales para el punto x i, es decir. E i \u003d y i - f (x i).

Es obvio que se puede usar una cantidad de desviaciones para estimar la precisión de la aproximación, es decir, cuando selecciona la línea recta para una representación aproximada x desde y, debe preferir el que el valor más pequeño de la cantidad de EI en Todos los puntos bajo consideración. Sin embargo, no todo es tan simple, ya que, junto con las desviaciones positivas, casi serán presentes y negativas.

Puede resolver el problema utilizando los módulos de desviación o sus cuadrados. El último método fue ampliamente distribuido. Se utiliza en muchas áreas, incluido el análisis de regresión (en Excel, su implementación se lleva a cabo utilizando dos funciones incorporadas), y ha demostrado durante mucho tiempo su efectividad.

Método mínimo cuadrado

En Excel, como se sabe, existe una función autosumutial incorporada, que le permite calcular los valores de todos los valores ubicados en el rango dedicado. Por lo tanto, nada interferirá con nosotros para calcular el valor de la expresión (E 1 2 + E 2 2 + E 3 2 + ... E n 2).

En el registro matemático, tiene la forma:

Dado que originalmente se decidió aproximar con una línea recta, tenemos:

Por lo tanto, la tarea de encontrar una línea recta, que mejor describe la dependencia específica de los valores de X e Y, se reduce al cálculo de la función mínima de dos variables:

Para hacer esto, es necesario equipararse a cero derivados privados de acuerdo con las nuevas variables A y B, y resolver el sistema primitivo que consiste en dos ecuaciones con 2 especies desconocidas:

Después de transformaciones simples, incluida la división en 2 y la manipulación con sumas, obtenemos:

Resolviéndolo, por ejemplo, por el dispositivo, obtenemos un punto estacionario con algunos coeficientes A * y B *. Esto es un mínimo, es decir, para la predicción, cuyo comercio estará en la tienda en un área determinada, la línea recta y \u003d a * x + b * es adecuada, que es un modelo de regresión para un ejemplo, que se discute. Por supuesto, no permitirá encontrar un resultado preciso, pero ayudará a tener una idea de si se comprará la compra de un área de concreto.

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

En "Excel" hay una función para calcular el valor de la MNA. Tiene la siguiente forma: "Tendencia" (conocida. Valores y conocidos. X valores; Nuevos valores x; const.). Aplique la fórmula de cálculo de MNC en Excel a nuestra mesa.

Para hacer esto, en la celda en la que se debe mostrar el resultado del cálculo de los cuadrados más pequeños en Excel, introducimos el "\u003d" signo y seleccione la función "Tendencia". En la ventana abierta, llena los campos correspondientes, resaltando:

rango de valores conocidos para y (en este caso, datos de facturación);
rango x 1, ... x n, es decir, los valores del espacio minorista;
y valores X conocidos, y desconocidos, para los cuales es necesario descubrir el tamaño de la facturación (para obtener información sobre su ubicación en la hoja de trabajo, consulte a continuación).

Además, la fórmula tiene una variable lógica "Const". Si ingresa al campo correspondiente 1, esto significará que se deben hacer los cálculos, creyendo que B \u003d 0.

Si necesita aprender el pronóstico durante más de un valor X, luego de ingresar a la fórmula, no debe hacer clic en "Enter", pero debe marcar el "Shift" + "Control" + "Enter" Combinación en el teclado.

Algunas caracteristicas

El análisis de regresión puede ser accesible incluso a las teteras. Fórmula de Excel para predecir el valor de una matriz de variables desconocidas - "Tendencia", incluso aquellos que nunca han escuchado sobre el método de mínimos cuadrados se pueden usar. Es suficiente solo saber algunas características de su trabajo. En particular:

Si organiza la gama de valores conocidos de la variable y en una fila o columna, la línea (columna) con valores X conocidos será percibido por el programa como una variable separada.
Si la ventana "Tendencia" no especifica un rango con X conocido, entonces, en el caso de usar la función en Excel, el programa lo considerará como una matriz que consiste en enteros, el número de los cuales corresponde al rango con los valores especificados De la variable y.
Para obtener una matriz de valores "predichos" en la salida, la expresión para calcular la tendencia debe administrarse como una fórmula de matriz.
Si no se especifican los nuevos valores X, la función "Tendencia" los considera iguales a los famosos. Si no se especifican, se toma una matriz de 1 como argumento; 2; 3; 4; ..., que es acorde con un rango con parámetros ya especificados y.
Un rango que contiene nuevos valores X debe consistir en las mismas o más filas o columnas como un rango con valores y especificados. En otras palabras, debe ser proporcional a las variables independientes.
En una matriz con valores X conocidos, pueden estar contenidas varias variables. Sin embargo, si estamos hablando solo de uno, se requiere que los rangos con los valores X e Y especificados sean proporcionados. En el caso de varias variables, es necesario que el rango con los valores especificados de la Y se acomodó en una columna o en una fila.

Función predicz

Implementado utilizando múltiples funciones. Uno de ellos se llama "predice". Es similar a la "tendencia", es decir, emite el resultado de los cálculos utilizando el método de mínimos cuadrados. Sin embargo, solo para una X, para la cual Y se desconoce.

Ahora conoce la fórmula en Excel para las teteras, lo que permite predecir el valor del valor futuro de uno u otro indicador de acuerdo con la tendencia lineal.

4.1. Utilizando funciones incorporadas

Cálculo coeficientes de recesión realizado utilizando la función

Linne(Valores_y.; Valores_x.; Const; estadísticas),

Valores_y. - matriz de valores y

Valores_x.- Array opcional de valores. x.Si una matriz h. omitido, entonces se supone que esta matriz (1; 2; 3; ...) del mismo tamaño que Valores_y.,

Debe recordarse que el resultado de funciones. Linene ()es un montón de valores: una matriz.

Para el cálculo coeficiente de correlación Función usada

Cornejo(Masivo1;Masivo2.),

varios valores del coeficiente de correlación donde Masivo1 - matriz de valores y, Masivo2. - matriz de valores x.. Masivo1 y Masivo2. Debe ser una dimensión.

Ejemplo 1.. Adiccion y(x.) Presentado 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

Presentamos la tabla de valores a la hoja de MS Excel y construimos un calendario de puntos. La lista de trabajo tomará la vista representada en la FIG. 2.

Para calcular el valor del coeficiente de correlación en la celda. C6. Se introdujo la siguiente fórmula:

C7 \u003d Corvela (B3: J3; B2: J2).

Conociendo los coeficientes de regresión. peroy b. Calcular los valores de la función y=hACHA.+b. Para especificado x.. Para hacer esto, presentamos la fórmula.

B5 \u003d $ A $ 7 * B2 + $ B $ 7

y copiarlo en el rango C5: J5.(Fig. 5).

Como resultado de todas las transformaciones, obtenemos una gráfica de datos experimentales y 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 aproximantes en MS Excel se implementa como propiedades de la tabla. línea de tendencia.

Ejemplo 2.. Como resultado del experimento, se determinó alguna 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 construya la adicción aproximada. Construir gráficos tabular y seleccionada de dependencia analítica.

La resolución de problemas se puede dividir en los siguientes pasos: ingresar los datos de origen, construir un programa de punto y agregar una línea de tendencia a estos gráficos.

El cuadro de diálogo que aparece le permite construir una dependencia aproximada.

En la primera pestaña (Fig. 11) de esta ventana, se indica el tipo de dependencia de aproximación.

En la segunda (Fig. 12) define los parámetros de la construcción:

· El nombre de la dependencia aproximada;

· Pronóstico adelante (atrás) en nORTE. unidades (este parámetro determina para qué número de unidades por delante (ATRÁS) debe estar extendiendo la línea de tendencia);

· Muestra si el punto de intersección de la curva con una línea recta y \u003d const.;

· Muestre una función de aproximación en un diagrama o no (parámetro para mostrar la ecuación en el diagrama);

· ¿El diagrama de la desviación estándar o no (el parámetro se coloca en el diagrama del valor de la precisión de la aproximación)?

Del mismo modo con la ayuda líneas de tendencia Puede elegir los parámetros de tales dependencias como

· Lineal y=a ∙ X.+b.,

· Logarítmico y=a ∙ ln.(x.)+b.,

· Exponencial y=a ∙ e b,

· Energía y=a ∙ x b,

· Polinomial y=a ∙ X. 2 +b ∙ X.+c., y=a ∙ X. 3 +b ∙ X. 2 +c ∙ X + D Y así sucesivamente, al 6º grado polinomial inclusivo,

· Filtrado lineal.

4.3. Uso de la herramienta de análisis de opciones: búsqueda de soluciones.

El interés significativo es un interés significativo en MS Excel, seleccionando los parámetros de la dependencia funcional por los mínimos cuadrados utilizando la herramienta de análisis de opciones: solución de solución. Esta técnica le permite seleccionar los parámetros de la función de cualquier tipo. Considere esta función en el ejemplo de la siguiente tarea.

Ejemplo 3.. Como resultado del experimento, 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

Recoger los coeficientes de la adicción Z (t) \u003d a 4 + bt 3 + ct 2 + dt + k El método de mínimos cuadrados.

Esta tarea es equivalente a la tarea de encontrar una función mínima de cinco variables.

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

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

Copia esta fórmula en el rango. C4: J4. y obtenemos el valor esperado de la función en los puntos, cuyos absclaciones se almacenan en las células B2: J2..

En la celda. B5. Presentamos una fórmula que calcula el cuadrado de la diferencia entre los puntos experimentales y calculados:

B5 \u003d (B4-B3) ^ 2,

y copiarlo en el rango C5: J5.. En celda F7. Guardaremos un error cuadrático total (10). Para hacer esto, presentamos la fórmula:

F7 \u003d CANTIDADES (B5: J5).

Higo. 17 Muestra la lista de trabajo de MS Excel después de que se realicen los cálculos.