AGREGAR no Excel (Sumário)

  • AGREGAR no Excel
  • Fórmula AGREGADA no Excel
  • Como usar a função AGREGAR?

AGREGAR no Excel

  • A função AGGREGATE foi introduzida no Microsoft Excel 2010, não disponível na versão Excel 2003 ou 2007
  • Ele atua como uma função de planilha e é uma função embutida no Excel, categorizada em Função Matemática / Trigonométrica
  • O Excel AGGREGATE possui uma opção e função interna adicional que permite ignorar linhas ocultas, erros e subtotais aninhados, é uma forma mais poderosa e avançada em comparação com outras
  • É tão versátil que pode substituir as 19 funções do Excel mais frequentemente mencionadas abaixo, nas estatísticas, de uma só vez (mencionado na página 2)

Definição

"Retorna AGGREGATE em um banco de dados de valores ou lista"

Significa que ele realiza vários cálculos (19 funções estatísticas do Excel)

Fórmula AGREGADA no Excel

A função AGREGAR tem dois tipos de fórmula

Pode ser notado quando você digita a função AGGREGATE em uma célula do Excel

1) AGREGAR no formulário de referência

2) AGREGAR na fórmula de matriz

Function_num: é um número, pode ser de 1 a 19, depende de qual função específica você deseja usar na lista abaixo mencionada

Aqui cada número representa uma função, é um argumento obrigatório

1 a 13 são forma de referência e 14 a 19 são forma de matriz

1: MÉDIA

2: COUNT

3: COUNTA

4: MAX

5: MIN

6: PRODUTO

7: DESVPADS

8: DESVPAD.P

9: SUM

10: VAR.S

11: VAR.P

12: MÉDIA

13: MODE.SNGL

14: GRANDE

15: PEQUENO

16: PERCENTILE.INC

17: QUARTILE.INC

18: PERCENTILE.EXC

19: QUARTILE.EXC

Opções: é o número 0 a 7 que especifica quais valores devem ser ignorados para a função agregada Nota: Se o parâmetro options for omitido, por padrão, as opções serão definidas como 0

0: Ignorar funções SUBTOTAL e AGGREGATE aninhadas

1: Ignorar funções SUBTOTAL, AGGREGATE e linhas ocultas aninhadas

2: Ignorar funções SUBTOTAL, AGGREGATE e valores de erro aninhados

3: Ignorar funções SUBTOTAL, AGREGADAS aninhadas, linhas ocultas e valores de erro

4: Ignorar nada

5: Ignorar linhas ocultas

6: Ignorar valores de erro

7: Ignorar linhas ocultas e valores de erro

ref1, ref2, ref (3): é o primeiro argumento numérico da função ao usar a sintaxe REFERENCE. são valores ou valores numéricos nos quais queremos executar a computação. Aqui são necessários no mínimo dois argumentos, argumentos de descanso são opcionais. Para segunda referência, argumentos numéricos devem estar entre 2 e 253 para os quais você deseja o valor agregado

Matriz: uma matriz ou refere-se a um intervalo de células ao usar a sintaxe ARRAY

(k): As últimas 6 funções (abaixo da lista de 1 a 19 funções): valor de k como quarto argumento

É um argumento opcional, usado, se precisarmos descobrir LARGE, SMALL, PERCENTILE.INC, QUARTILE.INC, PERCENTILE.EXC ou QUARTILE.EXC ao usar a sintaxe ARRAY

Como usar a função AGREGAR no Excel?

Esta função AGREGADA é muito simples e fácil de usar. Vamos agora ver como usar a função AGGREGATE com a ajuda de alguns exemplos.

Você pode fazer o download deste modelo do Excel da função AGREGADO aqui - Modelo do Excel da função AGREGADO

Exemplo 1

A tabela a seguir contém dados de vendas anuais (2015)

Se você executar a função = SUM (B8: B16) diretamente na célula B17,

fornece o valor correto porque essa coluna não contém linhas ocultas, erros e subtotais aninhados

Aqui, calculará a soma usando uma função AGGREGATE na célula B19.

= AGREGADO (9, 4, B8: B16),

O resultado será 487

Function_ num: Para a função SUM, a função num é 9

Opção: na coluna B, ou seja, para os dados de vendas do ano de 2015, todos os valores são fornecidos e não precisamos ignorar nenhum valor; ele não contém linhas ocultas, erros e subtotais aninhados. então, vamos selecionar a opção 4 (ignorar nada)

Matriz: é um intervalo para o qual você deseja calcular funções agregadas. Aqui o intervalo de valores de referência é B8: B16, ele é selecionado como uma matriz de valores numéricos

'k' é um argumento opcional, usado apenas para uma função como LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC. estamos calculando a soma aqui, então omitiremos o valor de k.

Exemplo 2

A tabela a seguir contém dados de vendas anuais (2016)

Na coluna C, para os dados de vendas anuais de 2016. No intervalo C8: C16, uma célula C11 e C12 contém um valor de erro (# DIV / 0! & # N / A). Na fórmula AGGREGATE, quando uma opção apropriada é usada, o AGGREGATE no Excel fornece o valor SUM correto, negligenciando o valor do erro.

Se você executar a função = SUM (C8: C16) diretamente na célula C17,

retorna um valor de erro, devido a um erro nesse intervalo. porque essa coluna contém # DIV / 0! & # N / D erros.

Para ignorar os valores de erro, precisamos usar a opção 6 em uma função AGGREGATE

= AGREGADO (9, 6, C8: C16).

O resultado ou a saída será 334

Function_ num: Para a função SUM, a função num é 9

Opção: na coluna C, ou seja, para dados de vendas do ano de 2016, no intervalo C8: C16, uma célula C11 e C12 contém um valor de erro (# DIV / 0! & # N / A). Para ignorar esses erros, selecionaremos a opção 6 (Ignorar valores de erro)

Matriz: é um intervalo para o qual você deseja calcular funções agregadas. Aqui o intervalo de valores de referência é C8: C16, ele é selecionado como uma matriz de valores numéricos

'k' é um argumento opcional, usado apenas para uma função como LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC. estamos calculando a soma aqui, então omitiremos o valor de k.

quando uma opção apropriada é usada na função AGGREGATE, o AGGREGATE no Excel retorna ou fornece a SOMA dos valores restantes, negligenciando o valor de erro na célula C11 e C12. ie 334

Exemplo 3

A tabela a seguir contém dados de vendas anuais (2017)

Na coluna D, para os dados de vendas anuais de 2017. No intervalo D8: D16, uma célula D9 é uma célula em branco ou linha oculta e D12 contém um valor de erro ( # N / A). Na fórmula AGGREGATE, quando uma opção apropriada é usada, o AGGREGATE no Excel fornece a soma correta, negligenciando a linha oculta e o valor do erro.

Se você executar a função = SUM (D8 : D16) diretamente na célula D17,

retorna um valor de erro # N / A, devido a um erro nesse intervalo. porque essa coluna contém erro # N / D e linha oculta ou valor em branco.

= AGREGADO (9, 7, D8: D16),

O resultado ou a saída será 262

Function_ num: Para a função SUM, a função num é 9

Opção: na coluna D, para os dados de vendas anuais de 2017. No intervalo D8: D16, uma célula D9 é uma célula em branco ou linha oculta e D12 contém um valor de erro (# N / A). Para ignorar esses erros, selecionaremos a opção 7 (Ignorar linhas ocultas e valores de erro)

Nota: você pode ocultar linhas selecionando as linhas> clique com o botão direito do mouse em uma linha específica> Ocultar

Matriz: é um intervalo para o qual você deseja calcular funções agregadas. Aqui o intervalo de referência dos valores é D8: D16, ele é selecionado como uma matriz de valores numéricos

'k' é um argumento opcional, usado apenas para uma função como LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC. estamos calculando a soma aqui, então omitiremos o valor de k.

quando uma opção apropriada é usada em uma função AGGREGATE, o AGGREGATE no Excel retorna ou fornece a SOMA dos valores restantes, negligenciando o valor de erro na célula D9 e D12 . ou seja, 262

Lembretes sobre a função AGGREGATE

  • A função AGGREGATE é aplicável apenas a intervalos verticais ou colunas de dados. Não foi projetado para intervalo horizontal ou linhas de dados.
  • Tem uma limitação; ignora apenas as linhas ocultas, não ignora as colunas ocultas
  • Uma função AGGREGATE é aplicável apenas ao valor numérico
  • O valor do argumento da função _ num não deve ser maior que 19 ou menor que 1; da mesma forma, o argumento da opção não deve ser maior que 7; caso contrário, ele dará # VALOR! erro
  • Na função AGGREGATE, se estiver no argumento do número da função, se você estiver usando 14 a 19 (LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC ou QUARTILE.EXC), o argumento "K" deve ser usado. g. = AGREGADO (15, 6, A1: A9, 3). Se, no caso, o valor “K” ou o segundo argumento de referência for ignorado, isso resultará em um # VALOR! erro

Artigos recomendados

Este foi um guia para a função AGREGAR. Aqui discutimos a fórmula AGGREGATE e como usar a função AGGREGATE, além de exemplos práticos e modelos de excel para download. Você também pode consultar nossos outros artigos sugeridos -

  1. Guia para a função CONCATENATE
  2. Guia da função OR
  3. Guia para a função NÃO
  4. Guia da função TRANSPOSE

Categoria: