MS Access VBA：计算中位数，而无需遍历记录

Access中没有中值功能。Excel有一个，但我相信它限制为30个数字，因此，即使您想尝试使用自动化功能，我也不认为它适用于您的情况。

我认为通过微调功能并让Microsoft的Jet Engine预编译查询，您可能会看到明显的速度提高。

• 确保在表中索引了基本字段和代码字段
• 使用具有标准参数[What Code]的代码创建参数查询
• 使用Recordset WITH构造，声明的变量和匹配的字段类型优化您的功能（代码= Long Integer ???）

在所有这些更改之前和之后进行计时，看看是否有明显的区别

我更正了一些错别字，这些错别字可能不是错别字-我假设CODE是一个长整数-这又一次可能是我错了。同样，我的更改以“ ***************”开头

创建预编译参数查询

创建名为“ qdfPrepMedian ”的新查询

复制/粘贴SQL >> PARAMETERS [What Code] Long; SELECT Co, Base FROM <YourTableName> WHERE Code = [What Code] ORDER BY Base ASC;

保存查询

调整功能

Option Explicit

'***********************
' changed posCode to Long
'***********************
Function FIncPercentile(ByVal posCode As Long, ByVal k As Single, ByVal tbl As String) As Variant

    '***********************
    ' CREATE/USE Precompiled Parameter Query
    ' Create New Query called "qdfPrepMedian"
    ' Copy/Paste SQL >> PARAMETERS [What Code] Long; SELECT Co, Base FROM <YourTableName> WHERE Code = [What Code] ORDER BY Base ASC;

    Const QRY_BY_CODES  As String = "qdfPrepMedian"

    Dim qdf     As QueryDef
    '
    '***********************

    Dim rstRec  As Recordset
    Dim db      As Database
    Dim n       As Integer
    Dim i       As Single

    ' Declare all Currency variables on separate lines
    ' Otherwise they will be variants
    Dim res     As Currency
    Dim d1      As Currency
    Dim d2      As Currency

    Set db = CurrentDb

    '***********************
    ' Create readonly recordset from querydef
    Set qdf = db.QueryDefs(QRY_BY_CODES)
    qdf.Parameters("What Code") = posCode ' matches LONG variable passed to function
    Set rstRec = qdf.OpenRecordset(dbOpenSnapshot, dbReadOnly) ' Readonly is sometimes faster
    '***********************

    ' Use WITH rstRec
    With rstRec

        ' Skip if there are no matches
        If IsNull(!base) Or .RecordCount = 0 Then
        '*** Is this a type ***
        '    FBasePercentile = Null
        ' Should it BE
            FIncPercentile = Null
            Exit Function
        End If

        ' Count records
        .MoveLast
        n = .RecordCount
        .MoveFirst

        ' Calculate the index where k is the percentile
        i = n * k

        ' Test the decimal and find value accordingly
        If i = Int(i) Then
            .Move i - 1
            d1 = !base
            .MoveNext
            d2 = !base
            FIncPercentile = (d1 + d2) / 2
        Else
            i = Round(i + 0.5, 0)
            .Move i - 1
            FIncPercentile = !base
        End If

    End With

End Function