martes, 4 de octubre de 2011

Conciliar una partida con Solver.

Vamos a ver cómo podemos encontrar, de un listado de valores, aquellos que suman una cantidad definida por nosotros; esto es, vamos a conciliar una partida con un listado de valores. Para esto emplearemos Solver.
Si bien, hay que advertir que esta aplicación será válida sólo en aquellos casos que la combinación de valores que suman el valor a conciliar (el valor buscado) es única.
Veamos nuestro listado de valores:

Conciliar una partida con Solver.


Convertiremos nuestro listado en una Tabla (Ctrl+q), y luego con la Tabla seleccionada, Insertaremos una columna de tabla a la derecha, que nombraremos como 'binario'.
También asignaremos nombre a los dos campos:
binario =Hoja1!$B$2:$B$17
Cantidades =Hoja1!$A$2:$A$17
Por último, como parte importante a la hora de configurar Solver, en la celda D2 insertaremos la función:
=SUMAPRODUCTO(binario;Cantidades)
esta función se encargará de multiplicar fila a fila, elemento a elemento, los valores del rango 'binario' y del rango 'Cantidades'.
Esta celda, será considerada en la configuración como la Celda objetivo.
Ejecutamos Solver, desde Datos > Análisis > Solver:

Conciliar una partida con Solver.


Vemos que hemos seleccionado como celda objetivo la celda $D$2, donde se encontraba la función SUMAPRODUCTO. Además, como Celdas cambiantes hemos seleccionado el rango binario; la consecuencia es que Solver completará este rango con los valores necesarios para que cumplan el objetivo y sus condiciones.
El objetivo para nuestro ejemplo es encontrar aquella combinación de cantidades que sumen 415,09.
La única condición que damos es que cada uno de los valores del rango 'binario', tras la ejecución de Solver únicamente podrán ser o ceros o unos (0 ó 1), es decir, un número binario. Esto lo conseguimos agregando la siguiente condición:

Conciliar una partida con Solver.


Ya estamos en disposición de Resolver. Obteniendo la siguiente ventana de información:

Conciliar una partida con Solver.


El resultado es que ha encontrado la combinación de valores del campo 'Cantidades' que suma 415,09:
415,09 = 126,35 + 71,00 + 117,75 + 99,99

Conciliar una partida con Solver.


Vemos como Solver ha añadido ceros y unos en el rango 'binario' hasta encontrar la combinación por la que SUMAPRODUCTO(binario; Cantidades) = 415,09

21 comentarios:

  1. Que buen ejemplo del uso de Solver! Gracias!

    ResponderEliminar
  2. Qué bien nos ha venido este ejemplo!!! Justo lo que necesitábamos.
    Nos viene fenomenal para las revisiones de los mayores.
    ¿podríamos hacer que las posibilidades de suma fueran por un aproximado de por ejemplo 5 unidades arriba y 5 abajo?

    ResponderEliminar
  3. Hola,
    bueno, si te refieres al acumulado (a la suma) total, se podría intentar.
    Si bien, habrá que tener en cuenta que probablemente no existirá una sóla combinación resultante, por lo que la validez del método quede entredicho.
    Subiré en próximos días un ejemplo limitando el resultado.
    Slds

    ResponderEliminar
  4. Hola, el problema que yo tengo es que no me devuelve ceros y unos, a que suele ser debido? Entiendase que ademas tengo una restricción por la que limita las combinaciones de ceros y unos en la matriz a buscar (no es solo una columna...), creo que estoy metido en un buen sarao! a ver si alguien puede darme una pista, tanto con resolver lineal o no me salen valores entre cero y uno pero no el puro binario,...

    ResponderEliminar
    Respuestas
    1. Hola,
      si has compuesto las restricciones correctamente, incluyendo valores como binario, y aún así no funciona como esperas, quizá deberías revisar la estructura del modelo... recuerda que Solver intenta cumplir y verificar las condiciones marcadas, pero si no es posible su prioridad es resolver el modelo 'como sea'.
      Slds

      Eliminar
  5. Buenos dias Excel foro...soy fredy de Colombia, revise este link que me habian dado anteriormente y efectivamente, excel me arroja que existe un resultado, pero no arroja los ceros y los unos. A que se debe este error ??? creo que tiene que ver con el error de funcion sumapromedio que aparece #nombre. les quedo agradecido !!!

    ResponderEliminar
  6. Buenas tardes...soy el del anterior comentario. Descubri el error, tonto pero ya lo solucione. Ahora el problema es que tengo 5.000 registro y hay mas de una respuesta. Que formula puedo utilizar en ese caso ??? Gracias. Fredy - Colombia

    ResponderEliminar
    Respuestas
    1. Hola Fredy,
      el problema de emplear este sistema de conciliación con Solver, es que sólo podemos obtener un resultado... si hay varias respuestas deberías concretar más las restricciones para apuntar a la correcta.
      Slds

      Eliminar
  7. Demasiadas celdas de variables. A que se refiere ????

    ResponderEliminar
    Respuestas
    1. Entiendo que estás aplicando 5000 celda cambiantes, probablemente ese mensaje provenga de ese número demasiado alto para el cálculo.
      Slds

      Eliminar
  8. Te puedo anexar algun correo un archivo donde pueda hacerme explicar mejor ??? Gracias

    ResponderEliminar
    Respuestas
    1. Fredy,
      envíame el fichero a
      excelforo@gmail.com
      Slds

      Eliminar
  9. Excel. Le envie ya el fichero !!!

    Mi gracias. Saludos

    ResponderEliminar
  10. hola, solver adivina que cantidad estoy buscando????? o en que momento debo especificar la cantidad buscada. eso no lo explica.

    por ejemplo: como solver sabe que debe busca la cantidad 415.09???? o en que parte la especificaron??

    ResponderEliminar
    Respuestas
    1. Hola,
      si te fijas en las imagenes, en la celda D2 está el producto, al que en la segunda imagen, en el campo Valor de la celda objetivo he definido el valor 415.09 (está remarcado en rojo).
      Slds

      Eliminar
  11. SI, SABES POR QUE LO PREGUNTO, ES QUE NO SE SI SEA ERROR EN MI COMPU PERO JUSTAMENTE ESA IMAGEN NO ESTA DISPONIBLE ENTONCES DESDE AHI PIERDO EL SEGUIMIENTO LAS DEMAS IMAGENES SI LAS PUEDO VER. DIME, HABRA OTRA MANERA DE VER ESA IMAGEN?????

    ResponderEliminar
    Respuestas
    1. Es algo raro que no puedas ver una de las imagenes ¿¿???.
      Prueba a hacer clic en cualquiera de las otras imagenes, te debería abrir una especie de carrusel con todas las fotos del post...
      Si sigues sin poder verla, revisa otras entradas donde se explica el uso de Solver, en ellas podrás ver alguna imagen similar.
      Slds

      Eliminar
  12. QUE TAL BUEN DIA LA FORMULA SALE A LA PERFECCION PERO YO TENGO ACERCA 4000 CANTIDADES PERO AL MOMENTO DE USAR EL SOLVER ME DICE QUE SON DEMASIADAS CELDAS DE VARIABLES Y NO ME LAS ENCUENTRA CANTIDAD QUE REQUIERO QUE PUEDO HACER ME PODRIAN AYUDAR,
    GRACIAS.

    ResponderEliminar
  13. Hola,
    es cierto que la herramienta Solver no está preparada para un uso, tan exhaustivo, como el que quieres darle... realmente tu necesitarías una aplicación muy específica de conciliación profesional...
    Probablemente a través de un código de programación personalizado se pudiera hacer algo, pero se requieren más conocimientos de programción de los que yo tengo para darte una respuesta.
    Lo siento.
    Un cordial saludo

    ResponderEliminar
  14. Hola por qué solver me devuelve a veces numeros muy elevados o numeros muy bajos (negativos)?????

    ResponderEliminar
    Respuestas
    1. Hola Jorgevt571,
      si Solver te devuelve diferentes valores con una misma configuración, debes fijarte en la ventana diálogo, y leer si ha encontrado una solución única o sencillamente una solución que cumple con todas las restricciones dadas.
      En tu caso, seguramente, sea la segunda opción. Esto significa que a tu modelo le faltan restricciones para lograr una única solución.
      Recuerda que Solver en esencia resuelve sistemas de ecuaciones, por tanto, es posible tener infinitas soluciones, si el sistema de ecuaciones (el modelo planteado) no es determinado...
      La solución pasa por revisar el modelo e intentar cerrarlo añadiendo nuevas condiciones.
      Slds

      Eliminar