如何使用区域公式实现Excel列值展开（将Table 1转为Table 2）

原始表格（Table 1）与目标表格（Table 2） #

Table 1 #

Table 2（目标结构） #

适用公式 #

1. Excel 365/2021（支持动态数组，自动溢出） #

直接在D1单元格输入以下公式，回车后会自动生成完整的D、E列数据：

=LET(
    原始A列, Table1[A],
    原始B列, Table1[B],
    拆分后B值, TEXTSPLIT(TEXTJOIN(",",,原始B列), ","),
    每行重复次数, LEN(原始B列) - LEN(SUBSTITUTE(原始B列, ",", "")) + 1,
    重复后的A列, TOCOL(IF(SEQUENCE(ROWS(原始A列))=SEQUENCE(,ROWS(原始A列)), 原始A列, ),,每行重复次数),
    HSTACK(重复后的A列, 拆分后B值)
)

或者更简洁的简化版：

=HSTACK(TOCOL(Table1[A],,TEXTSPLIT(TEXTJOIN(",",,Table1[B]), ",")), TEXTSPLIT(TEXTJOIN(",",,Table1[B]), ","))

2. 旧版Excel（不支持动态数组，需用数组公式） #

先选中D1:E5（目标区域，行数等于所有拆分后值的总数），然后输入以下公式，按 Ctrl+Shift+回车 完成数组公式输入：

• D列（重复A列值）公式：

=IFERROR(INDEX(Table1[A],SMALL(IF(LEN(Table1[B])-LEN(SUBSTITUTE(Table1[B],",",""))+1>=SEQUENCE(MAX(LEN(Table1[B])-LEN(SUBSTITUTE(Table1[B],",",""))+1)),ROW(Table1[A])-ROW(Table1[[#Headers],[A]]),""),ROWS($1:1))),"")

• E列（拆分B列单个值）公式：

=IFERROR(TRIM(MID(SUBSTITUTE(TEXTJOIN(",",,Table1[B]),",",REPT(" ",99)),(ROWS($1:1)-1)*99+1,99)),"")

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