Guia Completo da Função AGRUPARPOR no Excel

Neste artigo vou mostrar-te algumas técnicas que podes usar para ficar a conhecer tudo o que precisas para usar a função AGRUPARPOR [GROUPBY] no Excel.

A função AGRUPARPOR, juntamente com a função DINAMIZARPOR são duas das funções mais recentes que temos no Excel e permitem de uma forma rápida e simples, criar relatórios “tipo” tabela dinâmica, com a vantagem de estarmos a utilizar fórmulas que permitem atualizar os dados de forma instantânea ao contrário da Tabela Dinâmica que tem de ser atualizada.

Hoje vou mostrar-te a função AGRUPARPOR [GROUPBY], contudo a função DINAMIZARPOR [PIVOTBY] é semelhante, onde apenas deves ter em conta que podes criar campos de colunas também.

Argumentos da Função

Vamos começar com um exemplo básico para recordar os argumentos da função.

• campos_linha [row_fields] -> Intervalo que define o conjunto de valores usados para agrupar o relatório por linhas e gerar os cabeçalhos de linha. Aqui podemos selecionar mais que uma coluna, mas com dados adjacentes.
• valores [values] -> Intervalo que contem os valores a serem calculados / agregados. Mais uma vez podemos incluir várias colunas adjacentes.
• Função [function] -> Uma função LAMBDA ou eta lambda (SOMA, PERCENTAGEMDE, MÉDIA, CONTAR, etc.) que é utilizada para agregar valores.
• Cabeçalhos_de_campo [field_headers] -> indicamos através de um número se incluímos os cabeçalhos de campo (devem ser selecionados em conjunto com os dados):

0: Não

1: Sim e não mostrar

2: Não, mas gerar

3: Sim e mostrar

• Profundidade [total_depth] -> Determina se os cabeçalhos de linha devem conter totais.

0: Sem Totais

1: Totais Gerais

2: Totais Gerais e Subtotais

-1: Totais Gerais no Topo

-2: Totais Gerais e Subtotais no Topo

• Ordenação [sort_order] -> Indicamos um número positivo ou negativo para ordenar ascendente ou descendente a coluna do relatório.

1 / -1 -> Ordenamos a primeira coluna do relatório (nome dos campos) ascendente ou descendente

2 / -2 -> Ordenamos a segunda coluna do relatório (valores) ascendente ou descendente.

• Filtro [filter_array] -> Indicamos uma coluna (intervalo de valores) comparada com um valor booleano para aplicar um filtro no relatório.
• Relação_de_campos[field_relationship] -> Através de um valor (0 ou 1) definimos a relação existente entre campos, quando são fornecidas múltiplas colunas para os campos de linha [row_fields].
  • Existindo hierarquia (0) – A ordenação das colunas de seguintes (2ª e outras) obedece à hierarquia da primeira coluna – campos de linha [row_fields].
  • Sem hierarquia (1) – A ordenação obedece à lógica existente na Tabela (intervalo).

Vamos então ver um exemplo para começar.

=AGRUPARPOR(A3:A33;F3:F33;SOMA;3;1;1;;0)

No exemplo apenas ignorámos o parâmetro do Filtro, que significa que não pretendo retirar nenhum valor do relatório no resultado.

Na imagem em baixo, por exemplo, retirei a marca “Joma” utilizando o Filtro.

=AGRUPARPOR(A3:A33;F3:F33;SOMA;3;1;1;A3:A33<>"Joma";0)

AGRUPARPOR com Hierarquia [Field_relationship]

Agora vamos ver um exemplo com a opção de hierarquia.

=AGRUPARPOR(A3:B33;F3:F33;SOMA;3;0;2;;0)

Agora trocando a lógica da Hierarquia…

=AGRUPARPOR(A3:B33;F3:F33;SOMA;3;0;2;;1)

Agrupar por colunas não adjacentes

Quando produzimos o relatório com a função podemos usar tanto para os campos [row_fields] como para os valores [values] várias colunas, contudo devem ser adjacentes. Não sendo, temos de recorrer a outras funções. Um bom exemplo para este cenário é usar as funções JUNTARV [VSTACK] ou JUNTARH [HSTACK], neste caso a função JUNTARH é a ideal.

=AGRUPARPOR(JUNTARH(A3:A33;C3:C33);JUNTARH(D3:D33;F3:F33);MÉDIA;3;0;1;;0)

Criar relatórios com funções que não existem nas tabelas dinâmicas

Outra das vantagens que temos com a utilização desta função é que temos mais funções de Agregação de Valores. Temos 16 funções à disposição, mais a possibilidade de criar qualquer função com uma LAMBDA. Num relatório de tabela dinâmica temos apenas 11 funções de agregação.

Alguns exemplos:

Calcular a mediana…

=AGRUPARPOR(A3:A33;E3:E33;MED;3;0)

Calcular a Moda…

=AGRUPARPOR(A3:A33;D3:D33;MODO.SIMPLES;3;0)

Criar uma matriz de texto…

=AGRUPARPOR(A3:A33;B3:B33;MATRIZPARATEXTO;3;0)

Cálculos personalizados com Lambdas

Usando uma Lambda, podemos criar outro tipo de cálculo para o grupo. Neste exemplo vou mostrar-te uma variação da lógica do último exemplo, onde definimos a MATRIZPARATEXTO, mas supõe que não pretendo passar todo o texto para uma matriz. Quero definir apenas partes do texto.

No exemplo pretendo agrupar os dados por data e obter apenas o nome do produto retirando a palavra “produto”.

=AGRUPARPOR(A3:A103;B3:B103;LAMBDA(x;UNIRTEXTO(" - ";VERDADEIRO;TEXTODEPOIS(x;" ")));3;0)

Na fórmula temos:

1. Campos: A3:A103
2. Valores: B3:B103
3. Função: LAMBDA
   • Variável – > x

Cálculos da Lambda:

• UNIRTEXTO: Para simular a MATRIZPARATEXTO
• TEXTODEPOIS: Para retirar o texto após a palavra “Produto”

A função LAMBDA assume uma variável (x) que representa o intervalo de valores usado.

No segundo exemplo pretendo agrupar os dados por data e obter a soma do dobro do preço unitário.

=AGRUPARPOR(A3:A103;C3:C103;LAMBDA(Preço;SOMA(Preço*2));3;0)

Na fórmula temos:

1. Campos: A3:A103
2. Valores: C3:C103
3. Função: LAMBDA
   • Variável -> Preço
   • Cálculo da Lambda -> SOMA(Preço*2))

Realizar múltiplos cálculos num único relatório

No último exemplo vamos perceber como podemos realizar vários cálculos num único relatório.

Na verdade, podemos, mas usando o mesmo cálculo para várias colunas “adjacentes” o que pretendo é usar cálculos diferentes, e eventualmente para colunas diferentes também. Tu decides, pois, a técnica é a mesma.

=

JUNTARV({“Produto”\”Totais”\”% Total”};

JUNTARH(AGRUPARPOR(B4:B103;E4:E103;SOMA;0);EXCLUIR(AGRUPARPOR(B4:B103;E4:E103;PERCENTAGEMDE;0);;1)))

Em primeiro lugar vamos perceber como podemos usar cálculos diferentes. E na verdade é simples, basta fazer 2 ou mais cálculos AGRUPARPOR.

De seguida juntamos os vários resultados. Contudo para juntar os vários resultados podemos socorrer-nos de outras funções que nos ajudam a Moldar o Array, que na verdade é o resultado da função AGRUPARPOR. Então a técnica é a seguinte:

1. Criar o número de funções AGRUPARPOR pretendidas, uma para cada cálculo. No caso do exemplo tenho 2, uma para SOMA e outra para PERCENTAGEMDE.
2. Juntamos os vários “Arrays” com a função JUNTARH [HSTACK] a qual já vimos exemplos aqui no artigo.
3. Usamos funções que moldam Arrays, no cenário estou a usar a função EXCLUIR [DROP] Para excluir a primeira coluna da segunda função AGRUPARPOR, pois a coluna dos “Campos” é idêntica em ambas as funções.
4. Apenas para efeitos estéticos, uso a função JUNTARV [VSTACK] para criar um cabeçalho personalizado para o relatório. Este cabeçalho também é um “Array”
   1. Neste cenário como podes ver não estou a incluir os cabeçalhos de campo nas funções AGRUPARPOR.

Como podes verificar podes usar inúmeras soluções criativas para construir os teus relatórios. Como estamos a usar fórmulas, a atualização é direta e instantânea. Se tiveres como base uma Tabela de dados, podes ainda acrescentar linhas à tabela e o relatório atualiza-se instantaneamente!

Ficheiros

Artigos por Categoria

• Microsoft Excel (42)
• Power Apps (16)
• Power Automate (3)
• Power BI (11)
• Power Query (13)
• Python (3)
• VBA (7)

Últimos Artigos

• Funções Essenciais do Power Query: FillDown e ReplaceValue
• Como Mudar a Direção do Filtro em Power BI
• Consolidar Dados no Excel com Python: 3 Métodos Eficazes
• Comparar Intervalos em Excel com NumPy
• Aprende a Usar IMPORTTEXT e IMPORTCSV no Excel