今天我们讨论循环类函数的计算速度问题。 循环类函数包括:
它们的详细介绍请参见上面的链接。 总体来说,这几个函数的性能完全依赖于循环的次数 。所以,对于数量非常大的数组或区域,使用函数循环的性能一定不好。此时,就需要算法出场了。不过我们今天不说这一方面的内容,有兴趣的朋友可以参考下面的链接: Excel函数式编程:这个简单的案例,非常直观的说明了优秀的算法是多么重要 双指针算法:Excel函数帮你轻松找到合计等于给定数值x的两个数值! 今天我们主要关注单纯的循环中我们需要注意什么问题。 如果我们对数据的处理都是一样的,不进行复杂操作,那么这几个函数的性能是差不多的。 比如MAKEARRAY函数, =LET( rr, 4000, cc, 1000, MAKEARRAY(rr, cc, LAMBDA(r,c, 1))) 延续我们之前的例子,生成这个巨大的数组(4000行,1000列),在我的计算机上需要1.5秒左右的时间, 我们使用MAP函数对其进行循环, =LET( rr, G2, cc, H2, a, MAKEARRAY(rr, cc, LAMBDA(r,c, 1)), MAP(a, LAMBDA(x, x))) 需要3.1秒左右的时间, 显然是生成数组用了一半的时间,执行MAP函数用了一半的时间。 SCAN和RECUDE函数也是相同的结果,不过REDUCE函数稍微快大约不到10%左右,应该是因为计算中间不需要保存数组的导致的。 BYROW和BYCOL就是另外的逻辑了, =LET( rr, G2, cc, H2, a, MAKEARRAY(rr, cc, LAMBDA(r,c, 1)), BYROW(a, SUM)) 这个公式基本上不花额外的循环时间, 这是因为它只需要进行4000次循环,循环次数明显少于其余的函数。 =LET( rr, G2, cc, H2, a, MAKEARRAY(rr, cc, LAMBDA(r,c, 1)), BYCOL(a, SUM)) 而BYCOL循环也基本上不花额外的时间, 因为循环次数更少,只有1000次。 细心的朋友一定会发现这里有所不同,BYCOL只循环1000次,但是仍然比BYROW循环4000次要慢一点。 我们暂且按下不表。 这时我们把用于计算的数组变小一些,否则计算时间会超长。 我们生成一个200行,100列的数组, =LET( rr, 200, cc, 100, a, MAKEARRAY(rr, cc, LAMBDA(r,c, 1)), a) 计算时间非常短, 下面我们使用MAKEARRAY对其进行循环,具体操作是使用INDEX函数生成一个一模一样的数组, =LET( rr, 200, cc, 100, a, MAKEARRAY(rr, cc, LAMBDA(r,c, 1)), MAKEARRAY(rr, cc, LAMBDA(r,c, INDEX(a, r, c)))) 这个计算导致原本不需要花费时间的操作,现在需要2.5秒左右的时间, 这一点是很糟糕的。你可能觉得这个时间似乎还可以接受(其实并不是),那么数据量稍微大一点也没什么, 比如,将数组变成260行,100列, 挺慢的,有点糟糕,但似乎不是太糟糕。 再增加两行看看, 太糟糕了! 这就导致如果是比较复杂的计算,我们只能处理非常小的数据集了。 好消息是如果我们将相同的数组放到单元格区域中,循环单元格区域的话,速度会大大提升! 现在我们将那个巨大数组(4000行,1000列)放到Sheet1的B2:ALM4001中,然后循环这个区域, =LET( rr, 4000, cc, 1000, b, Sheet1!B2:ALM4001, MAKEARRAY(rr, cc, LAMBDA(r,c, INDEX(b, r, c)))) 时间变快了, 按说,选择BYROW,还是BYCOL,完全由实际需要决定。 但是这中间是由比较大的差别的。(我们上面提到过) 先看下面的公式, =LET( rr, 4000, cc, 1000, b, Sheet1!B2:ALM4001, BYROW(b, SUM)) 就是简单的对数组各行汇总求和, 当然这个时间是很快的。 再看按列求和 =LET( rr, 4000, cc, 1000, b, Sheet1!B2:ALM4001, BYCOL(b, SUM)) 再看看计算时间, 已经变成一倍多了! 所以,从效率来说,我们应尽量使用BYROW函数。比如,可以通过前面良好的设计,将问题变成可以按行求和。
|
|