LISTAR TODAS LAS SUMAS USANDO FÓRMULAS

Página inicial

Para poder hacer un listado de todas las sumas posibles para unos sumandos dados es necesario, como resulta evidente, calcular todas sus combinaciones sin repetición.

En Excel esto se puede conseguir usando VBA, bien sea mediante bucles o mediante llamadas recursivas a un procedimiento. En esta página hay enlaces a libros de ejemplo que usan la segunda técnica.

Pero esto mismo se puede conseguir también utilizando sólo funciones de hoja de cálculo de Excel. Este trabajo trata sobre las fórmulas necesarias para lograrlo, aunque antes de entrar en materia conviene hacer una serie de puntualizaciones:

• Hay que tener en cuenta que todas las fórmulas de esta página son matriciales, por lo que hay que introducirlas pulsando mayúsculas control entrada al mismo tiempo. Por convención, para indicar que una fórmula es matricial se la encierra entre llaves { }, pero no hay que teclearlas al introducir la fórmula; deberían aparecer automáticamente al introducirla pulsando la combinación de teclas indicada.

• La fórmula para conseguir la lista de todas las sumas posibles no se puede usar en las versiones de Excel anteriores a la 2007, debido a que supera el límite de siete niveles de anidamiento, que es el tope en dichas versiones.

• Los puntos y comas de las fórmulas podrían tener que ser sustituidos por comas: esto dependerá del separador de listas que esté establecido en la configuración regional del equipo.

• Si el número de sumandos es grande, serán necesarias miles de fórmulas, todas ellas evidentemente matriciales, por lo que el proceso de recálculo del libro donde se estén listando las sumas podría requerir bastante tiempo.

Antes de empezar con las fórmulas, un poco de teoría:

Para el ejemplo que vamos a desarrollar partiremos de un conjunto de 4 sumandos: 1, 2, 3, 4. Resulta evidente que estos números se pueden combinar en grupos de 1, 2, 3 ó 4 elementos. Recordemos que estamos hablando siempre de combinaciones sin repetición y no de permutaciones, puesto que el orden en el que estén los sumandos en cada combinación no importa debido a la propiedad conmutativa de la suma. Estas combinaciones sin repetición serían:

• 4 grupos de 1 elemento: 1, 2, 3, 4

• 6 grupos de 2 elementos: 1 2, 1 3, 1 4, 2 3, 2 4, 3 4

• 4 grupos de 3 elementos: 1 2 3, 1 2 4, 1 3 4, 2 3 4

• 1 grupo de 4 elementos: 1 2 3 4

Hay que tener en cuenta que los números de estos grupos no hacen referencia a los sumandos en sí sino a la posición que ocupa cada uno de ellos en la lista de sumandos. Es decir, la lista sería idéntica si los 4 sumandos fuesen, por poner un ejemplo, 55, 66, 77 y 88 en lugar de 1, 2, 3 y 4. En este caso, por ejemplo la 2ª combinación de 2 elementos seguiría siendo 1 3, que correspondería a 55+77 (esto es, los sumandos situados en la 1ª y 3ª posiciones).

El número total de estas combinaciones sin repetición se puede calcular en Excel utilizando la fórmula

=2^Número de elementos a combinar-1

es decir, en el caso del ejemplo que estamos viendo:

=2^4-1

que arroja el resultado de 15. Podemos ver que, en efecto, 15 es la cuenta de los grupos formados (4+6+4+1).

Lo primero que vamos a necesitar para desarrollar en Excel una fórmula capaz de ir devolviéndonos cada una de las combinaciones es ajustar la longitud de todos y cada uno de los grupos al número de sumandos, en este caso 4. Para poder ver cómo trabajará la fórmula rellenaremos con ceros a la izquierda hasta conseguir dicha longitud y situaremos cada elemento en el lugar que le corresponde dentro de las combinaciones en las que aparezca. También pondremos un cero en las posiciones correspondientes a los elementos que no van a aparecer en cada combinación:

0001, 0020, 0300, 4000

0021, 0301, 4001, 0320, 4020, 4300

0321, 4021, 4301, 4320

4321

La información en estos grupos resulta obviamente redundante, puesto que la posición de los números >0 ya está indicando que el sumando situado en dicha posición en la lista de sumandos se encuentra en cada combinación, así que no hay necesidad de especificar dicho número de posición. Por lo tanto, podemos sustituir todos los números >1 con 1, de forma que los sumandos incluidos en cada combinación estarán representados por un 1 y los no incluidos estarán representados por un 0:

0001, 0010, 0100, 1000

0011, 0101, 1001, 0110, 1010, 1100

0111, 1011, 1101, 1110

1111

Si nos fijamos un poco en estos grupos, veremos que todos ellos forman el conjunto de los números en base 2 de los números base 10 comprendidos entre el 1 y el 15 (recordemos que 15 es el número total de combinaciones sin repetición posibles para 4 elementos). Ordenados del 1 al 15 y normalizados a un largo de 4 dígitos estos números base 2 serían:

0001, 0010, 0011, 0100, 0101, 0110, 0111, 1000, 1001, 1010, 1011, 1100, 1101, 1110, 1111

Esto es aplicable a cualquier otro número de sumandos. Por ejemplo, si se tratase de 3 sumandos el número de grupos posible sería

=2^3-1

que devuelve 7 grupos, los cuales son:

001, 010, 100

011, 101, 110

111

es decir, de nuevo los números base 2 desde el 1 hasta el 7 base 10:

001, 010, 011, 100, 101, 110, 111

Por lo tanto, podemos concluir que es posible obtener todas las combinaciones sin repetición para un conjunto de n elementos calculando todos los valores en base 2 desde 1 hasta el número de combinaciones sin repetición posibles para esos n elementos (recordemos, 2ⁿ-1). Este hecho es lo que nos permitirá desarrollar la fórmula necesaria para devolver todas las combinaciones.

Y ahora, las fórmulas:

Excel, como todos sabemos, trabaja en base 10, pero dispone de una función (DEC.A.BIN) que devuelve en base 2 (o binario) el número decimal (o base 10) que se le pase como argumento. Por ejemplo,

=DEC.A.BIN(15)

devolverá 1111.

Esta función tiene el grave inconveniente de que tan sólo es capaz de procesar hasta el 511, por lo que si la usásemos en las fórmulas necesarias para devolver la lista de todas las sumas posibles el número de sumandos estaría limitado a 9 (2⁹-1=511).

Así que mejor prescindiremos de esta función, para lo que lógicamente habrá que pagar el precio de tener que desarrollar y utilizar una fórmula complicada. En esta página muestro una fórmula capaz de hacer la conversión decimal->binario hasta el 32768₁₀ (1000000000000000₂). De hecho, la fórmula podría procesar números más grandes de no ser por la precisión numérica máxima de Excel, que es de 15 dígitos (en esta página hay una explicación sobre esto).

Por ejemplo, para devolver el valor binario del número base 10 situado en A1 la fórmula sería:

{=SUMA(RESIDUO(ENTERO(A1/2^((FILA(INDIRECTO("1:"&ENTERO(LOG(A1;2))+1)))-1));2)*10^(FILA(INDIRECTO("1:"&ENTERO(LOG(A1;2))+1))-1))}

Necesitaremos anteponer una expresión adicional para añadir los ceros a la izquierda que puedan ser necesarios para que el número base 2 devuelto por la fórmula tenga tantos caracteres como sumandos estemos procesando. Para una mayor claridad en las fórmulas aplicaremos un nombre (solapa 'Fórmulas'->Asignar nombre) al rango donde se encuentran llamado "Sumandos". La expresión en este caso sería:

DERECHA(REPETIR("0";FILAS(Sumandos)-1)

a partir de ahí tendrá que ir la fórmula para calcular el valor binario que se esté procesando. Lo más cómodo es situar las fórmulas a partir de la fila 1 y utilizar la función FILA() en lugar de las referencias a la celda A1, con lo que la fórmula irá aumentando automáticamente en una unidad el valor base 10 a procesar.

Después de esto tiene que ir el último argumento de la función DERECHA, que es el número de caracteres a extraer el cual, lógicamente, se corresponde con el número de sumandos. La forma más sencilla de obtener dicho número es usar la función FILAS() sobre el rango en el que se encuentren los sumandos, en este caso el rango con nombre Sumandos.

Por lo tanto, la fórmula sería por ahora:

{=DERECHA(REPETIR("0";FILAS(Sumandos)-1)&SUMA(RESIDUO(ENTERO(FILA()/2^((FILA(INDIRECTO("1:"&ENTERO(LOG(FILA();2))+1)))-1));2)*10^(FILA(INDIRECTO("1:"&ENTERO(LOG(FILA();2))+1))-1));FILAS(Sumandos))}

Para entender mejor qué es lo que está haciendo la fórmula al llegar a este punto, pensemos que para el ejemplo que vimos antes con 4 sumandos, si se situase esta fórmula en las filas 1:15 de cualquier columna vacía devolvería los números base 2 desde el 1 hasta el 15, ordenados de forma ascendente y con tantos ceros a la izquierda como puedan ser necesarios para completar el número de sumandos.

Cada uno de los ceros y unos de los números binarios devueltos por esta fórmula hay que multiplicarlo por el sumando que ocupa la posición del dígito en cuestión, para luego sumar los resultados de estas multiplicaciones. Por ejemplo, para la fila 5 la fórmula devuelve:

0101

y cada uno de estos cuatro dígitos habrá que multiplicarlo por el sumando correspondiente para luego obtener la suma de estos productos, es decir:

0·4 = 0

1·3 = 3

0·2 = 0

1·1 = 1

        ---

         4

Para conseguir esto la fórmula quedará así:

{=SUMA(EXTRAE(DERECHA(REPETIR("0";FILAS(Sumandos)-1)&SUMA(RESIDUO(ENTERO(FILA()/2^((FILA(INDIRECTO("1:"&ENTERO(LOG(FILA();2))+1)))-1));2)*10^(FILA(INDIRECTO("1:"&ENTERO(LOG(FILA();2))+1))-1));FILAS(Sumandos));FILA(INDIRECTO("1:"&FILAS(Sumandos)));1)*Sumandos)}

Dado que el número de sumandos puede variar entre 2 y 15, esta fórmula habría que pegarla desde la fila 1 hasta la fila 32.767 de la columna elegida (2¹⁵-1 = 32767); pero para evitar que calcule los binarios correspondientes a los números base 10 que superen el de combinaciones posibles en función del número de sumandos es necesario anidarla dentro de una función SI, con lo que quedará:

{=SI(FILA()<2^FILAS(Sumandos);SUMA(EXTRAE(DERECHA(REPETIR("0";FILAS(Sumandos)-1)&SUMA(RESIDUO(ENTERO(FILA()/2^((FILA(INDIRECTO("1:"&ENTERO(LOG(FILA();2))+1)))-1));2)*10^(FILA(INDIRECTO("1:"&ENTERO(LOG(FILA();2))+1))-1));FILAS(Sumandos));FILA(INDIRECTO("1:"&FILAS(Sumandos)));1)*Sumandos))}

La suma que debería arrojar la columna en la que estén estas fórmulas debería ser:

=Suma de los sumandos a combinar*2^(Número de sumandos-1)

así que si está creado el rango con nombre Sumandos la fórmula sería:

=SUMA(Sumandos)*2^(FILAS(Sumandos)-1)

que para el caso de los sumandos 1, 2, 3 y 4 debería ser 80 (10·2³ = 80)

En este libro de ejemplo se pueden ver estas fórmulas trabajando. Sólo funcionarán en Excel 2007 y 2010.

Listar solamente las sumas con un número determinado de sumandos

Conseguir un listado con sólo las sumas para un número determinado de sumandos es algo que no se podría lograr directamente con la fórmula anterior, ya que como hemos visto su funcionamiento se basa en el cálculo y listado de todos los números binarios desde 1 hasta el de combinaciones posibles para el número de sumandos dado.

Está claro que sí se podría hacer añadiendo una columna en la que contar dicho número de sumandos y luego filtrar por ella. Por ejemplo para el libro de ejemplo vinculado un poco más arriba, podríamos utilizar la columna Z y poner a partir de Z1 la fórmula:

=LARGO(SUSTITUIR(H1;"0";""))

que habría que copiar y pegar hasta Z30 (o hasta la fila en la que aparezcan sumas >0).

Una vez hecho esto, se podría crear un filtro en esa columna Z y filtrar por el número de sumandos deseados, con lo que sólo quedarían visibles las sumas obtenidas al combinar ese número en concreto de sumandos. Pero esta fórmula sería muy ineficiente en este caso, ya que, como es obvio, para obtener las sumas correspondientes a n sumandos sería necesario generar las sumas con cualquier número de sumandos.

Por ejemplo, para 15 sumandos, utilizando la fórmula anterior serían necesarias 32.767 fórmulas (2¹⁵-1), mientras que si se pudiese obtener directamente tan sólo las combinaciones con el número deseado de sumandos tan sólo serían necesarias 6.435 fórmulas como máximo (esto es, para combinaciones de 7 u 8 elementos: 15!/7!·(15-7)! = 6.435, donde sustituyendo 7 por 8 se obtiene el mismo resultado). Es decir, en este caso cuatro de cada cinco de las fórmulas generadas utilizando la técnica anterior resultarían innecesarias. En Excel se puede obtener directamente este resultado mediante:

=COMBINAT(15;7)

y

=COMBINAT(15;8)

Como estamos utilizando números binarios para conseguir las combinaciones, lo que necesitaríamos es que la progresión de esos números fuese la de binarios que tuviesen tantos unos como el número de sumandos a combinar. El caso más extremo es posiblemente el más esclarecedor, incluso resultando tan ridículo como resulta: pongamos que deseásemos conseguir las combinaciones de 1 elemento para 4 sumandos.

Resulta evidente que esas combinaciones serían 4, tan evidente que no hace falta recurrir a la fórmula para averiguarlo, pero de todas formas lo haremos: 4!/1!·(4-1)! = 4, resultado que en Excel se puede obtener mediante:

=COMBINAT(4;1)

Los números binarios (de 4 dígitos puesto que se trata de 4 sumandos) para esas 4 combinaciones serían, lógicamente (ordenados en orden ascendente):

0001

0010

0100

1000

Como Excel trabaja en base 10, podemos decir que las diferencias calculadas en base 10 entre cada combinación y la que la precede son 9, 90 y 900. Así que sería posible ir obteniendo el siguiente binario que tenga tan sólo un 1 y todas sus demás cifras sean 0 averiguando cuál de esas tres posibles diferencias es la más pequeña que sería necesaria: partiremos del

0001

y ahora sumémosle 9, 90 y 900:

0001 + 9 = 10

0001 + 90 = 91

0001 + 900 =901

Así que está claro que necesitaremos sumar 9, con lo que conseguimos el 0010. Ahora le sumamos de nuevo 9, 90 y 900:

0010 + 9 = 19

0010 + 90 = 100

0010 + 900 = 910

El siguiente número que cumple la condición de tener un sólo uno, siendo todas sus demás cifras ceros, es obviamente el 0100, al que le sumaremos de nuevo las tres diferencias para obtener el último:

0100 + 9 = 109

0100 + 90 = 190

0100 + 900 = 1000

Ahora veamos qué sucedería si quisiésemos obtener todas las sumas posibles de dos sumandos procedentes de un conjunto de 4. En este caso serían necesarias 4!/2!·(4-2)! = 6 combinaciones. La primera combinación sería lógicamente 0011, puesto que esa primera combinación será siempre la formada con tantos unos como sumandos deba tener cada una de las combinaciones (en este caso, dos).

La 2ª combinación es 0101, así que la diferencia, calculada en base 10, entre esos dos binarios sería 101 - 11 = 90

La 3ª combinación sería 0110, cuya diferencia con la combinación que la precede es 110 - 101 = 9

La 4ª combinación sería 1001, y la diferencia con la anterior 1001 - 110 = 891

La 5ª combinación es 1010, y la diferencia 1010 - 1001 = 9

Y la 6ª y última combinación es, lógicamente, 1100, siendo la diferencia con la anterior 1100 - 1010 = 90

Así que de nuevo han sido necesarios 9, 90 y 900, con el añadido del 891.

El límite al que se puede llegar en Excel utilizando binarios para generar las combinaciones es, como ya vimos, 15 sumandos. La pregunta es, por tanto ¿cuántas posibles diferencias (calculadas, insisto, en base 10) hay entre los binarios necesarios para esos 15 sumandos?. Pues no demasiadas, en concreto cincuenta y seis:

Con estos 56 números estamos en codiciones de generar todas las combinaciones necesarias en función del número de sumandos y de la cantidad de los mismos que deseemos tenga cada combinación, y lo bueno de esta técnica es que no hará falta obtener las combinaciones que no tengan ese número de sumandos deseado.

Para el desarrollo de la fórmula que necesitaremos para ir obteniendo cada combinación situaremos esos 56 números en un rango y le aplicaremos el nombre Números_a_sumar. En el libro de ejemplo que vincularé más adelante ese rango es Binarios!I1:I56

En una hoja que llamaremos Elementos_a_sumar pondremos, a partir de A1, los sumandos (mínimo 2 y máximo 15, sin dejar celdas vacías entre medias).

En la celda A1 de la hoja Binarios pondremos tantos unos como sumandos deseemos que tengan las combinaciones (por ejemplo, si deseamos que las combinaciones sean de 4 sumandos podremos en A1 1111).

En la celda A2 de esta misma hoja es donde tendrá que ir la fórmula que nos devolverá el siguiente binario con tantos unos como los que tenga A1. Ahora desarrollaremos esta fórmula.

Lo primero que debe quedar claro es que la fórmula tendrá que ser matricial, dado que tendrá que sumar los 56 números del rango con nombre Números_a_sumar al binario que se encuentre justo encima de la celda donde vaya cada fórmula y elegir de entre esas sumas la que devuelva el menor de los binarios con tantos unos como elementos a combinar.

La expresión para la suma en sí es bastante sencilla:

A1+Números_a_sumar

Para contar los unos de cada una de las sumas obtenidas necesitaremos dos pasos. El primero será eliminar todos los ceros, con lo que la expresión será:

SUSTITUIR(A1+Números_a_sumar;"0";"")

El segundo paso será quitar todos los unos a los números obtenidos en el paso anterior, así que la expresión será de momento:

SUSTITUIR(SUSTITUIR(A1+Números_a_sumar;"0";"");"1";"")

Al llegar a este punto, todos los números "binarios" (es decir, en realidad números base 10 pero constituidos tan sólo por unos y ceros) estarán representados como cadenas de longitud cero ("") en la matriz creada por la fórmula. Así que ahora tenemos que conseguir que estas cadenas de longitud cero sean sustituidas por el o los números del rango con nombre Números_a_sumar que las han provocado. La expresión será:

SI(SUSTITUIR(SUSTITUIR(A1+Números_a_sumar;"0";"");"1";"")="";Números_a_sumar)

Para averiguar cuál es el más pequeño de los números "binarios" en la matriz así obtenida, lo único que necesitaremos es la función MIN:

MIN(SI(SUSTITUIR(SUSTITUIR(A1+Números_a_sumar;"0";"");"1";"")="";Números_a_sumar))

Y ya sólo queda sumar el número obtenido a la celda anterior (en este ejemplo, A1). Así que la fórmula para A2 será:

{=A1+MIN(SI(SUSTITUIR(SUSTITUIR(A1+Números_a_sumar;"0";"");"1";"")="";Números_a_sumar))}

Esta fórmula tendría que ser copiada y pegada hacia abajo, hasta la fila necesaria para que aparezcan todas las combinaciones. Como ya he dicho, este número depende obviamente de dos parámetros: el número de sumandos a combinar y la cantidad de esos sumandos que deberá tener cada combinación.

La fórmula para averiguar cuántas filas serán necesarias es:

=COMBINAT(CONTARA(Elementos_a_sumar!A:A);LARGO(A1))

Pero como casi siempre uno o ambos parámetros podrán variar, lo más sencillo es copiar y pegar la fórmula que va generando cada binario hasta la fila 6435, que como vimos antes es el máximo posible de combinaciones. Es lo que he hecho en el libro de ejemplo, que está en un fichero comprimido ya que "pesa" bastante.

En este libro de ejemplo hay formatos condicionales en las columnas A de las hojas Binarios y Sumas para que aparezcan con fondo rojo las filas que superen el número de combinaciones posible para el número de sumandos y el de sumandos de cada suma buscados.

Para terminar, he aquí la fórmula que nos dirá cuál debería ser la suma de todas las sumas para el número de sumandos deseado:

=COMBINAT(CONTAR(Elementos_a_sumar!A:A)-1;LARGO(A1)-1)*SUMA(Elementos_a_sumar!A:A)

Página inicial