文本拆分多级列表

发表于 更新于 分类于 ， 阅读次数： 评论数：

后补文章，说起来已经是一年半前的故事了。时excel刚更新lambda不久，偶然看到大佬们各种操作，秀的我万分羡慕，所以后面浅学了一些新东西。这篇文章便是当时的一些操作记录

题目

为两行文本，其内容为：

合同中心（发起、审批、用印、登记）
档案中心（电子档案、资质档案）

内容的左边为一级内容，括号内二级内容，其中一级、二级内容需要不同列存储、括号里的同一级同一列显示效果见图片

解法1

首先将使用LEFT(y, FIND("（", y))得到C列的内容；
使用TEXTSPLIT(MID(y, FIND("（", y) + 1, LEN(y) - FIND("（", y) - 1), , "、", TRUE)将括号内的二级内容拆分得到D列内容；
使用HSTACK()将两列组合到一起；
使用IFNA()解决由于C列和D列不等长导致出现#NA的问题；
使用VSTACK()将第一行内容和第二行组合在一起；
使用REDUCE()实现先传第一行进行处理完成后再传第二行进行处理，并将第二行第二行结果追加到第一行后面作为最新的结果（追加为广义上的追加，这是具体计算为VSTACK()）；
最后使用DROP解决由于REDUCE()初始值为空行导致多了一行空数据的问题。
以下为完整公式：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
=DROP(
    REDUCE(
        {"", ""},
        A2:A3,
        LAMBDA(x, y,
            VSTACK(
                x,
                IFNA(
                    HSTACK(
                        LEFT(y, FIND("（", y)),
                        TEXTSPLIT(MID(y, FIND("（", y) + 1, LEN(y) - FIND("（", y) - 1), , "、", TRUE)
                    ),
                    LEFT(y, FIND("（", y))
                )
            )
        )
    ),
    1
)

解法2

思路与解法1类似，相异点为取（前的字符串使用TEXTBEFORE()、为取（后的字符串使用TEXTAFTER()，累加器初始值是0而非{"", ""},,完整公式如下：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
=DROP(
    REDUCE(
        0,
        A2:A3,
        LAMBDA(x, y,
            VSTACK(
                x,
                IFNA(
                    HSTACK(TEXTBEFORE(y, "（"), TEXTSPLIT(TEXTAFTER(LEFT(y, LEN(y) - 1), "（"), , "、", TRUE)),
                    TEXTBEFORE(y, "（")
                )
            )
        )
    ),
    1
)

解法3

解法3与解法2相似，解法二TEXTBEFORE(y, "（")出现多次，在解法三中使用let进行了封装

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
=DROP(
    REDUCE(
        0,
        A2:A3,
        LAMBDA(x, y,
            VSTACK(
                x,
                LET(
                    a, TEXTBEFORE(y, "（"),
                    IFNA(HSTACK(a, TEXTSPLIT(TEXTAFTER(LEFT(y, LEN(y) - 1), "（"), , "、", TRUE)), a)
                )
            )
        )
    ),
    1
)

解法4

该解决办法大体思路与解法三相似，在初始处理上有几点相异：
- 其一为单行处理时利用TEXTSPLIT()的拆分参数可为数组的特性将其全部拆分为行数组；
- 其二为利用一级内容都在第一列的特点使用TAKE()将第一列单独存储；
- 其三为使用DROP()将第一列的元素删除后使用TOCOL()将行数据转换为列数组；
- 其四为使用SUBSTITUTE()统一将）字符去除

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
=SUBSTITUTE(
    DROP(
        REDUCE(
            "",
            A2:A3,
            LAMBDA(x, y,
                VSTACK(
                    x,
                    LET(
                        a, TEXTSPLIT(y, {"（", "、"}),
                        b, TAKE(a, , 1),
                        c, TOCOL(DROP(a, , 1)),
                        IFNA(HSTACK(b, c), b)
                    )
                )
            )
        ),
        1
    ),
    "）",
    ""
)

解法5

这是另一种解法，首先使用CONCAT(A2:A3)将要处理的内容拼接成一个字符串，然后使用TEXTSPLIT()将其分为两行多列的数组并存入s,然后使用TAKE(s, , 1) & ":" & DROP(s, , 1),将每一行的第一列的值先与:拼接后再分别与所属行的当前行每一个元素拼接，然后使用TOCOL()将两行多列数据转换为列数组后存入t，然后使用TEXTBEFORE(t, ":"), TEXTAFTER(t, ":")将数组t按:拆分为两列,最后使用HSTACK()合并展现

1
2
3
4
5
=LET(
    s, TEXTSPLIT(CONCAT(A2:A3), {"（", "、"}, "）", TRUE),
    t, TOCOL(TAKE(s, , 1) & ":" & DROP(s, , 1), 3),
    HSTACK(TEXTBEFORE(t, ":"), TEXTAFTER(t, ":"))
)