excel - Sum() 跨动态张数

Question

大家好，提前感谢您的帮助，

我有一个 Excel 表，它仅用于汇总多张表。最好最简单地说，公式类似于 =sum(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1) .不过，有一些问题会使事情复杂化。首先，我不知道要汇总的表格的数量或顺序，也不知道它们的名字。这个公式将被复制到大约 150 个其他单元格中，因此我需要动态求和，而不是每次都将工作表物理添加到大约 150 个单元格中。 (此外，工作表的配置和命名也不允许轻松拖动公式。)

所以首先，我认为我可以使用 indirect() 来编写它。引用。我做了一个列来列出将添加到每个单元格中的所有工作表名称。不幸的是，concatenate()不能在数组上使用，所以我不得不求助于下面看到的 UDF:

Function CONCAT(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
Dim Cell As Range, Area As Variant
If IsMissing(Delimiter) Then Delimiter = ""
For Each Area In CellRanges
If TypeName(Area) = "Range" Then
For Each Cell In Area
If Len(Cell.Value) Then CONCAT = CONCAT & Delimiter & Cell.Value
Next
Else
CONCAT = CONCAT & Delimiter & Area
End If
Next
CONCAT = Mid(CONCAT, Len(Delimiter) + 1)
End Function

使用 UDF，我可以获得一个具有正确语法的大字符串，例如 =CONCAT("'!A"&(B1+1)&",'",Array_of_Sheets)&"'!A"&(B1+1) . CONCAT()将分隔符作为第一个参数，将数组作为第二个参数。然后我在字符串末尾附加“分隔符”以输出诸如 Sheet1'!A1,'Sheet2'!A1 之类的内容。 .想到这里，一个简单的 =sum(indirect(STRING))就足够了，但是 =sum(indirect("Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1"))自从 indirect() 后不起作用似乎无法处理逗号。

所以为了解决这个问题，我切换了 =CONCAT()至 =CONCAT("'!A"&(B1+1)&"+'",Array_of_Sheets)&"'!A"&(B1+1)输出 Sheet1'!A1+'Sheet2'!A1 .现在我写了另一个 UDF 来强制它评估如下所示:

Function EVAL(RefCell As String)
Application.Volatile
EVAL = Evaluate(RefCell)
End Function

这行得通!太棒了，对吧？但是经过一些使用和测试，它似乎不太一致。当我们打开其他工作表时，它并不总是评估(我们将打开其他工作表)。在使用 volatile 时，我读到了其他一些不一致的地方。在 UDF 中，但我找不到它们。

所以对于我的理想，我想在没有任何 VBA 的情况下做到这一点，但我怀疑这是不可能的。我宁愿不依赖用户手动重新计算工作表( volatile 的部分原因)。最后，我只想要 sum()以一致的方式，因此如果用户打开文档并只是点击打印，用户不需要确认函数正在正确评估(检查 #REF 错误或手动添加值以确保它们正确)。我希望找到使用 eval() 的替代方法方程。

编辑以下附加信息

我曾尝试使用 3D 数组，但遇到了一些问题。假设工作簿有八张纸。在这八张纸中，只有两张可以输入到这张总纸中。有一个 setup表， total表，一个 data entry表和 data analysis床单。 total data analysis 的工作表总计床单。通常是 data entry表与 data analysis 重合床单。所以用户可以复制 data entry和 data analysis床单。最合乎逻辑的工作表顺序将从 setup 开始。表，然后每对 data entry和 data analysis床单和 total床单。显然，此设置不适用于 3D 阵列。我们将不得不重新排序工作表并确保用户知道工作表的顺序实际上很重要(非典型配置)。在某些情况下，一两个 data analysis工作表不会包含在 total 中床单。所以我们最终可能会得到 setup \ data entry (1) \ data entry (2) \ START \ data anaylsis (1) \ data analysis (2) \ END \ totals \ data entry (3) \ data entry (4) \ data anaylsis (3) \ data analysis (4) 的工作表配置。 .我的感觉是 3D 阵列配置几乎会使添加过于动态。我宁愿有一个明确的列表，然后依赖于用户有信心移动工作表以获得正确的总数。

Answer 1

好消息!你可以让你的生活变得更简单——通过使用 3D 公式:

如果你使用这个公式:=SUM(Sheet1:Sheet3!$A$1:$B$2) ，A1:B2 中的所有单元格将被求和 - 从 Sheet1 到 Sheet3 的所有工作表!

通常，使用两个“帮助”表来处理打开/更改数量的工作表很有帮助:

插入两个名为 START 和 END

的工作表

将您的 3D 公式跨越这两张纸

在这些工作表之间移动所有工作表或在这些工作表周围移动 START 和 END 工作表(当然，这也可以在步骤 1 中完成)

隐藏两个帮助表

看看这个 link以获得进一步的说明。