Microsoft 365：机灵鬼小y

shaowu459

不知道什么原因，有些回复显示不出来，在一楼上方点击【倒序浏览】或【只看该作者】能把我所有的回复看全。


Microsoft 365中的REDUCE、SCAN、MAP等函数都可以使用多个参数，LAMBDA函数运算体部分两参数的情况下一般可以LAMBDA(x,y,……)，LAMBDA(s,t,……)，LAMBDA(m,n,……)等等，第一参数x的一些基本情况在Microsoft 365：X檔案大揭秘中进行了介绍，本帖主要以REDUCE函数为例，介绍第二参数y的一些常见用法，供初学者参考。


REDUCE函数的第二参数y可以是单值也可以是数组，也可以是OFFSET函数生成的多维引用；y值可以在LAMBDA函数运算体内调用，也可以不使用y的具体值而只用于控制循环次数；如果y是单元格区域，在后续调用时还可以利用其单元格属性，例如提取所在行号、列号，作为OFFSET函数第一参数、SUMIF等函数的参数、使用A1:y这种方式表示动态区域；可以对一些数据进行拆分、去重等处理后作为y进行统计等等，y的使用非常之灵活。本帖会用一些具体示例来分别对上述情况加以说明。


注意事项：
1）本帖的一些示例数据和公式完可能仅仅是为了展示我想说明的用法构造的，那个问题本身可能没有实际意义，并且公式不是对那个问题最佳的解决方案；
2）如果后续有视频讲解，也会放在个人签名365最新函数基本用法介绍这个帖子的1楼网盘链接里面；
3）如果有错误或不当支出请不吝指教，若有其他想补充的内容，也请跟帖分享。

shaowu459

REDUCE函数在运算时，会循环遍历y的每一个值，y如果是数组，是多行多列、单行、单列等情况可能对返回数组不产生影响。

例如，以下公式返回A3:C4单元格区域每个单元格数值平方的合计。

在循环时，遍历A3:C4单元格区域每个单元格，将对应数字的平方与x相加。

数组变成单行或单列，对结果不产生影响：

shaowu459

REDUCE函数循环时，默认会按先行后列的方式遍历y的每一个值。可以使用VSTACK函数和HSTACK函数将每次循环产生的结果和x进行堆叠。

例如，以下公式将A3:C4单元格区域中的每个值纵向堆叠起来。


使用HSTACK函数，可以将数据横向堆叠：


在使用VSTACK函数和HSTACK函数纵向或横向堆叠时，y的行列情况不影响堆积结果。

shaowu459

y可以只用来控制循环次数，而不使用具体的y值。

例如，下面公式中的y使用1:1，x初始值设置为0，每循环一次x值加1，最终结果为16384。

在上面的计算中，1:1代表第一行整行，一共16384个单元格，因此REDUCE函数要循环16384次，由于运算过程不需要使用y的具体值，因此表格第一行存储任何内容都不影响运算。

利用这个特点，如果在解决某些问题不知道具体要循环多少次时，可以给一个比足够大的循环次数，常见的如使用Z1:Z9，ROW(1:99)，SEQUENCE(999)，1:1等。

shaowu459

如果省略x初始值，只用逗号占位，则第一次循环时，y的第一个值不会参与LAMBDA函数体部分运算，而直接变成x的初始值。

仍然以求A3:C4单元格区域数值的平方和为例，由于下面公式中没有输入x的初始值0，因此第一次循环时2不会进行平方运算，而是直接作为x的初始值。第二次循环时，会执行2+3^2的运算，后面依次类推。最终运算结果是137，会比输入x的初始值0时的结果少2。

shaowu459

根据以上的分析，y可以实现同时提供x初始值和循环次数的功能。

下面举一个去除括号字符串中有效括号的例子。字符串只由小括号组成，有效括号指()、(())、()()这种以左括号开始并以右括号闭合，且左右括号不交叉的括号。如)()、())(这种则是无效括号。数据源和结果如下图所示：


为达到上面的目的，我们使用公式循环将成对的()替换成空即可。在B2单元格输入以下公式，向下填充：

1. =REDUCE(,A2:A99,LAMBDA(x,y,SUBSTITUTE(x,"()",)))

复制代码

上面的公式中，y取A2:A99单元格区域，因为第一参数x省略，所以A2直接成为x初始值，后面将循环替换A2单元格中的()。又因为我们不确定要循环多少次，所以选定一个比较大的范围到A99单元格。REDUCE函数具体运算过程是：第一次循环A2直接成为x初始值，后面遍历A3:A99单元格区域的每个单元格，共循环97次，每次循环时都将x中的()替换成空。在这里例子中，y既提供了x初始值，又提供了足够多的循环次数。

当公式向下填充到B3单元格时，y变成了A3:A100，A3作为x的初始值，A4:A100控制循环次数（和A4:A100单元格之中存储的值没有关系）：

shaowu459

上面的例子中，y提供了初始值和足够多的循环次数，下面介绍一个精确利用循环次数的例子。

如下图，A列是一些由[]包裹名字组成的字符串，D2单元格是指定的人名，要求将A列字符串中D2单元格的人名全部挪动到字符串最前面去，结果如B列所示。附加要求：A列单元格只引用一次。


首先，观察一下使用TEXTSPLIT函数按分隔符拆分字符串时有什么特点。例如以“[A]”为分隔符拆分字符串"[A][B][B][A][C][A]"：

1. =TEXTSPLIT("[A][B][B][A][C][A]",,"[A]")

复制代码

可以观察到，原字符串中有3个[A]，拆分后数组为4行，规律其实也简单，有n个分隔符，字符串将被拆分成为n+1个。反过来说，拆分出来的数组如果有n行，那么原字符串中就有n-1个分隔符。就本题来说，按D2单元格姓名拆分A列字符串后，假设有y有5行（也就是A列字符串中D2单元格姓名有4个），那省略x后第一次循环y直接作为x初始值，后面LAMBDA函数运算体部分只循环4次（名字个数），每循环一次在x最前面加上一个D2单元格姓名即可。

有了上面的分析，我们可以写出如下公式：

1. =REDUCE(,TEXTSPLIT(A2,D$2),LAMBDA(x,y,D$2&x&y))

复制代码

对上述公式简单说明如下：
=REDUCE(
    ,                                 省略第一参数，以减少一次循环
    TEXTSPLIT(A2, D$2),    将A2按D2单元格姓名拆分，数组元素个数-1就是D2单元格姓名出现次数
    LAMBDA(x, y,
            D$2 & x & y,        循环一次，在x左侧加上一个D2单元格姓名，同时将y连接在右侧。y只有空或者非D2单元格姓名两种情况，所以直接&连接无影响。
                )
             )

shaowu459

再举一个使用y控制循环次数的例子。

如下图，A:E列存储着一些由-连接的名字，要求将每一行数据收尾相连后，去掉G列取消条件中的姓名，结果如H列所示。


参考公式如下：

1. =LET(s,A2:E2,TEXTJOIN("-",,UNIQUE(TEXTSPLIT(CONCAT(REDUCE(s,s,LAMBDA(x,y,XLOOKUP("*-"&LEFT(x)&"*",s,s&0,0,2)&x))),,TEXTSPLIT("-、0、"&G2,,"、",1)))))

复制代码

公式意思不再细说，主要标注下循环次数问题：

=LET(
    s, A2:E2,               每行一共5个元素，定义为s
    TEXTJOIN(
        "-",
        ,
        UNIQUE(
            TEXTSPLIT(
                CONCAT(
                    REDUCE(
                        s,    s为x的初始值
                        s,    根据后面运算要求，需要循环5次，但因为已经定义了s，可以直接使用s控制循环5次。改成A1:A5这种也可以。
                        LAMBDA(x, y,
                            XLOOKUP("*-" & LEFT(x) & "*", s, s & 0, 0, 2) & x
                        )
                    )
                ),
                ,
                TEXTSPLIT("-、0、" & G2, , "、", 1)
            )
        )
    )
)

shaowu459

y如果是单元格区域引用，则仍带有单元格属性，可以作为单元格使用。

例如，以下公式可以求A列数据从上到下的累加和。

1. =REDUCE(,A1:A8,LAMBDA(x,y,VSTACK(x,SUM(A1:y))))

复制代码

因为第二参数y引用A1:A8单元格区域，因此在第一层LAMBDA函数体部分引用时仍带有单元格属性，可以使用A1:y这种方式动态引用区域范围。

同样，此时y也可以用作OFFSET函数的第一参数：


返回所在单元格行号：


如果将y再次嵌套REDUCE函数、MAP函数等，再次引用时仍会保留单元格属性（截至发帖时Beta版可用，之前版本会丢失单元格属性）：



简单总结：如果y是单元格区域，后续调用时可以使用单元格相关的属性，如ROW()，COLUMN()，作为COUNTIF\SUMIF\OFFSET函数需要为range的参数等。

shaowu459

y可以使用ROW(1:99)或SEQUENCE函数生成的序列，一方面可以用来作为位置提取，另一方面也可以提供位置信息。

例如，y为行号，运算过程中使用INDEX函数从B列提取对应值：

也可以改成SEQUENCE函数生成：


又如下面的例子，y为足够大的自然数序列，运算过程中使用MID函数从字符串中逐个提取出字符。


参考公式如下：

1. =-REDUCE(0,ROW($1:99),LAMBDA(x,y,IF(@x<0,x,SWITCH(MID(A2,y,1),"{",VSTACK(MAX(x)+1,x,MAX(x)+1),"}",IF(@x=B2,-y,DROP(x,1)),x))))

复制代码

上面的公式中，y使用ROW($1:99)，因为后面LAMBDA函数体运算时会加以判断，所以给定一个超过必要的循环次数外的大数（也即99）不影响运算结果。在运算过程中，使用MID(A2,y,1)提取出A2中的每个字符来遍历，y即每个字符的位置。

本题还有一个特点，如果LAMBDA函数运算体部分只需使用A2中的每个字符，那么第二参数y可以直接使用MID(A2,ROW($1:99),1)。例如逆转字符串的例子：

但是如果像本题一样还需要同时使用每个字符所在的位置，第二参数直接使用ROW($1:99)，在运算时就可以同时获得并使用MID(A2,y,1)的这个字符和对应位置y的值。