Baixe Excel avançado e outras Notas de estudo em PDF para Informática, somente na Docsity!
F.A. Consultoria e Treinamento Rua General Jardim, 618 cj. 32 01223-010 - São Paulo - SP Tel: (11) 3256- Fax: (11) 3256- www.faconsultoria.com
Todos os direitos reservados. Nenhuma parte desta publicação poderá ser reproduzida, guardada por algum sistema de recuperação ou transmitida de qualquer modo ou por qualquer outro meio, seja este eletrônico, mecânico, de fotocópia, de gravação, ou outros, sem prévia autorização, por escrito, da F.A. Consultoria e Treinamento.
Responsável: Fernando Andrade Redação e organização: Fabíola Luz
Tabela Dinâmica
Para ver como funciona uma tabela dinâmica, vamos precisar de uma tabela como esta ao lado.
- Digite sua planilha.
Dica: Esta planilha também é chamada de banco de dados , porque tem uma linha com cabeçalhos de colunas e as outras linhas são dados.
Para trabalhar com banco de dados, você precisa mostrar ao Excel que planilha usar como banco de dados. Você faz isso deixando o cursor em qualquer célula preenchida.
- Clique em qualquer célula preenchida da planilha.
- Escolha Dados , Relatório de tabela e gráfico dinâmicos...
Você pode mostrar seus dados na forma de linha e coluna (como na figura abaixo) ou você pode organizar seus dados e exibi-los por assunto (como na figura acima). É o que faz uma tabela dinâmica. E ela é dinâmica porque você pode alterar dinamicamente a representação dos dados.
O que significam cada uma das informações que o assistente pede para você preencher?
Dados : São os números que aparecerão na tabela dinâmica. Perceba que não são exatamente os mesmos números que aparecem na planilha usada como base, mas sim um cálculo feito com estes números. Coluna : Contém as informações mais abrangentes da planilha. Neste exemplo as colunas conterão o tipo de produto vendido ( frutas/legumes ou laticínios ). Linha : Contém os detalhes. Na figura acima você vê o vendedor e o ano em que foi feita a venda. Página : Mostra as informações agrupadas por critérios mais amplos. No nosso exemplo separamos nossos dados por região em que foi feita a venda.
- Arraste para a área de Dados o campo Vendas.
Perceba que o assistente já agrupa as vendas somando cada um dos valores. Em vez de somá- los, você também pode calcular a média, obter o valor máximo etc. Veja como fazer isso nas páginas seguintes.
- Para Coluna arraste o campo Tipo.
- Para Linha arraste primeiro o campo Vendedor e depois o campo Ano.
- Para a Página arraste Região.
10
11
12
13
Dados
Dica: Como você pôde ver, é possível colocar mais de um campo em cada área.
- Clique em OK para finalizar a definição do layout e depois clique em Concluir para finalizar o assistente.
Veja ao lado como ficou nossa tabela dinâmica.
- Os campos possuem filtros para que possamos escolher os dados que serão exibidos. Por exemplo, para ver apenas os dados de um ano específico, clique na seta do campo Ano e deixe assinalado apenas o ano desejado.
- Em caso de querer retirar algum campo, clique sobre o campo (cinza) e arraste-o para fora da tabela. Veja que aparece um X vermelho indicando que o campo será eliminado quando você soltar o mouse.
- Você também pode trocar os campos de lugar, basta arrastá-los para a nova posição. Por exemplo, você pode retirar Ano da linha e colocá-lo na coluna. É por isso que chamamos este recurso de Tabela Dinâmica.
15
Alterando a fórmula que reúne os campos da tabela dinâmica
O assistente de tabela dinâmica automaticamente soma os valores dos campos que você arrasta a região Dados. Mas é possível definir outro tipo de cálculo para eles.
- Clique em cima do campo Soma de vendas na tabela dinâmica.
- Na barra de ferramentas que aparece, clique no botão Configurações de campo.
- Escolha o tipo de cálculo que você deseja fazer. No exemplo mostrado na figura ao lado escolhemos Média.
- Clique no botão OK. (^) 3
A operação Soma é definida automaticamente
Trabalhando com um conjunto de hipóteses
- Prepare sua planilha conforme a ilustração ao lado.
- A fórmula da célula B4 é: =B32-B32*B.
É hora então de avaliarmos outras hipóteses possíveis de impostos. Para isso precisamos de 2 colunas, uma com os possíveis impostos e outra com os valores correspondentes de salários líquidos.
- Selecione o intervalo de célula B36:C46 para estas colunas.
- Clique no menu Dados , Tabela...
Aparece o quadro Tabela para você preencher. Já dissemos que a primeira coluna selecionada será usada para a digitação dos vários impostos possíveis. Em que célula de nossa planilha de exemplo já digitamos um valor específico de imposto? Claro, na célula B.
- Passe esta informação ao Excel. Indique a célula B33 como a Célula de entrada de coluna.
Por enquanto ainda não faremos referência à Célula de entrada da linha. Você entenderá seu uso nas próximas páginas.
- Pronto, está tudo feito. Clique em OK para ver o resultado.
Na planilha ao lado o salário líquido é de 1900 quando o Imposto é de 5%. Qual será o líquido se o imposto for de 7%? E ser for de 10%? O Excel permite a criação de uma tabela com todas estas hipóteses. De forma simples e rápida!
5 6
Trabalhando com dois conjuntos de hipóteses
Vamos aproveitar a mesma planilha da página anterior e adaptá-la para duas hipóteses. Para isso, vamos inserir uma nova linha com Outros impostos , conforme mostra a figura ao lado..
- Para incluir Outros impostos no cálculo, digite a seguinte fórmula na célula B35 =B32-B32B33-B32B34.
Como você vê, esta fórmula desconta do salário líquido a parcela do Imposto de renda ( B32*B33 ) e a parcela dos Outros impostos ( B32*B34 ).
Na página anterior você selecionou 2 colunas para a tabela de hipóteses: uma coluna para as hipóteses (o imposto ) e outra coluna para os resultados. Agora sua seleção será maior, deve incluir dois tipos de hipóteses – o Imposto de renda e os Outros impostos.
- Selecione a área da tabela de hipóteses (B37:G47).
- Clique no menu Dados , Tabelas...
Veja a área selecionada na figura ao lado. A primeira coluna continua sendo o local onde aparecerão os vários Impostos de renda possíveis. Já na primeira linha estaremos informando as hipóteses para os Outros impostos.
- Então, na Célula de entrada da coluna , informe onde o valor do Imposto de renda aparece na fórmula original: B.
- Na Célula de entrada da linha informe onde aparece o valor dos Outros impostos na fórmula original: B.
4
5
Na página anterior você viu como trabalhar com uma única hipótese (a variação do imposto ). O Excel também permite a variação de duas hipóteses ao mesmo tempo – e esta é uma situação muito comum.
- Clique em OK.
Não se preocupe, sua tabela deva estar cheia de 0’s. É que ainda não informamos o que será usado nos cálculos.
Para calcular o salário líquido nesta tabela, repita na célula B37 (o cruzamento entre a primeira linha e a primeira coluna) a fórmula que faz este cálculo na planilha original (que está em B35 ). Claro, você não precisa montar a fórmula de novo, é só indicar em que célula está esta fórmula.
- Clique na célula B37 e digite a fórmula =B.
- Para informar as várias hipóteses para o Imposto de renda , preencha as células B38 a B47 com valores iniciando em 1% e terminando em 10% , com incrementos de 1% (confira na figura abaixo).
- Para informar as várias hipóteses para os Outros impostos , preencha as células C37 a G37 com valores iniciando em 20% e terminando em 40% , com incrementos de 5% (confira na figura abaixo).
A tabela é preenchida automaticamente enquanto você digita as informações necessárias. Assim fica muito fácil analisar o quanto o Imposto de renda e os Outros impostos impactam no Salário líquido , concorda?
O Atingir meta encontra uma solução, ele informa que o Salário Bruto deve ser de R$ 2.142, para que o Salário Líquido seja de R$ 1.500,.
- Para aceitar o valor proposto, clique em OK. Para voltar ao valor original clique em Cancelar.
Comentário: O Atingir meta varia uma célula de cada vez. Se você quiser variar mais de uma célula use o Solver , recurso que será explicado mais à frente nesta planilha.
Atingindo metas em gráficos
Para preparar um gráfico, precisamos de uma planilha.
- Faça uma planilha igual a esta ao lado.
- A coluna E3 (Total) é calculada ( Quantidade * Valor Unit ). Não digite estes valores, faça a fórmula correspondente, senão o Atingir meta não funciona.
Vamos fazer um gráfico que mostre os vendedores , o total de vendas e a meta a ser atingida.
- Para isso, selecione os intervalos A2:A8 , pressione o Ctrl e selecione os intervalos de E2:F.
- Clique no botão Assistente de gráfico na barra de ferramentas do Excel
- Clique na guia Tipos personalizados e logo escolha o gráfico Linhas-Colunas.
Queremos um gráfico simples e padrão, não precisamos passar por cada um dos passos que o Assistente de gráfico oferece.
- Então, para concluir agora o gráfico, clique no botão Concluir.
Pronto! O gráfico aparece em sua tela. Agora que você está vendo o gráfico pronto (veja a ilustração na próxima página), fica mais fácil entender porque fizemos uma coluna inteira só para as metas. Elas são representadas por uma linha no gráfico, que facilita quem atingiu as metas ou não.
Você também pode aproveitar o recurso Atingir meta a partir de gráficos. É muito mais visual, facilita o entendimento!
5
6
Cenários
Para ver como o Excel trabalha com cenários, nada melhor do que uma planilha de aplicações financeiras – como esta que você vê a seguir.
- Digite a seguinte planilha e faça os devidos cálculos.
Veja que a coluna C (Aplicado) e a coluna F (Retorno) são colunas calculadas. Não digite estes valores, senão o recurso de cenários não funciona, você deve calcular estes valores. Pergunte ao professor como fazer estes cálculos.
- Clique no menu Ferramentas , Cenários...
Aparece o quadro com o Gerenciador de cenários. Como não existe ainda qualquer cenário gravado, o quadro aparece vazio. É hora de adicionar um cenário.
- Clique no botão Adicionar para adicionar um novo cenário.
- Todo cenário tem um nome. No primeiro campo, coloque um nome para este cenário. Neste exemplo estamos chamando nosso cenário de aplicação.
- Um cenário serve para guardar valores diferentes para determinadas células. São as Células variáveis. Neste nosso exemplo estaremos variando os valores das células B2 a B.
Em uma planilha muitas vezes você precisa mudar valores de algumas células e ver os resultados correspondentes. Cada mudança traz uma resposta diferente. O Excel pode controlar estas mudanças para você, guardando cada alteração em um cenário. Assim você pode exibir qualquer cenário a qualquer instante – sem precisar digitar tudo de novo – e ainda ter de volta sua planilha original.
3
4
5
6
- Em Comentários digite uma descrição que melhor descreva seu cenário.
- Clique em OK.
Aparece uma quadro com os valores já digitados originalmente nas células. Não há o que fazer aqui, até porque este primeiro cenário será um retrato de sua planilha original, sem qualquer tipo de alteração.
É hora então de adicionarmos outros cenários. Agora, por exemplo, vamos digitar outros valores para as aplicações.
- Clique no botão Adicionar.
Aparece novamente o quadro de cenários para você informar como será o novo cenário. Vamos como exemplo digitar um valor maior para a aplicação em CDB.
- Por isso chame o novo cenário de CDB.
- As Células variáveis continuam as mesmas, mantenha o intervalo como B2:B.
- Digite um comentário para este novo cenário.
- Clique em OK.
- Novamente aparece o quadro Valores de cenário. Você precisa agora digitar novos valores para cada célula variável neste novo cenário.
Lembrete: Chamamos este cenário de CDB porque a maior porcentagem agora seria aplicada em CDB (célula B3 ). Na figura acima definimos 0,40 para B3 por este motivo.
Atenção: Neste exemplo específico estamos distribuindo as porcentagens de um capital que será aplicado. Assim, a soma das porcentagens indicadas no quadro acima tem quer ser 100% (ou 1 ). O Excel não controla isso, este cuidado é você que tem que ter.
8
9
10
11
