Ao trabalhar com dados no Excel, você geralmente terá problemas para lidar com outliers em seu conjunto de dados.
Ter valores discrepantes é bastante comum em todos os tipos de dados, e é importante identificar e tratar esses valores discrepantes para garantir que sua análise seja correta e mais significativa.
Neste tutorial, vou mostrar a você como descobrir outliers no Excel, e algumas das técnicas que usei em meu trabalho para lidar com esses outliers.
O que são valores discrepantes e por que é importante encontrá-los?
Um outlier é um ponto de dados que está muito além dos outros pontos de dados no conjunto de dados. Quando você tem um valor discrepante nos dados, ele pode distorcer seus dados, o que pode levar a inferências incorretas.
Deixe-me dar um exemplo simples.
Digamos que 30 pessoas estejam viajando em um ônibus do destino A ao destino B. Todas as pessoas estão em um grupo de peso e renda semelhante. Para o propósito deste tutorial, vamos considerar o peso médio de 220 libras e a renda média anual de $ 70.000.
Agora, em algum lugar no meio de nossa rota, o ônibus para e Bill Gates entra.
Agora, o que você acha que isso faria com o peso médio e a renda média das pessoas no ônibus.
Embora o peso médio provavelmente não mude muito, a renda média das pessoas no ônibus vai disparar fortemente.
Isso ocorre porque a receita de Bill Gates é um valor atípico em nosso grupo e isso nos dá uma interpretação errada dos dados. A renda média de cada pessoa no ônibus seria de alguns bilhões de dólares, o que está muito além do valor real.
Ao trabalhar com conjuntos de dados reais no Excel, você pode ter valores discrepantes em qualquer direção (ou seja, um valor discrepante positivo ou negativo).
E para ter certeza de que sua análise está correta, você precisa, de alguma forma, identificar esses outliers e decidir como tratá-los da melhor forma.
Agora vamos ver algumas maneiras de descobrir outliers no Excel.
Encontre outliers classificando os dados
Com pequenos conjuntos de dados, uma maneira rápida de identificar valores discrepantes é simplesmente classificar os dados e percorrer manualmente alguns dos valores no topo desses dados classificados.
E como pode haver valores discrepantes em ambas as direções, certifique-se de primeiro classificar os dados em ordem crescente e, em seguida, em ordem decrescente e, em seguida, passar pelos valores principais.
Deixe-me mostrar-lhe um exemplo.
Abaixo, tenho um conjunto de dados onde tenho durações de chamadas (em segundos) para 15 chamadas de atendimento ao cliente.
Abaixo estão as etapas para classificar esses dados para que possamos identificar os outliers no conjunto de dados:
- Selecione o cabeçalho da coluna que deseja classificar (célula B1 neste exemplo)
- Clique na guia Home
- No grupo Editing, clique no ícone Sort & Filter.
- Clique em Custom Sort
- Na caixa de diálogo Classificar, selecione "Duração" na lista suspensa Classificar por e "Do maior para o menor" na lista suspensa Pedido
- Clique OK
As etapas acima classificariam a coluna de duração da chamada com os valores mais altos no topo. Agora você pode verificar manualmente os dados e ver se há alguma discrepância.
Em nosso exemplo, posso ver que os dois primeiros valores são muito mais altos do que o resto dos valores (e os dois inferiores são muito mais baixos).
Nota: Este método funciona com pequenos conjuntos de dados onde você pode verificar manualmente os dados. Não é um método científico, mas funciona bem
Encontrando outliers usando as funções de quartil
Agora vamos falar sobre uma solução mais científica que pode ajudá-lo a identificar se existem discrepâncias ou não.
Em estatística, um quartil é um quarto do conjunto de dados. Por exemplo, se você tiver 12 pontos de dados, o primeiro quartil seriam os três pontos de dados inferiores, o segundo quartil seriam os próximos três pontos de dados e assim por diante.
Abaixo está o conjunto de dados onde desejo encontrar os outliers. Para fazer isso, terei que calcular o 1º e o 3º quartil e, em seguida, calcular o limite superior e o inferior.
Abaixo está a fórmula para calcular o primeiro quartil na célula E2:
= QUARTIL.INC ($ B $ 2: $ B $ 15,1)
e aqui está aquele para calcular o terceiro quartil na célula E3:
= QUARTIL.INC ($ B $ 2: $ B $ 15,3)
Agora, posso usar os dois cálculos acima para obter o intervalo interquartil (que é 50% dos nossos dados no primeiro e no terceiro quartil)
= F3-F2
Agora usaremos o intervalo interquartil para encontrar o limite inferior e superior que conteria a maioria de nossos dados.
Qualquer coisa que esteja fora desses limites inferior e superior seria considerada discrepante.
Abaixo está a fórmula para calcular o limite inferior:
= Quartil1 - 1,5 * (intervalo interquartil)
que em nosso exemplo se torna:
= F2-1,5 * F4
E a fórmula para calcular o limite superior é:
= Quartil 3 + 1,5 * (intervalo interquartil)
que em nosso exemplo se torna:
= F3 + 1,5 * F4
Agora que temos o limite superior e inferior em nosso conjunto de dados, podemos voltar aos dados originais e identificar rapidamente os valores que não estão neste intervalo.
Uma maneira rápida de fazer isso seria verificar cada valor e retornar VERDADEIRO ou FALSO em uma nova coluna.
Usei a fórmula OR abaixo para obter TRUE para os valores que são outliers.
= OU (B2 $ F $ 6)
Agora você pode filtrar a coluna Outlier e mostrar apenas os registros onde o valor é TRUE.
Como alternativa, você também pode usar a formatação condicional para destacar todas as células onde o valor é TRUE
Observação: Embora este seja um método mais aceito para descobrir outliers nas estatísticas. Acho esse método um pouco inutilizável em cenários da vida real. No exemplo acima, o limite inferior calculado pela fórmula é -103, enquanto o conjunto de dados que temos só pode ser positivo. Portanto, este método pode nos ajudar a encontrar outliers em uma direção (valores altos), é inútil identificar outliers na outra direção.Encontrar os outliers usando as funções LARGE / SMALL
Se você trabalha com muitos dados (valores em várias colunas), pode extrair o maior e o menor 5 ou 7 valores e ver se há valores discrepantes neles.
Se houver outliers, você poderá identificá-los sem ter que passar por todos os dados em ambas as direções.
Suponha que temos o conjunto de dados abaixo e queremos saber se há algum outliers.
Abaixo está a fórmula que fornecerá o maior valor no conjunto de dados:
= GRANDE ($ B $ 2: $ B $ 16,1)
Da mesma forma, o segundo maior valor será dado por
= GRANDE ($ B $ 2: $ B $ 16,1)
Se você não estiver usando o Microsoft 365, que tem matrizes dinâmicas, você pode usar a fórmula abaixo e ela fornecerá os cinco maiores valores do conjunto de dados com uma única fórmula:
= GRANDE ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
Da mesma forma, se você quiser os 5 valores menores, use a fórmula abaixo:
= PEQUENO ($ B $ 2: $ B $ 16, ROW ($ 1: 5))
ou o seguinte, caso você não tenha matrizes dinâmicas:
= PEQUENO ($ B $ 2: $ B $ 16,1)
Depois de ter esses valores, é realmente fácil descobrir quaisquer outliers no conjunto de dados.
Embora eu tenha optado por extrair os 5 valores maiores e menores, você pode escolher obter 7 ou 10 com base no tamanho do seu conjunto de dados.
Não tenho certeza se esse é um método aceitável para descobrir outliers no Excel ou não, mas é o método que usei quando tive que trabalhar com muitos dados financeiros em meu trabalho alguns anos atrás. Em comparação com todos os outros métodos abordados neste tutorial, descobri que este é o mais eficaz.
Como lidar com outliers da maneira certa
Até agora, vimos os métodos que nos ajudarão a encontrar os outliers em nosso conjunto de dados. Mas o que fazer quando você souber que existem valores discrepantes.
Aqui estão alguns métodos que você pode usar para lidar com outliers para que sua análise de dados esteja correta.
Exclua os outliers
A maneira mais fácil de remover outliers de seu conjunto de dados é simplesmente excluí-los. Dessa forma, não distorcerá sua análise.
É uma solução mais viável quando você tem grandes conjuntos de dados e a exclusão de alguns outliers não afetará a análise geral. E, claro, antes de excluir os dados, certifique-se de criar uma cópia e investigar o que está causando esses valores discrepantes.
Normalize os outliers (ajuste o valor)
Normalizar os valores discrepantes é o que eu costumava fazer quando estava em meu emprego de tempo integral. Para todos os valores atípicos, eu simplesmente os alteraria para um valor ligeiramente superior ao valor máximo no conjunto de dados.
Isso garantiu que eu não excluísse os dados, mas, ao mesmo tempo, não permitisse que isso distorcesse meus dados.
Para dar um exemplo da vida real, se você estiver analisando a margem de lucro líquido das empresas, onde a maioria das empresas está entre -10% a 30%, e há alguns valores que estão acima de 100%, eu simplesmente mudaria esses valores atípicos para 30% ou 35%.
Então, esses são alguns dos métodos que você pode usar em Excel para descobrir outliers.
Depois de identificar os outliers, você pode mergulhar nos dados e procurar o que os está causando, ao mesmo tempo, escolher uma das técnicas para lidar com esses outliers (que pode ser removê-los ou normalizá-los ajustando o valor)
Espero que você tenha achado este tutorial útil.
