Agregações em fórmulas
Este tópico apresenta as agregações e fornece uma visão geral dos tipos de agregações possíveis com o PowerPivot para Excel. O PowerPivot para Excel contém estas ferramentas para compilar agregações:
É possível compilar Tabelas Dinâmicas e Gráficos Dinâmicos com base em dados PowerPivot. Tabelas Dinâmicas do Excel são uma ferramenta conhecida para agrupar e resumir dados em planilhas. O PowerPivot é integrado a recursos da Tabela Dinâmica no Excel e oferece muitos aprimoramentos.
É possível usar o idioma da fórmula DAX para criar agregações personalizadas. O DAX pode ser usado para criar colunas calculadas em tabelas do PowerPivot e criar medidas em Tabelas Dinâmicas e Gráficos Dinâmicos.
A seção final deste tópico fornece links para obter informações mais detalhadas sobre como compilar agregações.
Introdução às agregações
As agregações são um modo de recolher, resumir ou agrupar dados. Quando você começa com dados raw de tabelas ou outras fontes de dados, eles costumam ser simples, o que significa haver muitos detalhes, mas não organizados ou agrupados de nenhuma forma. Essa falta de resumos ou de estrutura pode dificultar a descoberta de padrões nos dados. Por isso, uma parte importante do trabalho do analista é definir agregações que simplifiquem, separem ou resumam padrões em resposta a uma pergunta comercial específica.
Escolhendo os grupos para agregação
Ao agregar dados, você os agrupa por atributos como produto, preço, região ou data e, em seguida, define uma fórmula que funciona em todos os dados do grupo. Por exemplo, quando você cria um total para um ano, está criando uma agregação. Se, em seguida, você criar uma taxa deste ano sobre o ano anterior e apresentá-la como percentual, a agregação será de um tipo diferente.
A decisão de como agrupar os dados é orientada pela pergunta comercial. Por exemplo, as agregações podem responder às seguintes perguntas:
Contagens Quantas transações estavam lá em um mês?
Médias Quais foram as vendas médias deste mês, por vendedor?
Valores mínimo e máximo Quais cinco distritos de vendas estavam em primeiro lugar em termos de unidades vendidas?
Para criar um cálculo que responda essas perguntas, você deve ter dados detalhados que contenham os números a serem contados ou somados, e esses dados numéricos devem estar relacionados de alguma forma aos grupos que você usará para organizar os resultados.
Se os dados ainda não contiverem valores que seja possível usar para agrupamento, como uma categoria de produto ou o nome da região geográfica onde a loja está localizada, você talvez queira introduzir grupos nos dados, adicionando categorias. Ao compilar grupos no Excel, você deve digitar ou selecionar manualmente os grupos que deseja usar dentre as colunas da planilha.
No entanto, em um sistema relacional, hierarquias como categorias de produtos costumam ser armazenadas em uma tabela diferente da tabela de fatos ou valores. Normalmente, a tabela de categoria é vinculada aos dados de fato por algum tipo de chave. Por exemplo, suponhamos que você descubra que os dados contêm IDs de produto, mas não nomes de produto ou categorias. Para adicionar a categoria a uma planilha do Excel simples, você precisaria copiar a coluna que apresentasse os nomes de categoria. No entanto, em uma pasta de trabalho PowerPivot, é possível importar a tabela de categorias de produto, criar uma relação entre a tabela com os dados numéricos e a lista de categorias de produto e, em seguida, usar as categorias para agrupar dados. Para obter mais informações, consulte Relações entre tabelas.
Escolhendo uma função para agregação
Depois que você tiver identificado e adicionado os agrupamentos a serem usados, deverá decidir quais funções matemáticas serão usadas na agregação. Normalmente, a palavra agregação é usada como um sinônimo nas operações matemáticas ou estatísticas usadas em agregações, como somas, médias, mínimo ou contagens. No entanto, o PowerPivot para Excel permite criar fórmulas personalizadas para agregação, além das agregações padrão encontradas no Excel.
Por exemplo, com o mesmo conjunto de valores e os agrupamentos que foram usados nos exemplos anteriores, você poderia criar agregações personalizadas que respondessem as seguintes perguntas:
Contagens filtradas Quantas transações estavam lá em um mês, exceto a janela de manutenção de final de mês?
Taxas que usam médias com o passar do tempo Qual foi o percentual de aumento ou diminuição de vendas em comparação ao mesmo período do ano passado?
Valores mínimo e máximo agrupados Quais distritos de vendas foram classificados como primeiros em cada categoria de produto e em cada promoção de vendas?
Adicionando agregações a fórmulas e Tabelas Dinâmicas
Quando você tem uma ideia geral de como seus dados devem ser agrupados para serem significativos, e os valores com que deseja trabalhar, pode criar uma Tabela Dinâmica ou criar cálculos dentro de uma tabela. O PowerPivot para Excel estende e melhora a capacidade original do Excel para criar agregações como somas, contagens ou médias. Você pode criar agregações personalizadas no PowerPivot dentro da janela do PowerPivot, ou dentro da área Tabela Dinâmica do Excel.
Em uma coluna calculada, você pode criar agregações que levem em conta o contexto da linha atual para recuperar linhas relacionadas de outra tabela e, em seguida, efetuar a soma, a contagem ou a média desses valores nas linhas relacionadas.
Em uma medida, é possível criar agregações dinâmicas que usem os filtros definidos na fórmula e os filtros impostos pelo design da Tabela Dinâmica e a seleção de Segmentações de Dados, títulos de coluna e títulos de linha.
Para obter mais informações, consulte Criar fórmulas para cálculos.
Adicionando agrupamentos a uma Tabela Dinâmica
Ao criar uma Tabela Dinâmica, você arrasta campos que representam agrupamentos, categorias ou hierarquias para a seção de colunas e linhas da Tabela Dinâmica a fim de agrupar os dados. Em seguida, você arrasta campos que contêm valores numéricos para a área de valores, a fim de que eles sejam contados, somados e passem pelo cálculo da média.
Se adicionar categorias a uma Tabela Dinâmica, mas os dados da categoria não forem relacionados aos dados de fato, você poderá obter um erro ou resultados peculiares. Normalmente, o PowerPivot para Excel tentará corrigir o problema, fazendo a detecção automática e sugerindo relações. Para obter mais informações, consulte Trabalhar com relações em Tabelas Dinâmicas.
Você também pode arrastar campos para Segmentações de Dados a fim de selecionar determinados grupos para exibição. As Segmentações de Dados são um novo recurso no Excel e no PowerPivot para Excel que permite a você agrupar, classificar e filtrar interativamente os resultados em uma Tabela Dinâmica.
Trabalhando com agrupamentos em uma fórmula
Você também pode usar agrupamentos e categorias para agregar dados armazenados em tabelas, criando relações entre tabelas e, em seguida, criando fórmulas que aproveitam essas relações para pesquisar valores relacionados.
Em outra palavras, se quisesse criar uma fórmula que agrupasse valores por categoria, você primeiro usaria uma relação para conectar a tabela que contivesse os dados detalhados e as tabelas que contivessem as categorias e, em seguida, compilaria a fórmula.
Para obter mais informações sobre como compilar fórmulas que usam pesquisas, consulte Relações e pesquisas em fórmulas.
Usando filtros em agregações
Um novo recurso do PowerPivot é a possibilidade de aplicar filtros a colunas e tabelas de dados, não apenas na interface do usuário e em uma Tabela Dinâmica ou um gráfico, mas também nas mesmas fórmulas que você usa para calcular agregações. Os filtros podem ser usados em fórmulas tanto em colunas calculadas quanto em medidas.
Por exemplo, nas novas funções de agregação DAX, em vez de especificar valores sobre os quais somar ou contar, você poderá especificar uma tabela inteira como argumento. Se você não aplicar nenhum filtro a essa tabela, a função de agregação funcionará com base em todos os valores da coluna especificada da tabela. No entanto, no DAX, você pode criar um filtro dinâmico ou estático na tabela, a fim de que a agregação opere com base em um subconjunto de dados diferente que dependa da condição de filtro e do contexto atual.
Combinando-se condições e filtros em fórmulas, é possível criar agregações que mudem dependendo dos valores fornecidos nas fórmulas ou que mudem dependendo da seleção de títulos de linha e títulos de coluna em uma Tabela Dinâmica.
Para obter mais informações, consulte Filtrar dados em fórmulas.
Comparação de funções de agregação do Excel e funções de agregação do DAX
A tabela seguinte lista algumas das funções de agregação padrão fornecidas pelo Excel e fornece links para a implementação dessas funções no PowerPivot para Excel. A versão DAX dessas funções têm comportamento bem parecido com o da versão do Excel, com algumas diferenças mínimas na sintaxe e na manipulação de determinados tipos de dados.
Funções de agregação padrão
Função |
Uso |
Retorna a média (aritmética) de todos os números de uma coluna. |
|
Retorna a média (aritmética) de todos os valores de uma coluna. Manipula texto e valores não numéricos. |
|
Conta o número de valores numéricos de uma coluna. |
|
Conta o número de valores de uma coluna que não está vazia. |
|
Retorna o maior valor numérico de uma coluna. |
|
Retorna o maior valor de um conjunto de expressões avaliadas em uma tabela. |
|
Retorna o menor valor numérico de uma coluna. |
|
Retorna o menor valor de um conjunto de expressões avaliadas em uma tabela. |
|
Adiciona todos os números de uma coluna. |
Funções de agregação DAX
O DAX inclui funções de agregação que permitem especificar uma tabela na qual a agregação será executada. Portanto, em vez de apenas adicionar ou calcular a média dos valores de uma coluna, essas funções permitem criar uma expressão que define dinamicamente os dados a serem agregados.
A tabela a seguir lista as funções de agregação disponíveis no DAX.
Função |
Uso |
Calcula a média de um conjunto de expressões avaliadas em uma tabela. |
|
Conta um conjunto de expressões avaliadas em uma tabela. |
|
Conta o número de valores em branco em uma coluna. |
|
Conta o número total de linhas em uma tabela. |
|
Conta o número de linhas retornadas de uma função de tabela aninhada, como a função de filtro. |
|
Retorna a soma de um conjunto de expressões avaliadas em uma tabela. |
Diferenças entre funções de agregação do DAX e do Excel
Embora essas funções tenham os mesmos nomes que seus equivalentes do Excel, elas usam o mecanismo VertiPaq do PowerPivot e foram reescritas para funcionarem com tabelas e colunas. Não é possível usar uma fórmula DAX em uma pasta de trabalho do Excel e vice-versa. Elas só podem ser usadas na janela do PowerPivot e em Tabelas Dinâmicas baseadas nos dados do PowerPivot. Além disso, embora as funções tenham nomes idênticos, o comportamento pode ser um pouco diferente. Para obter mais informações, consulte cada tópico de referência da função.
A maneira como as colunas são avaliadas em uma agregação também é diferente da maneira como o Excel manipula as agregações. Um exemplo pode ajudar a ilustrar.
Suponha que você queira obter uma soma dos valores da coluna Amount da tabela Sales e cria a seguinte fórmula:
=SUM('Sales'[Amount])
No caso mais simples, a função obtém os valores de uma única coluna não filtrada e o resultado é o mesmo do Excel, que sempre apenas adiciona os valores na coluna Amount. Porém, no PowerPivot, a fórmula é interpretada como "Obter o valor em Amount para cada linha da tabela Sales e somar esses valores individuais". O PowerPivot avalia cada linha na qual a agregação é executada, calcula um único valor escalar para cada linha e depois executa uma agregação nesses valores. Por isso, o resultado de uma fórmula poderá ser diferente se filtros forem aplicados a uma tabela, ou se os valores forem calculados com base em outras agregações que possam ser filtradas. Para obter mais informações, consulte Contexto em fórmulas DAX.
Funções de inteligência de tempo DAX
Além das novas funções de agregação da tabela descritas na seção anterior, o DAX tem funções de agregação que funcionam com datas e horas especificadas por você, para fornecer inteligência de tempo interna. Essas funções usam intervalos de datas para relacionar e agregar os valores. Você também pode comparar valores em intervalos de datas.
A tabela a seguir lista as funções de inteligência de tempo que podem ser usadas para agregação.
Função |
Uso |
Calcula um valor ao final do calendário do período determinado. |
|
Calcula um valor ao final do calendário do período anterior ao período fornecido. |
|
Calcula um valor no intervalo que se inicia no primeiro dia do período e termina na última data da coluna de data especificada. |
As outras funções na seção Função de inteligência de tempo (Funções de inteligência de dados temporais (DAX)) são funções que podem ser usadas para recuperar datas ou intervalos personalizados de datas a serem usados na agregação. Por exemplo, é possível usar a função DATESINPERIOD para retornar um intervalo de datas e usa esse conjunto de datas como um argumento para outra função a fim de calcular uma agregação personalizada apenas para essas datas.
Consulte também
Conceitos
Relações e pesquisas em fórmulas