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:
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:
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:
Ya estamos en disposición de Resolver. Obteniendo la siguiente ventana de información:
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
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
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:
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:
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:
Ya estamos en disposición de Resolver. Obteniendo la siguiente ventana de información:
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
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
Que buen ejemplo del uso de Solver! Gracias!
ResponderEliminarQué bien nos ha venido este ejemplo!!! Justo lo que necesitábamos.
ResponderEliminarNos 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?
Hola,
ResponderEliminarbueno, 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
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,...
ResponderEliminarHola,
Eliminarsi 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
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 !!!
ResponderEliminarBuenas 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
ResponderEliminarHola Fredy,
Eliminarel 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
Demasiadas celdas de variables. A que se refiere ????
ResponderEliminarEntiendo que estás aplicando 5000 celda cambiantes, probablemente ese mensaje provenga de ese número demasiado alto para el cálculo.
EliminarSlds
Te puedo anexar algun correo un archivo donde pueda hacerme explicar mejor ??? Gracias
ResponderEliminarFredy,
Eliminarenvíame el fichero a
excelforo@gmail.com
Slds
Excel. Le envie ya el fichero !!!
ResponderEliminarMi gracias. Saludos
hola, solver adivina que cantidad estoy buscando????? o en que momento debo especificar la cantidad buscada. eso no lo explica.
ResponderEliminarpor ejemplo: como solver sabe que debe busca la cantidad 415.09???? o en que parte la especificaron??
Hola,
Eliminarsi 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
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?????
ResponderEliminarEs algo raro que no puedas ver una de las imagenes ¿¿???.
EliminarPrueba 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
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,
ResponderEliminarGRACIAS.
Hola,
ResponderEliminares 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
Hola por qué solver me devuelve a veces numeros muy elevados o numeros muy bajos (negativos)?????
ResponderEliminarHola Jorgevt571,
Eliminarsi 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
Hola!! Como puedo usar solver para que me busque los valores que suman cero en un rango?? Gracias!!!
ResponderEliminarHola,
Eliminarsigue las indicaciones pero en lugar de poner 415 pon cero en el cuadro Valor de celda objetivo
Saludos
Gracias. No funciona, cambia los unos por ceros, y la sumaproducto me da cero, entonces siempre encuentra una solucion que es la suma de todos los ceros.
Eliminarañade alguna otra restricción indicando, por ejemplo, que la suma de los 0 y 1 (B2:B17) tiene que sumar siempre 1 o más..
EliminarSlds