将VBA数组传递给公式

Question

我有两行数据，看起来像这样：

我想返回每个ID的所有位置编号，并将它们存储在一个数组中。我尝试过

={IF(A2:A562=E2,B2:B562)}

但是，每当我搜索的ID不是A列中的第一个时，它就会失败。（我尝试过按A列排序，但没有成功。）

因此，我想出了一个解决方法：我将使用以下公式。

={INDEX(B2:B562,positions(E2))}

positions是一个VBA函数，它返回一个与指定ID匹配的行数组。该函数被编码为返回一个Variant类型。但似乎VBA数组未能传递到Excel公式中。当我评估该公式时，positions(E2)等于0。（我已经在VBA中检查过，我的数组已正确填充。）

那么如何使我的公式正确解释VBA数组呢？

更新：这是我的代码：

Function positions(idrange As Range) As Variant
Dim V As Variant
Dim l, nb As Integer
Dim id As Double

nb = 4
ReDim V(nb) As Variant

id = idrange.Value
Set cible = Sheet2.Range("B1")
For l = 1 To nb
    Set cible = Sheet2.Columns(2).Find(What:=id, After:=cible, _
                LookIn:=xlValues)

    V(l) = cible.Row - 1   
Next l
positions = Application.Transpose(V)

End Function

更新2：这是期望的输出

- jeake

---

发布你的VBA代码。 - Gary's Student

那么期望的输出是什么？ - Scott Craner

@ScottCraner，假设我正在查看第一个ID。我希望它返回{3,5,7}。 - jeake

好的，下一个问题：你使用的是哪个版本的Excel？ - Scott Craner

请查看我在这里的答案：http://stackoverflow.com/questions/37923950/look-values-in-column-1-and-bring-column-2-values - Scott Craner

Answer 1

将这个数组公式放在F2中：

=IFERROR(INDEX($B$2:$B$562,MATCH(1,($A$2:$A$562=$E2)*(COUNTIF($E$2:E2,$B$2:$B$562)=0),0)),"")

使用Ctrl-Shift-Enter键来确认，而不是单纯的Enter键。如果正确操作，Excel将在公式周围加上{}。

然后复制足够的数据并向下填充。

---

编辑 #1

如果您能对数据进行排序，则可以避免使用数组公式并使用此常规公式：

=IF(COLUMN(A:A) <= COUNTIF($A:$A,$E2),INDEX($B:$B,MATCH($E2,$A:$A,0)+COLUMN(A:A)-1),"")

- Scott Craner

---

还有一个问题：如果两个ID能够占据相同的位置怎么办？ - jeake

我不确定我理解了。你是说这个单元格有两个ID吗？ - Scott Craner

假设A列的值（在B列中）为1、1、7、10。 - jeake

你现在已经进入了VBA领域。我建议您提出一个新问题，附上所需的数据和预期结果。我怀疑公式能否实现您想要的功能。 - Scott Craner

好的，我会开始一个新的问题。 - jeake

Answer 2

Drawing on 这篇文章，你可以使用数组和VBA来实现以下操作：

1. 这行代码 Filter(Application.Transpose(Application.Evaluate("=IF(A2:A100=OFFSET(E2," & lngCnt - 1 & ",0), (B2:B100),""x"")")), "x", False) 返回与 B 值匹配的字符串

例如：对于 A2，返回 "1","4","7","10"

2. 这行代码 [e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|") 将字符串赋值给每个数组

代码示例

Sub GetEm()

Dim lngCnt As Long
'range of your codes from E2 down
y = [E2:E4]

For lngCnt = 1 To UBound(y)
     x = Filter(Application.Transpose(Application.Evaluate("=IF(A2:A100=OFFSET(E2," & lngCnt - 1 & ",0), (B2:B100),""x"")")), "x", False)
    [e2].Offset(lngCnt - 1, 1).Resize(1, UBound(x) + 1) = Split(Join(x, "|"), "|")
Next
End Sub

- brettdj