¿Cómo encontrar todas las combinaciones que suman una cantidad dada en Excel?

Descubrir todas las posibles combinaciones de números dentro de una lista que sumen una cantidad específica es un desafío que muchos usuarios de Excel podrían enfrentar, ya sea para presupuestos, planificación o propósitos de análisis de datos.

En este ejemplo, tenemos una lista de números y el objetivo es identificar qué combinaciones de esta lista suman 480. La captura de pantalla proporcionada demuestra que hay cinco grupos posibles de combinaciones que alcanzan esta suma, incluyendo combinaciones como 300+120+60, 250+120+60+50, entre otras. En este artículo, exploraremos varios métodos para identificar las combinaciones específicas de números dentro de una lista que totalizan un valor designado en Excel.

Encuentra una combinación de números igual a una suma dada con la función Solver

Obtén todas las combinaciones de números iguales a una suma dada

• Mediante una Función Definida por el Usuario
• Mediante una característica inteligente: Kutools para Excel

Obtén todas las combinaciones de números que tienen una suma en un rango con código VBA

Encuentra combinaciones de celdas que sumen una cantidad dada con 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 hace fácil. Te guiaremos a través de los sencillos pasos para configurar Solver y encontrar la combinación correcta de celdas, haciendo que una tarea aparentemente compleja sea simple y factible.

Paso 1: Habilitar el complemento Solver

1. Por favor, ve a Archivo > Opciones, en el Excel Opciones cuadro de diálogo, haz clic en Complementos del panel izquierdo, luego haz clic en el botón Ir . Ver captura de pantalla:
   
2. Luego, aparece el cuadro de diálogo Complementos marca la opción Solver Add-in y haz clic en Aceptar para instalar este complemento con éxito.
   

Paso 2: Introduce la fórmula

Después de activar el complemento Solver, necesitas introducir esta fórmula en la celda B11:

=SUMPRODUCT(B2:B10,A2:A10)

Paso 3: Configura y ejecuta Solver para obtener el resultado

1. Haz clic en Datos > Solver para ir al cuadro de diálogo Parámetros de Solver En el cuadro de diálogo, realiza las siguientes operaciones:
   • (1.) Haz clic en el botón para seleccionar la celda B11 donde está ubicada tu fórmula en la sección Establecer Objetivo ;
   • (2.) Luego, en la sección Para, selecciona Valor De, e introduce tu valor objetivo 480 según sea necesario;
   • (3.) En la sección Cambiando Celdas Variables por favor, haz clic en el botón para seleccionar el rango de celdas B2:B10 que marcarán tus números correspondientes.
   • (4.) Luego, haz clic en el botón Agregar.
   • 
2. Luego, aparece el cuadro de diálogo Agregar Restricción haz clic en el botón para seleccionar el rango de celdas B2:B10, y selecciona bin desde la lista desplegable. Por último, haz clic en el botón Aceptar . Ver captura de pantalla:
   
3. En el cuadro de diálogo Parámetros de Solver haz clic en el botón Resolver unos minutos después, aparecerá el cuadro de diálogo Resultados de Solver y podrás ver que las combinaciones de celdas que suman 480 están marcadas con 1 en la columna B. En el cuadro de diálogo Resultados de Solver selecciona la opción Mantener Solución de Solver y haz clic en Aceptar para salir del cuadro de diálogo. Ver captura de pantalla:
   

Obtén todas las combinaciones de números iguales a una suma dada

Explorar las capacidades más profundas de Excel te permite encontrar cada combinación de números que coincida con una suma específica, y es más fácil de lo que podrías pensar. Esta sección te mostrará dos métodos para encontrar todas las combinaciones de números iguales a una suma dada.

Obtén todas las combinaciones de números iguales a una suma dada con una Función Definida por el Usuario

Para descubrir todas las posibles combinaciones de números de un conjunto específico que colectivamente alcancen un valor dado, la función personalizada descrita a continuación sirve como una herramienta efectiva.

Paso 1: Abre el editor de módulos VBA y copia el código

1. Mantén presionadas las teclas ALT + F11 en Excel, y se abrirá la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insertar > Módulo, y pega el siguiente código en la Ventana de Módulo.
   Código VBA: Obtén todas las combinaciones de números iguales a una suma dada

   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: Introduce la fórmula personalizada para obtener el resultado

Después de pegar el código, cierra la ventana de código para regresar a la hoja de trabajo. Introduce la siguiente fórmula en una celda en blanco para generar el resultado, y luego presiona la tecla Entrar para obtener todas las combinaciones. Ver captura de pantalla:

=MakeupANumber(A2:A10,B2)

Consejo: Si deseas listar los resultados de las combinaciones verticalmente en una columna, aplica la siguiente fórmula:

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

Obtén todas las combinaciones de números iguales a una suma dada con una característica poderosa

Dado las limitaciones de la función mencionada anteriormente, recomendamos una solución rápida y completa: la función Completar un Número de Kutools para Excel, que es compatible con cualquier versión de Excel. Esta alternativa puede manejar eficazmente números positivos, decimales y negativos. Con esta función, puedes obtener rápidamente todas las combinaciones que sumen un valor dado.

1. Haz clic en Kutools > Contenido > Completar un Número, ver captura de pantalla:
   
2. Luego, en el cuadro de diálogo Completar un Número haz clic en el botón para seleccionar la lista de números que deseas usar desde la Fuente de Datos, y luego introduce el número total en el cuadro de texto Suma Finalmente, haz clic en el botón Aceptar ver captura de pantalla:
   
3. Luego, aparecerá un cuadro de diálogo emergente para recordarte que selecciones una celda donde colocar el resultado, luego haz clic en Aceptar, ver captura de pantalla:
   
4. Y ahora, todas las combinaciones que suman ese número dado se han mostrado como se muestra en la siguiente captura de pantalla:
   

Obtén todas las combinaciones de números que tengan una suma en un rango con código VBA

A veces, podrías encontrarte en una situación donde necesites identificar todas las combinaciones posibles de números que sumen un valor dentro de un rango específico. Por ejemplo, podrías estar buscando encontrar todos los agrupamientos posibles de números donde el total caiga entre 470 y 480.

Descubrir todas las combinaciones posibles de números que sumen un valor dentro de un rango específico representa un desafío fascinante y altamente práctico en Excel. Esta sección presentará un código VBA para resolver esta tarea.

Paso 1: Abre el editor de módulos VBA y copia el código

1. Mantén presionadas las teclas ALT + F11 en Excel, y se abrirá la ventana Microsoft Visual Basic for Applications.
2. Haz clic en Insertar > Módulo, y pega el siguiente código en la Ventana de Módulo.
   Código VBA: Obtén todas las combinaciones de números que sumen un rango 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: Ejecuta el código

1. Después de pegar el código, presiona la tecla F5 para ejecutar este código, en el primer cuadro de diálogo emergente, selecciona el rango de números que deseas usar, y haz clic en Aceptar. Ver captura de pantalla:
   
2. En el segundo cuadro de diálogo emergente, selecciona o escribe el número límite inferior, y haz clic en Aceptar. Ver captura de pantalla:
   
3. En el tercer cuadro de diálogo emergente, selecciona o escribe el número límite superior, y haz clic en Aceptar. Ver captura de pantalla:
   
4. En el último cuadro de diálogo emergente, selecciona una celda de salida, que será donde comiencen a mostrarse los resultados. Luego haz clic en Aceptar. Ver captura de pantalla:
   

Resultado

Ahora, cada combinación calificada se listará en filas consecutivas en la hoja de trabajo, comenzando desde la celda de salida que elegiste.

Excel te ofrece varias formas de encontrar grupos de números que sumen un total determinado, cada método funciona de manera diferente, así que puedes elegir uno basado en tu familiaridad con Excel y lo que necesites para tu proyecto. Si estás interesado en explorar más consejos y trucos de Excel, nuestro sitio web ofrece miles de tutoriales. ¡Gracias por leer, y esperamos proporcionarte más información útil en el futuro!

Artículos Relacionados: