Como calcular média no Excel

Neste artigo você aprenderá como calcular média no Excel em um artigo completo.

Aprenda como calcular média no Excel

No Excel temos diversas opções para calcular e média. Podemos recorrer às funções nativas do Excel, como MÉDIA ou MÉDIASES, implementar o cálculo na tabela dinâmica ou ainda usar funções DAX para obter medidas. Além disso, podemos recorrer a fórmulas diferenciadas para proceder com o cálculo da média desprezando os zeros, o que é uma necessidade bastante comum e também fazer o cálculo de média ponderada. Este artigo objetiva tratar da média aritmética e também média ponderada e a seguir veremos várias alternativas para estes fins.

Calculando e Exibindo a Média no Excel

2.1. Usar a barra de status

A barra de status pode fornecer informações úteis acerca dos dados selecionados nas células. Podemos visualizar rapidamente medidas da estatística descritiva, como valores máximo, mínimo e média. Ao selecionar pelo menos duas células, a barra de status exibe algumas informações referentes aos dados selecionados. Veja na imagem a seguir que temos dados selecionados no intervalo de B6 a B16. Perceba no destaque em vermelho que a barra de status já nos fornece a média dos dados selecionados.

Caso a informação da média não seja exibida na barra de status podemos habilitar a sua visualização. Para isso, devemos clicar com o botão direito do mouse na barra de status e escolher a opção referente à média. A imagem a seguir ilustra esta situação onde a média não está sendo exibida.

2.2. Usando fórmulas

2.2.1. Função Média no Excel

Função MÉDIA é a recomendada para o cálculo da média aritmética (sem considerar critérios). Esta função tem por característica considerar o zero como valor válido. A sintaxe da função é bastante simples, sendo aceitos intervalos com referências de células contínuas ou não, valores constantes etc. Podemos inserir até 255 argumentos nesta função.

A imagem a seguir mostra como devemos proceder para obter a média dos valores presentes no intervalo de B6 a B16. Observe em vermelho na barra de fórmulas a fórmula empregada.

Note na imagem anterior que o valor zero presente em B10 está sendo considerado neste cálculo. Isto se dá porque a função MÉDIA considera todos os valores, sendo o valor zero um número válido. Células vazias são desconsideradas, ou seja: não são calculadas. Para um mesmo conjunto de dados, usar a função MÉDIA selecionando todo o intervalo contendo célula(s) em branco ou usar a função MÉDIA selecionando apenas células preenchidas retornará o mesmo resultado. Note na imagem a seguir que apagamos o valor zero e deixamos a célula em branco. Perceba que a média aumenta. Isto ocorre pelo próprio conceito da média aritmética: resulta da soma dos elementos dividida pela quantidade de elementos. Neste caso removemos um elemento, o que eleva a média, visto que a soma se mantém.

Note na imagem acima que utilizamos todo o intervalo, inclusive a célula em branco. Perceba na imagem a seguir que aplicamos a função MÉDIA “quebrando” o intervalo em dois intervalos, apenas com dados preenchidos e o resultado se mantém quando comparado com a imagem anterior (55).

2.2.2. Média Excel com critério(s): Funções MÉDIASE e MÉDIASES

Estas funções são empregadas para calcular a média aritmética com base em critério. A diferença básica entre estas funções é que MÉDIASE está limitada a permitir apenas um critério enquanto que MÉDIASES permite múltiplos critérios (até 127). De fato, MÉDIASES é mais abrangente, podendo resolver todas as situações. Fato análogo ocorre com SOMASES vs SOMASE e CONT.SES vs CONT.SE.

As funções MÉDIASE e MÉDIASES apresentas as seguintes sintaxes:

intervalo_média: é a faixa de valores que servirá como base para o cálculo da média

intervalo e intervalo_critérios1, intervalo_critérios2... : são os intervalos contendo os critérios

critérios e critérios1, critérios2... : são os critérios para o cálculo da média

Note que a diferença fundamental na sintaxe destas duas funções é o argumento intervalo_média: na função MÉDIASE ele é colocado por último e na função MÉDIASES ele é especificado logo no início, sendo o 1º argumento. Nota: em MÉDIASE, caso o intervalo_média não seja especificado, será considerado o primeiro argumento como intervalo_média.

Veja na imagem a seguir que usamos MÉDIASE para obter a média aritmética de casos de covid 19 por UF.

A imagem a seguir mostra o mesmo cálculo sendo realizado com MÉDIASES.

A imagem a seguir ilustra como faríamos para obter a média de casos de covid 19 por UF considerando apenas o mês de setembro. Inserimos o critério do mês direto na fórmula como (“Set”). Este critério poderia ser oriundo também de uma célula (referência).

2.2.3. Cálculo da média ponderada

A média ponderada admite pesos para o seu cálculo. Este é o ponto principal da diferença para a média aritmética. Enquanto que na média aritmética valores iguais têm sempre o mesmo peso, na média aritmética valores iguais podem ter representatividades diferentes de acordo com os seus pesos relativos.

Vamos ilustrar a média ponderada com um estudo de preço médio de compra de um produto. Aqui vale lembrar que o preço médio deve ser calculado de forma ponderada, e não obtendo uma média aritmética. Fazer duas compras, uma com preço R$ 5,00 e outra com preço R$ 6,00 não resultará (necessariamente) em um preço médio de R$ 5,50 (a menos que a quantidade associada a cada preço seja idêntica). Caso eu compre 2.000 unidades pelo preço de R$ 6,00 e 10 unidades pelo preço de R$ 5,00, por exemplo, o preço médio tende a estar mais próximo de R$ 6,00 pois ele tem mais “peso”, visto estar associado a um maior volume comprado.

A imagem a seguir ilustra o cálculo de preço médio de um produto que apresentou 8 compras ao longo de um mês. Devemos obter o valor total comprado e dividi-lo pela quantidade total comprada. O valor total comprado pode ser obtido com a função SOMARPRODUTO (primeira parte da fórmula, antes da divisão).

Note que o resultado foi um preço médio de R$ 7,95. Um cálculo de média aritmética de preço revelaria um valor diferente: R$ 7,63.

2.2.4. Média desconsiderando os zeros

Calcular a média desconsiderando os zeros pode ser uma necessidade em que se deseje equilibrar o resultado calculado. Há inúmeras possibilidades para o cálculo com fórmulas. Neste artigo vamos elencar uma solução que visa “reconstruir” o cálculo da média: dividir a soma pela contagem. Vamos montar a solução com SOMASES e CONT.SES.

Em relação aos dados abaixo, note que o zero ocorre 3 vezes (células B10, B14 e B16) e desejamos obter a média desconsiderando-os, visto que contemplar estes zeros retornaria uma média com valor 40. Perceba a fórmula empregada com destaque em vermelho para calcular a média desconsiderando os zeros, que retorna resultado 55.

Nota: consideramos que os números seriam diferentes de zero para o cálculo da média. Com isso, em caso de haver números negativos, estes seriam também contemplados no cálculo. Caso desejemos apenas números acima de zero, usaríamos “>0” na fórmula (no último argumento de SOMASES e de CONT.SES).

Poderíamos de forma simplificada usar MÉDIASE. A fórmula ficaria: =MÉDIASE(B6:B16;"<>0"). Com MÉDIASES a fórmula ficaria =MÉDIASES(B6:B16;B6:B16;"<>0").

3. CALCULANDO MÉDIA COM TABELA DINÂMICA

Nesta seção vamos calcular a média usando tabela dinâmica, tanto o cálculo realizado diretamente na tabela dinâmica quanto o cálculo usando DAX.

3.1. Média diretamente na tabela dinâmica

Para calcular a média diretamente na tabela dinâmica podemos alterar o resumo dos valores. Na imagem a seguir a tabela dinâmica está montada e os casos de covid 19 estão sendo somados.

Para mudar o cálculo realizado, de soma para média, podemos proceder da seguinte forma: clicar em qualquer valor somado na tabela dinâmica e com o botão direito escolher a opção “Resumir Valores por“ e em seguida escolher a opção “Média”.

Note o resultado final na imagem a seguir.

Outra forma de alterar o cálculo para a média seria clicar em qualquer valor somado na tabela dinâmica e com o botão direito escolher a opção “Configurações do Campo de Valor“ e em seguida escolher a opção “Média”.

Note o resultado final na imagem a seguir.

3.2. Usando medidas DAX

a) Média aritmética

Para calcular a média aritmética usando DAX podemos utilizar a função AVERAGE. Ela é análoga à função MÉDIA do Excel. Requer o campo dos valores dos quais ela deverá retornar a média. Veja na imagem a seguir a escrita da medida DAX para o cálculo da média.

b) Média ponderada no Excel

Para calcular a média ponderada vamos utilizar o mesmo cenário abordado no item 2.4, sendo que desta vez utilizaremos DAX. Naquela ocasião calculamos o valor total comprado com SOMARPRODUTO. Em DAX, podemos fazer o mesmo utilizando a função SUMX. Podemos afirmar que o algoritmo será o mesmo: encontrar o valor total comprado e dividir o resultado pela quantidade total comprada.

Nossa tabela se chama fCompras. Veja a seguir como ficará a medida DAX:

Na imagem a seguir podemos visualizar a medida aplicada para o cálculo do preço médio.

Note que o valor do preço médio foi idêntico àquele apresentado no item 2.4.

Download Exemplo Calcular Média Excel

Realize o download da planilha de exemplo de como calcular média no Excel neste botão abaixo. Basta se inscrever na nossa newsletter gratuita para o download automático.

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo: