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 si el sumando situado en dicha posición en la lista de sumandos se encuentra o no 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, desordenado (en realidad sí están ordenados, pero en orden lexicográfico), 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, 2n-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 (29-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 3276810 (10000000000000002). 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.
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 (215-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·23 = 80)
En este libro de ejemplo se pueden ver estas fórmulas trabajando. Sólo funcionarán en Excel 2007 y 2010.