➤Tabela dinâmica do Excel: exemplo, criar, atualizar ... 🕹

Você tem grandes listas de dados e deseja resumi-los em um relatório? Ou analisá-los de acordo com diferentes pontos de vista e critérios de pesquisa? O Excel contém uma ferramenta especialmente dedicada a essas tarefas: a tabela dinâmica!

Ao trabalhar com grandes quantidades de dados, é comum querer sintetizá-los, seja para obter uma visão global e rapidamente compreensível de um conjunto de valores ou, pelo contrário, para estudar um subconjunto de forma mais precisa, atendendo a critérios específicos.

É claro que você pode reservar um tempo para classificar, filtrar e, em seguida, agrupar manualmente seus dados valiosos em uma nova tabela que atenda às suas necessidades. No entanto, cada vez que você adiciona ou remove dados de sua lista inicial, você corre o risco de sua tabela de destino "esquecê-los" e terá que verificar regularmente suas fórmulas de cálculo para evitar isso. Além disso, se você deseja agrupar e resumir seus dados de acordo com diferentes critérios, então você terá que modificar manualmente a estrutura de sua tabela de destino, ou mesmo criar várias tabelas de resumo se quiser manter diferentes pontos de vista sobre seus dados. Essas manipulações logo se revelarão tediosas e uma fonte de erros.

Felizmente, a ferramenta de tabela dinâmica do Excel permite que você realize todas essas tarefas com apenas alguns cliques e sem digitar nenhuma fórmula. A fim de apresentar a você os diferentes conceitos necessários para o uso de tabelas dinâmicas, esta ficha prática é acompanhada porum exemplo de pasta de trabalho, pode ser baixado gratuitamente aqui. Todas as operações descritas neste guia são baseadas neste arquivo: portanto, recomendamos fortemente que você o use em paralelo com a sua leitura.

Baixe um exemplo de tabela dinâmica para Excel

Esta planilha foi produzida com a versão Office 2019 do Excel. No entanto, todas as funções apresentadas estão disponíveis de forma idêntica nas versões Office 2016 e Microsoft 365 (ex-Office 365) do Excel, exceto para a criação de estilos de tabela dinâmica personalizados que não são oferecidos na versão web da planilha da Microsoft.

Por que usar uma tabela dinâmica?

Você está trabalhando em uma grande lista de dados, por exemplo, um diário de vendas, e deseja saber rapidamente a soma dos elementos dessa lista agrupando-os simultaneamente de acordo com vários critérios, por exemplo, por categoria de produto e mês de venda. Nesse caso, o uso de uma tabela dinâmica permitirá que você obtenha esse resultado rapidamente, escolha como os dados de resumo serão organizados e altere instantaneamente esse layout sempre que precisar. O diagrama abaixo ilustra como uma Tabela Dinâmica sintetiza dados e os organiza visualmente.

Tabela dinâmica do Excel: exemplo, criar, atualizar ...

Falamos de “crosstab” porque as categorias usadas para agrupar os dados podem ser divididas em linhas e colunas. No exemplo de uma tabela dinâmica no canto superior direito da imagem, a célula amarela na "intersecção" dos campos "Janeiro" e "Categoria 1" retorna a soma de todos os itens na lista de dados inicial que continha o " Valores de janeiro "e" Categoria 1 "nas colunas" Mês "e" Categoria ". Nesse caso, um único item atende a ambos os critérios, mas a lista de dados pode conter milhares de itens que serão processados automaticamente pela Tabela Dinâmica, sem a necessidade de classificar ou filtrar os dados anteriormente.

Também falamos de tabela "dinâmica" porque, por um lado, seu layout pode ser alterado instantaneamente por meio de simples operações com o mouse ou teclado e, por outro lado, qualquer adição, modificação ou exclusão de dados na lista inicial. é automaticamente considerado no resultado dos agrupamentos.

Como criar uma tabela dinâmica com o Excel?

Antes de gerar uma Tabela Dinâmica, você deve garantir que os dados em sua lista inicial atendam aos seguintes pré-requisitos:

Os dados na mesma coluna devem ser todos do mesmo tipo (número, data, texto, etc.).
A primeira linha da lista de dados deve conter os cabeçalhos das colunas.
Sua lista não deve conter células mescladas.
Sua lista não deve conter células vazias (o Excel é capaz de reconhecer e tratar células vazias, se houver, mas é melhor evitá-las o máximo possível).

Depois que essas condições forem atendidas, você pode começar a criar sua Tabela Dinâmica.

Clique em algum lugar da lista de dados e, a seguir, na guia Inclusão na faixa de opções, clique PivotTable.

Na caixa de diálogo Criar Tabela Dinâmica, verifique se o campo Mesa / Praia contém as referências da lista de dados. Por padrão, se você clicou em algum lugar da lista corretamente e ela não contém células em branco ou células mescladas, o Excel determinou corretamente seu tamanho. Se não, clique ícone de seta para cima à direita do campo Mesa / Praia e selecione manualmente toda a lista de dados.
Na seção Escolha um local para o relatório de tabela dinâmica, verifique se o Nova planilha está checado. Você pode, é claro, marcar a caixa Planilha existente e selecionar manualmente um local na mesma planilha que a lista de dados, mas a legibilidade do todo pode ser menos boa.
Valide a criação da tabela dinâmica clicando no botão OK da caixa de diálogo Criar Tabela Dinâmica.

Uma nova planilha é inserida pelo Excel na pasta de trabalho e um painel denominado Campos da Tabela Dinâmica aparece à direita da tela.

O painel Campos da tabela dinâmica consiste em cinco quadros. O primeiro mostra a lista de campos de dados disponíveis, que corresponde aos cabeçalhos das colunas da lista de dados inicial. Os outros quatro quadros representam o zonas da tabela dinâmica: é movendo o campos de dados disponíveis no diferente zonas que você criará o layout de tabela dinâmica.
Na lista de campos de dados disponíveis, marque as caixas à esquerda das palavras Região, Produto et De vendas. Uma Tabela Dinâmica apareceu em sua planilha, e se você olhar para os quatro quadros inferiores, verá que as palavras Região et Produto foram adicionados na área linhas e que as palavras Soma das Vendas apareceu na área valores.

Neste ponto, você criou uma tabela dinâmica que agrupa seus dados por região e depois por nome de produto e tem um subtotal para cada categoria, bem como um total geral na parte inferior da tabela. Você também descobrirá que as categorias na Tabela Dinâmica são organizadas na mesma ordem que os campos de dados no quadro. linhas no painel Campos da tabela dinâmica disponíveis. Lembre-se deste princípio, ele é o coração do sistema de organização de uma tabela dinâmica.

Como parte linhas no painel Campos da Tabela Dinâmica, clique no seta cinza à direita do nome Região e, no menu contextual que aparece, clique em descerou clique com o botão esquerdo e mantenha pressionado o nome Região e trazê-lo para baixo com o nome Produto antes de soltar o botão do mouse.

Como parte linhas, o nome Produto está agora na primeira posição e o nome Região em segundo. No lado da Tabela Dinâmica, você descobrirá que a ordem dos agrupamentos e subtotais de dados também foi invertida.

Essa manipulação simples oferece uma visão geral do poder e da flexibilidade de uma mesa dinâmica. Em segundos, você tem uma perspectiva totalmente nova sobre seus dados sem precisar classificá-los ou reorganizá-los na lista original.

No painel Campos da tabela dinâmica disponíveis à direita da tela, no quadro superior, clique com o botão direito do mouse no nome Mês em seguida, selecione Adicionar aos rótulos da colunaou clique com o botão esquerdo e mantenha pressionado o nome Mês e mova-o para a área Colunas antes de soltar o botão do mouse.

Na planilha, a tabela dinâmica foi expandida para três colunas adicionais e agora mostra, além dos subtotais por nome de produto e região, subtotais mensais para cada uma dessas categorias, bem como um total geral à direita da tabela.

Neste ponto, você criou uma tabela dinâmica completa: a partir de uma lista de dados organizados em linhas, você obteve um relatório resumido que agrupa seus dados em categorias, divide-os em linhas e colunas e os exibe. Tente alterar a distribuição de categorias entre linhas e colunas para entender como o Excel altera a Tabela Dinâmica.

No painel Campos da Tabela Dinâmica Disponíveis, use o mouse para passar o nome Mês da área Colunas para a área linhas, na última posição após os nomes Produto et Região, então passe o nome Produto da área linhas para a área Colunas e observe as mudanças que ocorrem em cada etapa da Tabela Dinâmica.

Como formatar uma tabela dinâmica com o Excel?

Para tornar a sua Tabela Dinâmica mais fácil e agradável de ler, especialmente quando ela possui um grande número de categorias e dados, o Excel oferece (muitíssimas) opções de formatação. Por exemplo, você pode escolher um layout diferente para categorias, formatos de número personalizados para os dados da área de valores, cores para cabeçalhos e bordas e muito mais.

Como mudar o formato numérico dos valores?

Você notará que os dados na área valores da Tabela Dinâmica aparecem como números com duas casas decimais, sem um separador de milhares, o que não é muito legível para valores monetários. Você pode optar por exibir esses dados em qualquer formato de número disponível no Excel.

No painel Campos da Tabela Dinâmica Disponíveis, no valores, clique no nome Soma do Ventoes em seguida Parâmetros de campos de valor.

Na caixa de diálogo Configurações para os campos de valor que é aberta, clique no botão Formato numérico.

A caixa de diálogo Formato de Célula abre. Nele, você pode selecionar qualquer formato de célula existente no Excel ou criar um formato personalizado clicando na categoria personnalisé. Neste caso, clique na categoria Monetário em seguida, valide sua escolha clicando no botão OK na parte inferior direita da caixa de diálogo.

De volta à caixa de diálogo Parâmetros dos Campos de Valor, clique no botão OK no canto inferior direito para aplicar sua alteração à Tabela Dinâmica. Dados de zona valores agora são exibidos como valores monetários, com um separador de milhares, duas casas decimais e o símbolo €.

Como mudar a largura das colunas?

Ao criar uma tabela dinâmica, a largura das colunas adapta-se automaticamente ao conteúdo das células, o que pode não dar o resultado pretendido se, por exemplo, pretende que todas as colunas tenham a mesma largura. Felizmente, é muito fácil alterar manualmente a largura das colunas e mantê-la durante as atualizações da tabela dinâmica.

Para dar a mesma largura a várias colunas, selecione aquelas que deseja modificar clicando em seu rótulo (as letras A, B, C, D, etc., localizado acima da primeira linha da planilha). Por exemplo, clique com o botão esquerdo e segure no rótulo da coluna B, mova o cursor para o rótulo da coluna E em seguida, solte o botão esquerdo do mouse. Todas as colunas de B à E são então selecionados.

Em seguida, mova o mouse sobre a linha separadora entre os rótulos das colunas E et F até que o cursor mude para uma seta preta dupla. Em seguida, clique com o botão esquerdo e segure e mova o cursor para a direita para aumentar o tamanho das colunas ou para a esquerda para reduzi-lo. Ao soltar o botão esquerdo do mouse, todas as colunas selecionadas anteriormente assumem a mesma largura.

Método alternativo: selecione as colunas cuja largura você deseja alterar clicando em seu rótulo (como explicado acima), clique com o botão direito em qualquer lugar na área de seleção e escolha Largura da coluna no menu de contexto. Uma caixa de diálogo Largura da coluna é aberta, na qual você pode inserir diretamente um número para definir a largura das colunas selecionadas. Este método é mais preciso, mas menos intuitivo do que o anterior.

Depois de atribuir as larguras desejadas às suas colunas, é necessário percorrer as opções da tabela dinâmica para que essas configurações sejam mantidas ao atualizar a tabela. Na verdade, por padrão, o Excel ajusta automaticamente a largura das colunas ao seu conteúdo ao atualizar a Tabela Dinâmica. Felizmente, essa opção pode ser alterada com apenas alguns cliques.

Na planilha, clique em qualquer lugar dentro da Tabela Dinâmica. Isso abre a guia Análise de Tabela Dinâmica na fita. Clique nele e depois no menu Opções de tabela dinâmica e finalmente no botão Opções.

Você também pode clicar com o botão direito em qualquer célula da Tabela Dinâmica e, no menu de contexto, clicar Opções de tabela dinâmica.

Com qualquer um dos métodos descritos acima, a caixa de diálogo Opções de tabela dinâmica aparece na tela. Ele tem várias guias e abre por padrão com o título Layout e formatação. Isso é bom, é o que nos interessa neste caso. Encontre a caixa Ajustar automaticamente a largura da coluna ao atualizar e desmarque-o. Aproveite para verificar se a caixa Manter a formatação da célula ao atualizar, logo acima, está marcada. Dessa forma, você pode ter certeza de que toda a formatação aplicada manualmente será bem preservada ao atualizar a Tabela Dinâmica.

Como alterar o estilo e o layout da tabela dinâmica?

Existem muitas possibilidades no Excel para alterar a maneira como a Tabela Dinâmica organiza visualmente seus dados, como mostrar totais gerais para linhas e colunas, onde os subtotais estão localizados ou como as categorias de dados são aninhadas. O software de planilha da Microsoft também permite que você escolha em uma lista de estilos de tabela prontos (e muito elegantes na maior parte) que podem ser aplicados instantaneamente à sua Tabela Dinâmica, evitando o incômodo de formatação. Para permitir que você se concentre no conteúdo. E, se nenhum dos estilos predefinidos atender às suas expectativas, é perfeitamente possível criar o seu próprio, que estará disponível para todas as suas futuras tabelas dinâmicas. Observe que a criação de estilos personalizados não está disponível na versão web do Excel. Por outro lado, se você criou um estilo de tabela personalizado em um arquivo em uma versão desktop do Excel e, em seguida, abriu esse arquivo na versão da web, seu estilo personalizado estará presente e utilizável, mas não editável.

Na planilha, clique em qualquer célula da tabela dinâmica para abrir a guia criação na faixa de opções e, em seguida, clique na faixa. Essa guia tem três seções: Layout, Opções de estilo de tabela dinâmica e Estilos de tabela dinâmica.

Na seção Layout, o botão Subtotais permite que você escolha entre três opções de exibição, cujo nome é bastante autoexplicativo: Não exibir subtotais, Mostrar todos os subtotais na parte inferior do grupo et Mostrar todos os subtotais no topo do grupo (esta é a opção habilitada por padrão). Experimente cada um deles para ver como a tabela dinâmica muda na aparência e, em seguida, mude a exibição de volta para os subtotais na parte inferior do grupo antes de prosseguir. Uma opção final chamada Incluir itens filtrados nos totais aparece no final da lista, mas está inativo no momento porque você não aplicou nenhum filtro à sua Tabela Dinâmica. Como o próprio nome sugere, permite incluir nos subtotais o valor dos dados ocultos por um filtro, o que pode levar a erros na leitura e interpretação dos resultados de uma Tabela Dinâmica. Para ser usado com cautela, portanto.

Ainda na seção Layout, o botão Total geral oferece quatro possibilidades de exibição: Desativado para linhas e colunas, Habilitado para linhas e colunas, Habilitado apenas para linhas et Ativado apenas para colunas. Mais uma vez, tente algumas vezes e depois volte para a opção Ativado para linhas e colunas antes de prosseguir.

O próximo botão na seção Layout é chamado Layout do relatório. Ele permite que você escolha como o Excel organiza as categorias de dados que você colocou na caixa linhas da tabela dinâmica.

A primeira opção, Mostrar em formato compactado, é o usado por padrão e, portanto, o que você vê desde o início. Neste modo de exibição, as categorias de dados da zona linhas são agrupados em uma única coluna e exibidos como uma estrutura de árvore, com um recuo à direita aplicado a cada subcategoria e um subtotal para cada categoria que pode ser exibido na parte superior ou inferior do grupo (consulte a seção anterior desta ficha prática). Esta disposição oferece a vantagem de otimizar o espaço ocupado pelas categorias em largura e, portanto, deixar mais espaço para a exibição dos valores. Sua desvantagem é que ele agrupa os filtros aplicáveis às diferentes categorias em um único botão (presente na célula A4 em nosso exemplo), o que pode tornar a filtragem em várias categorias menos legível. Este aspecto será desenvolvido posteriormente nesta ficha prática.

A segunda opção disponível, Ver na vista de esboço, é bastante semelhante ao anterior: as categorias de dados da zona linhas são sempre exibidos como uma estrutura de árvore e um subtotal para cada categoria pode ser exibido na parte superior ou inferior do grupo. No entanto, as categorias da área linhas desta vez, estão espalhados por várias colunas, uma coluna por categoria para ser mais preciso. Este modo, portanto, usa mais espaço em largura para a exibição de categorias e pode dar uma impressão de "bagunça" com as zonas vazias em cada grupo. No entanto, como cada categoria tem sua própria coluna, este modo oferece a vantagem de atribuir um botão de filtro a cada categoria, o que torna a filtragem por vários critérios muito legível.

A terceira e última opção é chamada Ver em formato tabular. Desta vez, as categorias da área linhas não são mais exibidos na forma de uma estrutura em árvore, mas na forma de uma tabela clássica e, o que é crucial, os subtotais devem ser encontrados na parte inferior de cada grupo. Como na exibição no modo Plano, cada categoria da zona linhas é distribuído em uma coluna individual e, portanto, oferece seu próprio botão de filtro.

Finalmente, o botão Layout do relatório oferece duas opções adicionais: Repita todos os rótulos de elemento ou Não repita os rótulos dos itens. Na verdade, essas duas opções controlam um único parâmetro, permitindo que seja habilitado ou desabilitado. Por padrão, a repetição de rótulos de itens está desativada, e é por isso que você vê espaços vazios em cada grupo de categoria. Se você clicar na opção Repita todos os rótulos de elemento, então o nome de cada categoria será repetido em sua coluna até o próximo grupo. Isso pode ser útil se uma categoria tiver muitos itens e seu comprimento exceder a altura de exibição de sua mesa. Observe que esta opção não funciona em exibir em forma compactada.

A seção Layout possui um menu final chamado Linhas vazias que contém duas opções: Insira uma quebra de linha após cada elemento ou Remova a quebra de linha após cada item. A primeira opção insere uma linha em branco após cada grupo de categorias, o que pode ser útil para arejar sua tabela dinâmica, especialmente se você optou por repetindo rótulos de itens. A segunda opção simplesmente remove as linhas vazias inseridas pela primeira.

A próxima seção da guia criação na faixa de opções é denominado Opções de estilo de tabela dinâmica e contém quatro caixas de seleção, cada uma permitindo que você habilite uma opção de formatação específica. Por padrão, as opções Cabeçalhos de linha et Cabeçalhos de coluna são verificados e as opções Tiras de linhas et Colunas de banda estão desmarcados.

Se você desmarcar a caixa Cabeçalhos de linha, a formatação da primeira linha de cada grupo de categorias desaparecerá.

Se você desmarcar a caixa Cabeçalhos de coluna, é a formatação dos cabeçalhos das colunas da tabela dinâmica que então desaparece.

As casas Tiras de linhas et Colunas de banda produzem efeitos diferentes dependendo do estilo de tabela dinâmica aplicado. Você verá como escolher um estilo de tabela posteriormente neste tutorial, então fique à vontade para ativar e desativar essas opções após uma mudança de estilo para ver como elas afetam a formatação da tabela. Em geral, as opções Tiras de linhas et Colunas de banda torna mais fácil distinguir entre linhas e colunas, mostrando bordas separadoras ou aplicando formatação alternativa de linha ou coluna. Tente ativar e desativar essas opções para ver o resultado no estilo de tabela atual.

A última parte da seção Layout da guia criação tem direito Estilos de tabela dinâmica e contém os famosos estilos de tabela prontos que você pode aplicar instantaneamente à sua Tabela Dinâmica.

A seção Estilos de tabela dinâmica oferece um primeiro exemplo dos estilos de tabela disponíveis, mas existem muitos mais: clique no setas à direita da amostra para rolar pelos estilos disponíveis ou no seta no topo com uma linha para exibir uma lista suspensa de todos os estilos existentes. Ao passar o mouse sobre os estilos de tabela, sem precisar clicar neles, você verá que o Excel aplica temporariamente a formatação à Tabela Dinâmica, o que é muito útil para comparar rapidamente as várias possibilidades disponíveis.

Para aplicar permanentemente o estilo de tabela de sua preferência, basta clicar nele na lista suspensa, a formatação correspondente será aplicada a toda a Tabela Dinâmica. É claro que você pode alterar o estilo da mesa com a freqüência que desejar, simplesmente repetindo a etapa anterior.

Observe que os estilos oferecidos dependem do esquema de cores usado em sua pasta de trabalho do Excel e, se nenhuma das cores da tabela oferecida for adequada para você, você pode obter novas alterando o esquema de cores na guia Layout faixa de opções, seção Temas, menu Cores.

Se, apesar de tudo, não encontrar o que procura na multiplicidade de estilos de mesa oferecidos, é possível criar estilos totalmente personalizados. Observe que, conforme explicado anteriormente, esta função não está disponível na versão Web do Excel.

Na aba criação, na seção Estilos de tabela dinâmica, exiba a lista de estilos disponíveis clicando no seta no topo com uma linha então, abaixo da lista, clique em Novo estilo de tabela dinâmica. Uma caixa de diálogo com o mesmo nome é aberta.

No campo Nome completo, você pode dar ao seu estilo de tabela dinâmica um nome personalizado. Então as coisas ficam um pouco mais complicadas. Cada entrada na lista Itens da tabela representa uma área da tabela dinâmica que pode receber formatação especial. Você deve, portanto, configurar cada elemento individualmente até obter o resultado desejado, o que pode levar mais ou menos tempo dependendo da complexidade do estilo de tabela que deseja alcançar.

Para alterar a formatação de uma área da tabela, clique em seu nome na lista de elementos da Tabela, por exemplo em Linhas de cabeçalho, então o botão Formato sob esta mesma lista. Uma caixa de diálogo Formatar células é aberta. Ele contém três guias, Police, fronteira et O preenchimento, que permitem definir várias configurações de formatação. Eles funcionam exatamente da mesma forma que na formatação de células diretamente em uma planilha. Experimente e aplique uma formatação diferente e clique no botão OK na parte inferior direita da caixa de diálogo Formatar células. Repita a operação várias vezes com outras entradas na lista de itens da tabela. Quando terminar, observe a seção Visualização à direita da caixa de diálogo Novo estilo de tabela dinâmica. Como o nome sugere, ele fornece uma visualização da formatação que será aplicada pelo seu estilo de tabela personalizado.

Se você está satisfeito com o resultado, clique no botão OK na parte inferior direita da caixa de diálogo Novo estilo de tabela dinâmica para salvar seu estilo personalizado. Agora estará disponível na lista de Estilos de tabela dinâmica aba criação fita. É claro que você pode modificar, duplicar ou excluí-lo com um simples clique com o botão direito sobre ele.

O número de elementos da tabela que você pode personalizar é muito grande e provavelmente levará algum tempo para entender completamente a qual área da Tabela Dinâmica cada um corresponde. Não hesite em salvar um estilo personalizado que você desenvolverá com o tempo, testando-o nas várias tabelas dinâmicas com as quais você trabalha. O potencial criativo do Excel é imenso e (quase) limitado apenas por sua imaginação e experiência.

Como classificar e filtrar uma tabela dinâmica no Excel?

Como qualquer série de dados organizados como uma tabela no Excel, uma tabela dinâmica pode ser classificada e filtrada automaticamente usando as ferramentas fornecidas para esse fim. Em termos de classificação, encontramos as funções clássicas de classificação ascendente, descendente e personalizada. No lado da filtragem, autofiltros e segmentos estão presentes, bem como uma área de filtro específica para tabelas dinâmicas, que pode ser usada para maior legibilidade.

Como classificar dados em uma tabela dinâmica?

Primeira solução: use os filtros automáticos. Clique no botão branco contendo um triângulo preto que está à direita da célula do cabeçalho da coluna pela qual você deseja classificar seus dados, por exemplo coluna Região. Um menu de contexto é exibido. As duas primeiras opções permitem classificar seus dados em ordem crescente ou decrescente (de A a Z ou de Z a A no caso de uma coluna contendo texto).

Você também pode escolher usar Opções adicionais de classificação. Essas opções adicionais serão diferentes dependendo do tipo de dados da coluna (texto, número, data, hora). Em todos os casos, exceto para os números, você pode escolher classificar seus dados manualmente arrastando e soltando com o mouse.

Segunda solução: clique com o botão direito do mouse em uma célula da Tabela Dinâmica que contém os dados, não importa se é uma categoria ou um valor, e no menu de contexto que aparece, clique em Trier em seguida, escolha sua opção de classificação como antes.

Como filtrar dados em uma tabela dinâmica?

Assim como na classificação, o método mais intuitivo e direto é usar filtros automáticos. Clique no botão branco contendo um triângulo preto que está à direita da célula do cabeçalho da coluna pela qual você deseja filtrar seus dados, por exemplo coluna Região. Um menu de contexto é exibido.

Na segunda metade deste menu, uma lista de caixas de seleção permite selecionar os dados a mostrar ou ocultar, simplesmente marcando ou desmarcando a caixa correspondente. Por exemplo, desmarque as caixas Ile-de-Spain et Nouvelle-Aquitaine em seguida, valide clicando no botão OK na parte inferior do menu. A Tabela Dinâmica agora mostra apenas dados da região Auvergne-Rhône-Alpes.

Você pode combinar os critérios de filtro em várias categorias diferentes. Por exemplo, abra o filtro automático da coluna Mês e na lista de caixas de seleção, desmarque aquelas correspondentes aos meses de janeiro et Fevereiro, em seguida, valide esta filtragem clicando no botão OK. A Tabela Dinâmica agora exibe dados da região Auvergne-Rhône-Alpes do mês de março somente.

Para remover os critérios de filtro aplicados a uma coluna, abra o filtro automático para a coluna em questão e clique em Filtro limpo. Repita isso para cada coluna da qual deseja remover a filtragem.

Segundo método: use a área de filtros específica da tabela dinâmica. Clique em algum lugar da Tabela Dinâmica para que o painel Campos da Tabela Dinâmica apareça no lado direito da janela. Escolha uma das categorias presentes nas zonas linhas ou Colunas, clique nele e selecione Vá para a área de filtro do relatório no menu de contexto. Você também pode mover a categoria escolhida de sua zona atual para a zona Filtro arrastando e soltando com o mouse.

Para nosso exemplo, primeiro arraste a categoria Região da área linhas para a área Filtro, então a categoria Produto da área Colunas para a área Filtro.

Você verá que a estrutura da Tabela Dinâmica mudou: os subtotais por região e por produto desapareceram da tabela e duas linhas apareceram acima que correspondem aos filtros da Tabela Dinâmica. Cada um deles contém um filtro automático que funciona como os vistos anteriormente, mas de uma forma um pouco simplificada.

Clique no filtro automático na linha intitulada Região, um menu de contexto será aberto no qual você pode selecionar uma das entradas desta categoria. Por padrão, você só pode selecionar uma entrada da lista. Marcando a caixa Selecione vários itens na parte inferior da lista, você pode fazer uma seleção múltipla. No entanto, este tipo de filtragem com vários critérios dentro da mesma categoria não é recomendado neste modo de exibição, como ilustraremos a seguir. Existe uma ferramenta mais adequada para filtrar com múltiplos critérios (segmentos) que veremos mais adiante nesta ficha prática. Para nosso exemplo, abra o filtro automático da linha Região, selecione o elemento Ile-de-Spain e valide clicando no botão OK, em seguida, abra o filtro automático da linha Produto, selecione o elemento Produto A e valide clicando em OK.

A tabela dinâmica agora exibe apenas valores para categorias Ile-de-Spain et Produto A e os critérios de filtragem usados são claramente visíveis acima da faixa da tabela, o que facilita muito a leitura e evita erros de interpretação de dados ao realizar várias filtragens sucessivas. Se você tivesse marcado a opção Selecione vários itens na etapa anterior, e tivesse realizado uma filtragem com vários critérios na mesma categoria, então a linha de filtro teria exibido "Vários elementos" em vez do nome preciso do critério de filtragem. Isso exigiria que você reabrisse a lista de filtros periodicamente para lembrar quais critérios são aplicados.

Uso da área Filtres da tabela dinâmica, em vez dos filtros automáticos clássicos presentes em cada coluna, é, portanto, particularmente útil para trabalhar em subconjuntos precisos de uma lista de dados que compreende muitas categorias.

Por fim, um terceiro método, que combinará as vantagens dos dois anteriores, nomeadamente a apresentação dos subtotais de todas as categorias da tabela dinâmica e a apresentação permanente dos critérios utilizados em caso de filtragem múltipla: os segmentos. Comece revertendo para a estrutura anterior da Tabela Dinâmica. No painel Campos da Tabela Dinâmica, traga de volta a categoria Região em primeira posição na área linhas e a categoria Produto na área Colunas.

Clique em qualquer célula da tabela dinâmica para abrir a guia Análise de Tabela Dinâmica na faixa de opções e clique nele. Na seção Filtro, clique no botão Inserir segmento.

Uma caixa de diálogo intitulada Inserir segmentos é aberta. Ele contém a lista de categorias de sua Tabela Dinâmica na forma de caixas de seleção. Para nosso exemplo, marque as caixas Região et Produto em seguida, valide sua seleção clicando no botão OK na parte inferior da caixa de diálogo.

Dois objetos apareceram na planilha, acima da tabela dinâmica. Estes são os famosos segmentos Excel, ferramentas de filtragem visual muito práticas e ergonômicas. Primeiro, usando o mouse, arraste e solte-os à direita da tabela dinâmica para não ocultá-la.

Os segmentos funcionam da mesma forma que os filtros automáticos, permitindo escolher quais categorias mostrar ou ocultar simplesmente clicando em seus nomes dentro da lista, mas com a diferença de tamanho dos segmentos que permanecerão visíveis. No segmento RegiãoClique em o ícone que representa uma lista de validação à direita do nome "Região" para ativar a seleção múltipla, clique nos itens Auvergne-Rhône-Alpes et Nouvelle-Aquitaine. No segmento Produto, ative a seleção múltipla como antes e clique nos elementos Produto B et Produto C. A tabela dinâmica se adapta instantaneamente às suas alterações e os critérios de filtro usados permanecem visíveis o tempo todo nos segmentos. Obviamente, você pode alterar seus filtros simplesmente clicando nos elementos correspondentes nos segmentos.

Para limpar os filtros aplicados usando segmentos, clique ícone de funil e cruz vermelha no cabeçalho do segmento relevante.

Para excluir completamente um segmento, clique com o botão direito sobre ele e selecione Deletar " ... ".

Como atualizar uma tabela dinâmica com o Excel?

Outra vantagem das tabelas dinâmicas é que podem ser atualizadas instantaneamente quando são feitas alterações na lista de dados inicial. Quer você tenha adicionado, editado ou excluído linhas, ou mesmo adicionado colunas que representam novas categorias, é possível atualizar a Tabela Dinâmica com apenas alguns cliques.

Faça algumas modificações na lista de dados inicial: substitua alguns valores por um zero e adicione uma linha no final da lista com novas entradas para as categorias Regiões et Mês, por exemplo "Occitanie" e "Avril".

Retorne à planilha que contém a Tabela Dinâmica e clique em qualquer célula da Tabela Dinâmica para abrir a guia Análise de Tabela Dinâmica na faixa de opções e, em seguida, clique na faixa. Na seção Dados, clique no botão Atualizar em seguida Atualize tudo. Você também pode clicar com o botão direito do mouse em uma célula da tabela dinâmica e escolher Atualizar.

A tabela dinâmica foi atualizada: os valores mostram claramente os montantes que você modificou na lista de dados inicial (os zeros). No entanto, a linha adicionada no final da lista não aparece porque está fora do intervalo de dados no qual a tabela dinâmica se baseia. Não se preocupe, é muito fácil alterar o intervalo de dados de origem e até mesmo configurá-lo para expandir automaticamente toda vez que você adicionar uma linha ou coluna.

Na aba Análise de Tabela Dinâmica, na seção Dados, clique no botão Mudar a fonte de dados. O Excel o leva de volta à planilha que contém a lista de dados original e abre uma caixa de diálogo chamada Editar fonte de dados da tabela dinâmica. Você pode inserir diretamente as referências da fonte de dados no campo Mesa / Praia usando o teclado ou selecione o intervalo na planilha usando o mouse. Valide a modificação do intervalo clicando no botão OK na parte inferior da caixa de diálogo.

Você retorna automaticamente para a página que contém a tabela dinâmica que, desta vez, mostra uma nova categoria chamada Occitanie e uma subcategoria chamada Abril.

Para evitar ter que alterar o intervalo de dados de origem manualmente cada vez que você adiciona uma linha ou coluna, a melhor maneira é converter o intervalo de dados para mesa estruturada. Para fazer isso, volte para a planilha que contém o intervalo de dados de origem e selecione-o por completo. Em seguida, clique na guia Inclusão fita e depois no botão Quadro.

A caixa de diálogo Criar Tabela é aberta. Verifique se as referências contidas no campo Onde estão os dados da sua mesa? cobrir todo o intervalo de dados de origem e que a caixa de seleção Minha tabela tem cabeçalhos está marcada e, em seguida, valide clicando no botão OK.

A lista de dados então muda de aparência. Acima de tudo, tornou-se uma tabela estruturada com várias vantagens: pode ser referenciada por um nome em vez de pelo seu endereço, e as adições de linhas e colunas serão automaticamente tidas em conta no seu âmbito. Antes de prosseguir, atribua um nome a esta tabela estruturada: fique na mesma página e clique na guia Desenho de mesa na faixa de opções, depois no campo Nome da tabela e dê a ele um nome significativo, por exemplo De vendas.

Volte para a planilha que contém a tabela dinâmica, clique em uma célula desta última, na guia Análise de Tabela Dinâmica fita e, finalmente, o botão Mudar a fonte de dados. Na caixa de diálogo Editar fonte de dados da tabela dinâmica, no Mesa / Praias, basta inserir o nome de sua tabela estruturada, De vendas neste caso, e valide pressionando o botão OK.

A tabela dinâmica agora está vinculada à tabela estruturada De vendas. Qualquer adição de linha ou coluna a esta tabela estruturada será levada em consideração ao atualizar a tabela dinâmica.

Volte para a folha que contém a tabela estruturada De vendas e vá para a última célula da tabela (D29). Pressione a tecla Classificação no seu teclado. Uma linha em branco é adicionada à tabela estruturada, com a mesma formatação das linhas anteriores.

Preencha esta linha com novos dados, por exemplo "Normandia", "Maio", "Produto C" e "4952.38". Em seguida, volte para a página que contém a tabela dinâmica, clique na guia Análise de Tabela Dinâmica fita, no botão Atualizar depois Atualize tudo. A tabela dinâmica é atualizada e mostra uma nova categoria chamada Normandia e uma subcategoria chamada Maio.

Agora você pode adicionar quantos itens quiser à sua lista de dados inicial, sem se preocupar se eles serão ignorados ao atualizar a Tabela Dinâmica.

As tabelas dinâmicas oferecem uma infinidade de outras funções mais ou menos sofisticadas, possibilitando a obtenção de resultados mais ou menos complexos. Por exemplo, eles podem exibir valores não como somas, mas como porcentagens ou diferenças de um valor base da lista de dados inicial e muito mais. No entanto, essas funções estão além do escopo deste guia prático para uma introdução ao uso de tabelas dinâmicas. Os conceitos apresentados nesta ficha dão-lhe, no entanto, os conhecimentos necessários para uma utilização das tabelas dinâmicas que responderão à maioria dos casos de figura que poderá encontrar.

Tabela dinâmica do Excel: exemplo, criar, atualizar ...