qué - regresion en excel
Regresión cuadrática y cúbica en Excel (3)
Tengo la siguiente información:
Height Weight
170 65
167 55
189 85
175 70
166 55
174 55
169 69
170 58
184 84
161 56
170 75
182 68
167 51
187 85
178 62
173 60
172 68
178 55
175 65
176 70
Quiero construir análisis de regresión cuadrática y cúbica en Excel. Sé cómo hacerlo por regresión lineal en Excel, pero ¿qué hay de cuadrático y cúbico? Busqué muchos recursos, pero no pude encontrar nada útil.
Debe usar un truco no documentado con la función LINEST de Excel:
=LINEST(known_y''s, [known_x''s], [const], [stats])
Fondo
Una regresión lineal regular se calcula (con sus datos) como:
=LINEST(B2:B21,A2:A21)
que devuelve un único valor, la pendiente lineal ( m ) de acuerdo con la fórmula:
cuál para tus 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 1 , por ejemplo, x 1.2 :
usando la fórmula:
=LINEST(B2:B21, A2:A21^1.2)
que para ti datos:
es:
No estás limitado a un exponente
La función LINEST de Excel 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 se establece en europeo (símbolo decimal ","), entonces la coma debe reemplazarse por punto y coma y barra invertida, es decir,
=LINEST(B2:B21;A2:A21^{1/2})
Ahora Excel calculará regresiones usando tanto x 1 como x 2 al mismo tiempo:
Cómo hacerlo realmente
La parte imposiblemente difícil no es una forma 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 a los 2 espacios de la izquierda (necesita que la selección tenga al menos 3 celdas de ancho):
presione F2
presiona Ctrl + Shift + Enter
Ahora verá sus 3 constantes de regresión:
y = -0.01777539x^2 + 6.864151123x + -591.3531443
Bonus Chatter
Tenía una función que quería realizar una regresión usando algún exponente:
y = m × x k + b
Pero no sabía el exponente. Así que cambié la función LINEST para usar una referencia de celda en su lugar:
=LINEST(B2:B21,A2:A21^F3, true, true)
Con Excel, generando estadísticas completas (el 4 ° parámetro hasta LINEST ):
Le digo al solucionador que maximice R 2 :
Y puede descubrir el mejor exponente. Cuál para usted datos:
es:
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 utilizar adicionalmente la función ÍNDICE. En una celda, escriba: = ÍNDICE (ESTIMACION.LINEAL (B2: B21, A2: A21 ^ {1,2}, VERDADERO, FALSO), 1) (por cierto, el B2: B21 y A2: A21 que utilicé son solo los mismos valores que utilizó el primer póster que respondió esto ... por supuesto, cambiaría estos intervalos de forma adecuada para que coincida con sus datos). Esto da el coeficiente X ^ 2. En una celda adyacente, escriba la misma fórmula de nuevo, pero cambie la 1 final a 2 ... esto le da el coeficiente X ^ 1. Por último, en la celda siguiente, vuelva a escribir la misma fórmula pero cambie el último número a 3 ... esto da la constante. Observé que los tres coeficientes son muy cercanos, pero no del todo idénticos a los derivados al usar la función de línea de tendencia gráfica en la pestaña de gráficos. Además, descubrí que el 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 tenlo en cuenta si obtienes un error #VALOR.
Sé que esta pregunta es un poco antigua, pero pensé que ofrecerí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 la Herramienta 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 un cuadrático, necesitará X_1, X_2 e Y, donde X_1 es la variable xy X_2 es x ^ 2 ; Del mismo modo, si está haciendo un cubo, necesitará X_1, X_2, X_3, Y, donde X_1 es la variable x , X_2 es x ^ 2 y X_3 es x ^ 3 . Observe cómo el rango de entrada X es de A1 a B22, abarcando 2 columnas.
La siguiente imagen muestra el resultado del análisis de regresión. He resaltado los resultados comunes, incluidos los valores R-Squared y todos los coeficientes.