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

No Excel 2010, você pode aproveitar as opções de cálculo aperfeiçoadas.
Experimente o Office 365!

Nos relatórios de tabela dinâmica, você pode usar funções de resumo nos campos de valor para combinar valores dos dados de origem subjacentes. Se as funções de 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 a 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

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

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

Editar uma fórmula de Tabela Dinâmica

Excluir uma fórmula de Tabela Dinâmica

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 em campos de valor    Os dados na área de valores resumem os dados de origem subjacentes no relatório de tabela dinâmica, como no seguinte exemplo:

    Exemplo de dados de origem de Tabela Dinâmica

  • Produz os seguintes relatórios de tabela dinâmica e de Gráfico Dinâmico. Se você criar um relatório de Gráfico Dinâmico usando dados de um relatório de tabela dinâmica, os valores desse relatório de Gráfico Dinâmico refletirão os cálculos no relatório de Tabela Dinâmica associado.

    Exemplo de relatório de Tabela Dinâmica

    Exemplo de relatório de Gráfico Dinâmico

  • No relatório de tabela dinâmica, o campo da coluna Mês fornece 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 os valores de Mês relativos a Abril e os valores de Região relativos ao Norte.

  • Em um relatório de tabela dinâmica, o campo Região pode ser um campo de categoria que mostre Norte, Sul, Leste e Oeste como categorias. O campo Mês pode ser um campo de série que mostre os itens Março, Abril e Maio como séries representadas na legenda. Um campo Valores chamado Somatória de Vendas poderia conter marcadores de dados que representem uma receita total em cada região para cada mês. Por exemplo, um marcador de dados poderia representar, por sua posição no eixo vertical (valor), as vendas totais para Abril na região Norte.

  • Para calcular os campos de valor, as seguintes funções de sumário estão disponíveis para todos os tipos de fontes de dados, exceto dados de fonte OLAP (processamento analítico online).

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.

Média

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.

DesvPp

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.

  • Cálculos personalizados    Um cálculo personalizado mostra valores baseados em outros itens ou células na área de dados. Por exemplo, você poderia exibir valores no campo de dados Somatório de Vendas como uma porcentagem das vendas de Março ou como um total contínuo dos itens no campo Mês.

    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 do 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 do valor do Item base no Campo base.

Soma Acumulada em

Exibe os valores 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 saber mais, confira Alterar a função de resumo ou o cálculo personalizado para 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 as 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

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 do fato de os dados de origem terem vindo de um banco de dados OLAP ou de uma origem de dados não OLAP.

  • Cálculos baseados em dados de origem OLAP    Para relatórios de tabela dinâmica criados de cubos OLAP, os valores resumidos são previamente calculados no servidor OLAP antes de o Excel exibir os resultados. Portanto, você não poderá alterar a forma como esses valores são calculados no relatório de Tabela Dinâmica. Por exemplo, você não poderá alterar a função de resumo usada para calcular campos de dados ou subtotais, ou adicionar campos calculados 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. Verá também campos e itens calculados criados por macros escritos no Visual Basic for Applications e armazenados na sua pasta de trabalho, mas não poderá alterar esses campos ou itens. Se você precisa 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 de itens ocultos ao calcular subtotais e totais gerais.

  • Cálculos com base em dados de origem não OLAP    Em relatórios de tabela dinâmica que se baseiam em outros tipos de dados externos ou em dados de planilha, o Excel utiliza a função de resumo Soma para calcular campos de valor que contêm dados numéricos e a função de resumo Conta para calcular os campos de dados que contêm texto. Você pode escolher uma função de resumo diferente, por exemplo, Média, Máximo ou Mínimo, para personalizar e analisar ainda mais seus dados. Também pode 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

Usar 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:

  • Elementos de fórmula de tabela dinâmica    Nas fórmulas criadas para campos calculados e itens calculados, você pode usar operadores e expressões da mesma maneira que o faz em outras fórmulas de planilha. Você pode usar constantes e referir-se a dados do relatório, mas não pode usar referências de células ou nomes definidos. Não pode também usar funções de planilha que exigem referências de célula ou nomes definidos como argumentos, nem pode usar funções de matriz.

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

    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 em totais de soma, não em 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 poderiam 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    As fórmulas não podem se referir aos totais (como 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ínios 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, 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

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 a aparência das vendas se elas fossem aumentadas 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 o aumento de 10% nas vendas 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:

Relatório de Tabela Dinâmica com um item calculado.

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

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 vinculado a uma origem de dados OLAP.

  1. Decida se você deseja um campo calculado ou um item calculado dentro de um campo.

    • Use um campo calculado quando você desejar usar os dados de outro campo na fórmula.

    • Use um item calculado quando quiser que a fórmula utilize dados de um ou mais itens específicos 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%.

    5. Clique em Adicionar.

      Adicionar um item calculado a um campo    

    6. Se os itens no campo estiverem agrupados, na guia Opções, no grupo Grupo, clique em Desagrupar.

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

    8. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Item Calculado.

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

    10. 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).

    11. Clique em Adicionar.

    12. Se você tiver desagrupado itens na etapa 1, reagrupe-os se desejar. Para saber mais, confira Agrupar itens em um campo de tabela dinâmica.

  3. Para itens calculados, você pode inserir diferentes fórmulas célula a célula.

    Por exemplo, se um item calculado chamado PaísLaranja tiver uma fórmula =Laranjas * 0,25 para todos os meses, você poderá alterar a fórmula para =Laranjas *0,5 para junho, julho e agosto.

    Faça o seguinte:

    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. Na barra de fórmulas, digite as alterações para a fórmula.

  4. 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

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, faça o seguinte:

  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

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 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.

  2. Siga um destes procedimentos:

    Editar uma fórmula de 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 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    

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

    7. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Item Calculado.

    8. Na caixa Nome, selecione no item calculado.

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

    10. Clique em Modificar.

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

      Por exemplo, se um item calculado chamado CalcLaranjas tiver uma fórmula =Laranjas * 0,25 para todos os meses, você poderá alterar a fórmula para =Laranjas *0,5 para junho, julho e agosto.

    11. 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.

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

  3. 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

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 dentro de outros campos.

  2. 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   

    5. Clique no campo com o item que deseja excluir.

    6. Na guia Opções, no grupo Ferramentas, clique em Fórmulas e em Item Calculado.

    7. Na caixa Nome, selecione no item que deseja excluir.

    8. Clique em Excluir.

Início da Página

Essas informações foram úteis?

Ótimo! Outros comentários?

Como podemos melhorá-lo?

Obrigado por seus comentários!

×