Em Tabelas Dinâmicas, pode utilizar funções de resumo em 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 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, a tabela dinâmica incluirá automaticamente a comissão nos subtotais e nos totais gerais.
Outra forma de calcular é utilizar Medidas no Power Pivot, que cria com uma fórmula Data Analysis Expressions (DAX). Para obter mais informações, confira Criar uma Medida no Power Pivot.
As tabelas dinâmicas proporcionam formas de calcular dados. Saiba mais sobre os métodos de cálculo disponíveis, de que forma os cálculos são afetados pelo tipo de dados de origem e como utilizar fórmulas em tabelas dinâmicas e gráficos dinâmicos.
Métodos de cálculo disponíveis
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 valor Os dados na área de valores resumem os dados de origem subjacentes na 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 mostra 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 pretendidos, 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.
Como o tipo de origem de dados afeta os cálculos
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 baseados em dados de origem OLAP Para 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 baseados em dados de origem não OLAP Em Tabelas Dinâmicas baseadas noutros tipos de dados externos ou em dados de folha de cálculo, o Excel utiliza a função sumário soma para calcular campos de valor que contêm dados numéricos e a função de resumo Contar para calcular campos de dados que contêm 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.
Utilizar fórmulas em tabelas dinâmicas
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 cria para campos calculados e itens calculados, pode utilizar operadores e expressões, como faz 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 soma e não em registos individuais As fórmulas para campos calculados operam na soma dos dados subjacentes para quaisquer 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 inclua mais do que 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 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 campos 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]. Utilizar este formato para evitar #NAME? 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 impedir #NAME? ao referir os itens como Tipo[Carne] e Categoria[Carne].
Referenciar itens por posição Pode fazer referência a um item pela respetiva posição no relatório, conforme atualmente ordenado e apresentado. 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! .
Utilizar fórmulas em gráficos dinâmicos
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.
Precisa de mais ajuda?
Pode sempre perguntar a um especialista na Comunidade Tecnológica do Excel ou obter suporte nas Comunidades.