在切片的2D数组中查找值-Excel VBA [英] Looking up values in a sliced 2D array - Excel VBA

问题描述

我在Excel中有一个看起来像这样的表：

I have a table in Excel which looks like this:

        A         C         D
0       0.00%     0.00%     0.00%
473     0.00%     0.00%     3.40%
641     13.80%    13.80%    10.40%
646     13.80%    13.80%    10.40%
3337    13.80%    13.80%    13.80%
3454    13.80%    13.80%    13.80%

函数的形式为myFunction（x，y，thresholds_table）其中x可以是A，C或D，thresholds_table是整个表。该函数涉及根据x的值从三列之一中查找值。如果我手动指定一个列（例如，设置r = 2），该函数可以正常工作，但是我无法确定如何选择该列。

The function is of the form myFunction(x,y,thresholds_table) where x can be A, C or D and thresholds_table is the whole table. The function involves looking up values from one of the three columns depending on the value of x. If I manually specify a column (e.g. set r=2), the function works fine, but I can't work out how to choose the column.

我这是VBA的新功能，用于编程语言，对数组切片有更好的支持，所以也许我会采用错误的方式。我要使用的代码是：

I'm new to VBA and used to programming languages with much better support for array slicing, so perhaps I'm going about this the wrong way. The code I'm trying to use is:

Dim rates As Range

rates = Application.WorksheetFunction.Index(thresholds_table, 1, 0)
r = Application.WorksheetFunction.Match(x, rates)

使用INDEX函数选择第一行，然后使用Match查找列索引。但是，它总是返回VALUE错误。

using the INDEX function to select the top row, then Match to find the column index. However, it always returns a VALUE error.

这似乎很简单，但是我做了很多谷歌搜索，却找不到答案！

This seems like it must be simple, but I've done a lot of googling and not been able to find an answer!

编辑：

对不起，我并不完全清楚：未使用 y参数在这部分功能中。基本上我只需要一个函数，如果x = A，则返回2，如果x = C，则返回3，如果x = D，则返回4。我不想这样直接输入，尽管由于将来A / C / D可能会发生变化，所以它需要使用任何列标题。

Sorry, I wasn't entirely clear: the "y" argument isn't used in this part of the function. Basically I just need a function which returns 2 if x=A, 3 if x=C or 4 if x=D. I don't want to put it in directly like that though since the A/C/D may change in the future, so it needs to use whatever the column headings are.

推荐答案

也许有点long，但这是否可以更好地解释事情？

Perhaps a little long winded, but does this explain things a little better?

Public Function myFunction(x, y, thresholds_table As Range) As Variant

    Dim rates As Variant

    With Application
        rates = .Index(thresholds_table, 1, 0)
        myFunction = .Match(x, rates)
    End With

End Function

该函数返回一个数组，而不是范围对象。

The functions return an array, rather than a range object.

这可以缩短，但我想您会做得更好仅在工作表中使用公式即可获得更好的性能

This could be shortened, but I think you'd get far better performance from simply using a formula in the sheet

您的费率声明不正确-该函数返回二维数组

Your declaration of rates is incorrect - the function returns a 2 dimensional array

这篇关于在切片的2D数组中查找值-Excel VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！