Calcular valores em um relatório de Tabela Dinâmica

Nos relatórios de Tabela Dinâmica , use as funções resumo (função resumo: um tipo de cálculo que combina os dados de origem em um relatório de tabela dinâmica ou em uma tabela de combinação ou quando você está inserindo subtotais automáticos em uma lista ou banco de dados. Os exemplos de função resumo incluem Soma, Contar e Média.) no valor campos (campo: em um relatório de tabela dinâmica ou de gráfico dinâmico, uma categoria de dados que é derivada de um campo nos dados de origem. Os relatórios de tabela dinâmica têm linha, coluna, página e campos de dados. Os relatórios de gráfico dinâmico têm série, categoria, página e campos de dados.) para combinar valores de dados de origem (dados de origem: a lista ou a tabela usada para criar um relatório de tabela dinâmica ou de gráfico dinâmico. Os dados de origem podem ser obtidos de uma lista ou intervalo do Excel, de um banco de dados ou cubo externo, ou de outro relatório de tabela dinâmica.) de base. Se as funções resumo e os cálculos personalizados não fornecerem os resultados desejados, você poderá criar suas próprias fórmulas (fórmula: uma sequência de valores, referências de células, nomes, funções ou operadores em uma célula que juntos produzem um novo valor. Uma fórmula sempre começa com um sinal de igual (=).) em campos calculados (campo calculado: um campo em um relatório de tabela dinâmica ou de gráfico dinâmico que usa uma fórmula criada por você. Os campos calculados podem executar cálculos usando o conteúdo de outros campos no relatório de tabela dinâmica ou de gráfico dinâmico.) e itens calculados (item calculado: um item em um campo de tabela dinâmica ou de gráfico dinâmico que usa uma fórmula criada por você. Os itens calculados podem executar cálculos com o conteúdo de outros itens dentro do mesmo campo do relatório de tabela dinâmica ou de gráfico dinâmico.). Por exemplo, você poderia adicionar um item calculado com a fórmula para comissão de vendas, que poderia ser diferente para cada região. O relatório de Tabela Dinâmica incluiria, então, a comissão nos subtotais e totais gerais automaticamente..

O que você deseja fazer?


Saiba mais sobre como calcular valores em um relatório de Tabela Dinâmica

Os relatórios de Tabela Dinâmica fornecem formas de calcular dados. Saiba mais sobre os métodos de cálculo que estão disponíveis, como os cálculos são afetados pelo tipo de fonte de dados, e como usar fórmulas em relatórios de Tabela Dinâmica e Gráfico Dinâmico.

Métodos de cálculo disponíveis

Para calcular os valores em um relatório de Tabela Dinâmica, use qualquer 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 de base no relatório de Tabela Dinâmica, como o exemplo a seguir:

Exemplo de dados de origem de tabela dinâmica

Exemplo de relatório de tabela dinâmica

Exemplo de um relatório de gráfico dinâmico

Função Para resumir
Soma A soma dos valores. Esta é a função padrão para dados numéricos.
Contagem O número de valores de dados. A função resumo de Cont.Valores funciona da mesma forma que a função de planilha COUNTA. Cont.Valores é a função padrão para dados não numéricos.
Normal A média dos valores.
Máx O maior valor.
Mín O menor valor.
Produto O produto dos valores.
Cont.Núms O número de valores de dados numéricos. A função de resumo Cont.Núm funciona da mesma forma que a função de planilha Cont.Valores.
DesvP Uma estimativa do desvio padrão de uma população, onde a amostra é um subconjunto da população inteira.
StDevpp 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 variância de uma população, onde a amostra é um subconjunto da população inteira.
VARP A variância de uma população, onde a população são todos os dados que serão resumidos.

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

Função Resultado
Diferença de Exibe os valores como a diferença em relação ao valor do Item base no Campo base.
% de Exibe os valores como uma porcentagem do valor do Item base no Campo base.
% Diferença de Exibe os valores como a diferença percentual em relação ao valor do Item base no Campo base.
Soma Acumulada em Exibe o valor de itens sucessivos no Campo base como uma soma acumulada.
% da linha Exibe o valor em cada linha ou categoria como uma porcentagem do total da linha ou categoria.
% da coluna Exibe todos os valores em cada coluna ou seqüência como uma porcentagem do total da coluna ou seqüência.
% do total Exibe os valores como uma porcentagem do total geral de todos os dados ou pontos de dados no relatório.
Índice

Calcula valores da seguinte maneira:

((valor na célula) x (Total geral)) / ((Total geral da linha) x (Total geral da coluna))

Para obter mais informações, consulte Alterar a função de resumo ou o cálculo personalizado de um campo em um relatório de tabela dinâmica.

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

Início da página Início da página

Como o tipo de dados de origem afeta os cálculos

Os cálculos e as opções que estão disponíveis em um relatório dependem se os dados de origem vêm de um banco de dados OLAP ou uma origem de dados não-OLAP.

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. Verá também quaisquer campos e itens calculados criados por macros escritas no Visual Basic for Applications (Visual Basic for Applications (VBA):uma versão de linguagem macro do Microsoft Visual Basic usada para programar aplicativos do Microsoft Windows e incluída em vários programas da Microsoft.) e armazenados na sua pasta de trabalho, mas não poderá alterar esses campos ou itens. Se você precisar de tipos adicionais de cálculos, contate o administrador de banco de dados OLAP.

Para dados de origem OLAP, é possível incluir ou excluir os valores para itens ocultos quando você calcula subtotais e totais gerais.

  • Cálculos com base em dados de origem não-OLAP    Em Tabela Dinâmica, os relatórios com base em outros tipos de dados externos ou em dados de planilha, o Excel utiliza a função resumo Soma para calcular campos valor que contêm dados numéricos e a função resumo Cont.Núm para calcular campos de dados que contêm texto. Você pode escolher uma função resumo diferente como Média, Máximo ou Mínimo para personalizar e analisar ainda mais seus dados. Pode também criar suas próprias fórmulas que usam elementos do relatório ou outros dados de planilha, criando um campo ou item calculado dentro de um campo.

Início da página Início da página

Usando fórmulas em relatórios de Tabela Dinâmica

É possível criar fórmulas somente em relatórios que sejam baseados em dados de origem não-OLAP. Não use fórmulas em relatórios que têm como base um banco de dados OLAP. Ao usar fórmulas em relatórios de Tabela Dinâmica, é preciso saber sobre as seguintes regras de sintaxe da fórmula e o comportamento dessa fórmula:

Exemplo de relatório de tabela dinâmica

 Observação   Em um relatório de gráfico dinâmico, os nomes de campos são exibidos na lista de campos de tabela dinâmica e os nomes de itens podem ser vistos em cada listagem suspensa de campo. Não confunda esses nomes com aqueles vistos nas dicas de gráfico, que refletem nomes de seqüências e de pontos.

  • As fórmulas operam sobre o total da soma, não sobre registros individuais    As fórmulas para campos calculados operam sobre a soma dos dados subjacentes de quaisquer campos na fórmula. Por exemplo, a fórmula de campo calculada =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 sobre os registros individuais. Por exemplo, a fórmula de item calculado =Laticínio *115% multiplica cada venda individual de Laticínios por 115%, após o que os valores multiplicados são resumidos na área de 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 apóstrofo nos nomes que contêm mais de uma palavra ou que incluem números ou símbolos.
  • Totais    s fórmulas não podem se referir aos totais (como o Total Março, Total Abril e Total Geral no exemplo).
  • Nomes de campo em 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 esse formato para evitar erros #NOME? quando dois itens em dois campos diferentes em 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á evitar erros #NOME? referindo-se aos itens como Tipo[Carne] e Categoria[Carne].
  • Referindo-se 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 no momento. Tipo[1] é Laticínio e Tipo[2] é Frutos do Mar. O item referido dessa maneira pode se alterar sempre que as posições dos itens forem alteradas ou que diferentes itens forem exibidos ou ocultados. Os itens ocultos não são contados nesse índice.

Você pode usar posições relativas para referir-se aos itens. As posições são determinadas em relação ao item calculado que contém a fórmula. Se Sul for a região atual, a 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 fornecida estiver antes do primeiro item ou após o último item no campo, a fórmula resultará em um erro #REF!.

Início da página Início da página

Usando fórmulas em relatórios de gráfico dinâmico

Para usar fórmulas para um relatório do Gráfico Dinâmico, crie as fórmulas no relatório de Tabela Dinâmica associado, onde você pode ver os valores individuais que compõem os dados e exibir os resultados graficamente no relatório de Gráfico Dinâmico.

Por exemplo, o relatório de gráfico dinâmico a seguir mostra as vendas de cada vendedor por região:

Relatório de gráfico dinâmico mostrando as vendas de cada vendedor por região

Para ver como estão as vendas caso elas tenham aumentado em 10 por cento, crie um campo calculado no relatório de Tabela Dinâmica associado que utiliza uma fórmula como=Vendas * 110%.

O resultado aparece imediatamente no relatório de Gráfico Dinâmico, como mostra o gráfico a seguir:

Relatório de gráfico dinâmico mostrando as vendas aumentadas em 10% por região

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

O gráfico resultante teria o seguinte aspecto:

Gráfico dinâmico com um item calculado.

Porém, um item calculado no campo Vendedor, no entanto, apareceria como uma seqüência representada na legenda e apareceria no gráfico como um ponto de dados em cada categoria.

Início da página Início da página

Criar uma fórmula em um relatório de Tabela Dinâmica

 Importante   Você não pode criar fórmulas em um relatório de tabela dinâmica ou de gráfico dinâmico vinculado a dados de origem OLAP (OLAP: uma tecnologia de banco de dados otimizada para a geração de consultas e relatórios, e não para o processamento de transações. Os dados OLAP são organizados hierarquicamente e armazenados em cubos em vez de tabelas.).

  1. Decida se você deseja um campo calculado ou um item calculado dentro de um campo.
  2. Siga um destes procedimentos:

Adicionar um campo calculado    

  1. Clique no relatório de tabela dinâmica.
  2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Campo Calculado.
  3. Na caixa Nome, digite um nome para o campo.
  4. Na caixa Fórmula, digite 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% sobre cada valor do campo Vendas, você digitaria = Vendas * 15%.

  1. Clique em Adicionar.

Adicionar um item calculado a um campo    

  1. Se os itens no campo estiverem agrupados, na guia Opções, no grupo Grupo, clique em Desagrupar.
  2. Clique no campo ao qual você deseja adicionar o item calculado.
  3. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Item Calculado.
  4. Na caixa Nome, digite um nome para o item calculado.
  5. Na caixa Fórmula, digite a fórmula para o item.

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

  1. Clique em Adicionar.
  2. Se você tiver desagrupado itens na etapa 1, reagrupe-os, se desejar. Para obter mais informações, consulte Agrupar itens em um campo de Tabela Dinâmica.
  1. Para itens calculados, você pode inserir diferentes fórmulas célula a célula.

Por exemplo, se um item calculado chamado LaranjaDaTerra tiver uma fórmula de =Laranjas * .25 em todos os meses, em todos os meses, será possível mudar a fórmula para =Laranjas *.5 para junho, julho e agosto.

Siga este procedimento:

  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.

  1. Na barra de fórmulas (barra de fórmulas: uma barra na parte superior da janela do Excel usada para inserir ou editar valores ou fórmulas em células ou gráficos. Exibe o valor constante ou a fórmula armazenada na célula ativa.), digite as alterações para a fórmula.
  1. Se tiver vários itens ou fórmulas calculados, ajuste a ordem do cálculo, seguindo o procedimento abaixo:
  1. Clique no relatório de tabela dinâmica.
  2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Ordem de Resolução.
  3. Clique em uma fórmula e, em seguida, clique em Mover para Cima ou Mover para Baixo.
  4. Continue até que as fórmulas estejam na ordem de cálculo desejada.

Início da página Início da página

Exibir todas as fórmulas usadas em um relatório de Tabela Dinâmica

Para exibir uma lista de todas as fórmulas usadas no presente relatório de tabela dinâmica, siga o seguinte procedimento:

  1. Clique no relatório de tabela dinâmica.
  2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Listar Fórmulas.

Início da página Início da página

Editar uma fórmula de Tabela Dinâmica

  1. Determine se a fórmula está em um campo calculado ou em um item calculado. Se a fórmula estiver em um item calculado, determine se a fórmula será a única para o item calculado, seguindo o procedimento abaixo:
    1. Clique no relatório de tabela dinâmica.
    2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Listar Fórmulas.
    3. Na lista de fórmulas, localize a fórmula que deseja alterar listada no Campo Calculado ou no Item Calculado.

Quando houver várias fórmulas para um item calculado, a fórmula padrão que foi inserida quando o item (item: uma subcategoria de um campo nos relatórios de tabela dinâmica e gráfico dinâmico. Por exemplo, o campo "Mês" poderia ter itens como "Janeiro," "Fevereiro" e assim por diante.) foi criado terá o nome do item calculado na coluna B. Para obter fórmulas adicionais para um item calculado, a coluna B conterá o nome do item calculado e os nomes dos itens de interseção.

Por exemplo, você poderia ter uma fórmula padrão para um item calculado chamada MeuItem e outra fórmula para esse item identificada como Vendas Janeiro MeuItem. No relatório de tabela dinâmica, você localizaria essa fórmula na célula Vendas na linha MeuItem e coluna Janeiro.

  1. Siga um destes procedimentos:

Editar uma fórmula de item calculado    

  1. Clique no relatório de tabela dinâmica.
  2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Campo Calculado.
  3. Na caixa Nome, selecione no campo calculado cuja fórmula você deseja alterar.
  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 Opções, no grupo Ferramentas, clique em Fórmulas e em Item Calculado.
  3. Na caixa Nome, selecione no item calculado.
  4. Na caixa Fórmula, edite a fórmula.
  5. Clique em Modificar.

Editar fórmulas individuais para células específicas de um item calculado    

Por exemplo, se um item calculado chamado LaranjaDaTerra tiver uma fórmula de =Laranjas * .25 em todos os meses, em todos os meses, será possível mudar a fórmula para =Laranjas *.5 para junho, julho e agosto.

  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.

  1. Na barra de fórmulas (barra de fórmulas: uma barra na parte superior da janela do Excel usada para inserir ou editar valores ou fórmulas em células ou gráficos. Exibe o valor constante ou a fórmula armazenada na célula ativa.), digite as alterações para a fórmula.
  1. Se tiver vários itens ou fórmulas calculados, ajuste a ordem do cálculo, seguindo o procedimento abaixo:
    1. Clique no relatório de tabela dinâmica.
    2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Ordem de Resolução.
    3. Clique em uma fórmula e, em seguida, clique em Mover para Cima ou Mover para Baixo.
    4. Continue até que as fórmulas estejam na ordem de cálculo desejada.

Início da página Início da página

Excluir uma fórmula de Tabela Dinâmica

 Dica   Se você não desejar excluir uma fórmula permanentemente, você poderá ocultar o campo ou o item. Para ocultar um campo, arraste-o para fora do relatório.

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

Os campos calculados são exibidos na lista de campos da tabela dinâmica. Os itens calculados aparecem como itens (item: uma subcategoria de um campo nos relatórios de tabela dinâmica e gráfico dinâmico. Por exemplo, o campo "Mês" poderia ter itens como "Janeiro," "Fevereiro" e assim por diante.) dentro de outros campos.

  1. Siga um destes procedimentos:

Excluir um campo calculado    

  1. Clique no relatório de tabela dinâmica.
  2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Campo Calculado.
  3. Na caixa Nome, selecione no campo que deseja excluir.
  4. Clique em Excluir.

Excluir um item calculado    

  1. Clique no campo que contém o item que você deseja excluir.
  2. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Item Calculado.
  3. Na caixa Nome, selecione no item que deseja excluir.
  4. Clique em Excluir.

Início da página Início da página

 
 
Aplica-se a:
Excel 2007