=MÉDIASE(A2:A7;"North";C2:C7) calcula a média das vendas de C2:C7 nas linhas em que a coluna A é North. A MÉDIASE (AVERAGEIF em inglês) funciona como a SOMASE, só que divide o total pelo número de linhas que batem. A tabela mostra as fórmulas em inglês, com vírgulas, e também aceita a forma em português, com ponto e vírgula.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Condition | Average | |
| 2 | North | Apple | 120 | North | 90 | |
| 3 | South | Pear | 45 | North, Apple | 80 | |
| 4 | North | Pear | 110 | Over 50 | 120 | |
| 5 | East | Apple | 55 | |||
| 6 | South | Apple | 195 | |||
| 7 | North | Apple | 40 |
=MÉDIASE(A2:A7;"North";C2:C7)F2 calcula a média das três linhas de North, 120, 110 e 40, e mostra 90. F3 precisa de duas condições, North e Apple, então usa a MÉDIASES (AVERAGEIFS em inglês): (120 + 40) / 2 = 80. F4 não tem um intervalo de média separado, então calcula a média das próprias vendas que batem.
Sintaxe da MÉDIASE e da MÉDIASES
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
A ordem dos argumentos é a mesma armadilha da SOMASE e da SOMASES: a MÉDIASE coloca o intervalo da média por último (e deixa você omiti-lo), a MÉDIASES o coloca primeiro. Os critérios são escritos do mesmo jeito nas duas: "North", ">50", "<>0", "*apple*", ou um operador juntado a uma célula, ">"&F5. Células vazias e texto no intervalo da média são ignorados.
Média ignorando os zeros
A MÉDIA (AVERAGE em inglês) conta um 0 como valor, então dois alunos ausentes com nota 0 puxam a média da turma para baixo. Células vazias são diferentes: a MÉDIA as ignora. =MÉDIASE(B2:B7;"<>0") ignora também os zeros.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Student | Score | Method | Result | |
| 2 | Ana | 80 | AVERAGE | 48 | |
| 3 | Ben | 0 | Ignore zeros | 80 | |
| 4 | Cara | 90 | Count of zeros | 2 | |
| 5 | Dan | Count of numbers | 5 | ||
| 6 | Eva | 70 | |||
| 7 | Finn | 0 |
=MÉDIASE(B2:B7;"<>0")A MÉDIA divide 240 por 5, porque a célula vazia de Dan fica de fora mas os dois zeros contam, e mostra 48. A MÉDIASE com "<>0" divide 240 por 3 e mostra 80. Digite 60 em B5 e as duas mudam; digite 0 em B5 e só a MÉDIA se move. Para ignorar também os números negativos, use ">0".
Por que a MÉDIASE retorna #DIV/0!
Quando nada bate, a MÉDIASE não tem pelo que dividir e retorna #DIV/0!. Envolva a fórmula na SEERRO (IFERROR em inglês) para mostrar um traço, uma mensagem ou uma célula vazia no lugar.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Sales | Formula | Result | |
| 2 | North | Apple | 120 | West average | #DIV/0! | |
| 3 | South | Pear | 45 | With IFERROR | No sales | |
| 4 | North | Pear | 110 | North max | 120 | |
| 5 | East | Apple | 55 | North min | 40 | |
| 6 | South | Apple | 195 | Apple max | 195 | |
| 7 | North | Apple | 40 |
#DIV/0! A fórmula divide por zero ou por uma célula vazia.No Excel em português: =MÉDIASE(A2:A7;"West";C2:C7)Não existe linha de West, então F2 mostra #DIV/0! e F3 mostra a mensagem. Mude A3 para West e as duas mostram 45.
MÁXIMOSES e MÍNIMOSES
F4 a F6 na tabela acima encontram o maior e o menor valor com uma condição. Elas usam a ordem da MÉDIASES, com o intervalo a pesquisar primeiro: =MÁXIMOSES(C2:C7;A2:A7;"North") (MAXIFS em inglês) retorna 120 e =MÍNIMOSES(C2:C7;A2:A7;"North") (MINIFS em inglês) retorna 40. Ao contrário da MÉDIASE, elas retornam 0 quando nada bate, não um erro.
A MÁXIMOSES e a MÍNIMOSES precisam do Excel 2019 ou posterior, ou do Microsoft 365. No Excel 2016 e anteriores, =MÁXIMO(SE(A2:A7="North";C2:C7)) faz o mesmo; pressione Ctrl+Shift+Enter (Cmd+Shift+Enter no Mac) para inserir a fórmula nessas versões.
Prática: média com duas condições
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Class | Score | Condition | Average | |
| 2 | Ana | A | 80 | Class A, no zeros | ||
| 3 | Ben | B | 75 | |||
| 4 | Cara | A | 0 | |||
| 5 | Dan | B | 60 | |||
| 6 | Eva | A | 90 | |||
| 7 | Finn | B | 0 | |||
| 8 | Gus | A | 70 |
Sua vez: Calcule a média das notas da turma A, deixando de fora os zeros (alunos ausentes). Escreva a fórmula em F2.
Média de médias: um erro comum
Calcular a média das médias de grupos de tamanhos diferentes dá a média geral errada. North tem três linhas e South duas, então cada linha de South pesa mais do que deveria na média das duas médias.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Region | Sales | Formula | Result | |
| 2 | North | 120 | North | 90 | |
| 3 | South | 45 | South | 120 | |
| 4 | North | 110 | Average of the two | 105 | |
| 5 | South | 195 | All rows | 102 | |
| 6 | North | 40 |
=MÉDIA(E2:E3)E4 mostra 105, E5 a média real das cinco linhas, 102. Quando os grupos têm tamanhos diferentes, calcule a média das próprias linhas com uma MÉDIASES, ou divida uma SOMASES por um CONT.SES com as mesmas condições:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
No Excel em português: =SOMASES(B2:B6;A2:A6;"North")/CONT.SES(A2:A6;"North").
Uma nota ponderada por créditos ou por quantidade é outro cálculo: isso é uma média ponderada.
Perguntas frequentes
Qual a diferença entre MÉDIASE e MÉDIASES?
A MÉDIASE recebe uma condição e coloca o intervalo da média por último: =MÉDIASE(A2:A7;"North";C2:C7). A MÉDIASES recebe várias condições e coloca o intervalo da média primeiro: =MÉDIASES(C2:C7;A2:A7;"North";B2:B7;"Apple").
Como calcular a média no Excel ignorando os zeros?
Use =MÉDIASE(B2:B7;"<>0"). Ela calcula a média só das células diferentes de 0. A MÉDIA e a MÉDIASE já deixam as células vazias de fora, então só os zeros de verdade precisam da condição.
Por que a MÉDIASE retorna #DIV/0!?
Nenhuma célula atendeu à condição, então o Excel divide uma soma de 0 por uma contagem de 0. Envolva a fórmula para mostrar outra coisa: =SEERRO(MÉDIASE(A2:A7;"West";C2:C7);"No data").
Como achar o valor máximo com uma condição?
Use a MÁXIMOSES, com o intervalo a pesquisar primeiro: =MÁXIMOSES(C2:C7;A2:A7;"North") retorna o maior valor de North. A MÍNIMOSES funciona do mesmo jeito para o menor. As duas precisam do Excel 2019 ou posterior, ou do Microsoft 365.
