Skip to content

Goal Seek (1 de 2)

julio 17, 2008

Por Macario Hernández Garza
Sistemas de Optimización y Estadística, S. C. Copyright © 2008. Todos los Derechos Reservados.

Típicamente cada hoja de Excel le permite cambiar el valor de una celda y ver cual es el efecto en las celdas dependientes. Con Goal Seek (Buscar Objetivo) de Excel  del menú Tools (Herramientas) podemos revertir este proceso. Con Goal Seek, usted puede encontrar que valor debe tener una celda, para que una celda dependiente de ésta, tenga un valor objetivo determinado.
Goal Seek trabaja únicamente con dos celdas, la celda objetivo y otra celda de la cual depende la celda objetivo y a la cual Goal Seek cambiará los valores hasta encontrar una solución, es decir un valor que satisfaga la celda objetivo.
Si tenemos una celda objetivo la cual depende de dos o más celdas, no podemos aplicar Goal Seek, debemos utilizar el add-in Solver, el cual se ha tratado en anteriores post.
Ejemplo:
Si deseamos encontrar la solución de la ecuación:

Ecuación 1

Lo podemos hacer mediante el comando de Goal Seek (Buscar Objetivo) del menú Tools (Herramientas).
Para darnos una idea de donde se encuentran aproximadamente las raíces, vamos a realizar una gráfica de la ecuación anterior, pero antes vamos a desarrollar la ecuación, para ponerla en una forma polinomial igualada a cero.
Efectuando operaciones algebraicas sobre la ecuación anterior, esta ecuación se puede expresar de la manera siguiente:

Ecuación 2

Tenemos entonces un polinomio de tercer grado, veremos cuantas raíces reales tiene.
Ahora bien, graficando en Excel, el polinomio anterior nos queda graficado de la siguiente manera:

Por la forma en la que nos quedó el polinomio en la ecuación anterior, vemos que sus tres raíces son reales, y que dos de estas raíces son negativas y una raíz es positiva. Vemos también del gráfico que la raíz que se encuentra más hacia la izquierda, se encuentra entre -12 y -11, la siguiente raíz negativa se encuentra entre -1 y 0; y finalmente, la raíz positiva se encuentra entre 0 y 1.
La información anterior nos será de mucho interés ya que al momento de usar el Goal Seek necesitaremos darle un valor inicial, valor donde empezará la búsqueda de la solución Goal Seek.
Por otra parte, es importante señalar, que Goal Seek o Solver, darán soluciones más o menos precisas, dependiendo de los valores que tengan los parámetros: Máximo de Iteraciones y Máximo Cambio. Normalmente Excel tiene estos dos valores a 100 y 0.001 respectivamente. Para este ejemplo definí Máximo de Iteraciones como 1000 y Máximo cambio como 0.0001.
Podemos hacer los cambios a estos parámetros mediante la serie de comandos de menú: Tools/Options y cuando aparece la forma seleccionamos la pestaña Calculations. En seguida se muestra la forma con los parámetros definidos.

Si el usuario requiere soluciones más precisas necesitará incrementar el número de Iteraciones máximas y disminuir el Máximo cambio. Otro aspecto importante es que debemos empezar el valor de inicio cerca de la raíz a buscar.

En la celda D7 introducimos la fórmula del lado izquierdo de la Ecuación 1. Pudimos usar la ecuación equivalente Ecuación 2, y haríamos la celda objetivo igual a cero.

=(((3-2*C7)^2)*(1-C7))/((2*C7)^2)

Esta celda objetivo lo queremos hacer igual a 16.

Vemos que hemos dado el valor inicial de -1 a la celda cambiante (changing cell), en ese valor iniciará Goal Seek, la búsqueda de la solución.

Siendo la celda activa la D7, damos las opciones de menú: Tools/Goal Seek, después de lo cuál aparece la forma:

La celda D7, queremos que tenga el valor (To Value) de 16, y cambiando la celda (By changing cell) C7, introduciendo el 16 y seleccionando la celda C7, nos queda la forma como:

Le damos click con el ratón sobre el botón Ok, después de lo cual aparece una forma con la solución:

Si estamos satisfechos con la solución, damos click con el ratón sobre el botón OK y se guarda la solución en la celda, como se muestra en seguida:

En este caso la raíz es -0.73714229, sin embargo solo se están mostrando 8 cifras decimales, el valor completo que se puede leer en la barra de fórmulas, haciendo la celda activa a la celda C7 es: -0.737142290088953.
Si no se está de acuerdo con la solución, se pueden cambiar los parámetros que antes señalamos, los cuales controlan el número máximo de iteraciones y el máximo cambio.
Procediendo de la misma forma, podemos encontrar las otras dos soluciones. Las tres soluciones con hasta ocho cifras decimales se muestra en seguida:

No comments yet

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: