☰

Excel: Encontre um subconjunto de números que somam um determinado total?

tag-icon • tag-icon microsoft-excel microsoft-office microsoft-excel-2007
Excel: Encontre um subconjunto de números que somam um determinado total?

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

Responder1

Isso é 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 solucionador (guia Dados, grupo Análise)
  4. A célula alvo deve ser óbvia ($C$1 para este exemplo)
  5. Para 'Igual a:' selecione 'Valor de:' e insira o valor desejado
  6. Em 'Ao alterar células' insira "$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ê procura terão 1 na coluna B

Exemplo

Se o solucionador estiver demorando muito, você pode ajudar removendo linhas que obviamente não funcionarão (o total está em dólares e apenas uma linha tem centavos diferentes de zero)

Bônus: você pode fazer com que o Excel destaque automaticamente as células que você está procurando adicionando formatação condicional a essas células. Selecione todas as células que deseja formatar e em (guia Página inicial) >> (grupo Estilos) >> Formatação condicional >> Nova regra selecione 'Usar uma fórmula para determinar quais células formatar'. Na fórmula, insira '=$B1=1' (sem 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 virão para o topo.

*O complemento Solver pode ser instalado seguindo 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 Suplemento Solver e clique em OK. (Se o suplemento Solver 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 Solver não está instalado em seu computador, clique em Sim para instalá-lo.

Responder2

Existe um suplemento do Excel de baixo custoSomaMatch, que destacará o subconjunto de números que somam uma soma desejada.

insira a descrição da imagem aqui

informação relacionada