表头不同的文件想合并,本来我想说,不规则,你先整理呀!但是经常还是有人问,想必现实真的有乱的不成样子的表需要合并!那么我们就来聊聊吧! 偷个懒,我就去论坛找个素材,N个Exce文件,每个文件的表头部分有不同 现在需要合并,一般的程序都是按照位置合并的,并不通用,无法处理这个问题! 我们来看看如何处理!目前推荐的处理方法是PQ,如果你的版本目前不支持,再考虑后面提供的VBA代码! Power Query处理这个问题真的是非常的简单,我们可以考虑获取到所有表的标题去重,然后去展开数据!
/*写上你自己需要合并的路径*/ 文件夹路径 = "E:\不同表头", 源 = Table.Combine( List.Combine(List.Transform(Folder.Files(文件夹路径 )[Content], each Table.Buffer(Excel.Workbook(_,true))[Data]) ) ) in 源 '作者:E精精 '下载:公众号- Excel办公实战 '------------------------------------------------------------------- Sub ComData() Dim sPath As String '选择文件夹 With Application.FileDialog(msoFileDialogFolderPicker) If .Show Then sPath = .SelectedItems(1) sPath = sPath & IIf(VBA.Right(sPath, 1) = "\", "", "\") Else End End If End With Dim file As String, ShtCount As Long Dim dTitle As Object, dData As Object Dim Sht As Worksheet, wb As Workbook file = Dir(sPath & "*.xl*") Set dTitle = CreateObject("Scripting.dictionary") Set dData = CreateObject("Scripting.dictionary") Dim ShtName As String, wbName As String t = Timer '标题和数据分别装入字典备用 Application.ScreenUpdating = False Do While Len(file) > 0 Set wb = Workbooks.Open(sPath & file, False, True) For Each Sht In wb.Worksheets ShtCount = ShtCount + 1 arr = Sht.Range("A1").CurrentRegion.Value ShtName = Sht.Name '工作表名称 wbName = Split(wb.Name, ".")(0) '文件名 dData(wbName & "|" & ShtName) = arr For i = 1 To UBound(arr, 2) If Not dTitle.exists(arr(1, i)) Then k = k + 1 dTitle(arr(1, i)) = k End If Next Next wb.Close 0 file = Dir Loop Application.ScreenUpdating = True Dim brr() '+2 文件名+表名 ReDim brr(1 To 100000, 1 To dTitle.Count + 2) For Each eve In dData.keys() arr = dData(eve) For i = 2 To UBound(arr) n = n + 1 tp = Split(eve, "|") brr(n, 1) = tp(0) '文件名 brr(n, 2) = tp(1) '表名 For j = 1 To UBound(arr, 2) brr(n, dTitle(arr(1, j)) + 2) = arr(i, j) Next Next Next '写入汇总表,没有的自己建一个 With Sheets("汇总表") .Cells.Clear .Range("A1:B1") = Array("文件名", "表名") .Range("C1").Resize(1, dTitle.Count) = dTitle.keys() .Range("A2").Resize(n, dTitle.Count + 2) = brr End With MsgBox "汇总完成!共汇总:" & ShtCount & "个表!" _ & vbCrLf & "用时:" & Format(Timer - t, "0.00s") End Sub |
|