arrays - 将 VBA 数组传递给公式

Question

我有两行数据，如下所示:

我试图返回数组中每个 ID 的所有位置号。我已经尝试过

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

但是只要我搜索的 ID 不是 A 列中的第一个，它就会失败。(我确实尝试对 A 列进行排序，但没有成功)。

所以我想出了这个解决方法:相反，我会使用这个公式

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

其中 positions 是一个 VBA 函数，它返回与指定 ID 匹配的行数组。函数positions经过编码，使其返回一个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:这是所需的输出

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),"")