vba Sum() 跨动态张数

Sum() Across Dynamic Number of Sheets

提问by Jacob Bolda

Hello all and thanks for your help ahead of time,

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

I have an excel sheet that is solely to take the summation of multiple sheets. Best and most simply put, the formula is something like =sum(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1). There are a few issues that complicate matters though. First off all, I do not know the number or order of the sheets to sum, nor do I know their name. This formula will be copied into ~150 other cells, so I need the summation to be dynamic instead of physically adding sheets to ~150 cells every time. (Also the configuration and naming of the sheet does not allow for easy dragging for formulas.)

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

So first of all, I thought I could write it using an indirect()reference. I made a column to list all the sheets names that would be added into each cell. Unfortunately, concatenate()cannot be used over arrays, so I had to resort to a UDF seen below:

所以首先，我认为我可以使用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

Using the UDF, I could get a large string with the proper syntax such as =CONCAT("'!A"&(B1+1)&",'",Array_of_Sheets)&"'!A"&(B1+1). The CONCAT()takes a separator as the first parameter and the array(s) as the second parameter. I then append the "separator" on the end of the string as well to output something such as Sheet1'!A1,'Sheet2'!A1. I thought at this point, a simple =sum(indirect(STRING))would be sufficient, but =sum(indirect("Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1"))does not work since indirect()cannot seemingly process the commas.

使用 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()似乎无法处理逗号。

So to solve this, I switched the =CONCAT()to =CONCAT("'!A"&(B1+1)&"+'",Array_of_Sheets)&"'!A"&(B1+1)to output Sheet1'!A1+'Sheet2'!A1. Now I wrote another UDF to force it to evaluate seen below:

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

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

This worked! Awesome, right? After some use and testing though, it seems to not be quite consistent. When we have other sheets open, it does not always evaluate (and we will have other sheets open). There is some other inconsistencies that I read about with using volatilein the UDF, but I cannot find them.

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

So for my ideal, I would like to do this without any VBA at all, but I suspect that won't be possible. I would rather not depend on the user to manually recalculate worksheets (partly the reason for volatile). Finally, I just want it to sum()in a consistent manner so if a user opens the document and just hits print, the user does not need to confirm the functions are evaluating correctly (check for #REF errors or manually add up the values to make sure they are correct). I am hoping to find an alternative to using the eval()equation.

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

EDIT for additional information below

编辑以下附加信息

I had experimented with using 3D arrays, but there was a few issues I ran into. Lets say the workbook has eight sheets. Of those eight sheets, only two may feed into this total sheet. There is a setupsheet, the totalsheet, a data entrysheet, and a data analysissheet. The totalsheet totals off of the data analysissheets. Typically a data entrysheet coincides with a data analysissheet. So the user may make copies of both the data entryand the data analysissheets. The most logical order of the sheets would begin with the setupsheet, then each pairing of the data entryand data analysissheets, and the totalsheet. Obviously, this setup will not work with a 3D array. We would have to reorder the sheets and make sure the user knows that the order of the sheets actually matters (an atypical configuration). There will also be instances where one or two data analysissheets will not be included in the totalsheet. So we may end up with a sheet configuration of 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). My feeling was that a 3D array configuration would almost make the addition toodynamic. I would rather have an explicit list then depend on the user being confident in moving the sheets around to have the proper totals.

我曾尝试使用 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 阵列配置几乎会使添加过于动态。我宁愿有一个明确的列表，然后取决于用户是否有信心移动工作表以获得正确的总数。

采纳答案by Peter Albert

Here's another approach:

这是另一种方法：

Assuming that your user's data entry sheets are labelled according to some kind of system that has to be strictly adhered to (e.g. "Data entry 1", "Data entry 2", etc.), you could build a summary table using INDIRECT- and then total this table instead.

假设您的用户的数据输入表根据某种必须严格遵守的系统进行标记（例如“数据输入 1”、“数据输入 2”等），您可以使用INDIRECT- 然后构建一个汇总表改为总计这张表。

In detail, you'd have a helper table in your 'Totals' sheet, looking something like this:

详细地说，您的“总计”表中有一个辅助表，如下所示：

   Col A         Col B    Col C    Col D
1                         C5       X7
2  Sheet name    Exists?  Value 1  Value 2
3  Data entry 1  TRUE          10       20
4  Data entry 2  FALSE          0        0
5  Data entry 3  TRUE          20       30
6  ...
.
.
.
20 More sheet names than you'll ever get just to be sure!

• In cells C1 and the following columns you'd write the address of the cell you want to total. If you have a template and want to keep the references dynamic (usually the static nature of the address is a source of bugs here), use =ADDRESS(ROW(Template!C5);COLUMN(Template!C5))to get the dynamic address.
• In column B you'd use this formula: =NOT(ISERROR(INDIRECT(ADDRESS(1,1,,,A3))))
• In column C and the following columns, use this: =IF(B3,INDIRECT("'"&A3&"'!"&C$1),0)

• 在单元格 C1 和以下列中，您将写下要总计的单元格的地址。如果您有一个模板并希望保持引用动态（通常地址的静态性质是这里的错误来源），请使用=ADDRESS(ROW(Template!C5);COLUMN(Template!C5))获取动态地址。
• 在 B 列中，您将使用以下公式： =NOT(ISERROR(INDIRECT(ADDRESS(1,1,,,A3))))
• 在 C 列和以下列中，使用这个： =IF(B3,INDIRECT("'"&A3&"'!"&C$1),0)

Now you only need to total the columns C and so forth.

现在您只需要合计 C 列等等。

回答by Peter Albert

Good news! You can make your life much simpler - by using a 3D formula:

好消息！通过使用 3D 公式，您可以让您的生活变得更简单：

If you use this formula: =SUM(Sheet1:Sheet3!$A$1:$B$2), all cells in A1:B2 will be summed - across all sheet from Sheet1 to Sheet3!

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

Quite often, it is helpful to use two "helper" sheets to handle an open/changing number of worksheets:

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

1. Insert two worksheet called STARTand END
2. Span your 3D formula across those two sheets
3. Move all the sheets between or move STARTand ENDsheet around those sheets (of course, this can be done in step 1, too)
4. Hide the two helper sheets

1. 插入两个名为START和END 的工作表
2. 将您的 3D 公式跨越这两个工作表
3. 在这些工作表之间移动所有工作表或在这些工作表周围移动START和END 工作表（当然，这也可以在步骤 1 中完成）
4. 隐藏两个帮助表

Check out this linkfor further instructions.

查看此链接以获取更多说明。