Microsoft 365：机灵鬼小y

shaowu459

y可以是OFFSET函数生成的多维引用。

如下图，原数数据为不同省份的农作物和种植面积数据，要求转化成下方表格：


在任意空单元格输入以下公式：

1. =REDUCE(A1:C1,OFFSET(B1:I1,ROW(1:2),),LAMBDA(x,y,LET(s,@+OFFSET(y,,-1),VSTACK(x,IFNA(HSTACK(s,WRAPROWS(y,2)),s)))))

复制代码

公式中的OFFSET(B1:I1,ROW(1:2),)部分，以B1:I1为基点，向下分别偏移1行和2行，生成B2:I2和B3:I3的多维引用。第一次循环时y=B2:I2这个单元格区域（带单元格属性），第二次循环时y=B3:I3单元格区域。

y为多维引用的另外一个例子：

公式如下：

1. =TAKE(REDUCE({40,0},OFFSET(A3:B3,,24-COLUMN(A:K)*2),LAMBDA(x,y,x+MIN(@x,@+y)*IF({1,0},-1,y))),,-1)

复制代码

=TAKE(
    REDUCE(
        {40, 0},
        OFFSET(A3:B3, , 24 - COLUMN(A:K) * 2),  以A3:B3为基点，生成11月、10月、9月……数量和均价的引用。也可改成：OFFSET(Y3:Z3,,-COLUMN(A:K)*2)
        LAMBDA(x, y,
            x + MIN(@x, @+y) * IF({1, 0}, -1, y)
        )
    ),
    ,
    -1
)

shaowu459

y值如果是常量一维数组，可以引用空单元格或利用SEQUENCE函数进行扩充。

例题：要求将A2单元格字符串中的连续+或-替换成单个+或-。


思路是可以循环将++或--替换成+或-。可以使用如下公式完成：

1. =REDUCE(A2,{"+","-"}&Z1:Z9,LAMBDA(x,y,SUBSTITUTE(x,y&y,y)))

复制代码

第二参数{"+","-"}&Z1:Z9（空单元格区域）将+和-各扩充成9次，也即REDUCE函数一共循环18次，遍历所有的+和-。每次循环时，将y&y也即++或--替换成一个y。



也可以使用以下公式完成：

1. =REDUCE(A2,{"+","-"}&T(ROW(1:99)),LAMBDA(x,y,SUBSTITUTE(x,y&y,y)))

复制代码

其中T(ROW(1:99))部分使用T函数将数字转化成空，然后再和单行数组{"+","-"}连接，将其扩充成99行。当然，也可以使用IF(ROW(1:99),{"+","-"})来扩充。

shaowu459

y也经常使用UNIQUE函数获得的不重复值数组。

如下图，A:F为销售数据，要求获得H:I列的各产品销售数量。


H2单元格输入以下公式：

1. =REDUCE({"产品名称","销售数量"},UNIQUE(A2:A18),LAMBDA(x,y,VSTACK(x,HSTACK(y,SUMIF(A:A,y,D:D)))))

复制代码

公式中UNIQUE(A2:A18)获得产品不重复值列表，作为y，然后遍历y中的每个元素，每次循环时使用SUMIF函数求得该产品所有销售数量合计，然后和产品名称横向堆积在一起。最后，再将每次循环的结果使用VSTACK函数纵向堆叠在一起。

shaowu459

原始数据是单元格区域，注意y的选取简化公式，基本原则就是公式中需要重复使用的部分可以作为y。

下图是一个传统题目，要求将A列单元格内容重复B列对应的次数：


解决问题有很多方法，这里我们主要说明y的选取问题。如果y引用B列，那么引用A列就可以使用OFFSET(y,,-1)，如果y引用A列，那么引用B列就可以使用OFFSET(y,,1)。

如果核心使用EXPAND函数，因为姓名扩充成B列的行后，从第二行开始默认用#N/A填充，所以需要使用最后一个参数填充，最后一个参数需要是A列的姓名。也即A列的姓名需要在EXPAND函数中使用两次，所以，我们可以将y定义为A列姓名。公式如下：

1. =REDUCE("结果",A2:A4,LAMBDA(x,y,VSTACK(x,EXPAND(y,OFFSET(y,,1),,y))))

复制代码

可以看到，上面的公式中引用了y两次，使用OFFSET函数一次引用B列的值。如果定义y为B列值，那么就需要使用两次OFFSET函数引用A列的姓名。

该问题还可以使用以下的几个公式完成，主要是扩充方式有所不同：

shaowu459

在源数据是更复杂表格的情况下，y的选取就显得更重要一些，虽然选取不同的循环值，公式都能做出来，但是如果能更恰当的选择循环值可以使公式更简短、清晰易读。有时候可以作为y的值有很多，不是一眼能看出的，可以多修改y的引用尝试，选取最佳的引用方式。


如下图所示，需要将D列的同行人员拆分成E列的每人一行。


在这个例子中，你可以选择循环A列序号值，通过编号去引用每行单元格区域，也可以循环任意一列，使用ROW函数或者OFFSET函数引用当前行数据。但通过观察，因为还需要拆分同行人员，因此可以直接选取D列作为循环值。参考公式如下：

1. =REDUCE(A1:F1,D2:D4,LAMBDA(x,y,VSTACK(x,IF(A1:F1=E1,TEXTSPLIT(y,,"、"),OFFSET(y,,-3,,6)))))

复制代码

公式简单说明如下：
=REDUCE(
    A1:F1,         标题行
    D2:D4,         引用待拆分的内容作为y，遍历每个y值
    LAMBDA(x, y,
        VSTACK(
            x,
            IF(
                A1:F1 = E1,              如果标题="出差人员"则保留y值拆分结果
                TEXTSPLIT(y, , "、"),    按顿号拆分y值的字符串到列
                OFFSET(y, , -3, , 6)     使用OFFSET函数引用当前行，如果标题<>"出差人员"则保留当前行数据
            )
        )
    )
)

shaowu459

再来一个涉及y选取的例子。如下图，A:E列为原始数据，要求将数据3部分有内容的单元格转化成列，如果数据3都是空，也需要保留一行空，效果如G:I列所示。



这个例子中，需要获取数据3的三列数据，也需要使用数据1和数据2的单元格，其实选择那一列都可以，有兴趣的朋友可以自己尝试下不同列的引用。下面是循环B列值的参考公式：

1. =IFNA(REDUCE(G1:I1,B2:B11,LAMBDA(x,y,VSTACK(x,HSTACK(OFFSET(y,,-1,,2)&"",IFERROR(TOCOL(OFFSET(y,,1,,3),1),""))))),"")

复制代码

公式简要说明如下：
=IFNA(               将错误值屏蔽为空
    REDUCE(
        G1:I1,
        B2:B11,      循环数据2，也即B列的值
        LAMBDA(x, y,
            VSTACK(
                x,
                HSTACK(
                    OFFSET(y, , -1, , 2) & "",        B列向左偏移1列，然后取2列获取数据1和数据2的值
                    IFERROR(TOCOL(OFFSET(y, , 1, , 3), 1), "")   B列向右偏移1列取3列，获取数据3的值，忽略空转成列。
                )
            )
        )
    ),
    ""
)

shaowu459

y也经常使用TEXTSPLIT函数拆分的结果。

如下图，A列是用-分隔的单个字符组成的字符串，要求从左到右给字符编号。


因为要遍历每个字符，所以可以使用TEXTSPLIT函数将字符串按-拆分。参考的两个公式如下：

1. =MID(REDUCE(0,TEXTSPLIT(A2,"-"),LAMBDA(x,y,x&"-"&y&ROWS(TEXTSPLIT(x,,y)))),3,99)

复制代码

公式简要说明如下：
=MID(
    REDUCE(
        0,
        TEXTSPLIT(A2, "-"),     按-拆分字符串
        LAMBDA(x, y,
            x & "-" & y &       在x后面添加-和当前字母
                ROWS(TEXTSPLIT(x, , y))    将x按当前字母拆分，拆分结果有几列当前字母编号就是几
        )
    ),
    3,
    99
)

第二个参考公式：

1. =TEXTJOIN({"","-"},,REDUCE(I1:J1,TEXTSPLIT(A2,"-"),LAMBDA(x,y,VSTACK(x,HSTACK(y,1+SUM(N(x=y)))))))

复制代码

公式简要说明如下：
=TEXTJOIN(
    {"", "-"},          使用空和-分隔符连接REDUCE函数生成的多行两列的数组。第一列是字母，第二列是对应出现次数。
    ,
    REDUCE(
        I1:J1,
        TEXTSPLIT(A2, "-"),    按-拆分字符串
        LAMBDA(x, y,
            VSTACK(x, HSTACK(y, 1 + SUM(N(x = y))))   每次横向堆叠当前字母和次数（x中已有的次数+1），再纵向堆叠在x下方
        )
    )
)

shaowu459

在REDUCE函数里再嵌套REDUCE函数时，可能需要对第一个REDUCE函数中的每一行进行提取、运算或操作。这时，可以在第二个REDUCE函数中使用SEQUENCE(ROWS(X))生成一个自然数序列作为y，然后逐行提取x中的每一行进行运算。


第一个例子，生成多列数据的组合。如下图，A:D的每列是一个属性的元素，要求生成所有属性的不重复组合，效果如右侧F:I列。


参考公式如下：

1. =REDUCE(A1:A3,B1:D1,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(t,INDEX(x,n,),VSTACK(m,IFNA(HSTACK(t,TOCOL(OFFSET(y,,,99),1)),t))))),1)))

复制代码

公示简要说明如下：
=REDUCE(
    A1:A3,
    B1:D1,      循环每一列第一个单元格
    LAMBDA(x, y,
        DROP(
            REDUCE(
                0,
                SEQUENCE(ROWS(x)),    生成1~外层REDUCE函数当前x行数的序列
                LAMBDA(m, n,
                    LET(
                        t, INDEX(x, n, ),   提取外层x的每一行
                        VSTACK(
                            m,
                            IFNA(HSTACK(t, TOCOL(OFFSET(y, , , 99), 1)), t)   将x中的每一行和当前循环列元素进行横向堆叠
                        )
                    )
                )
            ),
            1
        )
    )
)


第二个例子，按层级关系展开。如下图A:B列是不同职务上下级关系，要求展开成为右侧结果。



参考公式如下：

1. =LET(P,B2:B46,Q,C2:C46,v,REDUCE(FILTER(P,Q=0),P,LAMBDA(x,y,DROP(REDUCE(0,SEQUENCE(ROWS(x)),LAMBDA(m,n,LET(o,INDEX(x,n,),u,TAKE(o,,-1),VSTACK(m,IF(COUNTIF(Q,u),HSTACK(o,SORT(FILTER(P,Q=u))),o))))),1))),w,IFNA(v&CHAR(10)&XLOOKUP(v,B:B,A:A),""),VSTACK("第"&SEQUENCE(,COLUMNS(w))&"级",w))

复制代码

公式简要说明如下：
=LET(
    P, B2:B46,
    Q, C2:C46,
    v, REDUCE(
        FILTER(P, Q = 0),   筛选出最顶级的职务姓名
        P,
        LAMBDA(x, y,
            DROP(
                REDUCE(
                    0,
                    SEQUENCE(ROWS(x)),   生成1~外层REDUCE函数当前x行数的序列
                    LAMBDA(m, n,
                        LET(
                            o, INDEX(x, n, ),  提取外层x的每一行
                            u, TAKE(o, , -1),  提取每一行最后一个姓名用于后续筛选
                            VSTACK(m, IF(COUNTIF(Q, u), HSTACK(o, SORT(FILTER(P, Q = u))), o))  筛选u的下一级并排序后和o横向堆叠
                        )
                    )
                ),
                1
            )
        )
    ),
    w, IFNA(v & CHAR(10) & XLOOKUP(v, B:B, A:A), ""),   查询职务名称并连接
    VSTACK("第" & SEQUENCE(, COLUMNS(w)) & "级", w)      生成级别的表头
)

shaowu459

y也可以使用1~数组列数的自然数序列，提取数组中的每一列进行运算。

如下图所示，A:B列存储的是零件规格型号及对应数量，要求从上到下162个零件装一箱，装满后装下一箱。要求生成右侧E:G列的结果。



参考公式如下：

1. =LET(s,WRAPCOLS(TOCOL(IF(B3:B9>=COLUMN(A:ZZ),A3:A9,\),3),162,0),REDUCE({"规格型号","箱号","数量"},SEQUENCE(,COLUMNS(s)),LAMBDA(x,y,LET(o,INDEX(s,,y),q,UNIQUE(o),p,FILTER(q,q>0),VSTACK(x,HSTACK(p,IF(p>0,y&"号箱"),MAP(p,LAMBDA(z,SUM(N(z=o))))))))))

复制代码

公示简要说明如下：
=LET(
    s, WRAPCOLS(
                        TOCOL(IF(B3:B9 >= COLUMN(A:ZZ), A3:A9, \), 3),
                        162,
                        0),  将每个零件型号展开成对应数量那么多行，堆叠在一起后，按162个一列折叠，折叠结果有几列就是有几箱
    REDUCE(
        {"规格型号", "箱号", "数量"},
        SEQUENCE(, COLUMNS(s)),   生成一行的序列从1开始到s的列数（箱数）
        LAMBDA(x, y,
            LET(
                o, INDEX(s, , y),    循环提取s数组中的每一列
                q, UNIQUE(o),        获取当前列不重复的型号列表，也即当前箱子能装的零件所有型号
                p, FILTER(q, q > 0),
                VSTACK(
                    x,
                    HSTACK(     横向堆积下面的结果
                        p,      当前箱的零件
                        IF(p > 0, y & "号箱"),    当前箱子的编号
                        MAP(p, LAMBDA(z, SUM(N(z = o))))   求出当前箱每个零件的数量
                    )
                )
            )
        )
    )
)

橒♂蝣

感谢分享，慢慢学习