Professor: Fabrízzio Condé de Oliveira

Disciplina: CDI I Curso: Engenharia de Produção

CONCEITOS DE EXCEL NECESSÁRIOS PARA O SOLVER Referências absolutas de células

Em fórmulas, referências absolutas para células são designadas pela representação de dólar ($) antes do numero de linha e/ou antes da letra da coluna. Por exemplo, $E$5 é uma referência absoluta. Quando incluída numa fórmula que está sendo copiada ou cortada, ela sempre tratará da mesma célula E5, independentemente de onde a fórmula é colada. Referências absolutas também podem ser aplicadas apenas à linha ou apenas à coluna. Os caracteres $ podem ser digitados diretamente em fórmulas ao entrar em referências de células, ou selecione o endereço da célula na fórmula e pressione a tecla F4 para inserir automaticamente os caracteres $.

Referências relativas de cédulas

Nas fórmulas, acontecem referências relativas quando nenhum caractere $ é usado no endereço da célula que aparece numa fórmula, fazendo o Excel se lembrar da referência de célula relativa à célula na qual a fórmula ou referência está contida. Usar a fórmula com referências relativas e copiá-la (através do comando “Preencher Acima” ou Copiar e Colar) evita a digitação de cada fórmula de célula. Por exemplo, se a fórmula na célula G10 é “=F10+G9”, e essa fórmula é copiada para a célula G11, ela se transforma em “=F11+G10” porque a referências relativas são mantidas ao copiar.

Matrizes de Células

Uma matriz é simplesmente um intervalo de células adjacentes numa planilha do Excel. A notação para uma matriz inclui a célula da extrema esquerda superior seguida por doispontos (:) , seguida pelo endereço da célula na extrema direita inferior. Por exemplo, “A1:B3” representa uma matriz que tem duas colunas de largura e três linhas de comprimento. Essa matriz começa na célula A1, inclui as células A2, A3, B1, B2 e termina com a célula B3.

A função SOMARPRODUTO

Essa função multiplica as células correspondentes nas matrizes dadas (de tamanho igual) e retorna a soma desses produtos, daí o nome SOMARPRODUTO. (Ver figura a seguir) A fórmula é inserida na célula com a fórmula “=SOMARPRODUTO(matriz_1,matriz_2,...)” onde matriz_1 e matriz_2 são as matrizes cujas células correspondentes você quer multiplicar juntas e depois adicionar os resultados. Os argumentos de matrizes precisam ter as mesmas dimensões, ou seja, o mesmo número de linhas e colunas para cada matriz (de outro modo, SOMARPRODUTO devolve o erro #VALUE!). Por exemplo, na figura a seguir, “P1, P2, P3” é o preço pago para cada um dos três itens e “Q1, Q2, Q3” é a quantidade correspondente de cada item comprado. O custo total é a soma dos três cálculos correspondentes de “preço vezes quantidade”, ou seja, 3*2 + 4*7 + 8*6. De modo especial para matrizes grandes, usar os intervalos de célula na função SOMARPRODUTO é mais eficaz do que escrever uma única fórmula longa que contém os cálculos imediatos. Importante: SOMARPRODUTO trata de células-membro de matriz que não são numéricas como se contivessem zeros.

de ferramentas ou vá até o menu INSERIR e selecione o item Funçãoe no submenu

Para acionar a função SOMARPRODUTO, clique no ícone de inserir função (fx) na barra categoria, escolha Matemática e trigonométrica. Logo em seguida, escolha no menu selecione uma função o item SOMARPRODUTO.

3 APLICAÇÃO DO SOLVER

Figura 1-O modelo PL simplificado de produção da Oak Products com as fórmulas.

Em modelos PL de planilha, folga é o termo genérico usado para descrever a diferença entre a função de restrição e o lado direito computado de modo a ser não-negativo.

Para uma limitação ≤, a folga é o lado direito subtraído do lado esquerdo. Para uma limitação ≥, a folga é o lado esquerdo subtraído do lado direito.

Os símbolos de desigualdade na coluna E que separam os valores da esquerda e da direita não são usados pelo Solver e, conseqüentemente, são opcionais. Mas usá-los facilita muito a documentação do modelo.

Figura 2- O modelo PL de produção da Oak Products para C = 20 e M = 80.

5 Figura 3- Executando o Solver.

Figura 4- Parâmetros do Solver do diálogo-padrão. Figura 5- Especificando a cédula de destino do Solver.

Figura 6- Minimizando o diálogo de Parâmetro do Solver.

Figura 7- Especificando cédulas variáveis do Solver. Figura 8- Especificando as restrições “<=” do LHS da Oak Products.

8 Figura 9- Especificando as restrições “<=” do RHS da Oak Products.

Figura 10- Especificando a restrição “>=” do LHS da Oak Products. Figura 1- Especificação de Parâmetros do Solver da Oak Products.

Figura 12- Especificando restrições de linearidade e não-negatividade de modelo. Figura 13- A caixa de diálogo de Resultados do Solver.

Figura 14- Modelo da Oak Products OTIMIZADO.

Explicação sobre as opções do Solver

Tempo máximo permite a definição do número de segundos antes do Solver parar. Iterações, de forma similar ao Tempo Máximo, permitem a especificação do número máximo de iterações (passos do algoritmo do Solver) antes de parar. Precisão é o grau de precisão do algoritmo do Solver (por exemplo, quão próximo do valor o lado esquerdo de uma restrição ele deve estar antes de ser considerado igual ao lado direito). Tolerância é usada para programas de inteiros. Ela especifica uma porcentagem dentro da qual a solução é garantida como sendo a ótima.

Se você busca a solução ideal, esse valor deve ser definido como zero. Se o tempo de execução for muito longo, você pode definir um valor mais alto (caso queira aceitar uma solução dentro desse percentual de idealização).

Caso o seu modelo seja um programa linear ou um programa linear de inteiros, você deve marcar a caixa Presumir modelo linear. Ela informa o Solver para utilizar o algoritmo simplex em vez de um algoritmo não-linear que consumirá um tempo maior (Método do Gradiente Reduzido Generalizado). A caixa Presumir não negativos deve ser marcada se você deseja que todas as mudanças nos seus valores de células sejam maiores ou iguais a zero.

Marque Mostrar resultado de iteração se deseja ver as informações iteração a iteração (isso pode realmente deixar as coisas mais lentas!). A caixa Usar escala automática é útil se o seu modelo apresentar uma escala deficiente (caso as entradas tenham ordens de magnitude drasticamente diferentes). Finalmente, a seção inferior da caixa de diálogo diz respeito às opções do algoritmo não-linear, a saber, como ele calcula as não-linearidades, como as taxas de mudança são estimadas e o tipo de técnica de pesquisa empregada.

Falando de forma geral, os valores padrão da maioria desses parâmetros funcionam bem. A coisa importante a ser lembrada é a marcação da caixa Presumir modelo linear se você tiver um programa linear ou um programa linear de inteiros. Marque Presumir não negativos se quiser que as mudanças nas células produzam somente valores não-negativos. Além disso, se estiver resolvendo um programa de inteiros e em busca da solução ideal, certifique-se de que a Tolerância seja definida como 0%.

Comentários