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.

Antes de começar, decida se quer um campo calculado ou um item calculado num campo. Utilize um campo calculado quando quiser utilizar os dados de outro campo na sua fórmula. Utilize um item calculado quando quiser que a fórmula utilize os dados de um ou mais itens específicos num campo.

Para itens calculados, pode introduzir fórmulas diferentes célula a célula. Por exemplo, se um item calculado com o nome ContaLaranjas tiver uma fórmula de =Laranjas *.25 em todos os meses, pode alterar a fórmula para =Laranjas *.5 em junho, julho e agosto.

Se tiver múltiplos itens calculados ou fórmulas, pode ajustar a ordem de cálculo.

Adicionar um campo calculado

1. Clique na tabela dinâmica.

   Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

2. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Campo Calculado.

   

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

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

   Para utilizar 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, pode introduzir = Vendas * 15%.

5. Clique em Adicionar.

Adicionar um item calculado a um campo

1. Clique na tabela dinâmica.

   Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

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

   

3. Clique no campo onde pretende adicionar o item calculado.

4. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Item Calculado.

   

5. Na caixa de diálogo Nome, escreva um nome para o item calculado.

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

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

7. Clique em Adicionar.

Introduzir fórmulas diferentes célula a célula para itens calculados

1. Clique na célula cuja fórmula quer alterar.

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

2. Na barra de fórmulas, escreva as alterações à fórmula.

Ajustar a ordem de cálculo de múltiplas fórmulas ou itens calculados

1. Clique na tabela dinâmica.

   Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

2. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Ordem de Resolução.

   

3. Clique numa fórmula e, em seguida, clique em Mover Para Cima ou Mover Para Baixo.

4. Continue até que as fórmulas estejam na ordem pela qual quer que sejam calculadas.

Pode ver uma lista de todas as fórmulas utilizadas na tabela dinâmica atual.

1. Clique na tabela dinâmica.

   Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

2. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Listar Fórmulas.

   

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

Para itens calculados, pode editar fórmulas individuais de células específicas de um item calculado. Por exemplo, se um item calculado com o nome CalcLaranjas tiver a fórmula =Laranjas *.25 em todos os meses, pode alterar a fórmula para =Laranjas *.5 em junho, julho e agosto.

Determinar se uma fórmula está num campo calculado ou num item calculado

1. Clique na tabela dinâmica.

   Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

2. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Listar Fórmulas.

   

3. Na lista de fórmulas, localize a fórmula que pretende alterar em Campo Calculado ou Item Calculado.
   
   Quando existem múltiplas fórmulas para um item calculado, a fórmula predefinida introduzida quando o item foi criado tem o nome do item calculado na coluna B. No caso das fórmulas adicionais de um item calculado, a coluna B contém o nome do item calculado e os nomes dos itens de interseção.

   Por exemplo, pode ter uma fórmula predefinida para um item calculado com o nome OMeuItem e outra fórmula para este item identificada como Vendas de Janeiro de OMeuItem. Na tabela dinâmica, deverá encontrar esta fórmula na célula Vendas da linha OMeuItem e na coluna Janeiro.

4. Siga um dos seguintes métodos de edição para continuar.

Editar uma fórmula de campo calculado

1. Clique na tabela dinâmica.

   Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

2. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Campo Calculado.

   

3. Na caixa Nome, selecione o campo calculado cuja fórmula quer alterar.

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

5. Clique em Modificar.

Editar uma única fórmula de um item calculado

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

2. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, 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 na célula cuja fórmula quer alterar.

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

2. Na barra de fórmulas, escreva as alterações à fórmula.

   Sugestão: Se tiver múltiplos itens calculados ou fórmulas, pode ajustar a ordem de cálculo. Para obter mais informações, consulte Ajustar a ordem de cálculo de múltiplas fórmulas ou itens calculados.

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

   Os campos calculados são apresentados na Lista de Campos de Tabela Dinâmica. Os itens calculados são apresentados como itens noutros campos.

2. Siga um dos seguintes procedimentos:

   • Para eliminar um campo calculado, clique em qualquer parte da tabela dinâmica.

   • Para eliminar um item calculado, na tabela dinâmica, clique no campo que contém o item que pretende eliminar.

     Esta ação apresenta as Ferramentas de Tabela Dinâmica, o que adiciona os separadores Analisar e Estrutura.

3. No separador Analisar, no grupo Cálculos, clique em Campos, Itens e Conjuntos e, em seguida, clique em Campo Calculado ou Item Calculado.

   

4. Na caixa Nome, selecione o campo ou o item que pretende eliminar.

5. Clique em Eliminar.