时间戳

首页 > 解决方案 > 数组公式中的Excel整列引用太慢，不尊重UsedRange？

arrays - 数组公式中的Excel整列引用太慢，不尊重UsedRange？

问题描述

我有一个包含许多数组公式的工作表，这些公式引用了另一个包含几行的小型源数据表。公式如下：

{=INDEX(Source!$F:$F,MATCH(1, (Source!$A:$A=$B6)*(Source!$B:$B=H$1),0))}

计算运行非常缓慢。当我用 1000 个单元格的范围替换整个列引用时，工作簿计算速度显着加快。

{=INDEX(Source!$F$1:$F$1000,MATCH(1, (Source!$A$1:$A$1000=$B6)*(Source!$B$1:$B$1000=H$1),0))}

我检查了 Source 表上的 UsedRange，它没问题，只有几行（使用 ctrl+end）。使用整列引用是很正常的做法，但在这里它并不能很好地工作，因为它不尊重 UsedRange。

找到一篇关于这个主题的文章： https ://fastexcel.wordpress.com/2015/12/12/excel-full-column-references-and-used-range-good-idea-or-bad-idea/

说的是：

但避免数组公式和 SUMPRODUCT 使用整列引用

但为什么？我无法理解 Excel 无法将数组公式的计算算法限制在使用范围内的原因？还有其他解决方法吗？

标签： arraysexcelexcel-formulaarray-formulas

解决方案

不确定您是否通过 VBA 构建它，但如果它只是您正在使用的公式，您可以尝试以下操作：

=INDEX(Source!$F$1:$F$1000,MATCH(1,INDEX((Source!$A$1:INDEX(Source!$A:$A,COUNTA(Source!$A:$A))=$B6)*(Source!$B$1:INDEX(Source!$B:$B,COUNTA(Source!$B:$B))=H$1),),0))

看起来很长，但它基本上是一种将列引用限制为最后使用的行的动态方式。顺便说一句，@Chris 在评论中完成的另一个很棒的技巧是使用实际的表格来代替，这应该会加快速度。

CtrlShiftEnter顺便说一句，由于第二个，您不必再输入此内容INDEX

推荐阅读