combinaciones sencillas en excel

He dedicado una entrada bastante compleja, hace un tiempo atrás, sobre la forma de obtener combinaciones en base a distintos caracteres, para, por ejemplo, romper una contraseña. Y digo mal "combinación", ya que una contraseña o password o clave es, en realidad, una "permutación". ¿La diferencia? en una combinación el orden de los elementos no importa: es lo mismo mezclar tomates, cebollas y lechuga que cebollas, lechugas y tomates. Pero en una permutación no es igual "abc" que "bca". Aquí el orden sí posee importancia, y mucha. Si alguien tiene alguna duda, que la proxima vez ingrese los caracteres de su contraseña al webmail en el orden que le plazca, y verá como el ingreso será rechazado.

Un lector que no está ducho en la implementación de macros, y que a su vez posee unos pocos números a combinar, me solicita otra solución, mas sencilla. Le remiten 5 o 6 cheques por día, de distintos valores, junto a una o dos facturas de compra. El sabe que esa factura se pagó con algunos de los cheques, pero no sabe cuales. Manualmente (y con calculadora en mano) debe "mezclar" esos cheques hasta dar con los que forman el importe de la factura, que pueden ser dos, cuatro, seis, etc. La solución no es complicada (hay otra forma de solucionarlo con la herramienta "solver", pero es mas difícil) y viene de la mano de lo que autodenominé "tabla binaria". Realmente no se si se llama así, pero la bauticé de esa forma por que nunca me enteré de su verdadero nombre.

la tan "mentada" tabla binaria... o como se llame.

[+/-] Ver el resto / Ocultar

En el encabezado de mi tabla verán una progresión binaria, muy familiar en el mundo de los "bits": 1, 2, 4, 8, 16, 32, 64, 128, 256, etc, etc, etc. El ejemplo sólo abarca hasta el 16, y luego veremos el por qué.

Esas cinco "progresiones binarias" van del 1 al 16 y cada una, digamos, se corresponde con uno de los cheques a combinar. En total tengo 5 cheques. Ahora bien... con cinco elementos ¿cuantas combinaciones posibles existen? La fórmula es sencilla: 2 elevado a la n, menos 1:

(2ⁿ ) - 1 

donde n es la cantidad de elementos. En este caso, los cheques. Entonces: 

(2⁵ ) - 1 

lo que nos arroja 15 ( 2 elevado a la 5 es = 16, menos 1 = 15). O sea: con 5 elementos puedo formar hasta 15 combinaciones. Y esas quince combinaciones son las que muestro en la columna B.

Creo que hasta aquí vamos bien. ¿Y que hacen esos números binarios? Sencillo: forman cada una de las combinaciones posibles.  

Observemos el número 3: tiene un 1 debajo del 1 y del 2, ya que en binario 3 es 11. Otro ejemplo: el 5. Vemos que debajo del 1 y del 4 hay un 1 y... si, no queda otra, 5 en binario es 101.

Ese 1 que observamos dentro de la tabla actúa como un interruptor, en su fase positiva. El 0 lo hace de igual forma, pero negativo. ¿Como formamos el número 10? Si miramos la tabla, con un 1 debajo del 8 y otro debajo del 2. O sea: 8 + 2 = 10.

Resumiendo: cuando debajo de uno de los números de la progresión binaria hay un número 1, sumo.

Esto a simple vista no tiene mucho sentido, pero lo entenderán perfectamente con una pequeña modificación a la tabla:

debajo de cada "progresión binaria" coloqué los importes de los 5 cheques que mi lector recibió en uno de sus días de trabajo. A la derecha pueden ver la suma total de esos 5 valores.

Dije que cada 1 de la tabla actuaba como un interruptor en positivo, es decir, VERDADERO. Ahora solo me  queda sumar (fila x fila, hasta la nro 15 de la tabla) todos los cheques que debajo, en la tabla, tengan un 1.

Para facilitar el cálculo y omitir el uso de columnas auxiliares, aplicaré la funcion SUMAPRODUCTO(Matriz1, Matriz2, Matrizn) que multiplica y suma los elementos de dos o mas matrices:

y el resultado se presenta en la columna H: ¿que hicimos? multiplicamos la fila de los cheques por cada una de las filas binarias. Noten que la fila de los cheques tiene referencia absoluta, para que al copiar la función hacia abajo no se desplace.

Ya está, a la derecha podemos observar el resultado de todas y cada una de las combinaciones posibles. Y si lo analizamos de a poco... es muy sencillo. Veamos:

1) El primer resultado es 1238,32. Si nos fijamos bien, solo ese cheque tiene un 1 debajo, así que a ese cheque lo multipliqué con 1 mediante SUMAPRODUCTO(), a todos los demas por 0. Internamente la función hizo: (1238,32 * 1) + (3621,98 * 0) + (416,78 * 0) + (10978,32 * 0) + (862,89 * 0), lo que es igual a: 1238,32 + 0 + 0 + 0 + 0 = 1238,32

2) El segundo resultado es 3621,98. Solo encontró un 1 debajo del mencionado valor, así que se aplica el mismo criterio del punto anterior. Hay un solo 1 en toda la fila.

3) El tercer resultado es 4860,30. Y aquí está el asunto... justo debajo de los valores 3621,98 y 1238,32 se encuentran un 1. Y la función los sumó, y de allí ese 4860,30 de resultado, que no son otra cosa que la combinación (sumada, en este caso) de los citados cheques.

Si seguimos aplicando la misma lógica línea por línea, nos daremos cuenta que la función sumó, en cada línea, todos los cheques que debajo poseían un 1. Y así hasta la fila 15, completando la totalidad de las posibles combinatorias.

Para presentar este trabajo a un cliente podríamos mejorar un poco la interface:

siempre les digo que soy un verdadero animal para diseñar el entorno gráfico, pero bué... creo que mejoró un poco.

Ahora le damos la posibilidad de ingresar el monto total de la factura, al pié de la tabla.

La frase "La combinación se encuentra en la fila XX de la tabla" se logra con la siguiente función:

="La combinación se encuentra en la fila "&COINCIDIR(E20;H4:H18;0)&" de la tabla"

Busco el importe de la factura en el rango de los resultados, devolviendo la posición dentro de esa matriz. Si no hay coincidencias nos devolverá un error, así que deberemos aplicar formato condicional o tomar otros recaudos para que "no quede tan feo".

Como siempre, el link al archivo.

Que les sea de utilidad y espero sus consultas, de existir alguna.