Microsoft 365：X檔案大揭秘

shaowu459

X初始值是数组时，每次更新值时可以选择能更新就更新，不能更新就保留原值。

下面例子是QQ群里大家做的一个小题，根据B:C列父子级别关系，查找E列每个人员最终的父级。公式来自群里且已简化，其中的E4:E12单元格区域的姓名列表可以通过公式从原数据区域生成。

1. =REDUCE(E4:E12,B4:B9,LAMBDA(x,y,IFNA(XLOOKUP(x,C:C,B:B),x)))

复制代码

上述公式运算过程如下：
1）初始值设定为所有人员列表；
2）每次都使用XLOOKUP函数在子级中查找姓名，返回B列的父级。如果能查找，说明有父级，就返回父级姓名，如果查不到，说明没有，就保留X中的姓名。
3）如棕色标注的C，第一次查找时，C对应的父级是B，因此C更新成B。第二次查找时，B对应的父级是A，因此返回A。再查找A时，A在C列里没有，因此返回#N/A错误值，因此仍然保留A。这样，就将C最终更新成了最终的父级A。

shaowu459

X在每次循环的时候不是必然每次增加一行，也可以一次性增加多行。


B列和C列是原始数据，黄色的A和B是一组，2个值，因此“列2”都填写2；棕色的C和D是一组，也是2个值，因此“列2”也填写2。要求在每组的每个元素后面都填写上当前组的元素合并结果，例如A和B后面都填上字符串“AB”。

1. =REDUCE("结果",C2:C16,LAMBDA(x,y,LET(s,DROP(B2:C16,ROWS(x)),t,TAKE(s,TAKE(s,1,-1),1),IFERROR(VSTACK(x,T(t>0)&CONCAT(t)),x))))

复制代码

公式运算说明如下：
1）每次循环时，在B2:C16单元格区域中DROP掉X中已有的行数，生成一个数组s。
2）s中第二列的第一个值，就是当前组元素的数量（假设为2），使用TAKE函数提取处s中的前2行，生成一个数组t。
3）将X和2行的CONCAT(T)的结果纵向堆叠在一起，也就是一次性在X下面堆叠了2行。
4）当X中已经有原数据那么多的行数时，再DROP就会出错，此时使用IFERROR函数保留当前的X结果即可。

shaowu459

给X一个值为0的数组作为初始值+将每步运算结果堆积在当前X上面+使用@提取X左上角值进行运算+计数器增加示例。

如下图，A列是系列数据，中间可能有空单元格，要求A列数据连续大于200以后就重新分组并求和，求和结果乘以当前组的元素数量。最后将所有组结果再求和。例如A2+A3=239已经大于200，元素是2个，所以本组结果是：239*2=478。A5+A8=243大于200，元素是2个，所以本组结果是243*2=486。以此类推。

1. =LET(s,REDUCE(+L1:M1,A2:A28,LAMBDA(x,y,VSTACK((@x%<=2)*TAKE(x,1)+IFERROR(y^{1,0},),x))),SUM((s*DROP(s,,1))*(s%>2)))

复制代码

公式运算说明如下：
1）首先给X一个初始值是0的数组，可以写常量，也可以引用一行的两个单元格。为了能使用@提取第一个值，在前面添加一个+将区域引用变成数组。
2）逐个循环A列的值，每次提取X最左上角的值和200进行比较。使用当前y^{1,0}获得当前数字和计数1的结果，例如y=121，则y^{1,0}返回{121,1}，也就是当前数字和计数1次。如果X左上角小于等于200，也就是还没超过200，则用X第一行加上当前y^{1,0}的值，第一个数字加和，第二个数字计数器+1。如果大于200，则(@x%<=2)*TAKE(x,1)部分返回{0,0}，加上当前y^{1,0}保持y^{1,0}值不变。
3）将步骤2生成的数字，用VSTACK函数堆叠在当前X上方。VSTACK(数组，X)。
4）最后在REDUCE函数生成结果里提取第一列大于200的行，乘以第二列的计数器，再加和即可。

shaowu459

给X初始值赋予一个很大的数字+每次循环时查找X中符合条件数据的最大值例子。

题目描述：已知A列有20个数，请你计算每列需要最少移除多少个数能使得序列变成nums[0]<nums[1]<nums[2]<…<nums>nums[i+1]>nums[i+2]>…>nums[k](1<=i<=k)的形式。也即求最少删除几个数字，使剩余的数字是一个先递增后抵减的序列。

1. =20-MAX(MAP(ROW(1:20),LAMBDA(z,LET(T,INDEX(A:A,z),F,LAMBDA(m,n,TAKE(DROP(REDUCE(9^9+{0,0},m,LAMBDA(x,y,LET(O,IF(n,INDEX(A:A,20+z-ROW(y)),y),VSTACK(x,HSTACK(O,1+MAX(FILTER(TAKE(x,,-1),TAKE(x,,1)<O,0))))))),1),-1,-1)),P,F(A1:T,0),Q,F(T:A20,1),IF(AND(P=1,Q=1),0,P+Q-1)))))
   

复制代码

上述公式简单说明：
1）给REDUCE函数设定一个非常大的初始值9^9+{0,0}，本例中也可以使用{"",""}替代，文本大于所有的数字。
2）MAX(FILTER(TAKE(x,,-1),TAKE(x,,1)<O,0))这部分每次都筛选X最后一列的数值，标准是第一列的值小于O的值。然后使用MAX函数获得筛选结果的最大值。
本例的视频讲解可以在下面的链接下载：
链接：https://pan.baidu.com/s/1PPMKigiMIp2z8lC8XYSD4w?pwd=a97r
提取码：a97r

shaowu459

给X赋予一个常量数组的初始值，后续每次循环时根据判断结果更新每个值，最后返回包含着多种信息的数组。

题目描述如下：

公式如下：

1. D2=LET(O,REDUCE(+A2:C2,SEQUENCE(C2),LAMBDA(x,y,LET(V,INDEX(x,2),U,HSTACK(0,-17,-1),T,CEILING((10-@x)/4,1),x+IFS(V<=0,0,@x>9,HSTACK(-10,-60,-1),INDEX(x,3)<=T,U,V>=17*T,HSTACK(4,0,-1),1,U)))),IF(INDEX(O,2)>0,"不能","能"))

复制代码

1. E2=LET(O,REDUCE(+A2:C2,SEQUENCE(C2),LAMBDA(x,y,LET(V,INDEX(x,2),U,HSTACK(0,-17,-1),T,CEILING((10-@x)/4,1),x+IFS(V<=0,0,@x>9,HSTACK(-10,-60,-1),INDEX(x,3)<=T,U,V>=17*T,HSTACK(4,0,-1),1,U)))),P,INDEX(O,2),IF(P>0,B2-P&"m",C2-INDEX(O,3)&"s"))

复制代码

公式运算说明：
1）直接引用A2:C2单元格的数组，每个值分别代表{"魔法初值","距离","时间"}。
2）每次循环时，使时间减少1s，直到减少到0为止。
3）如果这1s跑步前进，就让“距离”减少17m，魔法值不变。HSTACK(0,-17,-1)
4）如果这1s执行了闪现，则让“魔法”减少10，“距离”也减少10。HSTACK(-10,-60,-1)
5）如果这1s执行了等待回复，则让“魔法”增加1，“时间”减少1s。HSTACK(4,0,-1)
6）最后，时间退成0之后循环完毕。检查数组三个值的结果，然后判断是否逃的出去及跑出最远距离或最短时间。

逐渐陌生

太强大了~~~

shaowu459

省略第一参数+判断字符是否在X中存在+X字符串累计和替换例子


题目说明：将A列源字符串去重后在不改变原始字符顺序的情况下所能组成排位最靠前的字典字符串。  比如bcacb，去重后能变成bca、bac、cab、acb四种字符串，那么最小字典字符串就是acb。大小比较标准，直接按在单元格中输入="A">"B"这种判断结果为准。

1. =REDUCE(,MID(A2,SEQUENCE(LEN(A2)),1),LAMBDA(x,y,IF(ISERR(FIND(y,x)),x&y,IF((MID(x,FIND(y,x)+1,1)<y)+(y>RIGHT(x)),SUBSTITUTE(x,y,)&y,x))))

复制代码

公式运算说明：
1）省略第一参数，第二参数提取的单字符数组第一个字母将直接作为X初始值。
2）当y字符在X不存在，直接x&y连接字符。
3）当y字符在x中存在，如果y大于x中y字符右侧的字符或y大于X最右侧字符时，将X中的y字符去掉，将y添加在X最右侧。否则保持X不变。

shaowu459

循环过程中堆积中间值+使用@X提取X左上角的值+最后使用TAKE函数提取需要的结果示例

下图A:AC列是包含期初数量、每月出入库和期末数量的存货记录，要求期末库存的库龄。

AD单元格输入以下公式，下拉即可：

1. =TAKE(REDUCE(AC3,OFFSET(AC3,,VSTACK(-SEQUENCE(12)*2,-25)),LAMBDA(x,y,HSTACK(MAX(@x-y,),x,MIN(@x,y)))),,-13)

复制代码

公式运算过程说明：
1）X初始值设定为期末数量。
2）从右往左循环（从12月-1月方向），每次循环时在X左侧堆积尚需判断库龄的剩余存货数量（中间值），X右侧堆积当月入库留在期末库存中的数量。HSTACK(MAX(@x-y,),x,MIN(@x,y))。
3）步骤2运算时，每次都用@x提取最左侧的中间值，和y进行运算或比较。
4）最后使用TAKE函数提取最后13列需要的数值即可。

shaowu459

两个REDUCE函数嵌套，后面REDUCE函数的X初始值为前一个REDUCE函数的y值。

以下公式将A:D列有关联的数据放到同一行中，使用了两个REDUCE函数嵌套，第二个函数的X初始值是第一个函数的y值。

1. =DROP(REDUCE(0,A3:D17,LAMBDA(x,y,IF(OR(y=x,y=""),x,IFNA(VSTACK(x,REDUCE(y,ROW(1:99),LAMBDA(m,n,UNIQUE(HSTACK(m,TOROW(FILTER(A3:D17,BYROW(A3:D17,LAMBDA(z,OR(TOCOL(z)=m)))),1)),1)))),"")))),1)
   

复制代码

shaowu459

将每次循环中间值堆积在X左侧+在X右侧堆积需要结果+每次循环更新同一个结果值示例。

如下图，A列是B列数值编号，B列数值代表不同长度的柱子，要求所有柱子构成的矩形区域的最大面积值（力扣中原问题为盛雨水问题）。


思路说明：
从最左侧和最右侧柱子开始，以最短板为矩形的边长，以两个柱子间距为另外一边长，求得一个面积。然后将较短的一个板向中间移动，再次求矩形面积，以此类推，每次都将较短的板子往中间移动。因为如果移动长的板子，短板的边长不变，间距减少1，面积只能缩小，不会有机会变更大。当左右板子碰头的时候，循环结束。

方法一：

1. =MAX(TAKE(REDUCE(+B2:B11,A2:A10,LAMBDA(x,y,LET(s,TOCOL(TAKE(x,,1),3),t,LOOKUP(9^9,s),HSTACK(DROP(s,-1^(@x>t)),x,MIN(@x,t)*(ROWS(s)-1))))),1,-9))

复制代码

提取REDUCE函数部分，运算结果如下：

初始值X为B列全部数据，循环第一次时，计算第1个和最后1个值的较小者，然后乘以间距堆积在X右侧。同时，将X初始值第1个值和最后1个值较小者去掉形成一个1列的数组堆积在X左侧，这个数组就是下次要运算的基础。
第二次循环时，从X最左侧提取出1列，同样的方法，计算首尾较小者乘以间距堆积在X右侧，去掉首尾较小值后的数组堆积在X左侧。
这样，每次构造的面积都在X右侧堆积，下一次要运算的数组在X左侧堆积，每次提取X第一列运算即可。
最后，使用TAKE函数提取第1行，后9列数据即可。

方法二：

1. =@REDUCE(+B1:B11,A2:A10,LAMBDA(x,y,LET(s,DROP(x,1),t,@TAKE(s,-1),VSTACK(MAX(@x,MIN(@s,t)*(ROWS(s)-1)),DROP(s,-1^(@s>t))))))

复制代码

上面的公式初始值仍为B列数据（含B1这个空单元格，这个位置用于存储矩形面积值），不同之处在于，每次循环时将当前计算的面积值更新在X最上方（若当前计算的面积值大于@X的值，则取当前计算的面积值，否则保持@X不变），去掉第一行后的X为每次要运算的数组。

公式运算完成后，REDUCE函数返回2行1列的数组，第一个值为要求结果的最大值，使用@提取即可。