Microsoft 365：X檔案大揭秘

shaowu459

Microsoft 365与之前Office版本比较，Excel中增加了一系列函数，这些函数各有各的特点和用途，但我个人觉得其中有一个函数可当这系列函数的带头大哥，这个函数就是REDUCE函数。关于REDUCE函数的基本用法，可以参考我的个人签名处的链接365最新函数基本用法介绍，本帖主要介绍一下和REDUCE函数第一参数（也就是帖子标题里说的X）相关的一些常见用法，希望能对刚开始使用365版本Excel函数的坛友提供一些参考。

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

shaowu459

REDUCE函数的第一参数可以是单值也可以是数字，可以是文本、常量数组、单元格区域引用等等。

例如，以下公式实现将某单元格中的字符串逆转的效果，REDUCE函数的第一参数X设置的初始值是空文本。

1. =REDUCE("",MID(B3,SEQUENCE(LEN(B3)),1),LAMBDA(x,y,y&x))

复制代码

再例如，X初始值设定为0，每次运算执行X+1运算，实现累加（结果为Excel最大列数）：

1. =REDUCE(0,1:1,LAMBDA(x,y,x+1))

复制代码

X设置为数组的例子就不举例了，后面很多例子都会涉及到。

shaowu459

X如果省略，则第二参数（y）中的第一个值将变成X的初始值，不参与LAMBDA函数体部分运算。

例如，设定X初始值为0，以下公式返回结果为B7:D8单元格区域每个单元格的平方和，循环B7:D8单元格区域每个值的时候，执行x+y^2的运算：

1. =REDUCE(0,B7:D8,LAMBDA(x,y,x+y^2))

复制代码

若省略第一参数，B7:D8单元格区域中的第一个值7（B7单元格的值）将成为X的初始值，不参与后面平方运算，循环到C2单元格的值时，LAMBDA函数体部分运算为：x+y^2=7+2^2=11。

1. =REDUCE(,B7:D8,LAMBDA(x,y,x+y^2))

复制代码

shaowu459

通过在LAMBDA函数体部分使用VSTACK函数和HSTACK函数，可以将X初始值及后续的X值（均含数组，下同）与每步y运算产生的值或数组进行堆叠。

例如，以下公式将1~10的平方进行纵向堆叠。LAMBDA函数体部分每次运算都使用VSTACK函数将当前y的平方纵向堆叠在当前的x下面。

1. =REDUCE("数据",ROW(1:10),LAMBDA(x,y,VSTACK(x,y^2)))

复制代码

同理，使用HSTACK函数可以实现将结果横向堆叠的目的。

1. =REDUCE("数据",ROW(1:6),LAMBDA(x,y,HSTACK(x,y^2)))

复制代码

上面的例子是堆叠的单值，如果每次LAMBDA函数体运算产生的是数组，仍然可以堆叠：
=REDUCE(X初始值，数组，LAMBDA(x,y,VSTACK(X,HSTACK(值1，值2，值3))
此时X初始值可以是单值，也可以是数组。在堆叠过程中，由于堆叠数组的列数不同会产生#N/A，可以使用IFNA函数处理。
=REDUCE(X初始值，数组，LAMBDA(x,y,HSTACK(X,VSTACK(值1，值2，值3))
其中HSTACK(值1，值2，值3)或VSTACK(值1，值2，值3)部分不一定由VSTACK函数或HSTACK函数生成，可以是筛选、判断、引用等形成的数组。

shaowu459

REDUCE函数运算如果返回预期之外的结果，可以用堆积每步运算结果的方式来辅助检查问题。

假设A1:A20单元格区域存储着20个数字，现在要求每次随机从这20个数字中提取出10个数字并求和，一共提取20次，并且返回这20次随机提取结果合计的最大值。下面的公式实现将这20次随机选取过程都展示出来的目的：

1. =REDUCE(B1:B11,A1:A20,LAMBDA(x,y,LET(s,TAKE(SORTBY(A1:A20,RANDARRAY(20)),10),HSTACK(x,VSTACK(SUM(s),s)))))

复制代码

为了方便，我们将每次随机选取的数字合计值也堆积起来，因为每次提取10个数字，所以每次返回11行，第一行返回当前10个数字的合计值。公式运算步骤如下：
1）设定X初始值为11行的空单元格（空单元格最后返回0），也可以是单值0或者其他值，无所谓，因为最后不需要这一列的内容。
2）第二参数选定一个20个单元格的区域即可，也可以是4行5列或5行4列，亦或是row(1:20)这种，第二参数只用来控制循环次数。
3）LAMBDA函数体运算部分，定一个s，每次返回将A1:A20单元格区域按RANDARRAY(20)产生的20个随机数随机排列后取前10行的数组。
4）每次循环时使用HSTACK函数将x和VSTACK(SUM(s),s)部分（s合计下面堆积s这个数组）横向堆叠起来。
5）循环万20次后结果数组就如上面图片所示。第一行的值就是每次循环随机挑选的20个值的合计，使用TAKE函数提取第一行，然后求最大值即可。

上面只是一个举例，REDUCE函数每步产生的结果，都可以堆叠起来，方便查看每步运算的返回结果。

shaowu459

LAMBDA函数体运算部分，可以提取X的一部分进行运算或判断。

例如，提取X第一行TAKE(X,1)，提取X最后一行TAKE(X,-1)。
提取X最后一行中的第一个元素@TAKE(X,-1)（如果X是单行或单列数组，有时TAKE函数提取出来的仍然是数组，需要加@）或@+TAKE(X,-1)（当提取结果是单元格区域的时候，+将单元格区域引用转化为数组）。
提取X最后一行中的第二个元素，INDEX（TAKE(X,-1)，2）等等。

同理，TAKE(X,,1)和TAKE(X,,-1)提取X第一列或最后一列也可以用于运算和判断。


例如，假设当前y值是“高二”，可以使用SUM(N(X="高二"))或SUM(N(INDEX(X,,2)="高二"))来判断X中一共有多少行高二的记录。
例如，使用SUM(X)可以获得当前X中所有人成绩的总和（因为只有一列是数字），所以可以直接求和。
例如，假设当前y值是“高二”，可以使用FILTER(X,INDEX(X,,2)=Y)来筛选X中所有年级为“高二”的记录。


总之，X之中的任何部分都可以使用其他函数来提取用于LAMBDA函数体运算，常见的组合有TAKE函数提取，INDEX函数提取，直接使用X整体等。使用REDUCE函数时，可以随时考虑当前X值中是否有可以用于后续运算和判断的部分。

shaowu459

使用@X或者@+X可以直接提取X最左上角的值，用于LAMBDA函数体部分运算。
推衍一下，可以改变之前说的堆叠顺序，将当前循环产生的值堆叠在X上方，也即VSTACK(新数组，X)，这样就可以使用@X或者@+X直接提取了。
继续推衍，如果VSTACK(新数组，X)中的新数组是一个1行多列的数组，比如正常是用HSTACK(值1，值2，值3），如果值2是后续运算判断中要引用多次的判断依据，则可以将值2挪动到前面去，例如：VSTACK(HSTACK(值2，值1，值3），X)，这样可以避免先提取X第一行或最后一行，然后用INDEX函数提取第2个值这样的提取方式，可以直接使用@X来提取“值2”用于下次循环运算。

shaowu459

因为X是一个数组，所以可以直接使用XLOOKUP在X第一列里查找当前的y，然后返回后面某列或其他数组的内容。
同样，如下图，如果当前y值是姓名“A”，那么可以直接使用VLOOKUP(y,X,5,)来获取A对应的分数。如果X里面的姓名“A”有重复且想提取最后一个“A”对应的值，可以使用XLOOKUP函数，可以实现从后往前查找，并在查询不到的时候返回替换值。

shaowu459

提取X最后一行或第一行和当前y值判断。这个点在之前提过，但因为用的实在是非常频繁，因此单独再说一下。
例如，判断当前y是不是和X最后一行的第一个值相等，可以使用@TAKE(X,-1)=y。
若只是判断y是不是在X最后一行里有，可以直接使用OR(TAKE(X,-1)=y)。
若判断y和X最后一行里的第3个值判断，可以使用INDEX(TAKE(X,-1),3)=y。

shaowu459

LAMBDA(x,y,IF(条件判断，VSTACK(DROP(X,-1),基于X的最后一行生成的新数组)，VSTACK(X,基于y生成的新数组))

假如原数据姓名列人名是有重复的（每天或每次对应事项的姓名挨在一起，例如每天一个人多次测量体温，数据表中有这个人3天的记录，每天这个人的体温记录都挨在一起，但是3天的记录中间夹杂着其他人），比如说A在第2~5行出现过，在12~17行也出现过，需要统计每天连续出现的次数，这时就可以使用上面的套路。



上面的公式解释如下：
1）IF函数的第一参数判断当前的y是不是姓名“C”（当前y和X最后一行第一个值比较），返回TRUE或FALSE。TRUE代表当前的y和X最后一行的人是连续的。
2）如果是C的话，就把X最后一行的C去掉，也即DROP(X,-1)。然后将TAKE(X,-1)的最后一个值“1”加上1形成一个新数组，也即仅将计数部分+1。然后再将去掉X最后一行的结果和新生成的计数+1的数组堆叠在一起。
3）如果不是C的话，也即当前的人和上一行的人不是同一个人，因此应将该条记录直接堆积在X下方。基于y生成的数组可能是类似：HSTACK(OFFSET(y,,,,4),1)，从当前姓名往右取4列，然后右侧加上计数值1。