Aula 2 - Tabelas Dinâmicas

Prévia do material em texto

Página | 1 
Como filtrar, mostrar e ocultar dados de Tabelas Dinâmicas 
 
Se necessário, clique na Planilha: Base 
1. Clique em qualquer célula da tabela de dados (intervalo A1:H19738) – Isso mesmo, são mais de 19 mil 
linhas!!!. 
2. Na guia Inserir, no grupo Tabelas, clique em Tabela Dinâmica. 
 
 
3. O Excel presume que você quer fazer uma Tabela Dinâmica com base na tabela de dados ativa. Então, 
vai aparecer uma caixa de diálogo semelhante a esta: 
 
 
4. Mantenha as sugestões dadas pelo Excel e clique em OK. Desta vez, os Campos da Tabela Dinâmica 
não estarão marcados. 
5. No painel Campos da Tabela Dinâmica, arraste o cabeçalho do campo Ano para a área Colunas. 
6. Arraste o cabeçalho do campo Região para a área Linhas. 
7. Arraste o cabeçalho do campo Mês para a área Linhas. Abaixo de Região. 
8. Finalmente, arraste o cabeçalho do campo Vendas para a área Valores. 
 
Página | 2 
9. No painel Campos da Tabela Dinâmica, clique na seta do Mês. 
 
 
10. Desmarque (Selecionar Tudo) e marque somente os meses de Janeiro, Fevereiro e Março. Todos os 
dados relativos aos outros meses serão ocultados (e também não fazem parte do Total Geral). 
 
 
11. Troque as posições entre Região e Mês na área das Linhas. 
 
 
12. Clique em ocultar detalhes, próximo da palavra Janeiro. Neste caso, os detalhes de Janeiro serão 
ocultados, mas ainda fazem parte do Total Geral. 
 
 
13. No painel Campos da Tabela Dinâmica, arraste o cabeçalho do campo Estado para a área Filtros. 
 
 
 
Página | 3 
14. Clique na seta da célula B1. 
 
15. Clique em Amazonas e clique em OK. Apenas os totais das vendas relativas ao Estado do Amazonas 
serão exibidos. 
16. Clique novamente na seta (agora com aparência de funil) da célula B1. Clique em Selecionar Vários 
Itens. Desmarque Amazonas e selecione os Estados de Alagoas, Bahia e Ceará. Clique em OK. O Excel 
atualizará os totais das vendas relativas a esses três Estados. 
 
 
17. Para remover os filtros relativos aos Estados, clique no cabeçalho Estado no campo Filtros e arraste-o 
para fora desse campo. Pode ser em direção à planilha ou de volta aos outros Campos da Tabela 
Dinâmica. 
 
OU: Você poderia desmarcar Estado nos Campos da Tabela Dinâmica. 
 
 
 
 
Página | 4 
Editando Tabelas Dinâmicas 
 
Continue na mesma Planilha da Tabela Dinâmica. Certifique-se de eliminar os Filtros dos 
Estados. 
 
 
1. Na planilha onde está a Tabela Dinâmica, clique em qualquer célula da Tabela Dinâmica. 
2. Na guia de ferramenta Analisar, clique no botão Tabela Dinâmica e, no campo Nome da Tabela 
Dinâmica, digite Soma de Vendas e pressione Enter para renomear a Tabela Dinâmica. 
3. Na guia de ferramenta Design, no grupo Layout, clique em Subtotais e em Não Mostrar Subtotais. O 
Excel removerá as linhas de subtotais da Tabela Dinâmica. 
4. Na guia de ferramenta Design, no grupo Layout, clique em Totais Gerais e em Habilitado Somente 
para Colunas. O Excel removerá as células que calculam o total geral de cada linha. 
5. Clique com o botão direito do mouse em qualquer célula numérica (exceto os anos) da Tabela 
Dinâmica, aponte para Resumir Valores por e clique em Contagem para mudar a operação de resumo 
do campo Valor. O Excel exibirá a contagem das vendas (e não seus totais). 
 
6. Clique com o botão direito do mouse em qualquer célula numérica (exceto os anos) da Tabela 
Dinâmica e clique em Configurações do Campo de Valor 
 
7. Se necessário, clique na guia Resumir Valores por e na lista abaixo, clique em Soma. O Excel voltará a 
exibir o total de vendas. E não mais a sua contagem. 
 
 
Página | 5 
8. Clique com o botão direito do mouse em qualquer célula numérica da Tabela Dinâmica e clique 
novamente em Configurações do Campo de Valor. 
9. Clique na guia Mostrar Valores como para exibir a página Mostrar valores como. 
10. Na lista Mostrar valores como, clique em % do Total de Colunas. 
 
11. Clique em OK para alterar como o Excel calcula os valores da Tabela Dinâmica. 
12. Na guia Design, no grupo Layout, clique em Subtotais e em Mostrar Todos os Subtotais no Final do 
Grupo para exibir subtotais na pasta de trabalho. 
13. Clique no campo da Soma de Vendas. 
 
14. Clique em Configurações do Campo de Valor. 
 
15. Clique na guia Mostrar Valores como para exibir a página Mostrar valores como. Na lista Mostrar 
valores como, clique em Sem Cálculo. 
 
16. Clique na planilha Resumo. 
 
17. Na célula C4, digite =, mas não pressione Enter. 
18. Clique na guia da Tabela Dinâmica que você estava trabalhando para exibir essa planilha. 
19. Clique na célula que exibe o Total Geral do ano de 2016 e pressione Enter. Quando o fizer, o Excel 
criará a fórmula =INFODADOSTABELADINÂMICA("Vendas";Planilha1!$A$3;"Ano";2016) e irá inserir o 
valor 37.471.136,93 na célula C4. 
Agora vamos aprender a formatar uma Tabela Dinâmica 
 
Página | 6 
Formatando Tabelas Dinâmicas 
 
Planilha: Vendas 
 
1. Clique em qualquer célula da tabela de dados. Na guia Inserir, no grupo Tabelas, clique em Tabelas 
Dinâmicas Recomendadas. Escolha o quarto formato da lista à esquerda “Soma de Valor por Mês”. 
Clique em OK. 
2. Na planilha gerada, selecione o intervalo B5:B16 da Tabela Dinâmica, clique com o botão direito do 
mouse e selecione a opção Formato de Número para abrir a caixa de diálogo Formatar Células. 
 
3. Na lista Categoria, clique em Número, no campo Casas decimais, se necessário, digite 2. 
4. Marque a caixa de seleção Usar separador de 1000 (.). 
 
5. Clique em OK para reformatar os dados de sua Tabela Dinâmica. 
6. Com a tabela dinâmica ainda selecionada, na guia de ferramenta Design, no grupo Opções de Estilo de 
Tabela Dinâmica, marque a caixa de seleção Linhas em Tiras. 
 
 
Página | 7 
7. Na guia de ferramenta Design, no grupo Estilos de Tabela Dinâmica, clique no botão Mais. Escolha um 
estilo para o Excel aplicar à sua Tabela Dinâmica. Observe que, dependendo da sua escolha, a opção de 
Linhas em Tiras será cancelada. 
 
 
8. No canto inferior direito da galeria Estilo de Tabela Dinâmica, clique novamente no botão Mais para 
exibir a galeria de estilos. 
9. Clique em Novo Estilo de Tabela Dinâmica para abrir a caixa de diálogo de mesmo nome (lá em baixo!).
 
 
10. No campo Nome, digite Estilo Personalizado 1. 
11. Na lista Elemento de Tabela, clique em Linha de Cabeçalho e depois clique em Formatar para abrir a 
caixa de diálogo Formatar Células. 
 
 
 
Página | 8 
12. Na página Fonte, na lista Cor, escolha alguma tonalidade de Azul Escuro. Na lista Estilo da fonte, 
selecione Negrito. 
 
 
13. Na página Borda, na área Predefinições, clique em Contorno. 
 
 
14. Na página Preenchimento, na área Cor do Plano de Fundo, clique em alguma cor clara. 
15. Clique em OK para fechar a caixa de diálogo Formatar Células. A mudança de estilo aparecerá no painel 
Visualização da caixa de diálogo Novo Estilo de Tabela Dinâmica. 
 
 
Página | 9 
16. Na lista Elemento de Tabela, clique em Listra da Segunda Linha e então clique em Formatar para abrir 
a caixa de diálogo Formatar Células. 
 
17. Na página Preenchimento, na parte central da área Cor do Plano de Fundo, clique em alguma cor clara 
(que você acha que combina com sua escolha para o cabeçalho). 
18. Clique em OK duas vezes para fechar a caixa de diálogo Formatar Células. Seu formato aparecerá no 
grupo Estilos de Tabela Dinâmica. 
19. Se necessário, na guia de ferramenta Design, clique no seu novo estilo para reformatar a Tabela 
Dinâmica. 
20. Na guia de ferramenta Design, no grupo Opções de Estilo de Tabela Dinâmica, desmarque a caixa de 
seleção Linhas em Tiras. O Excel removerá as tiras de sua Tabela Dinâmica e da visualização do estilo 
personalizado. 
21. Selecione o intervalo de células B5:B15. 
22. No grupo Estilo da guia Página Inicial, clique em Formatação Condicional, aponte para Barras deDados e escolha alguma cor para exibição. As Barras de Dados destacam as vendas de cada mês. 
 
23. Acrescente alguns Campos nas Áreas de Colunas e Linhas. Por exemplo: 
 
 
 
Página | 10 
24. Mude os campos da Tabela Dinâmica, de forma a obter o seguinte aspecto: 
 
25. Se necessário, clique na tabela dinâmica, para que a guia Ferramentas de Tabela Dinâmica apareça no 
menu. 
 
 
26. Clique na Guia Design. No grupo Layout clique no comando Subtotais e, em seguida, em Não Mostrar 
Subtotais. Sua tabela deverá mudar para o seguinte aspecto: 
 
 
 
 
Página | 11 
Interpretando Tabelas Dinâmicas 
 
Clique na planilha Cervejas, se necessário, clique em qualquer célula da tabela de dados. 
Neste exercício, vou fazer algumas perguntas ao longo do texto! 
1. Crie uma tabela dinâmica (Inserir  Tabela Dinâmica), colocando País de Origem em linhas, Ano como 
coluna e Valor (US$) em Valores. (Já dá para responder as perguntas “a” e “b” em azul, logo abaixo). 
 
2. Se você quiser, coloque os dados da tabela no formato moeda. Intervalo B5:D15. 
a) Qual o valor total importado da Bélgica nos dois anos estudados? 
b) Qual o total de importação no ano de 2012? 
(pode responder no arquivo do Excel, planilha cerveja, células da coluna F) 
As respostas estão na última página deste arquivo pdf. 
3. Clique na seta de filtro do País de origem. 
 
Aparecerá a seguinte tela: 
 
 
 
Página | 12 
4. Aplique o filtro selecionando somente os países europeus para responder à pergunta: 
c) Qual o total de importação dos países europeus em 2013? (Se não souber quais são, pergunte para o Google) 
 
5. Mude o filtro para os países da América do Sul, e responda: 
d) Somando os dois anos, qual o total de importação dos países da América do Sul? 
 
6. Remova todos os filtros aplicados anteriormente e clique no campo da Soma de Valores: 
 
 
7. Clique em Configurações do Campo de Valor: 
 
 
8. Clique em Mostrar Valores como e em seguida em % do Total de Colunas 
 
Responda a pergunta “e”: 
e) No ano de 2012, qual a porcentagem de importações feitas da Holanda? 
 
9. Repita os procedimentos 7 e 8 para mostrar a % do Total Geral. Responda à pergunta “f”: 
f) As importações feitas da Holanda em 2012, correspondem a qual percentual em relação ao total do 
período analisado? 
 
 
 
Página | 13 
10. Repita os procedimentos 7 e 8 para mostrar a % Diferença de, clique em Ano e em 2012. 
 
Responda às perguntas “g” e “h”. 
g) Qual o país que apresentou o maior aumento percentual de importações? Qual foi esse aumento? 
h) Qual foi o país que apresentou a maior queda percentual de importações? Qual foi essa queda? 
 
11. Repita os procedimentos 7 e 8 para mostrar a % Diferença de, clique em País de origem e em Uruguai. 
A linha do Uruguai ficará em branco e o Excel exibirá somente os valores percentuais dos outros países 
em relação ao Uruguai... Responda às perguntas “i” e “j” 
i) Em 2012, importamos 13,50% mais cerveja da Argentina que do Uruguai. Em 2013 qual foi a situação? 
j) Ainda em relação ao Uruguai, qual a comparação percentual com as importações feitas da Alemanha em 2012? 
 
Respostas: 
a) Nos dois anos analisados importamos US$ 6.959.732,00 da Bélgica. 
b) O total de importação de cervejas em 2012 foi US$ 43.549.838,00 
c) Importamos US$ 23.335.275,00 dos países europeus em 2013. 
d) Nos dois anos analisados importamos US$ 12.414.297,00 desses dois países. 
e) 47,80% das importações de 2012 foram feitas da Holanda. 
f) Considerando os dois anos analisados, as importações feitas da Holanda em 2012, representam 27,80% 
do total. 
g) Foi a República Tcheca, com um aumento de 36,72% nas importações. 
h) Foi o México, com uma queda de 86,86%. 
i) Em 2013, as importações da Argentina foram 22,89% menores que do Uruguai. 
j) As importações da Alemanha foram 42,37% maior que a do Uruguai. 
 
Isto é só uma pequena amostra do potencial da ferramenta Tabela Dinâmica. 
Acho que você percebeu a quantidade de opções que existem na montagem de uma Tabela Dinâmica. 
Explore outros recursos e outros tipos de visualizações. 
Boa sorte. Espero que seja útil na sua vida profissional!!!