实际上这很容易。

假设计数的总和是N，并且您想要 0.3 (30%) 的底部百分位数。这意味着阈值将在0.3*N计数后出现。

现在你看一下累积分布，当它达到 时0.3*N，你就有了价值。这很容易实现。

例如，你有这个：

Value    No. of occurrences
  1              2
  2              4
  3              2
  4              3

所以你把它转换成累积的：

Value    No. of occurrences
 <=1              2
 <=2              6
 <=3              8
 <=4              11

这里N=11如此0.3*11=3.3。这发生在第二个 bin 中，因此 0.3 个百分位数为 2。

PercentileBinnedData是我开发的上述算法的实现，QuickSort将确保您的分箱数据按升序排序

Function PercentileBinnedData(rng As Range, percentile As Double) As Double
    Dim v As Variant
    v = rng.Value
    QuickSortArray v, , , 1 ' sort 2D array
    Dim i As Long
    Dim totalOccurences As Long

    'Convert to cumalative distribution
    For i = LBound(v, 1) To UBound(v, 1)
        If i < UBound(v, 1) Then
            v(i + 1, 2) = v(i + 1, 2) + v(i, 2)
        End If
    Next i
    totalOccurences = v(UBound(v, 1), 2) ' number of occurences is equal to last number of occurences
    Dim rank As Double: rank = percentile * totalOccurences
    For i = LBound(v, 1) To UBound(v, 1)
        If i = LBound(v, 1) And rank <= v(i, 2) Then
            PercentileBinnedData = v(i, 1)
        End If
        If i > LBound(v, 1) Then
            If rank > v(i - 1, 2) And rank <= v(i, 2) Then
                PercentileBinnedData = v(i, 1)
            End If
        End If
    Next i
End Function

Public Sub QuickSortArray(ByRef SortArray As Variant, Optional lngMin As Long = -1, Optional lngMax As Long = -1, Optional lngColumn As Long = 0)
    On Error Resume Next

    'Sort a 2-Dimensional array

    ' SampleUsage: sort arrData by the contents of column 3
    '
    '   QuickSortArray arrData, , , 3

    '
    'Posted by Jim Rech 10/20/98 Excel.Programming

    'Modifications, Nigel Heffernan:

    '       ' Escape failed comparison with empty variant
    '       ' Defensive coding: check inputs

    Dim i As Long
    Dim j As Long
    Dim varMid As Variant
    Dim arrRowTemp As Variant
    Dim lngColTemp As Long

    If IsEmpty(SortArray) Then
        Exit Sub
    End If
    If InStr(TypeName(SortArray), "()") < 1 Then  'IsArray() is somewhat broken: Look for brackets in the type name
        Exit Sub
    End If
    If lngMin = -1 Then
        lngMin = LBound(SortArray, 1)
    End If
    If lngMax = -1 Then
        lngMax = UBound(SortArray, 1)
    End If
    If lngMin >= lngMax Then    ' no sorting required
        Exit Sub
    End If

    i = lngMin
    j = lngMax

    varMid = Empty
    varMid = SortArray((lngMin + lngMax) \ 2, lngColumn)

    ' We  send 'Empty' and invalid data items to the end of the list:
    If IsObject(varMid) Then  ' note that we don't check isObject(SortArray(n)) - varMid *might* pick up a valid default member or property
        i = lngMax
        j = lngMin
    ElseIf IsEmpty(varMid) Then
        i = lngMax
        j = lngMin
    ElseIf IsNull(varMid) Then
        i = lngMax
        j = lngMin
    ElseIf varMid = "" Then
        i = lngMax
        j = lngMin
    ElseIf VarType(varMid) = vbError Then
        i = lngMax
        j = lngMin
    ElseIf VarType(varMid) > 17 Then
        i = lngMax
        j = lngMin
    End If

    While i <= j
        While SortArray(i, lngColumn) < varMid And i < lngMax
            i = i + 1
        Wend
        While varMid < SortArray(j, lngColumn) And j > lngMin
            j = j - 1
        Wend

        If i <= j Then
            ' Swap the rows
            ReDim arrRowTemp(LBound(SortArray, 2) To UBound(SortArray, 2))
            For lngColTemp = LBound(SortArray, 2) To UBound(SortArray, 2)
                arrRowTemp(lngColTemp) = SortArray(i, lngColTemp)
                SortArray(i, lngColTemp) = SortArray(j, lngColTemp)
                SortArray(j, lngColTemp) = arrRowTemp(lngColTemp)
            Next lngColTemp
            Erase arrRowTemp

            i = i + 1
            j = j - 1
        End If
    Wend

    If (lngMin < j) Then Call QuickSortArray(SortArray, lngMin, j, lngColumn)
    If (i < lngMax) Then Call QuickSortArray(SortArray, i, lngMax, lngColumn)

End Sub