Regresión Polinómica excel crear gráfico

[SchroederAaron]

Hola a todos, estaba viendo la forma de encontrar los coeficientes de una regresión polinómica partiendo de datos de una tabla, para este caso se necesita un polinómio de cuarto grado, mando una hoja de Excel con los datos, gracias y espero su ayuda

[César Krall]

Hola, ¿tienes una descripción de la metodología? ¿qué problema es el que se te presenta? Saludos

[SchroederAaron]

Precisamente eso es lo que necesito, cuando se realiza una gráfica y le das click con el botón derecho y seleccionas la opción "Agregar línea de tendencia" y seleccionas la opción Polinómica Orden 4, y las opciones de "Presentar ecuación en el gráfico" y "Presentar el valor R".

Con estas opciones aparece una ecuación en el gráfico, la cual es una ecuación polinómica de cuarto orden, lo que necesito extraer son los coeficientes de esta ecuación.

Me imagino que Excel tiene una rutina para sacar esa ecuación o algo así.

[César Krall]

¿Pero no eres tú quien ha creado los cálculos y procedimientos?

[SchroederAaron]

No yo no he creado ningún calculo ni procedimiento, lo que necesito es una forma de sacar los coeficientes del polinomio creando una formula o procedimiento.

En el archivo de Excel que he voy a adjuntar, esta explicito lo que se necesita, y explico un poco más a detalle.

En la Hoja, el Rango $B$3:$D$49 es la tabla de datos con los que se construye la gráfica para sus correspondientes curvas f(x) y g(x), después le agrego la linea de tendencia a f(x) y  g(x) (polinomio de 4to grado), que es el que más se ajusta por el tipo de curva.

Excel crea una ecuación a partir de una regresión polinómica y presenta el valor de R cuadrada, lo que necesito o quiero hacer es, determinar los coeficientes de esta regresión polinómica y el valor de R cuadrada sin hacer el procedimiento de: agregar la línea de tendencia, agregar ecuación al gráfico y presentar el valor de R cuadrada en el gráfico.

El ejemplo son datos de una presa de áreas-elevación y capacidad de almacenamiento; como tengo que analizar más de 100 presas necesito sintetizar esto, gracias por tu ayuda.

P.D. creo que desde el archivo adjunto se entiende mejor.

[César Krall]

Hola, es complejo porque tienes que ir analizando paso a paso qué es lo que hace la hoja de cálculo y si no lo has hecho tú puede resultar difícil.

Veo que usan caracteres comodín como #, hay varios caracteres comodín:

1 - Cierre interrogación (?): representa cualquier carácter único.
2 - Asterísco (*): puede indicar cero o más caracteres (indeterminados).
3 - Almohadilla (#): Cualquier dígito único
4 - Cadena entre corchetes ([lista de caracteres]): Cualquier carácter único en la lista dada de caracteres.
Esta 'lista de caracteres' puede especificar un rango de caracteres mediante el uso de un guión (-) para separar los límites superiores e inferiores del rango, pero deben aparecer en orden de clasificación ascendente (por ejemplo, A-Z ó 0-100).


La gráfica parece estar creada usando la herramienta de gráficos de Excel, que te crea automáticamente la línea de ajuste según el criterio que tú le indiques.

Por ejemplo, supón que tienes estos datos:


NAMINO      
x   f(x)   g(x)
14,4929   1933781   0,023650685
25,1152   2302609   0,034420102
37,5177   2662270   0,044471299
51,784   3047270   0,053626613
68,0168   3439220   0,062409679
86,1872   3834070   0,070937919
106,4145   4266705   0,078705286
128,6559   4663158   0,087065337





Ahora seleccionas en la ficha Insertar de Excel, opción Gráficos:

XY dispersión -- > Dispersión sólo con marcadores

Ahora en la ficha diseño (teniendo seleccionado el gráfico) eliges "Seleccionar datos" y seleccionas las columnas x , f(x) y g(x) pulsas "Aceptar"

Para que se vea con detalle pulsa sobre el eje y escoge "Dar formato a eje" y selecciona: unidades de visualización: 10000



Ahora pulsas sobre la línea del gráfico y eliges "Agregar línea de tendencia". Elige la opción "Polinómica" y en ordenación elige: 4 (para que sea un polinomio de grado 4).

En la parte inferior, activa "Presentar ecuación en el gráfico" y "Presentar el valor R cuadrado en el gráfico", elige color de línea y estilo de línea y pulsa aceptar.

En la parte inferior selecciona la otra serie de datos y haz lo mismo.

Te aparecerán los coeficientes de los polinomios de ajuste.

Adjunto archivo con el ejemplo que he hecho. Saludos.

[SchroederAaron]

De acuerdo eso lo se hacer, pero lo que necesito es sacar los coeficientes sin el gráfico, ni agregando línea de tendencia, sino con alguna secuencia, arreglo o formula

[César Krall]

Puedes intentar obtener coeficientes así:

selecciona los datos, por ejemplo de aquí tomaríamos los datos (sólo los numéricos)

NAMINO   
x                    f(x)
14,4929   1933781
25,1152   2302609
37,5177   2662270
51,784   3047270
68,0168   3439220
86,1872   3834070
106,4145   4266705
128,6559   4663158


Supongamos que los datos van de la celda B4 a C11

Escribe ahora en una celda (por ejemplo en G4):

=ESTIMACION.LINEAL(C4:C11;B4:B11^{1;2;3;4};1;1)


Donde C4:C11 son los valores de f(x)

B4:B11 son los valores de x

El símbolo "^" es el que dice a la función que vamos a hacer un ajuste polinómico. Entre corchetes "{}" se dicen los grados que

se van a ajustar. En este caso, el polinomio contiene coeficientes para el segundo grado, primer grado y término independiente.

{1;2;3;4} indica que vamos a ajustar 4 grados

1;1 indica que se tome en cuenta el término independiente y cálculo de errores

Ahora sitúate sobre la celda G4, donde habrá aparecido el valor -0,0081372 que corresponde al coeficiente de x^4 de la curva de

regresión y expande la selección a las celdas H4, I4, J4, y K4 (que estarán vacías).

Pulsa F2 y a continuación CTRL + Shift + Enter

Una vez hecho esto en H4 tienes el coeficiente de x^3 que será 2,673047477, en la celda I4 el coeficiente de x^2 que será -363,7578248, en la celda J2 el coeficiente de x y en K4 el término independiente.

Estos coeficientes son los mismos que aparecen si se hace por el método del gráfico indicado antes.

Adjunto archivo excel con ejemplo.

[SchroederAaron]

Perfecto, ya salio el problema, gracias....

[benja.dl]

Buena la explicación, a mi también me sirvió ahora que le necesitaba

Solo un detalle en {1;2;3;4}, realmente va \  , como pude notar en el excel.