Excel VBA-如何添加动态阵列公式

Question

我正在通过VBA将公式添加到工作表中，该公式应该是：

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

它利用Excel中的新溢出功能为我提供了B列值列表，其中a列中的相关值a列在单元格中。我也应用了。删除任何多个空白（“”）结果的唯一功能。

如果我将公式手动键入Excel，则可以很好地工作，但是在使用VBA添加公式时，Excel在公式中添加 @符号，并使其显示#value！。

用于添加公式的VBA线是：

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

Excel中的结果输出是：

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

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

提前致谢！

Answer 1

好问题，我查一下...

---

简而

_{代码>而不是=单元格（x，y）.formula}

---

_解释：

@称为隐式相交操作员。来自MS文档：

隐式相交逻辑将许多值降低到单个值。 excel这样做是为了迫使公式返回一个值，因为
单元只能包含一个值。如果您的公式返回
一个值，然后隐式交叉没有任何作用（即使它
从技术上讲是在后台完成的）。

，为什么它会出现在您的较新的excel O365中？好吧，range.formula使用IIE（隐式交叉点），从而将@添加到基本上撤消您的动态数组功能。 unique 是一个新的动态数组函数。因此，要在代码中写下此内容，您应该使用 range.formula2 属性（或 range.formula2r1c1 如果使用r1c1 note法）。这些属性使用AE（数组评估），现在是默认值。

• 这里是来自MS的一位信息文档，该主题解释了公式和公式2之间的区别。

• 如果您想进一步了解隐式交集操作员，请查看 this

• 我在早些时候与隐式相交的另一个问题回答了另一个问题示例实际工作在这里如果发现它很有趣。