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])
- núm_função : número da função que será aplicada na função agregar. Ela vai da opção 1 até a 19. Sendo 1-13 referenciais e 14-19 matriciais.
- opções : opções de filtragem na aplicação da fórmula. Vão de 0 à 7, permitindo que sejam considerados ou não, todas as linhas, somente visíveis, ignorando funções subtotal e de agregação e valores de erro.
- matriz : é a matriz de células que são consideradas na aplicação da função.
- [k] : número da posição do item, é usado em algumas funções do agregar, como por exemplo na opção MAIOR e MENOR.
Quais os Parâmetros da núm_função do Agregar?
A função agregar está dividida em dois grupos:
| Fórmulas | Opções | Tipo |
|---|---|---|
| AGREGAR(núm_função; opções; ref1; ref2; [ref3]; …) | 1-13 | Referenciais |
| AGREGAR(núm_função; opções; matriz; [k]) | 14-19 | Matriciais |
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ção | Descrição |
|---|---|
| 1 | MÉDIA |
| 2 | CONT.NÚM |
| 3 | CONT.VALORES |
| 4 | MÁXIMO |
| 5 | MÍNIMO |
| 6 | MULT |
| 7 | DESVPAD.A |
| 8 | DESVPAD.P |
| 9 | SOMA |
| 10 | VAR.A |
| 11 | VAR.P |
| 12 | MED |
| 13 | MODO.ÚNICO |
| 14 | MAIOR |
| 15 | MENOR |
| 16 | PERCENTIL.INC |
| 17 | QUARTIL.INC |
| 18 | PERCENTIL.EXC |
| 19 | QUARTIL.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ção | Descrição |
|---|---|
| 0 | Ignorar funções SUBTOTAL e AGREGAÇÃO aninhadas |
| 1 | Ignorar linhas ocultas, funções SUBTOTAL e AGREGAÇÃO aninhadas |
| 2 | Ignorar valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas |
| 3 | Ignorar linhas ocultas, valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas |
| 4 | Não ignorar nada |
| 5 | Ignorar linhas ocultas |
| 6 | Ignorar valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas |
| 7 | Ignorar 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)
- núm_função : 1, no caso Média.
- opções : 4, sem nenhum filtro aplicado nas linhas.
- matriz : D3:D832, intervalo que estamos aplicando a função.
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:
- $K$2 : Código do núm_função que será aplicada, no caso 14. Lembrando que é matricial.
- 3 : Opção de linhas que serão ignoradas, no caso 3 – Ignorar linhas ocultas, valores de erro, funções SUBTOTAL e AGREGAÇÃO aninhadas.
- (F3:F832=L6)*($E$3:$E$832>=$K$6)*($E$3:$E$832<=FIMMÊS($K$6;0))*($D$3:$D$832) : Neste ponto temos as condições. Na primeira é passado se o cliente é igual a QUEDE (F3:F832=L6), no segundo temos a verificação do intervalo de data ($E$3:$E$832>=$K$6)*($E$3:$E$832<=FIMMÊS($K$6;0)) . E por fim, temos o intervalo de valor ($D$3:$D$832).
Para adicionarmos novas condições, basta usarmos os operadoras ><>=<= ou <>, sempre entre parênteses.
Também adicione novas condições com *(E) ou + para (OU). - J3 : Número da ordem da função.
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.