Como usar a função AGGREGATE do Excel -

Índice

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 retorno

Valor de retorno

Depende da função especificada

Sintaxe

= 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 2010

Notas 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.

Artigos interessantes...