javascript - 如何在 VBA 中用 JavaScript 编写 "Application.Match"的等效代码？ - - 用于数值插值函数

Question

我想用 JavaScript 编写一个 INTERPOLATION 函数，以便能够将 Google 表格而不是 Excel 用于多种用途。在 Excel 中，我有这个用 VBA 编写的用户定义函数:

Function interpolate_1D(xreq As Single, x As Range, y As Range) As Single

' If we have variable y that is a function of x and have two ranges, x and y that give the values
'of y for particular values of x, we may need to find the value of y for a value of x not 'given in the table.
'For example, we may have power curve data for a wind turbine that gives the power output of a wind turbine
'for integer values of the wind speed. To find the output power at any other speed we         could 'use this function, using as arguments:
'xreq: wind speed for which we wish to know the output power
'x: range containing the known wind speeds in ascending order
'y: range containing the known wind turbine powers

Dim pointer As Integer
Dim x0 As Single, y0 As Single, x1 As Single, y1 As Single

pointer = Application.Match(xreq, x, 1)
x0 = x(pointer)
x1 = x(pointer + 1)
y0 = y(pointer)
y1 = y(pointer + 1)

interpolate_1D = y0 + (xreq - x0) * (y1 - y0) / (x1 - x0)

End Function

我可能从某处复制了此内容，例如 Billo 的关于 Excel for Scientists and Engineers 的书。它工作得很好，就像我写的它的 2D 版本一样。

我是 JavaScript 的新手，目前我看不出如何让它执行与 Application.Match (xreq,x,1) 行等效的操作，它会查看已知 x 的范围值并找到小于搜索值 xreq 的最大值的位置。一旦我有了那个职位，我就可以做其他任何事情。

有什么想法吗？

Answer 1

您可能不需要。您可以使用数组 .IndexOf 方法:

http://www.w3schools.com/jsref/jsref_indexof_array.asp

var fruits = ["Banana", "Orange", "Apple", "Mango"];
var a = fruits.indexOf("Apple");

a 的结果将是:2

是否可以用另一种语言重新创建优化 Match 工作表函数需要对底层代码有更详细的了解，我无法在这次。

否则，在看不到更多您正在做的事情的情况下，我会说尝试重构 Match 函数——虽然它会更慢——就像就像在数组上进行暴力迭代一样简单(如果您在数组而不是范围对象上进行测试，这会更快)。

我修改了Tim Williams' test functions to compare several methods.在这些测试中，Contains 函数在将范围 .Value 传输到数组后进行测试。我针对数组和范围对象测试了 Match 函数，还针对范围测试了 WorksheetFunction.Match。

Sub Tester()

    Application.ScreenUpdating = False
    Dim i As Long, B, T
    Dim Arr As Variant
    Dim rng As Range
    Set rng = Range("A1:A10000")

    rng.Formula = "=""value_""&" & "RandBetween(1,1000)"
    Range("A100000").Value = "Value_50"

    T = Timer
    Arr = Application.Transpose(rng.Value)
    For i = 1 To 10000
        B = Contains(Arr, "Value_50")
    Next i
    Debug.Print "Contains (array)" & vbTab & Timer - T

    T = Timer
    Arr = Application.Transpose(rng.Value)
    For i = 1 To 10000
        B = Application.Match("Value_50", Arr, False)
    Next i
    Debug.Print "Match (array)" & vbTab & Timer - T

    T = Timer
    For i = 1 To 10000
        B = Application.Match("Value_50", rng, False)
    Next i
    Debug.Print "Match (range)" & vbTab & Timer - T

    T = Timer
    On Error Resume Next
    For i = 1 To 10000
        B = Application.WorksheetFunction.Match("Value_50", rng, False)
    Next i
    On Error GoTo 0
    Debug.Print "WorksheetFunction.Match (range)" & vbTab & Timer - T


    Application.ScreenUpdating = True
End Sub

观察到当对内存中的数组执行时，Contains 自定义函数比 Match 更快，但是 Application.Match 和 Application.WorksheetFunction.Match 函数在工作表上的 Range 对象上执行时都比任何一个都快得多:

Contains (array)    18.90625
Match (array)   43.25
Match (range)   0.2304688
WorksheetFunction.Match (range) 0.1914063

感谢与@CharlesWilliams 的评论/讨论，感谢他协助进行此计时测试并澄清了一些相关内容。