Pular para o conteúdo

A primeira escola especializada em tecnologia do Alto Solimões, com corpo técnico formado na área.

Maiyu Academy

Módulo 6: Planilhas eletrônicas avançadas

Tabelas dinâmicas

Trocar a pergunta arrastando um campo, sem escrever uma fórmula

O que você vai ver aqui

A ferramenta que resume uma base de dez mil linhas num relatório cruzado sem uma fórmula sequer — e que permite mudar a pergunta arrastando um campo de lugar.

O que é

A tabela dinâmica substitui dezenas de SOMASES e permite reformular a análise em segundos.

Pré-requisito absoluto: base bem formada, conforme a aula 17. Uma linha de cabeçalho, sem mesclagem, sem linhas em branco, sem subtotais.

Criação

Selecione a base, Inserir, Tabela Dinâmica, escolha nova planilha. Criar a base como Tabela (Ctrl + T) antes é recomendado, porque a dinâmica passa a incluir automaticamente as linhas novas.

As quatro áreas do painel de campos

ÁreaPapel
FiltrosRecorte geral do relatório
ColunasCategorias distribuídas na horizontal
LinhasCategorias distribuídas na vertical
ValoresO que será calculado

A pergunta define o layout: "faturamento por categoria em cada filial" coloca categoria em Linhas, filial em Colunas e valor em Valores.

Configuração de valores

Clique no campo em Valores, Configurações do Campo de Valor:

  • Resumir por — Soma, Contagem, Média, Máximo, Mínimo, Desvio padrão.
  • Mostrar valores como — percentual do total geral, percentual do total da linha ou da coluna, diferença em relação a outro item, total acumulado, classificação. É aqui que se obtém participação percentual sem nenhuma fórmula.

Agrupamento

  • Datas — agrupar por dia, mês, trimestre e ano, criando hierarquia de detalhamento.
  • Números — agrupar em faixas, como valores de 0 a 500, 501 a 1000.
  • Texto — selecionar itens e agrupar manualmente, criando categorias personalizadas.

Filtros, segmentação e linha do tempo

  • Filtros de rótulo, de valor e dos 10 primeiros.
  • Segmentação de Dados — botões visuais de filtro, que podem ser conectados a várias tabelas dinâmicas ao mesmo tempo pelo comando Conexões de Relatório. É o que transforma um conjunto de tabelas num painel interativo.
  • Linha do Tempo — segmentação específica para datas.

Layout e manutenção

  • Design, Layout do Relatório: Compacto, Estrutura de Tópicos ou Tabular. Tabular é o mais legível para exportação.
  • Subtotais e Totais Gerais podem ser desativados.
  • Repetir Todos os Rótulos de Item facilita reaproveitar o resultado como base.
  • Atualizar (Alt + F5) é obrigatório após qualquer mudança na base. A tabela dinâmica não recalcula sozinha.
  • Campo Calculado e Item Calculado permitem criar métricas dentro da dinâmica, como margem.
  • Duplo clique num valor gera uma nova aba com os registros que compõem aquele número — recurso excelente para auditoria.
  • INFODADOSTABELADINÂMICA extrai um valor específico da dinâmica para uso em outra célula, útil na montagem de dashboards.

Na prática

  1. Converta a base de vendas em Tabela e crie a primeira dinâmica.
  2. Monte faturamento por categoria e por filial.
  3. Troque a pergunta arrastando campos e observe o relatório mudar.
  4. Agrupe as datas por ano, trimestre e mês.
  5. Mostre o valor como percentual do total da coluna.
  6. Crie faixas de valor por agrupamento numérico.
  7. Insira três segmentações e uma linha do tempo, conectadas a duas dinâmicas.
  8. Crie um campo calculado de ticket médio.
  9. Dê duplo clique num valor e analise o detalhamento gerado.
  10. Altere a base, atualize e confira.

Exercícios

  1. Quais são as quatro áreas do painel de campos e o que cada uma faz?
  2. A dinâmica está contando em vez de somar. Qual a causa provável?
  3. Monte um relatório de faturamento por vendedor e mês, com percentual do total.
  4. Explique a diferença entre filtro comum e segmentação de dados.
  5. Por que é preciso atualizar a tabela dinâmica manualmente?

Erros comuns

  • Base com linhas em branco ou cabeçalho mesclado.
  • Esquecer de atualizar depois de alterar a base.
  • Criar a dinâmica sobre um intervalo fixo que não cresce com os dados.

Checklist de saída

Você responde perguntas de negócio em segundos, sem escrever fórmula, e sabe auditar de onde veio cada número.

Referências

  • MAIA, André Luiz de Souza. Dashboards com Excel: extraia o máximo dos seus dados. São Paulo: Alta Books, 2018.
  • ALECRIM, Emerson. Excel 2019: o manual definitivo. São Paulo: Novatec, 2019.
  • Microsoft Support. Criar uma Tabela Dinâmica. Disponível em: https://support.microsoft.com/pt-br/excel

Testa o que você leu

4 perguntas sobre esta aula. Errar aqui não custa nada - a explicação vem junto da correção.

Pergunta 1 de 4
A tabela dinâmica está contando em vez de somar. Qual é a causa provável?