Calcular valores numa tabela dinâmica

Para calcular valores numa tabela dinâmica, pode utilizar um ou todos os seguintes tipos de métodos de cálculo:

• Funções de resumo em campos de valores:    os dados na área de valores resumem os dados de origem subjacentes da tabela dinâmica. Por exemplo, os seguintes dados de origem:

• Produz as seguintes tabelas dinâmicas e gráficos dinâmicos. Se criar um gráfico dinâmico a partir dos dados numa tabela dinâmica, os valores nesse gráfico dinâmico refletem os cálculos no relatório de tabela dinâmica associado.

• Na 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, Este e Oeste. O valor na intersecção da coluna Abril e da linha Norte é a receita total de vendas dos registos nos dados de origem com os valores de MêsAbril e os valores de RegiãoNorte.

• Num gráfico dinâmico, o campo Região pode ser um campo de categoria que apresenta Norte, Sul, Este 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érie representada na legenda. Um campo Valores denominado Soma das Vendas pode conter marcadores de dados que representam a receita total de cada região para cada mês. Por exemplo, um marcador de dados representaria, pela sua posição no eixo vertical (valor), o total de vendas para Abril na região Norte.

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

  Função	Resume
  Soma	A soma de valores. Esta é a função predefinida para os dados numéricos.
  Contar	O número de valores de dados. A função de resumo Contar funciona da mesma forma que a função CONTAR.VAL. A função Contar é a função predefinida para os dados que não sejam números.
  Média	A média dos valores.
  Máx	O valor mais alto.
  Mín	O valor mais baixo.
  Produto	O produto dos valores.
  Contar Números	O número de valores de dados que são números. A função de resumo Contar Números funciona da mesma forma que a função CONTAR.
  DesvPad	Uma estimativa do desvio padrão de uma população, em que a amostra é um subconjunto da população inteira.
  DesvPadP	O desvio padrão de uma população, em que a população é a totalidade dos dados a resumir.
  Var	Uma estimativa da variância de uma população, em que a amostra é um subconjunto da população inteira.
  VarP	A variância de uma população, em que a população é a totalidade dos dados a resumir.

• Cálculos personalizados    Um cálculo personalizado apresenta os valores com base noutros itens ou células na área de dados. Por exemplo, pode apresentar valores no campo de dados Soma das Vendas como uma percentagem das vendas de Março ou como um total corrente dos itens no campo Mês.

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

  Função	Resultado
  Sem Cálculo	Apresenta o valor introduzido no campo.
  % do Total Geral	Apresenta os valores como percentagem do total geral de todos os valores ou pontos de dados no relatório.
  % do Total da Coluna	Apresenta todos os valores em cada coluna ou série como uma percentagem do total da coluna ou série.
  % do Total da Linha	Apresenta o valor em cada linha ou categoria como uma percentagem do total da linha ou categoria.
  % de	Apresenta os valores como a percentagem do valor do Item base no Campo base.
  % do Total da Linha Principal	Calcula os valores da seguinte forma: (valor do item) / (valor do item principal nas linhas)
  % do Total da Coluna Principal	Calcula os valores da seguinte forma: (valor do item) / (valor do item principal nas colunas)
  % do Total Principal	Calcula os valores da seguinte forma: (valor do item) / (valor do item principal do Campo base selecionado)
  Diferença em Relação a	Apresenta os valores como a diferença do valor do Item base no Campo base.
  % de Diferença em Relação a	Apresenta os valores como a diferença de percentagem do valor do Item base no Campo base.
  Total Corrente em	Apresenta o valor de itens sucessivos no Campo base como um total corrente.
  % do Total Corrente em	Calcula o valor de itens sucessivos no Campo base apresentados como um total corrente como percentagem.
  Classificar do Menor para o Maior	Apresenta a classificação dos valores selecionados num campo específico, listando o menor item no campo como 1 e cada valor maior com um valor de classificação superior.
  Classificar do Maior para o Menor	Apresenta a classificação dos valores selecionados num campo específico, listando o item maior no campo como 1 e cada valor menor com um valor de classificação superior.
  Índice	Calcula os valores da seguinte forma: ((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 pretende, pode criar as suas próprias fórmulas em campos calculados e itens calculados. Por exemplo, pode adicionar um item calculado com a fórmula da comissão de vendas, que pode ser diferente em cada região. Dessa forma, o relatório incluirá automaticamente a comissão nos subtotais e nos totais gerais.

Os cálculos e as opções disponíveis num relatório dependem de se a origem de dados veio de uma base de dados OLAP ou de uma origem de dados não OLAP.

• Cálculos com base nos dados de origem OLAP:    para as tabelas dinâmicas criadas a partir de cubos OLAP, os valores resumidos são pré-calculados no servidor OLAP antes de o Excel apresentar os resultados. Não pode alterar a forma como estes valores pré-calculados são calculados na tabela dinâmica. Por exemplo, não pode alterar a função de resumo utilizada para calcular campos de dados ou subtotais nem adicionar campos calculados ou itens calculados.

  Além disso, se o servidor OLAP fornecer campos calculados, conhecidos como membros calculados, verá estes campos na Lista de Campos da Tabela Dinâmica. Verá também os campos calculados e os itens calculados criados por macros escritas em Visual Basic for Applications (VBA) e armazenadas no seu livro, mas não poderá alterar estes campos ou itens. Se precisar de tipos de cálculos adicionais, contacte o administrador da base de dados OLAP.

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

• Cálculos com base em dados de origem não OLAP:    nas tabelas dinâmicas baseadas noutros tipos de dados externos ou em dados da folha de cálculo, o Excel utiliza a função de resumo Soma para calcular campos de valores com dados numéricos e a função de resumo Contar para calcular campos de dados com texto. Pode escolher uma função de resumo diferente, por exemplo Média, Máximo ou Mínimo, para analisar e personalizar ainda mais os seus dados. Também pode criar as suas próprias fórmulas que utilizem elementos do relatório ou outros dados de folha de cálculo ao criar um campo calculado ou um item calculado num campo.

Só pode criar fórmulas em relatórios baseados em dados de origem não OLAP. Não pode utilizar fórmulas em relatórios baseados numa base de dados OLAP. Quando utilizar fórmulas em tabelas dinâmicas, deve conhecer as seguintes regras de sintaxe e o comportamento das fórmulas:

• Elementos de fórmulas de tabela dinâmica    Nas fórmulas que criar para itens calculados e campos calculados, pode utilizar operadores e expressões tal como faria noutras fórmulas de folha de cálculo. Pode utilizar constantes e referenciar dados do relatório, mas não pode utilizar referências de célula ou nomes definidos. Não pode utilizar funções de folha de cálculo que necessitem de referências de célula ou nomes definidos como argumentos, nem utilizar funções de matriz.

• Nomes de campos e itens    O Excel utiliza nomes de campos e itens para identificar esses elementos de um relatório nas suas fórmulas. No exemplo seguinte, os dados no intervalo C3:C9 estão a utilizar o nome de campo Laticínios. Um item calculado no campo Tipo que calcule as vendas de um novo produto com base nas vendas de Laticínios pode utilizar uma fórmula, como =Laticínios * 115%.

  

  Nota: Num gráfico dinâmico, os nomes de campo são apresentados na lista de campos da tabela dinâmica e os nomes de itens encontram-se na lista pendente de cada campo. Não confunda estes nomes com aqueles que vê nas sugestões do gráfico, os quais refletem nomes de séries e de pontos de dados.

• As fórmulas operam em totais de somas e não em registos individuais    As fórmulas dos campos calculados operam na soma dos dados subjacentes para todos os 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; não multiplica cada venda individual por 1,2, somando depois os montantes multiplicados.

  As fórmulas de itens calculados operam nos registos individuais. Por exemplo, a fórmula de item calculado =Laticínios * 115% multiplica cada venda individual de Laticínios por 115% e os montantes multiplicados são depois resumidos na área Valores.

• Espaços, números e símbolos em nomes    Num 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'. Utilize aspas simples em redor dos nomes que tenham mais de uma palavra ou que inclua números ou símbolos.

• Totais    As fórmulas não podem referenciar totais (tais como Total de Março, Total de Abril e Total Geral no exemplo).

• Nomes de campo em referências de itens    Pode incluir o nome do campo numa referência a um item. O nome do item tem de estar entre parênteses retos, por exemplo Região[Norte]. Utilize este formato para evitar erros #NOME? quando dois itens em dois campos diferentes num relatório têm o mesmo nome. Por exemplo, se um relatório tiver um item com o nome Carne no campo Tipo e outro item com o nome Carne no campo Categoria, pode evitar erros #NOME? ao referenciar os itens como Tipo[Carne] e Categoria[Carne].

• Referenciar itens por posição    Pode referenciar um item pela sua posição no relatório, conforme ordenado e apresentado atualmente. Tipo[1] é Laticínios e Tipo[2] é Marisco. O item referenciado desta forma poderá mudar sempre que as posições dos itens forem alteradas ou quando forem apresentados ou ocultados itens diferentes. Os itens ocultos não são contabilizados neste índice.

  Pode utilizar posições relativas para referenciar itens. As posições são determinadas relativamente ao item calculado que contém a fórmula. Se Sul for a região atual, Região[-1] é Norte; se Norte for a região atual, Região[+1] é Sul. Por exemplo, um item calculado pode utilizar a fórmula =Região[-1] * 3%. Se a posição que atribuir for antes do primeiro item ou depois do último item no campo, a fórmula resultará num erro #REF! .

Para utilizar fórmulas num gráfico dinâmico, pode criar as fórmulas na tabela dinâmica associada, onde pode ver os valores individuais que constituem os seus dados e, assim, poderá ver os resultados graficamente no gráfico dinâmico.

Por exemplo, o seguinte gráfico dinâmico apresenta as vendas de cada vendedor por região:

Para ver quais seriam os valores se as vendas fossem aumentadas em 10 por cento, pode criar um campo calculado na tabela dinâmica associada que utilize uma fórmula como =Vendas * 110%.

O resultado aparecerá imediatamente no gráfico dinâmico, conforme mostrado no seguinte gráfico:

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

O gráfico resultante teria o seguinte aspeto:

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