Calcular valores num relatório de Tabela Dinâmica

Nos relatórios de Tabela Dinâmica (relatório de tabela dinâmica: relatório interactivo e de referência cruzada do Excel que resume e analisa dados como, por exemplo, registos de base de dados, de várias origens, incluindo origens externas ao Excel.), pode utilizar funções de resumo (função de resumo: tipo de cálculo que combina dados de origem num relatório de tabela dinâmica ou numa tabela de consolidação ou quando são inseridos subtotais automáticos numa lista ou base de dados. As funções de resumo incluem, por exemplo, as funções Soma, Contar e Média.) em campos (campo: num relatório de tabela dinâmica ou de gráfico dinâmico, é uma categoria de dados que deriva de um campo dos dados de origem. Os relatórios de tabela dinâmica têm campos de linha, coluna, página e dados. Os relatórios de gráfico dinâmico têm campos de série, categoria, página e dados.) de valor para combinar valores da origem de dados (dados de origem: lista ou tabela utilizada para criar um relatório de tabela dinâmica ou de gráfico dinâmico. Os dados de origem podem ser provenientes de uma lista ou intervalo do Excel, de uma base de dados ou cubo externos ou de outro relatório de tabela dinâmica.) subjacente. Se as funções de resumo e os cálculos personalizados não fornecerem os resultados pretendidos, pode criar as suas próprias fórmulas (fórmula: sequência de valores, referências de células, nomes, funções ou operadores de uma célula que, em conjunto, produzem um novo valor. Uma fórmula começa sempre por um sinal de igualdade (=).) em campos calculados (campo calculado: campo num relatório de tabela dinâmica ou num relatório de gráfico dinâmico que utiliza uma fórmula criada pelo utilizador. Os campos calculados podem executar cálculos utilizando o conteúdo de outros campos do relatório de tabela dinâmica ou do relatório de gráfico dinâmico.) e em itens calculados (item calculado: item num campo de tabela dinâmica ou campo de gráfico dinâmico que utiliza uma fórmula criada pelo utilizador. Os itens calculados podem executar cálculos utilizando o conteúdo de outros itens no mesmo campo do relatório de tabela dinâmica ou do relatório de gráfico dinâmico.). Por exemplo, pode adicionar um item calculado com a fórmula para as comissões de venda, que podem ser diferentes para as diversas regiões. O relatório da Tabela Dinâmica incluirá, automaticamente, a comissão nos subtotais e nos totais gerais.

O que pretende fazer?


Obter mais informações sobre como calcular valores num relatório de Tabela Dinâmica

Os relatórios de Tabela Dinâmica oferecem formas de calcular dados. Obtenha mais informações sobre os métodos de cálculo disponíveis, como os cálculos são afetados pelo tipo de origem de dados e como utilizar fórmulas em relatórios de Tabela Dinâmica e de Gráfico Dinâmico.

Métodos de cálculo disponíveis

Para calcular valores num relatório de Tabela Dinâmica, pode utilizar um ou todos os tipos de método de cálculo seguintes:

  • Funções de resumo em campos de valor    Os dados na área de valores resumem a origem de dados subjacente no relatório de Tabela Dinâmica. Por exemplo, a origem de dados seguinte:
Exemplo de origem de dados de Tabela Dinâmica
Exemplo de relatório de Tabela Dinâmica Exemplo de relatório de Gráfico Dinâmico
Função Sumário obtido
Soma A soma dos valores. Esta é a função predefinida para dados numéricos.
Contar O número de valores de dados. A função de resumo Contar funciona do mesmo modo que a função CONTAR.VAL. Contar é a função predefinida para 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 do mesmo modo 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.

As funções seguintes 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 Mostra valores como uma percentagem do total geral de todos os valores ou pontos de dados no relatório.
% do Total da Coluna Mostra todos os valores em cada coluna ou série como uma percentagem do total para essa coluna ou série.
% do Total da Linha Apresenta o valor em cada linha ou categoria como uma percentagem do total para essa linha ou categoria.
% De Apresenta os valores como uma percentagem do valor do Item base no Campo base.
% do Total da Linha Principal

Calcula os valores do seguinte modo:

(valor do item) / (valor do item principal nas linhas)

% do Total da Coluna Principal

Calcula os valores do seguinte modo:

(valor do item) / (valor do item principal nas colunas)

% do Total Principal

Calcula os valores do seguinte modo:

(valor do item) / (valor do item principal do Campo base selecionado)

Diferença de Apresenta os valores como a diferença do valor do Item base no Campo base.
% Diferença de Apresenta os valores como a diferença de percentagem do valor do Item base no Campo base.
Total Corrente Em Apresenta o valor para itens sucessivos no Campo base como um total corrente.
% do Total Corrente Em Calcula o valor dos itens sucessivos no Campo base apresentados como total corrente como percentagem.
Classificar do Menor para o Maior Apresenta a classificação dos valores selecionados num campo específico, listando o item menor no campo como 1 e cada valor superior tem um valor de classificação maior.
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 inferior tem um valor de classificação menor.
Índice

Calcula os valores do seguinte modo:

((valor da célula) x (Total Geral dos Totais Gerais)) / ((Total Geral de Linhas) x (Total Geral de Colunas))

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

Início da Página Início da Página

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

Os cálculos e as opções disponíveis num relatório dependem da origem dos dados, isto é, se ela é proveniente de uma base de dados OLAP ou de uma origem de dados não OLAP.

Do mesmo modo, se o servidor OLAP fornecer campos calculados, denominados membros calculados, vai ver esses campos na lista de campos da Tabela Dinâmica. Também vai ver campos calculados ou itens calculados criados por macros escritas em Visual Basic for Aplications (VBA) (VBA: versão de linguagem de macros do Microsoft Visual Basic que é utilizada para programar aplicações baseadas no Windows e que está incluída em diversos programas da Microsoft.) e armazenadas no seu livro, mas não pode alterar esses campos ou itens. Se precisar de mais tipos de cálculos, contacte o administrador da sua base de dados OLAP.

No caso de origens de dados OLAP, pode incluir ou excluir os valores de itens ocultos ao calcular os subtotais e os totais gerais.

  • Cálculos baseados em origens de dados não OLAP    Em relatórios de Tabela Dinâmica baseados noutros tipos de dados externos ou dados da folha de cálculo, o Excel utiliza a função de resumo Soma para calcular campos de valores que contêm dados numéricos e a função de resumo Contar para calcular campos de dados que contêm texto. Pode selecionar uma função de resumo diferente, como, por exemplo, Média, Máx ou Mín, para analisar e personalizar ainda mais os dados. Também é possível criar as suas próprias fórmulas baseadas em elementos do relatório ou noutros dados da folha de cálculo criando um campo calculado ou um item calculado dentro de um campo.

Início da Página Início da Página

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

Só pode criar fórmulas em relatórios que sejam baseados numa origem de dados não OLAP. Não pode utilizar fórmulas em relatórios que sejam baseados em bases de dados OLAP. Quando utilizar fórmulas em relatórios de Tabela Dinâmica, deve conhecer as seguintes regras de sintaxe de fórmulas e o comportamento das fórmulas:

Exemplo de relatório de Tabela Dinâmica

Nota    Num relatório de Gráfico Dinâmico, os nomes dos campos são apresentados na lista de campos da Tabela Dinâmica e poderá ver os nomes dos itens na lista pendente de cada campo. Não confunda estes nomes com os nomes que vê nas descrições do gráfico, que indicam nomes de séries e de pontos de dados.

  • As fórmulas operam sobre totais de somas e não sobre registos individuais    As fórmulas de campos calculados operam sobre a soma dos dados subjacentes de qualquer campo na fórmula. Por exemplo, a fórmula de campo calculado =Vendas * 1,2 multiplica por 1,2 a soma das vendas de cada tipo e região; não multiplica por 1,2 cada venda individual e depois soma os montantes resultantes dessa multiplicação.

As fórmulas dos itens calculados operam sobre os registos individuais. Por exemplo, a fórmula de item calculado =Laticínios * 115% multiplica cada venda individual de Laticínios por 115%, sendo os valores multiplicados resumidos posteriormente na área de valores.

  • Espaços, números e símbolos nos nomes    Nos nomes que incluam mais de um campo, os campos podem estar por qualquer ordem. No exemplo acima, as células C6:D6 podem ser 'Abril Norte' ou 'Norte Abril'. Utilize plicas para delimitar nomes compostos por mais de uma palavra ou que incluem números ou símbolos.
  • Totais    As fórmulas não podem fazer referência aos totais (como, por exemplo, Total de Março, Total de Abril e Total Geral no exemplo).
  • Nomes de campos em referências a itens    Pode incluir o nome do campo numa referência a um item. O nome do item tem de estar em parênteses retos — por exemplo, Região[Norte]. Utilize este formato para evitar erros #NOME! 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 com o nome Carne no campo Categoria, pode evitar erros #NOME! referindo-se aos itens como Tipo[Carne] e Categoria[Carne].
  • Fazer referência a itens por posição    Pode fazer referência a um item pela posição deste no relatório conforme se encontra atualmente ordenado e apresentado. Tipo[1] é Laticínios e Tipo[2] é Marisco. O item referido desta forma pode ser alterado sempre que as posições dos itens forem alteradas ou itens diferentes forem apresentados ou ocultados. Os itens ocultos não são contados neste índice.

Pode utilizar posições relativas para se referir aos 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 estiver antes do primeiro item ou depois do último item no campo, a fórmula resulta num erro #Ref.!.

Início da Página Início da Página

Utilizar fórmulas em relatórios de Gráfico Dinâmico

Para utilizar fórmulas em relatórios de Gráfico Dinâmico, crie-as no relatório de Tabela Dinâmica associado, onde pode ver os valores individuais que compõem os dados e, em seguida, visualizar graficamente os resultados no relatório de Gráfico Dinâmico.

Por exemplo, o relatório de Gráfico Dinâmico seguinte mostra as vendas de cada vendedor por região:

Relatório de Gráfico Dinâmico das vendas de cada vendedor por região

Para saber como ficariam as vendas se aumentassem 10%, poderia criar um campo calculado no relatório de Tabela Dinâmica associado que utilizasse uma fórmula como =Vendas * 110%.

O resultado aparece imediatamente no relatório de Gráfico Dinâmico, conforme ilustrado no gráfico seguinte:

Relatório de Gráfico Dinâmico que mostra as vendas por região aumentadas em 10%

Para visualizar um marcador de dados separado para as vendas da 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 seria:

Relatório de Gráfico Dinâmico com um item calculado.

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

Início da Página Início da Página

Criar fórmulas num relatório de Tabela Dinâmica

Importante    Não pode criar fórmulas num relatório de Tabela Dinâmica que esteja ligado a uma origem de dados OLAP (Online Analytical Processing) (OLAP: tecnologia de base de dados que foi optimizada para efectuar consultas e relatórios em vez de processar transacções. Os dados OLAP são organizados hierarquicamente e armazenados em cubos e não em tabelas.).

Antes de começar, decida se pretende um campo calculado ou um item calculado dentro de um campo. Utilize um campo calculado quando pretender utilizar os dados de outro campo na fórmula. Utilize um item calculado quando pretender que a fórmula utilize dados de um ou mais itens (item: subcategoria de um campo nos relatórios de tabela dinâmica ou de gráfico dinâmico. Por exemplo, o campo "Mês" poderia conter itens, tais como "Janeiro", "Fevereiro" e, assim, sucessivamente.) específicos dentro de um campo.

Para itens calculados, é possível escrever, célula a célula, fórmulas diferentes. Por exemplo, se um item calculado com o nome RegiãoLaranja tiver a fórmula =Laranjas * 0,25 para todos os meses, pode alterar a fórmula para =Laranjas * 0,5 para 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 no relatório de Tabela Dinâmica.

Esta ação apresenta as Ferramentas de Tabela Dinâmica, adicionando os separadores Opções e Estrutura.

  1. No separador Opções, no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Campo Calculado.

Imagem do Friso do Excel

  1. Na caixa Nome, escreva um nome para o campo.
  2. Na caixa Fórmula, escreva 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% sobre cada valor no campo Vendas, pode escrever = Vendas * 15%.

  1. Clique em Adicionar.

Início da Página Início da Página

Adicionar um item calculado a um campo

  1. Clique no relatório de Tabela Dinâmica.

Esta ação apresenta as Ferramentas de Tabela Dinâmica, adicionando os separadores Opções e Estrutura.

  1. Se os itens no campo estiverem agrupados, no separador Opções, no grupo Agrupar, clique em Desagrupar.

Imagem do Friso do Excel

  1. Clique no campo onde deseja adicionar o item calculado.
  2. No separador Opções, clique no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Item Calculado.

Imagem do Friso do Excel

  1. Na caixa Nome, escreva um nome para o item calculado.
  2. Na caixa Fórmula, escreva a fórmula para o item.

Para utilizar os dados de um item numa 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).

  1. Clique em Adicionar.

Início da Página Início da Página

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

  1. Clique numa célula cuja fórmula pretende alterar.

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

  1. Na barra de fórmulas (barra de fórmulas: barra na parte superior da janela do Excel utilizada para introduzir ou editar valores ou fórmulas em células ou gráficos. Apresenta a fórmula ou valor constante armazenados na célula activa.), introduza as alterações na fórmula.

Início da Página Início da Página

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

  1. Clique no relatório de Tabela Dinâmica.

Esta ação apresenta as Ferramentas de Tabela Dinâmica, adicionando os separadores Opções e Estrutura.

  1. No separador Opções, no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Ordem de Resolução.

Imagem do Friso do Excel

  1. Clique numa fórmula e, em seguida, clique em Mover para Cima ou Mover para Baixo.
  2. Continue até que as fórmulas fiquem pela ordem de cálculo pretendida.

Início da Página Início da Página

Ver todas as fórmulas utilizadas num relatório de Tabela Dinâmica

Pode visualizar uma lista de todas as fórmulas utilizadas no relatório de Tabela Dinâmica atual.

  1. Clique no relatório de Tabela Dinâmica.

Esta ação apresenta as Ferramentas de Tabela Dinâmica, adicionando os separadores Opções e Estrutura.

  1. No separador Opções, no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Listar Fórmulas.

Imagem do Friso do Excel

Início da Página Início da Página

Editar uma fórmula de Tabela Dinâmica

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 só existe essa fórmula para o item calculado.

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

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

  1. Clique no relatório de Tabela Dinâmica.

Esta ação apresenta as Ferramentas de Tabela Dinâmica, adicionando os separadores Opções e Estrutura.

  1. No separador Opções, no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Listar fórmulas.

Imagem do Friso do Excel

  1. Na lista de fórmulas, localize a que pretende alterar, listada em Campo Calculado ou Item Calculado.

    Quando existem várias fórmulas para um item calculado, a fórmula predefinida introduzida quando o item (item: subcategoria de um campo nos relatórios de tabela dinâmica ou de gráfico dinâmico. Por exemplo, o campo "Mês" poderia conter itens, tais como "Janeiro", "Fevereiro" e, assim, sucessivamente.) foi criado contém o nome do item calculado na coluna B. Para fórmulas adicionais de um item calculado, a coluna B contém o nome do item calculado e os nomes dos itens intersetados.

Por exemplo, pode ter uma fórmula predefinida de um item calculado com o nome OMeuItem e outra fórmula deste item identificado como OMeuItem Vendas de Janeiro. No relatório de Tabela Dinâmica, encontraria esta fórmula na célula Vendas para a linha OMeuItem e a coluna Janeiro.

  1. Continue utilizando um dos seguintes métodos de edição.

Editar uma fórmula de campo calculado

  1. Clique no relatório de Tabela Dinâmica.

Esta ação apresenta as Ferramentas de Tabela Dinâmica, adicionando os separadores Opções e Estrutura.

  1. No separador Opções, no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Campo Calculado.

Imagem do Friso do Excel

  1. Na caixa Nome, selecione o campo calculado para o qual pretende alterar a fórmula.
  2. Na caixa Fórmula, edite a fórmula.
  3. Clique em Modificar.

Editar uma fórmula única de um item calculado

  1. Clique no campo que contém o item calculado.
  2. No separador Opções, clique no grupo Cálculos, clique em Campos, Itens e Conjuntos e clique em Item Calculado.

Imagem do Friso do Excel

  1. Na caixa Nome, selecione o item calculado.
  2. Na caixa Fórmula, edite a fórmula.
  3. Clique em Modificar.

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

  1. Clique numa célula cuja fórmula pretende alterar.

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

  1. Na barra de fórmulas (barra de fórmulas: barra na parte superior da janela do Excel utilizada para introduzir ou editar valores ou fórmulas em células ou gráficos. Apresenta a fórmula ou valor constante armazenados na célula activa.), introduza as alterações na 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 para múltiplos itens calculados ou fórmulas.

Início da Página Início da Página

Eliminar uma fórmula da Tabela Dinâmica

Nota    Deleting a PivotTable formula removes it permanently. If you do not want to remove a formula permanently, you can hide the field or item instead by dragging it out of the PivotTable report.

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

Os campos calculados aparecem na lista de campos da Tabela Dinâmica. Os itens calculados aparecem como itens (item: subcategoria de um campo nos relatórios de tabela dinâmica ou de gráfico dinâmico. Por exemplo, o campo "Mês" poderia conter itens, tais como "Janeiro", "Fevereiro" e, assim, sucessivamente.) dentro de outros campos.

  1. Execute um dos seguintes procedimentos:
    • Para eliminar um campo calculado, clique em qualquer parte do relatório de Tabela Dinâmica.
    • Para eliminar um campo 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, adicionando os separadores Opções e Estrutura.

  1. No separador Opções, no grupo Cálculos, clique em Campos, Itens, e Conjuntos e, em seguida, clique em Campo calculado ou em Item Calculado.

Imagem do Friso do Excel

  1. Na caixa Nome, selecione o campo ou item que pretende eliminar.
  2. Clique em Eliminar.

Início da Página Início da Página

 
 
Aplica-se a:
Excel 2010