如何在Excel中按工作表索引号对多个工作表中的同一单元格求和?

Question

我正在尝试对 excel 工作簿中所有有效工作表中的单元格 Y116 求和。为了帮助定义有效的工作表，我编写了一个VBA函数SHEETNAME(number(，该函数返回给定工作表索引(数字(处的工作表名称。我这样做是因为有效工作表的名称和数量永远不会是恒定的，但是有效范围将始终从第三个工作表(即 SHEETNAME(3((开始，并且始终从最后一个工作表的第三个工作表结束(即 SHEETNAME(SHEETS((-2((在我的工作簿中。

我觉得这对于 SUM(( 和 INDIRECT(( 都应该相对简单，但我不断收到引用错误(#REF！

我可以按照我想要的方式格式化字符串

="'"&SHEETNAME(3)&":"&SHEETNAME(SHEETS()-2)&"'!Y116"

但是当我尝试将它们放在一起时，我收到一个参考错误：

=SUM(INDIRECT("'"&SHEETNAME(3)&":"&SHEETNAME(SHEETS()-2)&"'!Y116"))

我知道单元格 Y116 是我所有工作表中的有效参考，因为我可以使用工作表的实际名称而不是索引对公式进行硬编码，并且我得到了我正在寻找的答案。有什么建议吗？

下面是 SHEETNAME(( 函数： Function SHEETNAME(number As Long) As String
SHEETNAME = Sheets(number).Name End Function

Answer 1

要使用 SUM(INDIRECT((( 做你想做的事情，on 需要将工作表名称数组返回给间接。

为此，需要将 UDF 更改为：

Function SHEETNAME(srt As Long, ed As Long) As Variant
Application.Volatile
If ed - srt <= 0 Then Exit Function
Dim temp() As Variant
ReDim temp(1 To ed - srt + 1) As Variant
Dim i As Long
For i = srt To ed
temp(i - srt + 1) = Worksheets(i).Name
Next
SHEETNAME = temp
End Function

然后可以使用：

=SUM(INDIRECT("'"&SHEETNAME(3,SHEETS()-2)&"'!Y116"))

---

但是，如果您无论如何都要迭代工作表，为什么不直接在 UDF 中进行求和：

Function MY3DSUM(srt As Long, ed As Long, add As String) As Double
Application.Volatile
If ed - srt <= 0 Then Exit Function
Dim temp As Double
temp = 0
Dim i As Long
For i = srt To ed
temp = temp + Worksheets(i).Range(add).Value2
Next
MY3DSUM = temp
End Function

然后你会称之为：

=MY3DSUM(3,SHEETS()-2,"Y116")