Excel索引与偏移/间接-我不明白为什么偏移/间接在这种情况下更快
我设置了一个工作簿，其中第一个选项卡包含一个参数列表。每一列都是不同实例的一组参数。
然后，我有一个实例的模板表，用户复制了这个模板。模板所做的事情之一就是动态地从第一个工作表中提取参数。用户输入一个列号(1到n)，并从要在该实例上使用的第一个表中提取该列的参数。然后，该实例使用这些参数运行数十万行计算。
参数表
示例实例
我的问题似乎与波动性有关。对于大量实例，如果使用偏移量或间接方法获取参数，则工作簿中任何单元格中的任何更改都会导致在每个实例表上重新检索参数，因此每个工作表每次都会重新计算，每次更改时冻结工作簿约3秒。
我想我可以用索引来减少这种情况。每个工作表的参数引用包含该参数的第一个工作表上的行，要从其中提取的列编号作为索引参数。这解决了导致重新计算的任何更改的问题。现在，只有参数范围的更改才会导致重新计算，但由于某种原因，情况要糟糕得多。
现在，更改工作簿中的随机单元格不再会导致整个任务在重新计算时冻结3秒，但是更改参数范围内的相关单元格将导致每个工作表重新计算，并花费大约10秒时间。为什么这个方法要慢得多。从技术上讲，它应该和以前一样，只有在相关参数被更改时才会这样做。
是否有任何方法将其设置为:当首页上的参数发生更改时，只有受该参数影响的工作表才能重新计算？
解决方案
我考虑了一个涉及VBA的复杂解决方案，因此复制模板实例可以监视其“实例号”单元格。当它被更改时，VBA代码可以将相应的参数复制到工作表中，并在这里硬复制值。我还需要VBA监视主要参数表上的更改事件。如果发生任何更改，则必须检查列号，遍历所有模板，如果引用该实例号，则必须重新复制值。出于将VBA排除在方程之外的通常原因，我希望避免使用此解决方案，但如果无法使excel重新计算更智能地处理参数更改，则可能是必要的。
回答 2
Stack Overflow用户
发布于 2011-06-27 23:06:32
这个问题是由使用Index函数将Param #值填充到实例表中引起的。
当在任何实例行上更改Param 1在Main中的值时，范围将被标记为已更改。由于每个实例表在其参数1值查找中引用此范围，因此所有param 1值都被标记为已更改。然后，在所有工作表上引用Param 1的所有Param 1公式都被标记为已更改。
不知道这会对工作簿设计产生什么影响，但请考虑将索引查找更改为直接单元格引用。例1单元格D3：=Main!B2
在准备这个答案时，我设置了一个带有工作簿事件的文本，以报告工作表重新计算。
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
MsgBox "Calc Sheet " & Sh.Name
End Sub我用Index、偏移量和其他方法做了一些关于各种查找的实验，但是找不到一个没有让所有的纸张重新计算的方法(不是彻底的搜索，所以其他的可能能够提供解决方案)。我发现唯一没有导致所有工作表计算的是直接单元格引用。
用于帮助设置或维护实例表参数引用的VBA例程可能是正确的.
Stack Overflow用户
发布于 2011-06-27 14:11:25
下面是一个仍然有效的旧Excel宏技巧：
选择要recalculate
- Use的所有单元格替换函数用
=
- This替换
=，只会强制这些特定的单元格到recalculate.
很容易将其记录为宏并定期执行。
https://stackoverflow.com/questions/6496609
复制
腾讯云开发者
