性能优化

下面会介绍一些方法优化表格的加载速度。

减少单元格

新建一个空白表格的时候,默认会创建一个 26 * 1000 单元格的表格,过多的单元格会降低表格的运行速度,即使是空白的单元格也一样,有的时候并没有完全使用到这么多的单元格,所以删除一些用不到的行或者列有助于提升性能。

建立一个单元格表格

在打开表格的时候,如果当前的工作表有很多内容,需要一些时间加载画面和计算函数的结果,这样就会增加表格的加载时间。建议建立一个单元格的表格,也就是一个表格里面什么东西都没有,只有一个空白单元格。

因为优先打开空白单元格的表格,里面没有内容加载的速度就会快一些,其他需要大量计算函数的表格就会延迟加载。

测试结果:常规的表格打开速度都在 10s 以上,空白表格打开速度不超过 4s。

将公式结果替换成文本

在表格中使用函数计算一些结果,或者获取一些数据后,可能就不再需要了。例如使用 GOOGLETRANSLATE 函数翻译一个文本,翻译后就不再需要它了,就可以把函数返回的结果仅粘贴值,替换成文本,这样每次打开表格就不会重新再计算或者获取数据了。

减少条件格式的使用

使用条件格式会额外增加表格的计算,同时也会降低一点运行速度。

引用指定范围

在引用单元格的时候,可能会直接使用 A:A,假设有 1000 行,也就是引用从 A1A1000 这个范围。如果实际用到的单元格没有那么多,也许只用到了 300 行,那么剩下 700 行空白单元格都被引用,这样会无意义的降低运行速度。所以可以缩小引用的范围,而不是一整列引用。

在一列相同长度的情况下,A1:A1000 的运行速度要比 A1:A 快,但是 A1:A 的运行速度要比 A:A 快。

测试结果:引用 A1:A70000 耗时 133ms,引用 A1:A 耗时 139ms,引用 A:A 耗时 263ms。

减少分表引用

分表引用的速度要比在同一个表格里面引用速度要慢一些。

测试结果:分表引用耗时 456ms,引用当前表格耗时 442ms。

尽可能地减少使用易失函数

NOW、TODAY、RAND 和 RANDBETWEEN,这些函数被称为易失函数。使用这些函数后,在表格内修改任意一个单元格后,会引起所有相关公式重新计算。

如果需要使用多个这些易失函数,建议设置一个值后,其他单元格来引用它。例如在 A1 单元格使用 NOW(),那么其他位置的单元格就可以使用 $A$1 来引用。

测试结果:一整列使用 NOW 函数耗时 671ms,第一行使用 NOW 函数,其他单元格引用 NOW 函数的值,耗时 668ms。

使用数组公式

如果一列里面有很多相同的公式,可以使用 ARRAYFORMULA 函数代替,这样运行的速度要快很多。

测试结果:使用 ARRAYFORMULA 函数耗时 128ms,不使用 ARRAYFORMULA 函数的结果耗时 308ms。

减少使用需要网络请求的函数

使用网络请求的函数:IMPORTDATA、IMPORTFEED、IMPORTHTML、IMPORTRANGE、IMPORTXML、GOOGLEFINANCE、GOOGLETRANSLATE 和 DETECTLANGUAGE。

使用 IMPORTRANGE 函数从另外一个工作表索引数据的时候,如果内容特别庞大,就会出现 #ERROR! 错误。可以使用多个 IMPORTRANGE 来索引数据。

使用 {xxx; xxx} 建立数组把多个函数合并起来。比如第一个 IMPORTRANGE 函数设置 A1:A3000 范围,那么第二个 IMPORTRANGE 函数就可以设置 A3001:A6000 范围。

={IMPORTRANGE("xxxx", "工作表1!A1:A3000");
IMPORTRANGE("xxxx", "工作表1!A3001:A6000")}

使用 IF 函数管理函数执行

IF(A1 = "", "", 被执行的函数)

这样可以跳过一些空值而不执行函数,避免了没必要的性能浪费,特别是使用一些网络请求或者 VLOOKUP 等比较影响性能的函数,效果会比较明显。

测试结果:使用 IF VLOOKUP 耗时 326ms,使 用VLOOKUP 耗时 438ms。

使用这种思路做一个开关,用来控制函数的运行。用在一些不是太经常用到,但是又偶尔用到的函数,这样只有在打开开关的时候才会执行,可以大幅减少性能的影响。

创建一个更小的辅助表格

如果需要使用 IMPORTRANGE 函数获取一个范围的数据,但是数据里面有些空白单元格或者重复的内容,这样就会造成没必要的性能浪费。

使用 ARRAY_CONSTRAIN 函数创建一个静态的辅助表。

例子:ARRAY_CONSTRAIN(A1:F13, 2, 4)

或者使用 FILTER 函数创建一个动态的辅助表。匹配指定内容,排除空白单元格。

例子:FILTER(A:C, LEN(C:C))

也可以使用 UNIQUE 函数去除重复内容。

例子:UNIQUE(A:A)

还可以使用 COUNTA 函数计算单元格的范围,然后使用 IMPORTRANGE 函数引用一个动态的范围。

例子:IMPORTRANGE("xxxx", "工作表1!A1:A"&COUNTA(工作表1!A:A))

慎用 Apps Script

在进行大批量的数据处理时,特别是需要重复循环执行的数据,Apps Script 要比使用函数处理数据要快得多。

尽可能避免使用 Apps Script 自定义函数,因为的处理速度要远比原生的函数要缓慢很多。

使用数组引用或者数组输出

例如使用 VLOOKUP 函数从 C 到 F 列进行匹配,但是只需要索引第 4 列的值,那么引用中间的 D 和 E 列就造成了没有必要的性能浪费。

例子:VLOOKUP(J1, C:F, 4, 0)

使用数组进行引用,只引用 C 和 F 两列,索引第二列的值。

例子:VLOOKUP(J1, {C:C, F:F}, 2, 0)

测试结果:VLOOKUP 函数引用 C:F 耗时 824ms,VLOOKUP 函数引用 {C:C, F:F} 耗时 701ms。

如果要索引多个值,可以使用数组输出

例子:ARRAYFORMULA(VLOOKUP(J1, C:F, {2, 3, 4}, 0))

使用性能更佳的函数实现功能

在实现某些功能的时候,可能有多种函数或者方法都可以达到,可以选择性能最佳的函数来实现功能。

INDEX 函数和 MATCH 函数代替 VLOOKUP 函数

例子:INDEX(D:D, MATCH(F1, C:C, 0))

例子:VLOOKUP(F1, C:D, 2, 0)

因为 INDEX 和 MATCH 函数执行的速度要比 VLOOKUP 函数快一些。

测试结果:INDEX 和 MATCH 函数耗时 563ms,VLOOKUP 耗时 765ms

如果需要多个条件匹配,使用 SUMPRODUCT 函数速度要比 SUMIFS 和 COUNTIFS 函数快一些。反之,如果没有多个条件匹配,那么 SUMPRODUCT 函数的性能不如 SUMIFS 或者 COUNTIFS 函数执行速度快。

Excel表格, Google Excel Excel 一点通