exceL数据批量格式转换处理

发布网友发布时间：2022-08-15 14:33

共2个回答

热心网友时间：2023-09-16 09:30

Public Sub HeBing()
cnt = 1
Sheets(1).Select
Sheets(1).Cells.Clear
Sheets(1).Cells(1, 1).Value = "序号"
Sheets(1).Cells(1, 2).Value = "姓名"
Sheets(1).Cells(1, 3).Value = "姓名 +地址"
Sheets(1).Cells(1, 4).Value = "工作表"x = ThisWorkbook.Sheets.Count
For i = 2 To x
Acol = Sheets(i).Range("IV1").End(xlToLeft).Column
Arow = Sheets(i).Range("A65535").End(xlUp).Row
For j = 2 To Arow
For k = 2 To Acol Step 2
cnt = cnt + 1
Sheet1.Cells(cnt, 1).Select
Sheet1.Cells(cnt, 1).Value = cnt - 1
Sheet1.Cells(cnt, 2).Value = Sheets(i).Cells(j, k).Value
Sheet1.Cells(cnt, 3).Value = Sheets(i).Cells(j, k + 1).Value & Sheets(i).Cells(j, k).Value
Sheet1.Cells(cnt, 4).Value = Sheets(i).Name
Next
Next
Next
End Sub

热心网友时间：2023-09-16 09:30

你说的是引用表1的数据放入表2中按照新的格式显示吧？按你的示意图，Sheet2的C1中输入公式=Sheet1!A2&B2在C1中得到的结果就是Sheet1中的A2和B2，也就是你示意图中的“地址1人名1”