Averiguar qué números sumar para obtener un resultado.
En el rango A1:A10 de una hoja de cálculo
tenemos una serie de números enteros:
y queremos averiguar si sería posible obtener un resultado determinado sumando
algunos de ellos, por ejemplo 32.
Podemos conseguirlo usando uno de los complementos que vienen en la instalación
de Excel: Solver.
Si no lo tenemos instalado, lo primero que tendremos que hacer es instalarlo
desde Herramientas -> Complementos.
Rellenamos con unos las filas 1:10 de una columna, por ejemplo la B, y ponemos
el resultado, 32, en una celda, por ejemplo A12:
En otra celda, por ejemplo en B12 ponemos la siguiente fórmula:
=SUMAPRODUCTO(A1:A10;B1:B10)
Teniendo seleccionada la celda donde pusimos esta fórmula (en este caso B12),
hacemos Herramientas -> Solver, y:
- dejamos $B$12 como 'Celda objetivo:"
- 'Valores de:' 32
- 'Cambiando las celdas' $B$1:$B$10
Pulsamos el botón 'Agregar...' para agregar una restricción que será:
- 'Referencia de la celda:' $B$1:$B$10
- en el desplegable que hay a la derecha, seleccionamos 'bin', con lo que la
restricción pasará a ser de tipo binario
Pulsamos el botón 'Aceptar' para volver al formulario principal del complemento
y le damos al botón 'Resolver':
Vemos que Solver consigue encontrar una solución y nos ofrece la posibilidad de
aceptarla:
En la columna B vemos cómo algunos unos han pasado a ser ceros. Los unos que
quedan son una de (en este caso) las posibles soluciones.
He escrito una función que hace esto mismo sin necesidad de usar el Solver. Se puede ver en este libro de ejemplo, pero hay que tener en cuenta que el número de combinaciones necesarias para encontrar un resultado (o para determinar que ninguna combinación de sumandos pruduce el resultado deseado) aumenta exponencialmente de acuerdo al número de celdas. La fórmula para calcular dicho número de combinaciones es:
Número de combinaciones = 2 ^ Número de celdas - 1
de forma que, por ejemplo, para 16 celdas dicho número es 65.535, para 24 celdas (máximo permitido por la función que he escrito), es 16.777.215, para 30 celdas sería 1.073.741.823, etc.
En el libro, las celdas con los sumandos tienen un formato condicional para que se coloreen en azul las que producen la suma buscada.
Si por necesidad o por curiosidad se desease obtener todas las sumas posibles para unos sumandos dados, aconsejo la consulta de alguno de los siguientes enlaces:
-
Este libro (versión Excel 2003), en el que se pueden obtener todas las sumas mediante VBA.
-
Este libro (el mismo que el anterior pero en versión Excel 2007-2010).
-
Este artículo, en el que muestro una forma de obtener todas las sumas posibles sin usar VBA (sólo a partir de Excel 2007 y con un límite de 15 sumandos)