vba 如何让我的公式始终参考最后一张纸？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10371030/
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
How do I get my formula to always reference to the last sheet?
提问by Thomas
I currently have 2 worksheets in my excel file.
我目前在我的 excel 文件中有 2 个工作表。
The first sheet is known as the Summary page, which displays an summary result of the second sheet.
第一个工作表称为“摘要”页面，它显示第二个工作表的摘要结果。
The second sheet is known as the raw data. An example would be a column named Fruits.
第二张表称为原始数据。一个例子是名为 Fruits 的列。
Apple
Apple
Apple
Banana
Banana
Pear
In the first sheet, I would have a formula that counts the number of time the respective fruits appear and the result will be displayed in different cells.
在第一张表中，我将有一个公式来计算各个水果出现的次数，并且结果将显示在不同的单元格中。
=COUNTIF(Fruits!A2:A7,"Apple")
=COUNTIF(Fruits!A2:A7,"Banana")
What I wanna do is, is it possible for me to program the formula such that everytime I add a new sheet of raw data (3rd sheet), the statistics on the first sheet is able to reference to the latest sheet to get the information.
我想要做的是，我是否可以对公式进行编程，以便每次添加新的原始数据表（第三张表）时，第一张表上的统计数据都可以参考最新的表来获取信息。
(Assuming that the positioning of the data and all are the same as the second sheet.)
（假设数据的定位和所有的都和第二张表一样。）
What I have done so far is to come out with a function GETLASTWSNAME()which is able to always retrieve the name of the last worksheet. but it seems kinda impossible for me to nest the function within the countif formula itself.
到目前为止，我所做的是推出一个GETLASTWSNAME()能够始终检索最后一个工作表名称的函数。但我似乎不太可能将函数嵌套在 countif 公式本身中。
=COUNTIF((GETLASTWSNAME())!A2:A7,"Apple)
The above formula is how i want my formula to work, but sadly excel does not allow me to do that.
上面的公式是我希望我的公式如何工作，但遗憾的是 excel 不允许我这样做。
Any comments would be appreciated. Thanks!
任何意见将不胜感激。谢谢！
采纳答案by Tomalak
=COUNTIF(INDIRECT(GETLASTWSNAME() & "!A2:A7"),"Apple")
=COUNTIF(INDIRECT(GETLASTWSNAME() & "!A2:A7"),"Apple")
回答by brettdj
You can use the XLM/Range Name workaround for this and not VBA if you prefer
如果您愿意，您可以使用 XLM/Range Name 解决方法，而不是 VBA
- Define a range name, wshNames to hold the array of sheet names
=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
Uses David Hager's technique - Use this Excel formula to extract the last sheet name from the array of sheet names
=INDEX(wshNames,COUNTA(wshNames)+RAND()*0)
- 定义范围名称 wshNames 以保存工作表名称数组
=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1)))
使用 David Hager 的技术 - 使用此 Excel 公式从工作表名称数组中提取最后一个工作表名称
=INDEX(wshNames,COUNTA(wshNames)+RAND()*0)
This formula says look at all the sheets, then return the last (using the COUNTA). The RAND()*0)portion ensures that the formula is volatile and updates when Excel does
此公式表示查看所有工作表，然后返回最后一张（使用 COUNTA）。该RAND()*0)部分确保公式是易变的，并在 Excel 执行时更新
If you do use VBA you will need to ensure your GETLASTWSNAMEfunction is volatile, i.e. it gets updated when changes occur.
如果您确实使用 VBA，您将需要确保您的GETLASTWSNAME函数是可变的，即在发生更改时它会更新。
