如何让Excel拆分逗号分隔单元格并重复整行其余数据？

方法1：Power Query（最推荐，无需公式/VBA） #

这是处理数千条数据最省心的方式：

1. 选中包含表头的数据区域，点击「数据」选项卡 → 「从表格/区域」（Excel 2016及以后版本可用，旧版本直接找「Power Query」选项卡）。
2. 在Power Query编辑器中，选中需要拆分的列（比如D列Ingredients）。
3. 点击「转换」选项卡 → 「拆分列」→ 「按分隔符」，选择逗号，勾选「拆分为行」，点击确定。
4. 处理完成后，点击「主页」→ 「关闭并上载」，结果会自动生成在新工作表中。

方法2：公式组合（适合不想用Power Query的场景） #

假设数据从A1开始，表头在第1行，数据范围为第2行到第1000行（可按需调整）：

1. 计算每行拆分后的行数：在空白列（如E列）的E2单元格输入公式 =LEN(D2)-LEN(SUBSTITUTE(D2,",",""))+1，下拉填充至所有数据行，该公式用于统计D列每个单元格的逗号分隔项数量。
2. 生成连续行号序列：在F列F2输入1，F3输入 =F2+E2，下拉填充，最终值即为拆分后的总数据行数。
3. 提取重复的主数据：
   • 在新工作表的A2单元格输入数组公式：=INDEX(原表!A:A,SMALL(IF(ROW(原表!$A$2:$A$1000)<=ROW(原表!$A$2:$A$1000)+原表!$E$2:$E$1000-1,ROW(原表!$A$2:$A$1000)),ROW(A1)))，按Ctrl+Shift+Enter确认（Excel 365版本直接回车即可），再向右填充至非拆分列（如C列）。
4. 提取拆分后的单个成分：
   • 在新工作表的D2单元格输入数组公式：=TRIM(MID(SUBSTITUTE(INDEX(原表!D:D,SMALL(IF(ROW(原表!$A$2:$A$1000)<=ROW(原表!$A$2:$A$1000)+原表!$E$2:$E$1000-1,ROW(原表!$A$2:$A$1000)),ROW(A1))),",",REPT(" ",100)),(ROW(A1)-SUM(原表!$E$2:INDEX(原表!$E$2:$E$1000,SMALL(IF(ROW(原表!$A$2:$A$1000)<=ROW(原表!$A$2:$A$1000)+原表!$E$2:$E$1000-1,ROW(原表!$A$2:$A$1000)),ROW(A1))-1)))*100+1,100))，按数组公式快捷键确认后下拉填充。

方法3：VBA宏（适合有基础的用户） #

按Alt+F11打开VBA编辑器，插入模块，粘贴以下代码（注意修改代码中的工作表名和列参数）：

Sub SplitIngredients()
    Dim ws As Worksheet, newWs As Worksheet
    Dim lastRow As Long, i As Long, j As Long, k As Long
    Dim ingredients As Variant
    
    Set ws = ThisWorkbook.Sheets("原表") '替换为你的原始数据工作表名称
    Set newWs = ThisWorkbook.Sheets.Add(After:=ws)
    newWs.Name = "拆分结果"
    
    '复制表头至新表
    ws.Rows(1).Copy newWs.Rows(1)
    k = 2 '结果表的起始数据行
    
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    For i = 2 To lastRow
        ingredients = Split(ws.Cells(i, "D").Value, ",") 'D列为需拆分的Ingredients列
        For j = LBound(ingredients) To UBound(ingredients)
            '复制非拆分列的数据
            ws.Cells(i, "A").Resize(1, 3).Copy newWs.Cells(k, "A")
            '写入单个成分（去除多余空格）
            newWs.Cells(k, "D").Value = Trim(ingredients(j))
            k = k + 1
        Next j
    Next i
End Sub

运行宏即可自动生成拆分后的结果。

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