O cálculo de média no Excel pode vir atrelado a condições específicas que devem ser analisadas, ou seja, parâmetros que delimitam os números que se enquadrarão no resultado da média aritmética. Para isso, em vez de usar função MÉDIA (que calcula a média de modo generalizado), você pode optar pela MÉDIASES, que determina uma média a partir de uma ou mais condições de análise.
Diferentemente da MÉDIASE, a função MÉDIASES permite mais de um critério de análise, o que faz sua sintaxe também ser um pouco diferente.
Versão do Excel utilizada na aula: Microsoft Excel Professional Plus [versão 2019]
Versão do Excel utilizada neste tutorial: Microsoft Excel Professional Plus [versão 2019]
MÉDIASES usando um critério de texto
A sintaxe da função MÉDIASES não é complexa, entretanto é invertida se comparada à MÉDIASE. Enquanto nesta última definimos o único critério de média para depois definirmos o intervalo de cálculo, na MÉDIASES devemos antes definir o intervalo de cálculo para, só então, definir os critérios, já que ela permite inúmeros critérios.
Para todos os exemplos deste artigo usaremos a planilha a seguir. Ela apresenta uma lista de pessoas com suas respectivas idades, bairro de moradia e renda mensal. Iremos calcular a média de renda apenas das pessoas que se adequarem a determinados critérios, tanto fixos em fórmula quanto referenciados, e tanto numéricos quanto em texto.
Nesse primeiro caso, vamos calcular a média de renda apenas das pessoas que moram no bairro Norte. Como se trata de uma única condição, até poderíamos fazer uso da MÉDIASE, mas a MÉDIASES também é capaz de trabalhar com apenas uma condição.
Em G3, podemos inserir a seguinte fórmula:
=MÉDIASES(D2:D20;C2:C20;”Norte”)
Basicamente, os argumentos dessa função são:
=MÉDIASES(intervalo_média; intervalo_critérios1; critérios1; […])
O primeiro argumento (intervalo_média) refere-se ao intervalo onde estão os valores dos quais o Excel irá extrair a média. No nosso exemplo, é o intervalo que contém as rendas.
Já intervalo_critérios1 define o intervalo a ser analisado em busca do primeiro critério. Como nossa meta é filtrar as rendas por bairro, devemos selecionar o intervalo onde esses bairros se encontram.
O parâmetro critério1 é justamente a condição a ser analisada dentro do intervalo de critérios. Como estamos analisando um texto, e esse texto será inserido diretamente na fórmula, ele precisa vir entre aspas duplas.
A seguir, caso haja mais critérios, basta seguir a mesma lógica: definir o intervalo onde se encontram esses dados e o critério a ser analisado nesse conjunto de dados.
Temos como resultado, nesse exemplo, R$ 2.063,00. Isso significa que o Excel analisou, na coluna D, todos os valores relacionados aos dados filtrados da coluna C e calculou, com esse resultado, a média aritmética.
Em vez de usar um texto diretamente na fórmula, você pode referenciar uma célula para definir o critério. Partindo do mesmo exemplo, no lugar de usar o termo “Norte” na fórmula, vamos substituí-lo pela célula F3, que contém justamente o texto “Norte”.
A fórmula então fica assim:
=MÉDIASES(D2:D20;C2:C20;F3)
Note que, nesse caso, como se trata de uma referência, esta não pode vir entre aspas.
Sendo assim, como a referência está atrelada ao conteúdo da célula F3, podemos alterar o texto contido na célula para servir de critério para a MÉDIASES.
MÉDIASES usando um critério numérico
Podemos também usar comparações numéricas como critério para a média. Usando a mesma base de dados, iremos agora calcular a média de renda dos maiores de 50 anos.
A fórmula ficaria assim:
=MÉDIASES(D2:D20;B2:B20;”>50″)
Aqui estamos comparando a idade, portanto o intervalo de análise muda. Ainda que o critério não seja um texto fixo, esse tipo de teste matemático também precisa vir entre aspas duplas.
Um detalhe importante é que, como estamos analisando a renda apenas daqueles cuja idade é maior que 50, o próprio 50 não entra no teste. Caso quiséssemos incluir também os que possuem 50 anos, deveríamos usar a comparação “>=50” (maior ou igual a 50).
MÉDIASES usando dois critérios de comparação numérica
Já no próximo exemplo vamos calcular a média de renda das pessoas que tenham entre 20 e 40 anos. Note que, matematicamente falando, temos dois critérios aqui: a idade precisa ser maior ou igual a 20, mas também precisa ser menor ou igual a 40.
A sintaxe da MÉDIASES para esta situação seria:
=MÉDIASES(D2:D20;B2:B20;”>=20″;B2:B20;”<=40″)
O intervalo de média continua sendo o mesmo: a renda. Entretanto, aqui temos duas condições diferentes para um mesmo intervalo de teste. O intervalo da coluna B, que contém as idades, deve ser testado tanto no primeiro critério como no segundo, afinal, os dois critérios são relacionado à idade.
Como estamos comparando uma faixa etária, o valor inicial e o valor final precisam estar inclusos nessa faixa, por isso estamos usando as comparações “maior ou igual” e “menor ou igual”.
MÉDIASES usando critérios de comparação numérica por referência
Seguindo a mesma linha do exemplo anterior, podemos definir como critério valores que estejam entre um valor inicial e um valor final, no entanto, em vez de inserir essas informações diretamente na fórmula, de maneira fixa, podemos referenciar células para tornar o cálculo mais dinâmico, de forma a permitir que possamos alterar a abrangência desses valores sem necessariamente editar a fórmula.
Vamos diretamente à fórmula para, só depois, às explicações, já que esse tipo de sintaxe é um pouquinho mais complexo.
=MÉDIASES(D2:D20;B2:B20;”>=”&F11;B2:B20;”<=”&F12)
Nosso intervalo de critérios continua sendo o mesmo: a idade, presente na coluna B. No entanto, aqui temos uma comparação numérica de um número contido em uma célula. Como iremos usar uma célula como referência, não podemos incluí-la entre aspas dentro da fórmula, mas a comparação matemática precisa estar entre aspas. Como agir diante desse impasse? Concatenando!
“>=”&F11
O critério de comparação (“>=”) fica entre aspas. Em seguida, usamos o sinal & para concatenar esse termo com o termo seguinte, que é justamente a célula de referência (F11). Tudo isso faz parte de um único critério; a diferença é que concatenamos um valor de texto com um valor referenciado.
O mesmo acontece no estabelecimento do segundo critério: a comparação matemática foi unida à célula de referência por concatenação.
Como os critérios estão atrelados a duas células, o Excel calculará a média de acordo com as idades inseridas nessas células.
MÉDIASES usando critérios de texto e numéricos
Não há também muito segredo quando precisamos misturar critérios de texto e critérios numéricos na mesma sintaxe da função MÉDIASES. Devemos, entretanto, prestar bastante atenção aos intervalos de análise e seus critérios.
No exemplo a seguir, vamos calcular a média de renda baseada em duas condições: dos moradores do bairro Sul que tenham mais de 40 anos.
A sintaxe ficaria assim:
=MÉDIASES(D2:D20;C2:C20;”Sul”;B2:B20;”>40″)
O primeiro critério (“Sul”) refere-se ao bairro, portanto o primeiro intervalo de análise deve ser aquele onde estão contidos os bairros. Já o segundo critério é um teste matemático de idade (“>40”), por isso o intervalo atrelado a ele deve ser o da idade.
Poderíamos também usar células de referência para atribuir valores a serem comparados, tanto para texto quanto para números, assim como fizemos nos exemplos anteriores. Bastaria definir uma célula para a digitação do texto e outra para a digitação da idade.
- Como a função MÉDIASES lida com divisão (afinal, a média aritmética é uma divisão após uma soma), caso não haja dados válidos para a comparação, é possível que o Excel retorne um erro #DIV/0!. Você pode corrigir essa situação com funções de tratamento de erros, como a SEERRO.
- Você pode calcular a média apenas de números negativos ou apenas números positivos usando como critério o zero: maiores que zero para números positivos e menores que zero para números negativos.
- Caso precise aplicar autopreenchimento para sua fórmula, não se esqueça de travar (usado $) os intervalos de análise e também de cálculo.
- Nos exemplos, fizemos uso de dados monetários, que, por padrão, são exibidos com duas casas decimais. A função MÉDIASES, por realizar divisões, pode mostrar como resultado um número com diferentes casas decimais, mesmo que você esteja lidando apenas com números inteiros. Cabe a você exibir essas casas decimais ou arredondar o valor, padronizando uma quantidade específica de casas decimais.
- Cuidado ao selecionar colunas ou linhas inteiras como intervalo de critério ou de média! Qualquer valor inserido nesse intervalo que corresponda ao critério de comparação será levado em conta, mesmo que não tenha relação com seu intervalo de dados específico.
- Nos casos em que você opte por usar uma célula referenciada como critério, é possível tornar ainda mais dinâmico e rápido esse cálculo, usando uma validação de dados em forma de lista para essa célula.
