Excel 2010 Avanado

Senac So Paulo 201

OBJETIVOS Introduzir recursos para simulao e anlise de valores.

Excel 2010 Avanado

202 Senac So Paulo

Excel 2010 Avanado

Senac So Paulo 203

Atividade 1 Atingir Meta

Objetivo : Apresentar o recurso Atingir Meta que permite formular hipteses de variao

de um valor para que uma frmula apresente um determinado resultado.

Tarefa : Calcular quanto um item das entradas deve variar para alcanar um saldo positivo.

1. Abra o arquivo Metas.xlsx.

Nos meses de abril e junho os saldos so negativos, indicando que as somas das Entradas, que representam os valores que empresa prev receber, no conseguiro superar as sadas, 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 alcanar. 2. Clique no boto Teste de Hipteses do grupo Ferramentas de Dados da guia Dados. Escolha Atingir

meta...

3. No quadro Atingir meta informe a clula E3 no campo Definir clula:. 4. Em Para valor: digite 222000 que o valor a ser alcanado por E3. 5. Defina a clula E7 como clula varivel em Alternando clula:.

6. Clique em OK.

Excel 2010 Avanado

204 Senac So Paulo

A clula E7 (Duplicatas Desconto) ter seu valor alterado para que a clula E13 (soma das entradas) alcance 222.000. Se voc clicar em OK estes valores sero assumidos. Se voc clicar em Cancelar os valores anteriores permanecero.

Excel 2010 Avanado

Senac So Paulo 205

Atividade 2 Solver

Objetivo : Apresentar o recurso Solver que permite formular hipteses.

Tarefa : Formular hipteses de alterao de valores em uma planilha, aplicando restries 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 Opes. 4. Clique em Suplementos. 5. Certifique-se que no campo Gerenciar: est selecionada a opo Suplementos do Excel e clique no

boto Ir...

6. Marque a opo Solver e clique em OK.

Excel 2010 Avanado

206 Senac So Paulo

A pasta Solver.xlsx apresenta o clculo que definir o nmero de passageiros que determinado avio pode carregar de acordo com algumas restries conhecidas.

O problema calcular o nmero de passageiros que um avio pode levar em uma viagem. O peso total deste tipo de avio no pode ultrapassar 1.157 kg. Para a viagem pretendida ele consome 182 kg de combustvel, levar 91 kg no bagageiro e deve-se considerar que nos 2 assentos dianteiros permite um peso mximo de 154 kg. Um destes assentos ocupado pelo piloto. Para clculo do peso das pessoas a bordo considerado o valor de 77 kg. O avio vazio pesa 635 kg.

Desta forma voc tem as seguintes restries a considerar:

Peso do avio vazio = 635 kg sempre o mesmo.

Assentos dianteiros >= 77 kg e

Excel 2010 Avanado

Senac So Paulo 207

11. Clique no boto Adicionar para definir as restries. 12. No campo Referncia de Clula: selecione A11. 13. Escolha o operador =. 14. No campo Restrio: selecione C11.

15. Clique em Adicionar. 16. Repita os passos 12 a 15 para as restries do quadro abaixo:

A12 >= C12 A12

Excel 2010 Avanado

208 Senac So Paulo

18. Clique no boto Resolver.

Caso haja conflito entre as restries e as opes escolhidas o Solver apresentar uma mensagem informando que no encontrou uma soluo.

19. Selecione os relatrios Resposta, Sensibilidade e Limites. 20. Caso os valores apresentados sejam satisfatrios, marque a opo Manter Soluo do Solver. Caso

contrrio, marque Restaurar Valores Originais.

21. Clique em OK.

Voc pode transformar a resposta do Solver em um Cenrio. Para isto, clique no boto Salvar Cenrio...

Excel 2010 Avanado

Senac So Paulo 209

Atividade 3 Tabela de Dados

Objetivo : Conhecer a ferramenta Tabela de dados que formula hipteses de variao de

valores em uma frmula.

Tarefa : Criar tabela com os valores das parcelas de um pagamento considerando variao na taxa de juros e no nmero de perodos.

Voc criar uma tabela que contm os valores das parcelas de um financiamento combinando vrios prazos e vrias taxas de juros.

1. Abra o arquivo Tabela de Dados.xlsx.

Na clula B5 existe uma frmula que calcula o valor da parcela considerando um emprstimo de R$ 180.000,00, uma taxa de juros de 2,40% e um prazo de 36 meses - =PGTO(B2;B3;B1).

Excel 2010 Avanado

210 Senac So Paulo

Abaixo a frmula se repete na clula C7. Na rea prxima existem valores de perodos e taxas a serem calculados. Voc usar o recurso Tabela de Dados para estender os clculos.

2. Selecione o intervalo C7:H27. A frmula deve estar includa no intervalo onde os dados sero expandidos.

3. Clique em Teste de Hipteses do grupo Ferramentas de Dados da guia Dados. 4. Escolha a opo Tabela de Dados... 5. Preencha o campo Clula de entrada da linha com a clula que na frmula representa os dados que

esto dispostos em linha, neste caso, os perodos. Na frmula o perodo est na clula B3. Clique nesta clula com o mouse.

6. Preencha o campo Clula de entrada da coluna com a clula que na frmula representa os dados que esto dispostos em coluna, ou seja, as taxas, clula B2.

7. Clique em OK.

Excel 2010 Avanado

Senac So Paulo 211

Quando o valor do emprstimo, que representa o VF (Valor futuro) na frmula, positivo , o valor das parcelas sero negativas. Para que as parcelas sejam positivas altere a frmula introduzindo o sinal de menos antes da clula que na funo representa o Valor Futuro =PGTO(B2;B3;-B1).

O recurso Tabela de dados cria frmulas matriciais. Estas frmulas no podem ser alteradas individualmente porque formam um conjunto.

Excel 2010 Avanado

212 Senac So Paulo

ANOTAES

Excel 2010 Avanado

Senac So Paulo 213

Atividade 4 Inserir Tabela

Objetivo : Transformar um intervalo em uma tabela gerencivel.

Tarefas : Transformar dados de um intervalo em uma tabela.

Acrescentar totalizaes.

1. Abra o arquivo MovimentoTabela.xlsx. 2. Clique em qualquer clula do intervalo de dados. 3. Clique em Tabela do grupo Tabelas na guia Inserir. 4. Confirme ou selecione o intervalo de dados com os cabealhos, todas as linhas e todas as colunas.

5. Clique em OK.

Excel 2010 Avanado

214 Senac So Paulo

Surgem os botes do AutoFiltro e uma nova guia Ferramentas de Tabela.

6. Marque a caixa de verificao Linha de total do grupo Opes de Estilo de Tabela. 7. Na coluna desejada, clique no boto da linha de totais e escolha a funo que deseja para a coluna.

Se voc clicar em Mais Funes... surge o assistente Inserir Funo.

Na escolha das funes do primeiro menu introduzida a funo SUBTOTAL, que tem a seguinte sintaxe:

SUBTOTAL(operao;campo)

A operao um nmero que representa a funo a ser usada e campo o cabealho da coluna a ser operada delimitada por colchetes.

Excel 2010 Avanado

Senac So Paulo 215

Tabela com os cdigos possveis para as operaes:

OPERAO (INCLUINDO VALORES OCULTOS)

OPERAO (IGNORANDO VALORES OCULTOS)

FUNO

1 101 MDIA 2 102 CONT.NUM 3 103 CONT.VALORES 4 104 MXIMO 5 105 MN 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 Avanado

216 Senac So Paulo

Exerccio Proposto

1. Abra o arquivo Metas-exerccio.xlsx.

Qual deve ser o valor das receitas para que o Lucro descontando o IR atinja 350.000?

2. Abra o arquivo Solver-exerccio.xlsx.

Neste exerccio a empresa gua Preta telecomunicaes 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 sero provenientes dos scios e completados com emprstimos considerando que os emprstimos devem corresponder a 33% da soma das Contas a Receber e do Dinheiro dos scios.

3. Abra o arquivo Tabela-exerccio.xlsx.

Excel 2010 Avanado

Senac So Paulo 217

Preencha a coluna para os possveis valores assumidos por X. Perceba que os dados esto dispostos em coluna.

Excel 2010 Avanado

218 Senac So Paulo

ANOTAES