Tarefa de transporte no Excel. Encontrar o melhor meio de transporte do vendedor ao comprador

O Excel é um programa muito funcional. Ele pode ser usado para resolver uma enorme camada de problemas que se tem de enfrentar nos negócios. Um dos mais comuns é o transporte. Imagine que precisamos entender qual método de transporte do fabricante ao comprador final é o mais ideal em termos de tempo, dinheiro e outros recursos. Esse problema é bastante popular, não importa em que setor o negócio esteja. Portanto, vamos dar uma olhada em como implementá-lo usando o Excel.

Descrição da tarefa de transporte

Então, temos duas contrapartes que interagem constantemente. No nosso caso, este é um comprador e um vendedor. Precisamos descobrir como transportar mercadorias de forma que os custos sejam mínimos. Para fazer isso, você precisa apresentar todos os dados em um formulário esquemático ou matricial. No Excel, usamos a última opção. Em geral, existem dois tipos de tarefas de transporte:

1. Fechado. Nesse caso, oferta e demanda estão em equilíbrio.
2. Abrir. Não há igualdade entre oferta e demanda aqui. Para obter uma solução para este problema, você deve primeiro trazê-lo para o primeiro tipo, equalizando oferta e demanda. Para fazer isso, você precisa introduzir um indicador adicional - a presença de um comprador ou vendedor condicional. Além disso, você precisa fazer algumas alterações na tabela de custos.

Como habilitar o recurso Localizar solução no Excel

Para resolver problemas de transporte no Excel, existe uma função especial chamada “Procurar uma solução”. Ele não está habilitado por padrão, então você precisa seguir os seguintes passos:

1. Abra o menu “Arquivo”, localizado no canto superior esquerdo da janela do programa.
2. Depois disso, clique no botão com os parâmetros.
3. Em seguida, encontramos a subseção “Configurações” e vamos ao menu de gerenciamento de complementos. São pequenos programas executados no ambiente Microsoft Excel. Vemos que primeiro clicamos no menu “Add-ins” e, na parte inferior direita, definimos o item “Excel Add-ins” e clicamos no botão “Go”. Todas as ações necessárias são destacadas com retângulos e setas vermelhas.
4. Em seguida, ative o complemento "Pesquisar uma solução", após o qual confirmamos nossas ações pressionando o botão OK. Com base na descrição do cenário, podemos ver que ele é projetado para analisar dados complexos, como científicos e financeiros.
5. Depois disso, vá para a guia “Dados”, onde vemos um novo botão, que é chamado o mesmo que o add-in. Ele pode ser encontrado no grupo de ferramentas Análise.

Resta apenas clicar neste botão e prosseguimos para a solução do problema de transporte. Mas antes disso, devemos falar um pouco mais sobre a ferramenta Solver no Excel. Este é um complemento especial do Excel que permite encontrar a solução mais rápida para um problema. Uma característica é a consideração de restrições que o usuário define na fase de preparação. Em termos simples, trata-se de uma sub-rotina que permite determinar a melhor forma de realizar determinada tarefa. Tais tarefas podem incluir o seguinte:

1. Investir, carregar um armazém ou qualquer outra atividade similar. Incluindo a entrega de mercadorias.
2. A melhor maneira. Isso inclui objetivos como obter o máximo de lucro a um custo mínimo, como obter a melhor qualidade com os recursos disponíveis e assim por diante.

Além das tarefas de transporte, este add-on também é usado para os seguintes propósitos:

1. Desenvolvimento de um plano de produção. Ou seja, quantas unidades de um produto precisam ser produzidas para atingir o rendimento máximo.
2. Encontre a distribuição de mão de obra para diferentes tipos de trabalho de modo que o custo total de produção de um produto ou serviço seja o menor.
3. Defina o tempo mínimo que levará para concluir todo o trabalho.

Como você pode ver, as tarefas são muito diferentes. A regra universal para a aplicação deste add-in é que antes de resolver o problema, é necessário criar um modelo que corresponda às principais características do problema proposto. Um modelo é uma coleção de funções que usam variáveis ​​como seus argumentos. Ou seja, valores que podem mudar.

É importante notar que a otimização de um conjunto de valores é realizada exclusivamente em um indicador, que é chamado de função objetivo.

O add-in Solver enumera os diferentes valores das variáveis ​​que são passadas para a função objetivo de forma que seja o máximo, mínimo ou igual a um determinado valor (esta é justamente a restrição). Existe outra função que é um pouco semelhante em seu princípio de funcionamento, e que muitas vezes é confundida com a “Busca de uma solução”. É chamado de “Seleção de opções”. Mas se você cavar mais fundo, a diferença entre eles é enorme:

1. A função Goal Seek não funciona com mais de uma variável.
2. Ele não prevê a capacidade de definir limites em variáveis.
3. Ele é capaz de determinar apenas a igualdade da função objetivo a um determinado valor, mas não permite encontrar o máximo e o mínimo. Portanto, não é adequado para nossa tarefa.
4. Capaz de calcular com eficiência apenas se o modelo for do tipo linear. Se o modelo não for linear, ele encontra o valor mais próximo do valor original.

A tarefa de transporte é muito mais complicada em sua estrutura, portanto, o complemento “Seleção de parâmetros” não é suficiente para isso. Vamos dar uma olhada em como implementar a função “Search for a Solution” na prática usando o exemplo de um problema de transporte.

Um exemplo de solução de um problema de transporte no Excel

Para demonstrar claramente como resolver problemas de transporte na prática no Excel, vamos dar um exemplo.

Tarefas de condições

Suponha que temos 6 vendedores e 7 compradores. A demanda e a oferta entre eles são distribuídas respectivamente da seguinte forma: 36, 51, 32, 44, 35 e 38 unidades são vendedores e 33, 48, 30, 36, 33, 24 e 32 unidades são compradores. Se você somar todos esses valores, descobrirá que oferta e demanda estão em equilíbrio. Portanto, este problema é do tipo fechado, que é resolvido de forma muito simples.

Além disso, temos informações sobre quanto você precisa gastar com transporte do ponto A ao ponto B (eles estão destacados em células amarelas no exemplo).

Solução – algoritmo passo a passo

Agora, depois de nos familiarizarmos com as tabelas com os dados iniciais, podemos usar o seguinte algoritmo para resolver esse problema:

1. Primeiro, fazemos uma tabela composta por 6 linhas e 7 colunas.
2. Depois disso, vamos para qualquer célula que não contenha nenhum valor e ao mesmo tempo esteja fora da tabela recém-criada e inserimos a função. Para fazer isso, clique no botão fx, localizado à esquerda da linha de entrada da função.
3. Temos uma janela na qual precisamos selecionar a categoria “Matemática”. Em que função estamos interessados? O destacado nesta captura de tela. Função SUMPRODUCT multiplica intervalos ou matrizes entre si e os soma. Apenas o que precisamos. Depois disso, pressione a tecla OK.
4. Em seguida, uma janela aparecerá na tela na qual você precisa especificar os parâmetros da função. Eles são os seguintes:
   1. Array 1. Este é o primeiro argumento no qual escrevemos o intervalo destacado em amarelo. Você pode definir os parâmetros da função usando o teclado ou selecionando a área apropriada com o botão esquerdo do mouse.
   2. Matriz 2. Este é o segundo argumento, que é a tabela recém-criada. As ações são executadas da mesma maneira.

Confirme sua ação pressionando o botão OK.

1. Depois disso, clicamos com o botão esquerdo do mouse na célula que serve como o canto superior esquerdo da tabela recém-criada. Agora clique no botão inserir função novamente.
2. Selecionamos a mesma categoria do caso anterior. Mas desta vez estamos interessados ​​na função SOMA.
3. Agora vem a etapa de preenchimento dos argumentos. Como primeiro argumento, escrevemos a linha superior da tabela que criamos no início. Da mesma forma que antes, isso pode ser feito selecionando essas células na planilha ou manualmente. Confirmamos nossas ações pressionando o botão OK.
4. Veremos os resultados na célula com a função. Neste caso, é zero. Em seguida, mova o cursor para o canto inferior direito, após o qual aparecerá um marcador de preenchimento automático. Parece uma pelúcia preta. Se aparecer, mantenha pressionado o botão esquerdo do mouse e mova o cursor para a última célula da nossa tabela.
5. Isso nos dá a oportunidade de transferir a fórmula para todas as outras células e obter os resultados corretos sem precisar realizar cálculos adicionais.
6. O próximo passo é selecionar a célula superior esquerda e colar a função SOMA nela. Depois disso, inserimos os argumentos e usamos o marcador de preenchimento automático para preencher todas as células restantes.
7. Depois disso, passamos diretamente para resolver o problema. Para fazer isso, usaremos o complemento que incluímos anteriormente. Vá para a guia “Dados” e lá encontramos a ferramenta “Pesquisar uma solução”. Clicamos neste botão.
8. Agora uma janela apareceu diante de nossos olhos, através da qual você pode configurar os parâmetros do nosso complemento. Vejamos cada uma dessas opções:
   1. Otimize a função objetivo. Aqui precisamos selecionar a célula que contém a função SUMPRODUCT. Vemos que esta opção permite selecionar uma função para a qual uma solução será pesquisada.
   2. Antes da. Aqui definimos a opção “Mínimo”.
   3. Alterando as células das variáveis. Aqui indicamos o intervalo correspondente à tabela que criamos no início (com exceção da linha e coluna de resumo).
   4. Sujeito a restrições. Aqui precisamos adicionar restrições clicando no botão Adicionar.
   5. Lembramos que tipo de restrição precisamos criar – a soma dos valores das demandas dos compradores e das ofertas dos vendedores deve ser a mesma.
9. A tarefa de restrições é realizada da seguinte forma:
   1. Link para células. Aqui entramos no intervalo da tabela para cálculos.
   2. Termos. Esta é uma operação matemática em relação à qual o intervalo especificado no primeiro campo de entrada é verificado.
   3. O valor da condição ou restrição. Aqui, inserimos a coluna apropriada na tabela de origem.
   4. Após todas as etapas serem concluídas, clique no botão OK, confirmando assim nossas ações.

Realizamos exatamente as mesmas operações para as linhas superiores, definindo a seguinte condição: elas devem ser iguais.

O próximo passo é definir as condições. Precisamos definir os seguintes critérios para a soma das células na tabela – maior ou igual a zero, um número inteiro. Como resultado, temos uma lista de condições sob as quais o problema é resolvido. Aqui você precisa certificar-se de que a caixa de seleção ao lado da opção “Tornar variáveis ​​sem limites não negativos” esteja marcada. Além disso, em nossa situação, é necessário que o método de resolução do problema seja escolhido – “Procurando uma solução para problemas não lineares de métodos OPG”. Agora podemos dizer com segurança que a configuração está feita. Portanto, resta apenas realizar os cálculos. Para fazer isso, clique no botão “Encontrar uma solução”.

Depois disso, todos os dados serão calculados automaticamente e, em seguida, o Excel mostrará uma janela com os resultados. É necessário verificar novamente o funcionamento do computador, pois são possíveis erros se as condições foram definidas incorretamente anteriormente. Se tudo estiver correto, clique no botão “OK” e veja a tabela finalizada.

Se acontecer que nossa tarefa se tornou um tipo aberto, isso é ruim, porque você precisa editar a tabela de origem para que a tarefa se torne fechada. No entanto, quando isso for feito, o algoritmo restante será o mesmo.

Conclusão

Como você pode ver, o Excel também pode ser usado para cálculos muito complexos, que à primeira vista não estão disponíveis para um simples programa de computador instalado em quase todos. No entanto, é. Hoje já cobrimos o nível avançado de uso. Este tópico não é tão simples, mas como se costuma dizer, a estrada será dominada por quem anda. O principal é seguir o plano de ação e executar com precisão todas as ações indicadas acima. Então não haverá erros e o programa executará independentemente todos os cálculos necessários. Não haverá necessidade de pensar sobre qual função usar e assim por diante.