As tabelas dinâmicas são ferramentas muito poderosas do Excel.
Esse recurso permite a criação de relatórios resumidos, possibilitando análise e visualização de dados filtrados, agrupados ou calculados de forma rápida e eficiente.
Por serem fáceis de criar são muito flexíveis. Com um simples arrastar do mouse, campos podem ser facilmente excluídos ou inseridos, reorganizando os dados da forma como for mais conveniente.
Além da visualização dos dados em uma tabela, você também tem o recurso de inserir gráficos dinâmicos, possibilitando uma visão geral das informações por meio de filtros e, consequentemente, uma forma de analisá-las por diferentes perspectivas.
Base de dados
Para explicar como criar a tabela dinâmica, vamos considerar a seguinte planilha de Vendas:
Inserir tabela dinâmica no Excel
Para criar uma tabela dinâmica, siga os passos descritos a seguir.
- Clique em qualquer célula da planilha.
- Na guia Inserir, selecione Tabela Dinâmica e, no menu apresentado, clique em Da Tabela/Intervalo.
- Na janela que irá aparecer, você pode definir a área da planilha que deve ser considerada para a criação da tabela dinâmica e o local onde a tabela será criada.
Pelo fato do processo de criação da tabela dinâmica ter sido iniciado com uma célula da planilha selecionada, o Excel assume que o intervalo considerado para a o relatório já está indicado.
Caso esse intervalo não seja o desejado, um novo intervalo deve ser informado.
Você também pode selecionar se a tabela dinâmica será criada em uma nova planilha na pasta ou na planilha corrente.
Para melhor visualização dos dados, uma nova planilha é a melhor opção.
Nesse caso, como as informações na janela são as corretas, deixe tudo como está e pressione a tecla OK.
Uma nova planilha é criada na sua pasta de trabalho e com duas novas guias: Análise de Tabela Dinâmica e Design.
Campos da tabela dinâmica
No lado direito da planilha é mostrado um painel com as seguintes seções:
- Campos da Tabela Dinâmica: mostra todos os campos que fazem parte da região selecionada. Essas são as informações que serão selecionadas para a Tabela Dinâmica.
- Filtros: campos que serão considerados como os filtros principais da tabela dinâmica.
- Colunas: campos que atuarão como rótulos das colunas. Esses campos serão utilizados sempre que for necessário o cruzamento de informações.
- Linhas: campos que serão agrupados nas linhas.
- Valores: campos que terão seus valores somados, contados, resumidos e outros tipos de cálculos.
Criar tabela dinâmica
Vamos criar uma primeira tabela dinâmica com a data como filtro principal, para mostrar todas as vendas, agrupadas por Departamento.
Execute os passos a seguir para criar esse relatório.
- Clique sobre o campo Data Venda e, com ou mouse clicado, leve até a seção
- Clique sobre o campo Depto. e, com o mouse clicado, leve até a seção Linhas.
- Clique sobre o campo Produto e, com o mouse clicado, leve até a seção Linhas colocando-o após o campo Depto.
- Clique sobre o campo Valor e, com o mouse clicado, leve até a seção Valores.
A sua tabela dinâmica deve estar da seguinte forma:
Veja que você pode exibir os dados de acordo com a data, ou datas, escolhida no filtro principal.
Basta clicar na setinha ao lado do campo Data Venda e selecionar o filtro desejado.
Da mesma forma você pode filtrar os valores dos Rótulos de Linha.
Ao lado dos nomes dos Departamentos, existe um sinal de “-“.
Se você clicar sobre ele, os produtos serão escondidos e você terá somente o resumo por Departamento.
Para mostrar os itens novamente, basta clicar sobre o sinal “+” que eles voltarão à tela.
A tabela dinâmica está resumida por soma de Valor.
Você pode resumir por alguma outra operação, bastando clicar sobre a setinha que se encontra ao lado do campo Soma de Valor na seção Valores.
No menu apresentado, selecione a opção Configurações do Campo de Valor…
Nessa janela você pode escolher o cálculo que deve ser usado para resumir o campo selecionado e, também, formatar o campo.
Entrando na opção Formato do Número, escolha Moeda e pressione OK.
Pronto! A sua tabela dinâmica está formatada.
Experimente utilizar os filtros para Data de Venda e Rótulos de Linha.
Modificar identificadores
Você pode modificar o conteúdo das células que identificam as colunas e os filtros (Data Venda, Rótulos de Linha e Soma de Valor) para textos mais explicativos ou mais adequados às colunas.
Por exemplo, mude a célula A1 para Data de Venda, a célula A3 para Departamentos/Produtos e a célula B3 para Total de Vendas.
O cuidado que deve ser tomado é que esses nomes para as colunas da tabela dinâmica não devem ser os nomes dos campos da planilha.
Nos guias
Se você clicar em qualquer célula da tabela dinâmica, duas guias serão mostradas:
- Análise de Tabela Dinâmica: com opções para configurar campos, agrupar valores, inserir filtros, atualizar os dados, inserir gráficos dinâmicos.
- Design: com opções para gerenciar totais e subtotais e layout do relatório.
Atualizar tabela dinâmica
Se você fizer qualquer modificação na planilha origem da tabela dinâmica, como modificar algum conteúdo de célula ou inserir novas linhas no final da planilha, é necessário atualizar o relatório.
A atualização não é automática.
Caso a modificação seja no conteúdo de qualquer célula já existente na planilha ou novas linhas tenham sido inseridas entre as linhas já existentes, basta clicar com o botão direito do mouse sobre qualquer lugar na tabela dinâmica e escolher a opção Atualizar.
Caso tenham sido inseridas novas linhas no final da planilha, será necessário atualizar a fonte de dados, ou seja, a nova região da planilha que deve ser considerada para a construção da tabela dinâmica.
Para isso, com qualquer célula do relatório selecionada, clique na guia Análise de Tabela Dinâmica e selecione a ferramenta Alterar Fonte de Dados.
O Excel mostra a tela inicial para inserir tabela dinâmica para que você atualize o Intervalo correto.
Observe que inserimos uma nova linha na planilha para a vendedora Rosana.
Selecione o intervalo correto (VENDAS!$A$3:$E$27) e verifique que a sua tabela dinâmica foi atualizada.
GRÁFICOS DINÂMICOS
Vamos inserir um gráfico dinâmico nessa tabela para mostrar as vendas nos Departamentos.
Para isso, esconda a visualização dos Produtos, clicando no sinal de “-“ ao lado dos nomes dos Departamentos e, na guia Análise de Tabela Dinâmica selecione Gráfico Dinâmico.
Lembre-se que, para qualquer operação na tabela dinâmica, é necessário que, pelo menos, uma das suas células esteja selecionada.
Escolha o gráfico de Pizza.
O Gráfico Dinâmico apresenta todos os filtros que aparecem na tabela dinâmica.
A formatação dos diversos componentes desse tipo de gráfico se faz exatamente da mesma forma que nos gráficos que utilizamos nas planilhas.
Nova tabela dinâmica
Vamos agora criar uma tabela dinâmica cruzando os dados de vendas com as datas.
Para isso escolha os campos de acordo com a figura a seguir:
Formate o valor para aparecer com separador de milhar.
Recolha os produtos e deixe visíveis somente os departamentos.
Agora vamos inserir um gráfico dinâmico de colunas para essa situação.
Se você clicar no sinal de “+” que se encontra no canto inferior direito do gráfico, a tabela dinâmica passa a mostrar os produtos que estão filtrados no momento.
Nova tabela dinâmica
Vamos agora criar uma tabela dinâmica com filtro principal para Produto e que contenha os nomes dos vendedores e suas vendas.
Nessa tabela vamos mostrar as vendas como valores e também como percentuais sobre o valor total de vendas.
Vamos inserir o campo Valor mais uma vez na seção
Clique com o botão direito do mouse sobre qualquer célula desse novo campo a escolha:
Você pode modificar os nomes das colunas para melhor identificação dos conteúdos.
CAMPOS CALCULADOS
Um campo calculado é um campo que vai fazer parte da tabela dinâmica, mas não faz parte da planilha origem.
Podemos criar campos com qualquer tipo de cálculo, seja um cálculo básico ou envolvendo funções do Excel.
Nessa tabela dinâmica vamos criar um campo de nome Comissão que vai calcular um percentual de 10% sobre a venda do vendedor, se a venda for maior ou igual a R$ 3.000,00.
Caso não seja, o valor que deve aparecer na coluna é zero.
- Com qualquer célula da tabela dinâmica selecionada, clique na guia Análise de Tabela Dinâmica, selecione Campos, Itens e Conjuntos e, a seguir, Campo Calculado…
- Para o nome informe: Comissão.
- A fórmula será: =SE(Valor>=3000; Valor*10%;0)
- Clique em Adicionar e OK.
Veja que o campo Comissão agora faz parte dos campos da tabela dinâmica, e estará disponível para outros relatórios que sejam criados com a planilha fonte.
SEGMENTAÇÃO DE DADOS
A segmentação de dados é um recurso que permite filtrar os dados da tabela dinâmica de forma rápida e fácil, sem a necessidade da colocação de filtros na própria estrutura da tabela.
Vamos criar a tabela dinâmica:
Agora, pelo recurso de segmentação de dados, vamos criar um filtro para Departamento.
- Clique em qualquer célula da tabela dinâmica e, na guia Análise de Tabela Dinâmica, selecione a opção Inserir Segmentação de Dados.
- Na janela apresentada, selecione e pressione OK.
- Veja que o campo não faz parte da tabela dinâmica, mas pode ser filtrado pela segmentação de dados.
LINHA DO TEMPO
Da mesma forma que criamos um filtro pela segmentação de dados, podemos criar filtros considerando diferentes períodos.
Nessa mesma tabela dinâmica vamos criar uma Linha do Tempo para ser o filtro de Data Venda.
- Clique em qualquer célula da tabela dinâmica e, na guia Análise de Tabela Dinâmica, selecione a opção Inserir Linha do Tempo.
- Na janela apresentada, selecione Data Venda e pressione
- Veja que o campo Data Venda não faz parte da tabela dinâmica, mas pode ser filtrado pela segmentação de dados. Esse filtro pode ser modificado para ser aplicado por Anos, Trimestres, Meses ou Dias.
Agora que você já viu como é fácil criar e modificar uma tabela dinâmica, pode utilizar esse recurso, com toda a sua flexibilidade, para resumir os dados das suas planilhas visando análises mais apuradas sob diferentes perspectivas.
Compartilhe ou Comente
Se você curtiu esse artigo aonde mostramos como criar uma tabela dinâmica no Excel, compartilhe com as suas redes sociais e não se esqueça de deixar um comentário aqui embaixo caso você tenha ficado com alguma dúvida.