另一个视角看Excel中的MAP/REDUCE/SCAN等函数：详说Excel函数式编程中的循环控制结构

之前我们已经详细介绍过Excel中的几个LAMBD伴侣函数：详细文章见下表：

这些函数提供了在Excel中使用函数进行循环的手段，为Excel函数式编程提供了循环控制结构。本文就从用循环解决实际问题的角度，详细分析这几个函数的适用场景和特点，并提出了几个实用技巧。

循环的规则

所谓循环，就是遍历区域（或数组）中的每个单元格（或元素），并按照一定的规则进行处理。

按照使用的规则分，Excel的函数中提供两种循环：

基于元素值定义的规则
这种规则只根据元素的值定义，比如对于元素a，使用规则IF(a > 0, a*1.2, 0)进行处理
基于索引定义的规则
这种规则只根据元素在区域（或数组）中的位置进行定义。比如，对于第r行第c列的元素，使用规则IF(AND(r>5, c>3), 1, 0)

这两种指定规则的方式需要使用不同的函数来实现。

2. 根据元素值制定规则的循环

MAP，REDUCE，SCAN，BYROW，BYCOL等函数都属于这一类。

MAP

MAP函数的作用是循环一个数组，按照给定规则处理数组的每个元素，其返回结果是处理后的元素组成的数组，结果数组与原数组等大。

左边的是原数组，右边的是结果数组，处理规则是MAP函数中的LAMBDA定义的。在这个循环中，只能知道循环到的每个元素的值，而根本无从知道当前元素的索引。

SCAN/REDUCE

SCAN和REDUCE函数应用的方式是一样的，只是返回结果有所不同。

SCAN/REDUCE循环时同时携带一个累加器，所以这两个函数的第一个参数就需要指定累加器的初值，第二参数才是需要处理的数组或区域。

SCAN/REDUCE的作用是将循环到的元素按照给定的规则累加到累加器中。这个规则就是其中的LAMBDA函数所定义的。

SCAN函数会将累加的每一步都保存下来，作为结果数组的元素，所以返回的是一个数组，与原数组等大。

REDUCE函数不记录中间结果，只是返回累加到最后一个元素之后的结果。因此返回一个值，该值与SCAN函数数组的末尾元素是一样的。

在这个循环过程中，我们只能知道当前循环到的元素和累计器的值，也无从知道当前元素的索引。

BYROW/BYCOL

BYROW函数是按照给定规则循环处理数组的每行元素，返回结果是一列数组。而BYCOL函数是按照给定规则循环处理数组的每列元素，返回结果是一行数组。

在循环中，只能知道每个数组的当前行或者当前列，对于其行号或列号也无从知道。

3. 根据位置制定规则的循环

只有MAKEARRAY函数才可以针对位置（即索引）制定规则。

MAKEARRAY循环生成一个给定大小的数组，在指定规则的LAMBDA函数中，使用的正是当前行号和列号。

但是只有行号和列号的规则实际是没多大用处的，必须结合具体的数值才能返回作用。因此，MAKEARRAY函数使用的使用通常伴随其余的一个（或多个）数组：

例如，当前循环到第r行第c列，就去左边的伴随数组中取出对应的元素，经过处理后，返回右边的结果数组中。比如，上图可以使用下面的公式实现：

=LET(arr, A1:F6,MAKEARRAY(6,6,        LAMBDA(r,c,                INDEX(arr, r, c) +1        ))

4. 使用MAKRARRAY函数的两个技巧

在实际应用中，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        ))

5. 同时使用元素值和索引设置规则

在实际应用中，更常见的情况是需要同时使用元素值和索引设置规则，例如：

最左边是原始数据，我们可以使用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)

详细解释请看视频

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。