Como encontrar todas as combinações que totalizam uma determinada soma no Excel?

Descobrir todas as combinações possíveis de números numa lista cuja soma seja igual a um valor específico é um desafio frequente entre utilizadores do Excel, seja para orçamentação, planeamento ou análise de dados.

Neste exemplo, temos uma lista de números e o objetivo é identificar quais as combinações cuja soma seja exatamente 480. A captura de ecrã fornecida mostra que existem cinco grupos possíveis que atingem essa soma, incluindo combinações como 300 + 120 + 60 ou 250 + 120 + 60 + 50, entre outras. Ao longo deste artigo, exploramos vários métodos eficazes para encontrar, no Excel, as combinações específicas de números numa lista cujo total corresponda a um valor predefinido.

Encontrar uma combinação de números igual a uma determinada soma com a função Solver

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

• Utilizando uma Função Definida pelo Utilizador
• Com uma funcionalidade inteligente – Kutools para Excel

Obter todas as combinações de números cuja soma se enquadre num intervalo com código VBA

Encontrar uma combinação de células cuja soma seja igual a um determinado valor com a função Solver

Explorar o Excel à procura de combinações de células cuja soma seja igual a um número específico pode parecer complicado — mas o Suplemento Solver torna essa tarefa simples. Vamos guiá-lo passo a passo na configuração do Solver para encontrar a combinação certa de células, transformando o que antes parecia complexo numa operação direta e perfeitamente exequível.

Passo 1: Ativar o Suplemento Solver

1. Aceda a Ficheiro > Opções. Na caixa de diálogo Opções do Excel, clique em Suplementos no painel esquerdo e, em seguida, clique no botão Ir. Veja a captura de ecrã:
   
2. Em seguida, é apresentada a caixa de diálogo Suplementos. Assinale a opção Suplemento Solver e clique em OK para instalar este suplemento com sucesso.
   

Passo 2: Introduzir a fórmula

Após ativar o suplemento Solver, introduza esta fórmula na célula B11:

=SUMPRODUCT(B2:B10,A2:A10)

Passo 3: Configurar e executar o Solver para obter o resultado

1. Clique em Dados>Solverpara aceder à caixa de diálogo Parâmetros do Solver, e realize as seguintes operações:
   • (1.) Clique no botão para selecionar a célula B11onde se encontra a sua fórmula na secção Definir Objetivo;
   • (2.) Em seguida, na secção Para, selecione Valor dee introduza o valor pretendido 480conforme necessário;
   • (3.) Na secção Ao Alterar as Células Variáveis, clique no botão para selecionar o intervalo de células B2:B10, onde serão marcados os seus números correspondentes.
   • (4.) Em seguida, clique no botão Adicionar.
   • 
2. Em seguida, é apresentada a caixa de diálogo Adicionar Restrição. Clique no botão para selecionar o intervalo de células B2:B10 e selecione bin na lista pendente. Por fim, clique no botão OK . Veja a captura de ecrã:
   
3. Na caixa de diálogo Parâmetros do Solver, clique no botão Resolver. Passados alguns minutos, surge a caixa de diálogo Resultados do Solver, onde pode ver que as combinações de células cuja soma é igual ao valor indicado (480) estão marcadas com 1 na coluna B. Na caixa de diálogo Resultados do Solver, selecione Manter Solução do Solver e clique em OK para fechar a caixa de diálogo. Veja a captura de ecrã:
   

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

Explorar as capacidades avançadas do Excel permite-lhe descobrir todas as combinações de números que totalizam um determinado valor — e é mais simples do que imagina. Esta secção apresenta-lhe dois métodos para encontrar todas as combinações de números cuja soma seja igual a um valor específico.

Obter todas as combinações de números iguais a uma determinada soma com uma Função Definida pelo Utilizador

Para descobrir todas as combinações possíveis de números de um conjunto específico cuja soma seja igual a um determinado valor, a função personalizada abaixo revela-se uma ferramenta eficaz.

Passo 1: Abrir o editor de módulos VBA e copiar o código

1. Mantenha premidas as teclas ALT + F11 no Excel para abrir a janela Microsoft Visual Basic para Aplicações.
2. Clique em Inserir>Móduloe cole o código seguinte na Janela do Módulo.
   Código VBA: Obter todas as combinações de números iguais a uma determinada soma

   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: Introduzir a fórmula personalizada para obter o resultado

Após colar o código, feche a janela de código para regressar à folha de cálculo. Introduza a seguinte fórmula numa célula vazia para apresentar o resultado e, em seguida, prima a tecla Enter para obter todas as combinações. Veja a captura de ecrã:

=MakeupANumber(A2:A10,B2)

Dica: Se pretender listar os resultados das combinações verticalmente numa coluna, aplique a seguinte fórmula:

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

Obter todas as combinações de números iguais a uma determinada soma com uma funcionalidade poderosa

Tendo em conta as limitações da função mencionada anteriormente, recomendamos uma solução rápida e abrangente: a funcionalidade **Arredondar Números** do Kutools para Excel, compatível com qualquer versão do Excel. Esta alternativa trata eficazmente números positivos, decimais e negativos, permitindo-lhe obter rapidamente todas as combinações cuja soma seja igual a um determinado valor.

1. Clique em Kutools>Conteúdo>Arredondar Números, veja a captura de ecrã:
   
2. Em seguida, na caixa de diálogo Arredondar Números, clique no botão para selecionar a lista de números que pretende utilizar a partir do Intervalo de Origem e, depois, introduza o valor total na caixa de texto Soma. Por fim, clique no botão OK. Veja a captura de ecrã:
   
3. Em seguida, surge uma caixa de aviso a solicitar que selecione uma célula para colocar o resultado; clique então em OK, veja a captura de ecrã:
   
4. De imediato, todas as combinações que correspondem a esse valor são apresentadas, tal como se mostra na captura de ecrã seguinte:
   

Obter todas as combinações de números cuja soma se enquadre num intervalo com código VBA

Por vezes, poderá encontrar-se numa situação em que necessita de identificar todas as combinações possíveis de números cuja soma total se enquadre num intervalo específico. Por exemplo, poderá pretender encontrar todos os agrupamentos possíveis de números cujo total esteja entre 470 e 480.

Descobrir todas as combinações possíveis de números cuja soma total caia dentro de um intervalo específico é um desafio fascinante e extremamente prático no Excel. Esta secção apresenta um código VBA para resolver essa tarefa com eficiência.

Passo 1: Abrir o editor de módulos VBA e copiar o código

1. Mantenha premidas as teclas ALT + F11 no Excel para abrir a janela Microsoft Visual Basic para Aplicações.
2. Clique em Inserir>Móduloe cole o código seguinte na Janela do Módulo.
   Código VBA: Obter todas as combinações de números cuja soma se enquadra num 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: Executar o código

1. Após colar o código, prima a tecla F5 para executar este código. Na primeira caixa de diálogo apresentada, selecione o intervalo de números que pretende utilizar e clique em OK. Veja a captura de ecrã:
   
2. Na segunda caixa de diálogo, selecione ou introduza o valor limite inferior e clique em OK. Veja a captura de ecrã:
   
3. Na terceira caixa de diálogo, selecione ou introduza o valor limite superior e clique em OK. Veja a captura de ecrã:
   
4. Na última caixa de diálogo, selecione uma célula de saída — o local onde os resultados começarão a ser apresentados — e clique em OK. Veja a captura de ecrã:
   

Resultado

Agora, cada combinação qualificada será listada em linhas consecutivas na folha de cálculo, a partir da célula de saída que escolheu.

O Excel oferece-lhe várias formas de encontrar conjuntos de números cuja soma corresponda a um determinado valor total; cada método funciona de forma distinta, permitindo-lhe escolher o que melhor se adapta ao seu nível de familiaridade com o Excel e às necessidades do seu projeto. Se quiser explorar mais dicas e truques do Excel, o nosso site disponibiliza milhares de tutoriais. Obrigado por ler — e esperamos continuar a fornecer-lhe informações úteis no futuro!

Artigos Relacionados: