Selección de mínimos cuadrados. Datos y aproximación y = k x

Método de mínimos cuadrados ordinarios (OLS)- un método matemático utilizado para resolver diversos problemas, basado en minimizar la suma de las desviaciones al cuadrado de determinadas funciones de las variables deseadas. Se puede utilizar para "resolver" sistemas de ecuaciones sobredeterminados (cuando el número de ecuaciones excede el número de incógnitas), para encontrar soluciones en el caso de sistemas de ecuaciones no lineales ordinarios (no sobredeterminados), para aproximar valores puntuales de algunos función. OLS es uno de los métodos básicos de análisis de regresión para estimar parámetros desconocidos de modelos de regresión a partir de datos de muestra.

YouTube enciclopédico

1 / 5

✪ Método de mínimos cuadrados. Sujeto

✪ Método de mínimos cuadrados, lección 1/2. Función lineal

✪ Econometría. Conferencia 5. Método de mínimos cuadrados.

✪ Mitin I.V. - Procesamiento de resultados físicos. Experimento: método de mínimos cuadrados (Conferencia 4)

✪ Econometría: La esencia del método de mínimos cuadrados n.° 2

Subtítulos

Historia

Hasta principios del siglo XIX. los científicos no tenían ciertas reglas para resolver un sistema de ecuaciones en el que el número de incógnitas es menor que el número de ecuaciones; Hasta ese momento se utilizaban técnicas privadas que dependían del tipo de ecuaciones y del ingenio de los calculadores, por lo que diferentes calculadores, basándose en los mismos datos de observación, llegaban a conclusiones diferentes. Gauss (1795) fue el primero en utilizar el método, y Legendre (1805) lo descubrió y publicó de forma independiente con su nombre moderno (francés. Méthode des moindres quarrés). Laplace relacionó el método con la teoría de la probabilidad, y el matemático estadounidense Adrian (1808) consideró sus aplicaciones en la teoría de la probabilidad. El método se generalizó y mejoró gracias a nuevas investigaciones de Encke, Bessel, Hansen y otros.

La esencia del método de mínimos cuadrados.

Dejar x (\displaystyle x)- equipo norte (\ Displaystyle n) variables desconocidas (parámetros), f yo (x) (\displaystyle f_(i)(x)), , metro > norte (\displaystyle m>n)- un conjunto de funciones de este conjunto de variables. La tarea es seleccionar dichos valores. x (\displaystyle x), para que los valores de estas funciones estén lo más cerca posible de ciertos valores y yo (\displaystyle y_(i)). Básicamente estamos hablando de la "solución" de un sistema de ecuaciones sobredeterminado. f yo (x) = y yo (\displaystyle f_(i)(x)=y_(i)), yo = 1 , … , metro (\displaystyle i=1,\ldots ,m) en el sentido indicado de máxima proximidad de las partes izquierda y derecha del sistema. La esencia del método de mínimos cuadrados es seleccionar como "medida de proximidad" la suma de las desviaciones al cuadrado de los lados izquierdo y derecho. | F yo (x) − y yo | (\displaystyle |f_(i)(x)-y_(i)|). Así, la esencia de MNC se puede expresar de la siguiente manera:

Si el sistema de ecuaciones tiene solución, entonces el mínimo de la suma de cuadrados será igual a cero y las soluciones exactas del sistema de ecuaciones se pueden encontrar analíticamente o, por ejemplo, utilizando varios métodos de optimización numérica. Si el sistema está sobredeterminado, es decir, en términos generales, el número de ecuaciones independientes es mayor que el número de variables deseadas, entonces el sistema no tiene una solución exacta y el método de mínimos cuadrados nos permite encontrar algún vector “óptimo”. x (\displaystyle x) en el sentido de máxima proximidad de vectores y (\displaystyle y) Y f (x) (\displaystyle f(x)) o proximidad máxima del vector de desviación mi (\displaystyle e) a cero (la cercanía se entiende en el sentido de distancia euclidiana).

Ejemplo: sistema de ecuaciones lineales.

En particular, el método de mínimos cuadrados se puede utilizar para "resolver" un sistema de ecuaciones lineales.

Dónde A (\displaystyle A) matriz de tamaño rectangular m × n , m > n (\displaystyle m\times n,m>n)(es decir, el número de filas de la matriz A es mayor que el número de variables buscadas).

En el caso general, tal sistema de ecuaciones no tiene solución. Por lo tanto, este sistema sólo puede "resolverse" en el sentido de elegir dicho vector x (\displaystyle x) para minimizar la "distancia" entre vectores A x (\displaystyle Hacha) Y segundo (\displaystyle b). Para hacer esto, puede aplicar el criterio de minimizar la suma de diferencias al cuadrado entre los lados izquierdo y derecho de las ecuaciones del sistema, es decir (A x − b) T (A x − b) → min (\displaystyle (Ax-b)^(T)(Ax-b)\rightarrow \min ). Es fácil demostrar que resolver este problema de minimización conduce a resolver el siguiente sistema de ecuaciones

MCO en análisis de regresión (aproximación de datos)

Dejalo ser norte (\ Displaystyle n) valores de alguna variable y (\displaystyle y)(estos podrían ser los resultados de observaciones, experimentos, etc.) y variables relacionadas x (\displaystyle x). El desafío es garantizar que la relación entre y (\displaystyle y) Y x (\displaystyle x) aproximado por alguna función conocida hasta algunos parámetros desconocidos segundo (\displaystyle b), es decir, encontrar realmente los mejores valores de los parámetros. segundo (\displaystyle b), aproximando al máximo los valores f (x, b) (\displaystyle f(x,b)) a valores reales y (\displaystyle y). De hecho, esto se reduce al caso de "resolver" un sistema de ecuaciones sobredeterminado con respecto a segundo (\displaystyle b):

F (x t , b) = y t , t = 1 , … , n (\displaystyle f(x_(t),b)=y_(t),t=1,\ldots ,n).

En el análisis de regresión y en particular en econometría, se utilizan modelos probabilísticos de dependencia entre variables.

Y t = f (x t , b) + ε t (\displaystyle y_(t)=f(x_(t),b)+\varepsilon _(t)),

Dónde ε t (\displaystyle \varepsilon _(t))- así llamado errores aleatorios modelos.

En consecuencia, las desviaciones de los valores observados. y (\displaystyle y) del modelo f (x, b) (\displaystyle f(x,b)) ya se asume en el propio modelo. La esencia del método de mínimos cuadrados (ordinario, clásico) es encontrar dichos parámetros segundo (\displaystyle b), en el que la suma de las desviaciones al cuadrado (errores, para los modelos de regresión a menudo se denominan residuos de regresión) mi t (\displaystyle e_(t)) será mínimo:

Dónde R S S (\displaystyle RSS)- Inglés La suma residual de cuadrados se define como:

En el caso general, este problema se puede resolver mediante métodos de optimización numérica (minimización). En este caso hablamos de mínimos cuadrados no lineales(NLS o NLLS - Mínimos cuadrados no lineales en inglés). En muchos casos es posible obtener una solución analítica. Para resolver el problema de minimización es necesario encontrar puntos estacionarios de la función. R S S (b) (\displaystyle RSS(b)), diferenciándolo según parámetros desconocidos segundo (\displaystyle b), igualando las derivadas a cero y resolviendo el sistema de ecuaciones resultante:

MCO en el caso de regresión lineal

Sea la dependencia de la regresión lineal:

Dejar y es el vector columna de observaciones de la variable que se está explicando, y X (\displaystyle X)- Este (norte × k) (\displaystyle ((n\times k)))-matriz de observaciones de factores (las filas de la matriz son vectores de valores de factores en una observación determinada, las columnas son un vector de valores de un factor determinado en todas las observaciones). La representación matricial del modelo lineal tiene la forma:

Entonces el vector de estimaciones de la variable explicada y el vector de residuos de regresión serán iguales

En consecuencia, la suma de los cuadrados de los residuos de regresión será igual a

Diferenciando esta función con respecto al vector de parámetros segundo (\displaystyle b) e igualando las derivadas a cero, obtenemos un sistema de ecuaciones (en forma matricial):

En forma matricial descifrada, este sistema de ecuaciones se ve así:

(∑ x t 1 2 ∑ x t 1 x t 2 ∑ x t 1 x t 3 … ∑ x t 1 x t k ∑ x t 2 x t 1 ∑ x t 2 2 ∑ x t 2 x t 3 … ∑ x t 2 x t k ∑ x t 3 x t 1 ∑ x t 3 x t 2 ∑ x t 3 2 … ∑ x t 3 x t k ⋮ ⋮ ⋮ ⋱ ⋮ ∑ x t k x t 1 ∑ x t k x t 2 ∑ x t k x t 3 … ∑ x t k 2) (b 1 b 2 b 3 ⋮ b k) = (∑ x t 1 y t ∑ x t 2 y t ∑xt3 y t ⋮ ∑ x t k y t) , (\displaystyle (\begin(pmatrix)\sum x_(t1)^(2)&\sum x_(t1)x_(t2)&\sum x_(t1)x_(t3)&\ldots &\sum x_(t1)x_(tk)\\\sum x_(t2)x_(t1)&\sum x_(t2)^(2)&\sum x_(t2)x_(t3)&\ldots &\ suma x_(t2)x_(tk)\\\sum x_(t3)x_(t1)&\sum x_(t3)x_(t2)&\sum x_(t3)^(2)&\ldots &\sum x_ (t3)x_(tk)\\\vdots &\vdots &\vdots &\ddots &\vdots \\\sum x_(tk)x_(t1)&\sum x_(tk)x_(t2)&\sum x_ (tk)x_(t3)&\ldots &\sum x_(tk)^(2)\\\end(pmatrix))(\begin(pmatrix)b_(1)\\b_(2)\\b_(3 )\\\vdots \\b_(k)\\\end(pmatrix))=(\begin(pmatrix)\sum x_(t1)y_(t)\\\sum x_(t2)y_(t)\\ \sum x_(t3)y_(t)\\\vdots \\\sum x_(tk)y_(t)\\\end(pmatrix)),) donde todas las sumas se toman sobre todos los valores válidos t (displaystyle t).

Si se incluye una constante en el modelo (como es habitual), entonces x t 1 = 1 (\displaystyle x_(t1)=1) en frente de todos t (displaystyle t), por lo tanto, en la esquina superior izquierda de la matriz del sistema de ecuaciones está el número de observaciones norte (\ Displaystyle n), y en los elementos restantes de la primera fila y la primera columna, simplemente las sumas de los valores de las variables: ∑ x t j (\displaystyle \sum x_(tj)) y el primer elemento del lado derecho del sistema es ∑ y t (\displaystyle \sum y_(t)).

La solución de este sistema de ecuaciones da la fórmula general para estimaciones de mínimos cuadrados para un modelo lineal:

Para fines analíticos, resulta útil la última representación de esta fórmula (en el sistema de ecuaciones al dividir por n, aparecen medias aritméticas en lugar de sumas). Si en un modelo de regresión los datos centrado, entonces en esta representación la primera matriz tiene el significado de una matriz de covarianzas de factores de muestra, y la segunda es un vector de covarianzas de factores con la variable dependiente. Si además los datos también son normalizado a MSE (es decir, en última instancia estandarizado), entonces la primera matriz tiene el significado de una matriz de correlación muestral de factores, el segundo vector, un vector de correlaciones muestrales de factores con la variable dependiente.

Una propiedad importante de las estimaciones MCO para modelos. con constante- la línea de regresión construida pasa por el centro de gravedad de los datos muestrales, es decir, se cumple la igualdad:

En particular, en el caso extremo, cuando el único regresor es una constante, encontramos que la estimación MCO del único parámetro (la constante misma) es igual al valor promedio de la variable explicada. Es decir, la media aritmética, conocida por sus buenas propiedades de las leyes de los grandes números, también es una estimación de mínimos cuadrados: satisface el criterio de la suma mínima de desviaciones al cuadrado de la misma.

Los casos especiales más simples.

En el caso de regresión lineal pareada y t = a + b x t + ε t (\displaystyle y_(t)=a+bx_(t)+\varepsilon _(t)), cuando se estima la dependencia lineal de una variable de otra, las fórmulas de cálculo se simplifican (puede prescindir del álgebra matricial). El sistema de ecuaciones tiene la forma:

Desde aquí es fácil encontrar estimaciones de coeficientes:

A pesar de que en el caso general son preferibles los modelos con una constante, en algunos casos se sabe por consideraciones teóricas que una constante un (displaystyle a) debe ser igual a cero. Por ejemplo, en física la relación entre voltaje y corriente es U = yo ⋅ R (\displaystyle U=I\cdot R); Al medir voltaje y corriente, es necesario estimar la resistencia. En este caso estamos hablando del modelo. y = bx (\displaystyle y=bx). En este caso, en lugar de un sistema de ecuaciones tenemos una única ecuación

(∑ x t 2) b = ∑ x t y t (\displaystyle \left(\sum x_(t)^(2)\right)b=\sum x_(t)y_(t)).

Por tanto, la fórmula para estimar el coeficiente único tiene la forma

B ^ = ∑ t = 1 n x t y t ∑ t = 1 n x t 2 = x y ¯ x 2 ¯ (\displaystyle (\hat (b))=(\frac (\sum _(t=1)^(n)x_(t )y_(t))(\sum _(t=1)^(n)x_(t)^(2)))=(\frac (\overline (xy))(\overline (x^(2)) ))).

El caso de un modelo polinomial

Si los datos se ajustan mediante una función de regresión polinómica de una variable f (x) = b 0 + ∑ i = 1 k b yo x i (\displaystyle f(x)=b_(0)+\sum \limits _(i=1)^(k)b_(i)x^(i)), entonces, percibiendo grados x yo (\displaystyle x^(i)) como factores independientes para cada yo (\displaystyle yo) es posible estimar los parámetros del modelo basándose en la fórmula general para estimar los parámetros de un modelo lineal. Para ello, basta con tener en cuenta en la fórmula general que con tal interpretación x t i x t j = x t i x t j = x t i + j (\displaystyle x_(ti)x_(tj)=x_(t)^(i)x_(t)^(j)=x_(t)^(i+j)) Y x t j y t = x t j y t (\displaystyle x_(tj)y_(t)=x_(t)^(j)y_(t)). En consecuencia, las ecuaciones matriciales en este caso tomarán la forma:

(n ∑ n x t … ∑ n x t k ∑ n x t ∑ n x t 2 … ∑ n x t k + 1 ⋮ ⋮ ⋱ ⋮ ∑ n x t k ∑ n x t k + 1 … ∑ n x t 2 k) [ b 0 b 1 ⋮ b k ] = [ ∑ norte y t ∑ norte t y t ⋮ ∑ norte x t k y t ] . (\displaystyle (\begin(pmatrix)n&\sum \limits _(n)x_(t)&\ldots &\sum \limits _(n)x_(t)^(k)\\\sum \limits _( n)x_(t)&\sum \limits _(n)x_(t)^(2)&\ldots &\sum \limits _(n)x_(t)^(k+1)\\\vdots & \vdots &\ddots &\vdots \\\sum \limits _(n)x_(t)^(k)&\sum \limits _(n)x_(t)^(k+1)&\ldots &\ suma \limits _(n)x_(t)^(2k)\end(pmatrix))(\begin(bmatrix)b_(0)\\b_(1)\\\vdots \\b_(k)\end( bmatrix))=(\begin(bmatrix)\sum \limits _(n)y_(t)\\\sum \limits _(n)x_(t)y_(t)\\\vdots \\\sum \limits _(n)x_(t)^(k)y_(t)\end(bmatrix)).)

Propiedades estadísticas de los estimadores MCO.

En primer lugar, observamos que para los modelos lineales, las estimaciones de MCO son estimaciones lineales, como se desprende de la fórmula anterior. Para estimaciones insesgadas de MCO, es necesario y suficiente cumplir la condición más importante del análisis de regresión: la expectativa matemática de un error aleatorio, condicionada a los factores, debe ser igual a cero. Esta condición, en particular, se cumple si

1. la expectativa matemática de errores aleatorios es cero, y
2. Los factores y los errores aleatorios son variables “aleatorias” independientes.

La segunda condición, la condición de exogeneidad de los factores, es fundamental. Si no se cumple esta propiedad, entonces podemos suponer que casi todas las estimaciones serán extremadamente insatisfactorias: ni siquiera serán consistentes (es decir, incluso una gran cantidad de datos no nos permite obtener estimaciones de alta calidad en este caso). ). En el caso clásico, se hace un supuesto más fuerte sobre el determinismo de los factores, en contraposición a un error aleatorio, lo que automáticamente significa que se cumple la condición de exogeneidad. En el caso general, para la consistencia de las estimaciones, basta con satisfacer la condición de exogeneidad junto con la convergencia de la matriz. V x (\displaystyle V_(x)) a alguna matriz no singular a medida que el tamaño de la muestra aumenta hasta el infinito.

Para que, además de la coherencia y la imparcialidad, las estimaciones de mínimos cuadrados (ordinarios) también sean efectivas (las mejores en la clase de estimaciones lineales insesgadas), se deben cumplir propiedades adicionales del error aleatorio:

Estos supuestos se pueden formular para la matriz de covarianza del vector de error aleatorio. V (ε) = σ 2 I (\displaystyle V(\varepsilon)=\sigma ^(2)I).

Un modelo lineal que satisface estas condiciones se llama clásico. Las estimaciones de MCO para la regresión lineal clásica son insesgadas, consistentes y las estimaciones más efectivas en la clase de todas las estimaciones lineales insesgadas (en la literatura inglesa a veces se usa la abreviatura AZUL (Mejor estimador lineal insesgado) - la mejor estimación lineal insesgada; En la literatura rusa, se cita con mayor frecuencia el teorema de Gauss-Markov). Como es fácil de demostrar, la matriz de covarianza del vector de estimaciones de coeficientes será igual a:

V (b ^ O L S) = σ 2 (X T X) − 1 (\displaystyle V((\hat (b))_(OLS))=\sigma ^(2)(X^(T)X)^(-1 )).

Eficiencia significa que esta matriz de covarianza es "mínima" (cualquier combinación lineal de coeficientes, y en particular los coeficientes mismos, tienen una varianza mínima), es decir, en la clase de estimadores lineales insesgados, los estimadores MCO son los mejores. Los elementos diagonales de esta matriz (las varianzas de las estimaciones de los coeficientes) son parámetros importantes de la calidad de las estimaciones obtenidas. Sin embargo, no es posible calcular la matriz de covarianza porque se desconoce la varianza del error aleatorio. Se puede demostrar que una estimación insesgada y consistente (para un modelo lineal clásico) de la varianza de los errores aleatorios es la cantidad:

S 2 = R S S / (n − k) (\displaystyle s^(2)=RSS/(n-k)).

Sustituyendo este valor en la fórmula de la matriz de covarianza, obtenemos una estimación de la matriz de covarianza. Las estimaciones resultantes también son imparciales y consistentes. También es importante que la estimación de la varianza del error (y por tanto la varianza de los coeficientes) y las estimaciones de los parámetros del modelo sean variables aleatorias independientes, lo que permite obtener estadísticas de prueba para probar hipótesis sobre los coeficientes del modelo.

Cabe señalar que si no se cumplen los supuestos clásicos, las estimaciones de los parámetros MCO no son las más eficientes y, cuando W (\displaystyle W) es una matriz de peso definida positiva simétrica. Los mínimos cuadrados convencionales son un caso especial de este enfoque, donde la matriz de peso es proporcional a la matriz identidad. Como se sabe, para matrices (u operadores) simétricas existe una expansión W = P T P (\displaystyle W=P^(T)P). Por lo tanto, el funcional especificado se puede representar de la siguiente manera e T P T P e = (P e) T P e = e ∗ T e ∗ (\displaystyle e^(T)P^(T)Pe=(Pe)^(T)Pe=e_(*)^(T)e_( *)), es decir, este funcional se puede representar como la suma de los cuadrados de unos “restos” transformados. Por tanto, podemos distinguir una clase de métodos de mínimos cuadrados: los métodos LS (Mínimos Cuadrados).

Se ha demostrado (teorema de Aitken) que para un modelo de regresión lineal generalizado (en el que no se imponen restricciones a la matriz de covarianza de errores aleatorios), las más efectivas (en la clase de estimaciones lineales insesgadas) son las llamadas estimaciones. Mínimos cuadrados generalizados (GLS - Mínimos cuadrados generalizados)- Método LS con una matriz de ponderaciones igual a la matriz de covarianza inversa de errores aleatorios: W = V ε − 1 (\displaystyle W=V_(\varepsilon )^(-1)).

Se puede demostrar que la fórmula para las estimaciones GLS de los parámetros de un modelo lineal tiene la forma

B ^ G L S = (X T V − 1 X) − 1 X T V − 1 y (\displaystyle (\hat (b))_(GLS)=(X^(T)V^(-1)X)^(-1) X^(T)V^(-1)y).

En consecuencia, la matriz de covarianza de estas estimaciones será igual a

V (b ^ G L S) = (X T V − 1 X) − 1 (\displaystyle V((\hat (b))_(GLS))=(X^(T)V^(-1)X)^(- 1)).

De hecho, la esencia de OLS radica en una determinada transformación (lineal) (P) de los datos originales y la aplicación de OLS ordinario a los datos transformados. El propósito de esta transformación es que para los datos transformados, los errores aleatorios ya satisfagan los supuestos clásicos.

MCO ponderado

En el caso de una matriz de ponderación diagonal (y por tanto de una matriz de covarianza de errores aleatorios), tenemos los llamados mínimos cuadrados ponderados (WLS). En este caso, la suma ponderada de cuadrados de los residuos del modelo se minimiza, es decir, cada observación recibe un “peso” que es inversamente proporcional a la varianza del error aleatorio en esta observación: e T W e = ∑ t = 1 norte e t 2 σ t 2 (\displaystyle e^(T)Nosotros=\sum _(t=1)^(n)(\frac (e_(t)^(2))(\ sigma_(t)^(2)))). De hecho, los datos se transforman ponderando las observaciones (dividiendo por una cantidad proporcional a la desviación estándar estimada de los errores aleatorios) y se aplica MCO ordinario a los datos ponderados.

ISBN 978-5-7749-0473-0.

El método de mínimos cuadrados es un procedimiento matemático para construir una ecuación lineal que se ajuste mejor a un conjunto de pares ordenados encontrando los valores de a y b, los coeficientes en la ecuación de la recta. El objetivo de los mínimos cuadrados es minimizar el error cuadrático total entre los valores de y y ŷ. Si para cada punto determinamos el error ŷ, el método de mínimos cuadrados minimiza:

donde n = número de pares ordenados alrededor de la recta. lo más cerca posible de los datos.

Este concepto se ilustra en la figura.

Según la figura, la línea que mejor se ajusta a los datos, la línea de regresión, minimiza el error cuadrático total de los cuatro puntos del gráfico. Te mostraré cómo determinar esto usando mínimos cuadrados con el siguiente ejemplo.

Imagine una pareja joven que se ha mudado recientemente y comparte un tocador en el baño. El joven empezó a notar que la mitad de su mesa se iba reduciendo inexorablemente, perdiendo terreno frente a las espumas para el cabello y los complejos de soja. Durante los últimos meses, el chico había estado monitoreando de cerca el ritmo al que aumentaba la cantidad de objetos en su lado de la mesa. La siguiente tabla muestra la cantidad de artículos que la niña ha acumulado en el tocador de su baño durante los últimos meses.

Dado que nuestro objetivo es averiguar si la cantidad de artículos aumenta con el tiempo, "Mes" será la variable independiente y "Número de artículos" será la variable dependiente.

Usando el método de mínimos cuadrados, determinamos la ecuación que mejor se ajusta a los datos calculando los valores de a, la intersección con el eje y, y b, la pendiente de la recta:

a = y promedio - bx promedio

donde x avg es el valor promedio de x, la variable independiente, y avg es el valor promedio de y, la variable independiente.

La siguiente tabla resume los cálculos necesarios para estas ecuaciones.

La curva de efecto para nuestro ejemplo de bañera estaría dada por la siguiente ecuación:

Dado que nuestra ecuación tiene una pendiente positiva de 0,976, el hombre tiene evidencia de que la cantidad de elementos sobre la mesa aumenta con el tiempo a una tasa promedio de 1 elemento por mes. El gráfico muestra la curva del efecto con pares ordenados.

La expectativa de número de artículos durante los próximos seis meses (mes 16) se calculará de la siguiente manera:

ŷ = 5,13 + 0,976x = 5,13 + 0,976(16) ~ 20,7 = 21 ítems

Entonces, es hora de que nuestro héroe actúe.

Función TENDENCIA en Excel

Como probablemente ya habrás adivinado, Excel tiene una función para calcular valores por método de mínimos cuadrados. Esta función se llama TENDENCIA. Su sintaxis es la siguiente:

TENDENCIA (valores Y conocidos; valores X conocidos; nuevos valores X; constante)

valores Y conocidos: una matriz de variables dependientes, en nuestro caso, la cantidad de objetos en la mesa

valores conocidos X: una matriz de variables independientes, en nuestro caso este es el mes

nuevos valores X – nuevos valores X (meses) para los cuales Función TENDENCIA devuelve el valor esperado de las variables dependientes (número de elementos)

constante - opcional. Un valor booleano que especifica si se requiere que la constante b sea 0.

Por ejemplo, la figura muestra la función TENDENCIA utilizada para determinar la cantidad esperada de artículos en un tocador de baño durante el decimosexto mes.

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.

Usando método de mínimos cuadrados, aproxima estos datos mediante una dependencia lineal y=ax+b(buscar parámetros A Y b). Descubra cuál de las dos líneas alinea mejor (en el sentido del método de mínimos cuadrados) los datos experimentales. Haz un dibujo.

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

La tarea es encontrar los coeficientes de dependencia lineal en los que 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 las desviaciones al cuadrado de los datos experimentales de la línea recta encontrada será la más pequeña. Este es el objetivo del método de mínimos cuadrados.

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

Derivar fórmulas para encontrar coeficientes.

Se compila y resuelve un sistema de dos ecuaciones con dos incógnitas. Encontrar las derivadas parciales de una función. por variables A Y b, equiparamos estas derivadas a cero.

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

Dado A Y b función toma el valor más pequeño. La prueba de este hecho se da abajo en el texto al final de la página.

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

Es hora de recordar el ejemplo original.

Solución.

En nuestro ejemplo n=5. Completamos la tabla para facilitar el cálculo de los montos que se incluyen en las fórmulas de los coeficientes requeridos.

Los valores de la cuarta fila de la tabla se obtienen multiplicando los valores de la 2ª fila por los valores de la 3ª fila para cada número i.

Los valores de la quinta fila de la tabla se obtienen elevando al cuadrado los valores de la 2ª fila para cada número i.

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

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 de aproximación deseada.

Queda por descubrir cuál de las líneas y = 0,165x+2,184 o se aproxima mejor a los datos originales, es decir, hace 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 las desviaciones al cuadrado de los datos originales de estas líneas. Y , un valor menor corresponde a una línea que se aproxima mejor a los datos originales en el sentido del método de mínimos cuadrados.

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

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

Todo es claramente 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 rosas son los datos originales.

En la práctica, al modelar varios procesos, en particular económicos, físicos, técnicos y sociales, se utiliza ampliamente uno u otro método para calcular valores aproximados de funciones a partir de sus valores conocidos en ciertos puntos fijos.

Este tipo de problema de aproximación de funciones surge a menudo:

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

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

si es necesario, calcule los valores de funciones en puntos intermedios del intervalo considerado;

al determinar los valores de las cantidades características de un proceso fuera del intervalo considerado, en particular al realizar predicciones.

Si, para modelar un determinado proceso especificado por una tabla, construimos una función que describa aproximadamente este proceso según el método de mínimos cuadrados, se llamará función de aproximación (regresión) y la tarea de construir funciones de aproximación en sí se llamará un problema de aproximación.

Este artículo analiza las capacidades del paquete MS Excel para resolver este tipo de problemas; además, proporciona métodos y técnicas para construir (crear) regresiones para funciones tabuladas (que es la base del análisis de regresión).

Excel tiene dos opciones para construir regresiones.

Agregar regresiones seleccionadas (líneas de tendencia) a un diagrama construido sobre la base de una tabla de datos para la característica del proceso en estudio (disponible solo si hay un diagrama construido);

Utilizando las funciones estadísticas integradas de la hoja de cálculo de Excel, lo que le permite obtener regresiones (líneas de tendencia) directamente desde la tabla de datos de origen.

Agregar líneas de tendencia a un gráfico

Para una tabla de datos que describe un proceso y está representada por un diagrama, Excel tiene una herramienta eficaz de análisis de regresión 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 regresión construida al diagrama;

determine el grado de correspondencia de la regresión seleccionada con los datos mostrados en el gráfico.

Basado en los datos del gráfico, Excel le permite obtener tipos de regresiones lineales, polinomiales, logarítmicas, de potencia y exponenciales, que se especifican mediante la ecuación:

y = y(x)

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

1 . La regresión lineal es buena para modelar características cuyos valores aumentan o disminuyen a un ritmo constante. Este es el modelo más simple de construir para el proceso en estudio. Se construye de acuerdo con la ecuación:

y = mx + b

donde m es la tangente de la pendiente de regresión lineal al eje x; b - coordenada del punto de intersección de la regresión lineal con el eje de ordenadas.

2 . Una 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 en estudio. Por tanto, un polinomio de segundo grado bien puede describir un proceso que tiene sólo un máximo o un mínimo; polinomio de tercer grado: no más de dos extremos; polinomio de cuarto grado: no más de tres extremos, etc.

En este caso, la línea de tendencia se construye 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 al modelar características cuyos valores inicialmente cambian rápidamente 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 relación en estudio se caracterizan por un cambio constante en la tasa de crecimiento. Un ejemplo de tal dependencia es la gráfica del movimiento uniformemente acelerado de un automóvil. Si hay valores cero o negativos en los datos, no se puede utilizar una línea de tendencia eléctrica.

Construido de acuerdo con la ecuación:

y = cxb

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 datos que contienen valores cero o negativos, este tipo de aproximación tampoco es aplicable.

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 confiabilidad de la aproximación: cuanto más cerca está el valor de R2 de la unidad, 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 Diagrama aparecerá en el menú principal;

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

Las mismas acciones se pueden implementar fácilmente moviendo el puntero del mouse sobre el gráfico correspondiente a una de las series de datos y haciendo clic derecho; En el menú contextual que aparece, seleccione el comando Agregar línea de tendencia. El cuadro de diálogo Línea de tendencia aparecerá en la pantalla con la pestaña Tipo abierta (Fig. 1).

Después de esto necesitas:

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

1 . El campo Serie integrada 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 Serie basada en.

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 de aproximación (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;

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

establezca 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 para la 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 empezar a editar una línea de tendencia ya dibujada, existen tres formas:

utilice el comando Línea de tendencia seleccionada del menú Formato, habiendo seleccionado previamente la línea de tendencia;

seleccione el comando Formatear línea de tendencia del menú contextual, que se abre haciendo clic derecho en la línea de tendencia;

Haga doble clic en la línea de tendencia.

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

Para eliminar una línea de tendencia que ya se ha dibujado, seleccione la línea de tendencia que desea eliminar y presione la tecla Eliminar.

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

la relativa facilidad de construir una línea de tendencia en los gráficos sin crear una tabla de datos para ella;

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 mediante un número arbitrario (dentro de los límites 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, en caso necesario, de obtener una evaluación de la fiabilidad de la aproximación.

Las desventajas incluyen las siguientes:

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 generación de series de datos para la característica en estudio basándose en las ecuaciones de la línea de tendencia obtenidas para ella es algo confuso: las ecuaciones de regresión requeridas se actualizan con cada cambio en los valores de la serie de datos original, pero solo dentro del área del gráfico. , mientras que la serie de datos formada sobre la base de la antigua tendencia de la ecuación lineal permanece sin cambios;

En los informes de gráfico dinámico, cambiar la vista de un gráfico o informe de tabla dinámica asociado no conserva las líneas de tendencia existentes, lo que significa que antes de dibujar líneas de tendencia o dar formato a un informe de gráfico dinámico, debe asegurarse de que el diseño del informe cumpla con los requisitos requeridos.

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

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

Usando las funciones integradas de Excel

Excel también tiene una herramienta de análisis de regresión para trazar líneas de tendencia fuera del área del gráfico. Hay varias funciones de hojas de cálculo estadísticas que puede utilizar para este propósito, pero todas ellas sólo le permiten crear regresiones lineales o exponenciales.

Excel tiene varias funciones para construir regresión lineal, en particular:

TENDENCIA;

• PENDIENTE y CORTE.

Además de varias funciones para construir una línea de tendencia exponencial, en particular:

LGRFPRIBL.

Cabe señalar que las técnicas para construir regresiones utilizando las funciones TENDENCIA y CRECIMIENTO son casi las mismas. Lo mismo puede decirse del par de funciones LINEST y LGRFPRIBL. Para estas cuatro funciones, la creación de una tabla de valores utiliza funciones de Excel, como fórmulas matriciales, que saturan un poco el proceso de creación de regresiones. Observemos también que la construcción de una regresión lineal, en nuestra opinión, se logra más fácilmente utilizando las funciones PENDIENTE e INTERCEPCIÓN, donde la primera determina la pendiente de la regresión lineal y la segunda determina el segmento interceptado por la regresión en el eje y.

Las ventajas de la herramienta de funciones integrada para el análisis de regresión son:

un proceso bastante simple y uniforme para generar series de datos de la característica en estudio para todas las funciones estadísticas integradas que definen las líneas de tendencia;

metodología estándar para construir líneas de tendencia basadas en series de datos generadas;

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

Las desventajas incluyen el hecho de que Excel no tiene funciones integradas para crear otros tipos de líneas de tendencia (excepto 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, se desconocen las ecuaciones de las líneas de tendencia.

Cabe señalar que los autores no se propusieron presentar el curso del análisis de regresión con ningún grado de exhaustividad. Su tarea principal es mostrar, mediante ejemplos específicos, las capacidades del paquete Excel a la hora de resolver problemas de aproximación; demostrar qué herramientas efectivas tiene Excel para crear regresiones y pronósticos; ilustran cómo tales problemas pueden ser resueltos con relativa facilidad incluso por un usuario que no tiene amplios conocimientos de análisis de regresión.

Ejemplos de resolución de problemas específicos.

Veamos cómo resolver problemas específicos utilizando las herramientas de Excel enumeradas.

Problema 1

Con un cuadro de datos sobre los beneficios de una empresa de transporte por carretera para 1995-2002. necesitas hacer lo siguiente:

Construye un diagrama.

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

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

Haga una previsión de las ganancias de la empresa para 2003 y 2004.

La solución del problema

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

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

Activamos el diagrama 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 Línea de tendencia (ver Fig. 1), agregamos alternativamente líneas de tendencia lineales, cuadráticas y cúbicas al diagrama. En el mismo cuadro de diálogo, abra la pestaña Parámetros (ver Fig. 2), en el campo Nombre de la curva de aproximación (suavizada), ingrese el nombre de la tendencia que se agrega y en el campo Pronóstico hacia adelante para: períodos, configure el valor 2, ya que se prevé realizar una previsión de beneficios para los próximos dos años. Para mostrar la ecuación de regresión y el valor de confiabilidad de la aproximación R2 en el área del diagrama, active las casillas de verificación Mostrar ecuación en la pantalla y coloque el valor de confiabilidad de la aproximación (R^2) en el diagrama. Para una mejor percepción visual, cambiamos 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 las ganancias empresariales para cada línea de tendencia para 1995-2004. Usemos las ecuaciones de la línea de tendencia presentadas 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 tiene como argumento una celda correspondiente del rango A4:A13. De manera similar, para la regresión cuadrática, complete el rango de celdas E4:E13, y para la regresión cúbica, complete el rango de celdas F4:F13. Así se ha elaborado una previsión de beneficios de la empresa para los años 2003 y 2004. utilizando tres tendencias. La tabla de valores resultante se muestra en la Fig. 6.

Problema 2

Construye un diagrama.

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

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

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

Haga un pronóstico de las ganancias de la empresa para 2003 y 2004 utilizando estas líneas de tendencia.

La solución del problema

Siguiendo la metodología dada al resolver el problema 1, obtenemos un diagrama al que se le agregan líneas de tendencia logarítmica, de potencia y exponencial (Fig. 7). A continuación, utilizando las ecuaciones de la línea de tendencia obtenidas, completamos una tabla de valores para las ganancias de la empresa, incluidos los valores previstos para 2003 y 2004. (Figura 8).

En la Fig. 5 y fig. Se puede observar que el modelo con tendencia logarítmica corresponde al valor más bajo de confiabilidad de aproximación.

R2 = 0,8659

Los valores más altos de R2 corresponden a modelos de tendencia polinómica: cuadrático (R2 = 0,9263) y cúbico (R2 = 0,933).

Problema 3

Con la tabla de datos sobre las ganancias de una empresa de transporte por carretera para 1995-2002, que figura en la tarea 1, se deben 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 originales y la serie de datos resultante.

La solución del problema

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

seleccione el rango de celdas D4:D11, que deben completarse con los valores de la función TENDENCIA correspondientes a los datos conocidos sobre las ganancias 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 haciendo clic en 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 el rango de celdas C4:C11 en el campo Valores_conocidos_y; en el campo Known_values_x - el rango de celdas B4:B11;

Para que la fórmula ingresada se convierta en una fórmula matricial, 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_values_x - el rango de celdas B4:B11; y en el campo New_values_x - el rango de celdas B12:B13.

convierta esta fórmula en una fórmula matricial usando la combinación de teclas 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).

La serie de datos se completa de manera similar 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 contraparte lineal TENDENCIA.

La Figura 10 muestra la tabla en 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.

Obtenga series de datos para regresión lineal: utilizando las funciones PENDIENTE e INTERCEPCIÓN; utilizando la función ESTIMACIÓN LINEAL.

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

Utilizando las funciones anteriores, haga una previsión de la recepción de solicitudes al servicio de despacho para el período del 12 al 14 del mes en curso.

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

La solución del problema

Tenga en cuenta que, a diferencia de las funciones TENDENCIA y CRECIMIENTO, ninguna de las funciones enumeradas anteriormente (PENDIENTE, INTERCEPCIÓN, ESTIMACIÓN LINEAL, LGRFPRIB) es regresión. Estas funciones desempeñan sólo un papel de apoyo, determinando los parámetros de regresión necesarios.

Para las regresiones lineales y exponenciales construidas utilizando las funciones PENDIENTE, INTERCEPCIÓN, ESTILO LINEAL, LGRFPRIB, la apariencia de sus ecuaciones siempre se conoce, a diferencia de las regresiones lineales y exponenciales correspondientes a las funciones TENDENCIA y CRECIMIENTO.

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

y = mx+b

utilizando las funciones PENDIENTE e INTERCEPCIÓN, con la pendiente de regresión m determinada por la función PENDIENTE y el término libre b por la función INTERCEPCIÓN.

Para ello llevamos a cabo las siguientes acciones:

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

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

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

Luego, ingrese 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 están escritas con referencias absolutas (la dirección de la celda no debe cambiar durante una posible copia). El signo de referencia absoluto $ 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 a que el número de aplicaciones es un número entero, debe establecer el formato numérico con el número de decimales en 0 en la pestaña Número de la ventana Formato de celda.

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

y = mx+b

utilizando la función ESTIMACIÓN LINEAL.

Para esto:

Ingrese la función ESTIMACIÓN LINEAL como una fórmula matricial en el rango de celdas C20:D20: =(ESTIMACIÓN LINEAL(B4:B14,A4:A14)). Como resultado, obtenemos el valor del parámetro m en la celda C20 y el valor del parámetro b en la celda D20;

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

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

3 . Construimos una regresión exponencial con la ecuación:

utilizando la función LGRFPRIBL se realiza de manera similar:

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

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

usando 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. La Figura 13 muestra una tabla donde se pueden ver las funciones que utilizamos con los rangos de celdas requeridos, así como fórmulas.

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 adquiere el valor máximo.

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

Dónde 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 la probabilidad de confianza aceptada, entonces el valor de R se considera significativo. Las tablas de valores críticos de F se proporcionan en libros de referencia sobre estadística matemática.

Por tanto, la importancia de R está determinada no sólo 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 igual a 1 (siempre se puede dibujar una sola línea recta a través de 2 puntos en un plano). Sin embargo, si los datos experimentales son variables aleatorias, se debe confiar en ese valor de R con gran cautela. Por lo general, para obtener R significativo y una regresión confiable, se esfuerzan por garantizar 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 necesita:

1) preparar una lista de n filas ym columnas que contienen datos experimentales (columna que contiene el valor de salida Y debe ser el primero o el último en la lista); Por ejemplo, tomemos los datos de la tarea anterior, agreguemos una columna llamada “Nº de período”, numeremos 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 falta el elemento "Análisis de datos" en el menú "Herramientas", debe ir al elemento "Complementos" en el mismo menú y marcar la casilla de verificación "Paquete de análisis".

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

· intervalo de entrada Y;

· intervalo de entrada X;

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

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

El método de mínimos cuadrados (MCO) le permite estimar varias cantidades utilizando los resultados de muchas mediciones que contienen errores aleatorios.

Características de las empresas multinacionales

La idea principal de este método es que la suma de los errores al cuadrado se considera un criterio para la precisión de la resolución del problema, que se esfuerzan por minimizar. Cuando se utiliza este método, se pueden utilizar enfoques tanto numéricos como analíticos.

En particular, como implementación numérica, el método de mínimos cuadrados implica tomar tantas medidas como sea posible de una variable aleatoria desconocida. Además, cuantos más cálculos, más precisa será la solución. A partir de este conjunto de cálculos (datos iniciales), se obtiene otro conjunto de soluciones estimadas, de las que luego se selecciona la mejor. Si el conjunto de soluciones está parametrizado, entonces el método de mínimos cuadrados se reducirá a encontrar el valor óptimo de los parámetros.

Como enfoque analítico para la implementación de LSM sobre un conjunto de datos iniciales (mediciones) y un conjunto esperado de soluciones, se determina uno determinado (funcional), que puede expresarse mediante una fórmula obtenida como una determinada hipótesis que requiere confirmación. En este caso, el método de mínimos cuadrados se reduce a encontrar el mínimo de este funcional en el conjunto de errores al cuadrado de los datos originales.

Tenga en cuenta que no se trata de los errores en sí, sino de los cuadrados de los errores. ¿Por qué? El hecho es que a menudo las desviaciones de las mediciones del valor exacto son tanto positivas como negativas. Al determinar el promedio, una simple suma puede llevar a una conclusión incorrecta sobre la calidad de la estimación, ya que la cancelación de valores positivos y negativos reducirá la capacidad de muestrear múltiples mediciones. Y, en consecuencia, la exactitud de la valoración.

Para evitar que esto suceda, se suman las desviaciones al cuadrado. Además, para igualar la dimensión del valor medido y la estimación final, se extrae la suma de los errores al cuadrado.

Algunas aplicaciones multinacionales

OLS se utiliza ampliamente en diversos campos. Por ejemplo, en teoría de la probabilidad y estadística matemática, el método se utiliza para determinar una característica de una variable aleatoria como la desviación estándar, que determina el ancho del rango de valores de la variable aleatoria.

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

Planteamiento del problema utilizando un ejemplo específico.

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

Entonces, sea X el espacio comercial de una tienda de comestibles, medido en metros cuadrados, e Y sea la facturación anual, determinada en millones de rublos.

Se requiere hacer una previsión de qué facturación (Y) tendrá la tienda si tiene tal o cual espacio comercial. Obviamente, la función Y = f (X) es creciente, ya que el hipermercado vende más productos que el puesto.

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

Digamos que tenemos una tabla creada con datos de n tiendas.

Según las estadísticas matemáticas, los resultados serán más o menos correctos si se examinan datos de al menos 5 o 6 objetos. Además, no se pueden utilizar resultados "anómalos". En particular, una pequeña boutique de élite puede tener una facturación varias veces mayor que la facturación de los grandes puntos de venta de la clase "masmarket".

La esencia del método.

Los datos de la tabla se pueden representar en un plano cartesiano en forma de puntos M 1 (x 1, y 1), ... M n (x n, y n). Ahora la solución al problema se reducirá a la selección de una función aproximada y = f (x), que tenga una gráfica que pase lo más cerca posible de los puntos M 1, M 2, .. M n.

Por supuesto, se puede utilizar un polinomio de alto grado, pero esta opción no sólo es difícil de implementar, sino que también es simplemente incorrecta, ya que no reflejará la tendencia principal que debe detectarse. La solución más razonable es buscar la recta y = ax + b, que mejor se aproxima a los datos experimentales, o más precisamente, a los coeficientes a y b.

Evaluación de precisión

En cualquier aproximación, evaluar 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 (xi).

Obviamente, para evaluar la precisión de la aproximación, se puede utilizar la suma de desviaciones, es decir, al elegir una línea recta para una representación aproximada de la dependencia de X de Y, es necesario dar preferencia a la que tiene el valor más pequeño de la suma e i en todos los puntos considerados. Sin embargo, no todo es tan sencillo, ya que junto a las desviaciones positivas también las habrá negativas.

El problema se puede solucionar utilizando módulos de desviación o sus cuadrados. El último método es el más utilizado. Se utiliza en muchas áreas, incluido el análisis de regresión (implementado en Excel mediante dos funciones integradas) y ha demostrado su eficacia desde hace mucho tiempo.

método de mínimos cuadrados

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

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

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

Así, la tarea 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:

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

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

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

Cómo implementar mínimos cuadrados en Excel

Excel tiene una función para calcular valores usando mínimos cuadrados. Tiene la siguiente forma: “TENDENCIA” (valores Y conocidos; valores X conocidos; valores X nuevos; constante). Apliquemos la fórmula para calcular OLS en Excel a nuestra tabla.

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

• rango de valores conocidos de Y (en este caso, datos sobre el volumen de negocios comercial);
• rango x 1 , …x n , es decir, el tamaño del espacio comercial;
• Valores tanto conocidos como desconocidos de x, para los cuales es necesario averiguar el tamaño del volumen de negocios (para obtener información sobre su ubicación en la hoja de trabajo, consulte a continuación).

Además, la fórmula contiene la variable lógica "Const". Si introduces 1 en el campo correspondiente, esto significará que deberás realizar los cálculos suponiendo 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 escribir la combinación "Shift" + "Control" + "Enter" en el teclado.

Algunas caracteristicas

El análisis de regresión puede ser accesible incluso para los principiantes. La fórmula de Excel para predecir el valor de una serie de variables desconocidas (TENDENCIA) puede ser utilizada incluso por aquellos que nunca han oído hablar de los mínimos cuadrados. Basta 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 conocidos de x como una variable separada.
• Si no se especifica un rango con x conocido en la ventana TENDENCIA, cuando use la función en Excel, el programa lo tratará como una matriz que consta de números enteros, cuyo número corresponde al rango con los valores dados de la variable y.
• Para generar una matriz de valores "predichos", la expresión para calcular la tendencia debe ingresarse como una fórmula matricial.
• Si no se especifican nuevos valores de x, entonces la función TENDENCIA los considera iguales a los conocidos. Si no se especifican, entonces la matriz 1 se toma como argumento; 2; 3; 4;…, que es proporcional al rango con los parámetros y ya especificados.
• El rango que contiene los nuevos valores de x debe tener la misma o más filas o columnas que el rango que contiene los valores de y dados. En otras palabras, debe ser proporcional a las variables independientes.
• Una matriz con valores de x conocidos puede contener múltiples variables. Sin embargo, si hablamos de solo uno, entonces se requiere que los rangos con los valores dados de xey sean proporcionales. En el caso de varias variables, es necesario que el rango con los valores de y dados quepa en una columna o una fila.

Función de PREDICCIÓN

Implementado utilizando varias funciones. Uno de ellos se llama “PREDICCIÓN”. Es similar a “TENDENCIA”, es decir, da el resultado de los cálculos utilizando el método de mínimos cuadrados. Sin embargo, sólo para un X, cuyo valor de Y se desconoce.

Ahora conoce fórmulas en Excel para principiantes que le permiten predecir el valor futuro de un indicador en particular según una tendencia lineal.