是否可以使用Excel函数评估函数的结果? [英] Is it possible to use Excel functions to evaluate the result of a function?
问题描述
我在I2中有一个公式:
= "=" & "'" & $H2 & $I2 & E2 & "'!" & "$C$2"
它结合了目录，文件名，工作表名称和单元格位置.
It combines the directory, filename, sheet name, and cell location.
现在，我想在J2中评估该公式的结果.是否可以不使用VBA或仅使用少量VBA来这样做?我需要能够将I2填充为I1000，我个人更喜欢非VBA方法.
Now I'd like to evaluate the result of that formula in J2. Is it possible to do so without VBA or with just a bit of VBA? I need to be able to fill down I2 to I1000 and I personally prefer a non-VBA approach.
例如，假设I2的结果为:
='R:\20180220\[Test.xlsb]'!$C$2
是否有一种方法可以真正显示J2中该单元格的值?请注意，工作簿Test.xlsb已关闭，不会打开(因为我有成千上万个工作簿，无法打开所有工作簿).
Is there a way to actually show the value of that cell in J2? Note that workbook Test.xlsb is closed and won't be opened (because I have thousands of workbooks and cannot open all of them).
我在VBA中尝试了EVALUATE(Range.Formula)和ExecuteExcel4Macro()，并尝试使用UDF包装它们，但是都失败了.
I tried EVALUATE(Range.Formula) and ExecuteExcel4Macro() in VBA, and tried to wrap them with a UDF, but both failed.
推荐答案
这可能对您有用.
-
像当前文章一样创建公式.请注意，帖子中的结果缺少对选项卡/工作表的引用 ---您可能需要更新公式以包括工作表.我在测试中使用的公式为
= "=" & "'" & $PATH & $FILE & $TAB & "'!" & "$C$2"(与您的公式相同，但为$ TAB添加了一个字段).
Create the formulas as in your current post. Note that the result in your post lacks a reference to a tab/worksheet --- you likely need to update your formula to include a worksheet. The formula I used in my tests is
= "=" & "'" & $PATH & $FILE & $TAB & "'!" & "$C$2"(the same as yours but with a field added for the $TAB).
复制/粘贴特殊值的结果.这将= "=" & "'" & $PATH & $FILE & $TAB & "'!" & "$C$2"替换为='C:\PATH\WORKBOOK\TAB!$C$2以提供正确的公式.令人讨厌的是，它们不会自动计算.
Copy/paste special values the results. This replaces = "=" & "'" & $PATH & $FILE & $TAB & "'!" & "$C$2" with ='C:\PATH\WORKBOOK\TAB!$C$2 to give the correct formulas. Annoyingly they do not calculate automatically.
要强制更新计算，F9似乎不起作用，因此选择结果并进行 mock 查找/替换，例如将$ C $ 2替换为$ 2加元.这不会更改公式，但会进行重新计算.您也许还可以保存，关闭然后重新打开工作簿.
To force an update of the calcs, F9 doesn't seem to work, so select the results and do a mock find/replace, say replace $C$2 with $C$2. This doesn't change the formulas but gets the recalc going. You might also be able to just save, close and re-open the workbook.
希望这会有所帮助.
这篇关于是否可以使用Excel函数评估函数的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
