Ajuda com Excel Solver

Olá pessoal, boa tarde!

Preciso de uma ajuda sobre configurar critério no Excel Solver.

Contextualizando a necessidade:

Tenho uma lista de valores (há valores iguais ou não, dependendo do dia) e preciso chegar a um numero objetivo. Assisti a um tutorial no YouTube que atendeu parcialmente a minha necessidade, o Solver vai marcar com “1” ou “0” aqueles números que somados correspondem ao meu valor objetivo. Isso já me ajudou bastante.

Mas eu preciso travar o Solver para dizer para ele o seguinte: exemplo “desses 15 números, eu quero que apenas 10 sejam elegíveis”.

No exemplo abaixo (cenário que criei) o Solver encontrou meu valor objetivo com apenas 10 numeros (dos 15 listados). Mas há vezes em que ele acha menos números ou mais números (mesmo atendendo o valor objetivo). Eu preciso travar ele dizendo “quero que sejam X números”.

Abaixo está o cenário que criei e a configuração do Solver que fiz.

Definir objetivo: selecionado a celular em que há uma formula =somarproduto

Para: valor exato → o valor que eu quero que chege

Alterando as células variáveis: selecionando as células vazas da coluna “B”, é nessa coluna que o Solver vai colocar “1” ou “0”.

Sujeito a restrições: Dizendo para o Solver que o preenchimento será em binário “1” ou “0”.

Se não ficou muito claro a minha duvida, podem dizer e eu vou respondendo.

Conseguem me ajudar?

Muito obrigado

1 curtida

Boa tarde!

Para configurar o Excel Solver de modo que ele selecione exatamente um número fixo de valores para atingir seu valor objetivo, você precisa adicionar uma restrição adicional que limite o número total de seleções. Vou descrever os passos detalhados para fazer isso:

Cenário Exemplo:

  • Coluna A: Lista de valores
  • Coluna B: Células onde o Solver colocará “1” ou “0”
  • Célula C1: Valor objetivo
  • Célula D1: Número fixo de seleções desejadas (por exemplo, 10)

Passos Detalhados:

  1. Defina sua planilha conforme abaixo:

    A (Valores) B (Seleção)
    10
    20
    15
    25
    30
    5
    • Em C1: coloque seu valor objetivo.
    • Em D1: coloque o número de seleções desejado (por exemplo, 10).
  2. Crie a fórmula SOMARPRODUTO para calcular a soma dos valores selecionados:

    • Em uma célula, digamos E1, insira a fórmula:
      =SOMARPRODUTO(A1:A15, B1:B15)
      
      Isso calculará a soma dos valores em A onde B é igual a 1.
  3. Crie a fórmula para contar o número de seleções:

    • Em outra célula, digamos F1, insira a fórmula:
      =SOMASE(B1:B15)
      
      Isso contará quantas vezes o número 1 aparece na coluna B.
  4. Abra o Solver:

    • Vá em Dados > Solver (se não estiver visível, ative-o em Arquivo > Opções > Complementos).
  5. Configurar o Solver:

    • Definir Objetivo: Selecione a célula E1 (onde está a soma dos valores selecionados).
    • Para: Valor de célula igual a (seu valor objetivo em C1).
    • Alterando as Células Variáveis: Selecione B1:B15 (onde o Solver colocará 1 ou 0).
    • Sujeito a Restrições:
      • Adicione a restrição que B1:B15 deve ser binário (0 ou 1):
        • Clique em Adicionar, selecione B1:B15, condição bin.
      • Adicione a restrição para o número de seleções:
        • Clique em Adicionar, selecione F1, condição igual a, valor D1.
  6. Executar o Solver:

    • Clique em Resolver e o Solver irá tentar encontrar a combinação que atenda aos critérios.

Exemplo Visual das Restrições:

  • Objetivo: =SOMARPRODUTO(A1:A15, B1:B15)
  • Valor Objetivo: Valor em C1
  • Variáveis: B1:B15
  • Restrições:
    • B1:B15 = binário
    • SOMASE(B1:B15) = D1

Resumo das Fórmulas:

  • E1: =SOMARPRODUTO(A1:A15, B1:B15)
  • F1: =SOMASE(B1:B15)

Seguindo esses passos, o Solver será configurado para selecionar exatamente o número de valores que você deseja, além de atingir o valor objetivo.

Oi Gabriel, boa noite.
Obrigado pelo retorno e ajuda.

Fiz um teste conforme descreveu. Mas o Solver se comportou bem diferente.
Veja, ele sequer preencheu com “1” ou “0” como antes. Ele nem fez os calculos (sem esses ajustes, ele tinha demorado até um pouco para concluir).

Segue os parametros que coloquei, conforme você me auxiliou.

Por gentileza, pode me ajudar?