Conheça a fórmula AGREGAR no Excel, uma ferramenta sobre a qual não ouvimos muito, mas que é mais poderosa que o PROCV.
Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo ou acesse o nosso canal do YouTube!
Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:
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!
A função AGREGAR torna os cálculos avançados simples de realizar. Para esta aula, usaremos uma planilha simples de vendas como exemplo.
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.
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.
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.
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.
=AGREGAR(9;5;C4:C9)
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.
Isso é ideal para realizar somatórios em dados filtrados ou com valores ocultos devido a filtros manuais.
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.
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.
=AGREGAR(14;4;C4:C9;{1;2;3})
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.
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.
=AGREGAR(15;6;C4:C9/(C4:C9>MÉDIA(C4:C9));1)
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.
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.
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.
Essa é uma apostila básica de Excel para que você saia do zero de forma 100% gratuita!
Para acessar outras publicações de Excel Avançado, clique aqui!
Expert em conteúdos da Hashtag Treinamentos. Auxilia na criação de conteúdos de variados temas voltados para aqueles que acompanham nossos canais.
Quer sair do zero no Python e virar uma referência na sua empresa? Inscreva-se agora mesmo no Python Impressionador