Dicas do Microsoft Excel é ao Domingo…no Pplware – 56

24 Fev 2013

19 Comentários

Hoje vamos aprender a trabalhar com o Solver

Ora vivam caros leitores. Mais um Domingo e mais uma artigo da rubrica “Dicas do Microsoft Excel é ao Domingo….no Pplware”. Lembramos que esta é uma rubrica “aberta” a todos os leitores que pretendam contribuir. Para tal devem enviar-nos um e-mail com a dica que pretendem ver publicada. Caso tenham também alguma dúvida ou problema a resolver no Excel, deixem essa informação nos comentários para que a comunidade Pplware ajude a resolver.

excel_00

Com o Solver (também conhecido como Solucionador ou Programação Linear), pode encontrar um valor óptimo para uma fórmula numa célula – chamada célula de destino. Ele ajusta os valores nas células especificadas que se alteram – chamadas células ajustáveis – para produzir o resultado que é obtido pela fórmula da célula de destino. Pode aplicar restrições (condições) de modo a limitar os valores que o Solver utiliza no modelo e as mesmas podem fazer referência às células que afectam a fórmula da célula de destino. Uma das principais diferenças entre o Solver e o Atingir Objectivo, é que o Solver permite colocar mais restrições em simultâneo.

Vamos apresentar um caso prático, para perceber melhor. Imagine que tem um orçamento limitado para publicidade em jornais, no entanto pretende-se diversificar em várias publicações com objectivo de atingir um determinado público-alvo.

Pode fazer download do ficheiro deste tutorial Excel do enunciado e soluções aqui.

Pretende-se saber quantos anúncios seriam necessários publicar (em cada jornal), tendo em conta que:
Custo Total não poderá exceder os 3.000€

O número mínimo de Leitores (Total) deverá ser 1.500.000
O número mínimo de anúncios, em cada jornal deverá ser 1
O número máximo de anúncios, em cada jornal deverá ser 3

Nota: o número de anúncios deverá ser um número inteiro: 1, 2 ou 3, portanto é necessário adicionar como restrição.
Nesta imagem pode ver as células amarelas que vão assumir 1, 2 ou 3 anúncios. As restantes células ao lado, vão ser preenchidas automaticamente após o Solver encontrar a solução ideal para as condicionantes deste problema.

Para poder utilizar o Solver deve instalar este suplemento. Aceda a Ficheiro > Opções > Suplementos > Gerir: Suplementos do Excel > Ir > Active o Solver e Ok

Notas:
Se for no Excel 2013, em inglês, File > Options > Add-ins > Manage: Excel Add-ins > Go > Solver Add-in.
Se for no Excel 2007: A instalação é idêntica, mas no separador dados deverá procurar por solucionador.
Se for no Excel 2003: Abra o menu Ferramentas > Suplementos. Seleccione Suplemento Solver e clique no botão OK. Para executar a função, abra o menu Ferramentas > Solver.

Agora aceda ao separador Dados > Solver.

Em Definir Objetivo, deve indicar a célula de destino, cujo resultado (deriva de uma fórmula necessariamente) deseja ajustar a um determinado valor, máximo ou mínimo. Neste caso, seleccione a célula que contém a fórmula a ajustar Custo Total ($E$8). Em Para, especificar qual a opção que deseja.

Neste exemplo, seleccione a opção que se ajusta ao que pretende (Máximo) em virtude de termos um plafond de 3000€. Alterando as Células de Variável, são as células ajustáveis, ou seja, as células cujos valores vão ser alterados pelo Solver, até que a solução do problema seja encontrada. Neste caso, seleccione as células que irão sofrer alteração $D$2:$D$7 que corresponde ao número de anúncios a realizar em cada jornal (células amarelas).

Sujeito às restrições, visualiza as restrições definidas pelo problema. As restrições são condições que precisam de ser satisfeitas pela solução. Clique em Adicionar para adicionar as restrições. Depois de adicionar a primeira, continue a clicar no adicionar e na última clique em OK. No nosso exemplo, indique as seguintes condições para a resolução do problema.

$E$8 <= $F$11 – O Custo Total não deve exceder o valor orçamentado para publicidade
$G$8 >= $F$12 – O mínimo Total de Leitores deve ser 1500
$D$2:$D$7 >= $F$13 – O nº mínimo de anúncios por jornal deve ser 1
$D$2:$D$7 <= $F$14 – O nº máximo de anúncios por jornal deve ser 3
$D$2:$D$7 = int

Clique no botão Resolver. Durante alguns segundos o MS Excel irá realizar cálculos iterativos (existe um limite, configurável nas opções do Excel, como alguns cálculos não têm solução por defeito existe um limite de tentativas)

Clique em Ok para aceitar a solução apresentada pelo Solver ou clique em Cancelar para repor os valores iniciais.

Veja agora nesta imagem a solução apresentada para o problema

Tutorial escrito por Vasco Marques, Microsoft Certified Trainer, Microsoft Office Master Instructor e Microsoft Excel Expert. Ver mini CV em Vasco Marques

Que utilidade esta ferramenta pode ter no seu contexto profissional?

PUB

Autor: Pedro Pinto

Partilhar:

Tags:

Excel Solver tutorial

PUB.

Questão Semanal

Loading …

Arquivo de Questões

PUB.

Velocímetro Pplware

Teste a velocidade da sua Internet

Arquivo

Artigo anterior

Blizzard anuncia Diablo III para Playstation 3 e 4

Próximo artigo

RainbowDrive – Vários serviços Cloud na mesma interface

PUB

Comentários

Deixe um comentário Cancelar resposta

Tiago

24 de Fevereiro de 2013

Boas

Precisava de fazer uma macro que guardasse os primeiros valores gerados por uma função aleatório, alguém me sabe dizer como o fazer?

Obrigado

Responder
1. José Augusto
  
  28 de Fevereiro de 2013
  
  ‘ Seleccione um intervalo de células
  ‘ de uma folha Excel;
  ‘
  ‘ Execute a macro que se segue
  ‘
  ‘ O intervalo seleccionado ficará
  ‘ preenchido com números aleatórios
  ‘ ———————————————-
  Sub aleatórios()
  Dim a As Range, i As Integer, j As Integer
  Set a = Selection
  Randomize ‘ inicializa lista
  For i = 1 To a.Rows.Count
  For j = 1 To a.Columns.Count
  a(i, j) = Rnd
  Next j
  Next i
  End Sub
  ‘ ————————————————
  ‘ Espero que ajude
  
  Responder
Amarelo

24 de Fevereiro de 2013

Uso com frequência nas minhas aulas… Muito bom o tutorial…

Responder
Hugo Matias

24 de Fevereiro de 2013

Extremamente interessante. Desconhecia completamente, mas teria dado jeito à muito tempo.

Responder
marta

24 de Fevereiro de 2013

Muito bom

Responder
paulo g.

25 de Fevereiro de 2013

Para quem tem LibreOffice calc também pode resolver este problema, indo ao menu Ferramentas → Sistema de Resolução.
Depois colocar os valores de acordo com a imagem que está no ficheiro, que como poderão observar são colocados de forma ligeiramente diferente do M$Excel. Obrigado ao Vasco por se disponibilizar (partilhar) este exemplo.
Tem a resolução com imagem descritiva, aqui:https://docs.google.com/file/d/0B1_jQSr740ArOGZIRnFWVnk2VzA/edit?usp=sharing
PS: Deixei os erros ortográficos de propósito, de modo a poderem ser corrigidos com a tecla do lado direito…

Responder
1. Pedro Pinto
  
  25 de Fevereiro de 2013
  
  Paulo preciso dos teus tutoriais sobre LibreOffice 🙂
  
  Responder
  1. paulo g.
    
    25 de Fevereiro de 2013
    
    Converter de M$Office para LibreOffice dá menos trabalho… dos tutoriais já feitos, aprendo mais pouco e com novas ferramentas.
    
    Responder
2. vasco marques
  
  25 de Fevereiro de 2013
  
  Muito obrigado Paulo G. pela adaptação para o Calc.LibreOffice que também gosto muito. Gosto das duas ferramentas, sendo que cada uma delas se adequa a necessidades diferentes. Assim ficamos com os 2 cenários, para que cada um use de acordo com a solução que tem.
  
  Responder
Jorge Geraldes

24 de Outubro de 2013

Será possível através do Visual Basic definir uma folha de cãlculo de forma a que o utilizador defina um bloco de céluas que não são protegidas e proteger tudo o resto?
Grato

Responder
Dário Moura

8 de Dezembro de 2014

Boa Noite pessoal do PPLWARE

Necessito da vossa ajuda numa situação do Excell

Eu tenho um folha de calculo e o que pretendo fazer é uma contagem de quantas células preenchidas a uma determinada cor existem numa coluna.

Como o posso fazer?

Obrigado

Responder
1. José Augusto
  
  10 de Dezembro de 2014
  
  Caro Dário Moura
  
  Para resolver o seu problema torna-se necessário usar VBA.
  Escreva a seguinte função:
  Function ContarCor(Intervalo As Range, Cor As Range) As Long
  ‘Elaborada por José Augusto
  ’10-12-2004
  
  ‘Conta o número de células do Intervalo que contém cor
  ‘de fundo igual à cor de fundo da célula Cor.
  
  ‘Intervalo é o intervalo de células que se pretende contar.
  ‘Cor é a célula que contém a cor que vai ser avaliada
  
  Dim i As Long, j As Long, res As Long, c As Long
  res = 0: c = Cor(1, 1).Interior.Color
  For i = 1 To Intervalo.Rows.Count
  For j = 1 To Intervalo.Columns.Count
  res = res + IIf(Intervalo(i, j).Interior.Color = c, 1, 0)
  Next j
  Next i
  ContarCor = res
  End Function
  
  No Excel pode então usar a seguinte fórmula:
  
  =ContarCor(B2:C50;A1)
  
  A função irá contar o número de células do intervalo B2:C50 cuja cor de fundo seja igual à cor de fundo da célula A1.
  
  Espero que ajude.
  
  Responder
  1. Dário Moura
    
    10 de Dezembro de 2014
    
    Boas Noites
    
    Dica bastante util mesmo
    
    Funciona se o preenchimento for introduzido automaticamente, e se depois mudar a cor, tenho de entrar na função e pressionar enter para ele alterar o valor.
    
    Eu tenho um livro excell em que as cores estão com formatação condicional, em que muda as cores automaticamente consoante os valores introduzidos.
    
    Existe alguma maneira de a contagem das cores alterar automaticamente, conforme for mudando o valor das células selecionadas?
    
    Obrigado
    
    Responder
    1. José Augusto
      
      11 de Dezembro de 2014
      
      Caro Dário Moura
      
      Veja os seguintes artigos:
      http://www.jorgepaulino.com/2011/01/excel-contar-cores-na-formatacao.html
      
      http://www.jorgepaulino.com/2011/02/excel-verifica-cor-na-formatacao.html
      
      Julgo que respondem inteiramente ao que pretende.
      
      Cumprimentos.
      
      Responder
Margarida

29 de Julho de 2015

Boa tarde,

Tenho um ficheiro excel com duas folhas, na Folha1 tenho uma lista de nomes com mais alguns dados, moradas, contactos etc.
Depois tenho a Folha2, que eu preencho linha a linha com o nome da tal pessoa que faz parte da Folha1, e com a data do pedido dessa pessoa.
Aquilo que pretendo é que na Folha1 exista uma fórmula que vá buscar a data mais recente relacionada com aquela pessoa na Folha2.

Ou seja:

Folha2

20/01/2014 Joaquim Duarte
15/03/2014 Joaquim Duarte
28/07/2014 Joaquim Duarte

Automaticamente, na Folha1 deveria aparecer numa determinada coluna no nome “Joaquim Duarte” 28/07/2014 que é a data mais recente, isto para que de uma forma mais rápida se possa ver quando foi a última vez que aquele cliente contactou connosco.

É possível alguém dar uma ajuda?

Obrigada.

Responder
1. José Augusto
  
  30 de Julho de 2015
  
  Cara Margarida
  Vou considerar que na Folha1 os nomes estão na coluna A a partir da linha 2 e que a tabela com as datas de contacto (coluna A) e nomes (coluna B) estão na Folha2!A2:B4000
  A data do último contacto (coluna E por exemplo) pode ser obtida através da fórmula matricial
  
  =MÁXIMO((Folha2!$B$2:$B$4000=A2)*Folha2!$A$2:$A$4000)
  
  Esta fórmula deve então ser escrita na Folha1 numa célula da linha 2 da coluna E e será válida por cópia para as restantes linhas dessa coluna.
  Atenção que esta é uma fórmula matricial e como tal deve ser validada não com a tecla Enter mas sim com as teclas Ctrl + Shift +Enter (mantendo CTRL e Shift premidas, premir Enter).
  Na barra de fórmulas a fórmula ficará escrita como {=MÁXIMO((Folha2!$B$2:$B$4000=A2)*Folha2!$A$2:$A$4000)}
  Espero ter ajudado.
  Cumprimentos
  
  Responder
José Augusto

30 de Julho de 2015

Cara Margarida
Vou considerar que na Folha1 os nomes estão na coluna A a partir da linha 2 e que a tabela com as datas de contacto (coluna A) e nomes (coluna B) estão na Folha2!A2:B4000
A data do último contacto (coluna E por exemplo) pode ser obtida através da fórmula matricial

=MÁXIMO((Folha2!$B$2:$B$4000=A2)*Folha2!$A$2:$A$4000)

Esta fórmula deve então ser escrita na Folha1 numa célula da linha 2 da coluna E e será válida por cópia para as restantes linhas dessa coluna.
Atenção que esta é uma fórmula matricial e como tal deve ser validada não com a tecla Enter mas sim com as teclas Ctrl + Shift +Enter (mantendo CTRL e Shift premidas, premir Enter).
Na barra de fórmulas a fórmula ficará escrita como {=MÁXIMO((Folha2!$B$2:$B$4000=A2)*Folha2!$A$2:$A$4000)}
Espero ter ajudado.
Cumprimentos

Responder
1. Margarida
  
  30 de Julho de 2015
  
  Boa tarde José Augusto,
  
  Desde já agradeço a sua ajuda, no entanto devo estar a colocar algo errado, porque o resultado que deveria obter na coluna da data do último contacto é “0-jan” em todos os nomes…
  
  =MÁXIMO((Folha2!$B$2:$B$4000=B3)*Folha2!$A$2:$A$4000)
  
  A única coisa que alterei foi a célula a partir de onde estão os nomes na Folha1, que no caso começam na célula B3.
  
  Obrigada.
  
  Responder
  1. José Augusto
    
    30 de Julho de 2015
    
    Boa tarde
    Por favor verifique se o nome que introduziu na Folha2, coluna B, é exactamente igual ao introduzido em B3 na Folha1.
    Verifique ainda se, na barra de fórmulas lhe aparecem as chavetas a englobar a fórmula.
    Cumprimentos
    
    Responder

Aviso:

Todo e qualquer texto publicado na internet através deste sistema não reflete, necessariamente, a opinião deste site ou do(s) seu(s) autor(es). Os comentários publicados através deste sistema são de exclusiva e integral responsabilidade e autoria dos leitores que dele fizerem uso. A administração deste site reserva-se, desde já, no direito de excluir comentários e textos que julgar ofensivos, difamatórios, caluniosos, preconceituosos ou de alguma forma prejudiciais a terceiros. Textos de caráter promocional ou inseridos no sistema sem a devida identificação do seu autor (nome completo e endereço válido de email) também poderão ser excluídos.