performance - 构建用于多键查找的快速Excel VBA UDF

我在一个工作簿中有几个非常大的数据表，需要创建一个Excel用户定义函数来在那些表中查找数据。我需要能够提供可变数量的键列和键值进行搜索，并且该功能需要找到第一个匹配行，然后使用此偏移量在相应的数据列中查找值。

有点像VLOOKUP的多键版本。我知道您可以创建一个单独的键，该键由第1列中附加的单个键组成，但是我想避免这样做。

我已经创建了此功能的多个版本，到目前为止，最好的一个版本运行良好，但速度较慢！我的一张桌子有将近9,000行乘11列，而我使用的是6字段键。我大约有18,000次出现此公式，并且重新计算工作表大约需要3分钟（我计算出涉及9.7亿次计算，因此当然会很慢）。

它使用Evaluate()就像我看到的许多解决方案一样。这是代码：

Function KeyLookup(datatable As Variant, datacol As String, _
                   key1table As Variant, key1 As String, _
                   Optional key2table As Variant, Optional key2 As String, _
                   Optional key3table As Variant, Optional key3 As String, _
                   Optional key4table As Variant, Optional key4 As String, _
                   Optional key5table As Variant, Optional key5 As String, _
                   Optional key6table As Variant, Optional key6 As String) As Variant
    Dim cmd As String

    cmd = "INDEX(" & datatable.Address & ",MATCH(1,("
    cmd = cmd & key1table.Address & "=""" & key1 & """)"
    If Not IsMissing(key2table) Then cmd = cmd & "*(" & key2table.Address & "=""" & key2 & """)"
    If Not IsMissing(key3table) Then cmd = cmd & "*(" & key3table.Address & "=""" & key3 & """)"
    If Not IsMissing(key4table) Then cmd = cmd & "*(" & key4table.Address & "=""" & key4 & """)"
    If Not IsMissing(key5table) Then cmd = cmd & "*(" & key5table.Address & "=""" & key5 & """)"
    If Not IsMissing(key6table) Then cmd = cmd & "*(" & key6table.Address & "=""" & key6 & """)"
    cmd = cmd & ",0)," & datacol & ")"

    KeyLookup = Evaluate(cmd)
End Function

这将生成如下所示的cmd值：

INDEX($K$3:$L$8993,MATCH(1,($B$3:$B$8993="a1-5")*($C$3:$C$8993="Tarp")*($E$3:$E$8993="Sydney")*($F$3:$F$8993="Highest Reach")*($G$3:$G$8993="1+")*($J$3:$J$8993="T0"),1),1)

我需要一些帮助以使其尽快完成。 3分钟太慢了。

如前所述，我不想使用基于VLOOKUP()的解决方案，因为我不需要预先计算组合键。

我还想避免SUMPRODUCT解决方案，该解决方案仅适用于数字，并且不返回第一个值，但是如果找到多个匹配项，则将所有值相加。

我也不能依赖第三方加载项，即使我知道存在一些好的加载项。

因此，我目前的想法是本机使用WorksheetFunction.Index() / Match()，因此删除Evaluate()，因为我知道这会增加大量开销。

但是，我尝试删除Evaluate()失败。有人可以在这里帮助我吗？

似乎VBA中的WorksheetFunction.Index() / Match()仅支持单个范围和单个键，除非有人可以解释如何实现可爱的(range1=key1)*(range2=key2)...表示法，即工作表中的MATCH函数受其祝福，但WorksheetFunction.Match()却不是这样没错

最佳答案

很好的帖子，虽然没有真正的问题。 ;）在编写代码时，像IF这样的条件检查会严重影响执行时间，但这通常是最可靠的检查方法。在上面的代码中，每次使用IF Not IsMissing条件都会检查5次。这会使每次检查的负载成指数增加（尽管我无法真正告诉您多少）。

无需过多编辑代码，可以采用一种逻辑来一次跳过5个检查。而不是检查是否存在，而是检查不存在。基本上，您的公式具有可选的key2。如果key2不存在，则key3... key6也不会。按照此模式，如果key3不存在，则key4... key6也不会。

这给我们带来了直接的优势。当然，当您没有其他键时，使用变量检查而不是五个检查是一个很大的飞跃。但是，如果您一次将其用于6个完整键，我将研究完全不同的代码。 Evaluate是一个巨大的杀手，如果您是每次都要重新计算UDF的类型（即Application.Volatile），那么计算时间将受到更大的打击。

为了显示很小的更改，以下是我对您的代码的看法（未测试）：

Function KeyLookup(datatable As Variant, datacol As String, _
                   key1table As Variant, key1 As String, _
                   Optional key2table As Variant, Optional key2 As String, _
                   Optional key3table As Variant, Optional key3 As String, _
                   Optional key4table As Variant, Optional key4 As String, _
                   Optional key5table As Variant, Optional key5 As String, _
                   Optional key6table As Variant, Optional key6 As String) As Variant
    Dim cmd As String

    cmd = "INDEX(" & datatable.Address & ",MATCH(1,(" & key1table.Address & "=""" & key1 & """)"
    cmd2 = ",0)," & datacol & ")"

    If IsMissing(key2table) Then GoTo SkipOthers
    ElseIf IsMissing(key3table) Then
        cmd = cmd & "*(" & key2table.Address & "=""" & key2 & """)"
        GoTo SkipOthers
    ElseIf IsMissing(key4table) Then
        cmd = cmd & "*(" & key3table.Address & "=""" & key3 & """)"
        GoTo SkipOthers
    ElseIf IsMissing(key5table) Then
        cmd = cmd & "*(" & key4table.Address & "=""" & key4 & """)"
        GoTo SkipOthers
    ElseIf IsMissing(key6table) Then
        cmd = cmd & "*(" & key5table.Address & "=""" & key5 & """)"
        GoTo SkipOthers
    Else
        cmd = cmd & "*(" & key6table.Address & "=""" & key6 & """)"
    End If

SkipOthers:
    KeyLookup = Evaluate(cmd & cmd2)

End Function

如果您想继续走这条路，那就祝您好运。 :)

关于performance - 构建用于多键查找的快速Excel VBA UDF，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/19943027/