Como encontrar todas as combinações que são iguais a uma soma específica no Excel?

Descobrir todas as combinações possíveis de números dentro de uma lista que somam um valor específico é um desafio que muitos usuários do Excel podem enfrentar, seja para orçamento, planejamento ou fins de análise de dados.

Neste exemplo, temos uma lista de números, e o objetivo é identificar quais combinações desta lista somam 480. A captura de tela fornecida demonstra que existem cinco grupos possíveis de combinações que alcançam essa soma, incluindo combinações como 300+120+60, 250+120+60+50, entre outras. Neste artigo, exploraremos vários métodos para identificar combinações específicas de números em uma lista que totalizam um valor designado no Excel.

Encontre uma combinação de números igual a uma soma dada com a função Solver

Obtenha todas as combinações de números iguais a uma soma dada

• Usando uma Função Definida pelo Usuário
• Com um recurso inteligente – Kutools para Excel

Obtenha todas as combinações de números que têm uma soma em um intervalo com código VBA

Encontre combinações de células que sejam iguais a uma soma dada com a função Solver

Mergulhar no Excel para encontrar combinações de células que somam um número específico pode parecer assustador, mas o Suplemento Solver torna isso fácil. Vamos guiá-lo pelos passos simples para configurar o Solver e encontrar a combinação certa de células, transformando o que parecia ser uma tarefa complexa em algo direto e viável.

Passo 1: Ativar o Suplemento Solver

1. Por favor, vá para Arquivo > Opções, na Caixa de Diálogo Opções do Excel caixa de diálogo, clique em Suplementos no painel esquerdo, e então clique no botão Ir botão. Veja a captura de tela:
   
2. Então, a caixa de diálogo Suplementos aparece, marque a opção Suplemento Solver e clique no botão OK para instalar este suplemento com sucesso.
   

Passo 2: Insira a fórmula

Após ativar o suplemento Solver, você precisa inserir esta fórmula na célula B11:

=SUMPRODUCT(B2:B10,A2:A10)

Passo 3: Configure e execute o Solver para obter o resultado

1. Clique em Dados > Solver para ir à caixa de diálogo Parâmetros do Solver na caixa de diálogo, por favor faça as seguintes operações:
   • (1.) Clique no botão para selecionar a célula B11 onde sua fórmula está localizada na seção Definir Objetivo ;
   • (2.) Depois, na seção Para, selecione Valor De, e insira seu valor alvo 480 conforme necessário;
   • (3.) Na seção Alterando Células Variáveis por favor, clique no botão para selecionar o intervalo de células B2:B10 que marcará seus números correspondentes.
   • (4.) Em seguida, clique no botão Adicionar.
   • 
2. Então, uma caixa de diálogo Adicionar Restrição é exibida, clique no botão para selecionar o intervalo de células B2:B10, e selecione bin da lista suspensa. Por fim, clique no botão OK Veja a captura de tela:
   
3. Na caixa de diálogo Parâmetros do Solver clique no botão Resolver alguns minutos depois, uma caixa de diálogo Resultados do Solver é exibida, e você pode ver a combinação de células que somam 480 marcadas como 1 na coluna B. Na caixa de diálogo Resultados do Solver por favor, selecione a opção Manter Solução do Solver e clique no botão OK para sair da caixa de diálogo. Veja a captura de tela:
   

Obtenha todas as combinações de números iguais a uma soma dada

Explorar capacidades mais profundas do Excel permite que você encontre todas as combinações de números que correspondem a uma soma específica, e é mais fácil do que você imagina. Esta seção mostrará dois métodos para encontrar todas as combinações de números iguais a uma soma dada.

Obtenha todas as combinações de números iguais a uma soma dada com Função Definida pelo Usuário

Para descobrir todas as combinações possíveis de números de um conjunto específico que coletivamente atingem um valor dado, a função personalizada descrita abaixo serve como uma ferramenta eficaz.

Passo 1: Abra o editor de módulo VBA e copie o código

1. Pressione as teclas ALT + F11 no Excel, e abrirá a janela Microsoft Visual Basic for Applications.
2. Clique em Inserir > Módulo, e cole o seguinte código na Janela do Módulo.
   Código VBA: Obtenha todas as combinações de números iguais a uma soma 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
   

Passo 2: Insira a fórmula personalizada para obter o resultado

Depois de colar o código, feche a janela de código para voltar à planilha. Insira a seguinte fórmula em uma célula em branco para gerar o resultado, e pressione a tecla Enter para obter todas as combinações. Veja a captura de tela:

=MakeupANumber(A2:A10,B2)

Dica: Se você deseja listar os resultados das combinações verticalmente em uma coluna, aplique a seguinte fórmula:

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

Obtenha todas as combinações de números iguais a uma soma dada com um recurso poderoso

Dadas as limitações da função mencionada anteriormente, recomendamos uma solução rápida e abrangente: o recurso Arredondar Números do Kutools para Excel, que é compatível com qualquer versão do Excel. Essa alternativa pode lidar efetivamente com números positivos, decimais e negativos. Com esse recurso, você pode rapidamente obter todas as combinações que são iguais a uma soma dada.

1. Clique em Kutools > Texto > Arredondar Um Número, veja a captura de tela:
   
2. Então, na caixa de diálogo Arredondar um número por favor, clique no botão para selecionar a lista de números que você deseja usar a partir da Fonte dos Dados, e então insira o número total no campo Soma Finalmente, clique no botão OK Veja a captura de tela:
   
3. E então, uma caixa de diálogo será exibida para lembrá-lo de selecionar uma célula para localizar o resultado, depois clique em OK, veja a captura de tela:
   
4. E agora, todas as combinações que são iguais ao número dado foram exibidas conforme mostrado na captura de tela abaixo:
   

Obtenha todas as combinações de números que têm uma soma em um intervalo com código VBA

Às vezes, você pode se encontrar em uma situação onde precisa identificar todas as combinações possíveis de números que somam um valor dentro de um intervalo específico. Por exemplo, você pode estar buscando encontrar todos os agrupamentos possíveis de números onde o total esteja entre 470 e 480.

Descobrir todas as combinações possíveis de números que somam um valor dentro de um intervalo específico representa um desafio fascinante e altamente prático no Excel. Esta seção introduzirá um código VBA para resolver essa tarefa.

Passo 1: Abra o editor de módulo VBA e copie o código

1. Pressione as teclas ALT + F11 no Excel, e abrirá a janela Microsoft Visual Basic for Applications.
2. Clique em Inserir > Módulo, e cole o seguinte código na Janela do Módulo.
   Código VBA: Obtenha todas as combinações de números que somam um 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
   
   
   

Passo 2: Execute o código

1. Depois de colar o código, pressione a tecla F5 para executar este código, na primeira caixa de diálogo exibida, selecione o intervalo de números que deseja usar e clique em OK. Veja a captura de tela:
   
2. Na segunda caixa de diálogo, selecione ou digite o número limite inferior, e clique em OK. Veja a captura de tela:
   
3. Na terceira caixa de diálogo, selecione ou digite o número limite superior, e clique em OK. Veja a captura de tela:
   
4. Na última caixa de diálogo, selecione uma célula de saída, que será onde os resultados começarão a ser exibidos. Depois clique em OK. Veja a captura de tela:
   

Resultado

Agora, cada combinação qualificada será listada em linhas consecutivas na planilha, começando pela célula de saída que você escolheu.

O Excel oferece várias maneiras de encontrar grupos de números que somam um total específico, cada método funciona de forma diferente, então você pode escolher um com base no seu nível de familiaridade com o Excel e nas necessidades do seu projeto. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais. Obrigado por ler, e esperamos fornecer mais informações úteis no futuro!

Artigos Relacionados: