Como (e por quê) usar a função Outliers no Excel

Um valor atípico é um valor significativamente maior ou menor do que a maioria dos valores em seus dados. Ao usar o Excel para analisar dados, os valores discrepantes podem distorcer os resultados. Por exemplo, a média da média de um conjunto de dados pode realmente refletir seus valores. O Excel fornece algumas funções úteis para ajudar a gerenciar seus valores discrepantes, então vamos dar uma olhada.

Um exemplo rápido

Na imagem abaixo, os valores discrepantes são razoavelmente fáceis de detectar - o valor de dois atribuídos a Eric e o valor de 173 atribuído a Ryan. Em um conjunto de dados como esse, é fácil localizar e lidar com esses valores extremos manualmente.

Em um conjunto maior de dados, isso não acontecerá. Ser capaz de identificar os valores discrepantes e removê-los dos cálculos estatísticos é importante - e é isso que veremos como fazer neste artigo.

Como encontrar exceções em seus dados

Para encontrar os outliers em um conjunto de dados, usamos as seguintes etapas:

• Calcule o primeiro e o terceiro quartis (falaremos sobre o que eles são em pouco). / li>
• Avalie o intervalo interquartílico (também explicaremos isso um pouco mais abaixo).
• Retorne os limites superior e inferior de nosso intervalo de dados.
• esses limites para identificar os pontos de dados periféricos.

O intervalo de células à direita do conjunto de dados visto na imagem abaixo será usado para armazenar esses valores.

Vamos começar.

Etapa Um: Calcular os Quartis

Se você dividir seus dados em trimestres, cada um desses conjuntos será chamado de quartil. Os 25% mais baixos dos números da faixa compõem o primeiro quartil, os 25% seguintes, o segundo quartil, e assim por diante. Nós tomamos este passo primeiro porque a definição mais amplamente utilizada de um outlier é um ponto de dados que é mais do que 1,5 intervalos interquartis (IQRs) abaixo do 1º quartil, e 1,5 intervalos interquartis acima do 3º quartil. Para determinar esses valores, primeiro temos que descobrir quais são os quartis.

O Excel fornece uma função QUARTILE para calcular os quartis. Isso requer duas informações: a matriz e o quart.

A matriz é o intervalo de valores que você está avaliando. E o quart é um número que representa o quartil que você deseja retornar (por exemplo, 1 para o primeiro quartil, 2 para o segundo quartil e assim por diante).

Nota: No Excel 2010, a Microsoft lançou o QUARTILE.INC e QUARTILE.EXC funcionam como melhorias na função QUARTILE. O QUARTILE é mais retrocompatível ao trabalhar com várias versões do Excel.

Vamos voltar à nossa tabela de exemplo.

Para calcular o 1º quartil, podemos usar a seguinte fórmula na célula F2.

Ao inserir a fórmula, o Excel fornece uma lista de opções para o argumento quart.

Para calcular a terceira quartil, podemos inserir uma fórmula como a anterior na célula F3, mas usando um três em vez de um.

Agora, temos os quartis dos pontos de dados exibidos nas células.

Etapa dois: avaliar o intervalo interquartil

O intervalo interquartílico (ou IQR) é o meio dos valores de 50% em seus dados. É calculado como a diferença entre o valor do 1º quartil e o valor do 3º quartil.

Vamos usar uma fórmula simples na célula F4 que subtrai o 1º quartil do 3º quartil:

Agora, podemos ver nosso intervalo interquartílico exibido.

Etapa três: retornar os limites inferior e superior

Os limites inferior e superior são os menores e maiores valores do intervalo de dados que queremos usar. Quaisquer valores menores ou maiores que esses valores associados são os outliers.

Nós calcularemos o limite inferior da célula F5 multiplicando o valor IQR por 1,5 e, em seguida, subtraindo-o do ponto de dados Q1:

Nota: Os colchetes nesta fórmula não são necessários porque a parte de multiplicação irá calcular antes da parte de subtração, mas eles facilitam a leitura da fórmula.

Para calcular o limite superior na célula F6, multiplicaremos o IQR por 1,5 novamente, mas desta vez adicione-o ao ponto de dados Q3:

Passo Quatro: Identificar os Outliers

Agora que temos todos os nossos dados subjacentes configurados, é hora de identificar nossos pontos de dados distantes - aqueles que são menores que o valor do limite inferior ou maiores que o valor do limite superior. / p>

Usaremos a função OR para realizar este teste lógico e mostrar os valores que atendem a esses critérios inserindo a seguinte fórmula na célula C2:

Copiaremos esse valor para nossas células C3-C14. Um valor TRUE indica um valor atípico e, como você pode ver, temos dois em nossos dados.

Ignorando os valores discrepantes ao calcular a média média

Usando a função QUARTILE, vamos calcular o IQR e trabalhar com a definição mais amplamente usada de um outlier. No entanto, ao calcular a média da média de um intervalo de valores e ignorar outliers, há uma função mais rápida e fácil de usar. Essa técnica não identificará um outlier como antes, mas nos permitirá sermos flexíveis com o que poderíamos considerar como nossa porção de outliers.

A função que precisamos é chamada TRIMMEAN, e você pode ver a sintaxe dela abaixo:

A matriz é o intervalo de valores que você deseja avaliar. A porcentagem é a porcentagem de pontos de dados a serem excluídos da parte superior e inferior do conjunto de dados (você pode inseri-los como uma porcentagem ou um valor decimal).

Inserimos a fórmula abaixo na célula D3 em nosso exemplo para calcular a média e excluir 20% de outliers.

---

Há duas funções diferentes para lidar com valores atípicos. Se você quiser identificá-los para algumas necessidades de relatórios ou excluí-los de cálculos, como médias, o Excel tem uma função que atende às suas necessidades.

Via: How to Geek