Excel: como calcular a média excluindo outliers

Existem duas maneiras de calcular uma média e excluir valores discrepantes no Excel:

1. Calcule a média e use TRIMMEAN para excluir valores discrepantes

2. Calcule a média e use o intervalo interquartil para excluir valores discrepantes

Usaremos o seguinte conjunto de dados no Excel para ilustrar como usar os dois métodos:

Método 1: Calcule a média e use TRIMMEAN para excluir valores discrepantes

A função TRIMMEAN no Excel pode ser usada para calcular a média de um intervalo de valores, excluindo uma certa porcentagem de observações da parte superior e inferior do conjunto de dados.

Por exemplo, podemos usar a seguinte fórmula para calcular o valor médio na coluna A excluindo um total de 20% das observações (10% superiores e 10% inferiores):

 =TRIMMEAN( A2:A16 , 20%)

Como temos 15 valores em nosso conjunto de dados, 10% é igual a 1,5, que é arredondado para 1. Portanto, esta fórmula calculará a média dos valores no intervalo, excluindo o menor valor e o maior valor:

A média, excluindo valores discrepantes, é 58,30769 .

Método 2: Calcular a média e usar o intervalo interquartil para excluir valores discrepantes

O intervalo interquartil (IQR) é a diferença entre o percentil 75 (Q3) e o percentil 25 (Q1) em um conjunto de dados. Mede a distribuição da média de 50% dos valores.

Podemos definir uma observação como outlier se for 1,5 vezes o intervalo interquartil acima do terceiro quartil (Q3) ou 1,5 vezes o intervalo interquartil abaixo do primeiro quartil (Q1).

Podemos usar a seguinte fórmula para calcular o intervalo interquartil do nosso conjunto de dados no Excel:

 =QUARTILE( A2:A16,3 )-QUARTILE( A2:A16,1 )

A captura de tela a seguir mostra como usar esta fórmula:

Em seguida, podemos usar a seguinte fórmula para usar o IQR para identificar valores discrepantes e atribuir um “1” a qualquer valor que seja um valor discrepante no conjunto de dados:

 =IF(OR( A2 <QUARTILE( $A$2:$A$16 ,1)-1.5* $B$18 , A2 >QUARTILE( $A$2:$A$16 ,3)+1.5* $B$18 ),1, 0)

A captura de tela a seguir mostra como usar esta fórmula:

Vemos que apenas um valor – 164 – acaba sendo um valor discrepante neste conjunto de dados.

Finalmente, podemos usar a seguinte fórmula para calcular a média de todos os valores no conjunto de dados que não são discrepantes:

 =AVERAGEIF( B2:B16 , 0, A2:A16 )

A captura de tela a seguir mostra como usar esta fórmula:

A média, excluindo valores discrepantes, é 55,42857 .

Recursos adicionais

Os tutoriais a seguir explicam como realizar outras tarefas comuns no Excel:

Como calcular o intervalo interquartil (IQR) no Excel
Como calcular a média por grupo no Excel
Como calcular médias móveis ponderadas no Excel