Pagina inicial » como » Como (e por que) usar a função Outliers no Excel

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

    Um outlier é 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 outliers, então vamos dar uma olhada.

    Um exemplo rápido

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

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

    Como encontrar outliers em seus dados

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

    1. Calcular o 1º e o 3º quartis (falaremos sobre o que eles são daqui a pouco).
    2. Avalie o intervalo interquartílico (também vamos explicar isso um pouco mais abaixo).
    3. Retorna os limites superior e inferior do nosso intervalo de dados.
    4. Use 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 1: 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 no intervalo 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 usada 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 quartis. Requer duas informações: a matriz e o quart.

    = QUARTILE (array, quart)

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

    Nota: No Excel 2010, a Microsoft liberou as funções QUARTILE.INC e QUARTILE.EXC como aprimoramentos para a função QUARTILE. QUARTILE é mais retrocompatível ao trabalhar em várias versões do Excel.

    Vamos voltar para nossa tabela de exemplo.

    Para calcular o 1st Quartile podemos usar a seguinte fórmula na célula F2.

    = QUARTILE (B2: B14,1)

    Conforme você insere a fórmula, o Excel fornece uma lista de opções para o argumento quart.

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

    = QUARTILE (B2: B14,3)

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

    Segundo Passo: Avalie o Interquartile Range

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

    Nós vamos usar uma fórmula simples na célula F4 que subtrai o 1st quartil dos 3rd quartil:

    = F3-F2

    Agora, podemos ver nosso intervalo interquartil exibido.

    Terceiro Passo: Retorne 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 vinculados são os outliers.

    Vamos calcular o limite inferior da célula F5 multiplicando o valor IQR por 1,5 e subtraindo-o do ponto de dados Q1:

    = F2- (1,5 * F4)

    Nota: Os colchetes nesta fórmula não são necessários porque a parte de multiplicação será calculada 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 adicionar para o ponto de dados Q3:

    = F3 + (1,5 * F4)

    Quarta etapa: 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.

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

    = OR (B2 $ F $ 6)

    Então, copiaremos esse valor em nossas células C3-C14. Um valor VERDADEIRO indica um valor atípico e, como você pode ver, temos dois em nossos dados.

    Ignorando os outliers ao calcular a média média

    Usando a função QUARTILE, vamos calcular o IQR e trabalhar com a definição mais amplamente utilizada 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á ser 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 abaixo:

    = TRIMMEAN (array, por cento)

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

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

    = TRIMMEAN (B2: B14, 20%)


    Lá você tem duas funções diferentes para lidar com outliers. 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..