Análise de dados do Excel - tabelas dinâmicas

A análise de dados em um grande conjunto de dados é freqüentemente necessária e importante. Envolve resumir os dados, obter os valores necessários e apresentar os resultados.

O Excel fornece uma Tabela Dinâmica para permitir que você resuma milhares de valores de dados de forma fácil e rápida para obter os resultados necessários.

Considere a seguinte tabela de dados de vendas. A partir desses dados, você pode ter que resumir o total de vendas por região, mês ou vendedor. A maneira fácil de lidar com essas tarefas é criar uma Tabela Dinâmica que você pode modificar dinamicamente para resumir os resultados da maneira que desejar.

Criação de tabela dinâmica

Para criar tabelas dinâmicas, certifique-se de que a primeira linha tenha cabeçalhos.

  • Clique na mesa.
  • Clique na guia INSERIR na faixa de opções.
  • Clique em Tabela Dinâmica no grupo Tabelas. A caixa de diálogo Tabela Dinâmica é exibida.

Como você pode ver na caixa de diálogo, você pode usar uma Tabela ou um Intervalo da pasta de trabalho atual ou usar uma fonte de dados externa.

  • Na caixa Tabela / Intervalo, digite o nome da tabela.
  • Clique em Nova planilha para informar ao Excel onde manter a tabela dinâmica.
  • Clique OK.

Uma tabela dinâmica em branco e uma lista de campos da tabela dinâmica aparecem.

Tabelas Dinâmicas Recomendadas

Caso você seja novo nas tabelas dinâmicas ou não saiba quais campos selecionar a partir dos dados, você pode usar o Recommended PivotTables que o Excel fornece.

  • Clique na tabela de dados.

  • Clique na guia INSERIR.

  • Clique em Tabelas Dinâmicas Recomendadas no grupo Tabelas. A caixa de diálogo Tabelas Dinâmicas Recomendadas é exibida.

Na caixa de diálogo Tabelas Dinâmicas recomendadas, as possíveis Tabelas Dinâmicas personalizadas que se adequam aos seus dados são exibidas.

  • Clique em cada uma das opções de tabela dinâmica para ver a visualização no lado direito.
  • Clique na soma da tabela dinâmica do valor do pedido por vendedor e por mês.

Clique OK. A tabela dinâmica selecionada aparece em uma nova planilha. Você pode observar os campos de tabela dinâmica que foram selecionados na lista de campos de tabela dinâmica.

Campos da Tabela Dinâmica

Os cabeçalhos em sua tabela de dados aparecerão como campos na Tabela Dinâmica.

Você pode selecioná-los / desmarcá-los para alterar instantaneamente sua Tabela Dinâmica para exibir apenas as informações que você deseja e da maneira que desejar. Por exemplo, se você deseja exibir as informações da conta em vez das informações do valor do pedido, desmarque Valor do pedido e selecione Conta.

Áreas da Tabela Dinâmica

Você pode até mesmo alterar o Layout de sua Tabela Dinâmica instantaneamente. Você pode usar as áreas da tabela dinâmica para fazer isso.

Nas áreas da Tabela Dinâmica, você pode escolher -

  • Quais campos exibir como linhas
  • Quais campos exibir como colunas
  • Como resumir seus dados
  • Filtros para qualquer um dos campos
  • Quando atualizar seu layout de tabela dinâmica
    • Você pode atualizá-lo instantaneamente enquanto arrasta os campos pelas áreas, ou
    • Você pode adiar a atualização e atualizá-la apenas quando clicar em ATUALIZAR

Uma atualização instantânea ajuda você a brincar com os diferentes layouts e escolher aquele que se adapta às suas necessidades de relatório.

Você pode simplesmente arrastar os campos por essas áreas e observar o layout da tabela dinâmica enquanto o faz.

Aninhamento na Tabela Dinâmica

Se você tiver mais de um talhão em qualquer uma das áreas, o aninhamento acontecerá na ordem em que você colocar os campos nessa área. Você pode alterar a ordem arrastando os campos e observar como o aninhamento muda. Nas opções de layout acima, você pode observar que

  • Os meses estão em colunas.
  • Região e vendedor em linhas nessa ordem. ou seja, os valores do vendedor são aninhados nos valores da região.
  • O resumo é feito pela soma do valor do pedido.
  • Nenhum filtro é escolhido.

A Tabela Dinâmica resultante é a seguinte -

Nas áreas da tabela dinâmica, em linhas, clique na região e arraste-a para baixo do vendedor de forma que fique da seguinte maneira -

A ordem de aninhamento muda e a Tabela Dinâmica resultante é a seguinte -

Note- Você pode observar claramente que o layout com a ordem de aninhamento - Região e depois Vendedor produz um relatório melhor e compacto do que aquele com a ordem de aninhamento - Vendedor e depois Região. Caso o Vendedor represente mais de uma área e você precise resumir as vendas por Vendedor, o segundo layout seria a melhor opção.

Filtros

Você pode atribuir um filtro a um dos campos para que possa alterar dinamicamente a tabela dinâmica com base nos valores desse campo.

Arraste a região das linhas para os filtros nas áreas da tabela dinâmica.

O filtro com o rótulo como Região aparece acima da Tabela Dinâmica (caso você não tenha linhas vazias acima da Tabela Dinâmica, a Tabela Dinâmica é empurrada para baixo para liberar espaço para o Filtro.

Você pode ver isso -

  • Os valores do vendedor aparecem em linhas.
  • Os valores do mês aparecem em colunas.
  • Filtro de região aparece na parte superior com o padrão selecionado como TODOS.
  • O valor de resumo é a soma do valor do pedido
    • A soma do valor do pedido do vendedor aparece na coluna Total geral
    • A soma do valor do pedido no mês aparece na linha Total geral

Clique na seta na caixa à direita da região do filtro. Uma lista suspensa com os valores da região do campo é exibida.

  • Marque a opção Select Multiple Items. Caixas de seleção aparecem para todos os valores.
  • Selecione Sul e Oeste, desmarque os outros valores e clique em OK.

Os dados relativos às regiões Sul e Oeste serão resumidos conforme mostrado na captura de tela abaixo -

Você pode ver que ao lado da região do filtro, Multiple Itemsé exibido, indicando que você selecionou mais de um item. No entanto, quantos itens e / ou quais itens são selecionados não é conhecido no relatório que é exibido. Nesse caso, usar Segmentações de Dados é a melhor opção de filtragem.

Slicers

Você pode usar Slicers para ter uma melhor clareza sobre quais itens os dados foram filtrados.

  • Clique em ANALISAR em FERRAMENTAS PIVOTÁVEIS na faixa de opções.

  • Clique em Inserir Slicer no grupo Filtro. A caixa Inserir Segmentações de Dados é exibida. Ele contém todos os campos de seus dados.

  • Selecione os campos Região e mês. Clique OK.

Os Slicers para cada um dos campos selecionados aparecem com todos os valores selecionados por padrão. As Ferramentas do Slicer aparecem na Faixa de Opções para trabalhar nas configurações e na aparência do Slicer.

  • Selecione Sul e Oeste no Slicer para Região.
  • Selecione fevereiro e março no Slicer para o mês.
  • Mantenha a tecla Ctrl pressionada enquanto seleciona vários valores em um Slicer.

Os itens selecionados nos Slicers são destacados. A tabela dinâmica com valores resumidos para os itens selecionados será exibida.

Resumindo valores por outros cálculos

Nos exemplos até agora, você viu valores resumidos por Soma. No entanto, você também pode usar outros cálculos, se necessário.

Na lista de campos da tabela dinâmica

  • Selecione a conta de campo.
  • Desmarque o valor do pedido de campo.
  • Arraste o campo Conta para a área Resumindo Valores. Por padrão, Soma da conta será exibida.
  • Clique na seta do lado direito da caixa.
  • No menu suspenso que aparece, clique em Configurações do campo de valor.

A caixa Configurações do campo de valor é exibida. Vários tipos de cálculos aparecem como uma lista no campo Resumir valor por -

  • Selecione Contagem na lista.
  • O nome personalizado muda automaticamente para Contagem de conta. Clique OK.

A tabela dinâmica resume os valores da conta por contagem.

Ferramentas de Tabela Dinâmica

Siga as etapas abaixo para aprender a usar as ferramentas de tabela dinâmica.

  • Selecione a Tabela Dinâmica.

As seguintes ferramentas de tabela dinâmica aparecem na faixa de opções -

  • ANALYZE
  • DESIGN

ANALISAR

Alguns dos ANALYZE Os comandos da faixa de opções são -

  • Definir opções de tabela dinâmica
  • Configurações de campo de valor para o campo selecionado
  • Expandir Campo
  • Campo de recolhimento
  • Insert Slicer
  • Inserir linha do tempo
  • Atualizar dados
  • Alterar fonte de dados
  • Mover Tabela Dinâmica
  • Resolva a ordem (se houver mais cálculos)
  • PivotChart

PROJETO

Alguns dos DESIGN Os comandos da faixa de opções são -

  • Layout de tabela dinâmica
    • Opções para subtotais
    • Opções para totais gerais
    • Formulários de Layout de Relatório
    • Opções para linhas em branco
  • Opções de estilo de tabela dinâmica
  • Estilos de tabela dinâmica

Campo de expansão e recolhimento

Você pode expandir ou recolher todos os itens de um campo selecionado de duas maneiras -

  • Selecionando o símbolo ou à esquerda do campo selecionado.
  • Ao clicar em Expandir campo ou Contrair campo na faixa ANALYZE.

Selecionando o símbolo de expandir ou recolher à esquerda do campo selecionado

  • Selecione a célula que contém Leste na Tabela Dinâmica.
  • Clique no símbolo de recolhimento à esquerda de Leste.

Todos os itens em Leste serão recolhidos. O símbolo de recolhimento à esquerda de Leste muda para o símbolo de expansão .

Você pode observar que apenas os itens abaixo do Leste estão recolhidos. O resto dos itens da Tabela Dinâmica são como estão.

Clique no símbolo Expandir à esquerda de Leste. Todos os itens abaixo de Leste serão exibidos.

Usando ANALYZE na fita

Você pode recolher ou expandir todos os itens na Tabela Dinâmica de uma vez com os comandos Expandir Campo e Contrair Campo na Faixa de Opções.

  • Clique na célula que contém Leste na Tabela Dinâmica.
  • Clique na guia ANALISAR na Faixa de Opções.
  • Clique em Recolher campo no grupo Campo ativo.

Todos os itens do campo Leste na Tabela Dinâmica serão recolhidos.

Clique em Expandir Campo no grupo Campo Ativo.

Todos os itens serão exibidos.

Estilos de apresentação de relatórios

Você pode escolher o estilo de apresentação da sua Tabela Dinâmica, pois a incluiria como um relatório. Selecione um estilo que se encaixe no resto da sua apresentação ou relatório. Porém, não se aborreça com os estilos porque um relatório que dá impacto na exibição dos resultados é sempre melhor do que um colorido, que não destaca os pontos de dados importantes.

  • Clique em Leste na Tabela Dinâmica.
  • Clique em ANALISAR.
  • Clique em Configurações de campo no grupo Campo ativo. A caixa de diálogo Configurações do campo é exibida.
  • Clique na guia Layout e impressão.
  • Marque Inserir linha em branco após cada etiqueta de item.

Linhas em branco serão exibidas após cada valor do campo Região.

Você pode inserir linhas em branco do DESIGN guia também.

  • Clique na guia DESIGN.
  • Clique em Layout de relatório no grupo Layout.
  • Selecione Mostrar no formulário de contorno na lista suspensa.
  • Passe o mouse sobre os Estilos de tabela dinâmica. Uma prévia do estilo em que o mouse foi colocado aparecerá.
  • Selecione o estilo que se adapta ao seu relatório.

Será exibida uma tabela dinâmica em formato de contorno com o estilo selecionado.

Linha do tempo em tabelas dinâmicas

Para entender como usar a linha do tempo, considere o exemplo a seguir, em que os dados de vendas de vários itens são fornecidos pelo vendedor e pela localização. Há um total de 1891 linhas de dados.

Crie uma tabela dinâmica a partir deste intervalo com -

  • Localização e vendedor em linhas nessa ordem
  • Produto em colunas
  • Soma da quantidade em valores de resumo
  • Clique na Tabela Dinâmica.
  • Clique na guia INSERIR.
  • Clique em Linha do tempo no grupo Filtros. O Insert Timelines aparece.

Clique em Data e clique em OK. A caixa de diálogo Linha do tempo é exibida e as Ferramentas da linha do tempo aparecem na faixa de opções.

  • Na caixa de diálogo Linha do tempo, selecione MESES.
  • Na lista suspensa, selecione QUARTERS.
  • Clique em 2014 Q2.
  • Mantenha a tecla Shift pressionada e arraste para o quarto trimestre de 2014.

O cronograma é selecionado para o segundo - quarto trimestre de 2014.

A tabela dinâmica é filtrada para esta linha do tempo.