Calcular valores em uma Tabela Dinâmica

Para calcular valores em uma Tabela Dinâmica, você pode usar um ou todos os seguintes tipos de métodos de cálculo:

• Funções de resumo nos campos de valor    Os dados na área de valores resumem os dados de origem subjacentes na Tabela Dinâmica. Por exemplo, os dados de origem a seguir:

• Produz as seguintes Tabelas Dinâmicas e Gráficos Dinâmicos. Se você criar um Gráfico Dinâmico a partir dos dados em uma Tabela Dinâmica, os valores nesse Gráfico Dinâmico refletirão os cálculos no relatório de Tabela Dinâmica associado.

• Na Tabela Dinâmica, o campo da coluna Mês fornecerá os itens Março e Abril. O campo da linha Região fornece os itens Norte, Sul, Leste e Oeste. O valor na interseção da coluna Abril e da linha Norte é a receita total de vendas dos registros nos dados de origem que têm valores de Mês de Abril, e valores de Região de Norte.

• Em um Gráfico Dinâmico, o campo Região pode ser um campo de categoria que mostra Norte, Sul, Leste e Oeste como categorias. O campo Mês pode ser um campo de série que mostra os itens Março, Abril e Maio como séries representadas na legenda. Um campo Valores chamado Soma das Vendas poderia conter marcadores de dados que representam a receita total em cada região para cada mês. Por exemplo, um marcador de dados representaria, por sua posição no eixo vertical (valor), o total de vendas para Abril na região Norte.

• Para calcular os campos de valor, as seguintes funções de resumo estão disponíveis para todos os tipos de dados de origem, exceto os dados de origem de OLAP (Processamento Analítico Online).

  Função	Resume
  Soma	A soma dos valores. Essa é a função padrão para dados numéricos.
  Contagem	O número de valores de dados. A função de resumo Contagem funciona de forma semelhante à função CONT.VALORES. Contagem é a função padrão para dados diferentes de números.
  Média	A média dos valores.
  Máx	O maior valor.
  Mín	O menor valor.
  Produto	O produto dos valores.
  Cont.Núm	O número de valores de dados que são números. A função de resumo Cont.Núm funciona de forma semelhante à função CONTAGEM.
  DesvPad	Uma estimativa do desvio padrão de uma população, onde a amostra é um subconjunto da população inteira.
  DesvPadP	O desvio padrão de uma população, onde a população são todos os dados que serão resumidos.
  Var	Uma estimativa da variação de uma população, onde a amostra é um subconjunto da população inteira.
  VARP	A variação de uma população, onde a população são todos os dados que serão resumidos.

• Cálculos personalizados    Um cálculo personalizado mostra valores com base em outros itens ou células na área de dados. Por exemplo, você pode exibir valores no campo de dados Soma das Vendas como uma porcentagem das vendas de Março, ou como uma soma acumulada dos itens no campo Mês.

  As funções a seguir estão disponíveis para cálculos personalizados em campos de valor.

  Função	Resultado
  Nenhum cálculo	Exibe o valor inserido no campo.
  % do Total Geral	Exibe valores como uma porcentagem do total geral de todos os valores ou pontos de dados no relatório.
  % do Total da Coluna	Exibe todos os valores em cada coluna ou série como uma porcentagem do total da coluna ou série.
  % do Total da Linha	Exibe o valor em cada linha ou categoria como uma porcentagem do total da linha ou categoria.
  % de	Exibe os valores como um percentual do valor do Item base no Campo base.
  % do Total de Linhas Pai	Calcula os valores da seguinte maneira: (valor do item) / (valor do item pai em linhas)
  % do Total de Colunas Pai	Calcula os valores da seguinte maneira: (valor do item) / (valor do item pai em colunas)
  % do Total de Pais	Calcula os valores da seguinte maneira: (valor do item) / (valor do item pai do Campo base selecionado)
  Diferença de	Exibe valores como a diferença do valor do Item base no Campo base.
  % Diferença de	Exibe valores como a diferença percentual do valor do Item base no Campo base.
  Soma Acumulada em	Exibe o valor de itens sucessivos no Campo base escolhido como uma soma acumulada.
  % da Soma Acumulada em	Calcula o valor de itens sucessivos no Campo base exibidos como uma soma acumulada em porcentagem.
  Classificar do Menor para o Maior	Exibe a classificação dos valores selecionados em um campo específico, listando o menor item no campo como 1, e cada valor maior terá um valor de classificação maior.
  Classificar do Maior para o Menor	Exibe a classificação dos valores selecionados em um campo específico, listando o maior item no campo como 1, e cada valor menor terá um valor de classificação maior.
  Índice	Calcula os valores da seguinte maneira: ((valor na célula) x (Total Geral dos Totais Gerais)) / ((Total Geral da Linha) x (Total Geral da Coluna))

• Fórmulas    Se as funções de resumo e os cálculos personalizados não fornecerem os resultados que você deseja, crie suas próprias fórmulas nos campos e itens calculados. Por exemplo, você poderia adicionar um item calculado com a fórmula para comissão de vendas, que seria diferente para cada região. Depois, o relatório incluiria automaticamente a comissão nos subtotais e totais gerais.

Os cálculos e opções disponíveis em um relatório dependem se os dados de origem vieram de um banco de dados OLAP ou de um banco de dados não OLAP.

• Cálculos com base nos dados de origem OLAP    Para Tabelas Dinâmicas criadas com cubos OLAP, os valores resumidos são pré-calculados no servidor OLAP antes de o Excel exibir os resultados. Não é possível alterar como esses valores pré-calculados são calculados na Tabela Dinâmica. Por exemplo, você não pode alterar a função resumida usada para calcular os campos de dados ou subtotais, ou adicionar campos ou itens calculados.

  Além disso, se o servidor OLAP fornecer campos calculados, conhecidos como membros calculados, você verá esses campos na lista de Campos da Tabela Dinâmica. Você também verá quaisquer campos e itens calculados criados por macros escritas em VBA (Visual Basic for Applications) e armazenadas em sua pasta de trabalho, mas não poderá alterar esses campos ou itens. Se você precisar de tipos adicionais de cálculos, entre em contato com o administrador de banco de dados OLAP.

  Para dados de origem OLAP, você pode incluir ou excluir os valores para itens ocultos ao calcular subtotais e totais gerais.

• Cálculos com base em dados de origem não OLAP    Nas Tabelas Dinâmicas baseadas em outros tipos de dados externos, ou em dados da planilha, o Excel usa a função de resumo Soma para calcular campos de valor que contêm dados numéricos, e a função de resumo Contagem para calcular campos de dados que contêm texto. Você pode escolher outra função de resumo, como Média, Máx ou Mín, para analisar e personalizar ainda mais seus dados. Você também pode criar suas próprias fórmulas que usam elementos do relatório, ou outros dados de planilha, criando um campo calculado ou um item calculado dentro de um campo.

Você só pode criar fórmulas em relatórios baseados em dados de origem não OLAP. Não é possível usar fórmulas em relatórios baseados em um banco de dados OLAP. Ao usar fórmulas em Tabelas Dinâmicas, você precisa conhecer as seguintes regras de sintaxe da fórmula e o comportamento da fórmula:

• Elementos da fórmula da Tabela Dinâmica    Em fórmulas que você cria para campos e itens calculados, é possível usar operadores e expressões da mesma forma que faz em outras fórmulas de planilha. Você pode usar constantes e fazer referência aos dados do relatório, mas você não pode usar referências de célula ou nomes definidos. Não é possível usar funções de planilha que exigem referências de célula ou nomes definidos como argumentos, e não é possível usar funções de matriz.

• Nomes de campo e de item    O Excel usa nomes de campo e de item para identificar os elementos de um relatório em suas fórmulas. No exemplo a seguir, os dados no intervalo C3:C9 estão usando o nome de campo Laticínios. Um item calculado no campo Tipo, que realiza a estimativa de vendas de um novo produto com base nas vendas de Laticínios, poderia usar uma fórmula como =Laticínios * 115%.

  

  Observação: Em um Gráfico Dinâmico, os nomes dos campos são exibidos na lista de campos da Tabela Dinâmica, e os nomes de itens podem ser vistos em cada lista suspensa de campo. Não confunda esses nomes com aqueles vistos nas dicas de gráfico, que refletem nomes de pontos de dados e série, em vez disso.

• Fórmulas operam sobre totais de soma, não sobre registros individuais    As fórmulas para campos calculados operam sobre a soma dos dados subjacentes para quaisquer campos na fórmula. Por exemplo, a fórmula de campo calculado =Vendas * 1,2 multiplica a soma das vendas de cada tipo e região por 1,2; ela não multiplica cada venda individual por 1,2 e depois soma os valores multiplicados.

  As fórmulas para itens calculados operam em registros individuais. Por exemplo, a fórmula de item calculado =Laticínios *115% multiplica cada venda individual de Laticínios por 115%. Depois, os valores multiplicados são resumidos na área Valores.

• Espaços, números e símbolos em nomes    Em um nome que inclui mais de um campo, os campos podem estar em qualquer ordem. No exemplo acima, as células C6:D6 podem ser 'Abril Norte' ou 'Norte Abril'. Use aspas simples nos nomes que possuem mais de uma palavra ou que incluem números ou símbolos.

• Totais    As fórmulas não podem se referir aos totais (por exemplo, Março Total, Abril Total e Total Geral no exemplo).

• Nomes de campo nas referências de item    Você pode incluir o nome do campo em uma referência a um item. O nome do item deve estar entre colchetes — por exemplo, Região[Norte]. Use este formato para evitar erros #NAME? quando dois itens em dois campos diferentes de um relatório tiverem o mesmo nome. Por exemplo, se um relatório tiver um item chamado Carne no campo Tipo, e outro item chamado Carne no campo Categoria, você poderá impedir erros #NAME? fazendo referência aos itens como Tipo[Carne] e Categoria[Carne].

• Fazendo referência a itens por posição    Você pode fazer referência a um item por sua posição no relatório, conforme ele está classificado e exibido. Tipo[1] é Laticínios, e Tipo[2] é Frutos do mar. O item referido dessa maneira pode mudar sempre que as posições dos itens mudarem, ou itens diferentes forem são exibidos ou escondidos. Os itens ocultos não são contados nesse índice.

  Use posições relativas para se referir aos itens. As posições são determinadas de forma relativa ao item calculado que contém a fórmula. Se Sul for a região atual, Região[-1] será Norte; se Norte for a região atual, Região[+ 1] será Sul. Por exemplo, um item calculado poderia usar a fórmula =Região[-1] * 3%. Se a posição que você fornecer estiver antes do primeiro item ou após o último item no campo, a fórmula resultará em um erro #REF!.

Para usar fórmulas em um Gráfico Dinâmico, crie as fórmulas na Tabela Dinâmica associada, onde você pode ver os valores individuais que compõem seus dados e, em seguida, pode exibir os resultados graficamente no Gráfico Dinâmico.

Por exemplo, o Gráfico Dinâmico a seguir mostra as vendas de cada vendedor por região:

Para ver como seriam as vendas se elas aumentassem em 10%, você pode criar um campo calculado na Tabela Dinâmica associada que usa uma fórmula como =Vendas * 110%.

O resultado aparece imediatamente no Gráfico Dinâmico, conforme mostra o gráfico a seguir:

Para ver um marcador de dados separado para as vendas na região Norte, menos um custo de transporte de 8%, você pode criar um item calculado no campo Região com uma fórmula como =Norte – (Norte * 8%).

O gráfico resultante teria esta aparência:

No entanto, um item calculado criado no campo Vendedor apareceria como uma série representada na legenda, e apareceria no gráfico como um ponto de dados em cada categoria.

Antes de começar, decida se quer um campo calculado ou um item calculado dentro de um campo. Use um campo calculado quando quiser usar os dados de outro campo em sua fórmula. Use um item calculado quando quiser que a fórmula use dados de um ou mais itens específicos em um campo.

Para itens calculados, você pode inserir fórmulas diferentes, célula por célula. Por exemplo, se um item calculado chamado OrangeCounty tiver uma fórmula de =Laranjas *.25 em todos os meses, você poderá alterar a fórmula para =Laranjas *.5 para junho, julho e agosto.

Se você tiver vários itens calculados ou fórmulas, ajuste a ordem do cálculo.

Adicionar um campo calculado

1. Clique na Tabela Dinâmica.

   Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

2. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, depois, clique em Campo Calculado.

   

3. Na caixa Nome, digite um nome para o campo.

4. Na caixa Fórmula, insira a fórmula para o campo.

   Para usar os dados de outro campo na fórmula, clique no campo na caixa Campos e, em seguida, clique em Inserir Campo. Por exemplo, para calcular uma comissão de 15% em cada valor no campo Vendas, você poderia inserir = Vendas * 15%.

5. Clique em Adicionar.

Adicionar um item calculado a um campo

1. Clique na Tabela Dinâmica.

   Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

2. Se os itens no campo estiverem agrupados, na guia Analisar, no grupo Grupo, clique em Desagrupar.

   

3. Clique no campo ao qual você deseja adicionar o item calculado.

4. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, depois, clique em Item Calculado.

   

5. Na caixa Nome, digite um nome para o item calculado.

6. Na caixa Fórmula, insira a fórmula para o item.

   Para usar os dados de um item na fórmula, clique no item na lista Itens e, depois, clique em Inserir Item (o item deve ser do mesmo campo que o item calculado).

7. Clique em Adicionar.

Insira fórmulas diferentes, célula por célula, para itens calculados.

1. Clique em uma célula cuja fórmula você deseja alterar.

   Para alterar a fórmula de várias células, mantenha pressionada a tecla CTRL e clique nas células adicionais.

2. No barra de fórmulas, digite as alterações para a fórmula.

Ajuste a ordem do cálculo para vários itens calculados ou fórmulas.

1. Clique na Tabela Dinâmica.

   Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

2. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, depois, clique em Ordem de Resolução.

   

3. Clique em uma fórmula e clique em Mover para Cima ou Mover para Baixo.

4. Continue até que as fórmulas estejam na ordem desejada para o cálculo.

Você pode exibir uma lista de todas as fórmulas usadas na Tabela Dinâmica atual.

1. Clique na Tabela Dinâmica.

   Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

2. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e então clique em Listar Fórmulas.

   

Antes de editar uma fórmula, determine se essa fórmula está em um campo calculado ou em um item calculado. Se a fórmula estiver em um item calculado, determine também se a fórmula é a única para o item calculado.

Para itens calculados, você pode editar fórmulas individuais para células específicas de um item calculado. Por exemplo, se um item calculado chamado OrangeCalc tiver uma fórmula de =Laranjas *.25 em todos os meses, você poderá alterar a fórmula para =Laranjas *.5 para junho, julho e agosto.

Determine se uma fórmula está em um campo calculado ou em um item calculado.

1. Clique na Tabela Dinâmica.

   Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

2. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e então clique em Listar Fórmulas.

   

3. Na lista de fórmulas, localize a fórmula que você deseja alterar em Campo Calculado ou Item Calculado.
   
   Quando houver várias fórmulas para um item calculado, a fórmula padrão inserida durante a criação do item terá o nome do item calculado na coluna B. Para outras fórmulas para um item calculado, a coluna B contém o nome do item calculado e os nomes dos itens de interseção.

   Por exemplo, você pode ter uma fórmula padrão para um item calculado chamada MeuItem, e outra fórmula para esse item identificada como Vendas Janeiro MeuItem. Na Tabela Dinâmica, você encontraria essa fórmula na célula Vendas para a linha MeuItem e coluna Janeiro.

4. Continue usando um destes métodos de edição:

Editar uma fórmula de campo calculado

1. Clique na Tabela Dinâmica.

   Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

2. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, depois, clique em Campo Calculado.

   

3. Na caixa Nome, selecione o campo calculado para o qual você deseja alterar a fórmula.

4. Na caixa Fórmula, edite a fórmula.

5. Clique em Modificar.

Editar uma única fórmula para um item calculado

1. Clique no campo que contém o item calculado.

2. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, depois, clique em Item Calculado.

   

3. Na caixa Nome, selecione o item calculado.

4. Na caixa Fórmula, edite a fórmula.

5. Clique em Modificar.

Editar uma fórmula individual de uma célula específica de um item calculado

1. Clique em uma célula cuja fórmula você deseja alterar.

   Para alterar a fórmula de várias células, mantenha pressionada a tecla CTRL e clique nas células adicionais.

2. No barra de fórmulas, digite as alterações para a fórmula.

   Dica: Se você tiver vários itens calculados ou fórmulas, ajuste a ordem do cálculo. Para saber mais, confira Ajustar a ordem do cálculo para vários itens calculados ou fórmulas.

1. Determine se a fórmula está em um campo calculado ou em um item calculado.

   Campos calculados aparecem na Lista de Campos da Tabela Dinâmica. Itens calculados aparecem como itens dentro de outros campos.

2. Siga um destes procedimentos:

   • Para excluir um campo calculado, clique em qualquer lugar na Tabela Dinâmica.

   • Para excluir um item calculado, na Tabela Dinâmica, clique no campo que contém o item que você deseja excluir.

     Isso exibe as Ferramentas de Tabela Dinâmica, adicionando as guias Analisar e Design.

3. Na guia Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, depois, clique em Campo Calculado ou Item Calculado.

   

4. Na caixa Nome, selecione o campo ou item que você quer excluir.

5. Clique em Excluir.