Excel如何将多列中分号分隔的对应值拆分为行？

原始Excel表格：

需要将各列中分号分隔的对应值拆分为行，得到目标表格：

尝试过Power Query的「按分隔符拆分列→拆分为行」操作，但出现大量重复值，去重困难，希望找到无需使用宏的实现方法。

编辑补充：Col1为单个值，拆分其他列时需重复对应显示。

方法一：Power Query正确操作（无重复值） #

你之前的操作错误在于没有同时选中所有需要拆分的列，正确步骤如下：

• 选中原始数据区域，点击「数据」选项卡→「从表格/区域」，确认数据包含标题，进入Power Query编辑器。
• 按住Ctrl键选中Col2、Col3、Col4三列，点击「转换」选项卡→「拆分列」→「按分隔符」。
• 在弹出的对话框中设置：
  • 分隔符选择「自定义」，输入;
  • 高级选项选择「拆分为行」
• 点击「确定」后，点击「关闭并上载」，即可得到无重复值的目标结果。

方法二：Excel动态数组公式法（适用于365/2021版本） #

利用内置函数实现，无需Power Query：

• 在空白区域的首行输入标题：F1填Col1，G1填Col2，H1填Col3，I1填Col4。
• 在F2单元格输入公式，按回车后自动溢出所有Col1的重复值：

  =TOCOL(IFERROR(TEXTSPLIT(TEXTJOIN(";",,REPT(Col1&";",LEN(Col2)-LEN(SUBSTITUTE(Col2,";",""))+1)),";"),""),3)
  

• 在G2单元格输入公式，自动拆分Col2的所有值：

  =TOCOL(TEXTSPLIT(TEXTJOIN(";",,Col2),";"),3)
  

• 在H2单元格输入公式，自动拆分Col3的所有值：

  =TOCOL(TEXTSPLIT(TEXTJOIN(";",,Col3),";"),3)
  

• 在I2单元格输入公式，自动拆分Col4的所有值：

  =TOCOL(TEXTSPLIT(TEXTJOIN(";",,Col4),";"),3)
  

公式简要说明 #

• REPT：根据对应行的拆分次数（分号数量+1）重复Col1的值
• TEXTJOIN+TEXTSPLIT：将整列的分号分隔文本合并后拆分为单个值数组
• TOCOL：将多维数组转为单列，自动忽略空值

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