☰

MS EXCEL：从数字数组中返回四分位数界限和值

tag-icon • tag-icon microsoft-excel worksheet-function
MS EXCEL：从数字数组中返回四分位数界限和值

我正在尝试计算一个值数组的边界，我使用了 Excel 中的 QUARTILE 函数，但我似乎无法理解它 - 我该如何返回第一四分位数、第二四分位数、第三四分位数和第四四分位数的边界？我想要这样做的原因是绘制一个箱线图而不显示异常值，因为它们会使图表难以阅读。

我正在使用的功能是：

=QUARTILE(C2:C6231,0)
=QUARTILE(C2:C6231,1)
=QUARTILE(C2:C6231,2)
=QUARTILE(C2:C6231,3)
=QUARTILE(C2:C6231,4)
  • 0 表示最小值（包括异常值）
  • 1 显示第二四分位数的下限
  • 2 显示中位数
  • 3 显示第三四分位数的上限
  • 4 显示最大值（包括异常值）

这里我遗漏了什么吗？

答案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)

这样就得到了一个上限和下限。上限是第四四分位数的顶部，下限是第一四分位数的底部。知道了这一点，你就可以利用它们之间的差值来计算内界。

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

相关内容