¿Cómo encontrar en Excel todas las combinaciones que suman una cantidad específica?

Descubrir todas las combinaciones posibles de números dentro de una lista que sumen una cantidad específica es un reto al que muchos usuarios de Excel se enfrentan, ya sea para presupuestar, planificar o realizar análisis de datos.

En este ejemplo, contamos con una lista de números y el objetivo es identificar qué combinaciones suman exactamente 480. La captura de pantalla muestra que existen cinco grupos posibles que alcanzan esta suma, como 300 + 120 + 60 o 250 + 120 + 60 + 50, entre otras. A lo largo de este artículo, exploraremos distintos métodos para encontrar combinaciones específicas de números en una lista cuyo total coincida con un valor determinado en Excel.

Encontrar una combinación de números que sume una cantidad dada mediante la función Solver

Obtenga todas las combinaciones de números cuya suma sea igual a un valor dado

• Mediante una función definida por el usuario
• Con una función inteligente – Kutools para Excel

Obtener todas las combinaciones de números cuya suma se encuentre dentro de un intervalo mediante código VBA

Encontrar combinaciones de celdas que sumen una cantidad dada mediante la función Solver

Adentrarse en Excel para encontrar combinaciones de celdas que sumen un número específico puede parecer complicado, pero el complemento Solver lo convierte en algo sencillo. Le guiaremos paso a paso en la configuración de Solver para hallar la combinación adecuada de celdas, transformando una tarea aparentemente compleja en algo directo y factible.

Paso 1: Habilitar el complemento Solver

1. Vaya a Archivo > Opciones. En el cuadro de diálogo Opciones de Excel, haga clic en Complementos en el panel izquierdo y, a continuación, haga clic en el botón Ir. Vea la captura de pantalla:
   
2. A continuación, aparece el cuadro de diálogo Complementos. Active la opción Complemento Solver y haga clic en Aceptar para instalar este complemento correctamente.
   

Paso 2: Introducir la fórmula

Tras activar el complemento Solver, debe introducir esta fórmula en la celda B11:

=SUMPRODUCT(B2:B10,A2:A10)

Paso 3: Configurar y ejecutar Solver para obtener el resultado

1. Haga clic en Datos>Solverpara acceder al cuadro de diálogo Parámetros de Solver, y realice las siguientes operaciones en dicho cuadro de diálogo:
   • (1.) Haga clic en el botón para seleccionar la celda B11donde se encuentra su fórmula en la sección Establecer objetivo;
   • (2.) A continuación, en la sección Para, seleccione Valor dee introduzca el valor objetivo que necesite 480;
   • (3.) En la sección Cambiando las celdas variables, haga clic en el botón para seleccionar el rango de celdas B2:B10, donde se marcarán sus números correspondientes.
   • (4.) A continuación, haz clic en el botón Agregar.
   • 
2. A continuación, se muestra el cuadro de diálogo Agregar restricción. Haga clic en el botón para seleccionar el rango de celdas B2:B10 y seleccione bin en la lista desplegable. Por último, haga clic en el botón Aceptar . Vea la captura de pantalla:
   
3. En el cuadro de diálogo Parámetros de Solver, haga clic en el botón Resolver. Al cabo de unos minutos, aparecerá el cuadro de diálogo Resultados de Solver, donde podrá ver que las combinaciones de celdas cuya suma equivale al valor dado (480) están marcadas con un 1 en la columna B. En el cuadro de diálogo Resultados de Solver, seleccione Mantener la solución de Solver y haga clic en Aceptar para cerrar el cuadro de diálogo. Vea la captura de pantalla:
   

Obtenga todas las combinaciones de números cuya suma sea igual a un valor dado

Explorar las capacidades avanzadas de Excel le permite encontrar todas las combinaciones numéricas que sumen una cantidad específica, ¡y es más sencillo de lo que imagina! En esta sección descubrirá dos métodos para hallar todas las combinaciones de números que den como resultado la suma deseada.

Obtener todas las combinaciones de números que sumen una cantidad dada mediante una función definida por el usuario

Para descubrir todas las combinaciones posibles de números de un conjunto específico que sumen un valor determinado, la función personalizada que se detalla a continuación es una herramienta eficaz.

Paso 1: Abrir el editor de módulos VBA y copiar el código

1. Mantenga pulsadas las teclas ALT + F11 en Excel; se abrirá la ventana de Microsoft Visual Basic para Aplicaciones.
2. Haga clic en Insertar>Móduloy pegue el siguiente código en la ventana del módulo.
   Código VBA: Obtener todas las combinaciones de números que sumen un valor determinado

   Public Function MakeupANumber(xNumbers As Range, xCount As Long)
   'updateby Extendoffice
       Dim arrNumbers() As Long
       Dim arrRes() As String
       Dim ArrTemp() As Long
       Dim xIndex As Long
       Dim rg As Range
   
       MakeupANumber = ""
       
       If xNumbers.CountLarge = 0 Then Exit Function
       ReDim arrNumbers(xNumbers.CountLarge - 1)
       
       xIndex = 0
       For Each rg In xNumbers
           If IsNumeric(rg.Value) Then
               arrNumbers(xIndex) = CLng(rg.Value)
               xIndex = xIndex + 1
           End If
       Next rg
       If xIndex = 0 Then Exit Function
       
       ReDim Preserve arrNumbers(0 To xIndex - 1)
       ReDim arrRes(0)
       
       Call Combinations(arrNumbers, xCount, ArrTemp(), arrRes())
       ReDim Preserve arrRes(0 To UBound(arrRes) - 1)
       MakeupANumber = arrRes
   End Function
   
   Private Sub Combinations(Numbers() As Long, Count As Long, ArrTemp() As Long, ByRef arrRes() As String)
   
       Dim currentSum As Long, i As Long, j As Long, k As Long, num As Long, indRes As Long
       Dim remainingNumbers() As Long, newCombination() As Long
       
       currentSum = 0
       If (Not Not ArrTemp) <> 0 Then
           For i = LBound(ArrTemp) To UBound(ArrTemp)
               currentSum = currentSum + ArrTemp(i)
           Next i
       End If
    
       If currentSum = Count Then
           indRes = UBound(arrRes)
           ReDim Preserve arrRes(0 To indRes + 1)
           
           arrRes(indRes) = ArrTemp(0)
           For i = LBound(ArrTemp) + 1 To UBound(ArrTemp)
               arrRes(indRes) = arrRes(indRes) & "," & ArrTemp(i)
           Next i
       End If
       
       If currentSum > Count Then Exit Sub
       If (Not Not Numbers) = 0 Then Exit Sub
       
       For i = 0 To UBound(Numbers)
           Erase remainingNumbers()
           num = Numbers(i)
           For j = i + 1 To UBound(Numbers)
               If (Not Not remainingNumbers) <> 0 Then
                   ReDim Preserve remainingNumbers(0 To UBound(remainingNumbers) + 1)
               Else
                   ReDim Preserve remainingNumbers(0 To 0)
               End If
               remainingNumbers(UBound(remainingNumbers)) = Numbers(j)
               
           Next j
           Erase newCombination()
   
           If (Not Not ArrTemp) <> 0 Then
               For k = 0 To UBound(ArrTemp)
                   If (Not Not newCombination) <> 0 Then
                       ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
                   Else
                       ReDim Preserve newCombination(0 To 0)
                   End If
                   newCombination(UBound(newCombination)) = ArrTemp(k)
   
               Next k
           End If
           
           If (Not Not newCombination) <> 0 Then
               ReDim Preserve newCombination(0 To UBound(newCombination) + 1)
           Else
               ReDim Preserve newCombination(0 To 0)
           End If
           
           newCombination(UBound(newCombination)) = num
   
           Combinations remainingNumbers, Count, newCombination, arrRes
       Next i
   
   End Sub
   

Paso 2: Introducir la fórmula personalizada para obtener el resultado

Tras pegar el código, cierre la ventana del editor para regresar a la hoja de cálculo. Introduzca la siguiente fórmula en una celda vacía para mostrar el resultado y, a continuación, pulse la tecla Entrar para obtener todas las combinaciones. Vea la captura de pantalla:

=MakeupANumber(A2:A10,B2)

Consejo: Si desea mostrar los resultados de las combinaciones verticalmente en una columna, aplique la siguiente fórmula:

=TRANSPOSE(MakeupANumber(A2:A10,B2))

Obtener todas las combinaciones de números que sumen una cantidad dada mediante una potente función

Dadas las limitaciones de la función mencionada anteriormente, le recomendamos una solución rápida y completa: la función **Completar números** de Kutools para Excel, compatible con cualquier versión de Excel. Esta alternativa gestiona eficazmente números positivos, decimales y negativos, permitiéndole obtener rápidamente todas las combinaciones que sumen la cantidad deseada.

1. Haga clic en Kutools > Contenido > Completar números. Vea la captura de pantalla:
   
2. A continuación, en el cuadro de diálogo Completar números, haga clic en el botón para seleccionar la lista de números que desea utilizar desde el Rango de origen e introduzca el número total en el cuadro de texto Suma. Por último, haga clic en el botón Aceptar. Vea la captura de pantalla:
   
3. A continuación, aparecerá un cuadro de aviso para recordarle que seleccione una celda donde ubicar el resultado. Luego, haga clic en Aceptar. Vea la captura de pantalla:
   
4. Ahora, todas las combinaciones que suman dicho número ya se han mostrado, tal como se indica en la siguiente captura de pantalla:
   

Obtener todas las combinaciones de números cuya suma se encuentre dentro de un intervalo mediante código VBA

A veces, puede encontrarse en una situación en la que necesite identificar todas las combinaciones posibles de números cuya suma total se sitúe dentro de un intervalo específico. Por ejemplo, podría querer encontrar todos los agrupamientos posibles de números cuyo total esté comprendido entre 470 y 480.

Descubrir todas las combinaciones posibles de números cuya suma caiga dentro de un intervalo determinado supone un reto fascinante y altamente práctico en Excel. Esta sección presenta un código VBA diseñado para resolver esta tarea.

Paso 1: Abrir el editor de módulos VBA y copiar el código

1. Mantenga pulsadas las teclas ALT + F11 en Excel y se abrirá la ventana de Microsoft Visual Basic para Aplicaciones.
2. Haga clic en Insertar>Móduloy pegue el siguiente código en la ventana del módulo.
   Código VBA: Obtener todas las combinaciones de números cuya suma se encuentre dentro de un intervalo específico

   Sub Getall_combinations()
   'Updateby Extendoffice
       Dim xNumbers As Variant
       Dim Output As Collection
       Dim rngSelection As Range
       Dim OutputCell As Range
       Dim LowLimit As Long, HiLimit As Long
       Dim i As Long, j As Long
       Dim TotalCombinations As Long
       Dim CombTotal As Double
       Set Output = New Collection
       On Error Resume Next
       Set rngSelection = Application.InputBox("Select the range of numbers:", "Kutools for Excel", Type:=8)
       If rngSelection Is Nothing Then
           MsgBox "No range selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       xNumbers = rngSelection.Value
       LowLimit = Application.InputBox("Select or enter the low limit number:", "Kutools for Excel", Type:=1)
       HiLimit = Application.InputBox("Select or enter the high limit number:", "Kutools for Excel", Type:=1)
       On Error Resume Next
       Set OutputCell = Application.InputBox("Select the first cell for output:", "Kutools for Excel", Type:=8)
       If OutputCell Is Nothing Then
           MsgBox "No output cell selected. Exiting macro.", vbInformation, "Kutools for Excel"
           Exit Sub
       End If
       On Error GoTo 0
       TotalCombinations = 2 ^ (UBound(xNumbers, 1) * UBound(xNumbers, 2))
       For i = 1 To TotalCombinations - 1
           Dim tempArr() As Double
           ReDim tempArr(1 To UBound(xNumbers, 1) * UBound(xNumbers, 2))
           CombTotal = 0
           Dim k As Long: k = 0
           
           For j = 1 To UBound(xNumbers, 1)
               If i And (2 ^ (j - 1)) Then
                   k = k + 1
                   tempArr(k) = xNumbers(j, 1)
                   CombTotal = CombTotal + xNumbers(j, 1)
               End If
           Next j
           If CombTotal >= LowLimit And CombTotal <= HiLimit Then
               ReDim Preserve tempArr(1 To k)
               Output.Add tempArr
           End If
       Next i
       Dim rowOffset As Long
       rowOffset = 0
       Dim item As Variant
       For Each item In Output
           For j = 1 To UBound(item)
               OutputCell.Offset(rowOffset, j - 1).Value = item(j)
           Next j
           rowOffset = rowOffset + 1
       Next item
   End Sub
   
   
   

Paso 2: Ejecutar el código

1. Después de pegar el código, pulse la tecla F5 para ejecutarlo. En el primer cuadro de diálogo que aparezca, seleccione el rango de números que desea utilizar y haga clic en Aceptar. Vea la captura de pantalla:
   
2. En el segundo cuadro de aviso, seleccione o introduzca el límite inferior y haga clic en Aceptar. Vea la captura de pantalla:
   
3. En el tercer cuadro de diálogo, seleccione o introduzca el número límite superior y haga clic en Aceptar. Vea la captura de pantalla:
   
4. En el último cuadro de aviso, seleccione una celda de salida que será el punto de partida para mostrar los resultados. A continuación, haga clic en Aceptar. Vea la captura de pantalla:
   

Resultado

Ahora, cada combinación válida se mostrará en filas consecutivas de la hoja de cálculo, comenzando desde la celda de salida que eligió.

Excel le ofrece varias formas de encontrar grupos de números que sumen un total determinado. Cada método funciona de manera distinta, así que puede elegir el que mejor se adapte a su nivel de familiaridad con Excel y a las necesidades de su proyecto. Si quiere descubrir más trucos y consejos de Excel,nuestra página web ofrece miles de tutoriales. ¡Gracias por leer y esperamos seguir brindándole información útil en el futuro!

Artículos relacionados: