Como Calcular Média no Excel – Funções mais Importantes

Neste artigo apresentarei como calcular média no Excel para diferentes tipos de dados usando as fórmulas MÉDIA ou MÉDIAA. Também apresentarei como usar as funções MÉDIASE e MÉDIASES para casos de médias que precisam atender a determinados critérios.

Em resumo, calcular a média para um conjunto de dados é descobrir o valor mais comum do conjunto.

Por exemplo, se uma dezena de atletas profissionais correm uma corrida de 100 metros, com os resultados de tempos de cada um podemos calcular o tempo médio para execução da prova, ou seja, em quanto tempo usualmente um velocista leva para completar o percurso de 100 metros.

Na matemática, a média é chamada de média aritmética e é calculada pela soma da série de dados dividido pela quantidade de dados da série.

No exemplo acima, se o primeiro atleta terminasse a prova em 10,5 segundos, o segundo precisasse de 10,7 segundos e o terceiro 11,2 segundos, o tempo médio seria de 10,8 segundos, pois = (10,5 + 10,7 + 11,2) / 3.

Para calcular a média no Excel não precisaremos escrever as expressões matemáticas propriamente ditas. As poderosas funções do Excel farão todo o trabalho por nós. Mais adiante neste artigo, vamos discutir a sintaxe de cada função que mencionei (MÉDIA, MÉDIAA, MÉDIASE, MÉDIASES) e, claro, acompanhados de bons exemplos de uso.

Função MÉDIA no Excel

A função MÉDIA no Excel para retornar a média aritmética de um conjunto de dados especificado.

=MÉDIA(número1;[número2];…)

númer1, número2 e etc são os valores numéricos do conjunto de dados do qual desejamos encontrar a média.

O primeiro parâmetro é obrigatório, os subsequentes são opcionais. Podemos inserir até 255 parâmetros em uma única fórmula. Os parâmetros são fornecidos como números, referências de célula ou intervalos. Vale ressaltar que não são 255 o total de dados para o cálculo da média, mas sim o total de parâmetros.

Atenção você deve tomar os seguintes cuidados com a função MÉDIA:

• A função MÉDIA no Excel inclui valores como zero no calculo da média, o que pode ser indesejado. Caso você precise excluir os zeros, use a função MÉDIASE, como veremos em uma próxima seção.
• Células contendo texto, valores booleanos de VERDADEIRO ou FALSO e células vazias são ignoradas. Se você deseja incluir valores booleanos e representações de números de texto no cálculo, use a função MÉDIAA.
• Valores booleanos de VERDADEIRO ou FALSO que você digita diretamente são contados na MÉDIA. Por exemplo, a fórmula =MÉDIA(VERDADEIRO, FALSO) retorna 0,5, que é a média de 1 e 0.
• Cheque se a opção “Mostrar um zero nas células cujo o valor é zero” está marcado para evitar confusão no cálculo da média considerar valores que são zero, porém nas células estão em branco.
  É possível checar isso no caminho Excel>Arquivo>Opções>Avançado, como na figura abaixo:
  

Exemplo 1. Calculando uma média aritmética

Para calcular uma média aritmética de determinados dados, você pode fornecê-los diretamente na sua fórmula média do Excel. Por exemplo, =MÉDIA(1,2,3,4) retorna 2,5 como resultado.

Para calcular uma média aritmética de uma coluna, podemos fornecer a referência para a coluna inteira:

=MÉDIA(A:A)

Para calcular a média aritmética de uma linha, podemos fornecer a referência para a linha inteira:

=MÉDIA(1:A)

Para calcular a média aritmética de dados dispostos em um intervalo como matriz, especifique esse intervalo na fórmula MÉDIA:

=MÉDIA(A1:C20)

Para retornar uma média de células não adjacentes, podemos fornecer cada célula individualmente, por exemplo, como na fórmula abaixo:

=MÉDIA(A1;C1;D1)

E, claro, nada impede que você inclua valores, referências de células, intervalos ou números na mesma fórmula. Por exemplo, a fórmula média a seguir calcula a média de dois intervalos (B3:B5 e C7:D9), uma célula individual (B11) e o número 12:

=MÉDIA(B3:B5;C7:D9;B11;12)

DICA: Para arredondar o resultado da média para o número inteiro mais próximo, use a função de arredondamento do Excel a ARRED, por exemplo:

=ARRED(MÉDIA(B3:B5;C7:D9;B11;12), 0)

Além da média numérica, você pode usar a função MÉDIA no Excel para calcular a média de outro tipo de dados como datas e horas, conforme demonstrado nos exemplos a seguir.

Exemplo 2. Calculando a data média no Excel

Suponhamos que você tenha uma planilha sobre um curso de ensino a distância com a data de conclusão do curso de uma centena de alunos. Considerando que todos iniciaram o curso na mesma data poderíamos calcular qual a data de conclusão em média para o referido curso.

Exemplo 3. Calculando o tempo médio no Excel

No início deste artigo, calculamos o tempo médio de três velocistas com um cálculo bem simples. Mas e se precisarmos calcular a média de um conjunto de dados que incluem horas, minutos e segundos? Calcular diferentes unidades de tempo à mão seria chato, entretanto na função MÉDIA no Excel não há distinção sobre o formato dos dados. O motivo está relacionado com a forma como o Excel trata datas e horas, como expliquei neste outro artigo aqui.

Por exemplo, imagine que agora nossos três velocistas disputaram uma maratona. Para calcularmos o tempo médio de conclusão da prova devemos fazer como na figura abaixo:

Função MÉDIAA do Excel

A função MÉDIAA é semelhante a função MÉDIA, pois calcula a média aritmética dos valores em seus parâmetros. A diferença é que a função MÉDIAA inclui todas as células não vazias em um cálculo, se elas contêm números, texto, valores booleanos de VERDADEIRO ou FALSO e  células com texto em branco, resultado de outras fórmulas.

MÉDIAA(valor1;[valor2];…)

Valor1, valor2,… são valores de referência de células ou intervalos que você deseja avaliar. O primeiro parâmetro é obrigatório, outros (até 255) são opcionais da mesma forma como em MÉDIA.

Como mencionado acima, a função MÉDIAA processa diferentes tipos de valor, como números, células com texto e valores lógicos de VERDADEIRO ou FALSO. Portanto, devemos considerar os seguintes critérios:

• Células vazias são ignoradas
• Valores de texto, incluindo células vazias (“”) retornadas por outras fórmulas são avaliadas como 0
• O valor booleano VERDADEIRO é avaliado como 1 e FALSO é avaliado como 0

Por exemplo, a fórmula =MÉDIAA(2;FALSO) retorna 1, que é a média de 2 e 0. A fórmula =MÉDIAA(2;VERDADEIRO) retorna 1,5, que é a média de 2 e 1.

Função MÉDIASE no Excel

A função MÉDIASE no Excel calcula a média aritmética) de todas as células que atendem a um critério especificado.

=MÉDIASE(intervalo;critérios;[intervalo_média])

A função MÉDIASE possui os seguintes parâmetros, os 2 primeiros são obrigatórios, o último é opcional:

• intervalo – o intervalo de células a ser testado em relação aos critérios fornecidos
• critérios – a condição usada para determinar quais células integrarão a média. Os critérios podem ser fornecidos na forma de um número, expressão lógica, texto ou referência de célula, por exemplo, 5, “> 5”, “José” ou A2
• intervalo_média – as células que integrarão a média (opcional). Caso seja omitido, a fórmula calculará a média dos valores do parâmetro intervalo.

Atenção a função MÉDIASE está disponível para o Excel nas versões 2010 e superior.

Nas próximas seções vamos ver como usar a função MÉDIASE em casos reais de necessidade de encontrar a média dado certo critério.

Exemplo 1. Cálculo da média de dados que se repetem em uma tabela

O caso clássico de uso da função MÉDIASE no Excel é para encontrar a média de dados que estão desordenados, então o critério seria calcular a média para dados com um determinado critério em comum. Neste exemplo, vamos calcular o preço médio (C2: C10) apenas para produtos (B2:B10) de nome “Cadeira”.

=MÉDIASE(B2:B10;"Cadeira";C2: C10)

Ao invés de inserir a condição diretamente em uma fórmula, você pode digitá-la em uma célula separada e fazer referência a essa célula em sua fórmula:

=MÉDIASE(B2:B10;F1;C2: C10)

Dica: Para arredondar o valor do cálculo para um determinado número de casas decimais, use a função de arredondamento ARRED.

Por exemplo, para arredondar a média retornada pela fórmula acima para duas casas decimais, você pode envolvê-la na função ARRED assim:

=ARRED(MÉDIASE(B2:B10;"Cadeira";C2: C10);2)

Como outra alternativa, você pode selecionar a célula com a fórmula (F4 neste exemplo), pressionar CTRL+1 para abrir a o menu de formatação de célula. Então, altere a formatação para a opção Número ou Moeda e selecione o número de casas decimais que deseja exibir.

Vale lembrar que, neste caso, o valor real armazenado em uma célula não será alterado, e o valor exato (não arredondado) será usado nos cálculos em que esteja presente.

Exemplo 2. Cálculo da média de dados que se repetem parcialmente em uma tabela

Na fórmula MÉDIASE no Excel podemos usar os caracteres curinga no parâmetro critérios para calcularmos a média de dados que correspondem parcialmente ao critério definido. Os caracteres curinga são os seguintes:

• O ponto de interrogação (?) corresponde a um caractere único
• O asterisco (*) corresponde a qualquer sequência de caracteres
• Para  ser possível localizar um ponto de interrogação ou asterisco, digite um til (~) antes do caractere nos critérios

No exemplo anterior, suponha que você tenha 3 tipos de cadeiras diferentes e queira encontrar a média delas. Entretanto as cadeiras tem palavras antes e depois do texto real que define nosso critério “Cadeiras”. A solução é como na fórmula abaixo:

=MÉDIASE (B2:B10;"*Cadeira*";C2: C10)

Desta forma iremos encontrar todos os registros na coluna B da tabela que tenham a palavra “Cadeira” em qualquer parte do texto.

Dica: Para encontrar a média de todos os itens, excluindo qualquer produto que tenha “Cadeira” no texto, use a seguinte fórmula:

=MÉDIASE(B2:B10;"<>*Cadeira*";C2: C10)

Portanto, o par de caracteres “<>” serve como sinal para indicar que o texto a seguir não deve ser encontrado no intervalo avaliado para cálculo da média.

Exemplo 3. Cálculo da média com o uso de operadores lógicos

Suponha que queremos calcular a média de uma série de dados, mas somente para valores que são maior que um determinado valor. Por exemplo, temos uma lista de preços na coluna A e queremos encontrar a média de preços de produtos que estão acima de R$100,00.

A maneira correta de inserir esse tipo de critérios é digitar a fórmula da operação lógica como se fosse um texto. Então, sua fórmula para calcular a média no Excel seria a seguinte:

=MÉDIASE(A2:A7;">100")

Outra tarefa comum é a média de números que não são iguais a zero. Para isso, você precisaria do operador “<>” no parâmetro de critérios da sua fórmula MÉDIASE, assim como vimos na seção anterior no critério de texto. Portanto, a fórmula ficaria como a seguir, caso não quiséssemos computar eventuais valores 0:

=MÉDIASE(A2:A7;"<>0")

Como você deve ter notado, não usamos o terceiro argumento [intervalo_média] neste exemplo, pois o intervalo avaliado pelo critério é a própria série de dados.

Exemplo 4. Cálculo da média com MÉDIASE considerando células vazias e não vazias

Ao executar a análise de dados no Excel precisamos levar em consideração todos os fatores que podem afetar nosso cálculo para não chegarmos a conclusões erradas por um erro de avaliação. Entretanto, checar manualmente uma tabela com muitos registros pode ser impraticável. Então, devemos sempre levar em conta os casos de existência de valores em branco ou não na nossa série de dados.

Para incluir células em branco que contenham absolutamente nada (sem fórmula, sem texto, sem número), insira “=” no argumento de critérios. Por exemplo, a fórmula a seguir calcula uma média de células C2:C8 se uma célula na coluna B na mesma linha estiver absolutamente vazia:

=MÉDIASE(B2: B8;"=";C2:C8)

Para calcular a media de células visualmente em vazias, incluindo aquelas que contêm texto vazio do retorno de outras funções (por exemplo, células com uma fórmula como =””), use “” no parâmetro critérios. Por exemplo:

=MÉDIASE(B2:B8;"";C2:C8)

Para calcular a média de valores correspondentes a células não vazias, digite “<>” em critérios.

Por exemplo, a seguinte fórmula MÉDIASE calcula uma média de células C2:C8 se uma célula na coluna B na mesma linha não estiver em branco:

=MÉDIASE (B2: B8;"<>";C2:C8)

Exemplo 5. Cálculo da média com MÉDIASE com critérios compostos

Em vez de digitar os critérios em uma fórmula podemos nos referir a uma determinada célula onde seus usuários podem inserir valores diferentes critérios de forma que o restante da fórmula permaneça inalterado.

Caso uma referência de célula seja um critério de correspondência exata, basta digitá-la no argumento de critérios, como fizemos no Exemplo 1:

=MÉDIASE(A2:A8;E1;B2: B8)

Para usarmos uma expressão lógica com uma referência de célula ou outra função em critérios, será necessário incluir o operador lógico entre aspas e incluir o E comercial (&) para concatenar uma referência ou função de célula. Eu expliquei mais sobre o operador & neste outro artigo aqui.

Por exemplo, para calcular a média de vendas (C2: C8) que são maiores que o valor em E4, use a seguinte fórmula:

=MÉDIASE(C2:C8;">" & E4)

Com datas em B2:B8, a fórmula abaixo retorna a média de vendas (C2:C8) que fizemos até a data atual:

=MÉDIASE(B2:B8;"<=" & HOJE();C2:C8)

Função MÉDIASES lógica E no Excel

A função MÉDIASES no Excel é a opção multicritério de MÉDIASE. Ela permite múltiplas condições e retorna a média aritmética das células que atendem a todos os critérios especificados.

MÉDIASES(intervalo_média;intervalo_critérios1;critério1;…)

A função MÉDIASES possui os seguintes parâmetros:

• intervalo_média – o intervalo de células que você deseja calcular.
• intervalo_critérios1 – 1 a 127 intervalos podem ser testados em relação aos critérios especificados
• critério1 – é obrigatório, os subsequentes são opcionais. É possível escolher de 1 a 127 critérios que determinam quais células participaram do cálculo da média. Os critérios podem ser fornecidos na forma de um número, expressão lógica, valor de texto ou referência de célula.

Atenção a função MÉDIASES está disponível para o Excel nas versões 2010 e superior.

Nas próximas seções vamos ver como usar a função MÉDIASES em casos reais de necessidade de encontrar a média dado certos critérios.

Exemplo 1. Cálculo da média com MÉDIASE por múltiplos critérios (texto e número)

Suponha que na mesma lista de produtos e quantidades que utilizamos nos outros exercícios precisamos calcular a média do preço de Mesas que tem quantidade acima de 250 unidades.

Para este exemplo a fórmula MÉDIASES será:

• intervalo_média é C2:C10 (células que você deseja calcular se ambas as condições forem atendidas)
• intervalo_critérios1 é A2:A10 (coluna Produto) e criterio1 é “Mesa“
• intervalo_critérios2 é C2:C10 (coluna Quantidade) e criterio2 é “>250“

Reunindo os componentes acima, obtemos a seguinte fórmula:

=MÉDIASES(C2:C10;A2:A10;"Mesa";C2:C10;">250")

Vale lembrar que você pode substituir os critérios “Mesa” e “>250” por referências de célula, assim como foi feito em exemplos da seção anterior.

Como você vê, apenas as células (C3 e C5) atendem a ambas as condições e, portanto, apenas essas células participaram do cálculo da média.

Exemplo 2.

Cálculo da média com MÉDIASE por múltiplos critérios (data)

Neste exemplo imagine que na mesma lista anterior queremos calcular a média da quantidade de produtos registrado no nosso estoque entre 02/01/2019 e 08/01/2019.

=MÉDIASES(C2:C10;B2:B10;”<08/01/2019″;B2:B10;”>01/01/2019″)

No critério1 e critério2 formamos o intervalo fechado de seleção de datas que é destacado com fundo amarelo na imagem acima.

Os demais operadores “=”, “&” e “<>” realizam as mesmas definições de critério que as descritas para MÉDIASE.

Calcular a média com lógica OU no Excel

Como a função Excel MÉDIASES trabalha com a lógica E e a função MÉDIAS permite apenas 1 critério, teremos que criar nossa própria fórmula para calcular a média de múltiplos critérios com a lógica OU. Em outras palavras, faremos uma fórmula para calcular a média no Excel se qualquer um dos critérios especificados estiver atendida.

Exemplo 1. Média com lógica OU baseada em múltiplos critérios de texto

Suponha que você queira obter uma média de vendas (C2: C8) para banana e maçã (A2: A8). Para calcular isso, você precisaria de uma fórmula de matriz que incluísse algumas funções do Excel, como a função SE() e outras:

=MÉDIA(SE(NÚMERO(CORRESP(A2:A10;{"Cadeira", "Mesa"};0));B2:B10))

LEMBRE-SE de que as fórmulas que contém matriz (coma a de cima) precisam ser inseridas por meio de CTRL+SHIFT+ENTER, e não apenas ENTER.

De todas as fórmulas de média no Excel discutidas até agora, essa é a mais complicado. Use o modelo de fórmula abaixo para encaixar na sua planolha

=MÉDIA(SE (NÚMERO(CORRESP(intervalo;{"critério1", "critério2"...};0));intervalo_média))

Detalhes a lembrar nas funções MÉDIASE e MÉDIASES

As funções do Excel MÉDIASE e MÉDIASES têm muito em comum. Para ambas devemos lembrar do seguinte:

• No parâmetro intervalo_média, células vazias, valores booleanos de VERDADEIRO ou FALSO e valores de texto são ignorados
• No parâmetro intervalo_critério e critério, as células vazias são tratadas como valores zero (0)
• Se intervalo_critério contiver apenas células em branco ou valores de texto, ambas as funções retornarão o erro #DIV/0
• Se nem uma única célula atende ao critério ou todos os critérios, no caso de MÉDIASES, a função retornará #DIV/0

Conclusão

Neste artigo discutimos as diversas maneiras de calcular uma média, principalmente como calcular média considerando um único critério e múltiplos critérios com lógica E ou OU.

Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas do Excel para que no futuro você possa se tornar um usuário avançado.

Portanto, continue lendo sobre Excel básico aqui ou mais dicas e truques no Excel aqui.