MS EXCEL: возвращение квартильных границ и значений из массива чисел

Question

Я пытаюсь вычислить границы массива значений, я использовал функцию QUARTILE в Excel, но, похоже, я не могу понять, как это сделать - как бы я мог вернуть границы первого квартиля, второго квартиль, третий квартиль и четвертый квартиль? Причина, по которой я хочу сделать это, состоит в том, чтобы составить коробчатый график, не показывая значения выбросов, так как они делают график нечитаемым.

Функции, которые я использую:

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

• 0 показывает минимальное значение (включая выбросы)
• 1 показывает нижнюю границу 2-го квартиля
• 2 показывает медиану
• 3 показывает верхнюю границу 3-го квартиля
• 4 показывает максимальное значение (включая выбросы)

Есть что-то, чего я здесь не хватает?

Answer 1

Сначала вам нужно определить диапазон, в который попадают выбросы. Используя нормальное распределение, вы можете выбрать, сколько стандартных отклонений от желаемого среднего. Одно стандартное отклонение должно содержать 68% населения, два - 95%, а три - 99%. Поскольку у вас есть выбросы, я бы использовал 1,5 стандартных отклонения, так как они должны охватывать примерно 86% от общей численности населения; 43% выше среднего и 43% ниже среднего. В Excel это можно сделать с помощью формул:=MEDIAN($A:$A)-1.5*STDEV.S($A:$A) и =MEDIAN($A:$A)+1.5*STDEV.S($A:$A)

Это дает вам верхнюю и нижнюю границу. Верхняя граница - вершина 4-го квартиля, а нижняя граница - дно 1-го квартиля. Зная это, вы можете использовать разницу между ними для вычисления внутренних границ.

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

Код VB для этого будет выглядеть так:

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