Painéis do Excel - Tabelas Dinâmicas

Se você tiver seus dados em uma única tabela do Excel, poderá resumir os dados da maneira que é necessária usando as tabelas dinâmicas do Excel. Uma Tabela Dinâmica é uma ferramenta extremamente poderosa que você pode usar para fatiar e dividir dados. Você pode rastrear e analisar centenas de milhares de pontos de dados com uma tabela compacta que pode ser alterada dinamicamente para permitir que você encontre as diferentes perspectivas dos dados. É uma ferramenta simples de usar, mas poderosa.

O Excel oferece uma maneira mais poderosa de criar uma Tabela Dinâmica a partir de várias tabelas, diferentes fontes de dados e fontes de dados externas. É denominado Power PivotTable que funciona em seu banco de dados conhecido como Data Model. Você conhecerá o Power PivotTable e outras ferramentas avançadas do Excel, como Power PivotChart e Relatórios do Power View em outros capítulos.

As tabelas dinâmicas, as tabelas dinâmicas do Power, os gráficos dinâmicos do Power e os relatórios do Power View são úteis para exibir resultados resumidos de conjuntos de big data em seu painel. Você pode obter o domínio da Tabela Dinâmica normal antes de se aventurar nas ferramentas elétricas.

Criação de uma tabela dinâmica

Você pode criar uma tabela dinâmica a partir de um intervalo de dados ou de uma tabela do Excel. Em ambos os casos, a primeira linha dos dados deve conter os cabeçalhos das colunas.

Você pode começar com uma Tabela Dinâmica vazia e construí-la do zero ou usar o comando Tabelas Dinâmicas Recomendadas do Excel para visualizar as possíveis Tabelas Dinâmicas personalizadas para seus dados e escolher uma que atenda ao seu propósito. Em ambos os casos, você pode modificar uma Tabela Dinâmica rapidamente para obter insights sobre os diferentes aspectos dos dados em mãos.

Considere o seguinte intervalo de dados que contém os dados de vendas para cada vendedor, em cada região e nos meses de janeiro, fevereiro e março -

Para criar uma tabela dinâmica a partir deste intervalo de dados, faça o seguinte -

  • Certifique-se de que a primeira linha tenha cabeçalhos. Você precisa de cabeçalhos porque eles serão os nomes dos campos em sua Tabela Dinâmica.

  • Nomeie o intervalo de dados como SalesData_Range.

  • Clique no intervalo de dados - SalesData_Range.

  • Clique na guia INSERIR na Faixa de Opções.

  • Clique em Tabela Dinâmica no grupo Tabelas.

A caixa de diálogo Criar Tabela Dinâmica é exibida.

Como você pode observar, na caixa de diálogo Criar Tabela Dinâmica, em Escolha os dados que deseja analisar, você pode selecionar uma Tabela ou um Intervalo da pasta de trabalho atual ou usar uma fonte de dados externa. Portanto, você pode usar as mesmas etapas para criar um formulário de tabela dinâmica um intervalo ou uma tabela.

  • Clique em Selecionar uma tabela ou intervalo.

  • Na caixa Tabela / Intervalo, digite o nome do intervalo - SalesData_Range.

  • Clique em Nova planilha em Escolha onde deseja que o relatório de tabela dinâmica seja colocado.

Você também pode observar que pode optar por analisar várias tabelas, adicionando este intervalo de dados ao Modelo de Dados. O modelo de dados é um banco de dados Excel Power Pivot.

  • Clique no botão OK. Uma nova planilha será inserida em sua pasta de trabalho. A nova planilha contém uma tabela dinâmica vazia.

  • Nomeie a planilha - Tabela Dinâmica de Intervalo.

Como você pode observar, a lista Campos da Tabela Dinâmica aparece no lado direito da planilha, contendo os nomes dos cabeçalhos das colunas no intervalo de dados. Além disso, na Faixa de Opções, Ferramentas de Tabela Dinâmica - ANALISAR e DESIGN aparecem.

Você precisa selecionar os campos da tabela dinâmica com base nos dados que deseja exibir. Ao colocar os campos nas áreas apropriadas, você pode obter o layout desejado para os dados. Por exemplo, para resumir o valor do pedido do vendedor para os meses - janeiro, fevereiro e março, você pode fazer o seguinte -

  • Clique no campo Vendedor na lista Campos da Tabela Dinâmica e arraste-o para a área ROWS.

  • Clique no campo Mês na lista Campos da Tabela Dinâmica e arraste-o também para a área LINHAS.

  • Clique em Order Amount e arraste-o para a área ∑ VALUES.

Sua tabela dinâmica está pronta. Você pode alterar o layout da Tabela Dinâmica apenas arrastando os campos pelas áreas. Você pode selecionar / desmarcar campos na lista Campos da tabela dinâmica para escolher os dados que deseja exibir.

Filtrando dados na tabela dinâmica

Se você precisar se concentrar em um subconjunto dos dados da tabela dinâmica, poderá filtrar os dados na tabela dinâmica com base em um subconjunto dos valores de um ou mais campos. Por exemplo, no exemplo acima, você pode filtrar os dados com base no campo Intervalo para que possa exibir dados apenas para a (s) região (ões) selecionada (s).

Existem várias maneiras de filtrar dados em uma Tabela Dinâmica -

  • Filtrando usando Filtros de Relatório.
  • Filtrando usando Slicers.
  • Filtrando dados manualmente.
  • Filtrando usando Filtros de rótulo.
  • Filtrando usando Filtros de Valor.
  • Filtrando usando Filtros de Data.
  • Filtrando usando o filtro Top 10.
  • Filtrando usando Timeline.

Você conhecerá o uso dos Filtros de relatório nesta seção e dos Segmentadores de Dados na próxima seção. Para outras opções de filtragem, consulte o tutorial de tabelas dinâmicas do Excel.

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 o campo Região para a área FILTROS.
  • Arraste o campo Vendedor para a área ROWS.
  • Arraste o campo Mês para a área COLUNAS.
  • Arraste o campo Quantidade do pedido para a área ∑ VALORES.

O Filtro com o rótulo de 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).

Como você pode observar,

  • 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 em termos de vendedor aparece na coluna Total geral.

    • A soma do valor do pedido no mês aparece na linha Total geral.

  • Clique na seta no Filtro de região.

Aparece uma lista suspensa com os valores do campo Região.

  • Marque a caixa Selecionar vários itens. Caixas de seleção aparecerão para todos os valores. Por padrão, todas as caixas estão marcadas.

  • Desmarque a caixa (Tudo). Todas as caixas ficarão desmarcadas.

  • Marque as caixas - Sul e Oeste.

  • Clique no botão OK. Os dados relativos às regiões Sul e Oeste serão apenas resumidos.

Como você pode observar, na célula ao lado do Filtro de Região - (Vários Itens) é exibido, indicando que você selecionou mais de um valor. Mas quantos valores e / ou quais valores não são conhecidos no relatório que é exibido. Nesse caso, usar Segmentações de Dados é a melhor opção de filtragem.

Usando Slicers na Tabela Dinâmica

Filtrar usando Slicers tem muitas vantagens -

  • Você pode ter vários Filtros selecionando os campos para os Segmentadores de Dados.

  • Você pode visualizar os campos nos quais o Filtro é aplicado (um Slicer por campo).

  • Um Slicer terá botões que indicam os valores do campo que ele representa. Você pode clicar nos botões do Slicer para selecionar / desmarcar os valores no campo.

  • Você pode visualizar quais valores de um campo são usados ​​no Filtro (os botões selecionados são destacados no Slicer).

  • Você pode usar um Slicer comum para várias Tabelas Dinâmicas e / ou Gráficos Dinâmicos.

  • Você pode ocultar / exibir um Slicer.

Para entender o uso de Slicers, considere a seguinte Tabela Dinâmica.

Suponha que você queira filtrar esta Tabela Dinâmica com base nos campos - Região e Mês.

  • Clique na guia ANALISAR em FERRAMENTAS PIVOTÁVEIS na faixa de opções.
  • Clique em Insert Slicer no grupo Filter.

A caixa de diálogo Inserir Slicers é exibida. Ele contém todos os campos de seus dados.

  • Marque as caixas Região e Mês.

  • Clique no botão 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.

Como você pode observar, cada Slicer tem todos os valores do campo que representa e os valores são exibidos como botões. Por padrão, todos os valores de um campo são selecionados e, portanto, todos os botões são destacados.

Suponha que você queira exibir a Tabela Dinâmica apenas para as regiões Sul e Oeste e para os meses de fevereiro e março.

  • Clique em South no Region Slicer. Apenas o Sul será destacado no Slicer - Região.

  • Mantenha a tecla Ctrl pressionada e clique em West no Region Slicer.

  • Clique em Fevereiro no Mês Slicer.

  • Mantenha a tecla Ctrl pressionada e clique em March no Month Slicer. Os valores selecionados nos Slicers são destacados. A tabela dinâmica será resumida para os valores selecionados.

Para adicionar / remover valores de um campo do filtro, mantenha a tecla Ctrl pressionada e clique nos botões no respectivo Slicer.