如何通过Excel中的工作表索引号对多个工作表中的同一单元格求和? [英] How can you sum the same cell across multiple worksheets by worksheet index number in excel?

问题描述

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

I am trying to sum cell Y116 across all valid worksheets in my excel workbook. To help define the valid worksheets, I wrote a VBA function, SHEETNAME(number), that returns the name of the worksheet at the given worksheet index (number). I did this because the names and number of valid worksheets will never be constant, however the valid range will always start at the 3rd worksheet (i.e. SHEETNAME(3)) and will always end at the third from last worksheet (i.e. SHEETNAME(SHEETS()-2)) in my workbook.

我觉得使用SUM()和INDIRECT()都应该相对简单一些，但是我总是收到引用错误(#REF！).

I feel like this should be relatively straightforward with both SUM() and INDIRECT(), but I keep getting reference errors (#REF!).

我可以使用

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

但是当我尝试将所有内容放在一起时，我得到一个参考错误:

but I get a reference error when I try to put it all together:

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

我知道单元格Y116在我所有的工作表中都是有效的引用，因为我可以使用工作表的实际名称而不是索引对公式进行硬编码，从而得到所需的答案.有什么建议吗?

I know cell Y116 is a valid reference in all of my worksheets because I can hardcode the formula with the actual names of the worksheets instead of the index and I get the answer I am looking for. Any advice?

这是SHEETNAME()函数: Function SHEETNAME(number As Long) As String
SHEETNAME = Sheets(number).Name End Function

Here is the SHEETNAME() function: Function SHEETNAME(number As Long) As String
SHEETNAME = Sheets(number).Name End Function

推荐答案

要对SUM(INDIRECT())进行操作，需要将一组工作表名称返回给间接表.

To do what you want with SUM(INDIRECT()), on needs to return an array of sheet names to the indirect.

要执行此操作，需要将UDF更改为:

To do this one will need to change the UDF to:

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中进行求和:

---

But if you are going to iterate the sheets anyway, why not just do the sum in the 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")

这篇关于如何通过Excel中的工作表索引号对多个工作表中的同一单元格求和?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！