REDUCE+MAKEARRAY循环更新二维数组的一种方法（完结）

shaowu459

本帖主要使用两个小例子来说明REDUCE函数和MAKEARRAY函数配合来逐个或批量更新二维数组某些值的一种方法，公式比较长，但优点在于思路简单，维护方便。

REDUCE函数可以实现循环的目的，并记录更新后的数组；MAKEARRAY函数因其特性，本身就有行坐标和列坐标信息，因此可以索引二维数组中任意位置的值，同时可以更新二维数组指定位置的值。

shaowu459

第一个问题是群里做的题目：计算修复地面空洞要耗费多少单位水泥。


如下图所示，A1:T10单元格区域只有数字1和0，其中0代表空洞，修补一个空洞需要1单位水泥。第一行代表地面，需要修补地面所有的空洞，修复方法为往空洞里倒水泥，但是水泥倒下后如果有相连的空洞（也就是相连的0），水泥会顺着相连的0在上下左右各个方向流动。问修补完空洞需要耗费多少水泥，结果为下图黄色区域0的个数（所有与地面空洞连接的空洞均被填满）。

shaowu459

先说明一下分析思路：

由于连接的空洞上下左右都有，为了避免陷入循环，可以挨个标记空洞。地面上的空洞首先做标记，例如将第一行的所有0变成2，然后再多次循环，每次循环时判断，如果当前的数组元素是0，就看这个0上下左右有没有2，如果有2，也就是和地面空洞相连的空洞，把这个元素也标记成2。这样循环多次之后，所有和地面0相连的0就都被标记成了2，最后再判断2的个数即可。

参考公式如下：

1. =SUM(N(REDUCE(A1:T10,A1:T10,LAMBDA(x,y,IF(MAKEARRAY(10,20,LAMBDA(m,n,IF(INDEX(x,m,n)=0,OR(m=1,IFERROR(INDEX(x,m+{1,-1,0,0},n+{0,0,1,-1}),)=2)))),2,x)))=2))

复制代码

shaowu459

上楼的公式简要说明如下：
=SUM(   求结果数组中=2的个数
    N(  将逻辑值转化为1和0
        REDUCE(
            A1:T10,   初始数组
            A1:T10,   循环足够多的次数，二参的数量为循环的总次数
            LAMBDA(x, y,
                IF(   判断MAKEARRY返回数组，如果是TRUE，则更新x中对应位置的值为2，否则保留x中的值不变。
                    MAKEARRAY(
                        10,   创造一个10行20列的数组
                        20,   创造一个10行20列的数组
                        LAMBDA(m, n,
                            IF(
                                INDEX(x, m, n) = 0,   如果提取x中第m行第n列的值是0，才进行后续判断
                                OR(  第1行的0或者其他行的0上下左右有一个2，意味着是和一层的空洞相连接的，需要更新x对应位置的值为2
                                    m = 1,   如果是第1行的空洞，肯定符合要求，需要标记为2   
                                    IFERROR(
                                        INDEX(x, m + {1, -1, 0, 0}, n + {0, 0, 1, -1}),   提取x中坐标[m,n]上下左右的值，判断是不是2                       
                                           ) = 2
                                  )
                            )
                        )
                    ),
                    2,  如果MAKEARRAY函数返回值是TRUE，则更新x对应位置值为2
                    x   否则保留x不变
                )
            )
        ) = 2     判断最终x的值是否等于2
    )
)

shaowu459

下面逐步演示一下公式运算的过程和结果：

首先，把REDUCE函数第二参数改成A1:A1：

1. =REDUCE(A1:T10,A1:A1,LAMBDA(x,y,IF(MAKEARRAY(10,20,LAMBDA(m,n,IF(INDEX(x,m,n)=0,OR(m=1,IFERROR(INDEX(x,m+{1,-1,0,0},n+{0,0,1,-1}),)=2)))),2,x)))

复制代码

因为第二参数只有一个值，所以也就是只循环一次，结果如下：


可以观察到，第一行的0都变成了2，因为公式中有m=1的判断，只要位于第一行，就把0变成2。

然后把REDUCE函数第二参数改成A1:B1，也就是循环两次，结果如下：

1. =REDUCE(A1:T10,A1:B1,LAMBDA(x,y,IF(MAKEARRAY(10,20,LAMBDA(m,n,IF(INDEX(x,m,n)=0,OR(m=1,IFERROR(INDEX(x,m+{1,-1,0,0},n+{0,0,1,-1}),)=2)))),2,x)))

复制代码

可以观察到，第一行所有2下面挨着的0都变成了2，其余的0值没有变化。因为循环到第2行的值时，判断0的上下左右是否有2，有2就意味着和一层的空洞相连，因此就将0变成2。

如此继续增加循环次数，可以观察每次都会将2紧挨着的0变为2：


以此类推，循环到达一定的次数时，所有和2挨着的0就都变成了0，也即所有连着一层空洞的空洞均已找到。

shaowu459

第二个问题是：如下B2:E5矩阵中，从左上角开始到右下角结束，各种路径中经过的数字合计最小值是多少。


这个是动态规划（DP）中可能用到的一种方法，根据条件更新二维数组，题目的具体说明详见Microsoft 365：动态规划（DP）基础的11楼，此处不再赘述。

shaowu459

参考公式如下：

1. =REDUCE(B2:E5,SEQUENCE(4*4)+3,LAMBDA(x,y,MAKEARRAY(4,4,LAMBDA(m,n,INDEX(x,m,n)+IF((m=INT(y/4))*(n-1=MOD(y,4)),IFS(m*n=1,,m=1,@INDEX(x,m,n-1),n=1,@INDEX(x,m-1,n),1,MIN(INDEX(x,m-{1,0},n-{0,1}))))))))

复制代码

shaowu459

解题思路简单分析如下：

根据DP帖子11楼的分析可知，到达第i行j列时经历数字的最小值=其左侧和上方累计数字最小值+数组i行j列的值。特殊情况在于第一行只能取左侧的值，第一列只能取上方的值。根据这种分析结果，可以使用REDUCE循环矩阵元素那么多次，每次循环时只更新矩阵中第i行第j列的一个值，更新完成后保留更新的矩阵，进入下一次循环。

在使用MAKEARRY判断需要更新的值时，需要判断参数的行列是否等于需要更新值的行列，如果原数据为单元格区域，可以利用其行列信息，如果是内存数组就不好识别了。为了方便，我们先创立一个从0开始的数组，其行数和列数与原矩阵一样大小：





这样方便在于，每个数字都包含了其所在行列信息，我们用INT和MOD即可获取对应值所在行列信息。

使用INT函数获得行信息：


使用MOD函数获得列信息：

shaowu459

公式简要说明如下：
=REDUCE(
    B2:E5,   原矩阵或数组
    SEQUENCE(4 * 4) + 3,  生成和原数组相同大小的矩阵，每个数字同时包含行、列信息
    LAMBDA(x, y,
        MAKEARRAY(
            4,  和原数组行数相同
            4,  和原数组列数相同
            LAMBDA(m, n,
                INDEX(x, m, n) +   因为无论如何都要添加当前行列的值，因此直接提取出来，
                    IF(
                        (m = INT(y / 4)) * (n - 1 = MOD(y, 4)),  每次循环只更新一个值，更新顺序是先行后列
                        IFS(
                            m * n = 1,  如果是最左上角单元格，需要加的数字就是0
                            ,
                            m = 1,  如果是第一行，
                            @INDEX(x, m, n - 1),  需要加的数字是x中当前行、左侧一列的值
                            n = 1,  如果是第一列
                            @INDEX(x, m - 1, n),  需要加的数字是x中当前列、上一行的值
                            1,   其他所有情况
                            MIN(INDEX(x, m - {1, 0}, n - {0, 1}))  需要加的数字是左侧和上方值中较小者
                        )
                    )
            )
        )
    )
)

shaowu459

以上REDUCE+MAKEARRAY并非解决对应问题的最优方法，实际问题可以有多种解决方案，此处只是提供一种理解相对简单的思路。