Enumerar combinaciones sin repetición usando funciones de hoja de cálculo.

jrgces · el 26 de noviembre

Hace algunas semanas hubo una consulta sobre cómo generar todas las combinaciones posibles en grupos de 5 mediante una macro, pero durante la discusión se tuvo que descartar la posibilidad de usar código VBA porque quien inició la consulta utiliza un mac y no le corría la macro.

En mi primera respuesta ( https://www.ayudaexcel.com/foro/excel-general-6/macro-que-genere-todas-combinaciones-posibles-5-numeros-25551/#post125849 ) contesté que para mí el principal problema era que no podía imaginar una fórmula que devolviese tan sólo las combinaciones sin repetición con el número de elementos deseado. Ofrecí una solución basada en fórmulas matriciales que devolvían (utilizando números binarios como base para las combinaciones) TODAS las combinaciones (o sea, las de 1 elemento, 2 elementos, 3 elementos...)

No he dejado de darle vueltas al tema desde entonces, y al final creo haber encontrado una solución que devuelve tan sólo las combinaciones con el número de elementos deseado. La mejora es muy importante, ya que el caso extremo que puede manejar Excel mediante fórmulas (combinaciones de 15 elementos), requeriría fórmulas en 32767 filas (2^15-1) usando la técnica que utilicé en ese hilo, pero como máximo sólo 6435 utilizando la técnica que describiré a continuación:

En una columna ponemos el conjunto de elementos que deseamos combinar, hasta un máximo de 15 (en el libro de ejemplo adjunto están en Elementos_a_combinar!A1:A15. En una celda cualquiera (en el ejemplo es Binarios!A1) ponemos tantos unos como elementos deseemos que tengan las combinaciones (es decir, si por ejemplo deseamos que cada combinación tenga 5 de esos n elementos pondríamos el número 11111).

Las fórmulas que van calculando las combinaciones sucesivas están a partir de Binarios!A2, hasta A6435. Este número de filas tan sólo sería necesario en los dos casos más extremos de entre todos los posibles, esto es un conjunto de 15 en combinaciones de 7 u 8. Para los demás casos sobrarán fórmulas, así que se pueden quitar. De todas formas, hay un formato condicional para que se muestren en rojo las celdas que ya no son necesarias. La celda Binarios!C4 calcula el número de celdas que deberían tener la fórmula. Todo el rango Binarios!A2:A6435 tiene fórmulas matriciales.

En la columna A de la hoja Combinaciones se añaden ceros a la izquierda de los números binarios que hay en la hoja Binarios para completar el número de elementos. El rango C1:Q6435 de esta hoja tiene las fórmulas para que aparezcan los elementos que componen cada combinación. Como en el caso de la hoja Binarios, las filas que superan el número de combinaciones necesario se muestran en rojo y pueden ser eliminadas si se han de enumerar combinaciones para menos de 15 elementos).

Las "tripas" de todo el sistema están en el rango con nombre Números_a_sumar (Binarios!I1:I56). Esta lista de números son todos los que pueden producir el siguiente número con tantos unos como el precedente al serle sumados, siendo ceros todos los demás dígitos. Por ejemplo, si en Binarios!A1 ponemos 11111, el siguiente número con 5 unos será lógicamente el 101111, es decir 11111+9000. Lo que hacen las fórmulas es averiguar cuál es el más pequeño de los números obtenidos al sumar a la celda precedente cada uno de los números que pueden producir “binarios” (en realidad son números base 10 --puesto que Excel trabaja en esta base-- pero constituidos tan sólo por unos y ceros).

Siguiendo con el ejemplo, el siguiente número tras 101111 con 5 unos es lógicamente el 110111 (101111+900). Hay casos en los que se obtiene más de un “binario”. Por ejemplo, con el 1100111 se obtiene tanto el 1101011 (1100111+900) como el 10100111 (1100111+9000000). De ahí que la fórmula escoja, utilizando la función MIN, el más pequeño de esos “binarios”.

Si fuesen menos de 15 elementos se podrían eliminar números en el rango con nombre Números_a_sumar. Por ejemplo, si se fuesen a combinar 10 elementos se podrían eliminar las filas 26 a 56, con lo que quizás se ganaría algo de rendimiento, aunque la verdad es que no lo comprobado.

Lógicamente, si en Binarios!A1 se pone un “binario” cualquiera, las filas siguientes mostrarán las combinaciones a partir de ese número (es decir, si por ejemplo ponemos 11011 las filas sucesivas mostrarán 11101, 11110, 100111, etc.) En este caso el cálculo devuelto por la función COMBINAT en C4 no sería correcto, pero las celdas superfluas en la columna A sí se seguirían mostrando en rojo.

El límite de 15 elementos está determinado por la precisión numérica máxima de Excel (15 dígitos + el signo), y hasta donde yo sé no puede ser superado usando funciones de hoja de cálculo.

Saludos.

Nota: el .zip pesa 272 Kb. así que no puedo subirlo. Lo he puesto en Listar_combinaciones_mediante_suma.zip

Listar_combinaciones_mediante_suma.zip

Gerson Pineda · el 26 de noviembre

Hola jrgces

Si duda un gran trabajo!

Saludos

verzulsan · el 26 de noviembre

Hola jrgces,

Me ha parecido muy ingenioso usar un contador binario para detectar los elementos únicos. Ya los había usado alguna vez para otro tipo de contadores pero de esto que te has montado no se me había pasado por la cabeza.

Te dejo mi propia versión pero sin formulas matriciales. He adaptado una fórmula que en su día subí como aporte: https://www.ayudaexcel.com/foro/ideas-aportes-64/contador-binario-formula-basada-posicion-celda-14279/

Es tu mismo archivo en donde le he agregado 2 hojas manteniendo solo la hoja Elementos_a_combinar. Lo he adaptado para que haga lo mismo que tu archivo, tan solo quedan arrastrar las primeras 20 columnas hasta 2^16=65536, y luego las columnas V:BB hasta que empiezen a soltar errores. La hoja "Panel" es un tanto chorra, tan solo sirve para decir el numero de combinaciones y esta puesto a 3 como en tu ejemplo asi que en realidad todo lo hace la hoja BinaryCont.

En esta versión no existen límites, los limites los pone la versión del Excel que uses, a mas filas, mas combinaciones. Al ser una formula independiente, el contador binario puede ser de tantos dígitos como quieras, así como los elementos. Tan solo tendrías que reajustar algunos números en las 3 formulas principales y añadir o quitar más columnas.

Saludos.

Listar_combinaciones_mediante_suma binarios_VZS.xls