VBA中的PercentRank算法 [英] PercentRank algorithm in VBA

问题描述

我在 Php 和(看起来像)C++ 中找到了问题当存在关系时如何为数组分配秩数"的答案.我还用我不懂的语言找到了几个关于 Excel 的 PercentRank 的答案.

I have found an answer to the question "How to assign a rank number to an array when ties exist" in Php and (looked lilke) C++. I also found a couple of answers about Excel's PercentRank in language I do not know.

有人可以帮我在 VBA 中做到这一点吗?我需要从 Access for a report 中的 12 个值计算 PercentRank，我不能使用 Excel.这是我所追求的示例:

Can someone help me to do this in VBA? I need to calculate the PercentRank from 12 values in Access fopr a report and I cannot use Excel. Here's an example of what I am after:

   Per  Val %Rank
    01  80  0.82
    02  74  0.45
    03  88  1.00
    04  60  0.00
    05  86  0.91
    06  68  0.18
    07  64  0.09
    08  78  0.64
    09  76  0.55
    10  72  0.27
    11  78  0.64
    12  72  0.27 

请注意，08 和 11 期间的值是相同的.也适用于第 10 和 12 期.我在某处读到，当有平局时，函数必须计算某种平均值.

Note that for the period 08 and 11 the value is the same. Also for the period 10 and 12. I read somewhere that when there are ties the function must calculate an average of somesort.

有人可以帮忙用 VBA 编写函数吗?

Would anyone please help with function written in VBA?

非常感谢.

D.拉马什

推荐答案

Ties 应该产生相同的百分比排名，就像您的示例所示.如果您的 x 数不在数组中，则您必须进行推断.如果你确定 x 在数组中，你可以简化为这个

Ties should produce the same percentage rank, just like your example shows. If your x number is not in the array, then you have to extrapolate. If you're assured that x is in array, you can simplify to this

Public Function PRank(vaArray As Variant, x As Variant) As Double

    Dim lLower As Long
    Dim lHigher As Long
    Dim i As Long

    For i = LBound(vaArray, 1) To UBound(vaArray, 1)
        If vaArray(i, 1) < x Then
            lLower = lLower + 1
        ElseIf vaArray(i, 1) > x Then
            lHigher = lHigher + 1
        End If
    Next i

    PRank = lLower / (lLower + lHigher)

End Function

如果你传递一个不在 vaArray 中的 x 值，这将产生错误的结果.此外，这假设您要传递一个二维数组(就像 Excel 列一样)，因此您可能需要对此进行调整.

If you pass an x values that is not in vaArray, this will produce the wrong result. Also, this assumes you're passing a two dimensional array (like an Excel column would be), so you may need to adjust for that.

这篇关于VBA中的PercentRank算法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！