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: