Excel文本拆分列需求：将含逗号分隔字段的表格转换为规范格式

输入表格 #

需求说明 #

需将上述表格转换为每行仅含单个资产序列号和单个许可证名称的格式，其余列（销售订单、资产型号等）自动重复对应行的内容。

已尝试的方法及问题 #

使用Replace函数结合转置操作，无法自动填充空白列；使用文本分列（Text-to-Column）功能，未达到预期拆分效果。

解决方案一：Power Query批量处理（推荐） #

这是Excel内置的高效数据处理工具，无需手动公式：

1. 选中表格所有数据，点击「数据」选项卡 → 「从表格/区域」，勾选「我的表格有标题」后进入Power Query编辑器。
2. 拆分并清洗资产序列号列：
   • 选中该列，点击「转换」→ 「拆分列」→ 「按分隔符」，选择逗号「,」，勾选「拆分到行」，点击确定。
   • 选中拆分后的资产序列号列，点击「转换」→ 「格式」→ 「修剪」，去除内容前后的多余空格。
3. 拆分并清洗许可证名称列：
   • 重复步骤2的操作，将该列拆分到行并修剪空格。
4. 点击「主页」→ 「关闭并上载」，处理后的数据会自动导出到新工作表，即为目标格式。

解决方案二：动态数组公式（适用于Excel 365/2021） #

假设源数据在A2:G3区域，在新工作表的A2单元格输入以下公式，按回车后自动生成所有行：

=LET(
    源数据,A2:G3,
    订单,INDEX(源数据,,1),
    资产号,TEXTSPLIT(TEXTJOIN(",",TRUE,INDEX(源数据,,2)),,", ",TRUE),
    型号,INDEX(源数据,,3),
    许可类别,INDEX(源数据,,4),
    许可类型,INDEX(源数据,,5),
    许可名称,TEXTSPLIT(TEXTJOIN(",",TRUE,INDEX(源数据,,6)),,", ",TRUE),
    客户,INDEX(源数据,,7),
    重复次数,MAX(LEN(资产号)-LEN(SUBSTITUTE(资产号,",",""))+1,LEN(许可名称)-LEN(SUBSTITUTE(许可名称,",",""))+1),
    扩展订单,TOCOL(IF(SEQUENCE(,重复次数),订单),3),
    扩展型号,TOCOL(IF(SEQUENCE(,重复次数),型号),3),
    扩展类别,TOCOL(IF(SEQUENCE(,重复次数),许可类别),3),
    扩展类型,TOCOL(IF(SEQUENCE(,重复次数),许可类型),3),
    扩展客户,TOCOL(IF(SEQUENCE(,重复次数),客户),3),
    HSTACK(扩展订单,资产号,扩展型号,扩展类别,扩展类型,许可名称,扩展客户)
)

公式会自动匹配资产序列号和许可证名称的拆分数量，同步填充其他列的重复内容。

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