excel文件合并及字段分割合并

excel文件合并及字段分割合并 Execl文件合并 步骤: 1.把要合并的Excel放到一个文件夹里,在这里新建一个Excel文件 2.Alt+F11 3.调出工程资源管理器 4.把代码复制进去,然后运行 5.在弹出的文件选择对话框里选中所有你要合并的Excel文件 Sub 合并当前目录下所有工作簿的全部工作() Dim MyPath, MyName, AWbName Dim Wb As Workbook, WbN As String Dim G As Long Dim Num As Long Dim BOX As String Application.ScreenUpdating = False MyPath = ActiveWorkbook.Path MyName = Dir(MyPath & "\" & "*.xls") AWbName = ActiveWorkbook.Name Num = 0 Do While MyName <> "" If MyName <> AWbName Then Set Wb = Workbooks.Open(MyPath & "\" & MyName) Num = Num + 1 With Workbooks(1).ActiveSheet .Cells(.Range("A65536").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4) For G = 1 To Sheets.Count Wb.Sheets(G).UsedRange.Copy .Cells(.Range("A65536").End(xlUp).Row + 1, 1) Next WbN = WbN & Chr(13) & Wb.Name Wb.Close False End With End If MyName = Dir Loop Range("A1").Select Application.ScreenUpdating = True MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下:" & Chr(13) & WbN, vbInformation, "提示" End Sub Excel表合并 步骤: 1.新建一个excel表 2.Alt+F11 3.调出工程资源管理器 4.把代码复制进去,然后运行 5.在弹出的文件选择对话框里选中所有你要合并的Excel文件 Sub CombineWorkbooks() Dim FilesToOpen Dim x As Integer On Error GoTo ErrHandler Application.ScreenUpdating = False FilesToOpen = Application.GetOpenFilename _ (FileFilter:="Microsoft Excel Files (*.xls), *.xls", _ MultiSelect:=True, Title:="Files to Merge") If TypeName(FilesToOpen) = "Boolean" Then MsgBox "No Files were selected" GoTo ExitHandler End If x = 1 While x <= UBound(FilesToOpen) Workbooks.Open Filename:=FilesToOpen(x) Sheets().Move After:=ThisWorkbook.Sheets _ (ThisWorkbook.Sheets.Count) x = x + 1 Wend ExitHandler: Application.ScreenUpdating = True Exit Sub ErrHandler: MsgBox Err.Description Resume ExitHandler End Sub 字段分割和合并(excel本身实现) 字段分割 打开excel 找人数据----》分列 字段合并 用函数CONCATENATE() 如: CONCATENATE("2005","-","a","-","a1") 你在数据表第一行数据后的空列里选择这个函数 Excel里函数非常多,还可以用数据透视表进行数据分析,很方便的