Tabelas Dinâmicas no Excel
Um módulo prático em 9 capítulos. Em vez de começar pelos menus, cada capítulo começa com perguntas reais de uma empresa — e mostra como a tabela dinâmica as responde em segundos.
Começar pelo capítulo 1 Descarregar os dados Livro com macros Teste finalO problema
A Frutas & Legumes de Portugal (empresa fictícia) exporta fruta e legumes para 16 países. Todas as encomendas de três anos estão numa única folha com 20 000 linhas. Na reunião de segunda-feira, a administração quer saber:
- Qual é o produto que mais faturamos? E em cada país?
- As vendas estão a crescer? Há meses fracos?
- Que vendedor tem mais encomendas na Europa, pelo canal Online?
- Quanto nos sobra depois de pagar o transporte?
Com filtros e fórmulas, cada pergunta demora muito tempo — e quando surge a pergunta seguinte, é preciso recomeçar. Com uma tabela dinâmica, cada resposta demora segundos. Tente responder a cada pergunta antes de ler a solução.
Capítulos
Tabelas Dinâmicas
Criar a primeira tabela dinâmica, ordenar, filtrar, mudar o cálculo e cruzar dois campos.
Nos últimos três anos, qual foi o produto que nos deu mais receita?CAPÍTULO 2
Agrupar Itens
Criar famílias de produtos que não existem nos dados e agrupar datas por anos e trimestres.
A direção quer comparar famílias de produtos (fruta tropical, fruta de clima temperado, legumes de raiz, legumes de folha e fruto). Essa coluna não existe — como fazer?CAPÍTULO 3
Tabela Dinâmica Multinível
Vários campos na mesma área: linhas em hierarquia, soma e percentagem lado a lado, vários filtros.
Dentro de cada região do mundo, quais são os países que mais compram?CAPÍTULO 4
Distribuição de Frequências
Contar quantas encomendas caem em cada escalão de valor e mostrar o resultado num gráfico.
Qual é o tamanho típico de uma encomenda: a maioria é pequena ou grande?CAPÍTULO 5
Gráficos Dinâmicos
Transformar uma tabela dinâmica num gráfico que se filtra e se atualiza com ela.
Preciso de um gráfico para a reunião: como evoluíram as vendas de cada região em cada ano?CAPÍTULO 6
Segmentação de Dados
Botões de filtro visuais que controlam várias tabelas dinâmicas ao mesmo tempo.
O diretor comercial quer um painel onde clica numa região e num canal e vê logo os produtos. Como?CAPÍTULO 7
Atualizar Tabela Dinâmica
Refletir alterações e novas linhas dos dados sem refazer os relatórios.
Corrigi um valor errado nos dados. Porque é que a tabela dinâmica não mudou?CAPÍTULO 8
Campo e Item Calculado
Criar novas medidas e novos itens com fórmulas dentro da tabela dinâmica.
Os produtos que vendem mais de 2,5 milhões de euros pagam uma taxa de 3%. Quanto pagamos por produto?CAPÍTULO 9
Função OBTERDADOSDIN
Ir buscar valores a uma tabela dinâmica com uma fórmula que não se perde quando a tabela muda.
Montei um quadro-resumo com =B7. Quando mudei um filtro, o valor passou a ser de outro produto. Porquê?
Livro com macros
Depois dos 9 capítulos, veja como automatizar as tabelas dinâmicas. Este livro usa os mesmos dados e tem um Painel com botões: cada botão responde a uma pergunta e cria a tabela dinâmica (com gráfico ou segmentações) numa folha nova. Há também uma tabela personalizada, em que escolhe os campos em células com listas, e uma resposta rápida com SOMA.SE.S.
- Ativar as macros: antes de abrir, clique com o botão direito no ficheiro › › marque Desbloquear › OK. Ao abrir, clique em Ativar Conteúdo.
- Ver o código: Alt+F11 (módulo
ModTabelasDinamicas). - Adaptar a outros dados: converta os dados numa Tabela (Ctrl+T), mude a constante
TABELA_DADOSe copie uma das macrosP1…P8, trocando os nomes dos campos:
Os dados
O conjunto de dados parte da tabela clássica dos tutoriais de tabelas dinâmicas (ID, Produto, Categoria, Valor, Data, País — 213 linhas) e foi alargado para 20 000 linhas e 20 colunas, com dados aleatórios mas realistas: sazonalidade dos produtos, crescimento anual, descontos por tipo de cliente, custos de transporte e encomendas canceladas. Todos os nomes de clientes e vendedores são fictícios.
Primeiras linhas (algumas colunas)
| ID Encomenda | Data Encomenda | Cliente | Produto | Categoria | País | Região | Canal | Quantidade | Valor | Estado |
|---|---|---|---|---|---|---|---|---|---|---|
| 100001 | 01/01/2023 | Cliente MA-002 | Brócolos | Legumes | Marrocos | África | Agente | 1 213 kg | 1 904,41 € | Entregue |
| 100002 | 01/01/2023 | Cliente DE-002 | Brócolos | Legumes | Alemanha | Europa | Online | 841 kg | 1 236,27 € | Entregue |
| 100003 | 01/01/2023 | Cliente ES-011 | Laranja | Fruta | Espanha | Europa | Online | 388 kg | 279,36 € | Entregue |
| 100004 | 01/01/2023 | Cliente CN-003 | Abacate | Fruta | China | Ásia | Venda Direta | 4 419 kg | 14 803,65 € | Entregue |
| 100005 | 01/01/2023 | Cliente CH-001 | Cenoura | Legumes | Suíça | Europa | Agente | 2 302 kg | 1 197,04 € | Entregue |
| 100006 | 01/01/2023 | Cliente NL-003 | Batata | Legumes | Países Baixos | Europa | Distribuidor | 492 kg | 231,24 € | Entregue |
Colunas
| Coluna | Significado |
|---|---|
| ID Encomenda | Número único da encomenda |
| Data Encomenda | Data em que o cliente fez a encomenda (2023–2025) |
| Data Entrega | Data de entrega (vazia nas encomendas canceladas) |
| Cliente | Código do cliente (fictício) |
| Tipo de Cliente | Cadeia de Supermercados, Grossista, Retalhista ou Restauração |
| Vendedor | Comercial responsável pela região |
| Produto | 15 produtos |
| Categoria | Fruta ou Legumes |
| Região de Origem | Região portuguesa onde o produto é produzido |
| País | País de destino (16 países) |
| Região | Região do mundo do país de destino |
| Canal | Venda Direta, Distribuidor, Online ou Agente |
| Transporte | Rodoviário, Marítimo ou Aéreo |
| Quantidade | Quilogramas encomendados |
| Preço Unitário | Preço por kg (€) |
| Valor | Quantidade × Preço Unitário (€) |
| Desconto | Desconto comercial (%) |
| Valor Líquido | Valor depois do desconto (€) |
| Custo Transporte | Custo do envio (€) |
| Estado | Entregue, Devolvida ou Cancelada |
Como usar os ficheiros
Cada capítulo tem um ficheiro Excel com as seguintes folhas:
- Instruções — as perguntas de partida e os passos a seguir;
- Colunas — o significado de cada coluna;
- Dados — as 20 000 encomendas (é aqui que se cria a tabela dinâmica);
- Resolução — as tabelas dinâmicas já feitas, para comparar com o seu resultado.