Função AGGREGATE Excel

Função AGGREGATE no Excel

A função AGGREGATE no Excel retorna a agregação de uma determinada tabela de dados ou listas de dados, esta função também tem o primeiro argumento como número de função e outros argumentos são para uma faixa de conjuntos de dados, o número da função deve ser lembrado para saber qual função usar .

Sintaxe

Existem duas sintaxes para a Fórmula AGGREGATE:

1. Sintaxe de referência

= AGREGADO (núm_função, opções, ref1, ref2, ref [3], ...)

2. Sintaxe de array

= AGREGADO (núm_função, opções, matriz, [k])

Function_num é um número que denota uma função específica que queremos usar, é um número de 1-19

Opção: também é um valor numérico que varia de 0 a 7 e determina quais valores devem ser ignorados durante os cálculos

Ref1, ref2, ref [3]:  é o argumento ao usar a sintaxe de referência, é o valor ou valores numéricos sobre os quais queremos realizar o cálculo, pelo menos dois argumentos são necessários, os argumentos restantes são opcionais.

Array: é um array de valores sobre o qual queremos realizar a operação, é usado na sintaxe de array da função AGGREGATE no excel

K: é um argumento opcional e é um valor numérico, é usado quando a função LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC no Excel é usada.

Exemplos

Você pode baixar este modelo de Excel de função AGGREGATE aqui - Modelo de Excel de função AGGREGATE

Exemplo 1

Suponha que temos uma lista de números e calcularemos a Média, Contagem que é o número de células que contêm um valor, Contagem - contagem de células que não estão vazias, Máximo, Mínimo, produto e soma dos valores numéricos fornecidos. Os valores são fornecidos abaixo na tabela:

Vamos primeiro calcular a média na linha 9, para todos os valores dados. Para a média, o function_ num é

Na coluna C, todos os valores são dados e não teremos que ignorar nenhum valor, então vamos selecionar a opção 4 (ignorar nada)

E selecionando o intervalo de valores C1: C8 como uma matriz de valores numéricos

Uma vez que ' k' é um argumento opcional e é usado quando uma função como LARGE, PEQUENO no Excel, PERCENTIL.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC é usado, mas neste caso, estamos calculando a média, então omitirá o valor de k.

Então, o valor médio é

Da mesma forma, para o intervalo D1: D8, novamente selecionaremos a opção 4.

Para o intervalo E1: E8, uma célula E6 contém um valor de erro, se usarmos a mesma fórmula AGREGADO obteremos um erro, mas quando uma opção apropriada é usada, o AGREGADO no Excel dá a média dos valores restantes, desprezando o erro valor em E6.

Para ignorar os valores de erro, temos a opção 6.

Da mesma forma, para a faixa G1: G8, usaremos a opção 6 (ignorar os valores de erro)

Agora, para o intervalo H3, se colocarmos um valor 64 e ocultar a terceira linha e usarmos a opção 5, para ignorar a linha oculta, o AGREGADO no Excel forneceremos o valor médio apenas para valores numéricos visíveis.

Saída sem ocultar a linha 3

Saída após ocultar a linha 3

Aplicando a fórmula AGREGADO para outras operações, temos

Exemplo - # 2

Suponha que temos uma tabela para a receita gerada em datas diferentes dos diferentes canais, conforme mostrado abaixo

Agora, queremos verificar a receita gerada para os diferentes canais. Então, quando aplicamos a função de soma obtemos a receita total gerada, mas no caso se quisermos verificar a receita gerada para canal orgânico ou canal direto ou qualquer outro, quando aplicamos filtros em excel para o mesmo, a função de soma sempre será dê a soma total

Queremos que, ao filtrar o canal, obtenhamos a soma dos valores que são visíveis, então, em vez de usar a função SUM, usaremos a função AGGREGATE para obter a soma dos valores que são visíveis quando um filtro é aplicado.

Assim, substituindo a fórmula SUM por uma função AGGREGATE com o código de opção 5 (ignorando as linhas e valores ocultos), temos,

Agora, quando aplicarmos o filtro para canais diferentes, ele mostrará a receita para aquele canal apenas quando o restante das linhas for ocultado.

Receita total gerada para canal direto:

Receita total gerada para o canal orgânico:

Receita total gerada para o canal pago:

Assim, podemos ver que a função AGGREGATE calcula os diferentes valores de Soma para a receita gerada para diferentes canais, uma vez que são filtrados. Assim, a função AGGREGATE pode ser usada dinamicamente para a substituição de diferentes funções para diferentes condições sem usar a fórmula condicional.

Suponha que para o mesmo canal de tabela e receita, alguns de nossos valores de receita contenham um erro, agora precisamos ignorar os erros e, ao mesmo tempo, se quisermos aplicar um filtro, a função AGGREGATE deve ignorar também os valores das linhas ocultas.

Quando usamos a opção 5, obtemos o erro para a soma da receita total, agora, para ignorar os erros, temos que usar a opção 6

Usando a opção 6, obtemos a soma ignorando os valores de erro, mas quando aplicamos o filtro, por exemplo, filtrar por valor de canal direto, obtemos a mesma soma ignorando os erros, mas ao mesmo tempo temos que ignorar os valores ocultos também.

Então, neste caso, usaremos a opção 7 que ignora os valores de erro e ao mesmo tempo as linhas ocultas

Coisas para lembrar

• A função AGGREGATE não reconhece a função _ num valor maior que 19 ou menor que 1 e, da mesma forma, para a opção número, ela não reconhece os valores maiores que 7 e menores que 1, se fornecermos quaisquer outros valores, dá um #VALUE ! Erro
• Ele sempre aceita o valor numérico e sempre retorna um valor numérico como saída
• O AGGREGATE no Excel tem uma limitação; ele apenas ignora as linhas ocultas, mas não ignora as colunas ocultas.