Encuentre todas las combinaciones de números que equivalen a una suma determinada en Excel

Encuentre una combinación de números igual a una suma dada con Excel Solver

Lamentablemente, ninguna de las funciones incorporadas de Excel puede ayudarle a identificar los números que suman un total determinado. Afortunadamente, Excel proporciona un complemento especial para resolver problemas de programación lineal. El complemento Solver se incluye con todas las versiones de Excel, pero no está habilitado de forma predeterminada. Si no está familiarizado con esta herramienta, aquí tiene un excelente artículo sobre cómo agregar y usar Solver en Excel.

Con el complemento Solver activado en tu Excel, continúa con los siguientes pasos:

1. Crea el modelo.

   Para esto, ingrese su conjunto de números en una columna (A3:A12 en nuestro caso) y agregue una columna en blanco a la derecha de sus números para los resultados (B3:B12). En una celda separada (B13), ingrese una fórmula SUMAPRODUCTO similar a esta:

   =SUMPRODUCT(A3:A12, B3:B12)
   

2. Ejecute el complemento Solver.

   Sobre el Datos pestaña, en la Análisis grupo, haga clic en el solucionador botón.
   

3. Defina el problema para Solver.

   En el Parámetros del solucionador cuadro de diálogo, configure las celdas objetivo y variable:

   • En el Establecer objetivo ingrese la dirección de la celda de fórmula (B13).
   • En el A sección, seleccione Valor de y escriba el valor de suma deseado (50 en este ejemplo).
   • En el Cambiando celdas variables seleccione el rango que se completará con los resultados (B3:B12).
   
   
4. Añade las restricciones.

   Para especificar las restricciones, es decir, las restricciones o condiciones que deben cumplirse, haga clic en el botón Agregar botón. En el Agregar restricción ventana de diálogo, seleccione el rango resultante (B3:B12) y elija papelera de la lista desplegable. La restricción se establecerá automáticamente en binario. Cuando termine, haga clic en Aceptar.
   

5. Resolver el problema.

   Cuando lo llevan de regreso al Parámetro del solucionador ventana de diálogo, revise su configuración y haga clic en el Resolver botón.
   

   Unos segundos (o minutos) más tarde, el Resultados del solucionador Aparecerá el cuadro de diálogo. Si tiene éxito, seleccione el Mantenga la solución Solver y haga clic en Aceptar para salir del cuadro de diálogo.
   

Como resultado, tendrá 1 insertado junto a los números que suman la suma especificada. No es una solución fácil de usar, pero es lo mejor que Excel puede hacer.

Para fines de visualización, he resaltado las celdas que dan la suma deseada en verde claro:

Limitación: Excel Solver puede encontrar, como máximo, solo una combinación de números que sea igual a una suma específica.

Encuentre todas las combinaciones que igualen una suma determinada con una función personalizada

Para obtener todas las combinaciones posibles de un conjunto determinado de números que suman un valor determinado, puede utilizar la función personalizada a continuación. Si es nuevo en las UDF, encontrará mucha información útil en esta guía: Cómo crear funciones personalizadas definidas por el usuario en Excel.

Cómo funciona esta función

La función principal, Buscar combinaciones de suma, llama a algunas funciones subsidiarias que implementan subtareas más pequeñas. La función nombrada SumUpCombinaciones recursivas ejecuta el algoritmo central que encuentra todas las sumas posibles en el rango especificado y filtra aquellas que alcanzan el objetivo. El matriz a cadena La función controla la forma de las cadenas de salida. Tres funciones más (matriz suma, Agregar a matrizy Copiar matriz) son responsables de procesar matrices intermedias: cada vez que creamos una matriz temporal, le agregamos un elemento de la matriz de origen y verificamos si se alcanza la suma objetivo. El algoritmo central se toma de este hilo de Stackoverflow¡Gracias chicos por compartir!

Sintaxis

Desde la perspectiva del usuario, la sintaxis de nuestra función personalizada es tan simple como esta:

FindSumCombinations(rango, suma)

Dónde:

• Rango es el rango de números a probar.
• Suma es la suma objetivo.

Cómo utilizar la función FindSumCombinations:

1. Inserte el código anterior en un módulo de Código de su libro de trabajo y guárdelo como un libro de trabajo habilitado para macros (.xlsm). Las instrucciones paso a paso están aquí.
2. En cualquier celda en blanco, ingrese un Buscar combinaciones de suma fórmula y presione el botón Ingresar llave. Asegúrese de que haya suficientes celdas vacías a la derecha para generar todas las combinaciones; de lo contrario, la fórmula devolverá un error #SPILL.

Por ejemplo, para encontrar todas las combinaciones posibles de números en el rango A6:A15 que sean iguales a la suma en A3, la fórmula es:

=FindSumCombinations(A6:A15, A3)

Al igual que con cualquier otra función de matriz dinámica, ingresa la fórmula en solo una celda (C6 en la imagen a continuación) y completa los resultados en tantas celdas como sea necesario. De forma predeterminada, las cadenas separadas por comas se muestran en una fila:

Para devolver los resultados en una columna, ajuste la función personalizada en TRANSPONER de esta manera:

=TRANSPOSE(FindSumCombinations(A6:A15, A3))

Para generar las cadenas en forma de una matriz encerrada entre llaves, modifique la función ArrayToString de la siguiente manera:

Los resultados serán similares a este:

Limitación: Esta función personalizada solo funciona en Dynamic Array Excel 365 y Excel 2021.

Obtenga todas las combinaciones que equivalen a una suma determinada con la macro VBA

La función personalizada descrita anteriormente devuelve las combinaciones de números como cadenas. Si prefiere tener cada número en una celda separada, esta macro le resultará útil. El código está escrito por otro experto en Excel, Alexander Trifuntov, que ha estado ayudando activamente a los usuarios a resolver varios problemas de Excel en este blog.

Comience agregando el siguiente código a su libro de trabajo. Para conocer los pasos detallados, consulte Cómo insertar código VBA en Excel.

A continuación, cree un UserForm con el siguiente diseño y propiedades:

Después de completar el diseño del formulario, agregue el código del formulario. Para esto, haga clic derecho en el formulario en el Explorador de proyectos y seleccione Ver código:

Con el código y el formulario en su lugar, presione Alt + F8 y ejecutar el Buscar todas las combinaciones macro:

En el formulario que aparece, defina lo siguiente:

• El rango con los números de origen (A4:A13)
• La suma objetivo (50)
• La celda superior izquierda del rango de destino (C4).

Al especificar la celda superior izquierda del rango de salida, asegúrese de que haya al menos una fila en blanco arriba (para encabezados) y suficiente celdas en blanco hacia abajo y hacia la derecha. Si no hay suficientes celdas en blanco, se sobrescribirán los datos existentes.

Al hacer clic en Aceptar se producirá el resultado como se muestra en la siguiente captura de pantalla:

• En C4:C6, tienes las combinaciones de números como valores separados por comas.
• Las columnas F, G y H contienen las mismas combinaciones de números, y cada número reside en una celda separada.
• En D4, tienes la suma objetivo.

Esta forma de salida hace que sea más fácil verificar el resultado: simplemente ingrese la fórmula SUMA en la celda F13, arrástrela hacia la derecha a través de dos celdas más y verá que cada combinación de números suma el valor especificado (50).

Limitación: Para un conjunto grande de números, la macro puede tardar algún tiempo en generar todas las combinaciones posibles.

Ventajas: Funciona en todas las versiones de Excel 2010 – 365; proporciona dos formas de salida: cadenas de valores separados por comas y números en celdas separadas.

Libro de práctica

Encuentra combinaciones que sean iguales a una suma dada – ejemplos (archivo .xlsm)