vba 在单元格中按编号而不是名称引用工作表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19790985/
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
referencing sheets by number instead of name in cells
提问by user2872180
Lets say
sheet3.name = "d"
可以说
sheet3.name = "d"
Is there a way I could put in a cell on sheet2the formula =sum(sheet3!b:b)where sheet3is being substituted with the actual sheet3 name?
有没有一种方法我可以把在一个单元格sheet2的公式=sum(sheet3!b:b)，其中sheet3被取代与实际工作表Sheet 3名？
I can only get =sum('d'!b:b)to work so far.
=sum('d'!b:b)到目前为止我只能上班。
I could use VBA for this probably but I'm curious how to do this in a cell so I don't have to run a macro each time.
我可能会为此使用 VBA，但我很好奇如何在单元格中执行此操作，因此我不必每次都运行宏。
采纳答案by SeanC
If you can use a UDF User Defined Functionthat will return the sheet name
如果您可以使用将返回工作表名称的 UDF用户定义函数
Function SHEETNAME(number As Long) As String
SHEETNAME = Sheets(number).Name
End Function
then a formula like
然后一个公式像
=SUM(INDIRECT(SHEETNAME(3) &"!B:B"))
will return the sum from column B on sheet 3.
将返回表 3 上 B 列的总和。
SHEETNAME(number)returns the sheet name of the number which is index.
SHEETNAME(number)返回作为索引的数字的工作表名称。
So Sheet(1)returns Sheet1, etc
所以Sheet(1)返回 Sheet1 等
回答by SeanC
Use below formula anywhere in the sheet to get the sheet name - the sheet musthave a filename for this to work:
在工作表中的任何位置使用以下公式来获取工作表名称 - 工作表必须有一个文件名才能工作：
=REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")
You can either reference that cell using Indirect:
您可以使用间接引用该单元格：
=SUM(Indirect("'"&A1&"'!B:B"))
or, if you don't want to have a second cell, you can combine the two formulas into one:
或者，如果您不想拥有第二个单元格，则可以将两个公式合二为一：
=SUM(INDIRECT("'"&REPLACE(CELL("filename"),1,FIND("]",CELL("filename")),"")&"'!B:B"))
回答by Kazimierz Jawor
I'm not sure if this is a good idea but it's the first one I could think of.
我不确定这是否是个好主意，但这是我能想到的第一个。
I would add additional function to your VBA project which will return actual name of your Sheet3:
我会向您的 VBA 项目添加附加功能，该功能将返回您的 Sheet3 的实际名称：
Function Sheet3Name()
Sheet3Name = Sheet3.Name
End Function
Next, when you create sum formula of column B:B in Excel cell you need to do it in this way:
接下来，当您在 Excel 单元格中创建列 B:B 的总和公式时，您需要这样做：
=SUM(INDIRECT(Sheet3Name()&"!A:A"))
回答by edata
For anyone not concerned with the order of the sheets, the post by Biff hereon mrexcel.com works well.
对于任何不关心纸张的顺序，后由一击这里上mrexcel.com效果很好。
In Excel 2013, go to the Formulas tab in the ribbon and make a defined name:
在 Excel 2013 中，转到功能区中的“公式”选项卡并定义一个名称：
Name: SheetNames
Refers to: =GET.WORKBOOK(1)&T(NOW())
Then use a formula like this example:
然后使用像这个例子这样的公式：
=INDIRECT("'"&INDEX(MID(SheetNames,FIND("]",SheetNames)+1,255),A3)&"'!A1")
where A3 refers to the index number in a cell in the current sheet, and A1 refers to the location of the value to be retrieved from the other sheet. I.e., in the current sheet, if A3 = 2, then the formula will point to cell A1 in the second sheet of the workbook. I just use a column of index numbers in my current sheet, then drag this formula down and it fills in values from all of my other sheets.
其中 A3 是指当前工作表中单元格中的索引号，A1 是指要从其他工作表中检索的值的位置。即，在当前工作表中，如果 A3 = 2，则公式将指向工作簿第二个工作表中的单元格 A1。我只是在当前工作表中使用一列索引号，然后向下拖动这个公式，它会填充我所有其他工作表中的值。
You will need to save as a macro-enabled file (.xlsm).
您需要另存为启用宏的文件 (.xlsm)。
