问题描述
背景
美国政府发布我用于工作的每日报告.最近，政府改变了他们发布报告的方式.他们现在将报告发布为 pdf，而不是作为文本文件发布.不幸的是，这影响了我从报告中提取信息的方式.以前，我从站点中提取信息，因为它已保存为文本文件.现在，我下载pdf并将其转换为excel文件.
The U.S. government releases a daily report I use for work. Recently, the government changed how they release their report. Instead of releasing the report as a text file, they are now releasing it as pdf. Unfortunately, this has affected how I pull information from the report. Previously, I pulled the information from the site as it was saved as a text file. Now, I download the pdf and convert it to an excel file.
我有 1 个工作簿，其中包含单个工作表上保存的报告副本，例如4 月 30 日、4 月 29 日、4 月 28 日等.不幸的是，由于转换，每个页面上的信息可能会有一两行不同，例如4 月 30 日工作表上的 Information_A 可能位于第 30 行，而在 4 月 29 日它可能位于第 33 行.
I have 1 workbook that contains saved copies of the report on individual worksheets, e.g. April 30, April 29, April 28, etc. Unfortunately, due to the conversion, the information on each page can vary by a row or two, e.g. Information_A on April 30 worksheet could be on row 30 and on April 29 it could be on row 33.
在工作簿的第一页上，我有一个摘要页，它从上一页中提取关键信息，例如摘要将从 4 月 30 日的工作表中提取信息.
On the first page of the workbook, I have a summary page which pulls key information from the previous page, e.g. Summary would pull information from the April 30 worksheet.
问题
现在的问题来自于我如何从 excel 文件中提取信息.由于信息不在同一行，我认为 Index/Match 方法是最好的.我做的公式如下.参考名称 Information_A 在 A 列中，我需要从 Information_A 中获得的值在 P 列中.
The problem now comes from how I pull the information from the excel file. Since the information is not on the same row, I thought the Index/Match method would be best. The formula I made is below. The reference name, Information_A, is in column A and the value I need from Information_A is in column P.
=(INDEX('April 30'!P:P,MATCH("Information_A",'April 30'!A:A,0)))
由于我的工作表不断变化，我想我可以提取最后一个工作表名称，然后像这样将其输入到公式中，
Since my worksheets constantly change I thought I would be able to pull the last worksheet name and then input it into the formula like so,
=(INDEX('[previous_sheet]'P:P,MATCH("Information_A",'[previous_sheet]'!A:A,0)))
所以我试图弄清楚如何提取以前的工作表名称.我尝试了几种不同的方法，最终使用了 ChrisB 的解决方案 这里.
So I tried to figure out how to pull the previous sheet name. I tried a few different ways and ended up using ChrisB's solution here.
我已将他提供的公式(如下)保存为 PrevSheet_2
I had saved the formula he provided (below) as PrevSheet_2
=IF(MATCH(wsName,wsNamesArray,0)-1 =0, ERROR.TYPE(7), INDEX(wsNamesArray,MATCH(wsName,wsNamesArray,0)+1))
其中，wsNamesArray 和 wsName 如下，
Where, wsNamesArray and wsNameare as follows,
wsNamesArray: =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) &T(NOW())
wsNamesArray: =RIGHT(GET.WORKBOOK(1),LEN(GET.WORKBOOK(1))-FIND("]",GET.WORKBOOK(1))) & T(NOW())
wsName: =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！错误.
which doesn't work. This formula gives me a #VALUE! error.
我在不使用 PrevSheet_2 的情况下测试了公式并输入了工作表名称，例如4 月 30 日，得到了正确的值，所以我知道那部分正在工作.我还在一个单元格中测试了 PrevSheet_2 并得到了正确的前一个工作表，所以我知道问题在于 2 的组合.
I tested the formula without using PrevSheet_2 and typed in the worksheet name, e.g. April 30, and got the correct value, so I know that part is working. I also test PrevSheet_2 in a cell and got the correct previous sheet as well, so I know the problem lies in the combination of the 2.
那么，我的问题是如何制作这个
My question, then, is how to make this
=(INDEX("'"&PrevSheet_2&"'!P:P",MATCH(Information_A","'"&PrevSheet_2&"'!A:A",0)))
公式工作.
我感觉 PrevSheet_2 的数据类型有误，但我不知道如何解决.
I have a feeling PrevSheet_2 is in the wrong data type however I don't know how to fix it.
我也尝试过使用 INDIRECT(如下)并得到了一个 #REF！错误.
I have also tried using INDIRECT (below) and got a #REF! error.
=INDEX(INDIRECT("''"&PrevSheet_2 &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PrevSheet_2&"'!A:A"),0))
推荐答案
参考 这个链接 .. 定义 SheetNames 数组(在公式 > 定义名称)为 SheetNames = MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))) .. 然后在 excel 公式中，您可以将上一个工作表名称设为 INDEX(SheetNames,SHEET()-1)
Refer this link .. Define SheetNames array (in Formulas > Define Name) as SheetNames = MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))) .. Then in excel formula you can get to previous sheet name as INDEX(SheetNames,SHEET()-1)
你也可以定义 PreviousSheetName = INDEX(MID(GET.WORKBOOK(1),FIND("]",GET.WORKBOOK(1),1)+1,LEN(GET.WORKBOOK(1))),SHEET()-1)
您可以按照@BigBen 的建议将其放在 INDIRECT 函数中
You can put that in INDIRECT function as suggested by @BigBen
所以公式将是 =INDEX(INDIRECT("'"&PreviousSheetName &"'!P:P"),MATCH("Information_A",INDIRECT("'"&PreviousSheetName&"'!A:A"),0))
See .. Sheet 函数为我们提供了当前工作表的索引.所以 Sheet()-1 给了我们上一张表的索引
See .. Sheet function gives us Index of the current sheet. So Sheet()-1 gives us Index of the previous sheet
在我回答之后，我注意到您在间接公式中放置了两次单引号.因此，#REF 错误
After my answer I noted you have put single quotes TWICE in your indirect formula. Hence, the #REF error
这篇关于索引/匹配公式中的动态工作表名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！