Ao adicionar um campo calculado a uma Tabela Dinâmica para exibir uma porcentagem, o resultado frequentemente mostra o valor errado. Isso acontece porque o Excel calcula o campo no nível da linha, não no total geral. Este artigo explica por que a porcentagem está errada e mostra o método correto para corrigi-la usando um item calculado ou uma coluna auxiliar.
Principais Conclusões: Corrigir Porcentagens Erradas em Campos Calculados da Tabela Dinâmica
- Limitação do campo calculado: Um campo calculado sempre soma seus componentes primeiro, depois aplica a fórmula — isso prejudica os cálculos de porcentagem.
- Item calculado como alternativa: Use um item calculado dentro de um campo em vez de um campo calculado para obter porcentagens corretas por item.
- Coluna auxiliar nos dados de origem: Adicione uma coluna de porcentagem aos dados originais antes de criar a Tabela Dinâmica para garantir precisão.
Por que um Campo Calculado Mostra a Porcentagem Errada
Um campo calculado opera sobre as somas agregadas de outros campos antes de aplicar sua fórmula. Por exemplo, se você criar um campo calculado chamado “% de Lucro” com a fórmula =Lucro/Receita, o Excel primeiro soma todos os valores de Lucro e todos os valores de Receita em toda a linha ou coluna da Tabela Dinâmica. Em seguida, divide os dois totais. Isso produz a porcentagem geral do total, não a porcentagem por item ou grupo individual.
Se seus dados de origem contiverem várias linhas para um único produto ou região, o campo calculado soma todas essas linhas primeiro. A divisão então ocorre sobre essas somas grandes, o que resulta em uma média ponderada — não a porcentagem por item que você pretendia. Essa é a causa raiz do resultado errado.
Exemplo do Problema
Suponha que você tenha dados de vendas com as colunas: Produto, Receita e Lucro. Você cria uma Tabela Dinâmica com Produto nas Linhas e um campo calculado “% de Lucro” como =Lucro/Receita. Se o Produto A tem duas transações — uma com receita de R$ 100 e lucro de R$ 10 (10%) e outra com receita de R$ 200 e lucro de R$ 30 (15%) — o Excel soma o lucro para R$ 40 e a receita para R$ 300, então calcula 40/300 = 13,3%. A média correta por transação seria (10%+15%)/2 = 12,5%. O campo calculado dá um resultado ponderado, não uma média simples.
Passos para Corrigir a Porcentagem Errada
Você tem três métodos confiáveis para corrigir esse problema. Escolha o que melhor se adapta à sua estrutura de dados e necessidades de relatório.
Método 1: Adicionar uma Coluna Auxiliar aos Dados de Origem
Este é o método mais direto. Você adiciona uma coluna de porcentagem à sua tabela original do Excel antes de criar a Tabela Dinâmica. A Tabela Dinâmica então trata a porcentagem como um valor numérico simples e a soma ou calcula a média corretamente.
- Insira uma nova coluna ao lado dos seus dados
Nomeie-a como “% de Lucro” ou um nome descritivo semelhante. - Digite a fórmula para a primeira linha de dados
Digite=D2/C2(supondo que Lucro esteja na coluna D e Receita na coluna C) e pressione Enter. Formate a célula como Porcentagem com as casas decimais desejadas. - Copie a fórmula para baixo
Clique duas vezes na alça de preenchimento ou arraste-a para cobrir todas as linhas do intervalo de dados. - Atualize a Tabela Dinâmica
Clique com o botão direito em qualquer lugar da Tabela Dinâmica e selecione Atualizar. A nova coluna aparece na Lista de Campos da Tabela Dinâmica. - Adicione a coluna auxiliar à área de Valores
Arraste “% de Lucro” para a área de Valores. Por padrão, o Excel soma as porcentagens. Se você precisar da média, clique na seta suspensa na área de Valores, selecione Configurações do Campo de Valor e escolha Média.
Método 2: Usar um Item Calculado em Vez de um Campo Calculado
Um item calculado funciona dentro de um único campo e calcula por linha, não por soma. Este método é útil quando você não pode modificar os dados de origem.
- Clique em qualquer lugar da Tabela Dinâmica
O Excel exibe a guia Analisar da Tabela Dinâmica na faixa de opções. - Vá para Analisar da Tabela Dinâmica > Campos, Itens e Conjuntos > Item Calculado
Isso abre a caixa de diálogo Inserir Item Calculado. Observação: Item Calculado está disponível apenas quando você tem um campo na área de Linhas ou Colunas. - Nomeie o item calculado
Na caixa Nome, digite “% de Lucro”. - Escreva a fórmula usando valores de campo
Na caixa Fórmula, digite=Lucro/Receita. Clique em Adicionar e depois em OK. - Remova os campos originais se necessário
O item calculado aparece como uma nova linha dentro do campo. Você pode ocultar os itens originais usando o filtro suspenso.
Método 3: Usar o Recurso Mostrar Valores Como
Se seu objetivo é mostrar cada valor como uma porcentagem de uma linha, coluna ou total geral, as opções internas de Mostrar Valores Como funcionam sem qualquer campo calculado.
- Adicione o campo base à área de Valores
Arraste Lucro para a área de Valores. Arraste Receita também para a área de Valores. - Altere o campo Receita para mostrar como porcentagem
Clique na seta suspensa no campo Receita na área de Valores. Selecione Configurações do Campo de Valor > guia Mostrar Valores Como. - Selecione % do Total Geral da Linha Pai ou % do Total Geral
Escolha a opção que atende à sua necessidade de relatório. Para uma porcentagem por item, escolha % do Total Geral da Linha Pai se você tiver vários níveis. Clique em OK. - Oculte o campo base Lucro se desejar
Remova o campo Lucro da área de Valores se você quiser apenas a coluna de porcentagem.
Quando a Correção Não Funciona
Mesmo após aplicar um dos métodos acima, você ainda pode ver resultados inesperados. Os seguintes problemas comuns explicam por que e como resolvê-los.
Item Calculado Não Disponível no Menu
A opção Item Calculado fica esmaecida se você não colocou um campo na área de Linhas ou Colunas. Mova um campo — como Produto ou Região — para a área de Linhas primeiro. A opção de item calculado então se torna ativa.
Excel Mostra Erro de Divisão por Zero
Se alguma linha nos dados de origem tiver um valor de Receita igual a zero, o campo calculado ou a coluna auxiliar mostra um erro #DIV/0!. Na coluna auxiliar, envolva sua fórmula com IFERROR: =SEERRO(Lucro/Receita,0). Para itens calculados ou campos calculados, filtre os itens com receita zero usando o filtro da Tabela Dinâmica.
Porcentagem do Total Geral Excede 100%
Ao usar um campo calculado, a porcentagem do total geral é calculada sobre os totais somados, não sobre as porcentagens individuais. Isso pode produzir um total geral que não é a soma das porcentagens visíveis. Mude para o método da coluna auxiliar ou use Mostrar Valores Como para evitar essa confusão.
Comparação Rápida: Métodos de Correção para Porcentagens Erradas
| Item | Coluna Auxiliar | Item Calculado | Mostrar Valores Como |
|---|---|---|---|
| Modifica dados de origem | Sim | Não | Não |
| Suporta vários campos | Sim | Apenas um campo | Apenas um campo |
| Lida com valores zero | Use SEERRO | Filtrar manualmente | Sem tratamento especial |
| Precisão da % por item | Correta | Correta | Correta |
| Facilidade de configuração | Fácil | Moderada | Fácil |
Agora você pode corrigir um campo calculado que mostra a porcentagem errada em uma Tabela Dinâmica. Comece adicionando uma coluna auxiliar aos dados de origem — este método oferece controle total e evita completamente o problema de agregação. Se não puder modificar os dados de origem, use um item calculado ou o recurso Mostrar Valores Como. Para trabalhos avançados, aprenda a diferença entre campos calculados e itens calculados pressionando Alt+J+J para abrir a Lista de Campos da Tabela Dinâmica e explorando o menu Campos, Itens e Conjuntos.