Como descobrir a soma dos valores que se encontram nas células?

Por exemplo, eu tenho a seguinte lista de números, e agora, eu quero saber qual combinação de números na lista soma 480, na imagem a seguir mostrada, você pode ver que há cinco grupos de combinações possíveis que somam iguais a 480, como 300 + 60 + 120, 300 + 60 + 40 + 80, etc. Neste artigo, falarei sobre alguns métodos para descobrir quais células somam um valor específico no Excel.

• Encontre combinações de células que sejam iguais a uma determinada soma com fórmulas
• Encontre a combinação de células que seja igual a uma determinada soma com o suplemento Solver
• Encontre a combinação de células que seja igual a uma determinada soma com a Função Definida pelo Usuário
• Encontre todas as combinações que equivalem a uma determinada soma com um recurso incrível

Encontre combinações de células que sejam iguais a uma determinada soma com fórmulas

Primeiro, você precisa criar alguns nomes de intervalo e, em seguida, aplicar uma fórmula de matriz para encontrar as células que somam o valor de destino. Faça o seguinte passo a passo:

1. Selecione a lista de números e defina esta lista como um nome de intervalo - Intervalo 1 no nome Boxe pressione Entrar chave para terminar o nome do intervalo definido, veja a captura de tela:

2. Depois de definir um nome de intervalo para a lista de números, você precisa criar mais dois nomes de intervalo no Gerente de Nomes caixa, por favor clique Fórmulas > Gerente de Nomes, Na Gerente de Nomes caixa de diálogo, clique em Novo botão, veja as capturas de tela:

3. No estalou para fora Novo nome caixa de diálogo, insira um nome List1 no Nome campo e digite esta fórmula = LINHA (INDIRETO ("1:" & LINHAS (Faixa1))) (Intervalo 1 é o nome do intervalo que você criou na etapa 1) no Refere-se a campo, veja a captura de tela:

4. Clique OK para retornar ao Gerente de Nomes caixa de diálogo e continue a clicar Novo botão para criar outro nome de intervalo, no Novo nome caixa de diálogo, insira um nome List2 no Nome campo e digite esta fórmula = LINHA (INDIRETO ("1:" & 2 ^ LINHAS (Faixa1))) (Intervalo 1 é o nome do intervalo que você criou na etapa 1) no Refere-se a campo, veja a captura de tela:

5. Depois de criar os nomes dos intervalos, aplique a seguinte fórmula de matriz na célula B2:

=IF(ISNUMBER(MATCH(ROWS($1:1),IF(INDEX(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),MATCH(TRUE,MMULT(MOD(INT((List2-1)/2^(TRANSPOSE(List1)-1)),2),Range1)=$C$2,0),),TRANSPOSE(List1)),0)),"X","")e pressione Shift + Ctrl + Enter juntas, arraste a alça de preenchimento para a célula B8, o último número da lista, e você poderá ver os números cujo valor total é 480 marcados como X na coluna B, veja a captura de tela:

• notas:
• Na longa fórmula acima: List1, List2 e Intervalo 1 são os nomes de intervalo que você criou nas etapas anteriores, C2 é o valor específico ao qual você deseja que os números sejam somados.
• Se mais de uma combinação de valores tiver uma soma igual ao valor específico, apenas uma combinação será listada.

Encontre e liste todas as combinações que equivalem a uma determinada soma rápida e facilmente no Excel

Kutools for Excel's Crie um número O utilitário pode ajudá-lo a encontrar e listar todas as combinações e combinações específicas que são iguais a um determinado número de soma de forma rápida e fácil. Clique para baixar o Kutools para Excel!

Kutools for Excel: com mais de 300 suplementos úteis do Excel, grátis para testar sem limitação em 30 dias. Baixe e teste grátis agora!

Encontre a combinação de células que seja igual a uma determinada soma com o suplemento Solver

Se você está confuso com o método acima, o Excel contém um Suplemento Solver recurso, usando este add-in, você também pode identificar os números cujo montante total é igual a um determinado valor.

1. Primeiro, você precisa ativar este Solver add-in, por favor vá a Envie o > Opções, Na Opções do Excel caixa de diálogo, clique em Suplementos no painel esquerdo e clique em Suplemento Solver de Complementos de aplicativos inativos seção, veja a captura de tela:

2. Então clique Go botão para entrar no Suplementos diálogo, verificar Suplemento Solver opção e clique OK para instalar este add-in com sucesso.

3. Depois de ativar o suplemento Solver, você precisa inserir esta fórmula na célula B9: = SUMPRODUTO (B2: B9, A2: A9), (B2: B9 é uma coluna em branco, células ao lado de sua lista de números, e A2: A9 é a lista de números que você usa. ), e pressione Entrar chave, veja a captura de tela:

4. Então clique Dados > 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 

(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

5. E, em seguida, clique em Adicionar botão para ir para o Adicionar Restrição caixa de diálogo, clique em

6. Clique OK voltar a Parâmetro do Solver caixa de diálogo e clique em Resolver botão, alguns minutos depois, um Resultados do Solver 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. 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:

Nota: Este método também só pode obter uma combinação de células se houver mais de uma combinação de valores com uma soma igual ao valor específico.

Encontre a combinação de células que seja igual a uma determinada soma com a Função Definida pelo Usuário

Os dois primeiros métodos são todos complexos para a maioria de nossos usuários do Excel, aqui, posso criar um código VBA para resolver esse trabalho de forma rápida e fácil.

Para obter o resultado correto, você deve primeiro classificar a lista de números em ordem decrescente. E então siga as seguintes etapas:

1. Segure o ALT + F11 chaves para abrir o Microsoft Visual Basic para Aplicações janela.

2. Clique inserção > Móduloe cole o seguinte código no Módulo Janela.

Código VBA: encontre a combinação de células que seja igual a uma determinada soma:

Function GetCombination(CoinsRange As Range, SumCellId As Double) As String
'updateby Extendoffice
    Dim xStr As String
    Dim xSum As Double
    Dim xCell As Range
    xSum = SumCellId
    For Each xCell In CoinsRange
        If Not (xSum / xCell < 1) Then
            xStr = xStr & Int(xSum / xCell) & " of " & xCell & "  "
            xSum = xSum - (Int(xSum / xCell)) * xCell
        End If
    Next
    GetCombination = xStr
End Function

3. Em seguida, salve e feche esta janela de código, volte para a planilha e insira esta fórmula = getcombination (A2: A9, C2) em uma célula em branco e pressione Entrar , você obterá o seguinte resultado que exibe os números da combinação que são iguais a uma determinada soma, consulte a captura de tela:

• notas:
• Na fórmula acima, A2: A9 é o intervalo de números, e C2 contém o valor alvo que você deseja igualar.
• Se mais de uma combinação de valores tiver uma soma igual ao valor específico, apenas uma combinação será listada.

Encontre todas as combinações que equivalem a uma determinada soma com um recurso incrível

Talvez todos os métodos acima sejam um pouco difíceis para você, aqui, vou apresentar uma ferramenta poderosa, Kutools for Excel, Com o seu Crie um número recurso, você pode obter rapidamente todas as combinações iguais a uma determinada soma.

Dicas:Para aplicar isso Crie um número recurso, em primeiro lugar, você deve baixar o Kutools for Excele, em seguida, aplique o recurso de forma rápida e fácil.

Depois de instalar Kutools for Excel, por favor, faça o seguinte:

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

3. E então, clique OK botão, uma caixa de prompt aparecerá para lembrá-lo de selecionar uma célula para localizar o resultado, veja a captura de tela:

4. Em seguida, clique OK, e agora, todas as combinações iguais a esse número fornecido foram exibidas conforme a captura de tela abaixo:

Clique para baixar Kutools para Excel e avaliação gratuita agora!

Demonstração: Encontre combinações de células que são iguais a uma determinada soma no Excel

As melhores ferramentas de produtividade para escritório

O Kutools for Excel resolve a maioria dos seus problemas e aumenta sua produtividade em 80%

• armadilha para peixes: Insira rapidamente fórmulas complexas, gráficos e qualquer coisa que você tenha usado antes; Criptografar células com senha; Criar lista de discussão e enviar emails ...
• Barra Super Fórmula (edite facilmente várias linhas de texto e fórmula); Layout de leitura (ler e editar facilmente um grande número de células); Colar na faixa filtrada...
• Mesclar células / linhas / colunas sem perder dados; Dividir o conteúdo das células; Combinar linhas / colunas duplicadas... Evite células duplicadas; Comparar intervalos...
• Selecione Duplicado ou Único Linhas; Selecione linhas em branco (todas as células estão vazias); Super Find e Fuzzy Find em muitos livros; Seleção aleatória ...
• Cópia exata Várias células sem alterar a referência da fórmula; Criação automática de referências para várias folhas; Inserir marcadores, Caixas de seleção e mais ...
• Extrair Texto, Adicionar texto, remover por posição, Remover Espaço; Criar e imprimir subtotais de paginação; Converter entre conteúdo de células e comentários...
• Super Filtro (salvar e aplicar esquemas de filtro a outras planilhas); Classificação Avançada por mês / semana / dia, frequência e mais; Filtro Especial por negrito, itálico ...
• Combine pastas de trabalho e planilhas; Mesclar tabelas com base em colunas-chave; Divida os dados em várias folhas; Conversão em lote de xls, xlsx e PDF...
• Mais de 300 recursos poderosos. Suporta Office / Excel 2007-2019 e 365. Suporta todos os idiomas. Fácil implantação em sua empresa ou organização. Teste gratuito de 30 dias com recursos completos. Garantia de devolução do dinheiro em 60 dias.

Guia do Office traz interface com guias para o Office e torna seu trabalho muito mais fácil

• Habilite a edição e leitura com guias em Word, Excel, PowerPoint, Publisher, Access, Visio e Project.
• Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
• Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!

Como descobrir a soma dos valores que se encontram nas células A1 A2 A3 A4 e A5 soma A1 A2 A3 A4 soma A1 * 5 A1 A2 A3 A4 A5 A1 A2 A3 A4 A5?

Como fazer soma de células específicas no Excel?

Qual e a fórmula correta no Excel 2010 para obter a soma das células no intervalo de A5 até A8?

O que e intervalo de soma?

Qual e a maneira mais rápida de somar os valores que estão em sequência numa mesma linha ou coluna?