Campo e Item Calculado
Criar novas medidas e novos itens com fórmulas dentro da tabela dinâmica.
Perguntas de partida
Antes de ler, pense: como responderia a isto só com fórmulas, numa folha com 20 000 linhas?
- Os produtos que vendem mais de 2,5 milhões de euros pagam uma taxa de 3%. Quanto pagamos por produto?
- Quanto nos sobra de cada produto depois de pagar o transporte?
- Qual é o preço médio por kg de cada produto? (E porque não devo usar a média simples do Preço Unitário?)
- Como comparo a Europa com o resto do mundo sem criar uma coluna nova nos dados?
XLS
Ficheiro deste capítulo08-campo-item-calculado.xlsx · 3.3 MB · folhas Instruções, Colunas, Dados e Resolução
DescarregarUm campo calculado é uma nova coluna de valores criada com uma fórmula que usa outros campos
(por exemplo, Valor/Quantidade). Um item calculado é um novo item dentro de um campo
(por exemplo, uma nova "região" que soma outras). Ambos ficam dentro da tabela dinâmica — os dados de origem não mudam.
Pergunta 1
Os produtos que vendem mais de 2,5 milhões de euros pagam uma taxa de 3%. Quanto pagamos por produto?
- Crie uma tabela com Produto em Linhas e Valor em Valores.
- Clique na tabela › › › .
- Em Nome escreva Imposto. Em Fórmula escreva a fórmula abaixo (pode inserir os campos com duplo clique na lista).
- Clique em Adicionar e em OK. O campo aparece automaticamente em Valores.
=SE(Valor>2500000;3%*Valor;0)
| Rótulos de Linha ⏷ | Soma de Valor | Soma de Imposto | Soma de Margem | Preço médio (€/kg) | Média simples de Preço Unitário |
|---|---|---|---|---|---|
| Abacate | 5 438 139 € | 163 144 € | 4 523 861 € | 3,57 € | 3,58 € |
| Banana | 4 596 658 € | 137 900 € | 2 877 131 € | 1,16 € | 1,16 € |
| Manga | 3 313 871 € | 99 416 € | 2 695 705 € | 2,74 € | 2,74 € |
| Ananás | 3 183 712 € | 95 511 € | 2 590 666 € | 3,06 € | 3,06 € |
| Uva | 3 010 617 € | 90 318 € | 2 292 226 € | 1,90 € | 1,89 € |
| Maçã | 2 803 734 € | 84 112 € | 1 427 011 € | 0,90 € | 0,90 € |
| Pera Rocha | 2 557 113 € | 76 713 € | 1 496 326 € | 1,00 € | 1,00 € |
| Brócolos | 2 530 271 € | 75 908 € | 1 887 900 € | 1,69 € | 1,69 € |
| Tomate | 2 375 349 € | 0 € | 1 362 784 € | 0,95 € | 0,95 € |
| Feijão Verde | 2 078 438 € | 0 € | 1 656 778 € | 2,21 € | 2,21 € |
| Laranja | 1 972 826 € | 0 € | 901 440 € | 0,74 € | 0,74 € |
| Alface | 1 399 683 € | 0 € | 936 470 € | 1,26 € | 1,27 € |
| Cenoura | 1 031 354 € | 0 € | 302 784 € | 0,58 € | 0,58 € |
| Batata | 1 011 642 € | 0 € | 153 604 € | 0,48 € | 0,47 € |
| Cebola | 758 984 € | 0 € | 209 562 € | 0,53 € | 0,53 € |
| Total Geral | 38 062 391 € | 1 141 872 € | 25 314 246 € | 1,31 € | 1,31 € |
Resposta: 8 produtos ultrapassam 2,5 milhões de euros e pagam imposto; a soma desses impostos é de 823 023 €.
Atenção ao total: o campo calculado é aplicado às somas de cada linha, não a cada encomenda.
Na linha Total Geral o Excel calcula
SE(38 062 391>2500000; 3%×38 062 391; 0) = 1 141 872 €,
que não é a soma das linhas (823 023 €). Em campos calculados com SE, desconfie sempre dos totais.Pergunta 2
Quanto nos sobra de cada produto depois de pagar o transporte?
Crie outro campo calculado. Como os nomes têm espaços, ficam entre plicas:
Nome: Margem
='Valor Líquido'-'Custo Transporte'
Resposta: a margem depois de descontos e transporte é de 25 314 246 €. Em percentagem do valor, o produto com melhor margem é o Abacate (83,2 %) e o pior é a Batata (15,2 %) — é um produto barato e pesado, onde o transporte pesa muito.
Pergunta 3
Qual é o preço médio por kg de cada produto? (E porque não devo usar a média simples do Preço Unitário?)
Nome: Preço Médio kg
=Valor/Quantidade
Este campo divide a soma do Valor pela soma da Quantidade, ou seja, é uma média ponderada pelos kg vendidos. A alternativa Média de Preço Unitário dá o mesmo peso a uma encomenda de 20 kg e a uma de 9000 kg.
Resposta: o preço médio global é de 1,31 €/kg (o Abacate é o mais caro, 3,57 €/kg). Neste conjunto de dados as duas médias são parecidas porque o preço de cada produto varia pouco, mas com preços muito diferentes entre clientes a média simples pode dar um valor bastante errado.
Dica: para ver ou apagar campos calculados: › .
Pergunta 4
Como comparo a Europa com o resto do mundo sem criar uma coluna nova nos dados?
- Crie uma tabela com Região em Linhas e Valor em Valores.
- Clique num nome de região (por exemplo, Europa) › › .
- Nome: Fora da Europa; Fórmula:
='América do Norte'+'América do Sul'+África+Ásia› Adicionar › OK. - No filtro de Rótulos de Linha, desmarque as quatro regiões usadas na fórmula.
| Rótulos de Linha ⏷ | Soma de Valor |
|---|---|
| Europa | 24 595 725 € |
| Fora da Europa | 13 466 666 € |
| Total Geral | 38 062 391 € |
Resposta: a Europa vale 24 595 725 € e o resto do mundo 13 466 666 €.
Atenção: se não ocultar as quatro regiões, o Total Geral conta-as duas vezes (uma vez sozinhas e outra dentro de Fora da Europa). Itens calculados também não funcionam em campos agrupados nem com Média/Contagem em alguns casos.
Desafio extra
Use o mesmo ficheiro e tente responder sozinho. Clique numa pergunta para ver uma pista.
Crie o campo calculado 'Custo por kg' = 'Custo Transporte'/Quantidade e acrescente Transporte em Colunas.
Verá que o transporte Aéreo é muito mais caro por kg do que o Marítimo e o Rodoviário.
Crie o item calculado 'Península Ibérica' no campo País.
Só existe Espanha como país ibérico de destino — o item fica igual a Espanha. Porquê? (Portugal é a origem!)