之前我们已经详细介绍过Excel中的几个LAMBD伴侣函数:详细文章见下表:
Excel函数循环解决大问题:如何统计每月明星产品 - 继续讨论BYROW/BYCOL
这些函数提供了在Excel中使用函数进行循环的手段,为Excel函数式编程提供了循环控制结构。本文就从用循环解决实际问题的角度,详细分析这几个函数的适用场景和特点,并提出了几个实用技巧。
循环的规则
所谓循环,就是遍历区域(或数组)中的每个单元格(或元素),并按照一定的规则进行处理。
按照使用的规则分,Excel的函数中提供两种循环:
基于元素值定义的规则
这种规则只根据元素的值定义,比如对于元素a,使用规则IF(a > 0, a*1.2, 0)进行处理
基于索引定义的规则
这种规则只根据元素在区域(或数组)中的位置进行定义。比如,对于第r行第c列的元素,使用规则IF(AND(r>5, c>3), 1, 0)
这两种指定规则的方式需要使用不同的函数来实现。
MAP,REDUCE,SCAN,BYROW,BYCOL等函数都属于这一类。
MAP函数的作用是循环一个数组,按照给定规则处理数组的每个元素,其返回结果是处理后的元素组成的数组,结果数组与原数组等大。
左边的是原数组,右边的是结果数组,处理规则是MAP函数中的LAMBDA定义的。在这个循环中,只能知道循环到的每个元素的值,而根本无从知道当前元素的索引。
SCAN和REDUCE函数应用的方式是一样的,只是返回结果有所不同。
SCAN/REDUCE循环时同时携带一个累加器,所以这两个函数的第一个参数就需要指定累加器的初值,第二参数才是需要处理的数组或区域。
SCAN/REDUCE的作用是将循环到的元素按照给定的规则累加到累加器中。这个规则就是其中的LAMBDA函数所定义的。
SCAN函数会将累加的每一步都保存下来,作为结果数组的元素,所以返回的是一个数组,与原数组等大。
REDUCE函数不记录中间结果,只是返回累加到最后一个元素之后的结果。因此返回一个值,该值与SCAN函数数组的末尾元素是一样的。
在这个循环过程中,我们只能知道当前循环到的元素和累计器的值,也无从知道当前元素的索引。
BYROW函数是按照给定规则循环处理数组的每行元素,返回结果是一列数组。而BYCOL函数是按照给定规则循环处理数组的每列元素,返回结果是一行数组。
在循环中,只能知道每个数组的当前行或者当前列,对于其行号或列号也无从知道。
只有MAKEARRAY函数才可以针对位置(即索引)制定规则。
MAKEARRAY循环生成一个给定大小的数组,在指定规则的LAMBDA函数中,使用的正是当前行号和列号。
但是只有行号和列号的规则实际是没多大用处的,必须结合具体的数值才能返回作用。因此,MAKEARRAY函数使用的使用通常伴随其余的一个(或多个)数组:
例如,当前循环到第r行第c列,就去左边的伴随数组中取出对应的元素,经过处理后,返回右边的结果数组中。比如,上图可以使用下面的公式实现:
=LET(arr, A1:F6,MAKEARRAY(6,6, LAMBDA(r,c, INDEX(arr, r, c) +1 ))
在实际应用中,MAKEARRAY函数的伴随数组(左侧数组)或结果数组(右侧数组)经常是不同维度的。
比如,
左侧的数组是一个只有一行的数组,但是需要返回的却是一个n×m的数组,所以MAKEARRAY函数就需要进行n行m列的循环,这样我们就需要将循环到的索引r和c对应到左侧的索引c_0中,
这里可以使用一个通用的计算公式:
c_0 = (r-1) * cs +c
其中,cs就是m,即结果数组的列数,r和c代表循环到的行和列索引。
这样就可以使用下面的公式实现这个过程:
=LET(arr, A1:F6,c_0, (r-1) * cs +c,MAKEARRAY(rs,cs, LAMBDA(r, c, INDEX(arr, c_0) +1 ))
反过来的情形也经常出现:
左侧的数组是一个多行多列的数组,返回结果要求是1行m列的数组,因此MAKEARRAY的循环就是1行,m列,这时需要将循环到的索引c(r=1,可以不考虑),对应到左侧数组的索引r_0,c_0。
可以使用下面的通用计算公式:
rs = ROWS(arr)cs = COLUMNS(arr)r_0 = ROUNDUP(c/cs,0)c_0 = IF(MOD(c, cs) = 0, cs, MOD(c, cs))
其中,rs和cs是左侧数组的行数和列数,c对应循环到的索引号。
可以使用下面的公式完成这个循环:
=LET(arr, A1:F6,rs, ROWS(arr),cs, COLUMNS(arr),r_0, ROUNDUP(c/cs,0),c_0, IF(MOD(c, cs) = 0, cs, MOD(c, cs))MAKEARRAY(1,6, LAMBDA(r, c, INDEX(arr, r_0, c_0) +1 ))
在实际应用中,更常见的情况是需要同时使用元素值和索引设置规则,例如:
最左边是原始数据,我们可以使用SCAN函数生成滚动合计。这个循环不需要索引。但是要生成滚动平均(最右侧的结果表),就必须知道每个值对应的索引号了。
这里有一个简单的技巧,那就是将两种循环结合起来使用:
用MAKRARRAY循环生成结果,这样可以知道每个元素对应的索引号r和c
使用MAP/SCAN/BYROW/BYCOL等函数作为中间结果,用上面得到的r和c去取出对应的中间结果。
例如,上面的公式可以写成:
=LET( arr, A2:A11, running_total, SCAN(0, arr, LAMBDA(acc,a, a+acc)), row_nums, SCAN(0, arr, LAMBDA(acc,a, acc+1)), running_avg, MAKEARRAY(ROWS(running_total), 1, LAMBDA(r,c, LET( value, INDEX(running_total, r), id, INDEX(row_nums,r), IF(MOD(id,2) = 0, value/id, "") ) ) ), running_avg)
详细解释请看视频
联系客服