Excel：由INDIRECT公式定位的列表范围 [英] Excel: list ranges targeted by INDIRECT formulas

问题描述

我们有几个非常大的Excel工作簿（几十个选项卡，每个MB，非常复杂的计算），许多数十个，也许数百个使用可怕的INDIRECT函数的公式。这些公式分布在整个工作簿中，并针对几个数据表来查找值。

We have a few very large Excel workbooks (dozens of tabs, over a MB each, very complex calculations) with many dozens, perhaps hundreds of formulas that use the dreaded INDIRECT function. These formulas are spread out throughout the workbook, and target several tables of data to look-up for values.

现在我需要移动数据的范围，这些公式到同一个工作簿中的不同位置。

Now I need to move the ranges of data that are targeted by these formulas to a different location in the same workbook.

（原因并不是特别相关，但是有趣的是我们需要在Excel Calculation Services并且一次加载每个相当大的表的延迟命中被证明是不可接受的高我们正在移动表在一个连续的范围，所以我们可以一次加载它们。）

(The reason is not particularly relevant, but interesting on its own. We need to run these things in Excel Calculation Services and the latency hit of loading each of the rather large tables one at a time proved to be unacceptably high. We are moving the tables in a contiguous range so we can load them all in one shot.)

有没有办法找到所有目前参考我们要移动的表的INDIRECT公式？

我不需要这样做在线。只要可靠的话，我会乐意采取需要4个小时运行的东西。

I don't need to do this on-line. I'll happily take something that takes 4 hours to run as long as it is reliable.

请注意.Precedent，.Dependent等方法只跟踪直接公式

Be aware that the .Precedent, .Dependent, etc methods only track direct formulas.

（另外，重写em 中的电子表格对我们来说不是一个选择）。

(Also, rewriting the spreadsheets in whatever is not an option for us).

谢谢！

推荐答案

您可以使用vba遍历整个工作簿（我已经包含了@PabloG的代码和@ euro-micelli）：

You could iterate over the entire Workbook using vba (i've included the code from @PabloG and @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（C4）替代每一次出现的indirect（D4）。如果您有更复杂的间接功能，您可以轻松地更换更换功能。性能不错，即使是更大的工作簿。

This example substitues every occurrence of "indirect(D4)" with "indirect(C4)". You can easily swap the replace-function with something more sophisticated, if you have more complicated indirect-functions. Performance is not that bad, even for bigger Workbooks.

这篇关于Excel：由INDIRECT公式定位的列表范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！