如何在Excel或Alteryx中实现多行数据自动转置分组及多表合并

Excel 实现方案 #

方法1：公式+辅助列（无需VBA） #

• 标记记录组：假设原始数据在A列，B1输入=IF(LEFT(A1,4)="name",ROW(),B1)，下拉填充。每个以name:开头的行会生成唯一组号，同组后续行继承该组号。
• 合并同组多行内容：C1输入数组公式=TEXTJOIN(" ",TRUE,IF(B:B=B1,A:A,""))，按Ctrl+Shift+Enter后下拉，同组所有行内容会合并成一行（格式如name: xxx address: xxx xxx taxid: xxx）。
• 拆分字段到列：用函数提取对应值：
  • 姓名列：=TRIM(TEXTAFTER(TEXTBEFORE(C1,"address:"),"name:"))
  • 地址列：=TRIM(TEXTAFTER(TEXTBEFORE(C1,"taxid:"),"address:"))
  • 税号列：=TRIM(TEXTAFTER(C1,"taxid:"))
• 合并多工作表：用Power Query批量处理：
  1. 点击「数据」选项卡 → 「获取数据」→ 「自文件」→ 「自工作簿」
  2. 选择目标文件，在导航器勾选所有工作表，点击「转换数据」
  3. 在编辑器中合并所有工作表数据，整理后加载回Excel

方法2：VBA宏（全自动批量处理） #

打开VBA编辑器（Alt+F11），插入模块后粘贴以下代码，运行后自动生成合并结果表：

Sub CleanAndCombineData()
    Dim ws As Worksheet, newWs As Worksheet
    Dim lastRow As Long, i As Long, groupNum As Long
    Dim nameVal As String, addrVal As String, taxVal As String
    
    Set newWs = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    newWs.Name = "CombinedResult"
    newWs.Range("A1:C1") = Array("Name", "Address", "TaxID")
    
    groupNum = 1
    For Each ws In ThisWorkbook.Sheets
        If ws.Name <> "CombinedResult" Then
            lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            i = 1
            Do While i <= lastRow
                '提取姓名
                nameVal = Trim(Mid(ws.Cells(i, "A").Value, InStr(ws.Cells(i, "A").Value, ":") + 1))
                i = i + 1
                '提取跨多行的地址（直到遇到taxid行）
                addrVal = ""
                Do While Left(ws.Cells(i, "A").Value, 6) <> "taxid:" And i <= lastRow
                    addrVal = addrVal & " " & Trim(ws.Cells(i, "A").Value)
                    i = i + 1
                Loop
                addrVal = Trim(addrVal)
                '提取税号
                taxVal = Trim(Mid(ws.Cells(i, "A").Value, InStr(ws.Cells(i, "A").Value, ":") + 1))
                i = i + 1
                '写入结果表
                newWs.Cells(groupNum + 1, "A").Value = nameVal
                newWs.Cells(groupNum + 1, "B").Value = addrVal
                newWs.Cells(groupNum + 1, "C").Value = taxVal
                groupNum = groupNum + 1
            Loop
        End If
    Next ws
End Sub

Alteryx 实现方案 #

1. 批量导入多工作表：用Input Data工具选择目标工作簿，勾选「所有工作表」加载全部数据。
2. 标记记录组：添加Multi-Row Formula工具，新建GroupID字段，公式：

   IF Left([Field1],4) = "name" THEN [Row-1:GroupID]+1 ELSE [Row-1:GroupID] ENDIF
   

   设置初始值为1，实现同组数据标记相同ID。
3. 合并同组内容：添加Summarize工具，按GroupID分组，对Field1选择「Concatenate with separator」，分隔符设为空格。
4. 正则拆分字段：添加Regex工具，分别匹配提取字段：
   • 姓名：正则表达式(?<=name: ).*?(?= address:)，输出到新字段Name
   • 地址：正则表达式(?<=address: ).*?(?= taxid:)，输出到新字段Address
   • 税号：正则表达式(?<=taxid: ).*，输出到新字段TaxID
5. 导出结果：用Output Data工具将整理后的数据导出为表格即可。

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