Tabela Dinâmica Multinível
Vários campos na mesma área: linhas em hierarquia, soma e percentagem lado a lado, vários filtros.
Perguntas de partida
Antes de ler, pense: como responderia a isto só com fórmulas, numa folha com 20 000 linhas?
XLS
Ficheiro deste capítulo03-tabela-dinamica-multinivel.xlsx · 4.1 MB · folhas Instruções, Colunas, Dados e Resolução
DescarregarCada área da lista de campos aceita mais do que um campo. É assim que se criam hierarquias (região › país), várias medidas lado a lado ou vários filtros ao mesmo tempo.
Pergunta 1
Dentro de cada região do mundo, quais são os países que mais compram?
- Arraste Região para Linhas e, depois, País também para Linhas (por baixo de Região).
- Arraste Valor para Valores.
- Ordene do maior para o menor (botão direito › › ) — o Excel ordena as regiões e os países dentro de cada região.
Campos da Tabela Dinâmica — arraste os campos entre as áreas:
Filtros
Colunas
LinhasRegiãoPaís
ValoresSoma de Valor
| Rótulos de Linha ⏷ | Soma de Valor |
|---|---|
| Europa | 24 595 725 € |
| Espanha | 7 420 081 € |
| França | 4 406 536 € |
| Alemanha | 4 352 909 € |
| Reino Unido | 2 657 555 € |
| Países Baixos | 2 201 357 € |
| Itália | 2 174 105 € |
| Suíça | 1 383 182 € |
| América do Norte | 5 521 166 € |
| Estados Unidos | 2 938 238 € |
| Canadá | 2 582 928 € |
| África | 3 646 321 € |
| Angola | 2 032 782 € |
| Moçambique | 668 866 € |
| Cabo Verde | 556 208 € |
| Marrocos | 388 465 € |
| América do Sul | 2 791 198 € |
| Brasil | 2 791 198 € |
| Ásia | 1 507 981 € |
| China | 1 226 628 € |
| Emirados Árabes Unidos | 281 353 € |
| Total Geral | 38 062 391 € |
Resposta: a Europa vale 24 595 725 € e dentro dela destaca-se Espanha (7 420 081 €). Na América do Norte lideram os Estados Unidos e em África, Angola.
Dica: a ordem dos campos importa. Troque País e Região na área Linhas e veja o resultado. Em › › cada campo fica na sua própria coluna.
Pergunta 2
Que percentagem das vendas totais representa cada região?
- Nova tabela: Região em Linhas e Valor em Valores.
- Arraste Valor outra vez para Valores. Fica com Soma de Valor e Soma de Valor2.
- Clique com o botão direito na segunda coluna › .
- Em Nome Personalizado escreva Percentagem; no separador Mostrar Valores Como escolha % do Total Geral.
Campos da Tabela Dinâmica — arraste os campos entre as áreas:
Filtros
Colunas
LinhasRegião
ValoresSoma de ValorPercentagem
| Rótulos de Linha ⏷ | Soma de Valor | Percentagem |
|---|---|---|
| Europa | 24 595 725 € | 64,6 % |
| América do Norte | 5 521 166 € | 14,5 % |
| África | 3 646 321 € | 9,6 % |
| América do Sul | 2 791 198 € | 7,3 % |
| Ásia | 1 507 981 € | 4,0 % |
| Total Geral | 38 062 391 € | 100,0 % |
Resposta: a Europa representa 64,6 % das vendas; todas as outras regiões juntas ficam com 35,4 %.
Pergunta 3
Um cliente do Reino Unido reclamou de uma encomenda de Brócolos. Que encomendas existem com essa combinação?
- Nova tabela: ID Encomenda em Linhas e Valor em Valores.
- Arraste País e Produto para Filtros.
- Escolha Reino Unido no filtro País e Brócolos no filtro Produto.
Campos da Tabela Dinâmica — arraste os campos entre as áreas:
FiltrosPaísProduto
Colunas
LinhasID Encomenda
ValoresSoma de Valor
| Produto | Brócolos ⏷ |
| País | Reino Unido ⏷ |
| Rótulos de Linha ⏷ | Soma de Valor |
|---|---|
| 100265 | 445,89 € |
| 100270 | 308,32 € |
| 100478 | 851,16 € |
| 100603 | 479,96 € |
| 100648 | 577,68 € |
| 100715 | 1 344,15 € |
| 100825 | 445,88 € |
| 100863 | 1 098,36 € |
| … mais 119 linhas … | |
| Total Geral | 221 976,88 € |
Resposta: existem 127 encomendas, num total de 221 976,88 €. A maior é a 114716 (12 013,76 €). Faça duplo clique num valor para o Excel abrir uma folha nova com as linhas completas dessa encomenda.
Desafio extra
Use o mesmo ficheiro e tente responder sozinho. Clique numa pergunta para ver uma pista.
Mostre Região › País › Produto. Qual é o produto mais vendido nos Estados Unidos?
Resposta: Abacate.
Acrescente a Média de Valor ao lado da Soma. Em que região as encomendas são, em média, maiores?
Arraste Valor uma terceira vez e mude o cálculo para Média.
Mostre a percentagem de cada país dentro da sua região (e não do total).
Em Mostrar Valores Como escolha % do Total do Elemento Principal.