Excel按分隔符拆分单元格并关联数据集输出至另一工作表的方法

一、Excel动态数组公式方案（适用于365/2021+） #

如果你的Excel支持动态数组函数，可直接用以下公式一键生成目标格式，无需手动处理溢出：

假设原数据在Sheet1的A:D列（A-C为关联列，D为逗号分隔的AHN列），在目标工作表Sheet2的A1单元格输入公式：

=LET(
    源数据, Sheet1!A:D,
    AHN列, INDEX(源数据,,4),
    拆分AHN, TEXTSPLIT(TEXTJOIN(",",TRUE,AHN列),,","),
    每行拆分数量, LEN(AHN列)-LEN(SUBSTITUTE(AHN列,",",""))+1,
    重复行索引, TOCOL(SEQUENCE(ROWS(源数据))&REPT("|",每行拆分数量),,TRUE),
    结果, HSTACK(INDEX(源数据,重复行索引,1),INDEX(源数据,重复行索引,2),INDEX(源数据,重复行索引,3),拆分AHN),
    结果
)

公式逻辑说明： #

• LET：封装变量简化公式结构
• TEXTJOIN+TEXTSPLIT：合并所有AHN单元格内容后拆分，得到单个AHN的垂直列表
• 每行拆分数量：计算每个原单元格的AHN个数（逗号数+1）
• 重复行索引：生成对应原行的重复索引，确保关联列与拆分后的AHN一一匹配
• HSTACK：合并关联列与拆分后的AHN，自动溢出到目标区域

二、VBA宏方案（适用于所有Excel版本） #

如果用旧版Excel，或需要一键批量处理，宏可自动完成拆分和导出：

1. 按Alt+F11打开VBA编辑器
2. 右键当前工作簿 → 插入 → 模块
3. 粘贴以下代码：

Sub SplitAHNToTargetSheet()
    Dim srcSheet As Worksheet, destSheet As Worksheet
    Dim lastSrcRow As Long, destRow As Long, i As Long, j As Long
    Dim ahnValues As Variant
    
    ' 替换为实际的源/目标工作表名称
    Set srcSheet = ThisWorkbook.Worksheets("Sheet1")
    Set destSheet = ThisWorkbook.Worksheets("Sheet2")
    
    ' 清空目标表原有数据（可选）
    destSheet.Cells.Clear
    
    lastSrcRow = srcSheet.Cells(srcSheet.Rows.Count, "A").End(xlUp).Row
    destRow = 1 ' 目标表起始行（有表头则设为2）
    
    ' 遍历源表数据行
    For i = 2 To lastSrcRow ' 假设源表第一行是表头，从第二行开始
        ' 拆分当前行的AHN值
        ahnValues = Split(srcSheet.Cells(i, "D").Value, ",")
        
        ' 写入每个拆分后的AHN及关联列数据
        For j = LBound(ahnValues) To UBound(ahnValues)
            destSheet.Cells(destRow, "A").Value = srcSheet.Cells(i, "A").Value
            destSheet.Cells(destRow, "B").Value = srcSheet.Cells(i, "B").Value
            destSheet.Cells(destRow, "C").Value = srcSheet.Cells(i, "C").Value
            destSheet.Cells(destRow, "D").Value = Trim(ahnValues(j)) ' 去除AHN前后空格
            destRow = destRow + 1
        Next j
    Next i
    
    ' 自动调整目标表列宽
    destSheet.Columns.AutoFit
    MsgBox "拆分完成！", vbInformation
End Sub

使用说明： #

• 修改代码中的Sheet1和Sheet2为实际工作表名
• 若源表无表头，将For i = 2 To lastSrcRow改为For i = 1 To lastSrcRow
• 运行宏：按F5或在开发工具选项卡点击“宏”选择运行

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