Excel

Dicas de tabela dinâmica

Pivot Table Tips

Visão geral | Por que Pivot? | Dicas | Exemplos | Treinamento

As tabelas dinâmicas são um mecanismo de relatório embutido no Excel. Eles são a melhor ferramenta no Excel para analisar dados sem fórmulas . Você pode criar uma tabela dinâmica básica em cerca de um minuto e começar a explorar seus dados de forma interativa. Abaixo estão mais de 20 dicas para obter o máximo desta ferramenta flexível e poderosa.





1. Você pode construir uma tabela dinâmica em cerca de um minuto

Muitas pessoas pensam que construir uma tabela dinâmica é complicado e demorado, mas simplesmente não é verdade. Em comparação com o tempo que você levaria para criar um relatório equivalente manualmente, as tabelas dinâmicas são incrivelmente rápidas. Se você tiver dados de origem bem estruturados, poderá criar uma tabela dinâmica em menos de um minuto. Comece selecionando qualquer célula nos dados de origem:

Dados brutos (vendas de chocolate), prontos para uma tabela dinâmica
Dados de fonte de exemplo





Em seguida, siga estas quatro etapas:

  1. Na guia Inserir da faixa de opções, clique no botão Tabela Dinâmica
  2. Na caixa de diálogo Criar Tabela Dinâmica, verifique os dados e clique em OK
  3. Arraste um campo 'rótulo' para a área Rótulos de linha (por exemplo, cliente)
  4. Arraste um campo numérico para a área Valores (por exemplo, vendas)

Uma rápida tabela dinâmica do Excel mostrando as vendas de chocolate
Uma tabela dinâmica básica em cerca de 30 segundos



A tabela dinâmica acima mostra o total de vendas por produto, mas você pode reorganizar facilmente os campos para mostrar o total de vendas por região, categoria, mês e assim por diante. Assista ao vídeo abaixo para uma rápida demonstração:

Vídeo: Como criar rapidamente uma tabela dinâmica

2. Limpe seus dados de origem

Para minimizar problemas futuros, certifique-se de que seus dados estejam em boas condições. Os dados de origem não devem ter linhas ou colunas em branco e nenhum subtotal. Cada coluna deve ter um nome exclusivo (em apenas uma linha) e representar um campo para cada linha / registro nos dados:

fórmula para adicionar várias células no excel

Dados perfeitos para uma tabela dinâmica!
Dados perfeitos para uma tabela dinâmica!

Às vezes, pode ser necessário adicionar dados ausentes. Veja este vídeo para dicas:

Vídeo: Como preencher rapidamente os dados ausentes

3. Conte os dados primeiro

Ao criar uma tabela dinâmica pela primeira vez, use-a para gerar uma contagem simples primeiro para garantir que a tabela dinâmica está processando os dados conforme o esperado. Para fazer isso, basta adicionar qualquer campo de texto como um campo de valor. Você verá uma tabela dinâmica muito pequena que exibe a contagem total de registros, ou seja, o número total de linhas em seus dados. Se esse número faz sentido para você, está pronto para continuar. Se o número não fizer sentido para você, é possível que a tabela dinâmica não esteja lendo os dados corretamente ou que os dados não tenham sido definidos corretamente.

300 primeiros nomes significa que temos 300 funcionários. Verificar.
300 primeiros nomes significa que temos 300 funcionários. Verificar.

4. Planeje antes de construir

Embora seja muito divertido arrastar campos em torno de uma tabela dinâmica e observar o Excel produzindo mais uma representação incomum dos dados, você pode se ver caindo em muitos buracos de coelho improdutivos com muita facilidade. Uma hora depois, não é mais tão divertido. Antes de começar a construir, anote o que você está tentando medir ou compreender e faça um esboço de alguns relatórios simples em um bloco de notas. Estas notas simples ajudarão a guiá-lo através do grande número de opções que você tem à sua disposição. Mantenha as coisas simples e concentre-se nas perguntas que você precisa responder.

5. Use uma tabela para seus dados para criar um 'intervalo dinâmico'

Se você usar uma tabela do Excel para os dados de origem de sua tabela dinâmica, terá um benefício muito bom: seu intervalo de dados torna-se 'dinâmico'. Um intervalo dinâmico irá expandir e encolher automaticamente a tabela conforme você adiciona ou remove dados, então não precisa se preocupar se a tabela dinâmica está perdendo os dados mais recentes. Quando você usa uma tabela para sua tabela dinâmica, ela sempre estará em sincronia com seus dados.

Para usar uma tabela para sua tabela dinâmica:

  1. Selecione qualquer célula nos dados, use o atalho de teclado Ctrl-T para criar uma Tabela
  2. Clique no botão Resumir com Tabela Dinâmica (TableTools> Design)
  3. Construa sua tabela dinâmica normalmente
  4. Lucro: os dados que você adicionar à sua tabela aparecerão automaticamente na sua tabela dinâmica na atualização

Vídeo: Use uma tabela para sua próxima tabela dinâmica

Criação de uma tabela simples a partir dos dados usando (Ctrl-T)
Criação de uma tabela simples a partir dos dados usando (Ctrl-T)

Agora que temos uma tabela, podemos usar Resumir com Tabela Dinâmica
Agora que temos uma tabela, podemos usar resumir com uma tabela dinâmica

Ainda precisa de inspiração sobre por que você deve aprender tabelas dinâmicas? Ver minha história pessoal .

6. Use uma tabela dinâmica para contar coisas

Por padrão, uma Tabela Dinâmica contará qualquer campo de texto. Este pode ser um recurso realmente útil em muitas situações gerais de negócios. Por exemplo, suponha que você tenha uma lista de funcionários e deseja obter uma contagem por departamento. Para obter uma divisão por departamento, siga estas etapas:

  1. Crie uma tabela dinâmica normalmente
  2. Adicionar o departamento como um rótulo de linha
  3. Adicione o campo Nome do funcionário como um valor
  4. A tabela dinâmica exibirá uma contagem de funcionários por departamento

Discriminação de funcionários por departamento
Discriminação de funcionários por departamento

7. Mostrar totais como uma porcentagem

Em muitas tabelas dinâmicas, você desejará mostrar uma porcentagem em vez de uma contagem. Por exemplo, talvez você queira mostrar um detalhamento das vendas por produto. Mas, em vez de mostrar as vendas totais de cada produto, você deseja mostrar as vendas como uma porcentagem das vendas totais. Supondo que você tenha um campo chamado Vendas em seus dados, basta seguir estas etapas:

  1. Adicionar produto à tabela dinâmica como um rótulo de linha
  2. Adicione vendas à tabela dinâmica como um valor
  3. Clique com o botão direito do mouse no campo Vendas e defina 'Mostrar valores como' como '% do total geral'

Veja a dica abaixo 'Adicionar um campo mais de uma vez a uma tabela dinâmica' para aprender como mostrar vendas totais e vendas como uma porcentagem do total ao mesmo tempo.

Alterando a exibição do valor para% do total
Alterando a exibição do valor para% do total

Soma de funcionários exibida como% do total
Soma de funcionários exibida como% do total

8. Use uma tabela dinâmica para construir uma lista de valores únicos

Como as tabelas dinâmicas resumem os dados, elas podem ser usadas para localizar valores exclusivos em um campo. Essa é uma boa maneira de ver rapidamente todos os valores que aparecem em um campo e também encontrar erros de digitação e outras inconsistências. Por exemplo, suponha que você tenha dados de vendas e queira ver uma lista de todos os produtos vendidos. Para criar uma lista de produtos:

  1. Crie uma tabela dinâmica normalmente
  2. Adicionar o produto como um rótulo de linha
  3. Adicione qualquer outro campo de texto (categoria, cliente, etc) como um valor
  4. A tabela dinâmica mostrará uma lista de todos os produtos que aparecem nos dados de vendas

Cada produto que aparece nos dados é listado (incluindo um erro de digitação)
Cada produto que aparece nos dados é listado (incluindo um erro de digitação)

Treinamento em vídeo Pivot Table - rápido, claro e direto ao ponto

9. Crie uma tabela dinâmica independente

Depois de criar uma tabela dinâmica a partir de dados na mesma planilha, você pode remover os dados se desejar e a tabela dinâmica continuará a operar normalmente. Isso ocorre porque uma tabela dinâmica tem um pivô oculto que contém uma duplicata exata dos dados usados ​​para criar a tabela dinâmica.

  1. Atualize a tabela dinâmica para garantir que o cache esteja atualizado (Ferramentas de tabela dinâmica> Atualizar)
  2. Exclua a planilha que contém os dados
  3. Use sua tabela dinâmica normalmente

Vídeo: Como fazer uma tabela dinâmica independente

10. Agrupe uma tabela dinâmica manualmente

Embora as tabelas dinâmicas agrupem dados automaticamente de várias maneiras, você também pode agrupar itens manualmente em seus próprios grupos personalizados. Por exemplo, suponha que você tenha uma tabela dinâmica que mostra uma divisão dos funcionários por departamento. Suponha que você queira agrupar ainda mais os departamentos de Engenharia, Cumprimento e Suporte no Grupo 1, e Vendas e Marketing no Grupo 2. Os Grupos 1 e 2 não aparecem nos dados, eles são seus próprios grupos personalizados. Para agrupar a tabela dinâmica em grupos ad hoc, Grupo 1 e Grupo 2:

  1. Clique com o botão direito do mouse para selecionar cada item do primeiro grupo
  2. Clique com o botão direito em um dos itens e escolha Grupo no menu
  3. O Excel cria um novo grupo, 'Grupo1'
  4. Selecione Marketing e Vendas na coluna B e agrupe como acima
  5. O Excel cria outro grupo, 'Grupo2'

Começando a agrupar manualmente
Começando a agrupar manualmente

A meio do agrupamento manual - Grupo 1 concluído
A meio do agrupamento manual - Grupo 1 concluído

Agrupamento finalizado manualmente
Agrupamento finalizado manualmente

11. Grupo de dados numéricos em intervalos

Um dos recursos mais interessantes e poderosos que toda tabela dinâmica possui é a capacidade de agrupar dados numéricos em intervalos ou segmentos. Por exemplo, suponha que você tenha uma lista de resultados de votação que inclui a idade do eleitor e deseja resumir os resultados por faixa etária:

  1. Crie sua tabela dinâmica normalmente
  2. Adicionar idade como rótulo de linha, voto como rótulo de coluna e nome como valor
  3. Clique com o botão direito em qualquer valor no campo Idade e escolha Grupo
  4. Insira 10 como o intervalo na área de entrada 'Por:'
  5. Ao clicar em OK, você verá os dados de votação agrupados por idade em intervalos de 10 anos

Vídeo: Como agrupar uma tabela dinâmica por faixa etária

Os dados de origem para os resultados da votação
Os dados de origem para os resultados da votação

Agrupando o campo de idade em intervalos de 10 anos
Agrupando o campo de idade em intervalos de 10 anos

Concluído o agrupamento dos resultados da votação por faixa etária
Concluído o agrupamento dos resultados da votação por faixa etária

12. Renomeie os campos para melhor legibilidade

Quando você adiciona campos a uma tabela dinâmica, a tabela dinâmica exibe o nome que aparece nos dados de origem. Os nomes dos campos de valor aparecerão com 'Soma de' ou 'Contagem de' quando forem adicionados a uma tabela dinâmica. Por exemplo, você verá Soma de Vendas, Contagem de Região e assim por diante. No entanto, você pode simplesmente substituir esse nome pelo seu. Basta selecionar a célula que contém o campo que deseja renomear e digitar um novo nome.

Renomeie um campo digitando sobre o nome original
Renomeie um campo digitando sobre o nome original

13. Adicione um espaço aos nomes dos campos quando o Excel reclamar

Ao tentar renomear campos, você pode ter um problema se tentar usar exatamente o mesmo nome de campo que aparece nos dados. Por exemplo, suponha que você tenha um campo chamado Vendas em seus dados de origem. Como um campo de valor, ele aparece como Soma de Vendas , mas (sensatamente) você quer dizer Vendas . No entanto, quando você tenta usar Vendas, o Excel reclama que o campo já existe e lança uma mensagem de erro 'O nome do campo da tabela dinâmica já existe'.

Excel não
O Excel não gosta do seu novo nome de campo

Como uma solução simples, basta adicionar um espaço ao final do seu novo nome de campo. Você não vê a diferença e o Excel não reclama.

Adicionar um espaço ao nome evita o problema
Adicionar um espaço ao nome evita o problema

14. Adicione um campo mais de uma vez a uma tabela dinâmica

Existem muitas situações em que faz sentido adicionar o mesmo campo a uma tabela dinâmica mais de uma vez. Pode parecer estranho, mas você pode adicionar o mesmo campo a uma tabela dinâmica mais de uma vez. Por exemplo, suponha que você tenha uma tabela dinâmica que mostra uma contagem de funcionários por departamento.

A contagem funciona bem, mas você também deseja mostrar a contagem como uma porcentagem do total de funcionários. Nesse caso, a solução mais simples é adicionar o mesmo campo duas vezes como um campo Valor:

  1. Adicione um campo de texto à área Valor (por exemplo, nome, nome, etc.)
  2. Por padrão, você obterá uma contagem de campos de texto
  3. Adicione o mesmo campo novamente à área de valor
  4. Clique com o botão direito na segunda instância e altere Mostrar valores como para '% do total geral'
  5. Renomeie ambos os campos como desejar

Definir um campo para mostrar a porcentagem do total
Definir um campo para mostrar a porcentagem do total

O campo Nome foi adicionado duas vezes
O campo Nome foi adicionado duas vezes

15. Formatar automaticamente todos os campos de valor

Sempre que você adiciona um campo numérico como um Valor em uma tabela dinâmica, deve definir o formato do número diretamente no campo. Você pode ficar tentado a formatar os valores que vê na tabela dinâmica diretamente, mas isso não é uma boa ideia, porque não é confiável, pois a tabela dinâmica muda. Definir o formato diretamente no campo garantirá que o campo seja exibido usando o formato desejado, não importa o quão grande ou pequena a tabela dinâmica se torne.

Por exemplo, suponha uma tabela dinâmica que mostra uma divisão das vendas por região. Ao adicionar pela primeira vez o campo Vendas à tabela dinâmica, ele será exibido no formato de número Geral, pois é um campo numérico. Para aplicar o formato de número contábil ao próprio campo:

a busca de metas é um exemplo de ferramenta ________.
  1. Clique com o botão direito do mouse no campo Vendas e selecione Configurações do campo de valor no menu
  2. Clique no botão Formato do Número na caixa de diálogo de configurações do campo Valor que aparece
  3. Defina o formato para Contabilidade e clique em OK para sair

Definir o formato diretamente em um campo de valor
Definir o formato diretamente em um campo de valor

16. Faça um detalhamento para ver (ou extrair) os dados por trás de qualquer total

Sempre que você vir um total exibido em uma tabela dinâmica, poderá ver e extrair facilmente os dados que compõem o total por meio de uma 'busca detalhada'. Por exemplo, suponha que você esteja olhando uma tabela dinâmica que mostra a contagem de funcionários por departamento. Você pode ver que há 50 funcionários no departamento de Engenharia, mas deseja ver os nomes reais. Para ver as 50 pessoas que compõem esse número, clique duas vezes diretamente no número 50 e o Excel adicionará uma nova planilha à sua pasta de trabalho que contém os dados exatos usados ​​para calcular 50 engenheiros. Você pode usar essa mesma abordagem para ver e extrair dados por trás dos totais onde quer que os veja em uma tabela dinâmica.

Clique duas vezes em um total para
Clique duas vezes em um total para 'detalhar'

Os 50 engenheiros, extraídos em uma nova planilha automaticamente
Os 50 engenheiros, extraídos em uma nova planilha automaticamente

17. Clone suas tabelas dinâmicas quando precisar de outra visualização

Depois de ter uma tabela dinâmica configurada, você pode querer ver uma visão diferente dos mesmos dados. Você pode, é claro, apenas reorganizar sua tabela dinâmica existente para criar a nova visualização. Mas se você estiver criando um relatório que planeja usar e atualizar continuamente, a coisa mais fácil a fazer é clonar uma tabela dinâmica existente, de modo que ambas as visualizações dos dados estejam sempre disponíveis.

Existem duas maneiras fáceis de clonar uma tabela dinâmica. A primeira forma envolveu a duplicação da planilha que contém a tabela dinâmica. Se você tiver uma tabela dinâmica configurada em uma planilha com um título, etc., você pode apenas clicar com o botão direito na guia da planilha para copiar a planilha na mesma planilha. Outra maneira de clonar uma tabela dinâmica é copiá-la e colá-la em outro lugar. Usando essas abordagens, você pode fazer quantas cópias quiser.

Quando você clona uma tabela dinâmica desta forma, ambas as tabelas dinâmicas compartilham o mesmo pivô oculto . Isso significa que, ao atualizar qualquer um dos clones (ou o original), todas as tabelas dinâmicas relacionadas serão atualizadas.

Vídeo: Como clonar uma tabela dinâmica

18. Desfaça a clonagem de uma tabela dinâmica para atualizar de forma independente

Depois de clonar uma tabela dinâmica, você pode se deparar com uma situação em que realmente não deseja que o clone seja vinculado ao mesmo cache dinâmico do original. Um exemplo comum é depois de agrupar um campo de data em uma tabela dinâmica, atualizar e descobrir que também agrupou acidentalmente o mesmo campo de data em outro tabela dinâmica que você não pretendia alterar. Quando as tabelas dinâmicas compartilham o mesmo cache dinâmico, também compartilham o agrupamento de campos.

Esta é uma maneira de desfazer a clonagem de uma tabela dinâmica, ou seja, desvinculá-la do cache dinâmico que ela compartilha com outras tabelas dinâmicas na mesma planilha:

  1. Corte toda a tabela dinâmica para a área de transferência
  2. Cole a tabela dinâmica em uma nova pasta de trabalho
  3. Atualize a tabela dinâmica
  4. Copie novamente para a área de transferência
  5. Cole-o de volta na pasta de trabalho original
  6. Descartar a pasta de trabalho temporária

Sua tabela dinâmica agora usará seu próprio cache dinâmico e não será atualizada com as outras tabelas dinâmicas na pasta de trabalho, nem compartilhará o mesmo agrupamento de campos.

19. Livre-se de títulos inúteis

O layout padrão para novas tabelas dinâmicas é o layout Compacto. Este layout exibirá 'Rótulos de linha' e 'Rótulos de coluna' como cabeçalhos na tabela dinâmica. Esses não são os títulos mais intuitivos, especialmente para pessoas que não costumam usar tabelas dinâmicas. Uma maneira fácil de se livrar desses títulos estranhos é mudar o layout da tabela dinâmica de Compacto para Contorno ou Tabular. Isso fará com que a tabela dinâmica exiba os nomes dos campos reais como cabeçalhos na tabela dinâmica, o que é muito mais sensato. Para se livrar totalmente desses rótulos, procure um botão chamado Cabeçalhos de campo na guia Analisar da faixa de opções Ferramentas da tabela dinâmica. Clicar neste botão desabilitará os títulos completamente.

Observe os cabeçalhos de campo inúteis e confusos
Observe os cabeçalhos de campo inúteis e confusos

Mudando o layout de Compacto para Contorno
Mudando o layout de Compacto para Contorno

Os títulos de campo no layout de esboço são muito mais sensatos
Os títulos de campo no layout de esboço são muito mais sensatos

20. Adicione um pequeno espaço em branco ao redor de suas tabelas dinâmicas

Esta é apenas uma dica de design simples. Todos os bons designers sabem que um design agradável requer um pequeno espaço em branco. Espaço em branco significa apenas espaço vazio reservado para dar espaço para respirar o layout. Depois de criar uma tabela dinâmica, insira uma coluna extra à esquerda e uma ou duas linhas extras no topo. Isso dará à sua mesa dinâmica algum espaço para respirar e criará um layout com melhor aparência. Na maioria dos casos, também recomendo que você desative as linhas de grade na planilha. A própria tabela dinâmica apresentará uma grade visual forte, de modo que as linhas de grade fora da tabela dinâmica são desnecessárias e simplesmente criarão ruído visual.

Adicione um pequeno espaço em branco ao redor das tabelas dinâmicas
Um pequeno espaço em branco faz suas tabelas dinâmicas parecerem mais polidas

Inspiração: 5 tabelas dinâmicas que você não viu antes .

21. Livre-se dos totais gerais de linha e coluna

Por padrão, as tabelas dinâmicas mostram totais para linhas e colunas, mas você pode desativar facilmente um ou ambos os totais se não quiser. Na guia Tabela Dinâmica da faixa de opções, basta clicar no botão Totais e escolher as opções desejadas.

Ativar e desativar totais gerais
Você pode remover totais gerais para linhas e colunas

22. Formate células vazias

Se você tiver uma tabela dinâmica com muitas células em branco, poderá controlar o caractere que é exibido em cada célula em branco. Por padrão, as células vazias não exibirão nada. Para definir seu próprio personagem, clique com o botão direito dentro da tabela dinâmica e selecione as opções da Tabela Dinâmica. Em seguida, certifique-se de que a opção 'Células vazias como:' esteja marcada e insira o caractere que deseja ver. Lembre-se de que essa configuração respeita o formato de número aplicado. Por exemplo. se você estiver usando o formato de número de contabilidade para um campo de valor numérico e inserir um zero, verá um hífen '-' exibido na tabela dinâmica, já que é assim que os valores zero são exibidos com o formato de contabilidade.

A tabela dinâmica mostra células vazias como 0 (zero) com formato de contabilidade
Células vazias definidas para exibir 0 (zero) e o formato de número de contabilidade fornece hifens

23. Desligue o ajuste automático quando necessário

Por padrão, quando você atualiza uma tabela dinâmica, as colunas que contêm dados são ajustadas automaticamente para melhor se ajustar aos dados. Normalmente, isso é uma coisa boa, mas pode deixá-lo louco se você tiver outras coisas na planilha junto com a tabela dinâmica, ou se você ajustou cuidadosamente as larguras das colunas manualmente e não deseja alterá-las. Para desativar esse recurso, clique com o botão direito dentro da tabela dinâmica e escolha Opções de tabela dinâmica. Na primeira guia das opções (ou na guia de layout em um Mac), desmarque 'Ajustar automaticamente as larguras das colunas ao atualizar'.

Opção de ajuste automático da coluna da tabela dinâmica para Windows
Opção de ajuste automático da coluna da tabela dinâmica para Windows

Opção de ajuste automático da coluna da tabela dinâmica para Mac
Opção de ajuste automático da coluna da tabela dinâmica para Mac

Precisa aprender tabelas dinâmicas? Nós temos treinamento de vídeo sólido com planilhas de prática.

Autor Dave Bruns


^