Remova Outliers no Excel: Guia Completo com QUARTIL e TRIMMEAN

Um valor atípico, ou outlier, representa um dado que se distancia consideravelmente da maioria dos valores em um conjunto de dados. No contexto do Excel, a presença de outliers pode influenciar negativamente a análise, distorcendo resultados, como a média. Felizmente, o Excel oferece recursos que auxiliam na gestão desses valores anômalos.

Analisando um Caso Prático

Em um conjunto de dados simples, outliers como o valor “2” associado a Eric e “173” a Ryan, são facilmente identificáveis. No entanto, em conjuntos maiores, a detecção manual torna-se impraticável. A capacidade de identificar e remover esses pontos discrepantes de cálculos estatísticos é fundamental. Este artigo explora como realizar essa tarefa.

Procedimentos para Identificar Outliers

Para identificar outliers, seguimos estas etapas:

  1. Calculamos o primeiro e o terceiro quartil (explicação abaixo).
  2. Avaliamos o intervalo interquartil (IQR), que será detalhado adiante.
  3. Definimos os limites superior e inferior do conjunto de dados.
  4. Usamos esses limites para detectar os pontos de dados remotos.

Os resultados desses cálculos serão armazenados em uma área específica ao lado do conjunto de dados original.

Iniciemos o processo.

Primeira Etapa: Cálculo dos Quartis

Os quartis dividem os dados em quatro partes iguais. O primeiro quartil representa os 25% inferiores dos dados, o segundo, os próximos 25%, e assim sucessivamente. Um outlier é geralmente definido como um ponto que está 1,5 vezes o IQR abaixo do primeiro quartil ou 1,5 vezes o IQR acima do terceiro quartil. Para identificar esses valores, precisamos calcular os quartis.

O Excel possui a função QUARTIL para o cálculo dos quartis, que requer o intervalo de dados e o número do quartil desejado.

Sintaxe: =QUARTIL(matriz, quart)

A “matriz” refere-se ao intervalo de dados, e “quart” ao quartil desejado (1 para o primeiro quartil, 2 para o segundo, etc.).

Nota: A partir do Excel 2010, as funções QUARTIL.INC e QUARTIL.EXC aprimoraram a funcionalidade QUARTIL. No entanto, a função QUARTIL oferece maior compatibilidade entre diferentes versões do Excel.

Vamos voltar ao nosso exemplo.

Para calcular o primeiro quartil, digitamos a seguinte fórmula na célula F2:

=QUARTIL(B2:B14,1)

Ao digitar a fórmula, o Excel exibe opções para o argumento “quart”.

Para calcular o terceiro quartil, podemos usar uma fórmula similar na célula F3, substituindo o “1” por “3”:

=QUARTIL(B2:B14,3)

Agora, os quartis estão exibidos nas células.

Segunda Etapa: Avaliação do Intervalo Interquartil (IQR)

O IQR representa a variação entre o primeiro e o terceiro quartil, ou seja, os 50% centrais dos dados. Calculamos o IQR subtraindo o primeiro quartil do terceiro quartil.

Aplicamos a seguinte fórmula na célula F4:

=F3-F2

O valor do IQR agora é mostrado.

Terceira Etapa: Definição dos Limites Inferior e Superior

Os limites inferior e superior definem a faixa de valores aceitáveis. Qualquer valor fora desses limites será considerado um outlier.

Para calcular o limite inferior (célula F5), multiplicamos o IQR por 1,5 e subtraímos o resultado do primeiro quartil:

=F2-(1.5*F4)

Observação: Os parênteses na fórmula não são obrigatórios devido à precedência de operadores, mas melhoram a legibilidade.

Para calcular o limite superior (célula F6), multiplicamos o IQR por 1,5 e somamos o resultado ao terceiro quartil:

=F3+(1.5*F4)

Quarta Etapa: Identificação dos Outliers

Com todos os dados calculados, podemos agora identificar os outliers: valores inferiores ao limite inferior ou superiores ao limite superior.

Usaremos a função OU para realizar o teste lógico, exibindo “VERDADEIRO” para outliers. Inserimos a seguinte fórmula na célula C2:

=OU(B2F$6)

Em seguida, copiamos essa fórmula para as células C3-C14. “VERDADEIRO” indica um outlier. No nosso exemplo, temos dois outliers.

Ignorando Outliers no Cálculo da Média

Além do método com a função QUARTIL, existe uma forma mais rápida para calcular a média, excluindo uma porcentagem de valores extremos. Esta função não identifica os outliers individualmente, mas permite flexibilidade na definição da porção de dados a ser excluída.

A função TRIMMEAN é usada para essa finalidade. Veja a sintaxe:

=TRIMMEAN(matriz, percent)

A “matriz” é o conjunto de dados, e “percent” a porcentagem de valores a serem excluídos das extremidades inferior e superior (inserida como porcentagem ou decimal).

Para excluir 20% dos outliers ao calcular a média, inserimos a seguinte fórmula na célula D3:

=TRIMMEAN(B2:B14, 20%)

Assim, apresentamos duas abordagens distintas para lidar com outliers. O Excel oferece as ferramentas necessárias tanto para identificar outliers para relatórios, quanto para excluí-los de cálculos estatísticos, como o cálculo de médias.