He discutido la regresión lineal en este blog antes, pero muy a menudo una línea recta no es la mejor manera de representar sus datos. Para estas situaciones específicas, podemos aprovechar algunas de las herramientas disponibles para realizar regresión no lineal o ajuste de curvas en Excel.
¿Recuerdas a nuestro viejo amigo LINEST? Aunque LINEST es la abreviatura de» estimación lineal», también podemos usarla para datos no lineales con unos simples ajustes.
digamos que tenemos algunos datos de caída de presión vs caudal a través de una válvula de agua, y después de trazar los datos en un gráfico vemos que los datos son cuadráticos.
Aunque estos datos no son lineales, la función LINEST también se puede usar aquí para encontrar la curva de ajuste mejor para estos datos. Para una ecuación polinómica, lo hacemos usando constantes de matriz.
Una ventaja de usar LINEST para obtener los coeficientes que definen la ecuación polinómica es que podemos devolver los coeficientes directamente a las celdas. De esa manera no tenemos que transferirlos manualmente del gráfico.
Dado que la ecuación es cuadrática, o un polinomio de segundo orden, hay tres coeficientes, uno para x al cuadrado, uno para x y una constante. Así que tendremos que empezar creando un espacio para almacenar los tres coeficientes de la ecuación.
Usando LINEST para regresión no lineal en Excel
La función LINEST devuelve una matriz de coeficientes y estadísticas de regresión opcionales. Así que necesitaremos ingresarlo como una fórmula de matriz seleccionando las tres celdas para los coeficientes antes de ingresar la fórmula.
Si las celdas que contienen los datos de flujo y presión se denominan «flujo» y «presión», la fórmula se ve así:
=LINEST(presión, flujo^{1,2},VERDADERO, FALSO)
Las y conocidas en este caso son las mediciones de presión, y las x conocidas son el flujo mediciones elevadas a la primera y segunda potencia. Los corchetes rizados, » {«y»}», indican una constante de matriz en Excel. Básicamente, le estamos diciendo a Excel que cree dos matrices: una de flujo y otra de flujo cuadrado, y que ajuste la presión a ambas matrices juntas.
Finalmente, los argumentos TRUE y FALSE le dicen a la función LINEST que calcule la intersección en y normalmente (en lugar de forzarla a cero) y que no devuelva estadísticas de regresión adicionales, respectivamente.
Dado que es una fórmula de matriz, necesitamos ingresarla escribiendo Ctrl+Shift+Enter.
La función devuelve los coeficientes de x2 y x, así como una constante (porque elegimos permitir que LINEST calcule la intersección en y).
Los coeficientes son idénticos a los generados por la herramienta línea de tendencia del gráfico, pero ahora están en celdas, lo que los hace mucho más fáciles de usar en cálculos posteriores.
Para cualquier ecuación polinómica, LINEST devuelve el coeficiente para el orden más alto de la variable independiente en el lado izquierdo, seguido por el siguiente más alto y así sucesivamente, y finalmente la constante.
También se puede utilizar una técnica similar para el ajuste exponencial, logarítmico y de curvas de función de potencia en Excel.
Ajuste de una curva Logarítmica a Datos
Una función logarítmica tiene la forma:
Todavía podemos usar LINEST para encontrar el coeficiente, m, y la constante, b, para esta ecuación insertando ln(x) como el argumento para el known_x:
= LINEST(y_values,ln (x_values),TRUE,FALSE)
Por supuesto, este método se aplica a cualquier ecuación logarítmica, independientemente del número base. Por lo tanto, podría aplicarse a una ecuación que contenga log10 o log2 con la misma facilidad.
Encontrar los coeficientes de una Curva Exponencial de Mejor ajuste
Una función exponencial tiene la forma:
Es un poco más complicado obtener los coeficientes, a y b, para esta ecuación porque primero necesitamos hacer un poco de álgebra para que la ecuación tome una forma» lineal». Primero, toma el logaritmo natural de ambos lados de la ecuación para obtener lo siguiente:
Ahora podemos usar LINEST para obtener ln (a) y b ingresando ln (y) como argumento para los valores y_:
=LINEST(ln(y_values),x_values,TRUE,FALSE)
El segundo valor devuelto por esta fórmula de matriz es ln(a), por lo que para obtener solo «a», simplemente usaríamos la función exponencial:
Que, en Excel, se traduce como:
=EXP(número)
Ajustar una función de potencia a los datos
Una curva de función de potencia se puede ajustar a los datos utilizando LINEST de la misma manera que lo hacemos para una función exponencial. Una función de potencia tiene la forma:
de Nuevo, podemos «linealizar» tomando la base 10 log de ambos lados de la ecuación para obtener:
Con la ecuación de esta forma, la ESTIMACION de la función para devolver b y log10(a) se puede configurar como esto:
=ESTIMACION(LOG10(yvalues),LOG10(xvalues),TRUE,FALSE)
a partir de la ESTIMACION función devuelve b y log10(a), vamos a tener que encontrar una con la siguiente fórmula:
En Excel, que es la formula:
=10^(número)
Eso es todo por ahora. Como puede ver, hay varias formas de usar la función LINEST para el ajuste de curvas no lineales en Excel.