Embed Size (px)
Citation preview
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Excel 2010 – Uso inteligente da TabelaDinâmica
Incrementando
Seus conhecimentos
Aperfeiçoando
A sua prática
Página 1/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Algumas palavras do autor: Este e-book trazuma série de dicas para enriquecer o seu conhecimento com relação aouso da Tabela Dinâmica do Excel 2010. Nosso objetivo é proverensinamentos para profissionais de diversas áreas (financeira,contabilidade, economia, administração), micro e pequenas empresasonde se busca uma melhor análise e gerência de informações. A seguirvocê poderá acompanhar todo o conteúdo desta apostila.
Aprecie o conteúdo deste e-book.
. Montando uma Tabela Dinâmica
. Transformando a tabela em valores percentuais sobre o total
. Adicionando um campo calculado
. Agrupando datas
. Tabela Dinâmica utilizando várias planilhas - I
. Tabela Dinâmica utilizando várias planilhas - II
. Preencher células em branco para montar uma Tabela Dinâmica
. Gráfico utilizando uma Tabela Dinâmica
2
Um bom estudo a todos e espero, sinceramente, que este e-book possaajudá-los a enriquecer seus conhecimentos e proporcionar uma melhorposição no mercado de trabalho.
Página 2/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática ÍNDICE
. Tabelas Dinâmicas.............................................................................. 005
. Montando uma Tabela Dinâmica ..........................................................005
. Transformando a tabela em valores percentuais sobre o total..................009
. Adicionando um campo calculado........................................................ 010
. Agrupando datas.............................................................................. 012
. Tabela Dinâmica utilizando várias planilhas - I.......................................014
. Tabela Dinâmica utilizando várias planilhas - II......................................021
. Preencher células em branco para montar uma Tabela Dinâmica
............. 029
. Gráfico utilizando uma Tabela Dinâmica ................................................033
Página 3/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Breve comentário Muitos dos exemplosabaixo, foram compilados ao longo de vários anos de experiência.Várias foram as pessoas que chegaram até a mim com dúvidas das maisdiversas possíveis e por isso resolvi criar esta apostila com um assunto
3
bem interessante e de uso diário.
Com estes exemplos, com certeza você irá angariar conhecimentos queas vezes, para uma determinada tarefa, outras pessoas demorariamhoras ou até mesmo dias e você os resolverá em questão de minutos.
Aproveite mais este e-book e bons estudos.
Página 4/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática TABELAS DINÂMICAS
Um relatório de Tabela Dinâmica consiste em uma forma interativaonde podemos resumir grandes quantidades de dados.
Utilizamos este tipo de tabela para análise sintética de dados.
Os relatórios de Tabela Dinâmica servem principalmente para:
Consultar grandes volumes de dados de várias formas diferentes.
Subtotalizar e agregar dados numéricos, resumindo dados porcategorias e subcategorias.
4
Vamos nos utilizar do seguinte exemplo
Vamos selecionar a área desejada. Neste caso será de A1 a D8.
Após esta seleção, clique na aba Inserir, Tabela Dinâmica , TabelaDinâmica .
Página 5/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Ao clicar em Tabela Dinâmica , já temospreenchido o intervalo selecionado na caixa de diálogo.
5
Clique em Local: e clique em qualquer célula, local onde será montadaa tabela.
Ex. F1
Uma lista de campos será apresentada.
Estes campos são os cabeçalhos de cada
coluna selecionada.
Página 6/34
6
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique na caixa ao lado de cada campopara selecioná-lo.
Arraste o campo entre as opções:
-
Filtro de Relatório
-
Rótulos de coluna
-
Rótulos de linha
-
Valores
7
Clique com o botão da esquerda do mouse, para fazer alteração doscampos.
Página 7/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Altere o resultado conforme a suanecessidade.
Exemplo: Formatar a apresentação dos números
Página 8/34
8
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Transformando a tabela em valorespercentuais sobre o total.
Clique na célula F1 e com o botão direito do mouse escolha a opçãoResumir Dados por, Mais opções, Mostrar valores como, % do total.
Vamos voltar à tabela à sua forma normal.
Clique na célula F6 e com o botão direito do mouse escolha a opçãoResumir Dados por, Mais opções, Mostrar valores como, Normal.
Página 9/34
9
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Adicionando um campo calculado.
Clique na Tabela Dinâmica para acessar o menu Ferramentas de TabelaDinâmica (parte superior).
Sobre o valor vamos acrescentar o ICMS no valor de 25% e acrescentartambém um valor líquido.
Clique na Tabela Dinâmica , no Menu, Ferramenta de Tabela Dinâmica(parte superior da tela), Opções, Fórmulas e insira um campo
10
calculado.
Vamos adicionar o campo ICMS e o campo Total Líquido
Página 10/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Veja o resultado.
Página 11/34
11
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Agrupando datas.
Deixe a Tabela Dinâmica conforme a figura abaixo.
Clique na célula F2. No Menu, Ferramenta de Tabela Dinâmica ,Opções, Agrupar Campo.
12
Em agrupamento selecione (dias, meses, ano – faça isso segurando atecla Control ) e digite as datas.
Página 12/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Repare em todos os campos que foramcriados na Tabela Dinâmica .
Escolha apenas Valor e Meses.
Página 13/34
13
14
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática TABELA DINÂMICA UTILIZANDOVÁRIAS PLANILHAS – Modelo I OBJETIVO
Montar um relatório de Tabela Dinâmica utilizando vários intervalos.
OBS: Se você tiver várias planilhas, junte todas em uma única planilha.
15
EXEMPLO
Lojas.xlsx
Abra uma nova planilha.
Clique no Menu, Dados, Obter Dados Externos, De Outras Fontes, DoMicrosoft Query.
Página 14/34
16
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Escolha a opção Excel Files*
Vamos escolher a planilha – Lojas.xlsx
Se as folhas de dados não aparecerem, clique em opções e marque os itensconforme as próximas figuras.
Página 15/34
17
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Marque os itens conforme figura abaixo
Clique na seta para a direita e adicione os campos apenas de Loja1
Você irá obter este resultado.
18
Clique em avançar por três vezes até que encontre a tela abaixo.
Escolha a opção Exibir dados ou editar consulta no Microsoft Query eclique em concluir.
Página 16/34
19
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Você irá obter a seguinte tela.
Clique na caixa SQL.
Selecione todo o texto e dê um Control C para copiar para a área detransferência.
Ao final escreva UNION ALL
Dê um Control V (colar).
Página 17/34
20
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Vamos repetir o processo.
Ao final escreva UNION ALL
Dê um Control V (colar).
Note que temos agora três partes.
Vamos alterar para ficar conforme figura abaixo, ou seja, um grupo paracada loja.
Clique em Ok e na próxima tela clique em Ok novamente.
Página 18/34
21
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Vamos obter a seguinte tela
Feche a janela
Na próxima tela escolha relatório de Tabela Dinâmica
22
Página 19/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Monte a Tabela Dinâmica conforme a suanecessidade.
Página 20/34
23
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática TABELA DINÂMICA UTILIZANDOVÁRIAS PLANILHAS – Modelo II OBJETIVO
Montar o mesmo relatório do exemplo anterior utilizando outra forma.
No exemplo anterior, você teria que juntar todas as planilhas em umaúnica, neste exemplo, não vai ser preciso fazer isso. A utilização deste
24
exemplo, é apenas para um uso mais rápido quando você precisa de umainformação apenas para aquele momento.
No exemplo abaixo temos 3 planilhas diferentes, uma para cada mês.
25
EXEMPLO
Lojas1.xlsx Lojas2.xlsx Lojas3.xlsx
Nota: Observe que nas figuras acima, você tem 3 planilhas diferentes, mascujas colunas são as mesmas.
Abra uma nova planilha.
Vamos criar uma atalho do Assistente da Tabela Dinâmica .
Página 21/34
26
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique em Arquivo
Clique em Opções
Clique em
Escolha todos os comandos
Clique no item Assistente de Tabela Dinâmica e clique no botão Adicionare depois clique em Ok.
Note que em sua planilha irá aparecer o ícone que você acabou deadicionar.
Agora deixe aberto ao mesmo tempo, a planilha que você acabou de abrir, aLojas1.xlsx, a Lojas2.xlsx e a Lojas3.xlsx. Aqui estes nomes foram usadosapenas como exemplo, você poderá abrir as planilhas que desejar.
Nota: Vale lembrar que todas deverão ter as mesmas colunas para fazermoso agrupamento.
Clique no botão que acabamos de adicionar
27
Página 22/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Você irá obter a seguinte tela, conforme figuraabaixo.
Clique no item vários intervalos de consolidação.
Clique em avançar
Você irá obter a seguinte resultado.
28
Clique em Criarei os campos de página e depois em Avançar.
Página 23/34
29
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Na tela seguinte, clique no item para marcar ointervalo.
Selecione toda a coluna A e B da planilha Lojas1.xlsx e clique emAdicionar.
Repita o mesmo processo para as outras duas planilhas.
Ao final, após adicionar as três planilhas você irá obter o seguinteresultado.
Página 24/34
30
31
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Clique na primeira linha do primeiro intervalo.
Marque 1 para a quantidade de campos.
E em Campo um escreva Dados Loja 1
Faça o mesmo procedimento para as outras duas linhas.
Quando você finalizar a terceira linha, vai obter o seguinte resultado
Clique em Avançar.
Página 25/34
32
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Escolha a opção na planilha existente e cliqueem Concluir.
Você irá obter o seguinte resultado.
33
No item Página1 na célula B1 você poderá selecionar todas ou apenas umaplanilha.
No item ∑ Valores com o botão esquerdo do mouse, clique emConfigurações do Campo de Valor e escolha a opção Soma.
Página 26/34
34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Você irá obter o seguinte resultado.
Você sabia?
Ao clicar na Tabela Dinâmica , um menu na parte superior é mostrado emsua tela.
Clique em Opções.
No primeiro item do lado esquerdo, Clique em Tabela Dinâmica .
Ao abrir o menu, clique em opções e escolha a opção Mostrar Páginas doFiltro de Relatório.
Uma nova janela irá aparecer, clique em Ok.
Página 27/34
35
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Você irá obter o seguinte resultado.
Note que agora além da sua Tabela Dinâmica , você tem as outras 3 folhasde dados respectivas do conteúdo que você acabou de gerar.
Agora basta você aplicar a sua imaginação para criar uma formatação maisbonita para a sua apresentação.
Página 28/34
36
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática PREENCHER CÉLULAS EM BRANCOPARA MONTAR UMA TABELA DINÂMICA Monte uma tabela igual àfigura abaixo.
Vamos supor que você gerou uma Tabela Dinâmica . Retire as linhas detotal, subtotais e vazios.
Copie e cole todos os seus valores para outra tabela.
Selecione as colunas de A a D. Estas colunas serão as que queremospreencher com os valores que estiverem em branco.
Cuidado: Não selecione os campos de valor para evitar duplicidade nahora de executar este processo.
Página 29/34
37
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Após selecionar toda a planilha, pressioneF5.
Clique em Especial e depois em branco e tecle OK.
38
Página 30/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática Note que todas as células em brancoficaram com uma tonalidade de cor diferente. Note também que a célula
39
B3 recebeu o foco.
Digite =B2
Página 31/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática E pressione CTRL + ENTER.
Toda a tabela foi preenchida com os campos logo acima dos campos queestavam em branco.
40
Página 32/34
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática CRIANDO UM GRÁFICO COM ATABELA DINÂMICA OBJETIVO
Criar um gráfico à partir de uma Tabela Dinâmica .
41
EXEMPLO
Monte uma tabela igual à figura abaixo.
Selecione todos os seus dados.
Página 33/34
42
Excel – Tabela Dinâmica - Incrementando seus conhecimentos eAperfeiçoando a sua prática No menu selecione Inserir, Tabela Dinâmica ,clique em Gráfico Dinâmico e em seguida clique em Ok.
Na lista de campos, selecione todos os campos.
Arraste os campos para que fiquem igual à figura abaixo.
Pronto.
Para testar selecione ou exclua os campos em sua Tabela Dinâmica .
43
Página 34/34
44
