☰

一个单元格中的公式文本用作不同工作表上单元格中公式的参考

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
一个单元格中的公式文本用作不同工作表上单元格中公式的参考

工作簿将包含数十张工作表，每张工作表上的某个单元格中都有一个函数，该函数会根据该工作表上的查找次数返回标准化的纯语言短语。该函数可能如下所示：

="The start date was "&A1&" and resulted in "&B1

这些工作表中的每一张都在 A1 和 B1 中具有该工作表独有的值，因此公式中的单元格引用在所有工作表中都是相同的。这意味着每张工作表上的 A1 都是唯一的开始日期，而 B1 是每张工作表的唯一结果。

问题就在这里。公式需要在所有工作表中即时编辑，这就是为什么我想将其保存在一个名为的“主”工作表中参考在名为“主”单元中ref_plain 语言。

这里还有另一个问题。这个简单语言公式结果需要出现在每张工作表的哪个位置，在不同的工作表上是不同的。在工作表 1 上，简单语言字符串需要出现在 C3 中，但在工作表 2 上，它出现在 D6 中，并且这些位置可能也需要动态更改。我知道如何用 VBA 做到这一点，但想要一个非宏版本，因为用 VBA 更改每张工作表上的位置很麻烦。

这是不起作用的。在主单元中ref_plain 语言，我放置的文本不带 = 符号：

"The start date was "&A1&" and resulted in "&B1

在另一张纸上，我输入

="="&ref_plainLang

现在，该单元格中将显示完整的函数，就像输入的那样

="The start date was "&A1&" and resulted in "&B1

但一切仍是文本。

我已经广泛尝试过 Indirect()，因为这似乎是自然的解决方案，但无法让它工作。如果文本和参考文献的顺序不太可能改变，我会将函数完全添加到每个工作表中，将每个简单语言短语的部分作为块存储在工作表中的单独单元格中参考，类似这样：

[named: ref_plainLang1] A1 = The start date was 
[named: ref_plainLang2] B1 = A1
[named: ref_plainLang3] C1 =  and resulted in
[named: ref_plainLang4] D1 = B1

然后按照如下方式将它们串在 Sheet 1、Sheet 2 等上：

=ref_plainLang1&indirect(ref_plainLang2)&ref_plainLang3&indirect(ref_plainLang4)

如果只使用一个命名单元就可以完成，我会犹豫是否采用这种方法。

答案1

这是一个复杂的问题，但我认为你已经接近你需要的了。INDIRECT我不会使用 ，而是使用简单的定义名称，并在需要时将它们组合成短语，或者（正如你已经开始的那样）在一个地方构建短语，并为完成的部分分配一个定义的名称，然后引用该名称。

假设：工作表ref_plainlang定义了以下名称：

name              scope     refers to  
ref_startdate     workbook  =ref_plainlang!$A$1   
ref_resulted_in   workbook  =ref_plainlang!$B$1  
ref_text          workbook  =ref_plainlang$D$1

cell  value
A1    2018-01-01
B1    $100.00
D1    ="the start date was "&TEXT(ref_startdate,"yyyy-mm-dd")&" and resulted in "&TEXT(ref_resulted_in,"$0.00")

这样，在另一张表上，我可以输入：

="the start date was "&TEXT(ref_startdate,"yyyy-mm-dd")&" and resulted in "&TEXT(ref_resulted_in,"$0.00")

或者

=ref_text

并且都返回字符串the start date was 2018-01-01 and resulted in $100.00

答案2

因此，所需的公式会保留在一个地方，但您希望使用它的位置会因页面而异。

没问题。您只需在模板公式所在的地址中使用绝对引用即可。

如果页面的输入单元也移动的话，这会更加困难，特别是如果它们没有保持与需要该结果的单元相同的相对位置的话。

当然，困难在于你实际上在哪里做出这种绝对的引用？

答案是：在命名范围内。这是有原因的，我稍后会解释。

因此，创建一个命名范围，可能称为Result_ref_plainLang或ref_plainLang_Result。由于您已经命名了公式所在的单元格，因此您显然知道如何执行此操作。

相关：检查命名范围ref_plainLang并确保具有模板公式的单元格绝对寻址。因此，如果主表上的 C1 是 C1，则命名范围内的将是 $C$1。（如果公式没有在页面之间移动，则这并不重要。）

下一个问题是如何使公式起作用。考虑到即将出现的小困难和你似乎不是初学者的 Excel 知识，我敢打赌你知道我正准备EVALUATE()在结果命名范围（“NR”）中使用，而且我猜你试过了，但没有成功。

当然，它不会，因为你告诉它进行两级评估，而它在这方面并不擅长。如果你用函数包装主公式FORMULATEXT()，这会将其作为一个单一实体带入EVALUATE()函数，然后它就会工作：

=EVALUATE(FORMULATEXT(Sheet1!$C$3))

（如果模板公式位于 Sheet1 上的 C3 中）。

=EVALUATE(FORMULATEXT(ref_plainLang))

（使用命名范围名称）。

当然，如果您愿意，您可以在单个命名范围内完成所有这些操作。只需将公式直接包含在我放置命名范围的位置即可ref_plainLang。它仍然必须进入FORMULATEXT()函数内部。只需使用您已有的名称，将其更改Refers to为此公式。或者保留两个名称，这样您就可以更轻松地编辑它，因为当前 NR 除了模板公式或对它的引用之外什么都没有。（当然是绝对引用。）

顺便说一句，如果引用的单元格

每当页面的 A1 或 B1 发生变化时，它都会更新。这意味着不需要添加类似的内容+TODAY()*0。如果这对您来说很重要（例如，您的电子表格足够大以至于会影响计算速度），那么它不会像预期的那样不稳定INDIRECT()。

相关内容