Recuerda que en las ciencias cualquier problema casi siempre tiene varias soluciones, de igual modo nosotros aquí te compartiremos lo más óptimo y eficiente.
Solución:
Necesitas usar un truco no documentado con Excel LINEST función:
=LINEST(known_y's, [known_x's], [const], [stats])
Antecedentes
Un habitual lineal la regresión se calcula (con sus datos) como:
=LINEST(B2:B21,A2:A21)
que devuelve un único valor, la pendiente lineal (m) según la fórmula:
que para sus datos:
es:
Truco indocumentado Número 1
También puede usar Excel para calcular una regresión con una fórmula que usa un exponente para x diferente de 1por ejemplo, x1.2:
usando la fórmula:
=LINEST(B2:B21, A2:A21^1.2)
que para ti datos:
es:
No estás limitado a un exponente
Excel LINEST La función también puede calcular regresiones múltiples, con diferentes exponentes en x al mismo tiempo, por ejemplo:
=LINEST(B2:B21,A2:A21^1,2)
Nota: si la configuración regional es europea (símbolo decimal “,”), la coma debe reemplazarse por punto y coma y barra invertida, es decir
=LINEST(B2:B21;A2:A21^12)
Ahora Excel calculará las regresiones usando tanto x1 y x2 al mismo tiempo:
Cómo hacerlo realmente
La parte imposiblemente complicada no hay una manera obvia de ver los otros valores de regresión. Para hacer eso necesitas:
-
seleccione la celda que contiene su fórmula:
-
extienda la selección los 2 espacios de la izquierda (necesita que la selección tenga al menos 3 celdas de ancho):
-
prensa F2
-
prensa control+Cambio+Ingresar
Ahora verá sus 3 constantes de regresión:
y = -0.01777539x^2 + 6.864151123x + -591.3531443
Charla adicional
Tenía una función que quería realizar una regresión usando algunos exponente:
y = m×xk + segundo
pero no lo hice saber el exponente Así que cambié el LINEST función para usar una referencia de celda en su lugar:
=LINEST(B2:B21,A2:A21^F3, true, true)
Con Excel luego generando estadísticas completas (el cuarto parámetro para LINEST):
le digo a la solucionador para maximizar R2:
Y puede averiguar el mejor exponente. Que para ti datos:
es:
Sé que esta pregunta es un poco antigua, pero pensé que daría una alternativa que, en mi opinión, podría ser un poco más fácil. Si está dispuesto a agregar columnas “temporales” a un conjunto de datos, puede usar el paquete de herramientas de análisis de Excel→Análisis de datos→Regresión. El secreto para hacer un análisis de regresión cuadrática o cúbica es definir el Rango de entrada X:.
Si está haciendo una regresión lineal simple, todo lo que necesita son 2 columnas, X e Y. Si está haciendo una regresión cuadrática, necesitará X_1, X_2 e Y donde X_1 es el X variable y X_2 es x^2; del mismo modo, si está haciendo un cúbico, necesitará X_1, X_2, X_3 e Y, donde X_1 es el X variable, X_2 es x^2 y X_3 es x ^ 3. Fíjate cómo el Rango de entrada X es de A1 a B22, abarcando 2 columnas.
La siguiente imagen es la salida del análisis de regresión. He resaltado los resultados comunes, incluidos los valores R-Squared y todos los coeficientes.
La función ESTIMACION.LINEAL descrita en una respuesta anterior es el camino a seguir, pero una forma más fácil de mostrar los 3 coeficientes de la salida es usar adicionalmente la función ÍNDICE. En una celda, escriba: =INDEX(LINEST(B2:B21,A2:A21^1,2,TRUE,FALSE),1) (por cierto, B2:B21 y A2:A21 que usé son solo los mismos valores que usó el primer cartel que respondió esto … por supuesto, cambiaría estos rangos adecuadamente para que coincidan con sus datos). Esto da el coeficiente X^2. En una celda adyacente, escriba la misma fórmula nuevamente pero cambie el 1 final por un 2… esto da el coeficiente X^1. Por último, en la siguiente celda, vuelva a escribir la misma fórmula pero cambie el último número a 3… esto da la constante. Me di cuenta de que los tres coeficientes son muy parecidos pero no del todo idénticos a los derivados mediante el uso de la función de línea de tendencia gráfica en la pestaña de gráficos. Además, descubrí que ESTIMACION.LINEAL solo parece funcionar si los datos X e Y están en columnas (no en filas), sin celdas vacías dentro del rango, así que tenga en cuenta que si obtiene un error #VALOR.
Recuerda que puedes compartir este post si te ayudó.