Calcular valores num relatório de Tabela Dinâmica

Nos Relatório de tabela dinâmica, pode utilizar função de resumo em campo de valor para combinar valores da dados de origem 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órmula em campo calculado e em item calculado. 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

Métodos de cálculo disponíveis

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

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

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

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

Adicionar um campo calculado

Adicionar um item calculado a um campo

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

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

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

Editar uma fórmula de Tabela Dinâmica

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

Editar uma fórmula de campo calculado

Editar uma fórmula única de um item calculado

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

Eliminar uma fórmula da Tabela Dinâmica

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 dados de origem da Tabela Dinâmica

  • Produzem os seguintes relatórios de Tabela Dinâmica e de Gráfico Dinâmico. Se criar um relatório de Gráfico Dinâmico a partir dos dados num relatório de Tabela Dinâmica, os valores nesse relatório de Gráfico Dinâmico refletem os cálculos no relatório de tabela dinâmica associado.

Descobrir o Microsoft Publisher

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

  • No relatório da Tabela Dinâmica, o campo da coluna Mês fornece os itemMarç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 com a linha Norte é a receita total das vendas derivada dos registos na origem de dados que têm os valores Mês de Abril e os valores Região de Norte.

  • Num relatório de Gráfico Dinâmico, o campo Região pode ser um campo de categoria que mostre Norte, Sul, Este 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 com o nome Soma das Vendas pode conter marcador de dados que representam as receitas totais de cada região em cada mês. Por exemplo, um marcador de dados representa, dada a sua posição no eixo, as vendas totais de Abril na região Norte.

  • Para calcular os campos de valor, estão disponíveis as seguintes funções de resumo para todos os tipos de origem de dados, exceto para a origem de dados OLAP.

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.

  • Cálculos personalizados    Um cálculo personalizado mostra valores baseados noutros itens ou células na área de dados. Por exemplo, é possível mostrar valores no campo de dados Soma das Vendas como percentagem das vendas de Março ou como total corrente dos itens no campo Mês.

    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

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.

  • Cálculos baseados em origens de dados OLAP     No caso de relatórios de Tabela Dinâmica criados a partir de cálculo personalizadocubo OLAP, os valores resumidos são pré-calculados no servidor OLAP antes de o Excel mostrar os resultados. Não pode alterar a forma como estes valores pré-calculados são calculados em relatórios de 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.

    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 VBA (Visual Basic for Applications) 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

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:

  • Elementos da fórmula de Tabela Dinâmica    Nas fórmulas que criar para campos calculados e itens calculados, pode utilizar operador e expressões tal como utiliza noutras fórmulas de folha de cálculo. Pode utilizar constante e referir-se a dados do relatório, mas não pode utilizar referências de células nem nome definidos. Não pode utilizar funções de folha de cálculo que exijam referências de células ou nomes definidos como argumentos e não pode utilizar funções de matriz.

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

    Ícone adicionar condição

    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

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:

O relatório de Gráfico Dinâmico a apresentar as 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 a apresentar uma subida das vendas de 10% por região

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

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.

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

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

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

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

  5. Clique em Adicionar.

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.

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

    Excel Ribbon Image

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

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

  5. Na caixa Nome, escreva um nome para o item calculado.

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

  7. Clique em Adicionar.

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.

  2. Na barra de fórmulas, introduza as alterações na fórmula.

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.

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

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

  4. Continue até que as fórmulas fiquem pela ordem de cálculo pretendida.

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.

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

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.

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

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

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

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

  3. Na caixa Nome, selecione o campo calculado para o qual pretende alterar a fórmula.

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

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

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

  2. Na barra de fórmulas, 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

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

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

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

    Utilizar sincronização de diretórios para adicionar utilizadores ao Office 365

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

  5. Clique em Eliminar.

Início da Página

Aplica-se a: Excel 2010



As informações foram úteis?

Sim Não

Como podemos melhorá-lo?

255 carateres restantes

Para proteger a sua privacidade, não inclua informações de contacto nos seus comentários. Reveja o nosso política de privacidade.

Obrigado pelos seus comentários!

Recursos de suporte

Alterar idioma