|
以前我们介绍过如何优化Excel的公式，那时的分析是基于“旧”Excel版本。随着Excel的使用方式发生了变化，也有不同的优化思路和方法。所以，这次重新整理了一系列优化的内容。 注：主要思路还是一致的。 很多人会遇到Excel文件运行速度太慢的问题，这时大部分人都会归咎于数据量太大，或者公式太多。 其实，这都不是运行速度太慢的根本原因。 速度慢在四个方面有表现：
数据量很大时，会影响前三个场景的速度，这是确定的。但是不会影响日常操作的速度。 但是很多时候数据量并不大，却导致运行速度慢，这就是由于公式计算速度慢导致的。这种情况下，这四个方面的速度都会变慢。 很多人就会说是公式太多造成的。 但是数据量不是随便就能减少的！ 那些公式也都是有用的，不能随便删除！ 难道我们就没有办法了吗？ 当然不是！ 因为公式多和数量大并不必然导致计算速度慢。我们可以选择最有效的公式，从而提高计算速度。 在优化公式时，要牢记一点： 影响Excel公式计算速度的核心要素是公式中所引用的单元格数量。 注意，不是数据量。 这个数量越大，公式的速度越慢。 下面我们可以通过一个简单的实例看源数据数量，公式引用单元格数量以及计算速度之间的关系。 注：这里需要用到一个时间分析工具。详细见Excel表格为什么那么慢以及怎么解决（一） 这里我们用下面的数据：
其中B，C两列是原始数据，总共20000行。需要在D列计算滚动累计销量。 首先使用公式： 然后往下拖拽填充。 然后点击“开始分析”，得到这些公式的计算时间： 接近1秒的时间了。 就这么一个简单的求和公式，用了1秒的时间。算是相当慢了。而且数据量并不大，只有20000行。 我们来看引用的单元格数量。 第一个公式，SUM($C$6:C6)，引用了1个 第二个公式，SUM($C$6:C7)，引用了2个 ...... 第20000个公式，SUM($C$6:C20005)，引用了20000个 所以，总共引用单元格数量就是： 简单计算就得到一个数值：接近2亿个单元格。 数量太多了。 为了提高速度，我们换一个方法计算这一列： 在D6单元格中使用公式： 然后在D7单元格中使用公式： 然后往下拖拽填充。 点击“开始分析”，得到计算时间为0.018秒。 这是一个差不多50多倍的差距。 再来看一下这个方法中引用了多少单元格： 约等于40000个单元格。 单元格数量的下降也导致了计算速度的快速下降。 能否继续提高速度呢？ 还可以。 这个问题我们可以通过SCAN函数来完成！使用如下公式： 这个公式可以得到整列的累计结果。 分析这个公式的时间，只有0.012秒左右。 而这个公式中只不过就是对C6:C20005这20000单元格进行循环而已，因此，引用单元格数量就是20000。 这个实例的三个解决方案清晰的说明了引用单元格数量是如何影响计算速度的。 考虑下面的两个表：
要为左表中的每行数据添加value列，其数值在右表中，假设右表数量比较大，超过40000行，你设计一个公式尽可能快的完成这个计算过程。 详细解释请看视频  加入E学会，永久免费学习更多Excel应用技巧 http://www./portal/learn/class_list Excel+Power Query+Power Pivot+Power BI 自定义函数 底部菜单：知识库->自定义函数 面授培训 底部菜单：培训学习->面授培训 Excel企业应用 底部菜单：企业应用 也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。 |
|
|
