Planilhas google: calcule a média excluindo valores discrepantes

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

1. Use TRIMMEAN para excluir valores discrepantes

2. Use o intervalo interquartil para excluir valores discrepantes

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

Método 1: use TRIMMEAN para excluir valores discrepantes

A função TRIMMEAN calcula a média de um intervalo de valores enquanto exclui 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 irá calcular a média dos valores do intervalo excluindo o menor valor e o maior valor:

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

Método 2: use 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 de nosso conjunto de dados no Planilhas Google:

 =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:

O único valor que acaba sendo um valor discrepante é 164 .

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 Planilhas Google:

Como calcular a média por grupo no Planilhas Google
Como calcular a porcentagem média no Planilhas Google
Como calcular uma média ponderada no Planilhas Google