Tabela dinâmica no Excel: como criar, passo a passo

Uma tabela dinâmica agrupa as linhas de uma tabela por uma categoria, como Region, e totaliza um número, como Sales, para cada grupo, sem fórmulas. Para criar uma, clique em uma célula dos dados, vá em Inserir > Tabela Dinâmica, clique em OK e arraste Region para Linhas e Sales para Valores. A tabela abaixo não é uma tabela dinâmica: ela monta o mesmo resumo com fórmulas, para você ver os totais mudarem. No Excel em português, as funções dela são ÚNICO (UNIQUE em inglês) e SOMASE (SUMIF), e você também pode digitar as fórmulas assim na tabela, com ponto e vírgula: =SOMASE($A$2:$A$9;E2;$C$2:$C$9).

FórmulasRestaurar

F2fx

=SUMIF($A$2:$A$9,E2,$C$2:$C$9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Sales	% of total
2	North	Apple	120		North	455	49%
3	South	Pear	85		South	305	33%
4	North	Pear	240		East	170	18%
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMASE($A$2:$A$9;E2;$C$2:$C$9)

A ÚNICO lista cada região uma vez e a SOMASE totaliza: North 455, South 305 e East 170, o que dá 49%, 33% e 18% dos 930 no total. Mude C3 para 185 e o total de South e as três porcentagens acompanham na hora. Uma tabela dinâmica mostraria os mesmos números, mas só depois de você atualizar.

Como criar uma tabela dinâmica

Antes de começar, confira os dados de origem: uma linha de cabeçalho com um nome em cada coluna, um registro por linha, nenhuma linha ou coluna em branco no meio e nenhuma linha de subtotal.

1. Clique em qualquer célula dos dados.
2. Vá em Inserir > Tabela Dinâmica (em algumas versões, Inserir > Tabela Dinâmica > Da Tabela/Intervalo).
3. O Excel preenche o intervalo. Escolha Nova Planilha e clique em OK.
4. Uma tabela dinâmica em branco aparece, com o painel Campos da Tabela Dinâmica à direita, listando os cabeçalhos das suas colunas.
5. Arraste Region para a caixa Linhas e Sales para a caixa Valores. A tabela dinâmica mostra cada região uma vez com a Soma de Sales ao lado, e uma linha de Total Geral.
6. Para mudar o que aparece, arraste campos entre as caixas ou para fora do painel.

Se você não sabe por onde começar, Inserir > Tabelas Dinâmicas Recomendadas mostra alguns layouts prontos para os seus dados. No Mac o menu é o mesmo: Inserir > Tabela Dinâmica.

Linhas, Colunas, Valores e Filtros

O painel Campos da Tabela Dinâmica tem quatro caixas, e toda tabela dinâmica é uma escolha de qual coluna vai para qual caixa:

• Linhas: as categorias no lado esquerdo, uma linha por valor distinto (Region).
• Colunas: categorias no alto, uma coluna por valor distinto (Product).
• Valores: os números a calcular para cada combinação. Soma é o padrão para uma coluna numérica; Contagem, Média, Máx, Mín e outros ficam em Configurações do Campo de Valor.
• Filtros: um campo pelo qual a tabela dinâmica inteira é filtrada, mostrado como uma lista suspensa acima dela.

Com Region em Linhas, Product em Colunas e Sales em Valores, a tabela dinâmica dos dados acima fica assim (Excel em inglês):

Sum of Sales   Column Labels
Row Labels     Apple   Pear   Grand Total
East              60    110           170
North            215    240           455
South            150    155           305
Grand Total      425    505           930

A versão com fórmulas desse layout lista as regiões para baixo com a ÚNICO, os produtos na horizontal com TRANSPOR(ÚNICO()) e calcula todas as células da grade com uma SOMASES que recebe as duas listas como critérios:

FórmulasRestaurar

F2fx

=SUMIFS(C2:C9,A2:A9,E2:E4,B2:B9,F1:G1)

	A	B	C	D	E	F	G
1	Region	Product	Sales			Apple	Pear
2	North	Apple	120		North	215	240
3	South	Pear	85		South	150	155
4	North	Pear	240		East	60	110
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =SOMASES(C2:C9;A2:A9;E2:E4;B2:B9;F1:G1)

E2 despeja North, South e East para baixo, F1 despeja Apple e Pear na horizontal, e a SOMASES em F2 preenche a grade de 3 por 2 entre elas: um total para cada par de região e produto. Mude B5 de Apple para Pear e as duas células de East mudam. A ordem aqui é a ordem em que os valores aparecem primeiro; uma tabela dinâmica coloca os rótulos em ordem alfabética.

Contagem, média ou porcentagem em vez de soma

Na tabela dinâmica, clique no campo da caixa Valores e escolha Configurações do Campo de Valor. A guia Resumir Valores por alterna entre Soma, Contagem, Média, Máx e Mín; a guia Mostrar Valores como transforma os números em % do Total Geral, % do Total da Coluna, um total acumulado e outros. Cada fórmula tem uma equivalente direta:

FórmulasRestaurar

E2fx

=UNIQUE(A2:A9)

	A	B	C	D	E	F	G
1	Region	Product	Sales		Region	Orders	Average
2	North	Apple	120		North	3	151.7
3	South	Pear	85		South	3	101.7
4	North	Pear	240		East	2	85.0
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.No Excel em português: =ÚNICO(A2:A9)

North tem 3 pedidos com média de 151.7, South 3 com média de 101.7, East 2 com média de 85.0. A coluna de % do Total Geral está na primeira tabela desta página.

Filtrar o resumo por um produto

A caixa Filtros coloca uma lista suspensa acima da tabela dinâmica. A versão com fórmulas é uma célula com uma lista suspensa e a SOMASES, que acrescenta mais uma condição à SOMASE. Escolha um produto em F1:

FórmulasRestaurar

F1fx

	A	B	C	D	E	F
1	Region	Product	Sales		Product:	Apple▾ApplePear
2	North	Apple	120
3	South	Pear	85		Region	Sales
4	North	Pear	240		North	215
5	East	Apple	60		South	150
6	South	Apple	150		East	60
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Com Apple escolhido, North mostra 215, South 150 e East 60. Escolha Pear e eles mudam para 240, 155 e 110. Veja SOMASES para mais condições e a página de lista suspensa para saber como acrescentar a lista no Excel.

Atualizar uma tabela dinâmica

Uma tabela dinâmica guarda uma cópia dos dados de origem (o cache da tabela dinâmica) e não recalcula quando uma célula da origem muda. Depois de editar os dados:

• Clique com o botão direito em qualquer lugar da tabela dinâmica e escolha Atualizar, ou pressione Alt+F5 no Windows.
• Dados > Atualizar Tudo (Ctrl+Alt+F5) atualiza todas as tabelas dinâmicas da pasta de trabalho.
• Para atualizar toda vez que o arquivo abrir, clique com o botão direito na tabela dinâmica, escolha Opções da Tabela Dinâmica e, na guia Dados, marque Atualizar dados ao abrir o arquivo.

Linhas novas acrescentadas abaixo do intervalo de origem não entram, mesmo depois de atualizar. Mude o intervalo em Analisar Tabela Dinâmica > Alterar Fonte de Dados, ou, melhor, transforme a origem em uma tabela antes de criar a tabela dinâmica: selecione os dados e pressione Ctrl+T, ou use Inserir > Tabela. Uma tabela cresce quando você acrescenta linhas, e a tabela dinâmica pega essas linhas na próxima atualização.

Totalizar todas as regiões com uma fórmula

FórmulasRestaurar

F2fx

	A	B	C	D	E	F
1	Region	Product	Sales		Region	Sales
2	North	Apple	120		North
3	South	Pear	85		South
4	North	Pear	240		East
5	East	Apple	60
6	South	Apple	150
7	North	Apple	95
8	East	Pear	110
9	South	Pear	70

Clique em uma célula para ver a fórmula. Mude um número ou uma fórmula e a planilha recalcula.

Sua vez: Em F2, totalize as vendas de todas as regiões listadas em E2:E4 com uma fórmula só.

VerificarVer resposta

A resposta despeja 455, 305 e 170. Passar a lista inteira E2:E4 como critério da SOMASE retorna um total por região, então não há nada para arrastar. O Excel 2019 e anteriores não têm ÚNICO nem despejo: digite as regiões em E2:E4 e arraste =SOMASE($A$2:$A$9;E2;$C$2:$C$9) para baixo. Sem os $, os intervalos descem a cada linha e os totais saem errados.

AGRUPARPOR e PIVOTAR: uma tabela dinâmica em uma fórmula

O Excel do Microsoft 365 tem duas funções que montam um resumo inteiro a partir de uma fórmula e recalculam como qualquer fórmula, sem atualizar. Elas precisam de uma assinatura atual do Microsoft 365. Para os dados acima:

=GROUPBY(A2:A9,C2:C9,SUM)

East      170
North     455
South     305
Total     930

=PIVOTBY(A2:A9,B2:B9,C2:C9,SUM)

          Apple   Pear   Total
East         60    110     170
North       215    240     455
South       150    155     305
Total       425    505     930

No Excel em português: =AGRUPARPOR(A2:A9;C2:C9;SOMA) e =PIVOTAR(A2:A9;B2:B9;C2:C9;SOMA). A AGRUPARPOR (GROUPBY em inglês) recebe o campo das linhas, os valores e a função (SOMA, CONT.VALORES, MÉDIA, MÁXIMO, PERCENTOF). A PIVOTAR (PIVOTBY) acrescenta um campo de colunas entre eles. As duas ordenam os rótulos e acrescentam linhas de total, como uma tabela dinâmica.

Tabela dinâmica ou fórmulas: qual usar

Use uma tabela dinâmica para explorar dados e responder a uma pergunta uma vez; use fórmulas para um resumo que fica em um relatório, alimenta outras fórmulas e precisa estar sempre atual. Para conferir os números de uma tabela dinâmica, refaça uma célula dela com a SOMASES: se as duas não batem, a tabela dinâmica geralmente precisa ser atualizada ou o intervalo de origem está curto demais.