TUTORIAL EXCEL

IMPLEMENTAÇÃO E RESOLUÇÃO DE MODELOS

MATEMÁTICOS UTILIZANDO A PLANILHA EXCEL

1. INTRODUÇÃO

Este tutorial apresenta, passo-a-passo, o

processo de implementação e resolução de
modelos matemáticos na planilha Excel.
Admite-se que o leitor apresenta um
conhecimento prévio do aplicativo e seja
capaz de realizar a entrada de dados e
fórmulas.
A identificação, na planilha, das variáveis,
parâmetros, restrições e função objetivo; e
processo de configuração e execução do
solver será detalhado neste tutorial.
O texto está organizado da seguinte forma:
a Seção 2 apresenta o modelo matemático
utilizado como base ao longo do tutorial; a
Seção 3 ilustra os passos para a descrição
do modelo, a execução do solver e a geração 3. OS PASSOS BÁSICOS NO EXCEL
de dados para a análise de sensibilidade do
modelo. O processo de resolução de modelos
matemáticos utilizando o solver da planilha
2. O MODELO MATEMÁTICO
Excel compreende, basicamente, as 3 fases
descritas a seguir:
Uma empresa produz 4 tipos de molduras,
diferenciadas por tamanho, formato e Fase 1 - Descrição do Modelo: inserção de
recursos utilizados para fabricação. A todos os parâmetros do problema, valores
empresa espera atender o mercado,
iniciais para as variáveis de decisão e os
respeitando as limitações de cada recurso:
cálculos que relacionam esses dados na
planilha. Em particular, a planilha deve incluir
a fórmula que relaciona a função objetivo às
células que representam as variáveis de
decisão, de tal maneira que qualquer
variação nestas últimas provoque a variação
correspondente na função objetivo.

Tabela 1. Recursos disponíveis e custos de produção. Fase 2 - Chamada do Solver: a chamada

do solver envolve a indicação das células
correspondentes à função objetivo, restrições
e variáveis do modelo; configuração dos
parâmetros de otimização e da exibição das
soluções.

Tabela 2. Condições de atendimento do mercado Fase 3 - Análise de Sensibilidade: após a

obtenção da solução ótima, é possível
O objetivo é determinar a quantidade a ser realizar análises das mudanças nessa
produzida de cada moldura a fim de solução em função de modificações nos
maximizar o lucro com as vendas : parâmetros do modelo. A análise de

MS428 – Programação Linear – Prof. Moretti 1/5

TUTORIAL EXCEL

sensibilidade é realizada sem a necessidade Passo 1: Criar a estrutura de apresentação

de novas execuções do solver. dos dados, localizando o grupo de células a
Assume-se que o solver está devidamente conter os parâmetros, variáveis, os cálculos
instalado na planilha Excel disponível para para as restrições e a função objetivo. No
uso do leitor. Se o menu “Ferramentas” não que segue, considera-se a estrutura ilustrada
apresentar a opção “Solver”, é necessário na Figura 2.
instalar esse suplemento.
Passo 2: Informar os parâmetros do modelo
de acordo com a estrutura de apresentação
adotada (veja Figura 3).

Figura 1. Localização da opção “Solver”.

3.1. DESCRIÇÃO DO MODELO

Segue abaixo a seqüência de

procedimentos para a descrição do modelo
matemático de produção de molduras (Seção
2) no Excel:

INSTALAÇÃO DO SOLVER

No menu “Ferramentas”, clique em

“Suplementos”, marque a opção “Solver”
e confirme a inserção.

Figura 2. Estrutura de apresentação dos dados.

Se a opção “Solver” não estiver listada

na caixa de suplementos disponíveis,
clique em “Procurar” e localize o Figura 3. Parâmetros do modelo.
suplemento “[Link]”.

MS428 – Programação Linear – Prof. Moretti 2/5

TUTORIAL EXCEL

Passo 3: Indicar as variáveis de decisão do =SOMARPRODUTO(B11:E11;B14:E14)-

modelo x1 , x 2 , x3 x 4 , através da inserção de SOMARPRODUTO(B3:B5;B19:B21)
valores (quaisquer) nas colunas B,C,D e E da
linha 14.

Figura 5. Indicação das restrições.

Figura 4. Variáveis de decisão do modelo.

Passo 4: Indicar as condições estabelecidas

pelas restrições e realizar os cálculos
necessários para tanto. O Excel não exibe as
restrições diretamente na planilha, sendo
diretamente especificadas na caixa de
diálogo do solver.
A figura 5 ilustra a planilha atualizada com
os cálculos e indicações de restrições do
modelo:

(i) Cálculo dos recursos a serem utilizados

na produção (limitados pela disponibilidade
dos mesmos). Tais valores devem refletir o
produto entre o número de molduras de cada Figura 6. Entrada da função objetivo
tipo e os montantes de cada recurso
utilizados na produção de cada unidade. No Passo 6: Criar rótulos para conjuntos de
Excel, tal cálculo pode ser realizado com a células que serão usadas para a descrição
utilização do comando “SOMARPRODUTO” : (na caixa de diálogo do solver) da função
objetivo, variáveis e restrições do problema.
B19 = SOMARPRODUTO(B8:E8;B14:E14) As células podem ser identificadas pelos
B20 = SOMARPRODUTO(B9:E9; B14:E14) seus endereços, mas atribuir rótulos a
BB21 = SOMARPRODUTO(B10:E10; B14:E14) conjuntos de células facilita a associação
com os elementos do modelo matemático.
(ii) Sinais de “ ” indicando as restrições de Para tanto, deve-se marcar o conjunto de
limitação de vendas, disponibilidade de células desejado e fornecer um rótulo na
horas, metal e vidro. caixa de texto localizada na porção superior
esquerda da tela (veja Figura 7).
Passo 5: Indicar o cálculo da margem total
(receita de venda menos os custos de
produção) na célula a conter o valor
otimizado da função objetivo (B23).

MS428 – Programação Linear – Prof. Moretti 3/5

TUTORIAL EXCEL

DETERMINAÇÃO DAS VARIÁVEIS

Para que o Solver proponha automáti-

camente as variáveis com base na célula
de função objetivo, clique em “Estimar”.

Para a descrição das restrições, deve-se

pressionar o botão “Adicionar” e preencher
os campos da caixa de edição de restrições
(Figuras 9 e 10) abertas pelo Excel:
Figura 7. Atribuição de rótulos a conjuntos de células.
Passo 1: insira a referência da célula ou o
Considere, para o próximo tópico, os rótulo do intervalo de células cujo valor você
seguintes rótulos : deseja restringir.

Passo 2: indique a relação “<=”, “=”, “>=”,

“núm” ou “bin” ) a ser imposta entre a célula
referenciada e a restrição. A relações “num”
e “bin” indicam variáveis inteiras e binárias,
respectivamente.

Passo 3: na caixa de texto “Restrição”,

Tabela 3. Rótulos de grupos de células.
indique o limitante da restrição (número,
3.2. CHAMADA DO SOLVER fórmula ou rótulo).

Ao acionar a opção “Solver” do menu

“Ferramentas” (veja Figura 1), o Excel abre a
caixa de configuração dos parâmetros do
solver, onde o usuário indica a célula
contendo a definição da função objetivo, o
sentido de otimização (maximização, minimi- Figura 9. Restrições de limite de vendas.
zação ou obtenção de valor determinado), o
intervalo de células correspondentes às
variáveis de decisão e as restrições do
problema. A Figura 8 ilustra o início do
preenchimento dos campos (função objetivo,
sentido de otimização e variáveis) – observe
a utilização dos rótulos criados.
Figura 10. Restrições de disponibilidade de recursos.

Figura 8. Indicação da função objetivo, sentido de

otimização e variáveis na configuração do solver. Figura 11. Restrições do modelo.

Algumas configurações adicionais (não-

negatividade das variáveis, por exemplo)

MS428 – Programação Linear – Prof. Moretti 4/5

TUTORIAL EXCEL

podem ser realizadas nos campos da caixa para todas as variáveis que não apresentem
de diálogo “Opções do Solver” (aberta ao limitantes inferiores definidos como restrições
pressionar o botão “Opções”). (caixa de diálogo “Adicionar restrição”).
Deve-se selecionar “Usar escala automática”
quando os tamanhos de entradas e saídas
forem muito diferentes (por exemplo, entrada
de dados expressa em milhões de reais e
função objetivo igual ao porcentual de lucro).
A opção “Mostrar resultado da iteração” força
o Solver a exibir os resultados de cada
iteração.

Finalmente, a chamada do solver é

realizada ao pressionar “Resolver” na tela da
Figura 8. Para visualizar a solução
encontrada pelo solver (e a planilha
atualizada com essa solução), deve-se
Figura 12. Configurações adicionais do solver. escolher a opção “Manter solução do Solver”
na caixa de diálogo ilustrada pela Figura 14.
Alterações nos campos “Tempo máximo” e
“Iterações” permitem a configuração do
É possível interromper a execução do
critério de parada do solver. Se o processo
solver pressionando a tecla “ESC”. A
de resolução atingir o limitante de tempo ou o
planilha é então recalculada com a
número máximo de iterações antes do solver solução da última iteração do solver.
encontrar uma solução, a caixa de diálogo da
Figura 13 é exibida, possibilitando a
continuidade do processo de resolução até a
solução ótima (botão “Continuar”) ou a
apresentação obtida até o momento (botão
„Parar”).

Figura 14. Atualização da planilha com a solução

encontrada pelo solver.

3.3. ANÁLISE DE SENSIBILIDADE

Figura 13. Configurações adicionais do solver.
A caixa de diálogo da Figura 14 permite
Os campos “Precisão”, “Tolerância” e ainda que sejam escolhidos relatórios de
“Convergência” permitem a configuração da saída do solver. Ao escolher “Resposta” o
precisão numérica das soluções, da Excel inclui a planilha “Relatório de
tolerância nas restrições de integralidade e Resposta” contendo os valores da função
do grau de convergência do solver (quando a objetivo, restrições e variáveis. A seleção do
alteração relativa no valor da função objetivo item “Sensibilidade” gera um relatório
- nas cinco últimas iterações - é menor que o contendo o custo reduzido das variáveis, o
grau de convergência, a execução do solver preço sombra das restrições e os limites de
é interrompida), respectivamente. acréscimo/decréscimo nas variáveis e
restrições que mantêm a base atual
(variáveis não nulas) como ótima. A opção
Para indicar ao solver que o modelo “Limites” permite a geração de um relatório
matemático é linear, deve-se selecionar a contendo a contribuição de cada variável no
opção “Presumir modelo linear”. A opção valor final da função objetivo.
“Presumir não negativos” indica ao solver a
existência de um limitante inferior igual a zero

MS428 – Programação Linear – Prof. Moretti 5/5