合并/拆分 Excel？Python、VBA轻松自动化

- 日理万妓 2022-12-10 11:27 182阅读 0赞

![format_png][]

作者：Ryoko

来源：凹凸数据

> 当你收集了 n 个人的 EXCEL 记录表，需要将它们汇成一个总表时你会怎么做呢？
> 
> 如果不通过技术手段，要一个个打开再复制粘贴也太麻烦了吧！
> 
> 此时就需要一个通过几秒钟的点击就能完成合并的工具。

![format_png 1][] 最终成品

## 合并 EXCEL ##

### VBA  实现合并 ###

不套路，下面直接放出 VBA 代码（来源于网络，经过了我修改）：

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("B200000").End(xlUp).Row + 2, 1) = Left(MyName, Len(MyName) - 4)
    For G = 1 To Sheets.Count
    Wb.Sheets(G).UsedRange.Copy .Cells(.Range("B200000").End(xlUp).Row + 1, 1)
    Next
    WbN = WbN & Chr(13) & Wb.Name
    Wb.Close False
    End With
    End If
    MyName = Dir
    Loop
    Range("B1").Select
    Application.ScreenUpdating = True
    MsgBox "共合并了" & Num & "个工作薄下的全部工作表。如下：" & Chr(13) & WbN, vbInformation, "提示"
    End Sub

看不懂没关系，不用再学 VBA，直接跑就完事儿了。那么该如何使用呢？

如下目录中放着 3 个待合并的 EXCEL 表，每个表中数据不同；

![format_png 2][]

新建一个 EXCEL 文件后打开它，用于存放合并后的数据；

![format_png 3][]

通过快捷键 Alt + F11 打开 VBA 界面；

![format_png 4][]

打开Sheet1，将上面的代码复制粘贴到其中，按 F5 运行；

![format_png 5][]

即将同目录下其他 3 个 EXCEL 文件中的数据合并至此；

![format_png 6][]

办公自动化有着各种各样的手段，下面让我们来看看万能的 Python 又是如何实现这个功能的。

### Python 实现合并 ###

直接上代码，说明见注释：

def merge_excel(dir):
        print('--- 执行合并 ---')
        filename_excel = [] # 存表名
        frames = [] # 存表内容
        d = dir.replace('/','\\\\') # 因pandsa读取路径为双斜杠，需转换
        if d.endswith('\\\\') == False: # 若为磁盘根目录则路径结尾自带\\，若为文件夹则无，需添加\\
            d = d + '\\\\'
        print("路径是：",d,"\n有以下文件：")
        for files in os.listdir(path=dir): # 遍历目录下的文件
            print(files)
            if 'xlsx' in files or 'xls' in files : # 搜索xlsx/xls后缀文件
                filename_excel.append(files) 
                df = pd.read_excel(d+files) # 读取一个表内容存入一个DataFrame
                frames.append(df)
        if len(frames)!= 0: # 若存在EXCEL表则合并保存
            result = pd.concat(frames) # 上下连接多个df
            result.to_excel(d+"合并结果表.xlsx")
    
    merge_excel("D:/某文件夹")

## 拆分 EXCEL ##

分久必合，合久必分（*这句话不是这么引用的吧*）。那么如果是要分配工作，比如把一个大表按行数分成多份小表该如何实现呢？我们还是先来看看 VBA 版本。

### VBA 实现拆分 ###

Sub ChaiFenSheet()
        Dim r, c, i, WJhangshu, WJshu, bt As Long
        r = Range("A" & Rows.Count).End(xlUp).Row
        b = InputBox("请输入分表行数")
        If IsNumeric(b) Then
               WJhangshu = Int(b)
            Else
                MsgBox "输入错误", vbOKOnly, "错误"
                End
        End If
        c = Cells(1, Columns.Count).End(xlToLeft).Column
        bt = 1 '标题行数
        'WJhangshu = 50 '每个文件的行数
        WJshu = IIf(r - bt Mod WJhangshu, Int((r - bt) / WJhangshu), Int((r - bt) / WJhangshu) + 1)
        
        '------
        Set fs = CreateObject("Scripting.FileSystemObject") '
        For i = 0 To WJshu
            Workbooks.Add
            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & Format(i + 1, String(Len(WJshu), 0)) & "." & fs.GetExtensionname(ThisWorkbook.FullName)   '扩展名
            Application.DisplayAlerts = True
            ThisWorkbook.ActiveSheet.Range("A1").Resize(bt, c).Copy ActiveSheet.Range("A1")
            ThisWorkbook.ActiveSheet.Range("A" & bt + i * WJhangshu + 1).Resize(WJhangshu, c).Copy _
            ActiveSheet.Range("A" & bt + 1)
            ActiveWorkbook.Close True
        Next
    End Sub

与合并表类似，先打开要拆分的大表，按 Alt + F11 进入 VBA 界面，再按 F5 运行代码即可。如下图所示，将一个表中的 15 个任务拆分到了 3 个新的表中

![format_png 7][] ![format_png 8][]

### Python 实现拆分 ###

拆分部分的源码由笔者的同事 yang 编写：

def split_excel(path,num):
        # print("--- 执行拆分 ---")
        p = path.replace('/', '\\\\') # 传入pd库read_excel方法的路径，含文件名
        dir = p[ : p.rfind('\\') + 1 ] # 输出被拆分表的目录，不含文件名
        sheetname = path[ path.rfind('/') + 1 :].strip('.xlsx').strip('.xlx') # 无后缀的文件名
        data = pd.read_excel(p) # 数据
        nrows = data.shape[0]  # 获取行数
        split_rows = num # 自定义要拆分的条数，即分隔成多少行一份
        count = int(nrows/split_rows) + 1  # 拆分的份数
        # print("应当拆分成%d份"%count)
        begin = 0
        end = 0
        for i in range(1,count+1):
            sheetname_temp = sheetname+str(i)+'.xlsx' # 拆分后的每个表名
            if i == 1:
                end = split_rows
            elif i == count:
                begin = end
                end = nrows
            else:
                begin = end
                end = begin + split_rows
            print(sheetname_temp)
            data_temp = data.iloc[ begin:end , : ] # [ 行范围 , 列范围 ]
            data_temp.to_excel(dir + sheetname_temp)
        # print('拆分完成')
    
    split_excel("test.xlsx",5)

作为 PyQt5 和画图爱好者，笔者又把这两段代码丢进界面里打包成一个小玩意儿。

exe 文件的图标是笔者绘制的字母 X 和 L 的组合画（*因为 XL 速读即为 EXCEL* ）。

合并和拆分功能图标也很形象（*一本正经的卖瓜 自夸*）。

![format_png 1][]

exe和完整源码已上传，欢迎下载使用（2种方式）！

*  奶牛快传（不限速）：https://alltodata.cowtransfer.com/s/7ec265ff021d43
 *  github：https://github.com/SeonPan/xltools

## 福利时间 ##

安利一本????：《**Python机器学习一本通**》此书结合了Python和机器学习两个热门领域，通过易于理解的知识讲解，帮助读者学习和掌握机器学习。217个实训案例+28集同步教学视频+调试通过的源代码+数据集，将算法与编程进行了完美融合。点击下图可看详情/购买！????

![format_png 9][]

感谢北京大学出版社的支持！「凹凸数据」???? 继续**300积分**免费兑换啦，阅读原文直达

![format_png 10][]

[![format_png 11][]][format_png 11 1]

[![format_png 12][]][format_png 12 1]

![format_png 13][]![format_png 14][]

[format_png]: /images/20221123/c35c5e3c2d4c456ead6b8b0770b2791e.png
[format_png 1]: /images/20221123/d98dc0115e4b4265bdd794fc343af84b.png
[format_png 2]: /images/20221123/339eeb80867b43ef8e70f466d2d1a628.png
[format_png 3]: /images/20221123/916f60766c664f4b9779387cf4d5ad8c.png
[format_png 4]: /images/20221123/1bbd3f19ec5a4ed4b83c664e01c6a51c.png
[format_png 5]: /images/20221123/05f6841583484cd2adcbf1e1fcf1e12d.png
[format_png 6]: /images/20221123/8029b90b16d0449f833e23df0d3a639f.png
[format_png 7]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90WFlpY3Q0MHhmTGo3ZWc4YUxLeFhuU3hub2hKcmlhZzV4cXFMQ2ZvQ3ppY2R3QXVpY2F3YkRmeVRRM1BpYjRkdzV4QnhqU05OU2ljaWNjUGRhVmljb0lnV0FHRHZRLzY0MA?x-oss-process=image/format,png
[format_png 8]: /images/20221123/c0329b8e6101403fac14d439060e6909.png
[format_png 9]: /images/20221123/57ea215708a54b48904d1ae5bf5c4a86.png
[format_png 10]: /images/20221123/62793b0a65b14a14920140722f8ffb62.png
[format_png 11]: /images/20221123/19b502e77b65450a8a0ad9e32815f23c.png
[format_png 11 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4ed195c939588328d7cb8175109cfa4690ba4361a2ce7efecb215daeedd98a57f006ebdf97&idx=2&mid=2247496721&scene=21&sn=92ffae36b72de91fbb098f3034636345#wechat_redirect
[format_png 12]: /images/20221123/95751daff4bf4e9ba4462c099962728b.png
[format_png 12 1]: http://mp.weixin.qq.com/s?__biz=MzU5Nzg5ODQ3NQ%3D%3D&chksm=fe4ed6ddc9395fcb4cd4c9a946c7eaa8f1d281dfcba82655b6de4e9bc0ff359cb2f3d7da4623&idx=1&mid=2247496537&scene=21&sn=f185dd09ca14d99a7a803954aa6157ff#wechat_redirect
[format_png 13]: https://imgconvert.csdnimg.cn/aHR0cHM6Ly9tbWJpei5xcGljLmNuL21tYml6X2dpZi90WFlpY3Q0MHhmTGhwZjNzcEJTbkx2Zk56WklaNkVLb0JBYmsxd1d4TTFqaWNZVTQ0clF2c2FvUmN6VHRpYlJTOFVtbWI3Vk1hTldvM1M3cjRnMVlETnduQS82NDA?x-oss-process=image/format,png
[format_png 14]: /images/20221123/78bed2e7b352483689b414b66aa9d44d.png