Função Agrupar ou GroupBy Excel

Como usar a função Agrupar ou Groupby no Excel passo-a-passo com vídeo e download gratuito da planilha exemplo.

Como Funciona a Função Agrupar ou GroupBy Excel

Nesta sessão iremos falar dos objetivos, sintaxe e parâmetros da função Agrupar no Excel.

Objetivo: A função AGRUPAR permite que sejam criados sumários de datas com fórmula. Também permite que sejam feitos filtros e classificação baseadas nos campos que especificar.

Sintaxe: =AGRUPAR(row_fields,values,function,[field_headers],[total_depth],[sort_order],[filter_array])

Parâmetros:

• row_fields: Uma matriz ou intervalo orientado a colunas que contém os valores usados ​​para agrupar linhas e gerar cabeçalhos de linha.
• values: Uma matriz ou intervalo orientado a colunas de dados a serem agregados. A matriz ou intervalo pode conter várias colunas. Nesse caso, a saída terá múltiplas agregações.
• function: Um lambda explícito ou eta reduzido (SUM, PERCENTOF, AVERAGE, COUNT, etc.) que é usado para agregar valores. Um vetor de lambdas pode ser fornecido. Nesse caso, a saída terá múltiplas agregações. A orientação do vetor determinará se eles serão dispostos em linhas ou colunas.
• [field_headers]:

Um número que especifica se row_fields e valores têm cabeçalhos e se os cabeçalhos dos campos devem ser retornados nos resultados. Os valores possíveis são:

Ausente : Automático.
0 : Não
1 : Sim e não mostra
2 : Não, mas gera
3 : Sim e mostra

Nota: Automático assume que os dados contêm cabeçalhos com base no argumento de valores. Se o primeiro valor for texto e o segundo valor for um número, presume-se que os dados tenham cabeçalhos. Os cabeçalhos dos campos serão mostrados se houver vários níveis de grupo de linhas ou colunas.

• [total_depth]:

Determina se os cabeçalhos das linhas devem conter totais. Os valores possíveis são:

Ausente : Automático: totais gerais e, sempre que possível, subtotais.
0 : Sem totais
1 : Totais gerais
2 : Totais gerais e subtotais
-1 : Totais gerais no topo
-2 : Totais gerais e subtotais no topo

Nota: Para subtotais, os campos devem ter pelo menos 2 colunas. Números maiores que 2 são suportados, desde que o campo tenha colunas suficientes.

• [sort_order]:

Um número que indica como as linhas devem ser classificadas. Os números correspondem às colunas em row_fields seguidas pelas colunas em values ​​. Se o número for negativo, as linhas serão classificadas em ordem decrescente/inversa.

Um vetor de números pode ser fornecido ao classificar com base apenas em row_fields .

• [filter_array]:

Uma matriz 1D de booleanos orientada a colunas que indica se a linha de dados correspondente deve ser considerada.

Nota: O comprimento da matriz deve corresponder ao comprimento daqueles fornecidos para row_fields .

Básico Função Agrupar

No exemplo abaixo temos uma tabela com as vendas por região, vendedor, data e empresa.

Total de Vendas por Região

No primeiro exemplo estamos fazendo uma soma agrupada das vendas por região.

Na função passamos primeiro qual o campo que queremos ter como sumário para o resultado, em seguida o campo de valor e por último qual o tipo de cálculo, no caso SUM, ou SOMA.

Para isso usamos a seguinte função:

=GROUPBY(tFaturamento[[#All];[Região]];tFaturamento[[#All];[Valor]];SUM)

Veja que o resultado que temos abaixo aonde temos a soma por cada uma das regiões e a soma por cada campo.

Total de Vendas e Classificação com a Função Agrupar Excel

Outro parâmetro interessante que temos na função agrupar é a possibilidade de classificar dados, para isso passamos no parâmetro [sort_order] a coluna de classificação, nele passe o número à partir da primeira coluna do campo de sumário e valores, no nosso exemplo passamos -2, pois com isso temos a classificação do campo de valor do maior para o menor.

Se usar negativo, será em ordem decrescente e se for positivo, será em ordem crescente.

Também é possível passar um vetor de classificação, como {1;2} por exemplo.

Este é o cálculo do nosso exemplo:

=GROUPBY(tFaturamento[[#All];[Região]];tFaturamento[[#All];[Valor]];SUM;;;-2)

Como resultado temos a seguinte matriz, com os valores sumarizados e classificados pelo total do maior para o menor.

GroupBy Excel com Fórmulas por Mês e Ano

No próximo exemplo precisamos fazer um agrupamento dos dados por Mês e Ano.

Para isso podemos usar uma função TEXTO para converter o formato da data para o formato de texto de mês e ano.

A fórmula que usamos é:

=GROUPBY(
TEXT(tFaturamento[[#All];[Data Venda]];"MMm/AA");
tFaturamento[[#All];[Valor]];
SUM;;;1)

Com o uso da função TEXTO para a formatação do campo Data Venda temos a conversão dos dados no formato de mês e ano e com isso o agrupamento por estes campos:

Função Agrupar com Subtotais e Formatação Condicional

A função agrupar permite que você faça resumos com subtotais.

Para isso é necessário que tenham pelo menos duas colunas no parâmetro row_fields. Como temos abaixo aonde colocamos os campos Região e Vendedor.

A função que usamos é a seguinte:

=GROUPBY(tFaturamento[[#All];[Região]:[Vendedor]];
tFaturamento[[#All];[Valor]];
SUM;;2;-2)

Perceba que passamos ali no parâmetro [total_depth] o subtotal por conta do parâmetro 2 passado.

Após isso selecione as linhas à partir do início do resultado e aplique a formatação abaixo aonde verifica se a primeira coluna está preenchida e se a segunda coluna não está preenchida.

Com isso temos uma formatação condicional aplicada em todos os subtotais e totais:

Filtrar na Função GroupBy Agrupar Excel

A função groupby pode ser feita preenchendo de forma semelhante ao que temos na função FILTRO do Excel.

Com isso passamos o parâmetro de filtro da seguinte forma:

=GROUPBY(tFaturamento4[[#All];[Região]:[Vendedor]];
tFaturamento4[[#All];[Valor]];AVERAGE;
3;
2;
1;
(tFaturamento4[[#All];[Vendedor]]=K3))

Veja que no parâmetro de filtro estamos passando (tFaturamento4[[#All];[Vendedor]]=K3), o que resulta em uma matriz de dados com VERDADEIRO e FALSO, sendo considerado portanto somente as linhas com o resultado VERDADEIRO para o agrupamento.

No resultado estamos filtrando à partir da célula em azul que está em K3 e escrita PEDRO.

E como resultado, podemos notar que a matriz retorna apenas os valores correspondentes a este filtro.

Função GroupBy Agrupar Excel

Neste exemplo iremos usar várias funções de agregação para o campo de valor.

Para realizar este cálculo é necessário fazer uso das funções HSTACK ou EMPILHARH e LAMBDA.

A fórmula de agrupamento que usamos foi:

=VSTACK({"Região"\"Soma"\"Mínimo"\"Máximo"};DROP(GROUPBY(tFaturamento3[Região];
tFaturamento3[Valor];
HSTACK(LAMBDA(X;SUM(X));LAMBDA(X;MIN(X));LAMBDA(X;MAX(X))));1))

Na primeira parte passamos para a função VSTACK, ou EMPILHARV para que as linhas sejam empilhadas uma abaixo da outra.

Então agrupamos com as colunas de título seguintes da matriz: {"Região"\"Soma"\"Mínimo"\"Máximo"}

E no parâmetro function, passamos um vetor de funções:

HSTACK(LAMBDA(X;SUM(X));LAMBDA(X;MIN(X));LAMBDA(X;MAX(X))))

Nela fizemos a aplicação de uma matriz usando HSTACK ou EMPILHARH e passamos eles por ponto-e-vírgula.

Cada função LAMBDA foi feita passando um parâmetro X e em seguida a função que será usada SUM ou SOMA, MIN e MAX.

Como resultado temos a aplicação do parâmetro de valor no parâmetro X de cada função LAMBDA e temos o seguinte resultado:

Download Planilha Agrupar ou GroupBy Excel

Clique no botão abaixo para realizar o  download do arquivo de exemplo: