Matricule-se
Voltar para o blog
Excel Avançado

Fórmula AGREGAR no Excel – Mais Poderosa que o PROCV

Diego Monutti

Conheça a fórmula AGREGAR no Excel, uma ferramenta sobre a qual não ouvimos muito, mas que é mais poderosa que o PROCV.

A função AGREGAR no Excel reúne 19 funções (SOMA, MÉDIA, MAIOR, MENOR e outras) em uma única fórmula e permite escolher o que ignorar no cálculo: linhas ocultas, valores de erro ou subtotais aninhados. Por exemplo, =AGREGAR(9;5;C4:C9) soma o intervalo considerando apenas as linhas visíveis do filtro — algo que a função SOMA sozinha não faz.

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!

O que você aprende neste vídeo

Resposta rápida: AGREGAR é uma função 19 em 1: você escolhe o cálculo pelo número (9 = SOMA, 14 = MAIOR, 15 = MENOR) e, no segundo argumento, o que ignorar, linhas ocultas, valores de erro, outros subtotais. É o que a SUBTOTAL faz, mais duas coisas: mais opções de exclusão e, das funções 14 a 19, cálculo matricial.

Neste vídeo (12 min):

  • 1:00 - Ela aparece em duas formas ao ser digitada: a referencial (a comum) e a matricial, que aceita intervalos inteiros.
  • 2:00 - O problema que ela resolve: SOMA continua somando o que o filtro escondeu, o total não muda ao filtrar.
  • 3:00 - Cada cálculo tem um número: ao abrir o parêntese, a lista mostra as 19 opções (9 é SOMA).
  • 3:30 - O segundo argumento é a vantagem sobre a SUBTOTAL: dá para ignorar linhas ocultas, valores de erro, ou os dois.
  • 4:30 - Com a opção 5 (ignorar linhas ocultas), o total passa a acompanhar o filtro.
  • 5:00 - Vale tanto para linha escondida por filtro quanto para linha ocultada à mão.
  • 5:30 - A divisão importa: funções 1 a 13 são referenciais; 14 a 19 aceitam matriz e um argumento k.
  • 6:30 - Com a função 14 (MAIOR), passar {1;2;3} no k devolve os três maiores valores de uma vez.
  • 7:00 - E dá para pular posições: {1;3;5} devolve o 1º, o 3º e o 5º maiores.
  • 8:00 - O truque avançado: menor valor acima da média, com =AGREGAR(15;6;valores/(valores>MÉDIA(valores));1).
  • 9:00 - A lógica: a comparação vira VERDADEIRO/FALSO, que no Excel valem 1 e 0.
  • 10:00 - Dividir por 0 gera erro de propósito, por isso a opção 6 (ignorar valores de erro) é obrigatória aqui.
  • 11:00 - Trocando o k para 2 ou 3 você pega o segundo e o terceiro menores acima da média.

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Fórmula AGREGAR no Excel – Mais Poderosa que o PROCV

Na aula de hoje, quero apresentar a fórmula AGREGAR no Excel. Embora não seja tão conhecida ou utilizada, é uma ferramenta extremamente poderosa.

A função AGREGAR permite a utilização de outras funções dentro dela, semelhante à função SUBTOTAL.

Nesta aula, mostrarei como utilizar as funções referenciais e matriciais no Excel. Com isso, você será capaz de realizar cálculos complexos usando a função AGREGAR e perceberá como ela pode ser útil em diversas situações.

Então, faça o download do material disponível e acompanhe-me nesta jornada de aprendizado!

Função AGREGAR no Excel

A função AGREGAR torna os cálculos avançados simples de realizar. Para esta aula, usaremos uma planilha simples de vendas como exemplo.

Planilha de vendas

A função AGREGAR pode ser empregada de duas maneiras: referencial e matricial. A forma referencial é aquela com a qual estamos mais familiarizados no Excel, ou seja, fazendo referência a outras células ou intervalos de células em uma planilha. Já a forma matricial envolve funções que operam em uma matriz ou intervalo de células.

A fórmula AGREGAR inclui um conjunto de 19 funções integradas que podemos utilizar para diversas operações.

19 funções integradas da AGREGAR

Embora seja semelhante à função SUBTOTAL, que também possui um conjunto de fórmulas integradas, a função AGREGAR se destaca pela capacidade de lidar com cálculos matriciais e oferecer opções adicionais.

A função AGREGAR nos permite realizar cálculos em um intervalo de células, ignorando erros e valores ocultos. Por exemplo, se quisermos calcular o total das vendas da tabela, podemos utilizar a função SOMA, passando o intervalo desejado.

Resultado da SOMA

No entanto, se filtrarmos a tabela para exibir apenas as vendas feitas na loja do Rio de Janeiro, o resultado da função SOMA permanecerá o mesmo, pois ela ignora se os valores estão filtrados ou não.

Resultado SOMA

Nesse cenário, a função AGREGAR se torna útil, utilizando a opção 9, que corresponde à fórmula de soma. Em seguida, selecionamos a opção na lista para ignorar as linhas ocultas.

Fórmula AGREGAR
Codigo
=AGREGAR(9;5;C4:C9)

resultado AGREGAR

Dessa forma, ao filtrar a tabela e ocultar as linhas que não se referem à loja do Rio de Janeiro, o valor na função AGREGAR será ajustado.

Ícone ExcelExcel Impressionador

Tudo que você precisa saber, do Básico ao Avançado, pra dominar a ferramenta mais importante do Mercado de Trabalho. Aprenda as principais ferramentas, funções e recursos do Excel para deixar qualquer planilha mais chamativa e eficiente.

Começar agoraSeta para a direita
Fundo ExcelTelas Excel
Luz Excel
resultado AGREGAR com filtro aplicado

Isso é ideal para realizar somatórios em dados filtrados ou com valores ocultos devido a filtros manuais.

Funções Matriciais no Excel – Função AGREGAR Matricial

Além de sua utilização na forma referencial, como acabamos de demonstrar, a função AGREGAR também pode ser empregada como função matricial.

É possível observar que ela inclusive possui um terceiro argumento que pode ser utilizado em funções matriciais.

Função AGREGAR

Dentre as opções disponíveis dentro da fórmula AGREGAR, temos as opções referenciais que vão do número 1 ao 13 e as matriciais que vão do 14 ao 19.

Ao realizar um cálculo matricial, não estamos limitados a selecionar apenas uma célula ou um intervalo de células. Com as funções matriciais, podemos realizar cálculos complexos comparando vários intervalos.

Por exemplo, para a função MAIOR (opção 14), podemos não apenas retornar o maior valor dentre as vendas, mas sim os 3 maiores valores. Para isso, em vez de informar um único número para K, podemos passar um conjunto de valores.

Codigo
=AGREGAR(14;4;C4:C9;{1;2;3})

Função AGREGAR para MAIOR valor

Dessa forma, com a função matricial, podemos retornar não apenas um único valor como em uma função normal, mas múltiplos valores ao mesmo tempo.

Cálculos Complexos com a Função AGREGAR

Além disso, podemos realizar cálculos ainda mais complexos utilizando a função AGREGAR. Por exemplo, podemos determinar o menor valor de venda entre as vendas que estão acima da média.

Para este caso, vamos utilizar a opção de número 15, que calcula o menor valor, e a opção de número 6, que ignora os valores de erro. Em seguida, realizaremos nosso cálculo matricial.

Para esse cálculo, passamos todo o intervalo de vendas e dividimos pelo somatório de todos os valores de vendas que são maiores do que a média de todos os valores de vendas.

Codigo
=AGREGAR(15;6;C4:C9/(C4:C9>MÉDIA(C4:C9));1)

 

Função agregar mais complexa

Ao calcular a média desses valores, veremos que o valor médio é 37,83. Portanto, 44 é de fato o menor valor acima da média dentre as vendas disponíveis.

Para compreender como o Excel chegou a esse resultado, basta selecionarmos o intervalo dentro da fórmula e observar o resumo que aparece acima.

Resumo da função

Em outras palavras, a expressão C4:C9>MÉDIA(C4:C9) retorna uma matriz de verdadeiro/falso, onde verdadeiro representa que o valor é maior que a média e falso o contrário. O Excel interpreta os valores verdadeiros como 1 e os falsos como 0.

Quando dividimos o intervalo de vendas C4:C9 por essa matriz, os valores são divididos apenas pelos valores que são maiores que a média (1), pois a divisão por falsos (0) resulta em erro, e esses erros são ignorados de acordo com a opção 6 da função AGREGAR.

Portanto, ao calcular o menor valor nesse intervalo de vendas que estão acima da média, obtemos o valor 44, que é o menor valor de venda acima da média dentre as vendas disponíveis.

Perguntas frequentes

1. Para que serve a função AGREGAR no Excel?

Ela executa cálculos como soma, média, máximo, mínimo e desvio padrão com controle total sobre o que entra na conta: dá para ignorar linhas ocultas por filtros, valores de erro e outras funções AGREGAR ou SUBTOTAL aninhadas. São 19 funções integradas em dois modos de uso, o referencial e o matricial.

2. Qual a diferença entre AGREGAR e SUBTOTAL no Excel?

A SUBTOTAL oferece 11 funções e trabalha apenas no modo referencial. A AGREGAR amplia para 19 funções, aceita cálculos matriciais (como retornar os três maiores valores de uma vez) e tem mais opções de controle, incluindo ignorar valores de erro no intervalo — cenário em que a SUBTOTAL retornaria erro.

3. Como somar no Excel ignorando linhas ocultas ou filtradas?

Use =AGREGAR(9;5;intervalo). O primeiro argumento (9) indica a função SOMA e o segundo (5) manda ignorar linhas ocultas. Assim, ao filtrar a tabela, o resultado se ajusta automaticamente para considerar só as linhas visíveis — diferente da função SOMA comum, que continua somando tudo, inclusive o que o filtro escondeu.

4. O que significam os números dentro da função AGREGAR?

O primeiro número escolhe qual cálculo executar: de 1 a 13 estão as funções referenciais (soma, média, contagem) e de 14 a 19 as matriciais (MAIOR, MENOR, PERCENTIL). O segundo número define o comportamento, como ignorar linhas ocultas (5) ou valores de erro (6). Depois vêm o intervalo e, nas matriciais, um argumento extra.

Conclusão – Fórmula AGREGAR no Excel – Mais Poderosa que o PROCV

Na aula de hoje, você aprendeu a utilizar a fórmula AGREGAR no Excel, uma função que possibilita a realização de cálculos mais complexos, mas que pode ser extremamente útil e poderosa.

Com essa função, é possível realizar cálculos referenciais e matriciais. Por ser uma função bastante completa e versátil, é possível inserir e utilizar diferentes lógicas e cálculos para chegar ao resultado desejado.

Hashtag Free Excel Básico

Apostila Básica de Excel

Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!

Download da Apostila

Hashtag Treinamentos

Para acessar outras publicações de Excel Avançado, clique aqui!

Quer aprender mais sobre Excel com um minicurso básico gratuito?

Posts mais recentes de Excel Avançado

Posts mais recentes da Hashtag Treinamentos

Diego Monutti

Autor do artigo

Diego Monutti

Analista de Operações e Especialista em Python

Analista de Operações e SEO, além de redator técnico na Hashtag Treinamentos, onde produz conteúdos sobre dados, IA, programação e produtividade. Pós-graduando em Ciência de Dados e Inteligência Artificial pela Universidade Tecnológica Federal do Paraná (UTFPR), com foco em Business Intelligence, Machine Learning, MLOps e Big Data.

Ver mais posts