Podemos ajustar una nube de puntos mediante una recta, ésta sería la típica regresión lineal y=a+bx. También podemos ajustar esa misma nube de puntos mediante un polinomio de grado dos, una parábola y=ax2+bx+c. Podemos ir subiendo el grado del polinomio a grado tres, o cuatro, o aún mayor para ver si el ajuste mejora.
En Excel disponemos de la función ESTIMACION.LINEAL que nos permite obtener los coeficientes de estos polinomios y el coeficiente de determinación R2, así como otras magnitudes estadísticas de errores y desviaciones propias del ajuste realizado. El coeficiente de correlación R es la raíz cuadrada de R2. El signo del coeficiente de correlación R nos indica si la recta es creciente (signo positivo) o es decreciente (signo negativo).
Disponemos de una nube de puntos formada por dos variables X e Y. La variable X es la variable independiente y la variable Y es la variable dependiente, que depende de X.
Disponemos de una nube de puntos formada por dos variables X e Y. La variable X es la variable independiente y la variable Y es la variable dependiente, que depende de X.
Para hacer más didáctica la explicación de la regresión polinómica hemos elegido unos valores que se ajustan perfectamente a un polinomio de grado 3. Concretamente se trata del polinomio siguiente.
y=2x3-7x2+9x+2
Pero esto no lo sabemos aún, y lo que pretendemos es ir probando grados 1, 2, 3, 4 hasta comprobar que el polinomio de grado 3 es el mejor.
Polinomio de grado 1. Línea recta
Excel nos permite construir un gráfico de tipo dispersión (XY) donde se representan las cinco parejas de puntos correspondientes a la nube de puntos. Sobre este gráfico podemos pedir, pulsando con el botón derecho del ratón, la opción 'Agregar línea de tendencia'.
Obtenemos a la derecha un menú denominado 'Formato de línea de tendencia', en el que podemos elegir que la línea de tendencia sea 'Lineal', con lo que obtendremos una línea recta.
- Presentar ecuación en el gráfico
- Representar el valor R cuadrado en el gráfico
El gráfico obtenido es el siguiente.
La ecuación de la recta tiene la siguiente expresión funcional.
y=11,8x-5,6Donde
- a es el término independiente, es el punto de corte con el eje vertical (eje de ordenadas)
- b es la pendiente de la recta, es la tangente del ángulo que forma la recta con el eje horizontal (eje de ordenadas). Si es positiva la recta es creciente, si es negativa la recta es decreciente y si es cero la recta es horizontal.
En Excel podemos calcular a y b mediante las siguientes funciones, así como r que es coeficiente de correlación y su cuadrado que es el coeficiente de determinación.
- INTERSECCION.EJE(Conocido_y; Conocido_x) → permite calcular a
- PENDIENTE(Conocido_y; Conocido_x) → permite calcular b
- COEF.DE.CORREL(matriz1; matriz2) → permite calcular r
- COEFICIENTE.R2(Conocido_y; Conocido_x) → permite calcular r2
Cálculo de los coeficientes del polinomio
La función ESTIMACION.LINEAL es una función matricial. Recordemos los tres pasos necesarios para utilizar una función matricial.- Seleccionamos las celdas donde la función matricial dejará sus resultados
- Escribimos la función matricial con sus argumentos
- Validamos pulsando simultáneamente tres teclas: CONTROL+SHIFT+ENTER
La sintaxis de función es la siguiente.
=ESTIMACION.LINEAL(conocido_y, [conocido_x], [constante], [estadística])
En nuestro caso hemos escrito la siguiente función en la Hoja1.
Hemos indicado el rango de la variable y, el rango de la variable x. Luego hemos dejado vacío el lugar correspondiente a la 'constante' lo que indica que no deseamos que se fuerce la intersección entre los ejes. Esto supone que no deseamos forzar para que la función pase por el punto (0,0). Finalmente hemos puesto un 1 o VERDADERO en 'estadística' porque deseamos que se muestren ciertos valores estadísticos de los que nos interesa especialmente el coeficiente de determinación, que aparece en color rosa, en la 3ª fila, 1ª columna de la tabla de resultados.
Con la función INDICE podemos extraer un valor individual de la tabla matricial. Esto se ha realizado en la celda F17 de la Hoja1.=ESTIMACION.LINEAL(C5:C9;B5:B9;;1)
Hemos indicado el rango de la variable y, el rango de la variable x. Luego hemos dejado vacío el lugar correspondiente a la 'constante' lo que indica que no deseamos que se fuerce la intersección entre los ejes. Esto supone que no deseamos forzar para que la función pase por el punto (0,0). Finalmente hemos puesto un 1 o VERDADERO en 'estadística' porque deseamos que se muestren ciertos valores estadísticos de los que nos interesa especialmente el coeficiente de determinación, que aparece en color rosa, en la 3ª fila, 1ª columna de la tabla de resultados.
=INDICE(ESTIMACION.LINEAL(C5:C9;B5:B9;;1);3;1)
La expresión anterior extrae el valor del coeficiente de determinación r2, celda de color rosa.
Polinomio de grado 2. Parábola
Ahora vamos a realizar una regresión polinómica de grado 2. Obtendremos una parábola de la forma:
La ecuación resulta ser la siguiente.
y=5x2-8,2x+4,4
Podemos ver el gráfico obtenido pidiendo una línea de tendencia polinómica de grado 2.
Para obtener los coeficientes del polinomio y demás magnitudes estadística utilizamos la función matricial siguiente.
=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\2};;1)
El primer argumento es el rango C5:C9 correspondiente a los valores conocidos de la variable y.
El segundo argumento es el ranto B5:B9 correspondiente a los valores conocidos de la variable x. Este rango se debe elevar a un vector, que va entre llaves {} donde aparecen dos elementos 1 y 2 separados por una barra inclinada \. Esto indica que en el polinomio existe un coeficiente (b) que acompaña a la variable x elevada a la 1, y otro coeficiente (a) que acompaña a la variable x elevada a 2.
Nota
En algunas versiones de Excel o según el idioma configurado, el separador de los elementos del vector no será la barra inclinada para ese lado (\), sino la contraria (/), o incluso el puno y coma (;) o la coma (,).
También funciona, en nuestro caso, poner la fórmula con la siguiente expresión.
=ESTIMACION.LINEAL(C5:C9;B5:B9^TRANSPONER({1;2});;1)
Recuerde que cuando se trabaja con vectores es importante manejar los vectores correctamente según sean de fila o columna. Este es el motivo por el que hemos usado
TRANSPONER y hemos separado los elementos 1 y 2 con punto y coma.Polinomio de grado 3
Efectuamos una regresión polinómica de grado 3 con la siguiente estructura.
La ecuación resulta ser la siguiente.
y=2x3-7x2+9x+2Podemos ver el gráfico obtenido pidiendo una línea de tendencia polinómica de grado 3.
Hemos utilizado la función matricial siguiente.
=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\2\3};;1)
Poniendo
{1\2\3} estamos pidiendo que calcule los coeficientes de grado 1, 2 y 3.
Podríamos no haber puesto alguno de los elementos del vector
{1\2\3}, y en ese caso se ajustaría un polinomio donde el coeficiente que no aparece se fuerza a que sea cero. Por ejemplo, podemos probar con la siguiente expresión donde falta el elemento que lleva la variable x elevada a 2.=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\3};;1)y=0,865740741x3-1,532407407x+3,75Polinomio de grado 4
Efectuamos una regresión polinómica de grado 4 con la siguiente estructura.
La ecuación resulta ser la siguiente.
y=2,87531·10-15x4+2x3-7x2+9x+2Podemos ver el gráfico obtenido pidiendo una línea de tendencia polinómica de grado 4.
Hemos utilizado la función matricial siguiente.
=ESTIMACION.LINEAL(C5:C9;B5:B9^{1\2\3\4};;1)
Poniendo
{1\2\3\4} estamos pidiendo que calcule los coeficientes de grado 1, 2, 3 y 4.
Nuestro caso es especial
Observe que el coeficiente de x4 es prácticamente cero. Con la función ESTIMACION.LINEAL obtenemos2,87531·10-15 y en el ajuste gráfico obtenemos -7·10-13. Ambos valores son muy cercanos a cero. Esto indica que no hemos ganado nada pasando de un polinomio de grado 3 a un polinomio de grado 4.Con el polinomio de grado 3 ya obtuvimos un coeficiente de determinación r2 igual a 1, que supone correlación perfecta. Este es el motivo de que al aumentar el grado del polinomio no ganemos nada, puesto que ya con el polinomio de grado 3 teníamos perfectamente explicada la nube de puntos. Todos los puntos conocidos, los pares (x,y) caen perfectamente en el polinomio de grado 3, por lo que no es necesario aumentar el grado del polinomio.
El caso que hemos planteado es muy especial ya que justamente hemos obtenido los pares (x,y) de la nube de puntos usando previamente el polinomio de grado 3.
y=2x3-7x2+9x+2
En un caso donde los datos sean más realistas será complicado llegar justo a un coeficiente de correlación igual a 1. Lo que observaremos, en general, es que aumentando el grado del polinomio se mejora el ajuste, pero aquí debemos llegar a un grado de compromiso, ya que en ocasiones pasar de grado 7 a grado 8, por ejemplo, no mejorará de forma apreciable la bondad del ajuste, no mejorando gran cosa el coeficiente de correlación. Por tanto, hemos de decidir con qué grado nos queremos quedar. Normalmente esto se dilucida aplicando un poco de sentido común, simplemente respondiendo a la siguiente cuestión ¿realmente al aumentar un grado más mejora significativamente el ajuste?
Super buena la explicación campeón. Muchas gracias, me ha quitado muchas dudas que tenía.
ResponderEliminarMe habéis salvado, por fin un lugar donde explican como obtener los errores para ajustes cuadráticos o superiores. Ni siquiera para programas de cálculo avanzados te indicaban como extraer el error asociado. Muchísimas gracias
ResponderEliminarHola, por alguna razón mi Excel no permite introducir en la fórmula las llaves {} .. Las toma como si fuese texto.
ResponderEliminarSaben qué podría estar pasando?
Gracias
Hola.
EliminarLas llaves {} no se deben introducir escribiéndolas a mano. Cuando vemos que una fórmula va entre llaves quiere decir que se trata de una función matricial. Las funciones matriciales requieren tres pasos:
1. Si la función abarca más de una celda se deben seleccionar todas las celdas donde actuará la función
2. Se escribe la fórmula
3. No se validan pulsando enter. Se validan pulsando simultáneamente las tres siguientes teclas: CONTROL+SHIFT+ENTER
Al validad la fórmula pulsando esas tres teclas verás que Excel añade automáticamente las llaves {}.
Un saludo.
Muy bonita tu explicación, pondré manos a la obra. Gracias por tu aporte.
EliminarHola.
ResponderEliminarCuando vemos una llaves en una fórmula de Excel {formula} quiere decir que se trata de una fórmula matricial. Las llaves no las debemos teclear nosostros ya que las pone Excel.
Lo que debemos hacer es escribir la fórmula sin las llaves y luego en lugar de pulsar Enter debemos pulsar tres teclas: Control+Shift+Enter. Esta es la forma en la que se introducen las fórmulas matriciales en Excel. Si todo va bien, verás que la fórmula que has puesto queda con las llaves incluidas.
Un saludo.
En serio que muchas gracias. Muy buen aporte
ResponderEliminarPues..., +10 un POST claro, funcional... útil. Queria aprender esto hace mucho...!!!! Gracias.
ResponderEliminarBuenas, por alguna razón tanto excel como hojas de cálculo de google me tiran las fórmulas de mis lineas de tendencias con una variable nueva llamada "E". Por ejemplo:
ResponderEliminarLineal: 303041x - 5,89E + 08
Polinómica grado 2: (-2374110.5783) + 6 + 2340*x + (-0.576*(x^2))
En concreto, estoy estudiando una variable de valor conocido para los años 1988, 2002, y 2018. Quiero estimar el valor de dicha variable para años intermedios.
Opté por resolver por E para obtener la función de la curva usando los datos 1988 y 2018, pero me gustaría identificar mi error para no cometerlo en el futuro... Alguien pasó ya por esta situación?
Hola Nicolás.
EliminarLa E que aparece no es una nueva variable se trata de la notación científica que utilizan Excel y todas las aplicaciones que tratan con números muy grandes o muy pequeños.
Cuando pone 5,98E +08 lo que quiere decir es 5,98*10^8. Esto es, 5,98 por diez elevado a 8. Que escrito con todas sus cifras es: 598000000. He corrido la coma dos lugares y luego he rellenado con 6 ceros, en total son las 8 cifras que se indican en la notación científica.
Espero que esto te ayude con tus cálculos.
Un saludo.
Estuve ensayando su método con grado 6 y las constantes que calcula (a,b,c,d,e y f) difieren muchísimo de la formula expresada en la gráfica de tendencia polinómica grado 6 que arroja excel, la cual es mucho más ajustada a la realidad y, por ende, espero a una proyección más fiel. alguna idea para realizar el ajuste? Con muchos datos la formula expuesta termina dibujando media parábola. Quedo atento, muchas gracias
ResponderEliminarHola Andres.
EliminarSería cuestión de ver el caso concreto, con los datos que estás manejando. Se me ocurre que lo pruebes a calcular en otro lenguaje de programación, por ejemplo, en R o en Python, o bien usando alguna calculadora programable para las que habitualmente existen rutinas que calculan las regresiones polinómicas o de cualquier tipo.
Por otro lado, siempre tenemos que distinguir entre un ajuste cuadrático y una interpolación. Supongo que lo que buscas es un polinomio de grado 6 que ajuste por el método de los mínimos cuadrados, usando una gran nube de datos. Mira también el error que se obtiene. Se supone que si todo lo hemos realizado correctamente ese error será el mínimo posible con esos datos.
Suerte con tu caso.
Un saludo.
Muchas gracias, llevo años con el excel y la estadística y me sigue maravillando lo que se llega a hacer y las funciones programadas.
ResponderEliminarOle y ole, gran post! Me estaba volviendo loco además con el caracter "\" vs "," y tu nota me ha ayudado! jeje
ResponderEliminarEXCELENTE MUY BUENA LA EXPLICACION.
ResponderEliminarME DA PROBLEMAS CUANDO LE COLOCO LAS BARRAS INCLINADAS Y EL GRADO NO ME LO ACEPTA EXISTE OTRA FORMA
EL CUADRO DE DIALOGO NO ME PERMITE COLOCAR EL GRADO DEL POLINOMIIO
ResponderEliminarComo se puede puede obtener el indice r para un ejemplo de orden 4 (por ejemplo)
ResponderEliminarusando la función indice no me la entrega
Hola.
EliminarAl inicio del post verás un link para descargar el archivo: regresionPolinomica.xlsx que te puede resultar útil ya que contiene los ejemplos que se manejan en esta entrada.
La ecuación se puede obtener con el gráfico o con la función =ESTIMACION.LINEAL que es una función matricial que requiere validar pulsando tres teclas simultáneamente CONTROL+SHIFT+ENTER.
Sigue los pasos del archivo descargable.
Un saludo.
Buenas tardes Adolfo,
ResponderEliminarEstoy realizando el ajuste de unos datos experimentales a una ecuación polinómica de segundo grado obligándola a que pase por el origen de coordenadas. De esta forma, estoy calculando los coeficientes de la curva de firma gráfica y con la función estimación lineal. Como cabía esperar me salen igual con ambos método. Sin embargo, el valor de r^2 da ligeramente diferente con un método y con el otro pero solo cuando obligo a que la curva pase por el origen. ¿A qué se debe esto?
En caso de curvas Y= AX2+BXX+C, de ocurrencias respecto al tiempo, donde eje X es fechas dd-mm-aa, como utilizar los valores de X para calcular Y?
ResponderEliminarPara graficar mejor la consulta: llevo base de datos de ocurrencias vs fechas de la enfermedad Covid en mi pais, y llego a la curva Y=215.62X2-2E+07X-4E+11 para una determinada ocurrencia, hasta la fecha última de ocurrencia es 16-mayo-2021....y deseo hacer una estimación de la ocurrencia para el 30-junio-2021; como lo trato? o como considero valor de X en la fórmula?
ResponderEliminarComo despejo x en la fórmula Y= ax2 +ax + b
ResponderEliminarMuchas Gracias por su aporte y paciencia
ResponderEliminar=ESTIMACION.LINEAL(F2:F50,E2:E50^TRANSPONER({1,2}),FALSO,FALSO), ESA ES MI FUNCIÓN Y ME DA EL ERROR #¡VALOR! NO ENCUENTRO EL ERROR
ResponderEliminar