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:
- Sintaxe de referência
= AGREGADO (núm_função, opções, ref1, ref2, ref (3), …)
- 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 desejamos 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 numérico ou valores sobre os quais queremos realizar o cálculo, pelo menos dois argumentos são necessários. Os argumentos restantes são opcionais.
Array: é uma matriz de valores sobre os quais queremos operar; ele é usado na sintaxe de array da função AGGREGATE no Excel.
K: é um argumento opcional e valor numérico; é usado quando a função como LARGE, SMALL, PERCENTIL.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC no Excel é usada.
Exemplos
Exemplo 1
Suponha que temos uma lista de números e iremos calcular 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 é 1
Na coluna C, todos os valores são fornecidos e não teremos que ignorar nenhum valor para 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 GRANDE, PEQUENO no Excel, PERCENTIL.EXC, QUARTIL.INC, PERCENTIL.INC ou QUARTIL.EXC é usado, mas neste caso, estamos calculando a média para omita 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. Ainda assim, quando uma opção apropriada é utilizada, o AGGREGATE no Excel dá a média dos valores restantes desprezando o valor do erro 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, ocultarmos 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 por canal orgânico ou canal direto ou qualquer outro, quando aplicamos filtros em excel para o mesmo, a função de soma sempre dará a soma total
Queremos que, ao filtrar o canal, obtenhamos a soma dos valores visíveis, portanto, 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 aplicamos 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 canal orgânico:
Receita total gerada para o canal pago:
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. Portanto, a função AGGREGATE pode ser usada dinamicamente para substituir funções diferentes para condições diferentes sem usar a fórmula condicional.
Suponha que para a mesma tabela, alguns dos 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. Para ignorar os erros, temos que usar a opção 6
Usando a opção 6, obtemos a soma ignorando os valores de erro. Ainda assim, quando aplicamos o filtro, por exemplo, filtro pelo valor do canal direto, obtemos a mesma soma ignorando os erros, mas ao mesmo tempo, temos que ignorar os valores ocultos também.
Portanto, 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 o valor da função _ num maior que 19 ou menor que 1. Da mesma forma, para Option number, ela não identifica os valores maiores que 7 e menores que 1; se fornecermos quaisquer outros valores, será fornecido 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.
