Resumo
A função AGGREGATE do Excel retorna um cálculo agregado como AVERAGE, COUNT, MAX, etc., opcionalmente ignorando linhas e erros ocultos. Um total de 19 operações estão disponíveis, especificadas pelo número da função no primeiro argumento (consulte a tabela para opções).
Objetivo
Cálculo agregado de retornoValor de retorno
Depende da função especificadaSintaxe
= AGREGADO (núm_função, opções, ref1, ref2)Argumentos
- function_num - Operação a ser executada (1-19).
- options - Valores a ignorar (0-7).
- ref1 - Primeiro argumento.
- ref2 - Segundo argumento (k).
Versão
Excel 2010Notas de uso
A função AGGREGATE retorna o resultado de um cálculo agregado como AVERAGE, COUNT, MAX, MIN, etc. Um total de 19 operações estão disponíveis, e a operação a ser realizada é especificada como um número, que aparece como o primeiro argumento na função. O segundo argumento, options, controla como AGGREGATE lida com erros e valores em linhas ocultas. Veja as tabelas abaixo para todas as opções disponíveis.
Exemplo 1
Para retornar o valor MAX no intervalo A1: A10, ignorando os erros e as linhas ocultas, forneça 4 para o número da função e 7 para as opções:
=AGGREGATE(4,7,A1:A10) // max value
Para retornar o valor MIN com as mesmas opções, altere o número da função para 5:
=AGGREGATE(5,7,A1:A10) // min value
Exemplo # 2
No exemplo mostrado acima, a fórmula em D5 é:
=AGGREGATE(4,6,values)
onde "valores" é o intervalo nomeado B5: B14. O número da função é 4, que especifica MAX. As opções são fornecidas como 6, para ignorar apenas os erros.
Exemplo # 3 - enésimo maior
As fórmulas em D8: D10 demonstram como retornar valores "enésimos maiores":
=AGGREGATE(14,6,values,1) // 1st largest =AGGREGATE(14,6,values,2) // 2nd largest =AGGREGATE(14,6,values,3) // 3rd largest
O número da função aqui é 14, que executa a função LARGE. Como a função LARGE requer um argumento k, ela aparece como o último argumento nas três fórmulas acima.
Exemplo # 4 - operação de matriz
O que torna AGGREGATE especialmente útil para fórmulas mais complexas é que ele pode manipular matrizes nativamente quando o número da função é 14-19. Por exemplo, para encontrar o valor MAX às segundas-feiras, com dados que incluem datas e valores, você pode usar AGGREGATE assim:
=AGGREGATE(14,6,values/(TEXT(dates,"ddd")="Mon"),1)
Aqui, especificamos 14 para a função (LARGE) e 6 para a opção (ignorar erros). Em seguida, construímos uma expressão lógica usando a função TEXT para verificar todas as datas das segundas-feiras. O resultado dessa operação é uma matriz de valores TRUE / FALSE, que se tornam o denominador dos valores originais. FALSE é avaliado como zero e gera um # DIV / 0! erro. TRUE é avaliado como 1 e retorna o valor original. A matriz final de valores e erros atua como um filtro. AGGREGATE ignora todos os erros e retorna o maior (máximo) dos valores remanescentes. Um exemplo mais completo aqui.
Números de função
A tabela a seguir lista os números das funções disponíveis para a função AGGREGATE, junto com o nome da função associada. A terceira coluna, Ref2, indica o segundo valor de argumento esperado quando necessário.
Função | Função | Ref2 |
---|---|---|
1 | MÉDIA | |
2 | CONTAGEM | |
3 | CONT.valor | |
4 | MAX | |
5 | MIN | |
6 | PRODUTOS | |
7 | STDEV.S | |
8 | STDEV.P | |
9 | SOMA | |
10 | VAR.S | |
11 | VAR.P | |
12 | MEDIANA | |
13 | MODE.SNGL | |
14 | AMPLA | k |
15 | PEQUENO | k |
16 | PERCENTIL.INC | k |
17 | QUARTILE.INC | litro |
18 | PERCENTIL.EXC | k |
19 | QUARTILE.EXC | litro |
Opções de comportamento
A função AGGREGATE tem muitas opções para ignorar erros, linhas ocultas e outras funções. Os valores possíveis são 0-7, conforme mostrado na tabela abaixo.
Opção | Comportamento |
---|---|
0 | Ignorar as funções SUBTOTAL e AGGREGATE |
1 | Ignorar linhas ocultas, funções SUBTOTAL e AGGREGATE |
2 | Ignorar valores de erro, funções SUBTOTAL e AGGREGATE |
3 | Ignorar linhas ocultas, valores de erro, funções SUBTOTAL e AGGREGATE |
4 | Ignore nada |
5 | Ignorar linhas ocultas |
6 | Ignorar valores de erro |
7 | Ignorar linhas ocultas e valores de erro |
Notas
- AGGREGATE retorna um #VALUE! erro se um segundo argumento de função for necessário, mas não fornecido.
- As referências 3D não funcionam com AGGREGATE.
- A função AGREGADO destina-se a intervalos verticais, não intervalos horizontais.