excel - OFFSET 实际上是缓慢的还是不稳定的？

Question

我在 Excel 中有一个经常使用 OFFSET 的表格得到一列相对于另一列。这是允许我动态调整表格大小的必要条件。

因此，例如在单元格 D5 中的 MyTable ，我有一个这样的公式 - 其中 D$2 是表格的第一行

=INDEX(
    OFFSET(
        D$2,
        0,
        (- OffsetDistance ),
        ROWS(
            MyTable
        )
    ),
    MATCH(
        LARGE(
            OFFSET(
                D$2,
                0,
                (- 1 ),
                ROWS(
                    MyTable
                )
            ),
            ROW() -
                ROW(
                    MyTable[#Headers]
                )
        ),
        OFFSET(
            D$2,
            0,
            (- 1 ),
            ROWS(
                MyTable
            )
        ),
        0
    )
)

现在我听说它说“ OFFSET 很慢，因为它易变”。这就像说 RAND()是缓慢的;生成随机数并不慢，与排序列表或搜索子字符串相比没有什么 - 缓慢是因为工作表经常重新计算。

我想知道，使用 OFFSET 函数比使用 Range 文字慢多少。基本上，我在灵 active 和能够动态调整表格大小与更快的硬编码偏移之间进行权衡。

所以一般来说， 偏移量本身可能是工作表中缓慢的根源 ?我想不是，因为我猜它是 O(1)只需将几个数字相加即可的操作。但我想不出如何量化这个

Answer 1

由于偏移量(或其他 volatile 公式)，缓慢将与重新计算的单元格数量成正比。由于它的波动性，它可能会变得非常缓慢。
几年前我停止使用 OFFSET，因为在使用许多公式时差异会变得很大。相反，我使用非 volatile 的 INDEX。
假设范围 A1:A100。假设我们想要从第 2 行开始的 50 个单元格。使用 OFFSET 将是:

=OFFSET(A1,1,0,50,1)

或者:

=OFFSET(A2,0,0,50,1)

相反，我可能会使用这个:

=A2:INDEX(A2:A101,50)

或者:

=INDEX(A1:A101,2):INDEX(A2:A101,51)

在这种情况下需要什么。

公式的复杂性也很重要。如果您将 INDEX 和 MATCH 等与 OFFSET 混合，然后将该公式拖动到 50000 行，那么这 50k 行将一直重新计算。也许您在 100000 行的表中进行匹配。我知道这是一个拉伸(stretch)，但应避免使用易变的公式。
在 VBA 端，触发了 Worksheet_Calculate 事件，并且可能有基于该事件运行的代码。