☰

如何将一整列公式转换为一列包含相同公式的稍加修改的版本的新列？

tag-icon • tag-icon microsoft-excel worksheet-function
如何将一整列公式转换为一列包含相同公式的稍加修改的版本的新列？

有问题的 Excel 文件的 A 列包含加利福尼亚州的 58 个县，B 列中包含度量“Col 1”。B 列引用了另一个 Excel 文件中的数据，因此该列包含如下公式：

='[CA 237 Time Series.xlsx]ALPINE'!$BM$26

='[CA 237 Time Series.xlsx]CALAVERAS'!$BM$26

='[CA 237 Time Series.xlsx]LOS ANGELES'!$BM$26

有问题的 Excel 工作表。

依此类推。B 列的所有 58 行看起来都是这样的。

现在问题来了。我想转换这些公式，使它们引用引用的工作表 [CA 237 时间序列] 的 A 列，并将其粘贴到 C 列中。这是因为 [CA 237 时间序列] 中的 A 列引用日期列，这是我需要检查的内容。当前文件 B 列中的所有项目（不是 CA 237 时间序列）都应转换为：

='[CA 237 Time Series.xlsx]ALPINE'!$A$26

='[CA 237 Time Series.xlsx]CALAVERAS'!$A$26

='[CA 237 Time Series.xlsx]LOS ANGELES'!$A$26

然后粘贴到 C 列。

为此，我发现了 SUBSTITUTE 公式。我可以这样做：

=SUBSTITUTE(FORMULATEXT(B2), "BM", "A")

结果将公式转换为：

='[CA 237 Time Series.xlsx]ALPINE'!$A$26

但它似乎将结果作为字符串返回给我也就是说，SUBSTITUTE 函数实际上返回“='[CA 237 Time Series.xlsx]ALPINE'!$A$26”作为字符串，而不对其进行求值。我不知道如何让它求值新公式。我怎样才能将公式转换为相同公式的修改版本并对其进行求值？

最后，Excel 文件应该是这样的。改变 B 列中的公式并将其放入 C 列后，它应该对其进行评估并返回一个日期（在本例中为 2022 年 7 月）。

答案1

您可以通过多种方式来实现这一点，但简单方法Find and Replace是最简单的。

在这种情况下，搜索字符串$CE$并将其替换为字符串$A$。

Excel 将进行替换并将公式保留为公式。

当不容易隔离需要替换的字符串时，这种方法就困难得多。当有两个元素需要替换，并且第一个元素留下的公式有结构错误时，不可能使用这里适用的简单版本。但如果你遇到过这种情况，请询问，因为无论有多少步骤，它在概念上仍然很容易。

另一种方法是获取从所述方法中获得的文本字符串，然后查找共同元素。如果有，您可以一次完成整个范围。如果没有，那么逐个或子集进行，尽管这可能会很痛苦。

在您的情况下，[字符，或者更好的是，一对字符'[会跳到一处。由于存在有用的搜索字符串，请复制范围并Paste|Special|Values转换为字符串，而不是FORMULATEXT结果。（虽然可以Find使用单元格结果而不是公式，但无法查找和替换。在我的版本中不行。）最后，使用查找和替换功能查找该字符串...并将其替换为自身。因此，可以查找并替换整个单元格范围。

Excel 会立即将字符串转换为实际公式。即使单元格的格式为文本（现在仍然是），公式仍会起作用，您将看到结果而不是字符串。看到数字结果在左边缘对齐，格式为文本，对齐方式为常规，这当然很奇怪，但这是一种查看方法。

甚至可以使用 VBA 的即时窗口进行转换，但上述方法更适合随机用户。（确保范围内的单元格格式不是文本，然后在选定范围内键入或粘贴selection.value = selection.value到即时窗口中并按 Enter。）

答案2

正如@gns100在评论中所说，使用INDIRECT()，结合你已经找到的SUBSTITUTE。所以类似=INDIRECT(=SUBSTITUTE(FORMULATEXT(B2), "BM", "A"))

相关内容