求Office2016/365中拆分斜杠分隔字符串并转置关联项目的Excel函数

需求说明 #

• 将Names列中以斜杠「/」分隔的字符串拆分后转置为行，同时关联对应A列的项目名称
• 新增项目或名称时，输出结果需自动同步更新

示例数据 #

期望输出 #

形式一（优先） #

形式二（便于数据透视表，可接受） #

实现方案 #

方案1：动态数组公式（Office 365及2016后期支持动态数组的版本） #

步骤：

1. 将数据源转为Excel表格（选中数据区域，按Ctrl+T，勾选「我的表格有标题」），新增数据会自动纳入范围。
2. 在空白单元格输入表头名称和项目，下方单元格输入以下公式：

=LET(
    proj, Table1[Col A],
    names, Table1[Names],
    split_names, TEXTSPLIT(names, "/"),
    repeat_proj, INDEX(proj, SEQUENCE(ROWS(proj),,1,1)),
    HSTACK(TOCOL(split_names, TRUE), TOCOL(repeat_proj, TRUE))
)

3. 回车后公式自动溢出填充结果，新增数据到Table1后，输出会自动更新。

2016非动态数组版本兼容公式（需按Ctrl+Shift+Enter数组输入）：

=TEXTSPLIT(TEXTJOIN("/",TRUE,IF(ROW(Table1[Col A])=TRANSPOSE(ROW(Table1[Col A])),Table1[Col A]&"|"&Table1[Names],"")),"/","|",TRUE)

方案2：Power Query（Office 2016/365通用） #

步骤：

1. 选中数据源区域，点击「数据」选项卡 →「从表格/区域」，导入Power Query编辑器。
2. 选中Names列，点击「转换」选项卡 →「拆分列」→「按分隔符」，选择「/」，拆分方式选「拆分为行」。
3. 点击「关闭并上载」，将结果加载到新工作表。
4. 自动更新设置：右键加载后的表格 →「刷新」即可同步新增数据；也可点击「数据」→「全部刷新」→「连接属性」，设置定时自动刷新。

内容的提问来源于stack exchange，提问作者Rajiv A