Como faço para usar o solucionador do Excel para resolver isso?

Question

ABC Corporation é um distribuidor global de peças e componentes elétricos. A empresa contrata a compra de componentes e peças da Europa e os entrega em armazéns em três portos europeus, E1, E2, E3. Os vários componentes e peças são carregados em contêineres com base na demanda dos clientes dos EUA. Cada porto tem um número fixo limitado de contêineres disponíveis a cada mês. Os contêineres são então enviados para o exterior em navios porta-contêineres para os portos P1, P2, P3 e P4. A partir destes portos marítimos, os contentores são normalmente acoplados a camiões e transportados para os portos interiores em I1, I2 e I3. Há um número fixo de transportadores disponíveis em cada porto a cada mês. Estes portos interiores são por vezes chamados de “aldeias de carga” ou entroncamentos intermodais, onde os contentores são recolhidos e transferidos de um modo de transporte para outro (ou seja, do camião para o comboio e vice-versa). Dos portos interiores, os contêineres são transportados para os centros de distribuição da ABC em D1, D2, D3, D4 e D5. A seguir estão os custos de manuseio e envio ($/contêiner) entre cada um dos pontos de embarque e destino ao longo desta cadeia de abastecimento no exterior e os contêineres disponíveis em cada porto:

Exemplo de planilha Excel

Uma célula vermelha significa que a rota específica não é uma opção viável e, portanto, não pode ser considerada. Determine as remessas ideais de cada ponto de embarque para cada destino ao longo desta cadeia de abastecimento no exterior que resultará no custo total mínimo de remessa e no custo total de remessa incorrido em cada etapa.

Answer 1

Aqui está um exemplo de configuração do seu problema para o Solver. Você pode torná-lo mais compacto, mas a maneira como eu o configuro facilita o diagnóstico de quaisquer problemas.

1. Use seus dados originais em uma guia "Dados".
2. Crie uma nova aba, chamei de "Equação", para configurar o problema do Solver.
3. As colunas A a D são usadas para configurar todas as combinações possíveis de rotas. por exemplo, a linha 1 é cabeçalho; A linha 2 é E1, P1, I1, D1; A linha 2 é E1, P1, I1, D2; etc.
4. As colunas E a G são os custos associados da Europa ao porto dos EUA, do porto dos EUA ao porto interior e do porto interior ao centro de distribuição. Isso é preenchido usando VLOOKUP e MATCH. por exemplo, em E2 é =VLOOKUP(A2,Data!$A$3:$E$5,MATCH(B2,Data!$A$2:$E$2,0),FALSE); em F2 é =VLOOKUP(B2,Data!$A$11:$D$14,MATCH(C2,Data!$A$10:$D$10,0),FALSE); em G2 é =VLOOKUP(C2,Data!$A$20:$F$22,MATCH(D2,Data!$A$19:$F$19,0),FALSE). Estão todos preenchidos.
5. A coluna H é o custo unitário total. Para tornar óbvias as rotas inviáveis, defini o custo total delas em 1.000.000. por exemplo, H2 é =IF(OR(E2=0,F2=0,G2=0),1000000,SUM(E2:G2)). Isto está preenchido.
6. A coluna I é o número de unidades (contêineres) que percorrem essa rota. Esta coluna será manipulada pelo solucionador. Inicializei todas as linhas com 1.
7. A coluna J é o custo total da rota. por exemplo, J2 é =H2*I2e está preenchido.
8. Outra tabela é construída na mesma aba para conter a função objetivo e as restrições. Está em L1 a O19. Alguns exemplos dos cálculos aqui incluem: nUnits são somas da tabela anterior, então o número de contêineres de E1 é =SUMIFS($I$2:$I$181,$A$2:$A$181,L2), o número de contêineres de P1 é =SUMIFS($I$2:$I$181,$B$2:$B$181,L5), etc.; maxUnits são extraídos da aba Dados, então o máximo de contêineres de E1 é =VLOOKUP(L2,Data!$A$3:$F$5,6,FALSE), o máximo de contêineres de I1 é =HLOOKUP(L9,Data!$B$10:$D$15,6,FALSE), etc.; da mesma forma, as unidades de demanda são extraídas da guia Dados.
9. Adicionei uma restrição adicional para garantir que nenhuma rota inviável seja selecionada.
10. O objetivo é o custo total, que é a soma da Coluna J.

Aqui está uma captura de tela da guia Equação, antes de executar o Solver. Várias linhas estão ocultas.

A configuração do Solver é a seguinte:

1. Definir objetivo: é$M$19
2. Para: é mínimo
3. Ao alterar células variáveis: é$I$2:$I$181
4. Tornar variáveis ​​irrestritas não negativas não está selecionado (isso é tratado com restrições)
5. Selecione um método de resolução: é evolucionário. O Evolutionary é muito mais rápido que os outros métodos ao lidar com restrições de números inteiros.
6. Sujeito às restrições: são muitos ...
   • $I$2:$I$181 = integer- deve ter números inteiros de contêineres
   • $I$2:$I$181 >= 0 - não pode incorrer em custos negativos
   • $M$2 <= $N$2duplicado para cada linha para $M$11 <=$N$11- não violar o número máximo de contêineres
   • $M$12 = $O$12duplicado para cada linha para $M$16 = $O$16- satisfazer a demanda
   • $M$17 = $N$17- não use opções inviáveis

Abaixo está uma captura de tela da caixa de diálogo Parâmetros do Solver...

Com esta configuração, obtenho um custo total de US$ 1.661.119,00 com toda a demanda satisfeita e nenhuma restrição violada. Abaixo está uma captura de tela da tabela de restrições ...

Abaixo está uma captura de tela da tabela de custos, com todas as rotas zero filtradas...

Answer 2

Se você quiser uma resposta única, a rota mais barata E2 > P4 > I2 > D2custa US$ 1.452.

O método que usei não foi elegante. Fiz uma tabela com todas as opções possíveis, usei INDEXe MATCHpara consultar o custo de cada trecho da viagem, e depois ordenei por custo total crescente. Esta é a aparência da minha planilha:

Os dados à esquerda são os que copiei da sua planilha. A tabela à direita é o que eu criei. Para cada um dos campos E, P, I e D, apenas os digitei manualmente em grupos. D1, D2, D3, D4, D5. Copie esses cinco e cole-os bastante. I1, copie para um conjunto de D's, I2, copie para um conjunto de D's, etc. até que a tabela esteja preenchida. Para o campo $EP, combinei INDEXe MATCHpesquisei o custo de viajar de E(qualquer) para P(qualquer).

=1/(1/INDEX($B$3:$E$5,MATCH([@E],$A$3:$A$5,0),MATCH([@P],$B$2:$E$2,0)))

INDEXpega um array e retorna uma célula de algum lugar dentro desse array. Para encontrar a linha que eu queria, eu costumava MATCHdescobrir onde estava o valor E no intervalo A3:A5. Para encontrar a coluna, procurei o valor P em B2:B4. Isso me deu o custo de ir de E a P. Adicionei o 1/(1/...)wrapper em torno de tudo para que retornasse um erro se o trecho da viagem fosse zero, indicando que não estava disponível.

Em seguida, copiei essa fórmula para os campos $PI e $ID, ajustando cada um para referenciar os intervalos corretos. Finalmente, o campo Custo Total simplesmente somou essas três partes. Classifiquei em ordem crescente e encontrei a rota mais barata. Todos os erros – aqueles que utilizam rotas indisponíveis – foram enviados para o fundo.

---

Se você deseja um sistema contínuo no qual deseja acompanhar quantos transportes estão atuais em rota para cada trecho da viagem, para poder selecionar a rota mais barata com base no que está disponível no momento, você pode querer procurar uma solução de banco de dados. Alternativamente, você pode usar a lista ordenada e começar do topo, descendo até encontrar uma rota onde cada trecho está disponível no momento.