Ecuación normal en Excel (estadística)

Question

Estoy intentando aprender sobre estadística en elindustria de gestión de activos de infraestructura.

---

Tengo una ecuación de regresión exponencial que se utiliza para encontrar la condición de un activo determinado:

Y = B - e ^hacha

En una pregunta relacionada, alguien me sugirió que"puede obtener la variable adirectamente desde elecuación normal":

Regresión exponencial básica

Eso suena prometedor. Pero como alguien que no tiene experiencia en matemáticas, tengo problemas para convertir la notación matemática en esa ecuación a la sintaxis de Excel.

---

Pregunta:

¿Cómo puedo calcular elecuación normal¿Usando la sintaxis/fórmulas de Excel 2016?

Answer 1

Aquí hay una alternativa que aborda el problema que planteó en su comentario a la respuesta publicada enmatemáticas.stackexhange

Tu modelo es:

y = B - exp(a*x)

y tu tienes,a priori, determinó que B = 21.

El modelo no es una relación exacta entre los valores de y(i) y x(i), por lo que es habitual agregar un término de error e(i) y representar el modelo como

y(i) = B - exp(a*x(i)) + e(i)

Los valores de y estimados por el modelo para cada uno de los valores de x se denotan por y'(i) donde

y'(i) = B - exp(a*x(i))

Elmétodo de mínimos cuadradostiene como objetivo elegir el valor de a que minimice la suma de los cuadrados de las diferencias entre los valores reales de y(i) y los valores correspondientes estimados o y'(i).

y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

Entonces la suma de los cuadrados de las diferencias entre los valores y(i) e y'(i) es

Suma[(y(i) - y'(i))^2] = Suma[e(i)^2]

El lado derecho es la suma de los términos de error al cuadrado, por eso se llama suma de errores de cuadrados oESS.

El proceso de transformar los valores y(i) en valores z(i) mediante

z(i) = LN(21 - y(i))

crea un modelo lineal

z = hacha

que permite el uso de la LINESTfunción para estimar el valor de "mejor ajuste" dea. Con los datos proporcionados en su respuesta (o su pregunta en math.stackexchange), este valor de mejor ajuste deaes 0,147233: la misma respuesta que obtuviste en tu respuesta al implementar la ecuación normal.

El ESS asociado con este valor deaes 8,27991. Sin embargo, este valor no es el valor mínimo alcanzable del ESS. Eso ocurre cuandoatoma el valor de 0.149140 y el ESS correspondiente es 6.66073.

La siguiente captura de pantalla muestra los cálculos.

Los valores estimados de y'(i) y el error al cuadrado asociado y ESS se muestran para dos versiones del modelo y = 21 - exp(a*x).

En la versión 1,ase obtiene utilizando el LINESTenfoque basado en el modelo transformado z = ax. En la versión 2,aes el valor que minimiza la ESS del modelo (no transformado). Más sobre cómo este valor dease obtuvo se proporciona a continuación.

Con modelos lineales como y = mx + c, las ecuaciones normales proporcionan una manera conveniente de estimar los valores de myc que minimizan la ESS. La función LINESTimplementa (entre otras cosas) las ecuaciones normales.

Para modelos no lineales (como y = 21 - exp(a*x)) estas ecuaciones convenientes generalmente no existen, por lo que es necesario utilizar otros métodos para encontrar el valor dealo que minimiza el ESS.

Un enfoque es utilizar métodos de búsqueda: esencialmente probar una variedad de posibilidades diferentes paraay elija el que resulte en el ESS más pequeño.

Esto es efectivamente lo que muestra la siguiente captura de pantalla. Utiliza lo que Microsoft llama unTabla de datos. Esta es una mala elección de nombre ya que una tabla de datos no es una tabla de datos. Más bien es una herramienta para determinar cómo cambia el valor de un cálculo a medida que cambian uno o dos elementos del cálculo. Se encuentra en la cinta del grupo Pronóstico del menú Datos seleccionando "Tabla de datos..." en el icono "Análisis de hipótesis".

La documentación de Microsft para crear y utilizar una tabla de datos es bastante horrible, por lo que proporcionaré un enfoque de libro de cocina.

1. La tabla de datos en sí se proporciona en el rango N2:O23.
2. La celda O2contiene el valor calculado que se va a explorar. Esta celda contiene la fórmula =J4que es el ESS asociado con el valor deaen la celda J3.
3. Las posibilidades para los diferentes valores dease colocan en rango N3:N23y los resultados de la ESS aparecerán en las celdas O3:O23. Esto proporciona 21 valores posibles dea. Esta es una elección arbitraria, las tablas de datos pueden incluir un número mayor o menor de valores posibles.
4. Una vez elalos valores están en su lugar, seleccione el rango N2:O23e inicie el cuadro de diálogo Tabla de datos seleccionando "Tabla de datos..." en el icono "Análisis de hipótesis" en el grupo Pronóstico del menú Datos.
5. En el cuadro de diálogo, ingrese $J$3en el campo denominado "Celda de entrada de columna:" y presione el botón "Aceptar".
6. El rango O3:O23ahora se completará con los valores de ESS correspondientes a los valores deaen N3:N23. Cambiar cualquiera de los valores N3:N23actualizará los valores de ESS en O3:O23.

ElaLos valores N3:N23se establecen mediante fórmulas en lugar de escribirse. Los valores se establecen utilizando una estrategia de búsqueda que analiza conjuntos de valores cada vez más finos paraa.

el 21aLos valores en N3:N23se basan alrededor de unCentralvalor en la posición 11 - celda N13- con las celdas por encima y por debajo de este que difieren sucesivamente en unIncrementocantidad de modo que todo el rango de 21 valores esté en orden ascendente.

La estrategia de búsqueda pasa por varios pasos, y el número de paso está controlado por el valor de la celda O1.

En el paso 1, elCentralEl valor se establece en 0,15 (en la celda R3) y elIncrementose establece como 0,001 (en la celda S3), lo que da valores que N3:N23van desde 0,14 a 0,16. Este rango se elige en base al valor de la Versión 1 dea, con la anticipación de que el valor mínimo de ESS estará dentro de este rango.

Éste resulta ser el caso. Para los 21 valores deacomenzando en 0,14 y aumentando de 0,001 a 0,16, los valores ESS correspondientes comienzan en más de 39 (cuandoaes 0,14), disminuye comoaaumenta hastaatiene el valor de 0.149 (cuando el ESS es 6.66972) y luego aumenta hasta alcanzar un valor de ESS superior a 70 cuandoaes 0,16. Esto demuestra que el valor deaque minimiza el ESS está en las proximidades de 0,149.

(Si no se hubiera demostrado que se encuentra un mínimo dentro del rango de valores de a, todos los valores de ESS habrían aumentado o disminuido, colocando el mínimo en un extremo del rango. En este caso,Centralvalor (en la celda R3) requerirá ajuste con posiblemente un aumento en elIncrementovalor (en la celda S3) hasta que se encuentre un mínimo de rango medio).

Para cualquier rango de valores en N3:N23, las celdas identifican O27respectivamente N27el valor mínimo de ESS y el valor deaque produce el mínimo.

El valor deaproducir el mínimo, proporciona el nuevoCentralvalor para el siguiente paso de la búsqueda. El nuevoIncrementoes el valor anterior reducido por un factor de 10. Estos nuevosCentralyIncrementolos valores se ingresan manualmente en la "tabla de control" en columnas Ry Sel número de paso se incrementa manualmente en 1 en la celda O1.

La búsqueda continúa a través de pasos sucesivos y termina cuando no se pueden obtener reducciones prácticas en el valor de ESS.

La captura de pantalla muestra los resultados del paso 2 de la búsqueda.

Answer 2

Así es como se vería en Excel:

---

Intenté describirlo en pseudocódigo:

1. Para cada registro del conjunto, calcule x*LN(21-y). Calcule la suma de esos valores (lo llamaremos "suma 1").
2. Para cada registro del conjunto, calcule x^2. Calcule la suma de esos valores (lo llamaremos "suma 2").
3. Divide la suma 1 por la suma 2.

---

De un colega:

irepresenta la observación particular. Todos estos cálculos suponen que hay un número fijo, generalmente llamadonorte, pares de observaciones. Por ejemplo, tenías 20 pares de observaciones en tus datos. Aquí, pares significa elXyyvalor juntos, normalmente denotados como (x, y), (0, 20), (1, 20)....(20, 2). Elirepresenta eli'ésimo par de observaciones entre todasnortepares.

Así que sii= 1, esto significa que nos referimos al primer par, (0, 20). Sii= 14, tomamos el par 14, (14, 12). En general, matemáticamente, eliEl par de observaciones es (xi, yi),iestá en subíndice.

El signo sigma que dicei= 1 anorte, significa esencialmente que estamos tomando todos los pares de observaciones desde la primera observación hasta la última.