表格合并和拆分（公式及详解）

lgcmeli

最近研究多表合并。看了很多方法，vba除外，感觉都过于复杂。思考出新方法。

shaowu459

lgcmeli 发表于 2014-9-29 11:15
最近研究多表合并。看了很多方法，vba除外，感觉都过于复杂。思考出新方法。

除了vba，没有太好办法。因为都要求自动扩展，自动识别工作表名称，方便合并和拆分。
我这个例子如果只弄合并或者拆分都会简单一点，但是速度不快，并且初学者学习起来稍微难一点。

就目前来说，有几个问题：
1）用公式得到全部工作表名称，得用函数
2）合并的话得识别每个行数，也得用涉及多表的函数
3）逐个列出结果，需要用数组公式，慢
4）如果再设置自动拆分，就需要提取工作表名称，并嵌套在公式中，长

所以大量数据还是vba，这些方法就是这个学习而已。

lgcmeli

1，得到工作表名称，肯定是宏表函数了，几十个上百个表还是速度可以的。而且一旦工作表数量暂时固定，也没必要老刷新宏表函数。

2，合并的每个表函数，容易得到indirect加counta。最好辅助列，不要在名称里一步到位。

3，根据每个表的行数重复表名，一般用lookup。不用数组公式，至于countif都不推荐使用，慢。

4，自动拆分，如果表名一定情况下固定，也是没什么速度很快。可以预先设想好那些被拆分出的表名，然后找一般都是辅助列加vlookup什么的。

所有情况下，都避免使用复杂数组公式，复杂速度不可能提高。

slywtj123

谢谢分享！

shaowu459

lgcmeli 发表于 2014-9-29 13:11
1，得到工作表名称，肯定是宏表函数了，几十个上百个表还是速度可以的。而且一旦工作表数量暂时固定，也没必 ...

没错。如果可以考虑部分手动操作和辅助列就可以快很多。
比如我做的拆分公式，就是属于自动型的。鼠标拖动复制一个工作表，将工作表名称改成要拆分的内容就可以自动用数组公式生成。缺点就是慢，如果用辅助列或者单独生成工作表后用公式就可以快很多了。

67598985

版主，还在没？
看到你发的帖子，正是我苦苦寻找的方法呀
但是我是菜鸟，研究了半天都没有看明白该怎么弄，能够帮我设置一下吗？或者给我说说1.2.3一部步该怎么做呢。跪求跪求

前面几个表格的格式都是一样的，汇总表格和长期活动表格格式一样，和其他表格的区分就是后面多了几列

拜托

shaowu459

67598985 发表于 2014-12-26 13:56
版主，还在没？
看到你发的帖子，正是我苦苦寻找的方法呀
但是我是菜鸟，研究了半天都没有看明白该怎么弄 ...

兄弟，这个用公式很慢啊。还是找vba方面的来帮忙吧。

xutianlin

还是用VBA比较好

Sub 合并数据()

Dim wk As Workbook, wsh As Worksheet, i As Long, brr()

a = Dir("C:\Users\Administrator\Desktop\新建文件夹 (3)\*.xlsm")
Workbooks.Open ("C:\Users\Administrator\Desktop\新建文件夹 (3)\" & a)
Do
a = Dir
If a <> "" Then
Workbooks.Open ("C:\Users\Administrator\Desktop\新建文件夹 (3)\" & a)
Else
Exit Do
End If
Loop

For Each wk In Workbooks
    a = wk.Name
    If wk.Name <> "汇总.xlsm" Then
    wk.Activate
    For Each wsh In Worksheets
        arr = wsh.Range("a2:e" & wsh.Range("e65536").End(xlUp).Row)
        k = k + UBound(arr)
        ReDim Preserve brr(1 To 6, 1 To k)
        For i = 1 To UBound(arr)
            n = n + 1
            brr(1, n) = arr(i, 1)
            brr(2, n) = arr(i, 2)
            brr(3, n) = arr(i, 3)
            brr(4, n) = arr(i, 4)
            brr(5, n) = arr(i, 5)
            brr(6, n) = wk.Name
            Next i
          Next
          End If
        Next
Sheet1.Range("a2").Resize(n, 6) = Application.Transpose(brr)

Sheet1.Range("f2:f" & Sheet1.Range("f65536").End(xlUp).Row).Replace What:=".xlsm", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

For Each wk In Workbooks
    If wk.Name <> "汇总.xlsm" Then
    wk.Activate
    wk.Close True
    End If
    Next
End Sub

67598985

xutianlin 发表于 2014-12-28 19:49
还是用VBA比较好

Sub 合并数据()

亲，可以帮我设置一下不？我是菜鸟啊，我做不来，摸不着头脑，元旦就要启用新表了，现在学也来不及了，多谢多谢

xutianlin

67598985 发表于 2014-12-29 17:13
亲，可以帮我设置一下不？我是菜鸟啊，我做不来，摸不着头脑，元旦就要启用新表了，现在学也来不及了，多 ...

你发个帖子，上传附件我看看再说。。。。。。