Este tutorial demonstra como usar as funções AVERAGEIF e AVERAGEIFS do Excel no Excel e nas planilhas do Google para calcular a média de dados que atendem a certos critérios.
Visão geral da função AVERAGEIF
Você pode usar a função AVERAGEIF no Excel para contar células que contêm um valor específico, contar células que são maiores ou iguais a um valor, etc.
Para usar a função de planilha do Excel AVERAGEIF, selecione uma célula e digite:
(Observe como as entradas da fórmula aparecem)
Sintaxe e argumentos da função AVERAGEIF:
= AVERAGEIF (intervalo, critérios, [intervalo_média])
faixa - O intervalo de células a serem contadas.
critério - Os critérios que controlam quais células devem ser contadas.
intervalo_média - [opcional] As células para calcular a média. Quando omitido, intervalo é usado.
O que é a função AVERAGEIF?
A função AVERAGEIF é uma das funções mais antigas usadas em planilhas. É usado para varrer um intervalo de células, verificando um critério específico e, em seguida, fornecendo a média (também conhecida como média matemática) se os valores em um intervalo correspondem a esses valores. A função AVERAGEIF original foi limitada a apenas um critério. Após 2007, foi criada a função AVERAGEIFS que permite uma infinidade de critérios. A maior parte do uso geral permanece o mesmo entre os dois, mas existem algumas diferenças críticas na sintaxe que discutiremos ao longo deste artigo.
Se ainda não o fez, você pode revisar grande parte da estrutura e exemplos semelhantes no artigo COUNTIFS.
Exemplo básico
Vamos considerar esta lista de vendas registradas e queremos saber a receita média.
Porque tivemos uma despesa, o valor negativo, não podemos apenas fazer uma média básica. Em vez disso, queremos calcular a média apenas dos valores maiores que 0. O “maior que 0” é o que será nosso critério em uma função MÉDIA. Nossa fórmula para afirmar isso é
= AVERAGEIF (A2: A7, "> 0")
Exemplo de duas colunas
Embora a função AVERAGEIF original tenha sido projetada para permitir que você aplique um critério ao intervalo de números que deseja somar, na maior parte do tempo você precisará aplicar um ou mais critérios a outras colunas. Vamos considerar esta tabela:
Agora, se usarmos a função AVERAGEIF original para descobrir quantas Bananas temos em média. Colocaremos nossos critérios na célula D1 e precisaremos fornecer o intervalo que desejamos média como o último argumento, e assim nossa fórmula seria
= MÉDIA SE (A2: A7, D1, B2: B7)
No entanto, quando os programadores finalmente perceberam que os usuários queriam fornecer mais de um critério, a função AVERAGEIFS foi criada. Para criar uma estrutura que funcione para qualquer número de critérios, o AVERAGEIFS requer que o intervalo de soma seja listado primeiro. Em nosso exemplo, isso significa que a fórmula precisa ser
= MÉDIOS (B2: B7, A2: A7, D1)
NOTA: Essas duas fórmulas obtêm o mesmo resultado e podem parecer semelhantes, portanto, preste muita atenção em qual função está sendo usada para certificar-se de listar todos os argumentos na ordem correta.
Trabalho com datas, vários critérios
Ao trabalhar com datas em uma planilha, embora seja possível inserir a data diretamente na fórmula, é uma prática recomendada ter a data em uma célula para que você possa apenas fazer referência à célula em uma fórmula. Por exemplo, isso ajuda o computador a saber que você deseja usar a data 27/05/2020, e não o número 5 dividido por 27 dividido por 2022.
Vejamos nossa próxima tabela que registra o número de visitantes de um site a cada duas semanas.
Podemos especificar os pontos inicial e final do intervalo que queremos observar em D2 e E2. Nossa fórmula para encontrar a média do número de visitantes neste intervalo poderia ser:
= MÉDIOS (B2: B7, A2: A7, "> =" & D2, A2: A7, "<=" & E2)
Observe como fomos capazes de concatenar as comparações de “=” com as referências de célula para criar os critérios. Além disso, embora ambos os critérios estivessem sendo aplicados ao mesmo intervalo de células (A2: A7), você precisa escrever o intervalo duas vezes, uma para cada critério.
Múltiplas colunas
Ao usar vários critérios, você pode aplicá-los ao mesmo intervalo do exemplo anterior ou pode aplicá-los a intervalos diferentes. Vamos combinar nossos dados de amostra nesta tabela:
Configuramos algumas células para que o usuário insira o que deseja pesquisar nas células E2 a G2. Portanto, precisamos de uma fórmula que some o número total de maçãs colhidas em fevereiro. Nossa fórmula se parece com esta:
= MÉDIOS (C2: C7, B2: B7, "> =" & F2, B2: B7, "<=" & G2, A2: A7, E2)
AVERAGEIFS com lógica de tipo OR
Até este ponto, os exemplos que usamos foram todos baseados em comparação E, em que procuramos linhas que atendam a todos os nossos critérios. Agora, vamos considerar o caso quando você deseja pesquisar a possibilidade de uma linha atender a um ou outro critério.
Vejamos esta lista de vendas:
Gostaríamos de somar as vendas médias de Adam e Bob. Primeiro, uma rápida discussão sobre como tirar médias. Se você tiver um número ímpar de coisas, como 3 entradas para Adam e 2 para Bob, você não pode simplesmente tirar a média das vendas de cada pessoa. Isso é conhecido como tirar a média das médias, e você acaba atribuindo um peso injusto ao item que tem poucas entradas. Se esse for o caso com seus dados, você precisará calcular uma média da forma “manual”: pegue a soma de todos os seus itens dividida pela contagem de seus itens. Para revisar como fazer isso, você pode verificar os artigos aqui:
Agora, se o número de entradas for o mesmo, como em nossa tabela, você tem algumas opções que pode fazer. O mais simples é somar dois MÉDIOS juntos, assim, e então dividir por 2 (o número de itens em nossa lista)
= (MÉDIOS (B2: B7, A2: A7, "Adam") + MÉDIOS (B2: B7, A2: A7, "Bob")) / 2
Aqui, fizemos com que o computador calculasse nossas pontuações individuais e depois as adicionamos.
Nossa próxima opção é boa para quando você tiver mais intervalos de critérios, de forma que não queira ter que reescrever toda a fórmula repetidamente. Na fórmula anterior, dissemos manualmente ao computador para adicionar dois MÉDIOS diferentes juntos. No entanto, você também pode fazer isso escrevendo seus critérios dentro de uma matriz, como este:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"}))
Observe como a matriz é construída dentro das chaves. Quando o computador avaliar esta fórmula, ele saberá que queremos calcular uma função AVERAGEIFS para cada item em nosso array, criando assim um array de números. A função AVERAGE externa pegará então aquele array de números e o transformará em um único número. Passando pela avaliação da fórmula, ficaria assim:
= AVERAGE (AVERAGEIFS (B2: B7, A2: A7, {"Adam", "Bob"})) = AVERAGE (13701, 21735) = 17718
Obtemos o mesmo resultado, mas fomos capazes de escrever a fórmula de forma um pouco mais sucinta.
Lidando com espaços em branco
Às vezes, seu conjunto de dados terá células em branco que você precisa localizar ou evitar. Definir os critérios para isso pode ser um pouco complicado, então vamos ver outro exemplo.
Observe que a célula A3 está realmente em branco, enquanto a célula A5 tem uma fórmula que retorna uma string de comprimento zero de “”. Se quisermos encontrar a média total de verdadeiramente células em branco, usaríamos um critério de “=” e nossa fórmula seria assim:
= MÉDIOS (B2: B7, A2: A7, "=")
Por outro lado, se quisermos obter a média de todas as células que parecem visualmente em branco, alteraremos os critérios para “”, e a fórmula parece
= MÉDIOS (B2: B7, A2: A7, "")
Vamos inverter: e se você quiser encontrar a média de células não vazias? Infelizmente, o design atual não permite que você evite a string de comprimento zero. Você pode usar um critério de “”, mas como você pode ver no exemplo, ele ainda inclui o valor da linha 5.
= MÉDIOS (B2: B7, A2: A7, "")
Se você precisar não contar células contendo strings de comprimento zero, considere o uso da função LEN dentro de um SUMPRODUCT
AVERAGEIF no Planilhas Google
A função AVERAGEIF funciona exatamente da mesma forma no Planilhas Google e no Excel:
