Definir e resolver um problema usando o Solver – Excel — Transcript

[Música] Olá, meu nome é Marcos Riper. Bem-vindo a mais um treinamento do Guide Excel. Nesse treinamento, nós vamos falar sobre o Solver. O Solver é um sistema dentro do Excel, feito pela Microsoft, que serve para a resolução de problemas de álgebra linear.

Certo, então, nesses problemas de álgebra linear, a gente consegue resolver problemas de estoque, problema de maximização de lucros, minimização de custos, encontrar ponto de equilíbrio em um produto, em uma produção, tudo isso baseado nas restrições e em fórmulas.

Dentro do Excel, usando o Solver de forma simples, bastante simples de utilizar o Solver dentro dele, tá? Então, é isso. Nós vamos entender como é que funciona o Solver, entender um pouquinho de álgebra linear, bem pouquinho mesmo, e depois a gente vai passar para dois exemplos que a gente tem, tá bom? Um exemplo que realmente foi aplicado, inclusive. Então, aqui a gente tem um problema, um problema clássico aqui que tem de ponto de equilíbrio. Então, a gente tem uma produção de um determinado produto, ele tem um custo variável e um custo fixo. O custo fixo são despesas administrativas, água, luz, telefone, prédio, aluguel, tudo isso, certo? Ele não vai mexer, e o custo variável é de acordo com o que foi produzido. Então, ele vai ter um material, uma matéria-prima que vai ser utilizada para aquele produto, certo? Então, ela está representada aqui por essa função, função C, C de X. O X é a quantidade de produtos produzidos. Então, por exemplo, se eu fizer um produto, 60 x + 500 vai dar 560. Se não produzir nenhum produto, já tem um custo 500, certo? Produzindo um, eu tenho 560, dois, 620, três, 680, e por aí vai. Para receita, eu tenho que para cada um vendido, eu estou vendendo ele por 110. Então, 110 x 1, 110 x 2, 220, por aí vai, até que ele chega num ponto de equilíbrio, onde se eu fizer 10 produtos, eu vou ter 1.100 de receita e 1.000 de custo. A partir daí, eu tenho lucro, né, em cima das vendas, e aqui eu tenho o ponto onde ele está mostrando que está chegando ali o ponto de equilíbrio dentro dessas duas funções. Esse é um exemplo bastante simples do que ele alcança. Ele consegue alcançar o 1.000, no caso, e dizer qual é o ponto de equilíbrio ou qual é a quantidade que eu tenho que produzir de X, ok? No não linear, nós temos duas equações onde nós temos o custo e a receita, onde a receita é quadrática, certo? A função quadrática que você está vendo, então é uma parábola, e aqui a função linear, né, onde a gente vai ver os dois pontos que estão sendo intercalados, que ele vai mostrar aqui onde eles estão se batendo os dois ali, certo? E daí ele vai aqui encontrar o maior ou menor, de acordo com o que for desejado na função que foi solicitada para ele, certo? Então, nem sempre produzir mais vai ser bom, porque você vai ter um ponto que você vai dizer: se eu produzir mais, eu vou ter problemas, vai ficar ruim para mim, certo? Então, continuando aqui, então, entendido essa parte que nós temos dois tipos de, dois tipos principalmente, que é o linear e o não linear, né? Entendendo as diferenças entre os dois, o Solver resolve esses dois tipos e ainda evolutivo, que não vou entrar em detalhes. Nós fizemos aqui um exemplo de como é que ele faz. Além disso que a gente viu ali, nós temos as restrições. Por exemplo, eu tenho uma restrição de custo ou eu tenho uma restrição de máquinas, de mão de obra. Eu tenho X horas para trabalhar com aquilo ali, então não podemos ultrapassar, correto? Então, esses riscos que a gente vê aqui, essas restrições aqui, elas estão restritas a B, C, e ele deixa a área possível, só a área azul. Então, ele vai cortando, ele vai cortando o gráfico até deixar apenas a área azul, e qual é o ponto máximo dele, tentando deixar o valor máximo. Então, ele vem aqui e encontra o valor máximo de acordo com essas duas barras, ok? E o mínimo, como é que é o mínimo? É invertido. Ele vai vir e vai alcançar o ponto mínimo que eu tenho aqui da área possível, certo? Da menor área possível. Então, ele vai vir aqui, vai encontrar o menor ponto possível dentro das restrições que eu tenho, certo? Ele vai vir aqui, vai encontrar na parte azul o menor ponto possível para trazer ali, certo? Além disso, ele também consegue encontrar um determinado valor. Se eu quero deixar esse valor, ele também tenta achar para você, certo? Então, a gente vai ver agora com um exemplo prático de como é que a gente vai, como a gente pode aplicar isso, certo? Então, primeiro ponto aqui, exemplo de Solver. Vou apagar aqui os valores, e nós temos um problema de uma empresa de macarrão, de massas, que ela trabalha com toneladas de produção de parafuso, talharim e lasanha, certo? Ele tem esses custos, esses preços: 2.500 para parafuso, 17.500 para talharim e 25.000 para lasanha. Então, a minha pergunta é: quantos parafusos, quantos talharim, quanta lasanha eu tenho que produzir para que o meu resultado seja o melhor possível, sendo que eu tenho um tempo limite de produção de 80, um tempo limite de montagem de 88 e um tempo limite de embalagem de 99, tá? Então, para parafuso, eu tenho 3 horas de produção, 19 de montagem e 20 de embalagem. Para talharim, 16 de produção, 8 de montagem e 18 de embalagem. E para lasanha, 11 de produção, 1 de montagem e embalagem. Então, eles variam. Eu tenho esses limites. Se eu produzir aqui, por exemplo, um parafuso, eu vou ver que ele vai multiplicar o 1 por 3, 20, vai dar aqui os valores, né, que não podem ultrapassar esses limites. Se eu produzir dois aqui, ele vai somar esses valores e vai multiplicar 2 por 16, 8, 18 e vai somar aqui o total, certo? E aqui o valor automaticamente já multiplicado também, ele está multiplicando a quantidade pelo preço e está dando valor aqui, correto? Então, vamos à nossa solução. Claro que não dá para a gente ficar chutando valores para tentar achar os valores. Até dá, mas vai dar muito trabalho. Quantas vezes que eu vou ter que fazer isso, né? E não pode ser o maior valor também, não garante nada, garante uma solução fácil, tá? É fácil. Imagina uma coisa complexa. Cliquei em Solver. Eu vou clicar em redefinir tudo só para limpar. Tá, eu já tinha feito. Você vai vir, ah, primeiro, se você não tem o Solver, você vai vir em Arquivo, Opções, Suplementos, ir e vai marcar o Solver, tá? Todos os Excel têm. Clica em Solver, dá um ok, vai vir aqui em Dados, Solver. Ok, então aqui na primeira opção, nós temos qual é o objetivo, qual é a função objetivo, é o resultado. O resultado eu quero maximizar, eu quero o melhor faturamento possível. Se eu quisesse o menor, daí eu botaria mínimo, ou se eu quisesse um valor definido, eu botaria 5.000, 5.000, botaria o valor que você quisesse, certo? Mas não, eu quero o maior faturamento possível. E quais são as células que vão alterar? Ele pergunta quais as células, alterando a quantidade produzida, certo? Então, o máximo valor com a quantidade produzida aqui, sujeito às restrições. Então, aqui as quantidades têm que ser números inteiros, tá? Não podem ser frações, meia tonelada, por aí, ok? Porque eu estou dizendo que não pode, não pode. Tá? Adicionar. Eu quero que seja pelo menos maior ou igual a um, ou seja, tem que ser pelo menos uma tonelada de cada um para ter pelo menos um mix para poder vender, tá bom? V adicionar. E a última, eu vou adicionar aqui, ó, o total da produção não pode ser maior, tem que ser menor ou igual que o limite dele, correto? Adicionar, fechar. Tá, então tem que ser tonelada, tem que ser maior ou igual a um, e o total da produção não pode ser maior que o limite, correto? Aqui você vai mudar por LP Simplex. Pode testar o GRG não linear também, mas esse caso aqui é o método LP Simplex. Clicar em resolver, e dessa vez ele resolveu rápido. Olha, o Solver encontrou uma solução. Todas as restrições e condições de adequação foram satisfeitas. Manter a solução, D. Ok, então ele está dizendo que para melhor faturamento possível para sua empresa, você vai ter três macarrões parafuso, um talharim e cinco lasanhas, e daí você vai bater aqui, ó, tempo de produção 80, 80, 88, 70 de montagem e 99,93 de tempo de embalagem, e você usou o máximo que você podia da tua fábrica e faturou R$ 10.000, certo? Então, essa é com a sua capacidade produtiva e com esses valores aqui, o melhor que você pode conseguir de resultado é isso aqui. Claro que a...

gente vai passar para dois exemplos que a gente tem tá bom Um exemplo que realmente foi aplicado inclusive então aqui a gente tem um problema de um problema clássico aqui que tem de Ponto de Equilíbrio Então a gente tem uma produção de um

determinado produto ele tem um custo variável e um custo fixo o custo fixo são despesas administrativas água luz telefone prédio aluguel tudo isso certo ele não vai mexer e o custo variável el de acordo com o que foi produzido então

ele vai ter é um material uma matéria prima que vai ser utilizada para aquele produto certo então ela tá representada aqui por essa função função C C de X o X é a quantidade de produtos produzidos então por exemplo se eu fizer um produto

60 x x + 500 vai dar 560 se não produzir nenhum produto já tem um custo 500 certo produzindo um eu tenho 560 2 620 3 680 E por aí vai pra receita eu tenho que para cada um vendido eu tô vendendo ele por

110 então 110 x 1 110 x 2 220 por aí vai até que ele chega num Ponto de Equilíbrio Aonde se eu fizer 10 produtos eu vou ter 1.00 de receita e 10000 de custo a partir daí eu tenho lucro né em

cima das vendas e aqui eu tenho o ponto onde ele tá mostrando eh que tá tá chegando ali o ponto de equilíbrio dentro dessas duas funções Esse é um exemplo bastante simples de do que ele alcança ele consegue alcançar o

10000 no caso e dizer qual é o ponto de equilíbrio ou Qual é a quantidade que eu tenho que produzir de X ok no não linear Nós temos duas equações aonde nós temos o custo e a receita onde a receita ela é

quadrática certo a função quadrática que você tá vendo Então é uma parábola e aqui a função linear né onde a gente a vai ver os dois pontos que estão sendo eh intercalados que ele vai mostrar aqui onde eles estão se batendo os dois ali

certo e daí ele vai aqui encontra o maior ou menor de acordo com o que for desejado na Ah foi solicitado para ele certo então nem sempre produzir mais vai ser bom que você vai ter um ponto que

você vai dizer se eu produzir mais eu vou ter problemas V ficar ruim para mim certo então continuando aqui então entendido essa parte que nós temos dois tipos de eh dois tipos principalmente que é o linear e o não linear né entendendo as

diferenças entre os dois o s resolve esses dois tipos e ainda evolucionário que não vou entrar em detalhes eh nós fos aqui um exemplo de como é que ele faz além disso que a gente viu ali nós temos as restrições por exemplo eu tenho

uma restrição de de custo ou eu tenho uma restrição de máquinas de mão deobra Eu tenho x horas para trabalhar com aquilo ali então não podemos ultrapassar correto então esses riscos que a gente vê aqui esses essas restrições aqui elas estão

restrição a b c e ele deixa a área possível só a área Azul então ele vai cortando ele cortando o gráfico até deixar apenas área azul e qual é o ponto máximo dele tá tentando deixar o valor máximo Então ele vem aqui e encontra o

valor máximo de acordo com essas duas barras Ok e o mínimo Como é que é o mínimo é invertido ele vai vir e vai alcançar o ponto mínimo que eu tenho aqui da área possível certo da menor área possível então ele vai vir aqui vai

encontrar o Men menor ponto possível dentro das restrições que eu tenho certo ele vai vir aqui vai encontrar na parte Azul qu o menor ponto possível para trazer al certo Além disso ele também consegue encontrar um determinado valor

se eu quero deixar esse valor el também Tenta achar para você certo então a gente vai ver agora com um exemplo prático de como é que a gente vai como a gente pode aplicar isso certo então primeiro ponto aqui exemplo de

solver Vou apagar aqui os valores e nós temos um problema de uma empresa de macarrão de massas que ela trabalha com toneladas de de produção de parafuso talharin e lasanha certo ele tem esses custos esses preços 2500 pro parafuso

17.500 Tonel tarim e 25.000 da Lasanha então a minha a minha pergunta é quantos que eu Quantos parafusos quantos talharin quanta lasanha eu tenho que produzir para que o meu resultado seja o melhor possível sendo que eu tenho um

tempo limite de produção de 80 um tempo limite de montagem de 88 e um tempo limite de embalagem de 99 tá então pro parafuso Eu tenho 3 horas de produção 19 de montagem e 20 de embalagem PR Tad de

Arinha em 16 de produção oito de montagem e 18 de embalagem e pra lasanha 11 de produção um de montagem embalagem então eles variam eu tenho esses limites se eu produzir aqui por exemplo um parafuso eu vou ver que ele vai

multiplicar o 1 por 3 20 vai dar aqui os valores né que não podem ultrapassar esses limites se eu produzir do aqui ele vai somar esses valores e vai multiplicar 2 por 16 8 18 e vai somar aqui o total certo e aqui o valor

automaticamente já multiplicado também el tá multiplicando a quantidade pelo preço e tá dando valor aqui correto Então vamos à nossa solução Claro que não dá pra gente ficar chutando valores para tentar achar os valores até dá mas vai dá muito trabalho quantas vezes que

eu vou ter que fazer isso né e não não pode não ser o maior valor também não garante nada garante uma solução fácil tá é fácil imagina uma coisa complexa cliquei em S eu vou clicar em redefinir tudo só para

limpar Tá eu já tinha feito você vai vir ah primeiro se você não tem o s Você vai vir em arquivo opções suplementos ir e vai marcar o s tá todos os Excel tem clica em S dá um ok vai vir

aqui em dados solver Ok então aqui na primeira opção nós temos Qual é o objetivo qual é a função objetivo é o resultado o resultado eu quero maximizar ele eu quero o melhor faturamento possível se eu quisesse o menor daí eu

botaria mínimo ou se eu quisesse um valor definido eu botaria 5000 5000 botaria o valor que você quisesse certo mas não eu quero o maior faturamento possível e quais são as células que vão alterar ele pergunta quais as células

alterando a quantidade produzida certo então o máximo valor com a quantidade produzida aqui sujeito as restrições então aqui as quantidades Elas têm que ser números inteiros tá não podem ser frações meia Tonelada por aí OK porque eu tô dizendo

que não precisa não pode tá adicionar eu quero que seja pelo menos maior igual a um ou seja tem que ser pelo menos uma tonelada de cada um pr ter pelo menos um mix para poder vender tá bom V adicionar

e a última eu vou adicionar aqui ó o total da o total do da produção não pode ser maior tem que ser menor ou igual que o limite dele correto adicionar fechar tá então tem que ser tonelada tem que

ser maior ou igual a um e o total da produção não pode ser maior que o limite correto aqui você vai mudar por LP Simplex pode testar o grg não linear também mas esse caso aqui é o método lip Simplex clicar em

resolver e dessa vez ele resolveu rápido olha o sver encontrou uma solução todas as restrições e condições de adequação foram satisfeitas manter a solução D Ok então ele tá dizendo que para melhor melhor faturamento possível para sua empresa você vai ter três macarrão

parafuso um talharim e cinco lasanha e daí você vai bater aqui ó tempo de produção 80 80 8870 de montagem e 99,93 de tempo de embalagem e você usou o máximo que você podia da tua fábrica e Def faturou r$ 10.000 certo Então essa é

com a com a sua capacidade produtiva e com essas esses valores aqui o melhor que você pode conseguir de resultado é isso aqui claro que a medida que você for reduzindo por exemplo o tempo de produção de agem melhorar a tua fábrica

esses valores vão melhorando certo então é um exercício interessante agora vamos para um segundo exercício o segundo exercício só dier aqui no segundo exercício nós temos uma Vidraçaria fazer um novo do do zero tá ó do zero sinos Vidraçaria aonde ele tem e

eu trabalho com Esquadrias Então ela faz janelas faz portas eh vários objetos feitos com com alumínio tá E essas barras elas vem de 6 m então só que as portas daí as quadras ele tem que cortar né em vários tamanhos

diferentes 1500 1300 800 MM né 600 1 m por aí vai então são cortes bastante variados então a pergunta dele era assim Eh quantas Barras eu tenho que cortar tenho que comprar para fazer o melhor qute possível para ter o menor

desperdício possível né porque as barras não podem ser Emendadas obviamente né E como é que essas barras devem ser cortadas Então essas três perguntas a gente vai responder usando o próprio solver tá então a primeira coisa que nós

vamos fazer aqui nós vamos colocar cortes os cortes Então vamos dizer que eu tenho eu queira eu tenha cortes de 10000 1200 600 800 900 1000 e 1300 certo então são esses quartes que eu tenho que eu vou querer fazer e quantos

quartes eu vou ter que fazer quantidade de quartes Eu quero quatro desse 2 1 1 5 3 e 2 certo Então essa é a quantidade de quartes que eu vou querer fazer correto Ok e como é que qual é o tamanho

dessas Barras eu botei Vamos trabalhar com Seis Barras tá 6 m 2 3 4 5 6 Ok então Seis Barras vou aumentar um pouquinho aqui por enquanto tá assim então tá e agora a gente vai fazer o seguinte nós vamos fazer uma

multiplicação somar produto abre parênteses esses cortes F4 para travar ele multiplicado pela quantidade que eu colocar aqui certo certo eu já V explicar como é que fo funcionar e vamos passar para cá para todos terem a mesmo cálculo Ok então se

eu botar um aqui ele vai dizer ó 1500 já foi usado 1500 da barra de 6000 1200 então 2700 dá a barra de 6000 e por aí vai certo aqui nós temos uma coluna chamada total que nós vamos fazer

igual soma que a gente vai somar a quantidade de barras que estão sendo feitas por exemplo de 1500 certo e 1500 e 600 por aí vai Ok mas a gente tem um probleminha o solver ele pode pegar e dizer assim ah é Quatro

Barras beleza uma aqui uma aqui uma aqui uma aqui e uma aqui só que eu não quero que ele faça isso eu quero que ele use o mínimo possível de barras certo ele pode fazer de várias formas isso mas não eu

quero que ele faça o mínimo possível então como é que a gente vai fazer isso a gente criou um negócio chamado índice nós vamos dizer que essa é a barra 1 a 2 A3 A4 A5 e A6 e aqui nós

vamos ter somar produto Vai Multiplicar esses esses valores aqui por esses daqui fech parênteses e vai somar ou seja aqui eu tenho uma barra de 1500 certo aqui continua 15 aqui 15 x 1 se eu tiver uma barra aqui ó el

fazer Opa 1500 beleza mais 1500 x 2 dá 3000 então ele tá dizendo assim não esse não é o menor valor o menor seria se eu usasse aqui correto então ele vai usar o máximo que ele puder da primeira barra

depois o máximo que ele puder da segunda barra depois máximo da terceira da quarta da quinta e da sexta por último certo então só isso já resolveu o nosso problema aqui tá outra coisa que a gente vai fazer aqui ó uma formatação control

um vai botar aqui aliás B Inicial formatação condicional nova regra contenho igual a zer se o valor for igual a zero nós vamos formatar personalizado como ponto vírgula ponto vírgula e ponto e vírgula Isso aqui vai fazer com que quando for zero ele vai

preencher como vazio como branco como se fosse tivesse pintado de branco tá então se botar zero ó um tá ok se botar zero ele some ok tá ok então tá pronto o nosso modelo nós já temos a fórmula a

função objetivo que é essa daqui e aqui ele vai botar a quantidade de cortes e o total esses valores são variáveis a pessoa vai preencher tá bom vai botar ali os valores Deixa eu só fazer ele ficar bonitinho aqui formatar

de acordo com o que eu já tenho certo só para não ficar muito bagunçado então tem também de esteticar tá muito com formatação ok tá ok aqui então então tá vamos preencher só o finalzinho ali pronto OK exibir Tá bom então tá e aqui é o nosso

total né Lembrando que o somar produto tá aqui correto agora a gente vai fazer então sver funcionar vai dados sver nós vamos dizer qual é a de objetivo de objetivo essa daqui eu quero o mínimo de valor tá então ele vai usar

essas barras aqui primeiro mínimo de valor Ok eu quero mínimo de uso alterando as variáveis eu quero Que altere aqui bote as quantidades aqui para ele multiplicar e as restrições primeira restrição que esses valores eles T que ser inteiros porque tem não

pode ser n meio de 1500 faz sentido número inteiro tá o segundo que esse total ele tem que ser igual a essa quantidade de quartos Lógico né quantidade total de quartos tem que ser igual a quantidade solicitada e aqui os valores eles têm que ser menor

ou igual que o total tamanho da Barra Ok então essas três restrições certo o total eh os valores quantidade dos cortes tem que ser menor ou igual né tem que ser um número inteiro também e também Aliás o número tem que ser

exatamente a quantidade de cordes os números aqui tem que ser números inteiros tá e os valores o tamanho das Barras cortes Total a soma dos cortes não pode ultrapassar o tamanho total da Barra ok também vamos usar o LP Simplex

e aqui você vai clicar em resolver ele falou que sver encontrou uma solução D OK e ele vai mostrar aqui como é que você vai cortar essa barra Então a primeira barra você vai cortar quatro tamanhos de 1500 né 1,5 M na

segunda barra você vai cortar do de 1200 um de 1000 e 2 de 1300 vai dar 6 m também certinho Exatamente olha aqui você vai ter qu de 900 2 de 1000 e vai dar 5600 e no último você vai usar 600

890 que é o final então aqui ele usou o máximo que ele podia usar dessas Barras e depois sobrou ainda mais um pouquinho aqui né e minimizando as perdas e maximizando um lucro da pessoa lucro da vida Saria e

aqui também sobraram Duas Barras certo Duas Barras que não precisou comprar certo então é isso ele encontrou aqui o menor valor possível então é isso muito obrigado por ter assistido mais essa videoaula Amanhã sábado dia 11 nós vamos

ter um super entrevista com o MVP em Excel João Benito Ele tem muita história para contar trabalho com Excel há muito muito tempo e acho que vai ser imperdível tá então é isso nos nos vemos amanhã tá sendo meu convidado muito

obrigado um forte abraço e até mais valeu [Música]