sábado, 4 de junio de 2011

Ajuste Potencial

Descargar el fichero: ajuste_potencial.xlsx


Veamos el caso del ajuste de una Hipérbola. Para ello utilizaremos una línea de tendencia de tipo Potencial. Primero efectuaremos una ajuste a una hipérbola y observaremos que el coeficiente de determinación (R2) es igual a 1, lo que indica que el ajuste es perfecto. Luego dispondremos de una nube de puntos que representan valores reales que se ajustan bastante bien a una hipérbola pero no de forma perfecta.

Hoja1

Tomamos unos cuantos valores para la variable x (columna B). En la columna C establecemos los valores de la variable y, según la ecuación

y=1/x

En la celda C5 ponemos la siguiente expresión que luego copiaremos hacia abajo.

=1/B5



Creamos el gráfico de tipo dispersión con las columnas B y C. Esto da lugar a una hipérbola. Luego pulsando con el botón derecho del ratón sobre la propia gráfica azul de la hipérbola seleccionamos 'Agregar línea de tendencia'.


Elegimos el tipo Potencial para nuestro caso, ya que nuestra parábola tiene por ecuación y=x-1.


Marcamos abajo las dos casillas de verificación siguientes:

  • Presentar ecuación en el gráfico
  • Presentar el valor de R cuadrado en el gráfico

La línea negra que contiene la línea de tendencia potencial cae justo sobre la curva azul.


La correlación es perfecta y en este caso la línea de tendencia obtenida sirve como curva de interpolación porque la curva pasa por todos los puntos.


Hoja2

Vamos a crear una nueva columna denominada "y real" que representaría los valores de una nube de puntos que se ajustan bastante bien a una hipérbola pero no de forma perfecta.

Para obtener estos valores y dotarlos de cierta imperfección vamos a utilizar la expresión siguiente en la celda  D5:

=DISTR.NORM.INV(ALEATORIO();C5;C5/5)

En la columna E calculamos el Error en términos porcentuales.

Al tratarse de datos aleatorios cada vez que se pulsa F9 (tecla de recálculo manual) o se escribe algo en alguna celda, los valores obtenidos cambian.


Creamos los gráficos y la línea de tendencia de tipo Potencial.


Pulse la tecla de función F9 de forma continuada y verá como 'bailan' los datos y la curva.

El coeficiente de determinación R2 no es uno, pero es bastante cercano a uno, lo que indica que la correlación es muy buena.

2 comentarios:

  1. ... creo que ya lo tengo, pero como todo lo del blog me parece.. ¡ fantástico !
    Gracias y ojala que no te canses nunca...

    ResponderEliminar
  2. Estimado Sr. Adolfo

    Le saluda Juan Palomino, soy estudiante de Ing. Civil en Lima, Perú. Felicitaciones por su blog "excelavanzado", y muchas gracias por compartir todas estas lecciones con los demás. He revisado los post "interpolación", "ajuste potencial", entre otros, pero no he encontrado respuesta a la siguiente pregunta: ¿cómo puedo encontrar los parametros A y B de la ecuacion y=1-(0.5^(Ax))^B a partir de pares de valores X-Y (0-0,130-7%,300-25%,400-50%) ? Las opciones de línea de tendencia no me dan una ecuación de esa forma, por lo tanto no puedo encontrar los parámetros de la ecuación que se ajusten a esos pares de valores. He encontrado que podría resolver esto utilizando las funciones de excel "crecimiento" o "tendencia", pero no he tenido éxito. Muchísimas gracias por su atención y disculpe las molestias. Espero sus comentarios, saludos cordiales
    PD: Sé que el resultado del ajuste es: "A=0.00247 y B=2.63 encontrados por medio del método de mínimos cuadrados", pero necesito saber cómo llegar a esa respuesta. Nuevamente muchas gracias por su gentil ayuda.

    ResponderEliminar