Como agregar dados no Excel (vários métodos)

O Excel costuma ser a opção preferida para armazenar e transformar dados, principalmente se você trabalha com grandes conjuntos de dados. Se a sua rotina envolve analisar ou interpretar milhares de linhas, o trabalho pode ficar pesado ou, pior ainda, pouco produtivo. Se você trabalha em Finanças ou Vendas, provavelmente vai precisar identificar padrões para fazer previsões. Para isso, você vai precisar agregar intervalos de células ou resumir dados importantes com o mínimo possível de erros ou fórmulas quebradas.

Neste artigo, você vai aprender a agregar dados no Excel com a função AGGREGATE (AGREGAR): primeiro, uma explicação detalhada da função AGGREGATE; depois, uma visão completa da sintaxe da fórmula; e, por fim, um passo a passo para calcular a soma e a média de dados com a fórmula AGGREGATE e com o Power Query do Excel.

O que é a fórmula AGGREGATE no Excel?

O Excel introduziu a função AGGREGATE na versão 2010. Você pode usá-la para agregar dados no Excel com diferentes finalidades, como somas ou médias, com a opção de ignorar linhas ocultas e erros.

Sintaxe da fórmula AGGREGATE

A função AGGREGATE do Excel pode ser escrita de duas formas: como fórmula de referência ou como matriz. A boa notícia é que você não precisa se preocupar com qual usar, porque o Excel escolhe a certa automaticamente com base nos parâmetros que você informar.

=AGGREGATE(function_num, options, ref1, [ref2], …)

ou

=AGGREGATE(function_num, options, array, [k])

Veja abaixo o que significa cada parâmetro:

• function_num: Este parâmetro obrigatório é um número de 1 a 19 que indica a função de agregação a ser executada.
• options: Outro parâmetro obrigatório, um número de 0 a 7, que indica quais valores a função deve ignorar.
• ref1: Este parâmetro obrigatório faz referência ao intervalo de células ao qual a função se aplica.
• [ref2]: É um parâmetro opcional que indica células adicionais. Você pode incluir até 253 intervalos, separados por vírgula (,).
• array: Refere-se a uma matriz de valores ou a uma fórmula de matriz à qual a função é aplicada.
• [k]: Este parâmetro só é exigido por algumas funções, como LARGE(array, k), SMALL(array, k), PERCENTILE.INC(array, k), PERCENTILE.EXC(array, k) e QUARTILE.EXC(array, quart).

Como agregar dados no Excel?

Você pode agregar dados no Excel com a fórmula de referência ou com a fórmula de matriz, como mostrado acima. Aqui, você vai aprender a usar a forma de referência da fórmula AGGREGATE para calcular a média de dados no Excel.

Como usar a função AGGREGATE no Excel?

As funções de agregação ajudam você a fazer cálculos que retornam um único valor como resumo. As cinco mais comuns são SUM (SOMA), AVERAGE (MÉDIA), COUNT (CONT.NÚM), MAX (MÁXIMO) e MIN (MÍNIMO), que também ficam à mão no botão "AutoSoma" da guia Página Inicial. Mas essas funções são básicas e não aceitam critérios mais complexos. Veja como usar a função AGGREGATE no Excel para obter resultados precisos.

Imagine que você precisa calcular o valor médio dos empréstimos concedidos aos seus clientes, mas quer ignorar os erros que existem no intervalo de células. Se você aplicar a função AVERAGE a esse intervalo, o resultado será um erro. Veja o que acontece quando a combinamos com AGGREGATE.

1. Digite "=AGGREGATE(" na primeira célula vazia depois do seu conjunto de dados. Como você pode ver, o Excel mostra automaticamente os dois tipos de fórmula AGGREGATE. Aqui, vamos usar a sintaxe de referência, já que AVERAGE é uma das funções (de 1 a 13) que a utilizam.

2. Clique no parâmetro function_num e o Excel vai mostrar a lista de funções disponíveis.

3. Como queremos calcular a média (AVERAGE), selecionamos "1". Quando terminar, digite a vírgula para passar ao próximo parâmetro.

4. Clique no parâmetro options e selecione "6" para ignorar os erros. Digite a vírgula para incluir o próximo parâmetro.

5. Clique no parâmetro ref1 e selecione com o mouse o intervalo que você quer calcular. Digite o parêntese de fechamento para concluir a fórmula e pressione "Enter".

6. Como você pode ver, a função AGGREGATE retornou a média (AVERAGE) dos valores do intervalo de células selecionado sem nenhum erro.

Agora que você viu como é fácil agregar dados com a função AGGREGATE para calcular uma média, veja como agregar dados no Power Query.

Como agregar dados com o Power Query do Excel?

Se você precisa resumir dados de várias fontes, o Power Query do Excel é a melhor opção. Aqui, você vai ver como incluir uma "Nova Fonte" no Power Query e aplicar os recursos de agregação para fazer cálculos básicos como SUM ou AVERAGE.

1. Acesse Dados > Obter Dados > Da Tabela/Intervalo.

2. Você também pode selecionar essa opção diretamente no grupo da faixa de opções, como mostrado abaixo.

3. O Excel vai abrir a caixa "Criar Tabela" para você confirmar o intervalo que quer editar no Power Query.

4. No Editor do Power Query, acesse Página Inicial > Nova Fonte > Arquivo para adicionar dados de outro arquivo.

5. Selecione o arquivo de onde quer importar os dados e clique em "Importar".

6. Na janela "Navegador", você vai encontrar uma pasta com os novos dados a importar para o Power Query. Selecione a nova tabela de origem e clique em "OK".

7. Acesse Transformar > Agrupar por.

8. Uma nova janela vai abrir para você indicar as colunas que quer agrupar. Para usar os recursos de agregação, clique na opção "Avançado".

9. Selecione no menu suspenso a primeira coluna a agrupar. Aqui, selecionei "LOAN AMOUNT". Se precisar incluir mais colunas, clique no botão "Adicionar agrupamento" logo abaixo.

10. Para agregar os dados com o cálculo que preferir, digite um nome em "Novo nome de coluna", selecione a "Operação" e depois a "Coluna" com os valores que você quer calcular. Clique no botão "Adicionar agregação" para incluir quantas operações forem necessárias.

11. Depois de selecionar os critérios de agregação, clique em "OK".

12. Como mostrado abaixo, o Power Query exibe automaticamente o resultado de todas as funções de agregação selecionadas. Além disso, o painel "Configurações de Consulta", à direita, lista todas as ações, ou seja, as etapas, realizadas para chegar a esse resultado.

Conclusão

Um dos pontos fortes do Excel é a grande variedade de fórmulas e recursos de cálculo, que ajudam a transformar e processar grandes volumes de dados de forma rápida e fácil. Embora o Power Query possa exigir um conhecimento mais avançado de Excel, as funções básicas da AGGREGATE já permitem operações poderosas que não daria para fazer manualmente.

Agora você já deve se sentir à vontade com a fórmula AGGREGATE do Excel e as funções básicas, e já consegue aplicá-la de acordo com o que precisa calcular.

Se você quiser saber mais sobre os recursos de processamento e transformação de dados que o Excel oferece, leia qualquer um destes artigos:

• Como consolidar dados no Excel (passo a passo)
• Combinar vários arquivos CSV em um único arquivo do Excel (vários métodos)
• Como combinar várias colunas do Excel em uma?

A AGGREGATE não é a única forma de resumir um intervalo. Para totais que dependem de uma condição, experimente SUMIF no Excel. Para agrupar e totalizar uma tabela grande sem escrever fórmulas, crie uma tabela dinâmica no Excel. E, se os números de que você precisa estão espalhados em várias pastas de trabalho, você pode consolidar arquivos do Excel automaticamente com o Sheetgo, uma plataforma de automação de dados com IA que mantém os dados combinados sempre atualizados. Experimente o Sheetgo grátis com seus próprios arquivos.