抵销实际上是缓慢的，还是仅仅是不稳定的？

问题描述

我在Excel中有一个表，它经常使用OFFSET来获取相对于另一列的列。这是允许我动态调整表大小所必需的。

例如，在MyTable的单元格D5中，我有一个如下公式-其中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函数比使用范围文字慢多少。基本上，我在灵活性和能够动态调整表的大小与更快的硬编码偏移量之间进行了权衡。

因此，通常情况下，抵消本身是否可能是纸张速度变慢的原因？我想没有，因为我猜这是一个只需将几个数字相加的O(1)操作。但我想不出该如何量化

推荐答案

慢将与由于偏移量(或其他挥发性公式)而重新计算的单元格数量成正比。由于它的波动性，它可能会变得非常慢。

假设范围A1：A100。假设我们想要从第2行开始的50个单元格。使用偏移量将为：

=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)

那种情况下需要什么都行。

公式的复杂性也很重要。如果您将索引和匹配混合在一起，依此类推，使用偏移量，然后拖动50000行的公式，这50k行将一直重新计算。也许您是在一个有100000行的表中进行匹配。我知道这很难，但应该避免挥发性配方。

在VBA端触发了WORKSHEET_Calculate事件，并且可能有基于该事件运行的代码。