MS EXCEL: Retorno de limites e valores quartil de uma matriz de números

Question

Eu estou tentando calcular os limites de uma matriz de valores, usei a função QUARTILE no Excel, mas não consigo entender o que está acontecendo - como eu faria para retornar os limites do primeiro quartil, segundo quartil, terceiro quartil e quarto quartil? A razão pela qual eu quero fazer isso é desenhar um gráfico de caixa sem mostrar os valores de outlier, pois eles estão tornando o gráfico não legível.

As funções que estou usando são:

=QUARTILE(C2:C6231,0)
=QUARTILE(C2:C6231,1)
=QUARTILE(C2:C6231,2)
=QUARTILE(C2:C6231,3)
=QUARTILE(C2:C6231,4)

• 0 mostra o valor mínimo (incluindo outliers)
• 1 mostra o limite inferior do 2º quartil
• 2 mostra a mediana
• 3 mostra o limite superior do terceiro quartil
• 4 mostra o valor máximo (incluindo outliers)

Há algo que eu esteja sentindo falta aqui?

    

Answer 1

Primeiro, você precisa determinar o intervalo em que os valores discrepantes caem. Usando uma distribuição normal, você pode escolher quantos desvios padrão estão fora da média que você quer. Um desvio padrão deve conter 68% da população, dois devem conter 95% e três devem conter 99%. Uma vez que você tem outliers, eu usaria 1.5 Desvios padrão, pois isso deveria abranger aproximadamente 86% da população total; 43% acima da média e 43% abaixo da média. No excel isso pode ser feito usando as fórmulas: =MEDIAN($A:$A)-1.5*STDEV.S($A:$A) e =MEDIAN($A:$A)+1.5*STDEV.S($A:$A)

Isso lhe dá um limite superior e inferior. O limite superior é o topo do quarto quartil e o limite inferior é o fundo do primeiro quartil. Sabendo disso, você pode usar a diferença entre eles para calcular os limites internos.

Upper Bound - Lower Bound = Range
Range / 4 = Interval
Lower Bound = Lower bound of 1st Quartile
Lower Bound + Interval = Upper bound of 1st quartile, Lower bound of 2nd quartile 
Lower Bound + 2*Interval = Median, Upper bound of 2nd Quartile, Lower bound of 3rd quartile
Upper Bound - 2*Interval = Median, Upper bound of 2nd Quartile, Lower bound of 3rd quartile
Upper Bound - Interval = Upper bound of 3rd quartile, Lower bound of 4th qartile
Upper Bound = Upper Bound of 4th qartile

O código vb para isso ficaria assim:

Public Sub Quartiles()
Dim Stdev, UpperBound, LowerBound, Interval As Double
Dim q1, q2, q3, q4 As String
Dim wf As WorksheetFunction
Stdev = WorksheetFunction.StDev_S(Selection)
UpperBound = WorksheetFunction.Median(Selection) + (1.5 * Stdev)
LowerBound = WorksheetFunction.Median(Selection) - (1.5 * Stdev)
Interval = (UpperBound - LowerBound) / 4
LowerBound = WorksheetFunction.Round(LowerBound, 3)
UpperBound = WorksheetFunction.Round(UpperBound, 3)
Interval = WorksheetFunction.Round(Interval, 3)
q1 = Str(LowerBound) + "," + Str(LowerBound + Interval)
q2 = Str(LowerBound + Interval) + "," + Str(LowerBound + 2 * Interval)
q3 = Str(UpperBound - 2 * Interval) + "," + Str(UpperBound - Interval)
q4 = Str(UpperBound - Interval) + "," + Str(UpperBound)
MsgBox ("Q1: " + q1 + vbNewLine + "Q2: " + q2 + vbNewLine + "Q3: " + q3 + 
vbNewLine + "Q4: " + q4)
End Sub