我们有一些非常大的 Excel 工作簿(数十个选项卡，每个超过 1 MB，非常复杂的计算)，其中包含许多甚至数百个使用可怕的 INDIRECT 函数的公式。这些公式分布在整个工作簿中，并针对几个数据表来查找值。
现在我需要将这些公式所针对的数据范围移动到同一工作簿中的不同位置。
(原因不是特别相关，但本身就很有趣。我们需要在 Excel Calculation Services 中运行这些东西，并且一次加载每个相当大的表的延迟时间被证明是不可接受的高。我们正在连续范围内移动表格，因此我们可以一次性加载它们。)
有没有什么方法可以找到当前引用我们要移动的表的所有 INDIRECT 公式？
我不需要在线执行此操作。只要它可靠，我会很乐意运行需要 4 小时才能运行的东西。
请注意，.Precedent、.Dependent 等方法仅跟踪直接公式。
(另外，以我们无法选择的方式重写电子表格)。
谢谢！
您可以使用 vba 迭代整个工作簿(我已包含来自 @PabloG 和 @euro-micelli 的代码)：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| Sub iterateOverWorkbook()
For Each i In ThisWorkbook.Worksheets
Set rRng = i.UsedRange
For Each j In rRng
If (Not IsEmpty(j)) Then
If (j.HasFormula) Then
If InStr(oCell.Formula,"INDIRECT") Then
j.Value = Replace(j.Formula,"INDIRECT(D4)","INDIRECT(C4)")
End If
End If
End If
Next j
Next i
End Sub |
本示例将每次出现的 "indirect(D4)" 替换为 "indirect(C4)"。如果您有更复杂的间接函数，您可以轻松地将替换函数替换为更复杂的函数。性能还不错，即使对于更大的工作簿也是如此。
问："有什么方法可以找到当前引用我们要移动的表的所有 INDIRECT 公式吗？"
当我阅读它时，您想查看 INDIRECT 的参数内部以获取对感兴趣领域的引用。
OTTOMH 我会写 VBA 来使用正则表达式解析器，甚至是一个简单的 INSTR 来查找 INDIRECT(向前读到匹配)，然后 EVALUATE() 里面的字符串将其转换为实际地址，根据需要重复多次INDIRECT(...) 调用并将公式及其翻译转储到工作表中的两列。
我不确定 SO 的礼仪是关于提及作者所连接的产品的，但 OAK，Operis 分析工具包，一个 Excel 插件，可以用他们的单元格引用替换 INDIRECT 函数决心。然后，您可以使用 Excel 的审计工具来确定每个范围有哪些依赖项。
当然，您可以对工作簿的临时副本执行此操作。
更多在
-
http://www.operisanalysiskit.com/oakpruning.htm
-
http://www.operisanalysiskit.com/help/2007/index.html?oakconceptpruning.htm
考虑到这个问题的年龄，您很可能已经找到了替代解决方案或解决方法。
Unfortunately, the arguments of
INDIRECT are usually more complex than
that. Here's an actual formula from
one of the sheets, not the most
complex formula we have:
=IF(INDIRECT("'"&$B$5&"'!"&$O5&"1")="","",INDIRECT("'"&$B$5&"'!"&$O5&"1"))
hm，您可以编写一个简单的解析器，忽略大部分字符，只查找相关部分(在此示例中："A..Z"、"0..9" 和 "！ :" 等)，但如果 "indirect" 中的参数是函数，你会遇到麻烦。
也许更安全的方法是在第三张纸上打印每次出现的"间接"。然后，您可以添加所需的输出并编写一个小型搜索和替换程序来写回您的更改。
If you"get" every cell in a huge
spreadsheet you might end up needing
monstrous amounts of memory. I am
still willing to try and take that
risk.
PabloG 选择使用范围的方法是可行的方法(将其添加到我的原始代码中)。速度相当不错，尤其是在检查当前单元格是否包含公式时。显然，这完全取决于您的工作簿的大小。
你可以在 VBA 中使用这样的东西：
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| Sub ListIndirectRef()
Dim rRng As Range
Dim oSh As Worksheet
Dim oCell As Range
For Each oSh In ThisWorkbook.Worksheets
Set rRng = oSh.UsedRange
For Each oCell In rRng
If InStr(oCell.Formula,"INDIRECT") Then
Debug.Print oCell.Address, oCell.Formula
End If
Next
Next
End Sub |
你可以添加代码来代替 Debug.Print 来满足你的口味
