如何在 VBA 中为 SUMIF() 公式提供文件路径

Question

=SUMIF('[filename1.xlsx]Sheet1'!$A:$A,C12,'[filename1.xlsx]Sheet1'!$Y:$Y)+SUMIF('[filename2.xlsm]Data from ABC'!$A:$A,C12,'[filename2.xlsm]Data from ABC'!$Y:$Y)

上面的公式正在filename3中使用，但是，如果关闭filename1和filename2，它会给出错误，我必须再次设置整个公式。为了避免这种情况，我们如何将文件名作为 sumif 公式的单元格引用

我使用以下公式，其中我在单元格 A10、A11、B10、B11 中给出了整个文件名，但它不起作用。

=SUMIF(A10,C12,A11+SUMIF(B10,C12,B11)

你能建议一个破解吗？

Answer 1

函数 SUMPRODUCT 可以处理关闭的工作簿。但是此函数中的参数位于矩阵(数组(上下文中，这对于整个列来说非常不有效。因此，仅当您可以将范围从整列缩小到部分列时，以下内容才有效。

打开这三个文件。在文件名 3 中创建公式：

=SUMPRODUCT(('[filename1.xlsx]Sheet1'!$A$1:$A$1000=C12)*'[filename1.xlsx]Sheet1'!$Y$1:$Y$1000)
+SUMPRODUCT(('[filename2.xlsm]Data from ABC'!$A$1:$A$1000=C12)*'[filename2.xlsm]Data from ABC'!$Y$1:$Y$1000)

现在关闭文件名 1 和文件名 2。现在，公式中应该是文件名1和文件名2的完整路径，并且值可自动更新。

问候

阿克塞尔

Answer 2

首先，声明一些变量来表示您的文件：

Dim f1 as Workbook, f2 as Workbook
Set f1 = Workbooks("filename1.xlsx")
Set f2 = Workbooks("filename2.xlsx")

现在，您可以在代码中访问这些对象，并使用它们的 Path 和 Name 属性，这将有助于构建公式字符串，该字符串在工作簿关闭后依赖于这些对象：

当引用这样的公式时，我认为语法是：

'_workbook_path[_workbook_name_]_sheet_name'!_cell_address

所以，像这样的东西(未经测试，如果您的文件名/工作表名称有空格/等，可能需要一些调整(

ActiveCell.Formula = "=SumIf('" & f1.Path & "[" & f1.Name & "]Sheet1'!$Y:Y)+" & _
                     "SumIf('" & f2.Path & "[" & f2.Name & "]Data from ABC'!$Y:Y)"

等。

然而

目前尚不清楚您何时/如何实现这一点，并且可能有更好的方法可以从未打开的orkbooks中获取数据，包括ADODB和ExecuteExcel4Macro方法。