Agregar Excel – Como usar?

https://youtu.be/s1wgDSa7OwcVideo can’t be loaded because JavaScript is disabled: Função Agregar Excel (https://youtu.be/s1wgDSa7Owc)

Neste artigo você aprenderá como usar a função Agregar do Excel.

A função agregar do Excel permite que você realize uma série de operações no Excel e com filtros diversos de restrições.

Esta poderosa função permite que você a use de modo referenciado ou de modo matricial.

Além de permitir opções de filtros que não temos nem na função Subtotal.

Veja detalhes do funcionamento desta função e como você pode usar ela para resolver problemas complexos:

Parâmetros da função Agregar no Excel

A função agregar é composta da seguinte maneira:

=AGREGAR(núm_função;opções;matriz;[k])

Quais os Parâmetros da núm_função do Agregar?

A função agregar está dividida em dois grupos:

FórmulasOpçõesTipo
AGREGAR(núm_função; opções; ref1; ref2; [ref3]; …)1-13Referenciais
AGREGAR(núm_função; opções; matriz; [k])14-19Matriciais

O tipo de grupo Referenciais funciona como a função SOMA, MÉDIA, MAIOR, etc, normalmente.

Ou seja, não conseguirá realizar cálculos para aplicar filtros como temos nos cálculos Matriciais.

E estes grupos estão divididos conforme o seu número de opção nas seguintes fórmulas.

núm_funçãoDescrição
1MÉDIA
2CONT.NÚM
3CONT.VALORES
4MÁXIMO
5MÍNIMO
6MULT
7DESVPAD.A
8DESVPAD.P
9SOMA
10VAR.A
11VAR.P
12MED
13MODO.ÚNICO
14MAIOR
15MENOR
16PERCENTIL.INC
17QUARTIL.INC
18PERCENTIL.EXC
19QUARTIL.EXC

Ao aplicar um destes números no primeiro parâmetro da função Agregar do Excel, o mesmo irá aplicar o funcionamento desta função no conjunto de dados filtrado.

Quais são as Opções de Linhas da Função Agregar?

O segundo parâmetro da função Agregar é opções.

Com ela nós podemos determinar quais valores ignorar no intervalo de dados.

Ela permite que você possa então filtrar os dados ignorando linhas conforme as opções abaixo:

OpçãoDescrição
0Ignorar funções SUBTOTAL e AGREGAÇÃO aninhadas
1Ignorar linhas ocultas, funções SUBTOTAL e AGREGAÇÃO aninhadas
2Ignorar valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas
3Ignorar linhas ocultas, valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas
4Não ignorar nada
5Ignorar linhas ocultas
6Ignorar valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas
7Ignorar linhas ocultas e valores de erro

Ao aplicar o código você terá então as linhas que serão ou não ignoradas no intervalo de dados conforme a opção selecionada.

Estes filtros funcionam para qualquer uma das 19 funções disponíveis em Agregar do Excel.

Exemplo Prático da função Agregar para Intervalos Referenciais

O intervalo referencial como dito é o intervalo como temos no uso da função SOMA, MAIOR e MÍNIMO por exemplo.

Nestas funções de 1-13, você não poderá realizar também o uso de cálculos matriciais, que como veremos, permite filtrar dados, como por exemplo, o maior valor de uma lista, mas apenas para a região Norte.

No exemplo acima, estamos aplicando a função 1 do Agregar, que é a Média.

Veja então o exemplo de uso da função abaixo:

=AGREGAR(1;4;D3:D832)

No exemplo acima, estamos apenas fazendo uma média dos valores, ou seja, não tem nenhuma diferença entre esta aplicação da função AGREGAR e da função MÉDIA.

Agora veja, precisamos por exemplo considerar a média apenas dos valores visíveis da lista e desconsiderando os erros.

Neste caso, mudamos opções para 7 e temos a aplicação do filtro Ignorar linhas ocultas e valores de erro.

Como você pode perceber acima, nós temos dados filtrados e também erros na base de dados da referencia.

Isto causaria um problema na função média e também não funcionaria na subtotal por conta dos erros.

Veja que usando a função Agregar conseguimos contornar esta situação e temos uma média apenas das linhas visíveis e sem erros.

Para aplicar outra função basta você alterar o número da função aplicada no Agregar para termos então o cálculo aplicado.

Aplicar Filtros na Função Agregar com Cálculos Matriciais

Com os números de funções de 14 à 19 você poderá aplicar cálculos matriciais.

Isso significa que você poderá aplicar filtros na base de dados, por exemplo, MAIOR valor de venda para o vendedor QUEDE, no período do mês de janeiro.

Isto considerando ainda que sejam retornados apenas os dados não ignorados pelo parâmetro de opções da função Agregar.

Para realizar esta configuração você deve realizar o filtro dos dados na matriz da seguinte forma.

=AGREGAR($K$2;3;(F3:F832=L6)*
($E$3:$E$832>=$K$6)*
($E$3:$E$832<=FIMMÊS($K$6;0))*
($D$3:$D$832);J3)

No exemplo acima temos:

Na parte dos filtros da matriz teremos então uma multiplicação de 1 e 0, sendo VERDADEIRO = 1, quando a condição for verdadeira e FALSO = 0, quando ela for falsa.

Isto fará com que você tenha no intervalo ($C$10:$C$361=I4) o retorno: {FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;FALSO;VERDADEIRO;FALSO;FALSO;VERDADEIRO;…)

Teremos então (0;0;0;0;1…)*(9960;9922;9812;9526;9467…), ou seja, teremos então o retorno apenas dos valores que atenderem os parâmetros.

Baixe o exemplo abaixo e veja mais em Excel Máximo Ses com condições e função Agregar.

Também veja passo-a-passo na aula deste artigo, no vídeo ao topo.

Download da Planilha Exemplo Agregar Excel

Realize o download da planilha de atualização de eixo Y do gráfico deste artigo no botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Baixe a planilha

Sair da versão mobile