Excel 索引与偏移/间接 - 我不明白为什么在这种情况下偏移/间接更快

Question

我设置了一个工作簿，其中第一个选项卡包含参数列表。每列是不同实例的一组参数。

然后我有一个实例的模板表，用户制作了这个模板的许多副本。模板所做的其中一件事是动态地从第一张表中提取参数。用户输入一个列号(1 到 n)，然后从要在该实例上使用的第一个工作表中拉出该列参数。然后，该实例使用这些参数运行了数十万行计算。

参数表



示例实例



我的问题似乎与波动性有关。对于大量实例，如果我使用偏移量或间接获取参数，工作簿中任何单元格的任何更改都会导致在每个实例表上重新检索参数，因此每个表每次都会重新计算，每当进行更改时，将工作簿卡住约 3 秒钟。

我想我可以通过使用 Index 来减轻这种情况。每个工作表的参数都引用包含该参数的第一个工作表上的行，并将要从中提取的列号作为索引参数。这解决了导致重新计算的任何更改的问题。现在只有参数范围的变化会导致重新计算，但由于某种原因，情况要糟糕得多。

现在，更改工作簿中的随机单元格不再导致整个事物在重新计算时卡住 3 秒，但更改参数范围内的相关单元格将导致每个工作表重新计算，并且需要大约 10 秒。为什么这个方法这么慢。从技术上讲，它应该和以前做同样的事情，只是当相关参数发生变化时。

有没有办法设置它，以便在更改前面板上的参数时，只有受该参数影响的工作表才会重新计算？

解决方案

我考虑过一个涉及 VBA 的复杂解决方案，因此复制模板实例会监视其“实例编号”单元格。当它发生变化时，VBA 代码可以将相应的参数复制到工作表中，并将值硬拷贝到这里。我还需要 VBA 监视主参数表上的更改事件。当有任何改变时，它必须检查列号，遍历所有模板，如果引用了该实例号，则重新复制值。出于将 VBA 排除在等式之外的常见原因，我想避免使用此解决方案，但如果无法使 excel 重新计算更智能地更改参数，则可能有必要。

Answer 1

该问题是由使用 Index 函数将 Param # 值填充到 Instance 表上引起的。

当您更改 Param 1 的值时在 Main在任何实例行上，范围都被标记为已更改。由于每个实例表都引用此范围进行参数 1 值查找，所有 param 1值被标记为已更改。然后全部Iteration #引用 Param 1 的公式在所有工作表上都标记为已更改。

不知道这会对您的工作簿设计产生什么影响，但请考虑将索引查找更改为直接单元格引用。例如，在实例 1 单元格 D3 上:=Main!B2
在准备这个答案时，我设置了一个带有工作簿事件的文本来报告工作表重新计算

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "Calc Sheet " & Sh.Name
End Sub

我使用 Index、Offset 和其他方法对各种查找进行了一些实验，但找不到不会导致所有工作表重新计算的实验(不是详尽的搜索，因此其他人可能能够提供解决方案)。我发现唯一没有导致所有工作表计算的是直接单元格引用

帮助设置或维护实例表参数引用的 VBA 例程可能需要...