Excel：从下拉单元格中获取公式的工作表名称

Question

如果我正确理解了您的意思，那么您需要一个摘要表，允许用户在下拉单元格中选择员工表名称，然后根据该选择查找单元格内容。

我将使用 VLOOKUP 执行此操作，但如果您是电子表格新手，您可能不知道这是一个旧函数，有时会导致问题，并且不被视为最佳实践。在 XLOOKUP（如果您有 Excel 2016+ 或 Excel 365）或“INDEX MATCH”上进行搜索，您将看到一种必备方法，如果您重新设计员工工作表，它将避免以后出现麻烦。

这是我为模拟您的问题而创建的工作表视图：

其中有您提到的“摘要”和“员工”表，以及我创建的名为“命名范围您的情况可能不需要这样做，但我发现将下拉列表和其他全局引用的内容放到名为 namedRanges 的工作表上更简单、更简洁 - 这样更容易找到和更改它们。在 namedRanges 中，我创建了工作表下拉列表的内容。我从 A1 开始创建列表，并使用我将用于该列表的名称，以便以后更容易阅读和诊断：

注意：这些必须与工作表名称完全一致。有一种方法可以确保它们匹配，即使您稍后更改了工作表名称，但这偏离了您的问题，所以我将该方法放在最后的注释中。

另外，我故意省略了 emp5，稍后将会讨论。

我们可以在这里停下来，但为了以后更容易管理，我们将为此下拉列表创建一个命名范围。为此，请转到公式选项卡并选择定义名称.我命名了我的范围_员工工作表名称在引用：字段中我输入了这个公式，该公式会在工作表发生更改时动态读取工作表列表： =$A$2:INDEX($A:$A,COUNTA($A:$A),1)

现在您已准备好实施解决方案。在概括，我申请了数据验证在下面数据带有列表验证的选项卡=_EmployeeSheetNames。

您也可以将=$A$2:INDEX($A:$A,COUNTA($A:$A),1)公式放在这里，但赋予其名称可以使其以后更容易重用、跟踪和管理。

现在B1在概括有您想要的下拉菜单 - 您可能还想命名该单元格，以使其他所有内容更具可读性，但我将继续而不命名它。

在单元格 B2 中，您可以输入所需的 vlookup，如下所示：

=VLOOKUP( A2, INDIRECT( "'" & $B$1 & "'!A1:N20" ), 14 ) 并将其复制到 B 列的其余单元格中。如果我理解了您的目的，那么这将解决它，但让我们讨论其他一些问题：

员工工作表发现

我想不出一种方法来动态发现新的员工表，因为它们是在没有 VBA 的情况下创建的。我尝试了一些技术，但它们从来没有奏效，如果有人有技术，那就太好了。所以你必须手动将员工添加到列表中命名范围。因此，因为我遗漏了emp5，它不在下拉列表中。如果您将 emp5 添加到列表中，它将自动添加到下拉列表中。

XLOOKUP 替代方案

VLOOKUP 的问题在于，如果您尝试处理的数据从第 N 列移动到另一列，则硬编码的 14 将导致 VLOOKUP 查看错误的数据，而您甚至可能不知道发生了什么。如果它抛出错误，您会很幸运，但如果它没有抛出错误，您可能真的不走运，因为它会默默地给您垃圾。XLOOKUP（和 INDEX MATCH 方法）避免了这个问题。上述 VLOOKUP 的另一种形式是：

=XLOOKUP( A2, INDIRECT( "'" & $B$1 & "'!A1:A20" ), INDIRECT( "'" & $B$1 & "'!N1:N20" ) )

挥发性

注意：INDIRECT 是一个易失性函数。每次输入数据或按 F9 进行计算时，它都会重新计算。如果您有许多其他单元格依赖于包含 INDIRECT 的单元格，它们也会重新计算，因此您最终可能会得到一个响应时间令人沮丧的电子表格。大多数情况下，这不是问题，但如果您发现每次有人在单元格中输入数据时，都需要 1-2 秒才能响应，则问题出在 INDIRECT 上。除了使用 INDIRECT，我不知道还有其他方法可以实现您的目标。

动态工作表名称

因此，本例中的工作表名称是手动输入的，并且必须与员工工作表名称完全匹配。如果以后有人可能会更改员工工作表名称，则必须更新 _EmployeeSheetNames 列表。自动执行此操作的一种方法是在每个员工工作表上创建一个获取工作表名称的命名范围。转到要命名的员工工作表，然后转到公式选项卡并选择定义名称. 创建一个名为_sheet名称使用以下公式：

=MID( CELL("filename", 'emp1'!$A$1 ), FIND( "]", CELL("filename", 'emp1'!$A$1 ) )+1, 9999)

您必须将范围更改为工作表。它将默认为工作簿。这很重要，因为您希望每个员工工作表都有一个 _sheetName 实例。现在对每个员工工作表执行相同操作。您可以使用以下方法加快速度姓名经理在里面公式标签。

完成此操作后，您可以返回到 _EmployeeSheetNames 列表，并在每个条目中将其从文本（如“emp5”）更改为公式='emp5'!_sheetName。它确保您拥有正确的名称，并且还可以确保如果工作表名称发生变化，您的下拉列表会知道。

注意：由于此方法使用了 CELL 函数，因此它也是易失性的，但它仅由已经是易失性的 INDIRECT 使用，因此这不会对计算时间做出任何新的贡献。

Answer 1