Roteiro Aula 17 excel 2010-1

Prévia do material em texto

78 
 Roteiro Aula 17 
 Solver 
 
 
- Abrir arquivo Aula17.xlsx 
 
- Planilha 1 
 
Usar a função PROCV (VLOOKUP) para buscar o NOME do jogador ao digitar o NÚMERO da 
camisa. 
 
- Planilha 2 
 
Usar a função PROCH (HLOOKUP)para buscar o NÚMERO da camisa ao digitar o NOME do 
jogador. 
 
- Planilha 3 
 
Utilizando a função SOMASE (SUMIF) calcular os seguintes totais: 
- Por Sexo 
- Por Raça 
- Esterilização 
 
 
Sexo Raça Idade Esterilizado Preço VALOR
Femea Husky Siberiano 60 dias Não R$ 1.200 TOTAL
Femea São Bernardo 65 dias Sim R$ 1.500 SEXO
Macho Poodle 60 dias Não R$ 2.000 Femea R$ 17.600
Macho Maltes 55 dias Não R$ 800 Macho R$ 23.570
Femea Cocker Spaniel 60 dias Sim R$ 1.000
Macho Sharpei 60 dias Sim R$ 1.000
Macho Afghandhound 55 dias Não R$ 1.200 VALOR
Femea Maltes 45 dias Não R$ 1.800 TOTAL
Femea Cocker Spaniel 60 dias Não R$ 1.100 RAÇA
Macho Cocker Spaniel 60 dias Não R$ 1.100 Teckel R$ 2.500
Femea Poodle 65 dias Sim R$ 1.200 Husky Siberiano R$ 4.650
Macho Poodle 2 anos Sim R$ 250 São Bernardo R$ 3.620
Femea Cocker Spaniel 1 ano Sim R$ 450 Poodle R$ 4.350
Macho Maltes 60 dias Não R$ 1.600 Maltes R$ 8.300
Macho Sharpei 50 dias Não R$ 2.300 Cocker Spaniel R$ 4.750
Femea Afghandhound 60 dias Não R$ 3.000 Sharpei R$ 5.800
Macho Afghandhound 60 dias Não R$ 3.000 Afghandhound R$ 7.200
Macho Maltes 60 dias Sim R$ 1.800
Macho Husky Siberiano 55 dias Sim R$ 1.700
Femea Husky Siberiano 50 dias Não R$ 1.750 VALOR
Femea Poodle 68 dias Sim R$ 900 TOTAL
Macho São Bernardo 70 dias Não R$ 2.120 ESTERILIZADO
Macho Teckel 70 dias Sim R$ 1.300 Sim R$ 13.600
Femea Teckel 60 dias Não R$ 1.200 Não R$ 27.570
Macho Cocker Spaniel 55 dias Não R$ 1.100
Macho Maltes 65 dias Não R$ 2.300
Femea Sharpei 70 dias Sim R$ 2.500
PET SHOP CAES
 
 
79 
- Planilha 4 
 
- Calcular o Desconto usando a função PROCV (VLOOKUP) considerando as informações da 
Tabela de Descontos. 
 
 Dica: o desconto dependerá do número de unidades adquiridas pela concessionária. 
 
- Calcular o Preço Líquido retirando da coluna preço a porcentagem de desconto. 
 
 Dica: fazer =PREÇO – (PREÇO x DESCONTO) 
 
- Calcular o Total (em Reais) multiplicando o Preço Líquido pelo número de veículos 
pedidos pelo cliente. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
CHEVROLET
Número de 
veículos 
pedidos pela 
concessionária
Modelo do Automóvel Preço Desconto
Preço
líquido
Valor Total
por Modelo
34 Corsa R$ 23.000 50% R$ 11.500 R$ 391.000
33 Celta R$ 30.000 50% R$ 15.000 R$ 495.000
27 Celta Sport R$ 35.000 45% R$ 19.250 R$ 519.750
24 Astra 2P R$ 40.000 40% R$ 24.000 R$ 576.000
22 Astra 4P R$ 45.000 40% R$ 27.000 R$ 594.000
4 S10 Cab. Dup. Flex R$ 50.000 5% R$ 47.500 R$ 190.000
5 S10 Diesel 4x4 R$ 70.000 10% R$ 63.000 R$ 315.000
6 S10 Cab. Dup.4x4 Diesel R$ 80.000 10% R$ 72.000 R$ 432.000
Número de 
Veículos 
Pedidos
Desconto
0 5%
5 10%
10 15%
15 20%
20 40%
25 45%
30 50%
Tabela de Descontos
 
 
80 
1- Solver. 
 
SOLVER é um programa de otimização interno ao Excel. 
 
Problemas: 
Qual a mistura de ingredientes irá maximizar o lucro? 
Quais rotas de transporte irão minimizar os custos de envio enquanto atingem a demanda? 
 
Esses tipos de problemas têm sido tradicionalmente resolvidos por métodos numéricos como 
a Programação Linear e a Programação Não-Linear. 
 
Todo um campo matemático conhecido como Pesquisa Operacional foi desenvolvido para 
lidar com tais problemas, que são encontrados em todos os tipos de disciplinas. 
 
O SOLVER usa um método iterativo para obter as soluções dos problemas. 
 
O SOLVER é um Suplemento do Microsoft Excel. 
 
O Suplemento é uma funcionalidade complementar que adiciona comandos personalizados e 
recursos especializados do Microsoft Office 2007. 
 
É comum o fato de que muitos Suplementos não estejam adicionados automaticamente. 
Portanto, se desejarmos utilizá-los, devemos adicioná-los. 
 
Para adicionar o Suplemento SOLVER, basta acionar o Botão Office e selecionar Opções 
do Excel. 
 
 
 
 
 
81 
 Surgirá a seguinte janela: 
 
 Selecione Suplementos (Add-Ins) e “Suplementos do Excel” “Ir”. 
 Surgirá a janela de Suplementos. 
 
 Selecione Solver. OK. 
 
 
 
 
 
82 
 
- Planilha 5 
 
 
Guia Dados / Análise / Solver 
 
 
 
Objetivo: 
Encontrar a quantidade ideal de cada veículo para que o valor do 
orçamento não exceda o limite estabelecido. 
 
Antes de iniciar o solver é necessário preparar a tabela: 
 
 É necessário encontrar os valores da coluna CUSTO TOTAL multiplicando o CUSTO 
pelo NÚMERO DE VIATURAS. 
 
 Em seguida é necessário calcular o CUSTO TOTAL DA FROTA fazendo o somatório da 
coluna CUSTO TOTAL. 
 
Acionar a ferramenta SOLVER. Surgirá a janela para inserir os parâmetros do solver. 
 
 
 
83 
 
Definir célula de destino: E14 
Igual a: (Máx) 
Células Variáveis: selecionar o intervalo (D8:D11) – Variáveis do Problema 
 
Submeter às Restrições: (as restrições devem ser ADICIONADAS individualmente) 
 Variáveis devem ser maiores ou iguais a 1. 
 Variáveis devem ser números inteiros (int). 
 A célula de destino deve apresentar valor menor ou igual ao valor disponível no 
orçamento. 
 
Clicar em RESOLVER para calcular o resultado. 
 
 
 
 
 
 
- Planilha 6 
 
Considerar o problema anterior com orçamento de R$ 300.000,00. 
 
 
 
 
 
R$ 75.000
VEICULO CUSTO N
o
 DE VIATURAS CUSTO TOTAL
Ambulancia R$ 22.000 1,00 R$ 22.000
Furgao R$ 17.000 1,00 R$ 17.000
Pick-up R$ 16.000 1,00 R$ 16.000
Comum R$ 12.000 1,00 R$ 12.000
CUSTO TOTAL
DA FROTA
R$ 67.000
ORÇAMENTO DISPONIVEL
R$ 300.000
VEICULO CUSTO N
o
 DE VIATURAS CUSTO TOTAL
Ambulancia R$ 22.000 1,00 R$ 22.000
Furgao R$ 17.000 9,00 R$ 153.000
Pick-up R$ 16.000 4,00 R$ 64.000
Comum R$ 12.000 5,00 R$ 60.000
CUSTO TOTAL
DA FROTA
R$ 299.000
ORÇAMENTO DISPONIVEL
 
 
84 
- Planilha 7 
 
 
- Planilha 8 
 
0
5
10
15
20
25
30
35
40
45
50
1997 1998 1999 2000 2001
Va
lo
re
s e
m
 %
Ano da Pesquisa
Marcas de Cerveja
Antarctica Brahma Skol Kaiser Schincariol
 
 
85 
 
 
 
Criar um gráfico com o intervalo (A3:F8). 
- Tipo de gráfico: Linhas / Linhas com Marcadores. 
- Titulo do gráfico – Acima do Gráfico: Marcas de Cerveja 
 
- Titulo do Eixo Horizontal Principal: 
– Título abaixo do eixo: Ano da Pesquisa 
- Tamanho da fonte: 12 
 
- Titulo do Eixo Vertical Principal – Título Girado: 
– Título girado: Valores em % 
- Tamanho da fonte: 12 
 
- Rótulos de dados: nenhum 
- Tabela de dados: nenhuma 
 
- Linhas de grade Horizontais Principais - mostrar linhas de grade principais. 
- Linhas de grade Verticais Principais - nenhuma. 
 
- Mostrar legenda abaixo. 
- Posicionar gráfico como objeto na planilha 8. 
 
 
 
 
 
 
Formatar Eixo Y. 
Selecionar o eixo, clique com o botão direito sobre o eixo e escolha Formatar Eixo. 
 
 
 
 
 
86 
 
Surgirá a janela: 
 
Em Opções de Eixo, selecione: 
 Máximo: Fixo 50,0 
 Unidade Principal: Fixo 5,0 
 
 
 
Formatar área de plotagem. 
 
Selecionar a área de plotagem, clique com o botão direito sobre a área e escolha Formatar 
Área de Plotagem. 
 
 
 
 
 
 
 
 
87 
 Tipo de Preenchimento: Preenchimento Gradual 
 
 
 
 
Formatar seqüência de dados ANTARCTICA. 
(Selecionar a série clicando em um dos marcadores, clique com o botão direito sobre o 
marcador e escolha Formatar Série de dados.) 
 
 - Opções de Marcador: Interno, Tipo Triângulo, Tamanho 9. 
 - Preenchimento de Marcador: preenchimento sólido, cor LARANJA . 
 - Cor da Linha: automática. 
 - Estilo da Linha: (aumentar a espessura da linha) largura 2,75pt. 
 
 
 
 
 
 
 
 
 
0
5
10
15
20
25
30
35
40
45
50
1997 1998 1999 2000 2001
Va
lo
re
s e
m
 %
Ano da Pesquisa
Marcas de Cerveja
Antarctica Brahma Skol Kaiser Schincariol
 
 
88 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
89 
 
Formatar seqüência de dados BRAHMA. 
(Selecionar a série clicando em um dos marcadores, clique com o botãodireito sobre o 
marcador e escolha Formatar Série de dados.) 
 
 - Opções de Marcador: Interno, Tipo Círculo, Tamanho 8. 
 - Preenchimento de Marcador: preenchimento sólido, cor AZUL ESCURO. 
 - Cor da Linha: automática. 
 - Estilo da Linha: (aumentar a espessura da linha) largura 2,75pt. 
 
 
Formatar seqüência de dados SKOL. 
(Selecionar a série clicando em um dos marcadores, clique com o botão direito sobre o 
marcador e escolha Formatar Série de dados.) 
 
 - Opções de Marcador: Interno, Tipo Quadrado, Tamanho 9. 
 - Preenchimento de Marcador: preenchimento sólido, cor VERMELHA. 
 - Cor da Linha: automática. 
 - Estilo da Linha: (aumentar a espessura da linha) largura 2,75pt. 
 
 
Formatar seqüência de dados KAISER. 
(Selecionar a série clicando em um dos marcadores, clique com o botão direito sobre o 
marcador e escolha Formatar Série de dados.) 
 
 - Opções de Marcador: Interno, Tipo Círculo, Tamanho 8. 
 - Preenchimento de Marcador: preenchimento sólido, cor AMARELA. 
 - Cor da Linha: automática. 
 - Estilo da Linha: (aumentar a espessura da linha) largura 2,75pt. 
 
 
 
 
0
5
10
15
20
25
30
35
40
45
50
1997 1998 1999 2000 2001
Va
lo
re
s e
m
 %
Ano da Pesquisa
Marcas de Cerveja
Antarctica Brahma Skol Kaiser Schincariol