如何用公式拆分多值列并关联另一列生成重复数据

原始数据 #

预期结果 #

适用Excel 365/2021（支持动态数组） #

直接用动态数组公式就能一键生成结果，不用手动下拉填充：

1. 生成拆分后的First Name列：在空白单元格输入以下公式，会自动溢出所有拆分后的名字

=TEXTSPLIT(TEXTJOIN(",",TRUE,A2:A3),",")

2. 生成对应Surname列：在旁边单元格输入这个公式，自动匹配对应行的姓氏

=INDEX(B2:B3,ROUNDUP(SEQUENCE(COUNTA(TEXTSPLIT(TEXTJOIN(",",TRUE,A2:A3),","))/BYROW(A2:A3,LAMBDA(x,LEN(x)-LEN(SUBSTITUTE(x,",",""))+1)),0))

或者用更简洁的组合公式，直接生成完整的两列结果：

=LET(
    拆分名字, TEXTSPLIT(TEXTJOIN(",",,A2:A3),","),
    重复姓氏, REPT(B2:B3, LEN(A2:A3)-LEN(SUBSTITUTE(A2:A3,",",""))+1),
    TEXTSPLIT(TEXTJOIN("|",,拆分名字&"|"&重复姓氏),"|")
)

适用旧版Excel（无动态数组） #

旧版需要结合数组公式和手动填充：

1. 先计算总拆分行数：输入公式得到最终结果的总行数（这里是6）

=SUMPRODUCT(LEN(A2:A3)-LEN(SUBSTITUTE(A2:A3,",",""))+1)

2. 提取First Name：在第一个结果单元格（比如C2）输入以下公式，按Ctrl+Shift+Enter确认（不是单独回车），然后下拉到计算出的总行数

=INDEX(TRIM(MID(SUBSTITUTE($A$2:$A$3,",",REPT(" ",99)),(ROW(INDIRECT("1:"&SUMPRODUCT(LEN($A$2:$A$3)-LEN(SUBSTITUTE($A$2:$A$3,",",""))+1)))-1)*99+1,99)),ROW(A1))

3. 提取对应Surname：在旁边单元格（比如D2）输入以下数组公式，同样按Ctrl+Shift+Enter确认后下拉

=INDEX($B$2:$B$3,MATCH(TRUE,SUMPRODUCT(--(ROW($A$2:$A$3)<=ROW($A$2:$A$3))*(LEN($A$2:$A$3)-LEN(SUBSTITUTE($A$2:$A$3,",",""))+1))>=ROW(A1),0))

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