Como usar a função de outliers no Excel

Quando você está analisando dados, os valores discrepantes podem distorcer os resultados e torná-los menos confiáveis. Por exemplo, se um ou mais de seus valores forem substancialmente mais altos do que a maioria dos dados, eles tornam a média mais alta, o que pode não refletir os dados como um todo. Remover esses pontos de dados remotos é, portanto, uma etapa importante na execução de cálculos estatísticos confiáveis. Sem função de outliers dedicada, a maneira mais fácil de realizar um teste de outlier no Excel é usar o intervalo interquartil para identificar outliers, mas você também pode usar a função Trimmean para aproximar o mesmo resultado.

O intervalo interquartil de dados é o intervalo coberto pela "caixa" em um gráfico de caixa e bigode, ou mais precisamente, o resultado da subtração do valor para o primeiro quartil dos dados daquele do terceiro quartil. O Excel contém um recurso integrado para calcular qualquer quartil para seus dados. Encontre uma célula sobressalente e digite "= QUARTILE ([intervalo de dados], [número do quartil])" e coloque o intervalo de células para seus dados onde diz "[intervalo de dados]" e o quartil desejado onde diz "[quartil número]."

Por exemplo, se você tiver dados nas células A2 a A101 e quiser o valor para o primeiro quartil, digite "= QUARTILE (A2: A101, 1)" para encontrar o valor. Para a primeira parte do argumento, você pode destacar as células relevantes com o mouse, mas após a vírgula, você precisa escrever o número do quartil desejado. Para o terceiro quartil com os mesmos dados, digite "= QUARTILE (A2: A101, 3)" para obter o resultado.

Usando outra célula vazia, subtraia o valor da célula do primeiro quartil do valor da célula do terceiro quartil. Se o primeiro quartil estiver na célula C2 e o terceiro quartil na célula D2, digite "= D2-C2" para obter o resultado. Este é o intervalo interquartil.

Para descobrir outliers, agora você pode usar o intervalo interquartil na fórmula outlier, que afirma que o limite superior dos dados é o valor do terceiro quartil mais 1,5 vezes o intervalo interquartil, e o limite inferior é o valor do primeiro quartil menos 1,5 vezes o interquartil alcance.

Se o valor do primeiro quartil está em C2, o valor do terceiro quartil está na célula D2, e o intervalo interquartil é na célula E2, você digitaria "= C2- (1,5 * E2)" para encontrar o limite inferior e "= D2 + (1,5 * E2)" para encontrar o limite superior limite. Em geral, você insere "= [primeiro quartil] - (1,5 * [intervalo interquartil])" para encontrar o limite inferior e "= [terceiro quartil] + (1,5 * [intervalo interquartil])" para encontrar o limite superior.

Qualquer coisa abaixo do limite inferior ou acima do limite superior é um outlier.

Para terminar o teste de valores discrepantes no Excel, use a função lógica "OU" para identificar quais valores em sua classe de dados são valores discrepantes de maneira eficiente. Digite "= OR ([célula de dados]> [limite superior], [célula de dados] $ F $ 2, A2

Se o valor em A2 estiver acima do limite superior ou abaixo do limite inferior, ele exibe "TRUE", indicando que o valor é um outlier. Você pode arrastar esta fórmula para baixo clicando no canto inferior direito da célula com a fórmula e arrastando-o para baixo de modo que termine ao lado da célula de dados final para realizar o mesmo cálculo em cada ponto de dados.

Você também pode destacar os dados e ir para "Formatação condicional" na seção "Estilos" da guia "Página inicial" se desejar alterar a formatação dos outliers. Escolha "Nova regra" e destaque a opção "Usar uma fórmula para determinar quais células formatar". Digite a mesma fórmula do parágrafo anterior e clique na opção "Formatar" para escolher a formatação exclusiva para outliers.

A função "Trimmean" é uma maneira mais simples de identificar valores discrepantes. Digite "= TRIMMEAN ([intervalo de dados], [proporção ao corte])" para usar a função, com o intervalo de células contendo dados no lugar de "[intervalo de dados]" e um decimal porcentagem que você deseja cortar onde diz "[proporção para cortar]." Isso remove os valores extremos na parte superior e inferior e, em seguida, calcula a média com base nesses remanescente. Portanto, se você cortar 10%, removerá os 5% superiores e os 5% inferiores antes de calcular a média.

Se os dados vão de A2 a A101 e você deseja cortar os 5% extremos dos valores, digite "= TRIMMEAN (A2: A101, 0,05)" para encontrar a média ajustada. Você poderia cortar 15 por cento escrevendo "= TRIMMEAN (A2: A101, 0,15)".