Cap 12 Excel 2010 Avançado

Prévia do material em texto

Excel 2010 – Avançado 
 
 
 
Senac São Paulo 201 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
OBJETIVOS 
 Introduzir recursos para simulação e análise de valores. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
202 Senac São Paulo 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 203 
 
Atividade 1 – Atingir Meta 
 
Objetivo : • Apresentar o recurso Atingir Meta que permite formular hipóteses de variação 
de um valor para que uma fórmula apresente um determinado resultado. 
Tarefa : • Calcular quanto um item das entradas deve variar para alcançar um saldo 
positivo. 
 
1. Abra o arquivo Metas.xlsx. 
 
Nos meses de abril e junho os saldos são negativos, indicando que as somas das Entradas, que representam 
os valores que empresa prevê receber, não conseguirão superar as saídas, que representam os 
compromissos da empresa. Para ela captar recursos e aumentar a entrada de recursos será preciso realizar 
o desconto de duplicatas nesses meses para fazer frente aos compromissos. Você usará o recurso Atingir 
meta para definir quanto o item Duplicatas - Desconto deve alcançar. 
2. Clique no botão Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. Escolha Atingir 
meta... 
 
 
3. No quadro Atingir meta informe a célula E3 no campo Definir célula:. 
4. Em Para valor: digite 222000 que é o valor a ser alcançado por E3. 
5. Defina a célula E7 como célula variável em Alternando célula:. 
 
 
6. Clique em OK. 
Excel 2010 – Avançado 
 
 
 
 
204 Senac São Paulo 
 
 
A célula E7 (Duplicatas – Desconto) terá seu valor alterado para que a célula E13 (soma das entradas) 
alcance 222.000. Se você clicar em OK estes valores serão assumidos. Se você clicar em Cancelar os 
valores anteriores permanecerão. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 205 
 
Atividade 2 – Solver 
 
Objetivo : • Apresentar o recurso Solver que permite formular hipóteses. 
Tarefa : • Formular hipóteses de alteração de valores em uma planilha, aplicando 
restrições conhecidas. 
 
O recurso Solver consiste em um complemento que precisa ser carregado para que você possa usá-lo. 
Carregue este complemento. 
1. Abra o arquivo Solver.xlsx. 
2. Clique na guia Arquivo. 
3. Clique em Opções. 
4. Clique em Suplementos. 
5. Certifique-se que no campo Gerenciar: está selecionada a opção Suplementos do Excel e clique no 
botão Ir... 
 
 
6. Marque a opção Solver e clique em OK. 
Excel 2010 – Avançado 
 
 
 
 
206 Senac São Paulo 
 
 
A pasta Solver.xlsx apresenta o cálculo que definirá o número de passageiros que determinado avião pode 
carregar de acordo com algumas restrições conhecidas. 
O problema é calcular o número de passageiros que um avião pode levar em uma viagem. O peso total 
deste tipo de avião não pode ultrapassar 1.157 kg. Para a viagem pretendida ele consome 182 kg de 
combustível, levará 91 kg no bagageiro e deve-se considerar que nos 2 assentos dianteiros permite um 
peso máximo de 154 kg. Um destes assentos é ocupado pelo piloto. Para cálculo do peso das pessoas a 
bordo é considerado o valor de 77 kg. O avião vazio pesa 635 kg. 
Desta forma você tem as seguintes restrições a considerar: 
Peso do avião vazio = 635 kg É sempre o mesmo. 
Assentos dianteiros >= 77 kg e <= 154 kg 
Pelo menos um assento 
ocupado pelo piloto. 
Combustível = 182 kg Necessário para a viagem. 
Bagageiro = 91 kg Peso da bagagem. 
Passageiros ? Valor a ser encontrado. Cada passageiro = 77 kg. 
Peso máximo permitido <=1.157 kg A soma dos itens acima não 
pode ultrapassar este limite. 
 
Quantos passageiros pode-se transportar respeitando estes limites e maximizando o peso disponível?. 
7. Clique na guia Dados. Agora existe um novo grupo chamado Análise. 
8. Clique em Solver. 
 
A célula B7 apresenta o cálculo do peso total e quanto maior for este valor maior será o número de 
passageiros que poderá ser transformado. Portanto, você definirá esta célula como objetivo a ser 
maximizado. 
9. No campo Definir Objetivo: selecione a célula B7. 
10. No campo Alterando células variáveis: selecione o intervalo B2:B6. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 207 
11. Clique no botão Adicionar para definir as restrições. 
12. No campo Referência de Célula: selecione A11. 
13. Escolha o operador =. 
14. No campo Restrição: selecione C11. 
 
 
15. Clique em Adicionar. 
16. Repita os passos 12 a 15 para as restrições do quadro abaixo: 
 
A12 >= C12 
A12 <= E12 
A13 = C13 
A14 = C14 
A15 <= C15 
 
17. Após a última restrição clique em OK ao invés de Adicionar. 
 
Excel 2010 – Avançado 
 
 
 
 
208 Senac São Paulo 
 
18. Clique no botão Resolver. 
 
 
Caso haja conflito entre as restrições e as opções escolhidas o Solver apresentará uma 
mensagem informando que não encontrou uma solução. 
 
 
 
19. Selecione os relatórios Resposta, Sensibilidade e Limites. 
20. Caso os valores apresentados sejam satisfatórios, marque a opção Manter Solução do Solver. Caso 
contrário, marque Restaurar Valores Originais. 
21. Clique em OK. 
 
 
Você pode transformar a resposta do Solver em um Cenário. Para isto, clique no botão Salvar 
Cenário... 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 209 
 
Atividade 3 – Tabela de Dados 
 
Objetivo : • Conhecer a ferramenta Tabela de dados que formula hipóteses de variação de 
valores em uma fórmula. 
Tarefa : • Criar tabela com os valores das parcelas de um pagamento considerando 
variação na taxa de juros e no número de períodos. 
 
Você criará uma tabela que contém os valores das parcelas de um financiamento combinando vários prazos 
e várias taxas de juros. 
1. Abra o arquivo Tabela de Dados.xlsx. 
 
 
Na célula B5 existe uma fórmula que calcula o valor da parcela considerando um empréstimo de R$ 
180.000,00, uma taxa de juros de 2,40% e um prazo de 36 meses - =PGTO(B2;B3;B1). 
Excel 2010 – Avançado 
 
 
 
 
210 Senac São Paulo 
 
 
Abaixo a fórmula se repete na célula C7. Na área próxima existem valores de períodos e taxas a serem 
calculados. Você usará o recurso Tabela de Dados para estender os cálculos. 
 
2. Selecione o intervalo C7:H27. A fórmula deve estar incluída no intervalo onde os dados serão 
expandidos. 
3. Clique em Teste de Hipóteses do grupo Ferramentas de Dados da guia Dados. 
4. Escolha a opção Tabela de Dados... 
5. Preencha o campo Célula de entrada da linha com a célula que na fórmula representa os dados que 
estão dispostos em linha, neste caso, os períodos. Na fórmula o período está na célula B3. Clique nesta 
célula com o mouse. 
6. Preencha o campo Célula de entrada da coluna com a célula que na fórmula representa os dados que 
estão dispostos em coluna, ou seja, as taxas, célula B2. 
 
 
7. Clique em OK. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 211 
 
 
 
Quando o valor do empréstimo, que representa o VF (Valor futuro) na fórmula, é positivo , o 
valor das parcelas serão negativas. Para que as parcelas sejam positivas altere a fórmula 
introduzindo o sinal de menos antes da célula que na função representa o Valor Futuro 
=PGTO(B2;B3;-B1). 
 
 
O recurso Tabela de dados cria fórmulas matriciais. Estas fórmulasnão podem ser alteradas 
individualmente porque formam um conjunto. 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
212 Senac São Paulo 
 
ANOTAÇÕES 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 213 
 
Atividade 4 – Inserir Tabela 
 
Objetivo : • Transformar um intervalo em uma tabela gerenciável. 
Tarefas : • Transformar dados de um intervalo em uma tabela. 
 • Acrescentar totalizações. 
 
1. Abra o arquivo MovimentoTabela.xlsx. 
2. Clique em qualquer célula do intervalo de dados. 
3. Clique em Tabela do grupo Tabelas na guia Inserir. 
4. Confirme ou selecione o intervalo de dados com os cabeçalhos, todas as linhas e todas as colunas. 
 
 
5. Clique em OK. 
 
Excel 2010 – Avançado 
 
 
 
 
214 Senac São Paulo 
Surgem os botões do AutoFiltro e uma nova guia Ferramentas de Tabela. 
6. Marque a caixa de verificação Linha de total do grupo Opções de Estilo de Tabela. 
7. Na coluna desejada, clique no botão da linha de totais e escolha a função que deseja para a coluna. 
 
 
Se você clicar em Mais Funções... surge o assistente Inserir Função. 
 
 
Na escolha das funções do primeiro menu é introduzida a função SUBTOTAL, que tem a seguinte sintaxe: 
SUBTOTAL(operação;campo) 
 
A operação é um número que representa a função a ser usada e campo o cabeçalho da coluna a ser 
operada delimitada por colchetes. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 215 
 
 
Tabela com os códigos possíveis para as operações: 
OPERAÇÃO 
(INCLUINDO VALORES OCULTOS) 
OPERAÇÃO 
(IGNORANDO VALORES OCULTOS) 
FUNÇÃO 
1 101 MÉDIA 
2 102 CONT.NUM 
3 103 CONT.VALORES 
4 104 MÁXIMO 
5 105 MÍN 
6 106 PRODUTO 
7 107 DESVPAD.A 
8 108 DESVPAD.P 
9 109 SOMA 
10 110 VAR.A 
11 111 VAR.P 
 
Para formatar a tabela use a guia Design da Ferramentas de Tabela: 
 
 
8. Para transformar novamente a tabela em intervalo clique em Converter em Intervalo do grupo 
Ferramentas da guia Ferramentas de Tabela. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
216 Senac São Paulo 
 Exercício Proposto 
 
1. Abra o arquivo Metas-exercício.xlsx. 
 
 
Qual deve ser o valor das receitas para que o Lucro descontando o IR atinja 350.000? 
2. Abra o arquivo Solver-exercício.xlsx. 
 
Neste exercício a empresa Água Preta telecomunicações deseja obter um total de entradas igual a 926.000. 
O valor das Contas a Receber é igual a 576.000. Você deve calcular quais os valores que serão 
provenientes dos sócios e completados com empréstimos considerando que os empréstimos devem 
corresponder a 33% da soma das Contas a Receber e do Dinheiro dos sócios. 
 
 
3. Abra o arquivo Tabela-exercício.xlsx. 
Excel 2010 – Avançado 
 
 
 
Senac São Paulo 217 
 
 
Preencha a coluna para os possíveis valores assumidos por X. Perceba que os dados estão dispostos em 
coluna. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel 2010 – Avançado 
 
 
 
 
218 Senac São Paulo 
 
ANOTAÇÕES