Excel：列出由INDIRECT公式指定的范围
我们有一些非常大的Excel工作簿(数十个选项卡，每个选项卡一个MB以上，非常复杂的计算)，其中包含数十个，也许数百个使用可怕的INDIRECT函数的公式。这些公式分布在整个工作簿中，并针对数个数据表以查找值。
现在，我需要将这些公式所针对的数据范围移动到同一工作簿中的其他位置。
(原因并不特别相关，但其本身很有趣。我们需要在Excel Calculation Services中运行这些操作，并且一次加载一个相当大的表的延迟造成的负载过高被证明是无法接受的。我们正在移动表格在一个连续的范围内，因此我们可以一次加载它们。)
有什么方法可以找到当前引用我们要移动的表的所有INDIRECT公式？
我不需要在线进行此操作。只要它可靠，我将愉快地花一些时间来运行4个小时。
请注意，.Precedent，.Dependent等方法仅跟踪直接公式。
(此外，以我们无法选择的方式重写电子表格)。
谢谢！
解决方案
回答
我们可以使用vba遍历整个工作簿(我已经包含了@PabloG和@ euro-micelli的代码)：
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)"。如果我们具有更复杂的间接功能，则可以轻松地将替换功能替换为更复杂的功能。即使对于更大的工作簿，性能也并不差。
回答
我们可以在VBA中使用类似的方法：
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外，我们还可以添加适合自己口味的代码
回答
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&"'!"&$O5&"1")="","",INDIRECT("'"&$B&"'!"&$O5&"1"))
嗯，我们可以通过忽略大多数字符并只查找相关部分(在此示例中为" A..Z"，" 0..9"和"！："等)来编写简单的解析器，但是我们将如果"间接"中的参数是函数，则会遇到麻烦。
也许更安全的方法是在第三张纸上打印每次出现的"间接"事件。然后，我们可以添加所需的输出并编写一个小的搜索和替换程序，以将所做的更改写回。
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选择使用范围的方法就是解决方法(将其添加到我的原始代码中)。速度非常好，特别是如果我们检查当前单元格是否包含公式。显然，这全都取决于工作簿的大小。
回答
问："有什么方法可以找到当前引用我们要移动的表的所有INDIRECT公式吗？"
在阅读本文时，我们想查看INDIRECT的参数以查找感兴趣的区域。
OTTOMH我会写VBA来使用正则表达式解析器，甚至是简单的INSTR来找到INDIRECT(向前读到匹配的)，然后EVALUATE()里面的字符串将其转换为实际地址，并根据需要重复多个INDIRECT (...)调用并将公式及其转换结果转储到工作表中的两列中。
回答
我不确定SO涉及到与书写器连接的产品有什么礼节，但是OAK，Operis Analysis Kit(Excel加载项)可以通过解析的单元格引用替换INDIRECT函数。然后，我们可以使用Excel的审核工具来确定每个范围具有哪些依存关系。
当然，我们将对工作簿的临时副本执行此操作。
更多内容
- http://www.operisanalysiskit.com/oakpruning.htm
- http://www.operisanalysiskit.com/help/2007/index.html?oakconceptpruning.htm
鉴于这个问题的年代久远，我们可能已经找到了替代解决方案或者解决方法。
