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

Excel Index vs Offset/Indirect - I don't understand why Offset/Indirect is faster in this case

提问by Alain

I have a workbook set up where the first tab contains a list of parameters. Each column is a set of parameters for a different instance.

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

I then have a template sheet for one instance, and the user makes many copies of this template. One of the things the template does is dynamically pull over the parameters from the first sheet. The user enters a column number (1 to n) and that column of parameters is pulled from the first sheet to be used on that instance. The instance then has runs hundreds of thousands of rows calculations using those parameters.

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

Parameters Sheet

参数表

Sample Instance

示例实例

My problem appears to be related to volatility. With a large number of instances, if I use offset or indirect to get the parameters, any change in any cell in the workbook causes the parameters to be re-retrieved on each instance sheet, and so each sheet recalculates in its entirety every time, freezing the workbook for about 3 seconds whenever a change is made.

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

I thought I could lessen this by using Index. Each sheet's parameters reference the row on the first sheet containing that parameter, with the column number to pull from as the index parameter. This solved the problem of any change causing a recalculation. Now only changes in the parameter ranges cause a recalculation, but for some reason, it's much much worse.

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

Now, changing a random cell in the workbook no longer causes the whole thing to freeze for 3 seconds while it recalculates, but changing a relevantcell in the parameters range will cause each sheet to recalculate, and take about 10 seconds. Why is this method so much slower. It should technically be doing the same thing as before, just only when a relevant parameter is changed.

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

Is there any way to set this up so that when a parameter on the front sheet is changed, only the sheets that are affected by that parameterrecalculate?

有什么方法可以设置它，以便在更改前表上的参数时，仅重新计算受该参数影响的表？

Solutions

解决方案

I've considered a complex solution involving VBA, whereupon copying the template instance monitors its "Instance Number" cell. When it's changed, VBA code could copy the corresponding parameters to the sheet and hard copy the values here. I would also need VBA monitoring the change event on the main parameters sheet. When anything is changed, it would have to check the column number, iterate over all templates, and re-copying the values if that instance number is referenced. I want to avoid this solution for the usual reasons of keeping VBA out of the equation, but it might be necessary if there's no way to make excel recalculation smarter about changes to parameters.

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

采纳答案by chris neilsen

The issue is caused by using Index function to populate Param # values onto the Instance sheets.

该问题是由使用索引函数将参数 # 值填充到实例表中引起的。

When you change a value for Param 1in Mainon any Instance row, the range is marked as changed. Since every Instance sheet refers to this range for its parameter 1 value lookup, all param 1values are marked as changed. Then all Iteration #formula that refer to Param 1on all sheets are marked as changed.

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

Don't know what flow on effects this would have on you workbook design, but consider changing Index lookup to direct cell references. eg on Instance 1 cell D3: =Main!B2

不知道这会对您的工作簿设计产生什么影响，但请考虑将索引查找更改为直接单元格引用。例如在实例 1 单元格 D3 上：=Main!B2

In preparing this answer I set up a text with a workbook event to report sheet recalulations

在准备这个答案时，我设置了一个带有工作簿事件的文本来报告工作表重新计算

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

I did some experiments on various lookups using Index, Offset and others but could not find one that did not cause all sheets to recalculate (not an exhaustive search, so others may be able to offer a solution). The only one I did find that did not cause all sheets to calculate was the direct cell reference

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

A VBA routine to help set upor maintainthe Instance sheets parameter references may be in order...

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

回答by Joel Spolsky

Here's an old Excel macro trick that still works:

这是一个仍然有效的旧 Excel 宏技巧：

• Turn off automatic recalc
• Select all the cells that you want to recalculate
• Use the replace function to replace =with =
• This does nothing but force those particular cells to recalculate.

• 关闭自动重新计算
• 选择要重新计算的所有单元格
• 使用替换功能替换=为=
• 这只会迫使那些特定的单元格重新计算。

It's fairly easy to record this as a macro and do it regularly.

将其记录为宏并定期执行是相当容易的。