Donde se aplica el método de mínimos cuadrados. Matemáticas de la yema del dedo: función de mínimos cuadrados

La aproximación de datos experimentales es un método que se basa en reemplazar los datos obtenidos experimentalmente con una función analítica que pasa o coincide más de cerca en los puntos nodales con los valores iniciales (datos obtenidos durante el experimento o experimento). Actualmente hay dos formas de definir una función analítica:

Al construir un polinomio de interpolación de n grados que pasa directamente a través de todos los puntos una matriz de datos determinada. En este caso, la función de aproximación se representa en forma de polinomio de interpolación en forma de Lagrange o polinomio de interpolación en forma de Newton.

Al construir un polinomio aproximado de n grados que pasa muy cerca de los puntos de una matriz de datos determinada. Por lo tanto, la función de aproximación suaviza todos los ruidos aleatorios (o errores) que pueden surgir durante el experimento: los valores medidos durante el experimento dependen de factores aleatorios que fluctúan de acuerdo con sus propias leyes aleatorias (errores de medición o instrumentos, inexactitud o errores experimentales). errores). En este caso, la función de aproximación se determina utilizando el método de mínimos cuadrados.

Método de mínimos cuadrados(en la literatura en inglés Ordinary Least Squares, OLS) es un método matemático basado en la definición de una función aproximada, que se construye en la proximidad más cercana a los puntos de una matriz dada de datos experimentales. La cercanía de la función inicial y de aproximación F (x) se determina mediante una medida numérica, a saber: la suma de los cuadrados de las desviaciones de los datos experimentales de la curva de aproximación F (x) debe ser la más pequeña.

Curva de ajuste por mínimos cuadrados

Se utiliza el método de mínimos cuadrados:

Resolver sistemas de ecuaciones sobredeterminados cuando el número de ecuaciones excede el número de incógnitas;

Para buscar una solución en el caso de sistemas de ecuaciones no lineales ordinarios (no sobredeterminados);

Para aproximar los valores de los puntos mediante alguna función de aproximación.

La función de aproximación por el método de mínimos cuadrados se determina a partir de la condición de la suma mínima de cuadrados de desviaciones de la función de aproximación calculada de una matriz dada de datos experimentales. Este criterio para el método de mínimos cuadrados se escribe como la siguiente expresión:

Los valores de la función de aproximación calculada en los puntos nodales,

Una matriz dada de datos experimentales en los puntos nodales.

El criterio cuadrático tiene varias propiedades "buenas", como la diferenciabilidad, que proporciona una solución única al problema de aproximación con funciones de aproximación polinomiales.

Dependiendo de las condiciones del problema, la función de aproximación es un polinomio de grado m

El grado de la función de aproximación no depende del número de puntos nodales, pero su dimensión siempre debe ser menor que la dimensión (número de puntos) de una matriz dada de datos experimentales.

∙ Si el grado de la función de aproximación es m = 1, entonces aproximamos la función tabular con una línea recta (regresión lineal).

∙ Si el grado de la función de aproximación es m = 2, entonces aproximamos la función tabular con una parábola cuadrática (aproximación cuadrática).

∙ Si el grado de la función de aproximación es m = 3, entonces aproximamos la función tabular con una parábola cúbica (aproximación cúbica).

En el caso general, cuando se requiere construir un polinomio aproximado de grado m para valores tabulares dados, la condición para el mínimo de la suma de cuadrados de desviaciones para todos los puntos nodales se reescribe de la siguiente manera:

- coeficientes desconocidos del polinomio aproximado de grado m;

El número de valores de tabla especificados.

Una condición necesaria para la existencia de un mínimo de una función es la igualdad a cero de sus derivadas parciales con respecto a las variables desconocidas. ... Como resultado, obtenemos el siguiente sistema de ecuaciones:

Transformamos el sistema lineal de ecuaciones resultante: abrimos los corchetes y transferimos los términos libres al lado derecho de la expresión. Como resultado, el sistema resultante de expresiones algebraicas lineales se escribirá en la siguiente forma:

Este sistema de expresiones algebraicas lineales se puede reescribir en forma de matriz:

Como resultado, se obtuvo un sistema de ecuaciones lineales de dimensión m + 1, que consta de m + 1 incógnitas. Este sistema se puede resolver usando cualquier método para resolver ecuaciones algebraicas lineales (por ejemplo, el método de Gauss). Como resultado de la solución, se encontrarán parámetros desconocidos de la función de aproximación que proporcionen la suma mínima de cuadrados de las desviaciones de la función de aproximación de los datos iniciales, es decir, la mejor aproximación cuadrática posible. Debe recordarse que cuando cambia incluso un valor de los datos iniciales, todos los coeficientes cambiarán sus valores, ya que están completamente determinados por los datos iniciales.

Aproximación lineal de datos iniciales

(regresión lineal)

Como ejemplo, considere el método para determinar la función de aproximación, que se especifica como una relación lineal. De acuerdo con el método de mínimos cuadrados, la condición para la suma mínima de cuadrados de desviaciones se escribe de la siguiente forma:

Las coordenadas de los puntos de la cuadrícula de la tabla;

Coeficientes desconocidos de la función de aproximación, que se da como una relación lineal.

Una condición necesaria para la existencia de un mínimo de una función es la igualdad a cero de sus derivadas parciales con respecto a las variables desconocidas. Como resultado, obtenemos el siguiente sistema de ecuaciones:

Transformamos el sistema lineal de ecuaciones resultante.

Resolvemos el sistema resultante de ecuaciones lineales. Los coeficientes de la función de aproximación en forma analítica se determinan de la siguiente manera (método de Cramer):

Estos coeficientes proporcionan la construcción de una función de aproximación lineal de acuerdo con el criterio para minimizar la suma de cuadrados de la función de aproximación a partir de los valores de la tabla dados (datos experimentales).

Algoritmo para la implementación del método de mínimos cuadrados

1. Datos iniciales:

Se da una matriz de datos experimentales con el número de mediciones N

El grado del polinomio aproximado se da (m)

2. Algoritmo de cálculo:

2.1. Los coeficientes se determinan para construir un sistema de ecuaciones con la dimensión

Coeficientes del sistema de ecuaciones (lado izquierdo de la ecuación)

es el índice del número de columna de la matriz cuadrada del sistema de ecuaciones

Términos libres de un sistema de ecuaciones lineales (lado derecho de la ecuación)

es el índice del número de fila de la matriz cuadrada del sistema de ecuaciones

2.2. Formación de un sistema de ecuaciones lineales en dimensión.

2.3. Resolver un sistema de ecuaciones lineales para determinar los coeficientes desconocidos del polinomio aproximado de grado m.

2.4 Determinación de la suma de cuadrados de las desviaciones del polinomio aproximado de los valores originales para todos los puntos nodales

El valor encontrado de la suma de los cuadrados de las desviaciones es el mínimo posible.

Aproximación usando otras funciones

Cabe señalar que cuando se aproximan los datos iniciales de acuerdo con el método de mínimos cuadrados, a veces se utilizan una función logarítmica, una función exponencial y una función de potencia como función de aproximación.

Aproximación logarítmica

Considere el caso en el que la función de aproximación viene dada por una función logarítmica de la forma:

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

Supongamos que hay dos indicadores X e Y. E 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 ir inmediatamente para considerar un problema específico.

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

Se requiere hacer una previsión de la facturación (Y) que tendrá la tienda si tiene un espacio de venta 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.

Según las estadísticas matemáticas, los resultados serán más o menos correctos si se examinan los datos de al menos 5-6 objetos. Además, no 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 una función de aproximación y = f (x) con una gráfica que pase lo más cerca posible de los puntos M 1, M 2, .. M n.

Por supuesto, puede usar un polinomio de alto grado, pero esta opción no solo es difícil de implementar, sino simplemente incorrecta, ya que no reflejará la tendencia principal que debe detectarse. La solución más razonable es 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 la que tenga el valor más pequeño de la sum ei en todos los puntos considerados. Sin embargo, no todo es tan simple, ya que junto a las desviaciones positivas, las desviaciones negativas prácticamente estarán presentes.

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 implementa 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 describa 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 unos 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 la compra a crédito de una tienda de 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 el OLS 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:

  • el 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;
  • valores de x tanto conocidos como desconocidos, para los cuales debe averiguar el tamaño del volumen de negocios (consulte a continuación para obtener información sobre su ubicación en la hoja de trabajo).

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

El análisis de regresión puede incluso estar disponible para principiantes. 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 la ventana "TENDENCIA" no contiene un rango con x conocido, 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 dados. 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 ser el mismo 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

Está implementado con 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.

Slobodyanyuk A.I. El método de mínimos cuadrados en un experimento físico escolar // Física: problemas. presentado.- 1995. - Emisión. 1. - S. 88-99.

Hasta la fecha, se han desarrollado varios métodos para procesar los resultados de las mediciones. El más común y preciso es el método de mínimos cuadrados (MCO).

El artículo describe la esencia del método de mínimos cuadrados, las condiciones para su aplicabilidad. Los autores ofrecen ejemplos del uso del método OLS.

Como regla general, todos los experimentos físicos se reducen a medir la dependencia de una cierta cantidad tu de una o más otras cantidades z 1 , z 2 , …, z n.

La necesidad de obtener una dependencia (y no realizar una medición "puntual" en valores fijos de parámetros) se justifica por las siguientes ventajas:

  • la capacidad de verificar construcciones teóricas;
  • la capacidad de excluir parámetros difíciles de determinar;
  • en algunos casos, una forma más sencilla de estimar errores.

Hasta la fecha, se han desarrollado varios métodos para procesar los resultados de las mediciones. El más común, simple y razonable es el método de mínimos cuadrados (MCO).

1. La esencia del método de mínimos cuadrados, condiciones para su aplicabilidad

Supongamos que conocemos la forma de la dependencia funcional de la cantidad física tu de otra cantidad física z, pero los parámetros de esta dependencia no se conocen a, B, C, .... Como resultado de las mediciones se obtuvo una tabla de valores tu yo en algunos valores ... Es necesario encontrar dichos valores de parámetros. a, B, C, ... para lo cual la función describe mejor los datos experimentales.

OLS afirma que la "mejor" curva será aquella en la que la suma de las desviaciones cuadradas de los valores experimentales tu yo de los valores de la función es mínimo. Así, para determinar los parámetros a, B, C, ... es necesario encontrar el mínimo de la función

. (1)

Tenga en cuenta que Φ se considera aquí como una función de los parámetros a, B, C, ..., ya que las cantidades tu yo, z yo conocido a partir de datos experimentales.

En el caso general, está lejos de ser siempre posible encontrar el mínimo de la función (1). Por lo tanto, para la implementación práctica de OLS, a menudo se usa el siguiente método artificial: encuentran alguna transformación funcional , que lleva la dependencia investigada a una forma lineal

para lo cual la implementación del OLS es la más sencilla. En la tabla se dan ejemplos de transformaciones de este tipo. 1. Algunas transformaciones se analizarán a continuación con ejemplos específicos.

Sustituya la expresión (2) en la expresión (1)

(3)

y obtenemos las ecuaciones para determinar los parámetros a y B... Para ello, calculamos las derivadas de la función Φ con respecto a a y B e igualarlos a cero,

(4)

Este sistema es lineal y fácil de resolver:

(5)

Sin embargo, las expresiones obtenidas no son muy convenientes para cálculos prácticos, por lo que las reescribiremos en una forma ligeramente diferente. Por esto denotamos

(6)

(los paréntesis angulares significan la media aritmética de acuerdo con los datos experimentales) y escriba

(7)

A partir de la segunda ecuación del sistema (4), expresamos .

Las expresiones (6), (7) le permiten calcular rápidamente los parámetros de dependencia lineal (2) utilizando una calculadora no programable.

Formulemos las condiciones bajo las cuales los valores de los parámetros así obtenidos son óptimos (estimaciones insesgadas, consistentes, efectivas).

1. Los resultados de la medición son independientes.

2. Los errores de medición obedecen a una distribución normal.

3. Cantidades XI son conocidos con certeza.

En la práctica, el OLS en la forma indicada se utiliza si los errores de medición enI superan significativamente (más de un orden de magnitud) los errores de medición x yo.

Cuando se cumplen estas condiciones, los parámetros a, B expresado linealmente en términos de resultados de medición enI, (errores de medición x yo descuidado), por lo tanto, el error en la determinación de los parámetros puede ser encontrado por el método estándar como el error de medición indirecta. Cálculos algo engorrosos conducen a las siguientes fórmulas para estimaciones de error:

(8)

donde , el resto de la notación permanece igual:

(9)

Así, las fórmulas (6) - (9) agotan completamente el LSM para el análisis de dependencia lineal. Las fórmulas (7) - (8) dan estimaciones de solo errores de medición aleatorios. Su uso está plenamente justificado si prevalece este tipo de error, que suele ser el caso en la práctica. Este predominio se evidencia por la notable dispersión de puntos ( enI, XI) en el gráfico, cuando estos puntos no se encuentran exactamente en una línea recta. Tenga en cuenta que el error instrumental sistemático constante no afecta la determinación del parámetro a y es una adición aditiva al error de parámetro B, es decir. si el error de medición instrumental enI es igual, entonces .

Tenga en cuenta también que en algunos casos es necesario realizar varias mediciones de la cantidad tu con el mismo valor z... En este caso, no se requieren modificaciones de OLS. Basta considerar estos valores como independientes, es decir incluir pares en los cálculos z yo, tu yo Con los mismos valores z yo... En otras palabras, un valor z varios valores pueden coincidir tu... Naturalmente, no puede haber todo z lo mismo, de lo contrario en la fórmula (5) el denominador será cero.

2. Implementación práctica de OLS para dependencia lineal de una calculadora no programable

La experiencia muestra que es mejor utilizar un formulario preparado previamente para calcular los parámetros de dependencia lineal y sus errores (Tabla 2). La columna 1 registra el número de mediciones realizadas ( I = 1, 2, ..., Ν ); en las columnas 2, 3 - resultados de mediciones de cantidades z yo, tu yo.

El primer paso para utilizar este formulario para la implementación de MCO es completar las columnas 4, 5. Presentan los resultados de las transformaciones de z, tu a los valores X, en, entre los que se busca una relación lineal.

Las fórmulas de cálculo que se muestran en la columna 6 permiten realizar cálculos en la calculadora sin registrar resultados intermedios. Cualquiera, incluso la calculadora más simple, tiene una celda de memoria en la que se pueden acumular los valores de las sumas. Los cálculos deben realizarse en la siguiente secuencia:

1) calcular: para esto, ingrese secuencialmente en la memoria todos los valores XI escrito en la columna 4, y luego dividir el contenido por el número de pares de medidas NORTE, registre el resultado en la columna 7;

2) calcular escribiendo valores secuencialmente x yo, acumular la suma de sus cuadrados en la memoria (escriba los valores - "multiplicar" - "igual" - "en la memoria +") y dividir por norte, del resultado obtenido, reste el cuadrado del promedio, escriba el resultado en la columna 7;

3 - 4) calcular de forma similar y;

5) acumular la suma de trabajos en la memoria, dividir por norte, reste el producto de los promedios y divida por - obtenga el valor del parámetro a.

Los cálculos adicionales son bastante obvios.

3. Un ejemplo de uso de OLS

Tarea... Mide la aceleración debida a la gravedad usando un péndulo matemático.

Equipo: hilo, peso, trípode, regla, cronómetro.

Solución... Periodo de pequeñas oscilaciones de un péndulo matemático T está determinada por la fórmula. Esta fórmula se puede convertir en forma.

En otras palabras, entre la longitud del péndulo l y el cuadrado del período existe una relación lineal, que escribiremos en la forma :, donde (transformación a forma lineal). Introducción de parámetros B en este caso es opcional, ya que teóricamente B= 0. Sin embargo, escribir la dependencia lineal en forma general le permite tener en cuenta automáticamente el error al determinar la longitud del péndulo, además, en este caso, es posible medir no la longitud del péndulo, sino solo su cambio. Si todas las mediciones se llevan a cabo correctamente, entonces el LSM debería dar lugar a un resultado, que lo indicará.

Los resultados de las mediciones del cambio en la longitud del péndulo Δ l(se midió la distancia desde el punto de suspensión a algún punto fijo en el hilo) y el tiempo t En la tabla se dan veinte fluctuaciones (medidas con un reloj de pulsera). 3. También se presentan allí los resultados de los cálculos según el método indicado.

Calcular el coeficiente a, puedes encontrar el valor de la aceleración de la gravedad y su error .

Resultado final Sra.

Valor de parámetro B no se utilizó (el significado del valor obtenido es la distancia desde un punto fijo en la rosca hasta el centro de masa de la carga). El uso de este parámetro se justifica por la dificultad de determinar con precisión la posición del centro de gravedad.

4. Problemas experimentales relacionados con el uso de OLS

En conclusión, proponemos varios problemas experimentales, para cuya solución se debe utilizar el método descrito. Cada tarea se proporciona con breves instrucciones para resolver. Dado que en cada caso las fórmulas para las estimaciones de error son obvias, no se presentan aquí.

Problema 1... El período de oscilación de un péndulo matemático depende de la amplitud j 0 (en radianes) según la ley

(10)

Determine el valor del parámetro β.

Equipo: hilo, peso, trípode, transportador, cronómetro electrónico.

Instrucciones de solución... La dependencia del período de oscilación de la amplitud es bastante débil. Para detectarlo, es necesario tomar medidas con alta precisión (–0,01 s), lo que requiere un cronómetro electrónico.

La dependencia (10) se presenta en la forma, donde y =T,B = T 0. Usando el método de mínimos cuadrados para una dependencia lineal, puede encontrar los valores de los parámetros a y B, entonces el coeficiente deseado será determinado por la fórmula (tenga en cuenta que el valor teórico).

Tarea 2... Determine la distancia focal de la lente recolectora.

Equipo: fuente de luz, pantalla, lente, regla.

Instrucciones de solución... Usemos la fórmula de lentes finas

donde D- la distancia del objeto a la lente, F- distancia del objetivo a la imagen, F Es la distancia focal del objetivo.

Denotemos, entonces. Si mide varios pares de valores DI y f yo y trazar los puntos , entonces estos puntos deben estar en una línea recta que corta en los ejes X, en segmentos numéricamente iguales. Si procesa esta dependencia por OLS, puede obtener y luego encontrar.

Problema 3... El enfriamiento del agua se describe mediante la fórmula, donde Δ T–Diferencia entre la temperatura del agua y del aire en la habitación, Δ T 0 - la misma diferencia en el momento del tiempo t= 0. Determina cuánto tiempo ha pasado desde el punto de ebullición del agua.

Equipo: agua caliente en un recipiente, termómetro, reloj.

Instrucciones de solución... Es necesario hervir el agua con anticipación y ponerla a enfriar. Después de algún tiempo, este recipiente se puede proporcionar para la tarea. Debe tenerse en cuenta que el tiempo de enfriamiento de un vaso de agua en condiciones ambientales es de unos 40 minutos.

Para resolver el problema, es necesario medir la dependencia de la temperatura del agua. T de vez t... A continuación, reescribimos la fórmula anterior en la forma, donde T 0 - temperatura ambiente, T kip es el punto de ebullición del agua, t 0 - tiempo transcurrido desde la ebullición hasta el inicio de la medición. Desde en. la fórmula incluye solo diferencias de temperatura, entonces puede usar la escala Celsius. Tomemos el logaritmo de la última expresión.

(12)

y denotar , X= t, obtenemos una dependencia lineal

Al procesar los resultados de las mediciones por el método de mínimos cuadrados, encontramos los valores de los parámetros a, B, a partir del cual puede calcular el valor de tiempo requerido t 0: .

Problema 4... Explore cómo la fuerza de la resistencia del aire que actúa sobre los trozos de papel que caen depende de la velocidad de estos últimos.

Equipo: trozos de papel, cronómetro.

Instrucciones de solución... Los pedazos de papel deben hacerse cuadrados (aproximadamente cm) y ligeramente doblados en forma de "paracaídas" para que sus caídas sean estables. Los platos desechables hechos de papel grueso o papel de aluminio son excelentes para el mismo propósito.

La caída de platos de papel (o paracaídas) se produce a velocidad constante, si descuidamos la pequeña etapa inicial de aceleración. La fuerza de la resistencia del aire depende de la velocidad u según la ley

(se requiere para determinar γ), con movimiento estable esta fuerza es numéricamente igual a la fuerza de la gravedad, por lo tanto, la velocidad del movimiento estable y el tiempo de caída desde una altura h:

(14)

Tome varias (1, 2, 3, ..., 5) placas idénticas y mida el tiempo de caída t n apilados juntos norte platos. Coeficiente Con en la fórmula (13) será la misma (depende solo de la forma de la placa), mientras que la masa de los cuerpos que caen, donde metro 0 es la masa de un plato. Usamos (14): , en forma logarítmica

(15)

Como se desprende de esta fórmula, existe una relación lineal entre y, donde, en B incluye todas las demás constantes, que no necesitan ser medidas.

Por tanto, habiendo medido la dependencia del tiempo de caída t n, del número sumado norte placas pequeñas y construcción de dependencia (15), utilizando el método de mínimos cuadrados, puede encontrar el valor del parámetro a y el valor requerido.

Al realizar el experimento hay que tener en cuenta que el tiempo que tarda un trozo de papel cm en caer desde una altura es de aproximadamente 1,5 s, por lo que es necesario medir el tiempo de caída con un error del orden de 0,1 s. Por lo tanto, para cada valor del número norte necesita obtener varios valores tnorte... Enfatizamos que en esta situación no es necesario calcular preliminarmente los valores promedio, es posible (y necesario) considerar todos los resultados de medición como independientes e incluirlos en el formulario de cálculo.

Otro problema de este tipo se comenta en detalle en la revista Focus.

5. Conclusión

El algoritmo considerado de los cálculos de OLS se probó en el campamento de verano "Zubrenok". Las lecciones impartidas con los ganadores de las Olimpiadas demostraron que este método es bastante accesible para los escolares de último año con un estudio en profundidad de la física. Después de adquirir la habilidad de trabajar con una microcalculadora, los cálculos toman aproximadamente de 5 a 10 minutos.

La necesidad de estudiar métodos de procesamiento gráfico de resultados (según MHK u otros) se justifica por la participación de los equipos de la república en competencias internacionales (Olimpiadas, torneos para jóvenes físicos), donde los métodos gráficos ocupan un lugar dominante y son muy apreciados.

1. Taylor J. Introducción a la teoría de errores. - M: Mir, 1985.

2. Bolshev L.N., Smirnov N.V. Tablas de estadística matemática. - M .: Nauka, 1983.

3. Timofeev A. .. ¿Vamos a comprobar Stokes? - Enfocar. - 1995. - No. 2. - S. 44-49.

Reducción de linealidad

Tipo de dependencia

Transformación

Parámetros

Formulario para calcular los parámetros de dependencia lineal.

I

z

tu

X

y

Fórmulas de cálculo

resultados

Definición de parámetros de dependencia
el período de oscilación del péndulo en su longitud

Δl,

Fórmulas de cálculo

resultados

Habiendo elegido el tipo de función de regresión, es decir el tipo del modelo considerado de dependencia de Y sobre X (o X sobre Y), por ejemplo, un modelo lineal y x = a + bx, es necesario determinar los valores específicos de los coeficientes del modelo.

Para diferentes valores de ayb, se puede construir un número infinito de dependencias de la forma yx = a + bx, es decir, hay un número infinito de líneas rectas en el plano de coordenadas, pero necesitamos tal dependencia que corresponde a los valores observados de la mejor manera. Así, la tarea se reduce a la selección de los mejores coeficientes.

Buscamos la función lineal a + bx, partiendo solo de un cierto número de observaciones disponibles. Para encontrar la función con el mejor ajuste a los valores observados, usamos el método de mínimos cuadrados.

Denote: Y i - el valor calculado de acuerdo con la ecuación Y i = a + bx i. y i es el valor medido, ε i = y i -Y i es la diferencia entre los valores medidos y calculados de acuerdo con la ecuación, ε i = y i -a-bx i.

El método de mínimos cuadrados requiere que ε i, la diferencia entre los valores y i medidos y los valores Y i calculados, sea mínima. Por lo tanto, encontramos los coeficientes ayb de modo que la suma de los cuadrados de las desviaciones de los valores observados de los valores en la línea recta de la regresión sea la más pequeña:

Explorando esta función de argumentos ay para el extremo con la ayuda de derivadas, se puede probar que la función toma un valor mínimo si los coeficientes ayb son soluciones del sistema:

(2)

Si dividimos ambos lados de las ecuaciones normales por n, obtenemos:

Teniendo en cuenta que (3)

Obtenemos , a partir de aquí, sustituyendo el valor de a en la primera ecuación, obtenemos:

En este caso, b se denomina coeficiente de regresión; a se denomina término libre de la ecuación de regresión y se calcula mediante la fórmula:

La línea recta resultante es una estimación de la línea de regresión teórica. Tenemos:

Entonces, es una ecuación de regresión lineal.

La regresión puede ser directa (b> 0) e inversa (b Ejemplo 1. Los resultados de medir los valores X e Y se dan en la tabla:

x yo -2 0 1 2 4
y yo 0.5 1 1.5 2 3

Suponiendo que existe una relación lineal y = a + bx entre X e Y, determine los coeficientes ayb usando el método de mínimos cuadrados.

Solución. Aquí n = 5
x i = -2 + 0 + 1 + 2 + 4 = 5;
x yo 2 = 4 + 0 + 1 + 4 + 16 = 25
x yo y yo = -2 0,5 + 0 1 + 1 1,5 + 2 2 + 4 3 = 16,5
y yo = 0.5 + 1 + 1.5 + 2 + 3 = 8

y el sistema normal (2) tiene la forma

Resolviendo este sistema, obtenemos: b = 0.425, a = 1.175. Por lo tanto, y = 1,175 + 0,425x.

Ejemplo 2. Hay una muestra de 10 observaciones de indicadores económicos (X) e (Y).

x yo 180 172 173 169 175 170 179 170 167 174
y yo 186 180 176 171 182 166 182 172 169 177

Es necesario encontrar la ecuación de regresión de muestra Y-X. Trace la línea de regresión de muestra Y-X.

Solución. 1. Ordenemos los datos por los valores x i y y i. Obtenemos una nueva tabla:

x yo 167 169 170 170 172 173 174 175 179 180
y yo 169 171 166 172 180 176 177 182 182 186

Para simplificar los cálculos, elaboraremos una tabla de cálculo en la que ingresaremos los valores numéricos necesarios.

x yo y yo x yo 2 x yo y yo
167 169 27889 28223
169 171 28561 28899
170 166 28900 28220
170 172 28900 29240
172 180 29584 30960
173 176 29929 30448
174 177 30276 30798
175 182 30625 31850
179 182 32041 32578
180 186 32400 33480
∑x i = 1729 ∑y i = 1761 ∑x i 2 299105 ∑x yo y yo = 304696
x = 172,9 y = 176,1 x yo 2 = 29910,5 xy = 30469,6

Según la fórmula (4), calculamos el coeficiente de regresión

y según la fórmula (5)

Por tanto, la ecuación de regresión muestral es y = -59,34 + 1,3804x.
Dibujemos puntos (x i; y i) en el plano de coordenadas y marquemos la línea de regresión.


Figura 4

La Figura 4 muestra cómo se ubican los valores observados en relación con la línea de regresión. Para una evaluación numérica de las desviaciones de y i de Y i, donde se observan y i, y Y i son los valores determinados por la regresión, elaboramos una tabla:

x yo y yo Y yo Y yo -y yo
167 169 168.055 -0.945
169 171 170.778 -0.222
170 166 172.140 6.140
170 172 172.140 0.140
172 180 174.863 -5.137
173 176 176.225 0.225
174 177 177.587 0.587
175 182 178.949 -3.051
179 182 184.395 2.395
180 186 185.757 -0.243

Los valores de Y i se calculan de acuerdo con la ecuación de regresión.

La marcada desviación de algunos de los valores observados de la línea de regresión se explica por el pequeño número de observaciones. Al estudiar el grado de dependencia lineal de Y sobre X, se tiene en cuenta el número de observaciones. La fuerza de la dependencia está determinada por el valor del coeficiente de correlación.

Ejemplo.

Datos experimentales sobre los valores de las variables. X y en se dan en la tabla.

Como resultado de su alineación, se obtiene la función

Utilizando método de mínimos cuadrados, aproxima estos datos con una dependencia lineal y = ax + b(buscar parámetros a y B). Descubra cuál de las dos líneas es mejor (en el sentido del método de mínimos cuadrados) iguala los datos experimentales. Haz un dibujo.

La esencia del método de mínimos cuadrados (mns).

La tarea es encontrar los coeficientes de dependencia lineal para los cuales la función de dos variables a y B toma el valor más pequeño. Es decir, dado a y B la suma de los cuadrados de las desviaciones de los datos experimentales de la línea recta encontrada será la más pequeña. Este es el punto central del método de mínimos cuadrados.

Por tanto, la solución del ejemplo se reduce a encontrar el extremo de una función de dos variables.

Derivación de fórmulas para encontrar coeficientes.

Se compone y resuelve un sistema de dos ecuaciones con dos incógnitas. Encuentra las derivadas parciales de la función por variables a y B, equiparamos estas derivadas a cero.

Resolvemos el sistema de ecuaciones resultante por cualquier método (por ejemplo método de sustitución o El método de Cramer) y obtener fórmulas para encontrar los coeficientes utilizando el método de mínimos cuadrados (MCO).

Con datos a y B función toma el valor más pequeño. La prueba de este hecho se da a continuación en el texto al final de la página.

Ese es el método de mínimos cuadrados completos. Fórmula para encontrar el parámetro a contiene las sumas ,,, y el parámetro norte- la cantidad de datos experimentales. Recomendamos calcular los valores de estos importes por separado. Coeficiente B es después del cálculo a.

Es hora de recordar el ejemplo original.

Solución.

En nuestro ejemplo n = 5... Completamos la tabla para la conveniencia de calcular las cantidades que se incluyen en las fórmulas de los coeficientes deseados.

Los valores de la cuarta fila de la tabla se obtienen multiplicando los valores de la segunda fila por los valores de la tercera fila para cada número. I.

Los valores de la quinta fila de la tabla se obtienen elevando al cuadrado los valores de la segunda fila para cada número I.

Los valores de la última columna de la tabla son las sumas de los valores por fila.

Usamos las fórmulas del método de mínimos cuadrados para encontrar los coeficientes a y B... Sustituimos en ellos los valores correspondientes de la última columna de la tabla:

Por eso, y = 0,165x + 2,184- la recta aproximada requerida.

Queda por saber cuál de las líneas y = 0,165x + 2,184 o aproxima mejor los datos originales, es decir, realiza una estimación utilizando el método de mínimos cuadrados.

Estimación del error del método de mínimos cuadrados.

Para hacer esto, necesita calcular la suma de los cuadrados de las desviaciones de los datos iniciales de estas líneas y , el valor más bajo corresponde a la línea que mejor se aproxima a los datos originales en el sentido del método de mínimos cuadrados.

Desde entonces directamente y = 0,165x + 2,184 se aproxima mejor a los datos originales.

Ilustración gráfica del método de mínimos cuadrados (mns).

Todo es perfectamente visible en los gráficos. La línea roja es la línea recta encontrada. y = 0,165x + 2,184, la línea azul es , los puntos rosados ​​son datos sin procesar.

En la práctica, al modelar varios procesos, en particular, económico, físico, técnico, social, se usa ampliamente uno u otro método para calcular los valores aproximados de funciones a partir de sus valores conocidos en algunos puntos fijos.

Tales problemas de aproximación de funciones surgen a menudo:

    al construir fórmulas aproximadas para calcular los valores de los valores característicos del proceso en estudio de acuerdo con los datos tabulares obtenidos como resultado del experimento;

    para integración numérica, diferenciación, resolución de ecuaciones diferenciales, etc.;

    cuando es necesario calcular los valores de funciones en puntos intermedios del intervalo considerado;

    al determinar los valores de las magnitudes características del proceso fuera del intervalo considerado, en particular al predecir.

Si, para modelar un determinado proceso dado por la tabla, construye una función que describa aproximadamente este proceso con base en el método de mínimos cuadrados, se le llamará función de aproximación (regresión), y el problema de construir funciones de aproximación en sí mismo es un problema de aproximación .

Este artículo analiza las capacidades del paquete MS Excel para resolver tales problemas, además, se proporcionan métodos y técnicas para construir (crear) regresiones para funciones definidas por tablas (que es la base del análisis de regresión).

Excel tiene dos opciones para trazar regresiones.

    Agregar las regresiones seleccionadas (líneas de tendencia) al diagrama basado en la tabla de datos para la característica del proceso estudiado (disponible solo si hay un diagrama construido);

    Utilice las funciones estadísticas integradas de la hoja de cálculo de Excel para obtener regresiones (líneas de tendencia) directamente de la tabla de datos sin procesar.

Agregar líneas de tendencia a un gráfico

Para una tabla de datos que describe un determinado proceso y representada por un diagrama, Excel tiene una herramienta de análisis de regresión eficaz que le permite:

    construir sobre la base del método de mínimos cuadrados y agregar cinco tipos de regresiones al diagrama, que modelan el proceso en estudio con diversos grados de precisión;

    agregue la ecuación de la regresión construida al diagrama;

    determinar el grado en que la regresión seleccionada coincide con los datos que se muestran en el gráfico.

Con base en los datos del gráfico de Excel, le permite obtener tipos de regresiones lineales, polinomiales, logarítmicas, de potencia, exponenciales, que vienen dadas por la ecuación:

y = y (x)

donde x es una variable independiente, que a menudo toma los valores de una secuencia de números naturales (1; 2; 3; ...) y produce, por ejemplo, contar el tiempo del proceso en estudio (características).

1 ... La regresión lineal es buena para modelar características que aumentan o disminuyen a una tasa constante. Este es el modelo más simple del proceso que se está estudiando para construir. Se construye de acuerdo con la ecuación:

y = mx + b

donde m es la tangente del ángulo de inclinación de la regresión lineal al eje de abscisas; b - coordenada del punto de intersección de la regresión lineal con el eje de ordenadas.

2 ... La línea de tendencia polinomial es útil para describir características que tienen varios extremos distintos (máximos y mínimos). La elección del grado del polinomio está determinada por el número de extremos de la característica estudiada. Así, un polinomio de segundo grado puede describir bien un proceso que tiene solo un máximo o mínimo; polinomio de tercer grado: no más de dos extremos; polinomio del cuarto grado: no más de tres extremos, etc.

En este caso, la línea de tendencia se traza de acuerdo con la ecuación:

y = c0 + c1x + c2x2 + c3x3 + c4x4 + c5x5 + c6x6

donde los coeficientes c0, c1, c2, ... c6 son constantes, cuyos valores se determinan durante la construcción.

3 ... La línea de tendencia logarítmica se utiliza con éxito para simular características, cuyos valores cambian rápidamente al principio y luego se estabilizan gradualmente.

y = c ln (x) + b

4 ... Una línea de tendencia de ley de potencia da buenos resultados si los valores de la dependencia estudiada se caracterizan por un cambio constante en la tasa de crecimiento. Un ejemplo de tal relación es un gráfico de movimiento uniformemente acelerado de un automóvil. Si los datos contienen valores cero o negativos, no puede utilizar una línea de tendencia de potencia.

Está construido de acuerdo con la ecuación:

y = c xb

donde los coeficientes b, c son constantes.

5 ... Se debe utilizar una línea de tendencia exponencial cuando la tasa de cambio en los datos aumenta continuamente. Para los datos que contienen valores cero o negativos, este tipo de aproximación tampoco es aplicable.

Está construido de acuerdo con la ecuación:

y = c ebx

donde los coeficientes b, c son constantes.

Al seleccionar una línea de tendencia, Excel calcula automáticamente el valor de R2, que caracteriza la precisión de la aproximación: cuanto más cercano está el valor de R2 a uno, más confiablemente se aproxima la línea de tendencia al proceso en estudio. Si es necesario, el valor R2 siempre se puede mostrar en el gráfico.

Determinado por la fórmula:

Para agregar una línea de tendencia a una serie de datos:

    activar un gráfico basado en una serie de datos, es decir, hacer clic dentro del área del gráfico. El elemento Gráfico aparecerá en el menú principal;

    después de hacer clic en este elemento, aparecerá un menú en la pantalla, en el que debe seleccionar el comando Agregar línea de tendencia.

Las mismas acciones se realizan fácilmente al pasar el puntero del mouse sobre el gráfico correspondiente a una de las series de datos y hacer clic con el botón derecho del mouse; En el menú contextual que aparece, seleccione el comando Agregar línea de tendencia. El cuadro de diálogo Trendline con la pestaña Type expandida (Fig. 1) aparecerá en la pantalla.

Después de eso es necesario:

Seleccione el tipo de línea de tendencia requerido en la pestaña Tipo (por defecto, el tipo Lineal está seleccionado). Para el tipo Polinomio, en el campo Grado, especifique el grado del polinomio seleccionado.

1 ... El cuadro Trazado en serie enumera todas las series de datos del gráfico en cuestión. Para agregar una línea de tendencia a una serie de datos específica, seleccione su nombre en el campo Trazado en serie.

Si es necesario, yendo a la pestaña Parámetros (Fig.2), puede configurar los siguientes parámetros para la línea de tendencia:

    cambie el nombre de la línea de tendencia en el campo Nombre de la curva aproximada (suavizada).

    establezca el número de períodos (hacia adelante o hacia atrás) para el pronóstico en el campo Pronóstico;

    mostrar la ecuación de la línea de tendencia en el área del diagrama, para lo cual debe habilitar la casilla de verificación Mostrar ecuación en el diagrama;

    mostrar el valor de la confiabilidad de aproximación R2 en el área del diagrama, para lo cual debe habilitar la casilla de verificación para colocar el valor de confiabilidad de aproximación (R ^ 2) en el diagrama;

    establecer el punto de intersección de la línea de tendencia con el eje Y, para lo cual debe habilitar la casilla de verificación de intersección de la curva con el eje Y en un punto;

    haga clic en el botón Aceptar para cerrar el cuadro de diálogo.

Para comenzar a editar una línea de tendencia ya construida, hay tres formas:

    use el comando Línea de tendencia seleccionada del menú Formato después de seleccionar la línea de tendencia;

    seleccione el comando Formato de línea de tendencia del menú contextual, que se invoca al hacer clic con el botón derecho en la línea de tendencia;

    haciendo doble clic en la línea de tendencia.

El cuadro de diálogo Formato de línea de tendencia (Fig.3) aparecerá en la pantalla, que contiene tres pestañas: Vista, Tipo, Parámetros, y el contenido de las dos últimas coincide completamente con pestañas similares en el cuadro de diálogo Línea de tendencia (Fig.1-2) . En la pestaña Ver, puede establecer el tipo de línea, su color y grosor.

Para eliminar una línea de tendencia ya construida, seleccione la línea de tendencia eliminada y presione la tecla Eliminar.

Las ventajas de la herramienta de análisis de regresión considerada son:

    la relativa facilidad de trazar una línea de tendencia en gráficos sin crear una tabla de datos para ello;

    una lista bastante amplia de tipos de líneas de tendencia propuestas, y esta lista incluye los tipos de regresión más utilizados;

    la capacidad de predecir el comportamiento del proceso en estudio para un número arbitrario (dentro del sentido común) de pasos hacia adelante y hacia atrás;

    la capacidad de obtener la ecuación de la línea de tendencia en forma analítica;

    la posibilidad, si es necesario, de obtener una estimación de la fiabilidad de la aproximación realizada.

Las desventajas incluyen los siguientes puntos:

    la construcción de una línea de tendencia se lleva a cabo solo si hay un diagrama construido sobre una serie de datos;

    el proceso de formar series de datos para la característica estudiada basado en las ecuaciones de la línea de tendencia obtenidas para ella es algo desordenado: las ecuaciones de regresión buscadas se actualizan con cada cambio en los valores de la serie de datos original, pero solo dentro del área del diagrama, mientras que la serie de datos formada sobre la base de la tendencia de la ecuación de línea antigua permanece sin cambios;

    En los informes de gráfico dinámico, cuando cambia la vista de un gráfico o un informe de tabla dinámica vinculado, las líneas de tendencia existentes no se conservan, es decir, debe asegurarse de que el diseño del informe cumpla con sus requisitos antes de dibujar líneas de tendencia o formatear el informe de gráfico dinámico.

Las líneas de tendencia se pueden utilizar para complementar las series de datos presentadas en gráficos como gráficos, barras, gráficos de áreas planas no normalizadas, gráficos de barras, de dispersión, de burbujas y de valores.

No puede agregar líneas de tendencia a series de datos en gráficos 3D, normalizados, de radar, circulares y de anillos.

Usar funciones integradas de Excel

Excel también proporciona una herramienta de análisis de regresión para trazar líneas de tendencia fuera del área del gráfico. Se pueden usar varias funciones estadísticas de la hoja de trabajo para este propósito, pero todas permiten construir solo regresiones lineales o exponenciales.

Excel proporciona varias funciones para construir regresiones lineales, en particular:

    TENDENCIA;

  • INCLINACIÓN e INTERCEPCIÓN.

Y también varias funciones para construir una línea de tendencia exponencial, en particular:

    LGRFPRIBL.

Cabe señalar que los métodos de construcción de regresiones utilizando las funciones TENDENCIA y CRECIMIENTO prácticamente coinciden. Lo mismo puede decirse de un par de funciones LINEST y LGRFPRIBL. Para estas cuatro funciones, las características de Excel, como las fórmulas de matriz, se utilizan para crear una tabla de valores, lo que hace que el proceso de regresión sea algo desordenado. Tenga en cuenta también que la construcción de la regresión lineal, en nuestra opinión, es más fácil de realizar utilizando las funciones PENDIENTE e INTERCEPCIÓN, donde la primera de ellas determina la pendiente de la regresión lineal, y la segunda es el segmento cortado por la regresión en el eje de ordenadas.

Los beneficios de la herramienta de análisis de regresión incorporada incluyen:

    un proceso bastante simple del mismo tipo de formación de series de datos de la característica estudiada para todas las funciones estadísticas integradas que establecen líneas de tendencia;

    técnica estándar para construir líneas de tendencia basadas en series de datos generadas;

    la capacidad de predecir el comportamiento del proceso en estudio para el número requerido de pasos hacia adelante o hacia atrás.

La desventaja es que Excel no tiene funciones integradas para crear otros tipos de líneas de tendencia (además de lineales y exponenciales). Esta circunstancia muchas veces no permite elegir un modelo suficientemente preciso del proceso en estudio, así como obtener previsiones cercanas a la realidad. Además, cuando se utilizan las funciones TENDENCIA y CRECIMIENTO, no se conocen las ecuaciones de la línea de tendencia.

Cabe señalar que los autores no establecieron el objetivo del artículo de presentar el curso del análisis de regresión con diversos grados de exhaustividad. Su tarea principal es mostrar las capacidades del paquete Excel para resolver problemas de aproximación utilizando ejemplos específicos; demostrar qué herramientas efectivas tiene Excel para construir regresiones y pronósticos; ilustrar la relativa facilidad con la que estos problemas pueden ser resueltos incluso por un usuario que no tiene un conocimiento profundo del análisis de regresión.

Ejemplos de resolución de problemas específicos.

Consideremos la solución de tareas específicas utilizando las herramientas enumeradas del paquete de Excel.

Problema 1

Con una tabla de datos sobre las ganancias de una empresa de camiones para 1995-2002. debe hacer lo siguiente.

    Construye un diagrama.

    Agregue líneas de tendencia lineales y polinomiales (cuadráticas y cúbicas) al gráfico.

    Utilizando las ecuaciones de la línea de tendencia, obtenga datos tabulares sobre las ganancias empresariales para cada línea de tendencia para 1995-2004.

    Haga una previsión de los beneficios de la empresa para 2003 y 2004.

La solucion del problema

    En el rango de celdas A4: C11 de la hoja de cálculo de Excel, ingrese la hoja de trabajo que se muestra en la Fig. 4.

    Habiendo seleccionado el rango de celdas B4: C11, construimos un diagrama.

    Activamos el gráfico construido y, de acuerdo con el método descrito anteriormente, después de seleccionar el tipo de línea de tendencia en el cuadro de diálogo Trendline (ver Fig. 1), agregamos alternativamente líneas de tendencia lineales, cuadráticas y cúbicas al gráfico. En el mismo cuadro de diálogo, abra la pestaña Parámetros (ver Fig.2), en el campo Nombre de la curva aproximada (suavizada), ingrese el nombre de la tendencia agregada, y en el campo Previsión para: períodos, establezca el valor 2 , ya que se prevé realizar una previsión de beneficios a dos años. Para mostrar la ecuación de regresión y el valor de confiabilidad de aproximación R2 en el área del diagrama, active las casillas de verificación para mostrar la ecuación en la pantalla y coloque el valor de confiabilidad de aproximación (R ^ 2) en el diagrama. Para una mejor percepción visual, cambie el tipo, color y grosor de las líneas de tendencia construidas, para lo cual usamos la pestaña Ver del cuadro de diálogo Formato de línea de tendencia (ver Fig. 3). El diagrama resultante con líneas de tendencia agregadas se muestra en la Fig. 5.

    Obtener datos tabulares sobre el beneficio de la empresa para cada línea de tendencia para 1995-2004. Usemos las ecuaciones de la línea de tendencia que se muestran en la Fig. 5. Para hacer esto, en las celdas del rango D3: F3, ingrese información de texto sobre el tipo de línea de tendencia seleccionada: tendencia lineal, tendencia cuadrática, tendencia cúbica. Luego, ingrese la fórmula de regresión lineal en la celda D4 y, usando el marcador de relleno, copie esta fórmula con referencias relativas al rango de celdas D5: D13. Cabe señalar que cada celda con una fórmula de regresión lineal del rango de celdas D4: D13 toma la celda correspondiente del rango A4: A13 como argumento. De manera similar, para la regresión cuadrática, el rango de celdas E4: E13 se llena, y para la regresión cúbica, el rango de celdas F4: F13. Por lo tanto, se realizó la previsión de beneficios de la empresa para 2003 y 2004. utilizando tres tendencias. La tabla de valores resultante se muestra en la Fig. 6.

Tarea 2

    Construye un diagrama.

    Agregue líneas de tendencia logarítmicas, exponenciales y exponenciales al gráfico.

    Derivar las ecuaciones de las líneas de tendencia obtenidas, así como los valores de la confiabilidad de aproximación R2 para cada una de ellas.

    Utilizando las ecuaciones de la línea de tendencia, obtenga datos tabulares sobre las ganancias empresariales para cada línea de tendencia para 1995-2002.

    Haga una previsión de los beneficios de la empresa para 2003 y 2004 utilizando estas líneas de tendencia.

La solucion del problema

Siguiendo la metodología dada en la resolución del problema 1, obtenemos un diagrama con líneas de tendencia logarítmicas, de potencia y exponenciales agregadas (Fig. 7). Además, utilizando las ecuaciones obtenidas de las líneas de tendencia, completamos la tabla de valores para la ganancia de la empresa, incluidos los valores predichos para 2003 y 2004. (figura 8).

En la Fig. 5 y fig. se puede observar que el modelo con tendencia logarítmica corresponde al menor valor de la confiabilidad de aproximación

R2 = 0,8659

Los mayores valores de R2 corresponden a modelos con tendencia polinomial: cuadrática (R2 = 0.9263) y cúbica (R2 = 0.933).

Problema 3

Con la tabla de datos sobre la utilidad de una empresa de camiones para 1995-2002, que se presenta en la tarea 1, debe realizar los siguientes pasos.

    Obtenga series de datos para líneas de tendencia lineales y exponenciales utilizando las funciones TENDENCIA y CRECIMIENTO.

    Utilizando las funciones TENDENCIA y CRECIMIENTO, haga un pronóstico de las ganancias de la empresa para 2003 y 2004.

    Construya un diagrama para los datos iniciales y la serie de datos resultante.

La solucion del problema

Usemos la hoja de trabajo de la tarea 1 (ver Fig. 4). Comencemos con la función TENDENCIA:

    seleccione el rango de celdas D4: D11, que debe llenarse con los valores de la función TENDENCIA, correspondientes a los datos conocidos sobre el beneficio de la empresa;

    Llame al comando Función desde el menú Insertar. En el cuadro de diálogo Asistente de funciones que aparece, seleccione la función TENDENCIA de la categoría Estadística y luego haga clic en el botón Aceptar. La misma operación se puede realizar presionando el botón (Insertar función) en la barra de herramientas estándar.

    En el cuadro de diálogo Argumentos de función que aparece, ingrese en el campo Valores_conocidos_y el rango de celdas C4: C11; en el campo Known_x, el rango de celdas B4: B11;

    para hacer que la fórmula ingresada se convierta en una fórmula de matriz, use la combinación de teclas + +.

La fórmula que ingresamos en la barra de fórmulas se verá así: = (TENDENCIA (C4: C11; B4: B11)).

Como resultado, el rango de celdas D4: D11 se llena con los valores correspondientes de la función TENDENCIA (Fig. 9).

Realizar una previsión de los beneficios de la empresa para los años 2003 y 2004. necesario:

    seleccione el rango de celdas D12: D13, donde se ingresarán los valores predichos por la función TENDENCIA.

    llame a la función TENDENCIA y en el cuadro de diálogo Argumentos de función que aparece, ingrese en el campo Valores_conocidos_y: el rango de celdas C4: C11; en el campo Known_x, el rango de celdas B4: B11; y el campo New_x_values ​​contiene el rango de celdas B12: B13.

    convierta esta fórmula en una fórmula de matriz usando el atajo de teclado Ctrl + Shift + Enter.

    La fórmula ingresada se verá así: = (TENDENCIA (C4: C11; B4: B11; B12: B13)), y el rango de celdas D12: D13 se completará con los valores predichos de la función TENDENCIA (ver Fig. 9).

De manera similar, una serie de datos se llena usando la función CRECIMIENTO, que se usa en el análisis de dependencias no lineales y funciona exactamente de la misma manera que su TENDENCIA analógica lineal.

La Figura 10 muestra una tabla en el modo de visualización de fórmulas.

Para los datos iniciales y la serie de datos obtenidos, el diagrama que se muestra en la Fig. once.

Problema 4

Con la tabla de datos sobre la recepción de solicitudes de servicios por parte del servicio de despacho de una empresa de autotransporte para el período del 1 al 11 del mes en curso, se deben realizar las siguientes acciones.

    Obtener series de datos para regresión lineal: usando las funciones PENDIENTE e INTERCEPT; utilizando la función LINEST.

    Obtenga una serie de datos para la regresión exponencial usando la función LGRFPRIBL.

    Con las funciones anteriores, haga un pronóstico sobre la recepción de solicitudes en el servicio de despacho para el período comprendido entre el día 12 y el 14 del mes en curso.

    Construya un diagrama para la serie de datos original y recibida.

La solucion del problema

Tenga en cuenta que, a diferencia de las funciones TREND y GROWTH, ninguna de las funciones anteriores (SLOPE, INTERCEPT, LINEST, LGRFPRIB) es una regresión. Estas funciones juegan solo un papel auxiliar, definiendo los parámetros necesarios de la regresión.

Para regresiones lineales y exponenciales, construidas usando las funciones SLOPE, INTERCEPT, LINEST, LGRFPRIB, siempre se conoce la apariencia de sus ecuaciones, en contraste con las regresiones lineales y exponenciales correspondientes a las funciones TREND y GROWTH.

1 ... Construyamos una regresión lineal con la ecuación:

y = mx + b

con las funciones PENDIENTE e INTERCEPCIÓN, donde la pendiente m está determinada por la función PENDIENTE y la intersección b por la función INTERCEPCIÓN.

Para ello, realizamos las siguientes acciones:

    ingresamos la tabla original en el rango de celdas A4: B14;

    el valor del parámetro m se determinará en la celda C19. Seleccione de la categoría Estadística Pendiente; ingrese el rango de celdas B4: B14 en el campo conocido_y y el rango de celdas A4: A14 en el campo conocido_x. Ingresará la fórmula en la celda C19: = PENDIENTE (B4: B14; A4: A14);

    utilizando una metodología similar, se determina el valor del parámetro b en la celda D19. Y su contenido se verá así: = INTERCEPT (B4: B14; A4: A14). Así, los valores de los parámetros myb, necesarios para construir la regresión lineal, serán almacenados en las celdas C19, D19, respectivamente;

    luego ingresamos la fórmula de regresión lineal en la celda C4 en la forma: = $ C * A4 + $ D. En esta fórmula, las celdas C19 y D19 se escriben con referencias absolutas (la dirección de celda no debe cambiar cuando es posible copiar). El signo de referencia absoluta $ se puede escribir desde el teclado o usando la tecla F4, después de colocar el cursor en la dirección de la celda. Usando el controlador de relleno, copie esta fórmula en el rango de celdas C4: C17. Obtenemos la serie de datos requerida (Fig. 12). Debido al hecho de que el número de pedidos es un entero, establezca el formato del número con 0 lugares decimales en la pestaña Número de la ventana Formato de celdas.

2 ... Ahora construyamos la regresión lineal dada por la ecuación:

y = mx + b

utilizando la función LINEST.

Para esto:

    ingrese la función LINEST en el rango de celdas C20: D20 como una fórmula de matriz: = (LINEST (B4: B14; A4: A14)). Como resultado, obtenemos en la celda C20 el valor del parámetro my en la celda D20, el valor del parámetro b;

    ingrese la fórmula en la celda D4: = $ C * A4 + $ D;

    copie esta fórmula usando el controlador de relleno en el rango de celdas D4: D17 y obtenga la serie de datos requerida.

3 ... Construimos una regresión exponencial que tiene la ecuación:

utilizando la función LGRFPRIBL, se realiza de la misma forma:

    en el rango de celdas C21: D21 ingresamos la función LGRFPRIBL como una fórmula de matriz: = (LGRFPRIBL (B4: B14; A4: A14)). En este caso, en la celda C21 se determinará el valor del parámetro m, y en la celda D21, el valor del parámetro b;

    la fórmula se ingresa en la celda E4: = $ D * $ C ^ A4;

    utilizando el marcador de relleno, esta fórmula se copia al rango de celdas E4: E17, donde se ubicará la serie de datos para la regresión exponencial (ver Fig. 12).

En la Fig. 13 es una tabla donde puede ver las funciones que usamos con los rangos de celdas requeridos, así como fórmulas.

La magnitud R 2 llamado coeficiente de determinación.

La tarea de construir una dependencia de regresión es encontrar el vector de coeficientes m del modelo (1) en el que el coeficiente R toma su valor máximo.

Para evaluar la significancia de R, se utiliza la prueba F de Fisher, calculada mediante la fórmula

donde norte- tamaño de la muestra (número de experimentos);

k es el número de coeficientes del modelo.

Si F excede algún valor crítico para los datos norte y k y el nivel de confianza aceptado, entonces el valor de R se considera significativo. Las tablas de valores críticos de F se dan en manuales de estadística matemática.

Por lo tanto, la importancia de R está determinada no solo por su valor, sino también por la relación entre el número de experimentos y el número de coeficientes (parámetros) del modelo. De hecho, la relación de correlación para n = 2 para un modelo lineal simple es 1 (a través de 2 puntos en el plano, siempre se puede dibujar una sola línea recta). Sin embargo, si los datos experimentales son valores aleatorios, se debe confiar con mucho cuidado en dicho valor R. Por lo general, para obtener una R significativa y una regresión confiable, uno se esfuerza por asegurar que el número de experimentos exceda significativamente el número de coeficientes del modelo (n> k).

Para construir un modelo de regresión lineal, debe:

1) prepare una lista de n filas ym columnas que contengan datos experimentales (una columna que contenga el valor de salida Y debe ser el primero o el último en la lista); por ejemplo, tomaremos los datos de la tarea anterior, agregando una columna con el nombre "No. Período", numere los números del período del 1 al 12. (estos serán los valores X)

2) vaya al menú Datos / Análisis de datos / Regresión

Si el elemento "Análisis de datos" en el menú "Herramientas" está ausente, entonces debe ir al elemento "Complementos" del mismo menú y seleccionar la casilla de verificación "Paquete de análisis".

3) en el cuadro de diálogo "Regresión" establecido:

· Intervalo de entrada Y;

· Intervalo de entrada X;

· Intervalo de salida: la celda superior izquierda del intervalo en el que se colocarán los resultados de los cálculos (se recomienda colocarlos en una nueva hoja de trabajo);

4) haga clic en "Aceptar" y analice los resultados.