来自多个工作表的 Excel VBA 总和
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41028767/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VBA Sum from Multiple Sheets
提问by bearded4glory
I am trying to create a function or functions that can sum daily hours from time cards for each client to come up with the total hours worked per day. Each client has it's own sheet inside of a single workbook.
我正在尝试创建一个或多个函数，该函数可以从每个客户的时间卡中总结每天的工作时间，得出每天的总工作时间。每个客户在单个工作簿中都有自己的工作表。
Currently, I have a function that determines the sheet that goes with the first client (the third sheet in the workbook):
目前，我有一个函数可以确定与第一个客户端（工作簿中的第三个工作表）一起使用的工作表：
Function FirstSheet()
Application.Volatile
FirstSheet = Sheets(3).Name
End Function
And one to find the last sheet:
还有一个找到最后一张纸：
Function LastSheet()
Application.Volatile
LastSheet = Sheets(Sheets.Count).Name
End Function
The part that I am having trouble with it getting these to work within the sum function.
我无法让这些在 sum 函数中工作的部分。
=sum(FirstSheet():LastSheet()!A1
That is basically what I want to accomplish. I think the problem is that I don't know how to concatenate it without turning it into a string and it doesn't realize that it is sheet and cell references.
这基本上就是我想要完成的。我认为问题在于我不知道如何在不将其转换为字符串的情况下将其连接起来，并且它没有意识到它是工作表和单元格引用。
Any help would be greatly appreciated.
任何帮助将不胜感激。
回答by user1274820
So, an example formula would look like this:
因此，示例公式如下所示：
=SUM(Sheet2!A1:A5,Sheet3!A1:A5,Sheet4!A1:A5)
=SUM(Sheet2!A1:A5,Sheet3!A1:A5,Sheet4!A1:A5)
That would sum Sheet2-Sheet4, A1:A5on all sheets.
这将总结Sheet2-Sheet4，A1:A5在所有工作表上。
Is there a reason you need to write the VBA code to do this?
是否有理由需要编写 VBA 代码来执行此操作？
Can't you just enter it as a formula once?
你不能把它作为公式输入一次吗？
Also, if you're going to the trouble of writing VBA to generate a formula, it may make more sense to just do the sum entirely in VBA code.
此外，如果您在编写 VBA 以生成公式时遇到麻烦，那么完全在 VBA 代码中进行求和可能更有意义。
If not, try this:
如果没有，试试这个：
Sub GenerateTheFormula()
Dim x, Formula
Formula = "=SUM(" 'Formula begins with =SUM(
For x = 3 To Sheets.Count
Formula = Formula & Sheets(x).Name & "!A1," 'Add SheetName and Cell and Comma
Next x
Formula = Left(Formula, Len(Formula) - 1) & ")" 'Remove trailing comma and add parenthesis
Range("B1").Formula = Formula 'Where do you want to put this formula?
End Sub
Results:
结果：
回答by Scott Craner
The functions return strings and not actual worksheets. The Worksheet does not parse strings well. So add a third function that uses the Evaluate function:
这些函数返回字符串而不是实际的工作表。工作表不能很好地解析字符串。所以添加第三个使用 Evaluate 函数的函数：
Function MySum(rng As Range)
MySum = Application.Caller.Parent.Evaluate("SUM(" & FirstSheet & ":" & LastSheet & "!" & rng.Address & ")")
End Function
Then you would simply call it: MySum(A1)
然后你可以简单地调用它： MySum(A1)
It uses the other two function you already have created to create a string that can be evaluated as a formula.
它使用您已经创建的另外两个函数来创建一个可以作为公式计算的字符串。
回答by Scott Craner
I didn't understand ur question completely but As I understood u have different sheets of different clients which contains supoose column 1 date and column 2
contains hours on that particular date wise hours and a final sheet which column1 contains name of client and column 2 contains total hours
Please try it
我没有完全理解你的问题，但据我所知，你有不同客户的不同工作表，其中包含第 1 列日期，第 2 列包含该特定日期的小时数和最后一张工作表，其中第 1 列包含客户名称，第 2 列包含总小时数
请试试
Sub countHours()
Dim last_Row As Integer
Dim sum As Double
sum = 0
'Because I know number of client
For i = 1 To 2 'i shows client particular sheet
last_Row = Range("A" & Rows.Count).End(xlUp).Row
Sheets(i).Activate
For j = 2 To last_Row
'In my Excel sheet column 1 contains dates and column 2 contains number of hours
sum = sum + Cells(j, 2)
'MsgBox sum
Next j
'Sheet 3 is my final sheet
ThisWorkbook.Sheets(3).Cells(i + 1, 2).Value = sum
sum = 0
Next i
End Sub
Happy Coding :
快乐编码：
