lunes, 13 de julio de 2009

Ejemplo de Buscar objetivo.

Conviene conocer las diferentes herramientas que nos ofrece nuestro Excel, sólo así atisvaremos las grandes posibilidades que tiene esta aplicación. Le dedicaremos, a modo de introducción, un poco de tiempo a aprender el uso de una herramienta avanzada: Buscar objetivo (el hermano pequeño de Solver).
Nuestra herramienta - Buscar objetivo - no es más que un solucionador de ecuaciones, i.e., si tenemos en nuestra hoja de cálculo diferentes celdas vinculadas entre sí (es básico que exista una relación entre las celdas) y necesitamos estimar UNA de las variables para forzar un resultado concreto, aplicaremos la utilidad Buscar objetivo. He remarcado UNA variable, y es que la limitación de este Buscar objetivo es precisamente ésta, sólo nos permite reestimar UN parámetro de todas aquellas celdas vnculadas de las que hablabamos.
Como siempre planteremos un ejemplo y su solución, en este caso, empleando Buscar objetivo.
Supongamos un productor de leche de vaca, el cual produce mensualmente (de acuerdo al número de ganado que tiene) un total de 925 litros de leche. Este ganadero ha calculado sus costes fijos y variables (de producción y de distribución) y ha planteado la siguiente Cuenta de resultados, muy importante bajo la suposición que vende la totalidad de la leche producida:


Vemos que de acuerdo a sus costes y a la producción dada obtiene un resultado de 968,75 eur de pérdida.
La pregunta sería entonces ¿cuál es la cantidad de leche que debería producir nuestro ganadero para no tener ninguna pérdida?, es decir, ¿cuál es el número de litros con el que se alcanza el Punto muerto para esta situción?.
La respuesta nos la devuelve la herramienta Buscar objetivo. La ejecutaremos, para versiones de Excel 2003 y anteriores desde el Menú Herramientas - Buscar objetivo...


Vemos planteada la solución a nuestra pregunta tal cual se propone en los manuales técnicos (producir 4800 litros); nosotros le daremos solución con Excel, resolviendo matematicamente la ecuación planteada, ¿cuantos litros debemos producir para que nuestro resultado final sea Cero?.
Aplicamos Buscar objetivo y vemos qué nos pide definir previamente a su solución:


Nos solicita le indiquemos cuál es la celda objetivo sobre la que queremos forzar un resultado con el valor que queramos, cambiando alguno de los parámetros o variables del que depende este resultado (recordemos que debe existir una vinculación entre la celda objetivo y la variable a cambiar). En nuestro caso determinamos como celda objetivo la $F$8, i.e., la celda donde hemos calculado nuestro resultado. Luego definimos cuál es el valor buscado, y ya que buscamos el punto muerto lo definimos con el valor cero. Por último determinamos la variable a cambiar para obtener un resultado cero, esto es, la celda $C$2 de litros producidos. Cuando aceptemos nos dará una solución que deberá cincidir con el cálculo obtenido de los manuales técnicos, es decir, habrá que producir 4.800 litros de leche para alcanzar el resultado cero.

16 comentarios:

  1. saludos excelforo, esta muy buena la explicacion. yo utilizo mucho "buscar Objetivo" pero tengo varios dia buscando la abreviacion en el teclado ya que no me gusta utilizar el teclado. yo uso mucho el copiar, pegar, f2 y para estas funciones uso el teclado.

    favor si saben cual o cual es la abreviacion en el teclado para la funcion "buscar Objetivo" se los agradeceria que me lo hagan llegar a mi correo:

    jaimesbeni@hotmail.com

    agradeciendo de antemano.

    beni jaimes

    ResponderEliminar
  2. Hola Beni Jaimes,
    la verdad es que también he buscado por diferentes tutoriales de excel y no he dado con la respuesta. Sin embargo te propongo algunas soluciones. Una sería crearte una macro (con un teclado abreviado que tu desees) que ejecute la herramienta; o bien extraer un Nuevo menú en la barra de menús donde agregar el elemento Herramientas. Quizá, si te ocurre a menudo, deberías personalizarte un menú con todas las herramientas que más emplees.
    Daré las explicaciones precisas en una entrada posterior.
    Saludos y espero haberte ayudado en algo

    P.d.. siento la demora en la contestación, pero en Agosto es cuando más trabajo (y menos tiempo puedo dedicar al blog).

    ResponderEliminar
  3. saludos , mi nombre es rodrigo menares, mi direccion de correo es rodrigo.menares@gamil.com; que posibilidad habria que me enviaras un ejemplo de programacion lineal , de antemano , muchas gracias

    ResponderEliminar
  4. Hola Rodrigo,
    respondo y envió tu petición a través del correo.
    Espero que te sirva el ejemplo adjuntado.
    Un saludo

    ResponderEliminar
  5. HOLA, TE FELICTO POR TU PAGINA ES EXCELENTE Y AYUDA A RESOLVER TODAS LAS DUDAS.

    NECESITO QUE ME AYUDES OK. QUIERO CREAR UNA FORMULA O CONDICION QUE EN UN RANGO DE FECHA INICIAL Y FINAL PUEDA ETERMINAR UN PAGO TOMANDO EN CUENTA EL NUMERO DE CUENTA. EN UNA BASE DE DATOS, PODRIAS AYUDARME POR FAVOR ME URGE

    ResponderEliminar
  6. Hola Erick,
    discúlpame, pero no termino de entender lo que necesitas.
    Sería mucho mejor si me enviaras un ejemplo al correo:
    excelforo@gmail.com
    te contestaré lo antes posible.
    Slds

    ResponderEliminar
  7. Ok. Supongamos que esta es mi base de datos, en tonces necesitos que dentro de esas fechas establecer un rango de fecha del 5 de Feb. al 22 e feb. y que en ese rango que busque estos pagos con los numeros de cuenta de tal forma que si la cuenta se repitiera solo busque el pago en el rango de fecha que estarìa determinando, lo necesito de urgencia y luego ver la forma de crear una macro .

    Cta Fecha Cont. Fecha pgo pgoC$ Pgo$
    111 05/02/2010 15/02/2010 1000 0
    222 06/02/2010 16/02/2010 0 100
    333 08/02/2010 17/02/2010 3000 200
    444 10/02/2010 22/02/2010 0 1000

    Saludes y muchas gracias que dios te bendiga

    ResponderEliminar
  8. Hola Erick,
    si entendí correctamente, lo que deberás aplicar es una función SUMAPRODUCTO.
    Si suponemos que necesitas sumar los pagos de la cuenta 222 que se encuentren entre las fechas 5/02/2010 y 22/02/2010, plantearemos la función matricial:
    =SUMAPRODUCTO((rango cuentas=222)*(Rango fecha cont>=FECHA(2010;2;5))*(Rango fecha pago<=FECHA(2010;2;22))*(rango pago))
    con lo que obtendrás los pagos que cumplen las condiciones dadas.
    si lo que necesitas es BUSCAR y mostrar los registros deberás aplicar un Filtro
    http://excelforo.blogspot.com/2009/10/macro-de-un-filtro-avanzado-en-excel.html
    te adjunto una entrada del blog donde explico la manera de sacar un Filtro avanzado y generar una macro.
    Slds

    ResponderEliminar
  9. Buenas tardes excelForo gracias por tu ayuda, pero te envie hoy un correo para ser mas preciso podrias ayudarme por favor

    ResponderEliminar
  10. BUENAS NOCHES EXCELFORO TODAVIA NO HE RECIBIDO RESPUESTA A MI CORREO DIJISTES QUE ME LO ENVIARIAS POR EMAIL. NECESITO QUE POR FAVOR ME AYUDES REALMENTE ESTOY INTERESADO Y QUIERO APRENDER.

    SALUDES

    ResponderEliminar
  11. Ya tienes el fichero enviado.
    Queda cerrado el asunto.
    Slds

    ResponderEliminar
  12. Excel foro haciendo referencia a buscar si tengo dos costos a y b como puedo determinar el numero de diasque tengo en una celda para que los costos a y b sean iguales; o tengo que usar una funcion

    ResponderEliminar
  13. Hola Luís Enrique,
    deberías usar Solver para resolver tu cuestión...
    aunque me parece que algo te falta en tu planteamiento, probablemente algún tipo de función de Costo.
    Ya que suponiendo la celda B2 para el 'coste a' dado, y la celda C2 para 'el costo b' dado, y una única celda B3 para un 'número de días' a determinar por Solver, si en B5 =B2*B3 y en C5 =B3*C2, siendo B5 el 'Total costo a' y C5 el 'Total costo b', para en la celda D5 =B5-C5 como celda objetivo en la configuración de Solver a asignar un Valor de cero.
    Este planteamiento te diría que valor debe tener B3(núm días) para que D5 sea cero, y por tanto respondería a tu cuestión...
    aunque tal cual preguntas, fijate como queda la ecuación:
    ax - bx = 0 => x=0
    no tiene mucho sentido...
    Mándame a
    excelforo@gmail.com
    el problema completo, junto a un archivo excel.
    Slds

    ResponderEliminar
  14. hola necesito por mas ejericios si tu me puedes colaborar, pra entender mejor
    Mil gracoas!!!

    ResponderEliminar

Nota: solo los miembros de este blog pueden publicar comentarios.