Excel整列外部引用公式批量修改后如何转为可求值公式？

嗨，我来帮你搞定这个问题！你已经找对了思路用SUBSTITUTE修改公式文本，但确实因为返回的是字符串格式，Excel不会自动把它当成公式计算。这里有几个实用的方法，你可以根据自己的Excel版本和需求来选：

方法一：查找替换法（所有Excel版本通用，最简单） #

这个方法分三步，操作起来超直观：

• 先在C2单元格输入你的公式：=SUBSTITUTE(FORMULATEXT(B2), "BM", "A")，然后下拉填充整列，这时候C列显示的都是修改后的公式文本（比如='[CA 237 Time Series.xlsx]ALPINE'!$A$26）。
• 选中整个C列，按Ctrl+C复制，然后右键点击C列，选择粘贴为值，把公式结果转换成纯文本。
• 按Ctrl+H打开「查找和替换」对话框：在「查找内容」和「替换为」里都输入=，点击「全部替换」。这时候Excel会把这些文本重新识别为公式，自动计算出对应的日期值。

方法二：用INDIRECT函数（适合Excel 2013及以上） #

如果不想手动操作查找替换，可以用INDIRECT函数直接将修改后的文本引用转为可求值的公式，不过要注意目标文件[CA 237 Time Series.xlsx]必须处于打开状态，否则会返回错误。公式如下：

=LET(newFormula, SUBSTITUTE(FORMULATEXT(B2), "BM", "A"), INDIRECT(MID(newFormula, 2, LEN(newFormula)-1)))

解释一下：MID(newFormula, 2, LEN(newFormula)-1)是把公式文本开头的=去掉，因为INDIRECT只需要引用地址文本；然后INDIRECT就会识别这个引用并返回对应单元格的日期值。直接下拉填充整列即可。

方法三：宏表函数EVALUATE（适合旧版Excel） #

如果你的Excel版本比较老，没有LET函数，可以用宏表函数EVALUATE，但需要注意文件要保存为启用宏的格式（.xlsm）：

• 选中C2单元格，点击「公式」选项卡→「定义名称」，随便取个名字比如EvalMyFormula，在「引用位置」输入：

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

• 点击确定后，在C2单元格输入=EvalMyFormula，下拉填充整列就能得到求值后的日期了。

备注：内容来源于stack exchange，提问作者Andrew