☰

如何使用可选字段来更改公式中的工作表引用？

tag-icon • tag-icon microsoft-excel worksheet-function
如何使用可选字段来更改公式中的工作表引用？

我有一个类似这样的公式

=B2 - IF((B2 - Single!$D$1) < Single!$A$3, (B2 - Single!$A$2 - Single!$D$1) * Single!$B$2 + Single!$C$2, ...

我正在使用标有“单一”的工作表上的查找表进行分析。我希望有一个用户可选择的字段（理想情况下是下拉菜单之类的东西，比如在单元格 S1 中），这样我就可以在不同的工作表之间切换。

例如，如果我的工作表选项是“Sheet1”、“Sheet2”和“Sheet3”，那么单元格 S1 中就会出现一个包含这些选项的下拉菜单，公式也会更新以反映选择。如果选择了“Sheet1”，则公式将是

B2 - IF((B2 - Sheet1!$D$1) < Sheet1!$A$3, (B2 - Sheet1!$A$2 - Sheet1!$D$1) * Sheet1!$B$2 + Sheet1!$C$2, ...

如果选择了“Sheet2”，则

B2 - IF((B2 - Sheet2!$D$1) < Sheet2!$A$3, (B2 - Sheet2!$A$2 - Sheet2!$D$1) * Sheet2!$B$2 + Sheet2!$C$2, ...

等等。

答案1

您必须使用该INDIRECT函数来创建公式，并使用下拉列表以便用户可以选择工作表。

两者都相当容易使用：

（1）下拉列表：

  • 创建一个包含您希望用户能够选择的值的范围；在您的例子中，这是一个包含工作表的列表。我们假设它们对用户来说足够有意义，因此您可以在列表中使用准确的拼写（但在本例中我们采用“Sheet1”等）。
  • 选择您想要放置下拉框的单元格（假设为 A1），转到数据 > “数据验证”，列表，然后选择您创建的值列表（或范围）。

（2）现在INDIRECT在公式中使用该函数（为了简单起见，我只取公式的一小部分，但当然，在公式中使用 Sheet1! 的地方都必须使用该函数）：

而不是B2 - Sheet1!$D$1，而是使用B2 - INDIRECT(CONCATENATE(A1;"!$D$1"))。
（该CONCATENATE函数从单元格 A1 中获取字符串“Sheet1”，并将其与硬编码文本相结合以提供有效的单元格引用，然后使用该INDIRECT函数对其进行评估；CONCATENATE您还可以使用简写与符号运算符来代替&；另请注意，该函数CONCAT已从 Excel 2016 开始重命名，但CONCATENATE在可预见的未来仍将有效以保持向后兼容性）。

相关内容