递归替换REDUCE某类循环方式的一种方法（完结）

shaowu459

本帖示例比较简单，没有什么新鲜的公式，发本帖主要有以下三个出发点：

1）一是REDUCE函数在循环过程中没有达到条件就退出循环的机制，目前一般是用IF(条件判断，x，循环运算）的方式来实现当满足某些条件的时候，保留x不变，这种方式REDUCE还是要循环二参那么多次，使用递归可以达到满足条件即退出，减少循环次数。本帖仅就该点再次做个提示。

2）在Microsoft 365：X檔案大揭秘帖子里，我列示了我自己常用的一种解决问题的思路（在2楼具体说明），如果只用REDUCE循环，在堆积、提取、判断位置的时候会遇到些麻烦，增加公式的复杂成都，使用递归就可以很好的解决这个问题。标题所说，也就是使用递归代替这种循环解决某类问题的思路。

3）之前发的很多帖子，帖子文字都是在做了很多类型题目之后总结概括的内容，虽然有附例题，但是总归有些不太容易被初学者理解。因此本帖也想给之前发的LAMBDA函数：递归基础下面截图这段话增加示例进行说明，以使其更方便理解。

shaowu459

之前在x档案大揭秘中提到过这样一个解题方法，有如下一个原始数组（可能是单行、单列或多行、多列）：

要求每次从数组中从上到下提出出若干行（例如经典的分组问题），通过重组、计算等方式组合生成一个新的结果，使用过的行不再使用，直到原数组所有行都被用过为止。


实际公式书写大概是下面的逻辑。假设原数组前2行符合第一次提取条件，那么提取前2行生成一个结果，这个结果可能是单值也可能是多行列的结果，堆积在原数组下方，也就是新数组=VSTACK(X,前2行生成结果)，然后再将新数组前2行DROP掉。




下次循环的时候，从新数组的顶端开始判断，比如说提取前3行生成一个结果堆积在上一步的数组下方，再把结果数组前3行DROP掉。这样一直循环直到原数组所有行的被使用过为止。




按上面的方式循环，需要增加一些提取和判断，包括原数组和新数组的分界标识、错误值和边界值的处理等等，稍微麻烦一些。而在递归中，我们可以使用一个参数arr单独存储原数组及逐渐DROP行的数组，一个参数res存储新生成结果的数组。什么时候退出呢？等arr返回错误值的时候，也就是所有行都被DROP掉的时候，让递归返回res结果即可。

shaowu459

示例1：将一列数据分组，标准是累计和≤120就分一组。




因为要从上到下累计求和，所以我们很自然可以想到使用SCAN函数实现。


然后可以使用XMATCH函数判断≤120的位置是第几个：


有了位置2，提取数组的前2行进行合并存储即可。同时，把原数组前2行使用DROP函数去掉。

下一次循环的时候，同样使用SCAN函数对剩余的数组累计求和：



同样判断≤120的位置，然后提取、合并、存储新结果、去掉数组的前3行：



有了上面的分析，我们就可以使用递归来完成这个任务。使用arr代表原数组和变化后的数组，使用res存储每次循环的结果。参考公式如下（公式都不是最优的，仅用于清晰说明思路）：

1. =LET(f,LAMBDA(f,arr,res,LET(s,XMATCH(120,SCAN(,arr,SUM),-1),IF(COUNT(arr),f(f,DROP(arr,s),VSTACK(res,ARRAYTOTEXT(TAKE(arr,s)))),res))),f(f,A1:A14,"结果"))

复制代码

公式简要说明如下：
=LET(
    f, LAMBDA(f,

              arr,   存储原数组和变化数组

              res,   存储结果
              LET(
                  s, XMATCH(
                            120, 匹配值
                            SCAN(, arr, SUM),   对arr累计求和
                           -1),   s结果为从上到下≤120的最大值所处位置
                  IF(
                     COUNT(arr),    如果arr是错误值时COUNT结果为0，也即可以退出循环
                     f(f,
                       DROP(arr, s), 递归重新调用f，新参数为上次的arr去掉前s行
                       VSTACK(res, ARRAYTOTEXT(TAKE(arr, s)))  堆积上一步的res和本次前s行的合并结果
                       ),
                     res   如果arr已经是错误值，则返回res结果

                    )
                 )
             ),
    f(f, A1:A14, "结果")
)

如果需要堆积成多行多列的结果，可以参考如下公式，堆积部分稍作调整即可：

1. =LET(f,LAMBDA(f,arr,res,LET(s,XMATCH(120,SCAN(,arr,SUM),-1),IF(COUNT(arr),f(f,DROP(arr,s),VSTACK(res,TOROW(TAKE(arr,s)))),res))),IFNA(DROP(f(f,A1:A14,0),1),""))

复制代码

shaowu459

示例2：根据数量分组。

下图A列是名称，B列是数组，要求分组后生成右侧的结果样式。规则举例说明：第一行的Accessories共440个，2-6行对应的B列数量加起来等于440，则1-6行为一组。




解题思路和示例1基本一样，大体思路如下：
1）将数组第2列累计求和；
2）查找数组第一个值的2倍在累计求和数组中的位置t；
3）提取出数组前t行得到数组u，也就是整个分组的数据；
4）根据u进行组合调整，得到结果样式，堆积在res下方；
5）再次递归调用，新数组arr为上一步的arr去掉前t行。
6）判断arr已经是错误值，则可退出递归，返回res。

参考公式如下：

1. =LET(f,LAMBDA(f,arr,res,LET(s,DROP(arr,,1),t,MATCH(@s*2,SCAN(,s,SUM),),u,TAKE(arr,t),IF(COUNT(arr),f(f,DROP(arr,t),VSTACK(res,IFNA(HSTACK(@u,DROP(u,1)),@u))),res))),DROP(f(f,+A1:B10,0),1))

复制代码

公式简要说明如下：
=LET(
    f, LAMBDA(f, arr, res,
        LET(
            s, DROP(arr, , 1),    提取数组第2列的数值
            t, MATCH(@s * 2, SCAN(, s, SUM), ),   获取在s累计求和中第一个值2倍的数字所在位置
            u, TAKE(arr, t),  提取arr的前t行，也就是当前分组所有的行数
            IF(
                COUNT(arr),   判断数组中数字的个数，如果是错误值返回0，也即可退出循环
                f(
                    f,
                    DROP(arr, t),  再次循环时参数变成arr去掉前t行的结果
                    VSTACK(res, IFNA(HSTACK(@u, DROP(u, 1)), @u))  堆积res和当前分组行内容产生的结果
                ),
                res  arr是错误值时退出循环，返回res
            )
        )
    ),
    DROP(f(f, +A1:B10, 0), 1)
)

华尔街操盘手

超人大佬，为啥我复制了你的公式，是报错的

shaowu459

华尔街操盘手 发表于 2024-6-6 15:13
超人大佬，为啥我复制了你的公式，是报错的

一楼有文件，你可以直接下载，里面有公式。
另外，你应该不是直接复制的，在2*s那里，s前面还有个字符的，提取数组s的第一个值。

edwin11891

每天学习，每天进一小步。。。。

edwin11891

shaowu459 发表于 2024-6-6 13:57
示例1：将一列数据分组，标准是累计和≤120就分一组。

如果前面加一列，对每行数据求和（证实<120）呢，公式该如何修改？

shaowu459

edwin11891 发表于 2024-6-6 18:56
如果前面加一列，对每行数据求和（证实

开始想过放合计，不过作为示例弄得太麻烦就不好了。就是在vstack第二参数堆积的地方改改，横向堆一个结果

shaowu459

edwin11891 发表于 2024-6-6 18:56
如果前面加一列，对每行数据求和（证实

没带电脑，就是后面这里VSTACK(res,hstack(sum(TAKE(arr,s)),TOROW(TAKE(arr,s))))