excel - 跨多个工作表的索引匹配

Question

我有一张工作表，我需要从另一个工作簿中提取特定数据，该工作簿有超过 20 个按年份命名的选项卡。

我需要匹配三组标准才能完成此操作，因此在所有选项卡和主工作表中创建了一个串联列，该列将由相同的数据组成(所有字母数字都添加为文本)。

我可以从一张纸上做到这一点，但需要在很多地方做，并希望它是动态的，所以我不必每年都管理公式。

表 1 称为 ADS，表 2 称为 NDTREQ，在 ADS 文件中，我为年份名称创建了一个名为 Years 的表，但不知道如何将其合并到动态公式中。

这是我在 ADS 文件中单元格中的公式如下:

=IF(B16="","",INDEX([NDTREQ.xls]2018'!$G$1:$G$65536,MATCH(J16,[NDTREQ.xls]2018'!$A$1:$A$65536,FALSE),0))

我怎样才能让它动态工作？

Answer 1

您可以使公式的选项卡引用部分动态化，即 [NDTREQ.xls]2018' .

因此，不是在公式中“硬编码”工作表的年份“2018”，而是可以引用一个单元格(在我的代码示例中，我使用单元格:C5)。该单元格将包含您要使用公式的年份(以及明确的工作表选项卡)。
IE。将 C5 中的值从 2017 更改为 2018 会将公式从工作表选项卡 2017 重定向到选项卡 2018。

所以你的公式应该是这样的:

=IF(B16="","",INDEX(INDIRECT("[NDTREQ.xls]"&C5&"!"&"$G$1:$G$65536"),MATCH(J16,INDIRECT("[NDTREQ.xls]"&C5&"!"&"$A$1:$A$6553"),FALSE),0))

示例说明:

这是 INDIRECT允许通过动态公式执行此操作的函数:
INDIRECT("[NDTREQ.xls]"&C5&"!"&"B4:B7")

在哪里 [NDTREQ.xls]是文件名，

"&C5&"!"是文件“NDTREQ”​​中的选项卡名称，

&"B4:B7"是您要在其中返回值的索引公式中的范围。

编辑标记:
我分享了我使用过的两份文档，让您了解使用 INDIRECT 可以完成的工作。 Excel 中的函数。文件 ADS 是“主/主”文件，它使用来自 NDTREQ 文件的数据。

同时打开同一文件夹中的两个工作表。然后你应该得到如图所示的结果。
(如果要将它们放在不同的位置，则需要将文档与路径名链接起来)。


https://www.dropbox.com/s/a4b86erzq6xj5l6/ADS.xls?dl=0
https://www.dropbox.com/s/dq1yhgm2zppyaf3/NDTREQ.xls?dl=0