19 Funções em uma só | Aprenda a função AGREGAR

SOMA, MÉDIA, MÁXIMO, MÍNIMO, CONT.NÚM, MAIOR, MENOR… E se uma única função fizesse tudo isso, e ainda soubesse ignorar linhas filtradas e erros? Essa é a função AGREGAR. Dentro dela cabem 19 operações diferentes, escolhidas por um número, mais um segundo número que diz o que deve ficar de fora do cálculo.

Neste guia você vai entender os argumentos da AGREGAR, ver a lista das 19 funções e das opções de exclusão, e aprender os dois usos em que ela brilha: planilhas filtradas e rankings com vários resultados de uma vez.

Caso prefira aprender por vídeo, veja o vídeo abaixo:

O que você vai aprender

• A sintaxe da AGREGAR e o que é cada argumento;
• As 19 operações disponíveis;
• As 8 opções de exclusão (linhas ocultas, erros, subtotais);
• Como fazer cálculos que respeitam o filtro;
• Como trazer o 1º, 2º e 3º maiores valores numa fórmula só.

A planilha da aula está disponível para download logo abaixo do vídeo.

A base do exemplo

Uma base simples de estoque, com as colunas Unidade (a filial), Produto (computador, mouse, celular…) e Quantidade em estoque (111, 118, 63, 105…), nas linhas 2 a 9. A ideia é focar na função, não na base.

A sintaxe

=AGREGAR(núm_função; opções; ref1; [ref2]; …)

Argumento	O que é
núm_função	Qual operação fazer (1 a 19)
opções	O que ignorar no cálculo (0 a 7)
ref1	O intervalo com os valores
ref2 / k	Nas funções 14 a 19, o "k" (a posição, como o 2º maior)

A AGREGAR existe desde o Excel 2010. Ao digitar =AGREGAR(, o Excel mostra a lista de funções e de opções, então não é preciso decorar os números.

As 19 funções

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

As opções: o que ignorar

Opção	Ignora
0 (ou omitida)	Funções SUBTOTAL e AGREGAR aninhadas
1	Linhas ocultas + SUBTOTAL/AGREGAR aninhadas
2	Valores de erro + SUBTOTAL/AGREGAR aninhadas
3	Linhas ocultas, erros e SUBTOTAL/AGREGAR aninhadas
4	Nada
5	Linhas ocultas
6	Valores de erro
7	Linhas ocultas e valores de erro

É essa segunda escolha que faz a AGREGAR valer a pena: com o mesmo cálculo, você decide se as linhas escondidas pelo filtro e as células com erro entram ou não na conta.

Exemplo 1: média que respeita o filtro

Primeiro, a média normal: =MÉDIA(C2:C9) dá 101. Agora com a AGREGAR, função 1 (média), opção 5 (ignorar linhas ocultas):

=AGREGAR(1;5;C2:C9)

Sem filtro, o resultado também é 101. A diferença aparece ao filtrar:

1. Clique na base e pressione Ctrl + Shift + L para ativar o filtro;
2. Na coluna Produto, desmarque "Selecionar tudo" e marque só Computador;
3. A fórmula passa a calcular só a média das linhas visíveis, ou seja, o estoque médio de computadores.

Troque o filtro para Celular e a média muda de novo. Já com a opção 4 (não ignorar nada), o resultado continua 101 qualquer que seja o filtro, porque as linhas ocultas entram na conta.

Dica: deixe a fórmula fora da área que será filtrada (acima da base ou em outra coluna). Se ela estiver numa linha que o filtro esconde, você não vê o resultado.

Exemplo 2: trocar a operação em segundos

Como a operação é só um número, mudar de cálculo é rápido. Pressione F2 para editar e troque o primeiro argumento:

=AGREGAR(9;5;C2:C9)   → soma
=AGREGAR(4;5;C2:C9)   → máximo (143 no exemplo)
=AGREGAR(5;5;C2:C9)   → mínimo

Tudo continua dinâmico: se uma quantidade mudar para 200, o máximo vira 200 na hora.

Exemplo 3: top 3 numa fórmula só

A partir da função 14 (MAIOR), a AGREGAR pede um argumento a mais, o k: qual posição você quer. O 1º maior, o 2º maior, e assim por diante.

=AGREGAR(14;5;C2:C9;1)

Retorna o maior estoque. Para trazer os três primeiros de uma vez, passe uma lista entre chaves no lugar do k:

=AGREGAR(14;5;C2:C9;{1;2;3})

No Excel 365, o resultado se espalha por três células para baixo: o 1º, o 2º e o 3º maiores. Escreva ao lado "1º estoque", "2º estoque", "3º estoque" e você tem um ranking com uma única fórmula. Para os menores, use a função 15 (MENOR).

Duas observações sobre o resultado espalhado:

• As células abaixo precisam estar vazias, senão aparece o erro #DESPEJAR!;
• Só a primeira célula tem a fórmula. Apagar as de baixo não funciona; para remover, apague a primeira.

AGREGAR x SUBTOTAL

A SUBTOTAL também respeita filtros, mas tem 11 funções e não ignora erros. A AGREGAR faz tudo o que ela faz, com 19 funções, opção de ignorar erros e as funções de posição (MAIOR, MENOR, percentis). Se um #N/D ou #DIV/0! no meio da coluna está quebrando sua soma, a AGREGAR com opção 6 resolve:

=AGREGAR(9;6;C2:C9)

Perguntas frequentes

Para que serve a função AGREGAR?

Para fazer 19 cálculos diferentes (soma, média, máximo, maior, entre outros) escolhendo o que ignorar: linhas ocultas, erros ou subtotais.

Como somar só as linhas filtradas?

Use =AGREGAR(9;5;intervalo): função 9 (soma) com opção 5 (ignorar linhas ocultas).

Como somar ignorando erros?

Use =AGREGAR(9;6;intervalo).

Em que versão do Excel a AGREGAR existe?

Desde o Excel 2010. O resultado espalhado com {1;2;3} depende das matrizes dinâmicas do Excel 365.

Conclusão

A AGREGAR junta 19 funções numa só e acrescenta o controle do que entra no cálculo: com a opção 5, os resultados acompanham o filtro; com a 6, erros deixam de quebrar a conta; com MAIOR e {1;2;3}, você monta um top 3 numa fórmula. Baixe a planilha da aula e teste as combinações.

Bons estudos e até o próximo conteúdo! 🚀