Ir para o conteúdo principal

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

Descobrir todas as combinações possíveis de números em uma lista que resultam em uma soma específica é um desafio que muitos usuários do Excel podem encontrar, seja para fins de orçamento, planejamento ou 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 atingem essa soma, incluindo combinações como 300+120 +60, 250+120+60+50, entre outros. Neste artigo, exploraremos vários métodos para identificar as combinações específicas de números em uma lista que totalizam um valor designado no Excel.

Encontre uma combinação de números iguais a uma determinada soma com a função Solver

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

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


Encontre uma combinação de células igual a uma determinada soma 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 muito fácil. Orientaremos você pelas etapas simples para configurar o Solver e encontrar a combinação certa de células, tornando o que parecia ser uma tarefa complexa, simples e factível.

Etapa 1: ativar o suplemento Solver

  1. Por favor, vá para Envie o > Opções, Na Opções do Excel caixa de diálogo, clique em Suplementos no painel esquerdo, em seguida, clique em Go botão. Veja a imagem:
  2. Então o Suplementos caixa de diálogo for exibida, verifique o Suplemento Solver opção e clique OK para instalar este add-in com sucesso.

Etapa 2: insira a fórmula

Depois de ativar o complemento Solver, você precisa inserir esta fórmula na célula B11:

=SUMPRODUCT(B2:B10,A2:A10)
Note: Nesta fórmula: B2: B10 é uma coluna de células em branco ao lado da sua lista de números e A2: A10 é a lista de números que você usa.

Etapa 3: configure e execute o Solver para obter o resultado

  1. Clique Data > Solver para ir para o Parâmetro do Solver caixa de diálogo, na caixa de diálogo, faça as seguintes operações:
    • (1.) Clique botão para selecionar a célula B11 onde sua fórmula está localizada a partir do Definir objetivo seção;
    • (2.) Em seguida, no Para seção, selecione Valor dee insira seu valor alvo 480 como você precisa;
    • (3.) Sob o Alterando células variáveis seção, por favor clique botão para selecionar o intervalo de células B2: B10 onde marcará seus números correspondentes.
    • (4.) Em seguida, clique Adicionar botão.
  2. Então, um Adicionar Restrição caixa de diálogo for exibida, clique em botão para selecionar o intervalo de células B2: B10E selecione caixa na lista suspensa. Por fim, clique OK botão. Veja a imagem:
  3. No Parâmetro do Solver diálogo, clique no Resolver botão, alguns minutos depois, um Resultados do Solver a caixa de diálogo é exibida e você pode ver a combinação de células que equivalem a uma determinada soma 480 marcadas como 1 na coluna B. No Resultados do Solver diálogo, por favor selecione Solução Keep Solver opção e clique OK para sair da caixa de diálogo. Veja a imagem:
Note: Este método, no entanto, tem uma limitação: ele só pode identificar uma combinação de células que somam a soma especificada, mesmo que existam várias combinações válidas.

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

Explorar os recursos mais profundos do Excel permite encontrar cada combinação de números que corresponda 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 determinada soma.

Obtenha todas as combinações de números iguais a uma determinada soma com a função definida pelo usuário

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

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

  1. Mantenha pressionada a ALT + F11 chaves no Excel, e abre o Microsoft Visual Basic para Aplicações janela.
  2. Clique inserção > Móduloe cole o código a seguir na janela do módulo.
    Código VBA: obtenha 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
    

Etapa 2: insira a fórmula personalizada para obter o resultado

Após 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 Entrar chave para obter todas as combinações. Veja a captura de tela:

=MakeupANumber(A2:A10,B2)
Note: Nesta fórmula: A2: A10 é a lista de números e B2 é a soma total que você deseja obter.

Dica: Se você deseja listar os resultados da combinação verticalmente em uma coluna, aplique a seguinte fórmula:
=TRANSPOSE(MakeupANumber(A2:A10,B2))
As limitações deste método:
  • Esta função personalizada funciona apenas no Excel 365 e 2021.
  • Este método é eficaz exclusivamente para números positivos; os valores decimais são arredondados automaticamente para o número inteiro mais próximo e números negativos resultarão em erros.

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

Dadas as limitações da função acima mencionada, recomendamos uma solução rápida e abrangente: a função Make up a Number do Kutools for Excel, que é compatível com qualquer versão do Excel. Esta alternativa pode lidar efetivamente com números positivos, decimais e números negativos. Com esse recurso, você pode obter rapidamente todas as combinações iguais a uma determinada soma.

Tips: Para aplicar isso Crie um número recurso, em primeiro lugar, você deve baixar Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.
  1. Clique Kutools > Conteúdo > Crie um número, veja a captura de tela:
  2. Então, no Invente um número caixa de diálogo, por favor clique botão para selecionar a lista de números que deseja usar a partir do Fonte de dadose, em seguida, insira o número total no Soma caixa de texto. Por fim, clique OK botão, veja a captura de tela:
  3. E então, uma caixa de prompt aparecerá para lembrá-lo de selecionar uma célula para localizar o resultado e, em seguida, clique OK, veja a captura de tela:
  4. E agora, todas as combinações iguais a esse número fornecido foram exibidas conforme a imagem mostrada abaixo:
Note: Para aplicar este recurso, por favor baixe e instale o Kutools para Excel em primeiro lugar.

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

Às vezes, você pode se encontrar em uma situação em que precisa identificar todas as combinações possíveis de números que, coletivamente, resultam em uma soma dentro de um intervalo específico. Por exemplo, você pode estar procurando 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 apresentará um código VBA para resolver esta tarefa.

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

  1. Mantenha pressionada a ALT + F11 chaves no Excel, e abre o Microsoft Visual Basic para Aplicações janela.
  2. Clique inserção > Móduloe cole o código a seguir 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
    
    
    

Etapa 2: execute o código

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

Resultado

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

O Excel oferece várias maneiras de encontrar grupos de números que somam um determinado total, cada método funciona de maneira diferente, então você pode escolher um com base em sua familiaridade com o Excel e no que você precisa para o seu projeto. Se você estiver interessado em explorar mais dicas e truques do Excel, nosso site oferece milhares de tutoriais, por favor clique aqui para acessá-los. Obrigado por ler e esperamos fornecer mais informações úteis no futuro!


Artigos relacionados:

  • Liste ou gere todas as combinações possíveis
  • Digamos que tenho as duas colunas de dados a seguir e, agora, desejo gerar uma lista de todas as combinações possíveis com base nas duas listas de valores conforme a captura de tela à esquerda mostrada. Talvez você possa listar todas as combinações uma a uma se houver poucos valores, mas, se houver várias colunas com vários valores necessários para listar as combinações possíveis, aqui estão alguns truques rápidos que podem ajudá-lo a lidar com este problema no Excel .
  • Gere todas as combinações de 3 ou múltiplas colunas
  • Supondo que eu tenha 3 colunas de dados, agora, quero gerar ou listar todas as combinações dos dados nessas 3 colunas, conforme a captura de tela abaixo mostrada. Você tem bons métodos para resolver essa tarefa no Excel?
  • Gere uma lista de todas as combinações possíveis de 4 dígitos
  • Em alguns casos, podemos precisar gerar uma lista de todas as combinações possíveis de 4 dígitos dos números 0 a 9, o que significa gerar uma lista de 0000, 0001, 0002… 9999. Para resolver rapidamente a tarefa de lista no Excel, apresento alguns truques para você.