Excel VBA - 如何添加动态数组公式

Question

我正在通过VBA向工作表添加一个公式，该公式应为：

=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""))

利用Excel中的新SPILL功能，给我一个列B值的列表，其中列A中的相关值与单元格A中的值匹配。我还应用了UNIQUE函数来删除任何多个空白（“”）结果。

如果我手动在Excel中键入公式，它可以完美运行，但是当我使用VBA添加公式时，Excel会在公式中添加@符号，从而导致显示#VALUE!。

用于添加公式的VBA代码行为：

=Cells(x,y).Formula = "=UNIQUE(IF(TableA[ColumnA]=A1,TableA[ColumnB],""""))"

在 Excel 中生成的输出结果为：

=@UNIQUE(IF(TableA[@[ColumnA]]=A1,TableA[ColumnB],""))

发生了什么事，我错过了什么？

先行致谢！

- Ally Mitchell

Answer 1

好问题，我查了一下...

---

_{简而言之:}

使用=Cells(x,y).Formula2代替=Cells(x,y).Formula

---

_说明：

显示的@被称为隐式交集运算符。根据MS文档:

隐式交集逻辑将许多值简化为一个值。Excel这样做是为了强制公式返回单个值，因为单元格只能包含单个值。如果您的公式返回单个值，则隐式交集不起作用（即使在后台实际上正在执行）。

但是为什么它会出现在您的新版Excel O365中？好吧，Range.Formula使用IIE（隐式交集），从而添加@以基本上撤消您的动态数组功能。UNIQUE是一种新的动态数组函数。因此，要在代码中编写此内容，您应该使用Range.Formula2属性（或Range.Formula2R1C1如果您使用R1C1表示法）。这些属性使用AE（数组评估）并且现在是默认值。

• 这里有一个来自MS的信息文档，介绍了更详细的有关Formula和Formula2之间差异的内容。

• 如果您想了解有关隐式交集运算符的更多信息，请查看这个。

• 我之前回答了另一个问题，其中涉及到隐式交集，提供了一个示例，展示了它如何实际工作这里，如果有人觉得有趣。

- JvdV

---

有益的，对于基本理解是一个很好的介绍：+) - T.M.

好的答案。有人需要把“如何避免使用SELECT”的链接发送给微软。 - SJR

谢谢 - 这正是它的原因。我想我现在回想起来在某个地方看到了关于新的Formula2/Formula2R1C1属性的内容，但完全忘记了它们。现在肯定不会再忘记它们了！ - Ally Mitchell

这是一个非常清晰和富有指导性的答案。上帝保佑你，JvdV。 - PatrickBeuseize

先生，这真的很有帮助，优秀和 奇妙。我们从您那里获得了很多知识和学习，非常感谢您投入的努力和时间，提供如此精湛的excel工艺。上帝永远保佑您！ - Mayukh Bhattacharya