Como destacar outliers estatísticos no Excel

Ao realizar a análise de dados, você geralmente assume que seus valores se agrupam em torno de algum ponto de dados central (uma mediana). Mas às vezes alguns dos valores ficam muito longe do ponto central. Esses valores são chamados de outliers (eles estão fora do intervalo esperado). Outliers podem distorcer suas análises estatísticas, levando você a conclusões falsas ou enganosas sobre seus dados.

Você pode usar algumas fórmulas simples e formatação condicional para destacar os valores discrepantes em seus dados.

A primeira etapa na identificação de outliers é localizar o centro estatístico do intervalo. Para fazer essa localização, você começa encontrando o primeiro e o terceiro quartis. Um quartil é uma divisão estatística de um conjunto de dados em quatro grupos iguais, com cada grupo representando 25 por cento dos dados. Os 25% superiores de uma coleção são considerados o 1º quartil, enquanto os 25% inferiores são considerados o 4º quartil.

No Excel, você pode obter facilmente os valores dos quartil usando a função QUARTILE. Esta função requer dois argumentos: um intervalo de dados e o número quartil desejado.

No exemplo mostrado, os valores nas células E3 e E4 são o primeiro e o terceiro quartis para os dados no intervalo B3: B20.

Tomando esses dois quartis, você pode calcular os 50 por cento estatísticos do conjunto de dados subtraindo o 3º quartil do 1º quartil. Esses 50 por cento estatísticos são chamados de intervalo interquartil (IQR). A Figura 9-18 exibe o IQR na célula E5.

Agora a questão é: quão longe dos 50 por cento intermediários um valor pode permanecer e ainda ser considerado um valor “razoável”? Os estatísticos geralmente concordam que IQR * 1.5 pode ser usado para estabelecer uma cerca superior e inferior razoável:

A cerca inferior é igual ao 1º quartil - IQR * 1.5.

A cerca superior é igual ao 3º quartil + IQR * 1,5.

Como você pode ver, as células E7 e E8 calculam as cercas superiores e inferiores finais. Qualquer valor maior que a cerca superior ou menor que a cerca inferior é considerado um valor atípico.

Neste ponto, a regra de formatação condicional é fácil de implementar.

Para construir essa regra de formatação básica, siga estas etapas:

Selecione as células de dados em seu intervalo de destino (células B3: B20 neste exemplo), clique na guia Página inicial da faixa do Excel e selecione Formatação condicional → Nova regra.

Isso abre a caixa de diálogo Nova regra de formatação.

Na caixa de listagem na parte superior da caixa de diálogo, clique na opção Usar uma fórmula para determinar quais células formatar.

Esta seleção avalia os valores com base em uma fórmula que você especifica. Se um determinado valor for avaliado como TRUE, a formatação condicional será aplicada a essa célula.

Na caixa de entrada da fórmula, insira a fórmula mostrada aqui.

Observe que você usa a função OR para comparar o valor em sua célula-alvo (B3) com as cercas superior e inferior encontradas nas células $ E $ 7 e $ E $ 8, respectivamente. Se a célula de destino for maior que a cerca superior ou menor que a cerca inferior, ela é considerada um outlier e, portanto, será avaliada como TRUE, acionando a formatação condicional.

= OU (B3 <$ e $ 8, b3> $ E $ 7)

Observe que, na fórmula, você exclui os símbolos de dólar de referência absoluta ($) para a célula de destino (B3). Se você clicar na célula B3 em vez de digitar a referência de célula, o Excel automaticamente tornará sua referência de célula absoluta. É importante que você não inclua os símbolos de cifrão de referência absoluta em sua célula-alvo porque você precisa do Excel para aplicar essa regra de formatação com base no próprio valor de cada célula.

Clique no botão Formatar.

Isso abre a caixa de diálogo Formatar células, onde você tem um conjunto completo de opções para formatar a fonte, borda e preenchimento da célula de destino. Depois de escolher as opções de formatação, clique no botão OK para confirmar as alterações e retornar à caixa de diálogo Nova regra de formatação.

De volta à caixa de diálogo Nova regra de formatação, clique no botão OK para confirmar sua regra de formatação.

Se você precisar editar sua regra de formatação condicional, simplesmente coloque o cursor em qualquer uma das células de dados dentro do seu intervalo formatado e vá para a aba Página Inicial e selecione Formatação Condicional → Gerenciar Regras. Isso abre a caixa de diálogo Gerenciador de Regras de Formatação Condicional. Clique na regra que deseja editar e, em seguida, clique no botão Editar Regra.