Excel: encontra um subconjunto de números que adicionam a um total determinado?

Question

Eu tenho uma coluna de números (digamos que é A1: A100) e eu preciso encontrar um subconjunto deles que somam um determinado total.

    

Answer 1

É possível com o suplemento Solver *. As etapas a seguir funcionaram para mim no Excel 2007 e 2010.

1. Designe uma célula para armazenar o resultado (C1 para este exemplo) - esta é a célula de destino e uma coluna que o Excel pode usar para trabalho de rascunho (B1: B100 para este exemplo)
2. Na célula de destino, insira a fórmula "= SUMPRODUCT (A1: A100, B1: B100)" (sem aspas). Isto irá calcular a soma de A1 * B1 + A2 * B2 + ... etc
3. Selecione Abrir o solver (guia Dados, grupo de análise)
4. A célula de destino deve ser óbvia ($ C $ 1 para este exemplo)
5. Para "Igual a:", selecione "Valor de:" e insira o valor desejado
6. Em "Alterando Células" digite "$ B $ 1: $ B $ 100" (sem aspas, e pode ser necessário inicializar esses valores para 0 você mesmo)
7. Adicione uma restrição às células que podem ser alteradas. No menu suspenso, selecione 'bin' (Binário). Isso restringe os valores dessas células a 0 (removendo a célula A correspondente da soma) ou 1 (adicionando a célula A correspondente à soma).
8. Clique em "Resolver" e aguarde. Os números que fazem parte do subconjunto que você está procurando terão um 1 na coluna B

Seosolverestiverdemorandomuitotempo,vocêpodeajudá-loremovendolinhasqueobviamentenãofuncionarão(ototaléemdólares,eapenasumalinhatemzerozero)

Bônus:vocêpodefazercomqueoExceldestaqueautomaticamenteascélulasquevocêestáprocurando,adicionandoformataçãocondicionalaessascélulas.Selecionetodasascélulasquedesejaformatare,em(guiaInício)>>(grupodeestilos)>>Formataçãocondicional>>Novaregra,selecione"Usar uma fórmula para determinar quais células devem ser formatadas". Na fórmula, digite '= $ B1 = 1' (sem as aspas), que será avaliado como verdadeiro se a linha correspondente na coluna B for 1. Para o formato, você pode adicionar o que quiser (negrito, itálico, preenchimento verde, etc).

Outra maneira fácil de encontrar as linhas importantes é classificar a coluna B Z- > A e todos os 1s chegarão ao topo.

* O suplemento solucionador pode ser instalado com estas etapas

1. Clique no Botão Microsoft Office e, em seguida, clique em Opções do Excel.
2. Clique em Suplementos e, na caixa Gerenciar, selecione Suplementos do Excel.
3. Clique em Ir.
4. Na caixa Suplementos disponíveis, marque a caixa de seleção Solver Add-in e clique em OK. (Se o Solver Add-in não estiver listado na caixa Suplementos disponíveis, clique em Procurar para localizar o suplemento.)
5. Se você for avisado de que o Suplemento do Solver não está atualmente instalado em seu computador, clique em Sim para instalá-lo.

Answer 2

Há um SumMatch de baixo custo do Excel, que destacará o subconjunto de números que somam uma soma alvo.