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:
