Quando fazemos uma planilha enorme no Excel, talvez seja interessante criar uma Tabela Dinâmica para resumir os dados mais importantes. A tabela dinâmica é um recurso poderoso que foi adicionado para calcular, resumir e analisar os dados que lhe permitem fazer comparações, padrões e tendências.
Vamos aprender como fazer uma tabela dinâmica nos relatórios do Excel, para consolidar várias planilhas em um relatório resumido. Muito embora, isso tenha sentido, para sua planilha, a consolidação de dados é uma maneira útil de combinar dados de diferentes origens em um relatório.
Como criar uma tabela dinâmica no Excel?
As tabelas dinâmicas funcionam de maneira um pouco diferente, dependendo da plataforma que você está usando para executar o Excel. Siga estas etapas fáceis para criar sua TD no Excel, para que possa resumir os dados de sua pasta de trabalho, rapidamente.
Existem duas maneiras:
- Criar tabela dinâmica.
- Criar tabelas dinâmicas recomendadas.
E qual é a diferença entre elas? As tabelas dinâmicas recomendadas são mais simples de serem criadas, porque o usuário só precisa escolher entre duas opções, inserir o intervalo de dados e o Excel cuida do restante, como exibimos na imagem a seguir:
Já a tabela dinâmica que criamos de forma personalizada abrange mais recursos. Por exemplo: você pode selecionar dados externos, dados de outras planilhas, inserir a tabela na mesma planilha, ou em uma nova planilha. Estes são alguns recursos que o usuário pode selecionar para criar uma tabela dinâmica.
Vamos criar nossa primeira tabela dinâmica
- Primeiro, selecione as células a partir das quais você deseja criar uma Tabela Dinâmica.
Nota: Seus dados não devem ter linhas ou colunas vazias. Deve haver apenas uma única linha de título.
- Selecione guia Inserir e, em seguida, clique em Tabela Dinâmica.
- Na opção Escolha, que é a parte dos dados que você deseja analisar, selecione: Selecionar uma tabela ou intervalo.
- Em Tabela / Intervalo, verifique o intervalo de células.
Em Escolha onde deseja que o relatório da Tabela Dinâmica seja posicionado, selecione Nova Planilha para posicionar a Tabela Dinâmica em uma nova planilha, ou escolha Planilha Existente e selecione o local em que deseja exibir a Tabela Dinâmica.
Feito isso, clique em OK.
Por exemplo: se você tiver um relatório de tabela dinâmica de despesas de cada escritório diferente, poderá usar uma consolidação de dados para acumular essas informações em um relatório de despesas corporativo.
Esse relatório pode conter os totais e as médias de vendas e os níveis de estoque atuais e os produtos mais vendidos em toda a empresa, como resultado.
Configurar campos da tabela dinâmica
Essa opção faz com que o usuário possa personalizar sua tabela dinâmica. É a escolha que se pode fazer para visualizar os dados a serem exibidos dentro da tabela criada. Por exemplo: se você deseja criar um relatório apenas para a descrição e os preços do produtos, sem a necessidade de ver outros detalhes, você pode marcar ou desmarcar as caixinhas de seleção na seção de campos da tabela.
- Ocultar ou Mostrar Linhas de Grade na Planilha de Excel
- Construindo Tabela Dinâmica nos Relatórios do Excel
- A Maneira Mais Fácil de Inserir Gráfico na Planilha de Excel
A imagem a seguir mostra como estão organizados estes campos.
Você pode arrastar os campos para as áreas abaixo, para Filtros, Colunas, Linhas e Valores. Estas seções indicam onde cada dado da tabela irá se acomodar. E mostra também, quais dados você quer que a tabela mostre.
Se você deseja modificar sua análise basta alterar estes dados nos campos de dados da tabela. Sempre que você clicar em sua tabela dinâmica o painel de tarefas surge no lado direito da planilha.
Como Atualizar a TD para que os dados fiquem atualizados?
Quando você faz alterações na planilha, seja adicionando ou excluindo dados, sua tabela dinâmica deve ser atualizada para que os dados sejam precisamente exibidos na forma atual. Siga as etapas para atualizar sua tabela dinâmica.
- Clique em qualquer parte do cabeçalho da tabela, com o botão direito do mouse.
- Quando surgir a lista suspensa, localize a opção Atualizar e clique nela.
- Aguarde até que o Excel conclua a atualização. às vezes isso pode demorar um pouco dependendo da quantidade de dados.
O suporte do MS Office tem um vídeo que ensina rapidamente como fazer isso.
Opções avançadas que talvez você queira ver
Configurando os dados de origem na tabela dinâmica
Cada intervalo de dados deve ser organizado em formato de tabulação cruzada, com nomes de linha e coluna correspondentes para os itens que você deseja agrupar. Não inclua nenhuma linha ou coluna de total dos dados de origem ao especificar os dados para o relatório. O exemplo a seguir mostra quatro intervalos em formato de tabulação cruzada.
Consolidar vários intervalos
Use o Assistente de Tabela Dinâmica e Gráfico Dinâmico para consolidar vários intervalos. No assistente, você pode escolher entre usar nenhum campo de página, um único campo de página ou vários campos de página.
Consolidar dados sem usar campos de página
Para combinar os dados de todos os intervalos e criar uma consolidação que não possua campos de página, faça o seguinte:
- Clique em uma célula em branco na pasta de trabalho que não seja parte de um relatório de tabela dinâmica.
- Para iniciar o Assistente de Tabela Dinâmica e Gráfico Dinâmico, pressione ALT+D+P.
Para adicionar o Assistente de Tabela Dinâmica e Gráfico Dinâmico à Barra de Ferramentas de Acesso Rápido, faça o seguinte:
- Clique na seta ao lado da barra de ferramentas e, em seguida, clique em Mais Comandos.
- Em Escolher comandos em, selecione Todos os Comandos.
- Na lista, selecione Assistente de Tabela Dinâmica e Gráfico Dinâmico, clique em Adicionar e, em seguida, clique em OK.
- Na página Etapa 1 do assistente, clique em Vários intervalos de consolidação. Depois clique em Avançar.
- E na página Etapa 2 do assistente, clique em Criarei os campos de página. Logo depois, clique em Avançar.
- Na página Etapa 2b do assistente, faça o seguinte: Para cada intervalo de células, clique em Recolher Caixa de Diálogo a fim de ocultar temporariamente a caixa de diálogo, selecione o intervalo de células na planilha, pressione Expandir Caixa de Diálogo e clique em Adicionar.
Se o intervalo de células estiver em outra pasta de trabalho, abra a pasta de trabalho primeiro para facilitar a seleção dos dados.
Em Quantos campos de página você deseja?, clique em 0 e, em seguida, clique em Avançar. Na página Etapa 3 do assistente, selecione um local para o relatório de tabela dinâmica e clique em Concluir.
Consolidar dados usando um único campo de página
Para incluir um único campo de página que tenha um item para cada intervalo de origem, mais um item que consolide todos os intervalos, faça o seguinte:
- Clique em uma célula em branco na pasta de trabalho que não seja parte de um relatório de tabela dinâmica.
- Para iniciar o Assistente de Tabela Dinâmica e Gráfico Dinâmico, pressione ALT+D+P.
Para adicionar o Assistente de Tabela Dinâmica e Gráfico Dinâmico à Barra de Ferramentas de Acesso Rápido, faça o seguinte:
- Clique na seta ao lado da barra de ferramentas e, em seguida, clique em Mais Comandos.
- Em Escolher comandos em, selecione Todos os Comandos.
- Na lista, selecione Assistente de Tabela Dinâmica e Gráfico Dinâmico, clique em Adicionar e, em seguida, clique em OK.
- Na página Etapa 1 do assistente, clique em Vários intervalos de consolidação e, em seguida, clique em Avançar.
- E na página Etapa 2a do assistente, clique em Crie um único campo de página para mim e, em seguida, clique em Avançar.
- Na página Etapa 2b do assistente, faça o seguinte:
- Para cada intervalo de células, clique em Recolher Caixa de Diálogo a fim de ocultar temporariamente a caixa de diálogo, selecione o intervalo de células na planilha e pressione Expandir Caixa de Diálogo.
- Se o intervalo de células estiver em outra pasta de trabalho, abra a pasta de trabalho primeiro para facilitar a seleção dos dados.
- Clique em Avançar.
- Na página Etapa 3 do assistente, selecione um local para o relatório de tabela dinâmica e clique em Concluir.
Consolidar dados usando vários campos de página
Você pode criar vários campos de página e também pode atribuir seus próprios nomes de item a cada intervalo de origem. Isso lhe permitirá criar consolidações parciais ou completas; por exemplo: um campo de página que consolide Marketing e Vendas separadamente de Fabricação, e outro campo de página que consolide os três departamentos. Para criar uma consolidação que use vários campos de página, faça o seguinte:
- Clique em uma célula em branco na pasta de trabalho que não seja parte de um relatório de tabela dinâmica.
- Para iniciar o Assistente de Tabela Dinâmica e Gráfico Dinâmico, pressione ALT+D+P.
Para adicionar o Assistente de Tabela Dinâmica e Gráfico Dinâmico à Barra de Ferramentas de Acesso Rápido, faça o seguinte:
- Clique na seta ao lado da barra de ferramentas e, em seguida, clique em Mais Comandos.
- Em Escolher comandos em, selecione Todos os Comandos.
- Na lista, selecione Assistente de Tabela Dinâmica e Gráfico Dinâmico, clique em& Adicionar e, em seguida, clique em OK.
- Na página Etapa 1 do assistente, clique em Vários intervalos de consolidação e, em seguida, clique em Avançar.
- Agora, na página Etapa 2 do assistente, clique em Criarei os campos de página e, em seguida, clique em Avançar.
- Na página Etapa 2b do assistente, faça o seguinte:
A cada intervalo de células, clique em Recolher Caixa de Diálogo a fim de ocultar temporariamente a caixa de diálogo, selecione o intervalo de células na planilha, pressione Expandir Caixa de Diálogo e clique em Adicionar.
Se o intervalo de células estiver em outra pasta de trabalho, abra a pasta de trabalho primeiro para facilitar a seleção dos dados.
Em quantos campos de página você deseja? Então clique no número de campos de página a ser usado.
Em quais rótulos de itens você deseja que cada campo de página use para identificar o intervalo de dados selecionados?, em cada campo de página, selecione o intervalo de células e digite um nome para ele.
O exemplo de: construindo tabela dinâmica nos relatórios do Excel
- Se você tiver selecionado 1 em Quantos campos de página você deseja?, selecione cada intervalo e digite um nome diferente na caixa Campo Um. Se você tiver quatro intervalos e cada um corresponder a um trimestre diferente do ano fiscal, selecione o primeiro intervalo, digite T1, selecione o segundo intervalo, digite T2 e repita o processo para T3 e T4.
- Se você tiver selecionado 2 em Quantos campos de página você deseja?, faça o mesmo que no exemplo anterior na caixa Campo um. Em seguida, selecione dois intervalos e digite o mesmo nome (por exemplo, H1 e H2) na caixa Campo dois. Selecione o primeiro intervalo, digite H1, selecione o segundo intervalo, digite H1, selecione o terceiro intervalo, digite H2, selecione o quarto intervalo e digite H2.
- Clique em Avançar.
- Na página Etapa 3 do assistente, selecione um local para o relatório de tabela dinâmica e clique em Concluir.
- Como criar modelo de dados no Excel?
- O que são tabelas dinâmicas e por que as usamos?
- Quais São as Opções Mais Usadas Em Uma Tabela Dinâmica
- Como Excluir uma Tabela de Excel Sem Perder os Dados e a Formatação
- Ocultar Valores Zero na Tabela Dinâmica
Isso foi nosso post para construir sua primeira tabela dinâmica. Compre livros de Excel também, para aprimorar suas habilidades!
Muito bom o exercicio. valeu pelas dicas.