excel - 索引/匹配公式中的动态工作表名称

Question

背景

美国政府发布我用于工作的每日报告。最近，政府改变了他们发布报告的方式。他们不再以文本文件的形式发布报告，而是以 pdf 格式发布。不幸的是，这影响了我从报告中提取信息的方式。以前，我从站点中提取信息，因为它被保存为文本文件。现在，我下载 pdf 并将其转换为 excel 文件。

我有 1 个工作簿，其中包含各个工作表上已保存的报告副本，例如 4 月 30 日、4 月 29 日、4 月 28 日等。不幸的是，由于转换，每页上的信息可能会有所不同，例如 Information_A on 4 月 30 日的工作表可能位于第 30 行，而 4 月 29 日的工作表可能位于第 33 行。

在工作簿的第一页，我有一个摘要页面，它从前一页中提取关键信息，例如，摘要将从 4 月 30 日的工作表中提取信息。

问题

现在的问题来自我如何从 excel 文件中提取信息。由于信息不在同一行，我认为索引/匹配方法是最好的。我做的公式如下。参考名称 Information_A 在 A 列中，而我需要的 Information_A 值在 P 列中。

=(INDEX('April 30'!P:P,MATCH("Information_A",'April 30'!A:A,0)))

由于我的工作表不断变化，我认为我可以提取最后一个工作表名称，然后将其输入到公式中，如下所示，

=(INDEX('[previous_sheet]'P:P,MATCH("Information_A",'[previous_sheet]'!A:A,0)))

所以我试图弄清楚如何提取以前的工作表名称。我尝试了几种不同的方法，最终在这里使用了 ChrisB 的解决方案。

我已将他提供的公式（如下）保存为 PrevSheet_2

=IF( MATCH(wsName,wsNamesArray,0)-1 =0, ERROR.TYPE(7), INDEX(wsNamesArray,MATCH(wsName,wsNamesArray,0)+1))

其中，wsNamesArray和wsName分别如下，

wsNamesArray：=RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) & T(NOW())

名称：=MID(CELL("filename", INDIRECT("A1")),FIND("]",CELL("filename",INDIRECT("A1")))+1,255) & T(NOW())

这导致了“最终”公式，

=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))

这是行不通的。这个公式给了我一个#VALUE！错误。

我在没有使用 PrevSheet_2 的情况下测试了公式并输入了工作表名称，例如 April 30，并得到了正确的值，所以我知道该部分正在工作。我还在一个单元格中测试 PrevSheet_2 并获得了正确的前一张表，所以我知道问题出在 2 的组合上。

那么，我的问题是如何制作这个

=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))

公式工作。

我感觉 PrevSheet_2 的数据类型错误，但我不知道如何修复它。

编辑：

我也尝试过使用 INDIRECT（如下）并获得了#REF！错误。

=INDEX(INDIRECT("''"&PrevSheet_2 &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PrevSheet_2&"'!A:A"),0))

标签： excelexcel-formula

Answer 1

请参阅此链接.. 将 SheetNames 数组（在“公式”>“定义名称”中）定义为SheetNames = MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))).. 然后在 excel 公式中，您可以将以前的工作表名称作为INDEX(SheetNames,SHEET()-1)

你也可以定义PreviousSheetName = INDEX(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))),SHEET()-1)

您可以按照@BigBen 的建议将其放入 INDIRECT 函数中

所以公式将是=INDEX(INDIRECT("'"&PreviousSheetName &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PreviousSheetName&"'!A:A"),0))

请参阅 ..Sheet函数为我们提供当前工作表的索引。所以Sheet()-1给了我们上一张表的索引

在我回答之后，我注意到您在间接公式中添加了 TWICE 单引号。因此，#REF 错误