如何让我的公式总是参考最后一张表？

问题描述

我目前在我的excel文件中有2个工作表。

第一个工作表被称为摘要页面，其中显示了第二个工作表的摘要结果。 p>

第二张表被称为原始数据。一个例子是一个名为Fruits的列。

  Apple 
 Apple 
 Apple 
香蕉
香蕉
梨
  

在第一张表中，我会有一个计算公式相应水果出现的次数，结果将显示在不同的单元格中。

  = COUNTIF（Fruits！A2：A7 ，Apple）
 = COUNTIF（Fruits！A2：A7，Banana）
  

我想做的是，我可以编程公式，以便每次我添加一张原始数据（第三张）时，第一张表上的统计信息可以参考最新的表格来获取信息。

（假设数据和全部的位置与第二张表格相同）

什么到目前为止，我已经完成了一个功能 GETLASTWSNAME（），它能够始终检索最后一个工作表的名称。但是我似乎无法将该函数嵌套在countif公式本身中。

  = COUNTIF（（GETLASTWSNAME（））！ A2：A7，苹果
  

上面的公式是我想要我的公式工作，但可悲的是excel不允许我这样做。

任何意见都不会赞赏谢谢！

解决方案

= COUNTIF（INDIRECT（GETLASTWSNAME（）&！A2：A7），Apple）

I currently have 2 worksheets in my excel file.

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.

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.

=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.

Any comments would be appreciated. Thanks!

解决方案

=COUNTIF(INDIRECT(GETLASTWSNAME() & "!A2:A7"),"Apple")

这篇关于如何让我的公式总是参考最后一张表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！