总和（）横跨动态数量的表 [英] Sum() Across Dynamic Number of Sheets
问题描述
你好，所有的谢谢你的帮助提前，
我有一个excel表，只是为了得到多张表的总和。最好和最简单的说，公式就像 = sum（Sheet1！A1，Sheet2！A1，Sheet3！A1，Sheet4！A1）。有一些问题使问题复杂化。首先，我不知道表格的数量或顺序总和，也不知道他们的名字。该公式将被复制到〜150个其他单元格中，所以我需要求和才能是动态的，而不是每次在150个单元格之间物理地添加页面。 （另外，表单的配置和命名不允许方便地拖动公式。）
所以首先，我以为我可以使用 indirect（）引用。我做了一个列，列出将添加到每个单元格的所有工作表名称。不幸的是， concatenate（）不能在数组上使用，所以我不得不诉诸于下面的UDF：
函数CONCAT（分隔符作为变体，ParamArray CellRanges（）As Variant）As String
Dim Cell As Range，Area As Variant
如果IsMissing（Delimiter）Then Delimiter =
对于CellRanges中的每个区域
如果TypeName（Area）=Range然后
对于每个单元格区域
如果Len（Cell.Value）则CONCAT = CONCAT&分隔符Cell.Value
Next
Else
CONCAT = CONCAT&分隔符区域
结束如果
下一个
CONCAT =中（CONCAT，Len（分隔符）+ 1）
结束函数
使用UDF，我可以使用正确的语法获取一个大字符串，例如 = CONCAT（'！A&（B1 + 1 ）及;，'，Array_of_Sheets）及 '！A 及（B1 + 1）。 CONCAT（）将分隔符作为第一个参数，数组作为第二个参数。然后我在字符串的末尾附加分隔符，并输出诸如 Sheet1'！A1，'Sheet2'！A1 的内容。我想，在这一点上，一个简单的 = sum（indirect（STRING））将足够，但 = sum（indirect（Sheet1！A1， 不起作用，因为 indirect（）似乎不能处理逗号。
所以要解决这个问题，我把 = CONCAT（）切换到 = CONCAT（'！一个&（B1 + 1）&+，Array_of_Sheets）&'！A&（B1 + 1）输出 Sheet1'！ A1 + 'Sheet2的'！A1 。现在我写了另一个UDF来强制评估如下：
函数EVAL（RefCell As String）
应用程序。 Volatile
EVAL =评估（RefCell）
结束功能
真棒，对吧经过一些使用和测试，似乎并不完全一致。当我们打开其他工作表时，并不总是评估（我们将打开其他工作表）。在UDF中使用 volatile 还有一些其他不一致之处，但我找不到它们。
所以对于我的理想，我想这样做没有任何VBA，但我怀疑是不可能的。我宁可不依赖于用户手动重新计算工作表（部分原因是 volatile ）。最后，我只是希望它以一致的方式 sum（），所以如果用户打开文档并且只打印打印，用户就不需要确认功能正在评估正确（检查#REF错误或手动添加值以确保它们是正确的）。我希望找到使用 eval（）方程式的替代方案。
编辑其他信息以下
我曾尝试过使用3D阵列，但遇到了一些问题。让我们说工作簿有八张。在这八张纸中，只有两张可以进入这张纸。有一个设置表，总计表，$ code>数据输入 sheet和数据分析表。 总计表格总计关闭数据分析表。通常，数据输入表格与数据分析表格一致。因此，用户可以复制数据条目和数据分析表。表格中最合乎逻辑的顺序将以安装程序表格开头，然后每次配对数据输入和数据分析表，以及总计表。显然，这个设置不能用于3D阵列。我们必须重新排列纸张，并确保用户知道纸张的顺序确实重要（非典型配置）。还将有一个或两个数据分析表不会包含在总计表中的情况。因此，我们可能会得到一个表格配置 setup \ data entry（1）\ data entry（2）\ START \ data anaylsis（1）\ data analysis（2）\ END \总计\数据输入（3）\数据输入（4）\ data anaylsis（3）\数据分析（4）。我的感觉是，3D阵列配置几乎可以使动态添加。我宁愿有一个明确的列表，然后依赖于用户有信心将表格移动到正确的总计。
这是另一种方法：
假设您的用户的数据输入表根据必须严格遵守的某种系统进行标签（例如数据输入1，数据输入2等），您可以使用 INDIRECT 构建汇总表，然后再填写此表。
详细来说，您的总计表中将有一个帮手表，看起来像这样：
Col Col B Col C Col D
1 C5 X7
2工作表名称存在？值1值2
3数据输入1 TRUE 10 20
4数据输入2 FALSE 0 0
5数据输入3 TRUE 20 30
6 ...
。
。
。
20更多的名单比你永远得到只是为了确定！
- 在单元格C1和以下列中，您将编写要合并的单元格的地址。如果你有一个模板，并希望保持引用动态（通常地址的静态特性是这里的bug的一个来源），使用
= ADDRESS（ROW（Template！C5）; COLUMN（Template！C5 ）获取动态地址。 - 在列B中，您将使用以下公式：
= NOT（ISERROR（INDIRECT （1,1 ,,, A3）））） - 在C列和以下列中，使用：
= IF B3，INDIRECT（'& A3&'！& C $ 1），0）
现在，您只需要总共列C等。
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.)
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:
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.
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:
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 volatile in the UDF, but I cannot find them.
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.
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 setup sheet, the total sheet, a data entry sheet, and a data analysis sheet. The total sheet totals off of the data analysis sheets. Typically a data entry sheet coincides with a data analysis sheet. So the user may make copies of both the data entry and the data analysis sheets. The most logical order of the sheets would begin with the setup sheet, then each pairing of the data entry and data analysis sheets, and the total sheet. 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 analysis sheets will not be included in the total sheet. 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 too dynamic. I would rather have an explicit list then depend on the user being confident in moving the sheets around to have the proper totals.
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.
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)
Now you only need to total the columns C and so forth.
这篇关于总和（）横跨动态数量的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
