用vba执行python python和vba操作excel

转载

温柔一刀 2023-12-12 17:31:20

文章标签 用vba执行python 数据测试数据微信公众号 文章分类 Python 后端开发

1.Excel表格合并

我们在日常工作中经常会导出一些数据，但是这些数据较大可能是按照某个分类形成的单独表格，比如每一天的数据，每个品牌的数据等。

但是，我们在进行数据分析的时候可能往往需要对这些数据进行整体处理，这个时候很多小伙伴可能会打开每张表一个一个复制粘贴！！！

为了解决这一低效的操作，我们这里介绍两种方案，让大家可以一键秒解合并Excel表格！

1.1.Python实现表格合并

Python实现表格合并的本质是 遍历全部表格数据，然后采用concat方法进行数据和横向连接。

因此，在这里我们主要用到两个库：os和pandas,其中os用于获取文件夹下全部满足要求的文件信息，pandas用于读取表格数据并进行concat。

# 导入os库
import os

# 修改当前目录为 测试数据所在目录
os.chdir(r'F:\微信公众号\表格合并与拆分\测试数据')

# 查看当前目录下文件列表
os.listdir()

['huawei.xlsx', 'oppo.xlsx', 'vivo.xlsx']

打开测试数据所在文件夹，我们看到一共有三份数据，和上面结果一致

用vba执行python python和vba操作excel_用vba执行python

#获取文件夹下全部文件的绝对路径
for fileName in os.walk(os.getcwd()):
    for table in fileName[2]:
        path = fileName[0] + '\\' + table
        print(path)

F:\微信公众号\表格合并与拆分\测试数据\huawei.xlsx
F:\微信公众号\表格合并与拆分\测试数据\oppo.xlsx
F:\微信公众号\表格合并与拆分\测试数据\vivo.xlsx

# 导入pandas库
import pandas as pd

print(path)

F:\微信公众号\表格合并与拆分\测试数据\vivo.xlsx

# 读取某个文件，并预览数据
df = pd.read_excel(path)
print(df.to_markdown())

	序号	品牌	机型	数量
0	1	vivo	V1901A	16139
1	2	vivo	vivo X9	12843
2	3	vivo	V1818A	11727
3	4	vivo	V1934A	8662
4	5	vivo	V1818CA	8367
5	6	vivo	vivo Y66	8354
6	7	vivo	V1813A	7686

df

	序号	品牌	机型	数量
0	1	vivo	V1901A	16139
1	2	vivo	vivo X9	12843
2	3	vivo	V1818A	11727
3	4	vivo	V1934A	8662
4	5	vivo	V1818CA	8367
5	6	vivo	vivo Y66	8354
6	7	vivo	V1813A	7686

# 新建一个空列表，用于存储表格数据
fileList = []
# 把文件夹下表格数据放在一个列表里
for fileName in os.walk(os.getcwd()):
    for table in fileName[2]:
        path = fileName[0] + '\\' + table
        li = pd.read_excel(path)
        fileList.append(li)
# 用concat方法合并表单数据
result = pd.concat(fileList)

# 导出数据
result.to_excel(r'机型汇总数据.xlsx',index=False,sheet_name='汇总')

result

	序号	品牌	机型	数量
0	1	HUAWEI	HLK-AL00	16123
1	2	HUAWEI	SEA-AL10	9428
2	3	HUAWEI	JSN-AL00a	9267
3	4	HUAWEI	DUB-AL00	8443
4	5	HUAWEI	STK-AL00	8278
5	6	HUAWEI	ASK-AL00x	7926
0	1	OPPO	PBAM00	24730
1	2	OPPO	OPPO A57	17172
2	3	OPPO	OPPO R9s	12442
3	4	OPPO	PBBM30	12374
4	5	OPPO	PCHM10	10938
5	6	OPPO	PCAM10	9787
6	7	OPPO	PBEM00	8532
0	1	vivo	V1901A	16139
1	2	vivo	vivo X9	12843
2	3	vivo	V1818A	11727
3	4	vivo	V1934A	8662
4	5	vivo	V1818CA	8367
5	6	vivo	vivo Y66	8354
6	7	vivo	V1813A	7686

# 按照数量进行排序
result.sort_values(by='数量',ascending=False,inplace=True)
# 重置序号
result['序号'] = range(1,len(result.index)+1)
result.reset_index(drop=True)

	序号	品牌	机型	数量
0	1	OPPO	PBAM00	24730
1	2	OPPO	OPPO A57	17172
2	3	vivo	V1901A	16139
3	4	HUAWEI	HLK-AL00	16123
4	5	vivo	vivo X9	12843
5	6	OPPO	OPPO R9s	12442
6	7	OPPO	PBBM30	12374
7	8	vivo	V1818A	11727
8	9	OPPO	PCHM10	10938
9	10	OPPO	PCAM10	9787
10	11	HUAWEI	SEA-AL10	9428
11	12	HUAWEI	JSN-AL00a	9267
12	13	vivo	V1934A	8662
13	14	OPPO	PBEM00	8532
14	15	HUAWEI	DUB-AL00	8443
15	16	vivo	V1818CA	8367
16	17	vivo	vivo Y66	8354
17	18	HUAWEI	STK-AL00	8278
18	19	HUAWEI	ASK-AL00x	7926
19	20	vivo	V1813A	7686

全部代码

import os
import pandas as pd

# 修改当前目录为 测试数据所在目录
os.chdir(r'F:\微信公众号\表格合并与拆分\测试数据')

# 新建一个空列表，用于存储表格数据
fileList = []
# 把文件夹下表格数据放在一个列表里
for fileName in os.walk(os.getcwd()):
    for table in fileName[2]:
        path = fileName[0] + '\\' + table
        li = pd.read_excel(path)
        fileList.append(li)
# 用concat方法合并表单数据
result = pd.concat(fileList)
# 导出数据
result.to_excel(r'机型汇总数据.xlsx',index=False,sheet_name='汇总')

1.2. VBA实现表格合并

VBA实现表格合并的核心思想 遍历全部表格，然后将每个表格数据复制到汇总表中，每次在复制的时候从第一个为空的行开始

遍历用 Dir

FileName = Dir(ThisWorkbook.Path & "\*.xlsx")

Sub 合并数据()

    Dim bt As Range, r As Long, c As Long
    r = 1 '表头行数
    Application.ScreenUpdating = False '关闭屏幕更新
    Dim fileName As String, wb As Workbook, sht As Worksheet, temp As Worksheet, Erow As Long, fn As String, arr As Variant
    ' 遍历全部文件
    fileName = Dir(ThisWorkbook.Path & "\*.xlsx")
    ' 获取文件宽度
    Set temp = GetObject(ThisWorkbook.Path & "\" & fileName).Worksheets(1)
    c = temp.Range("A1").CurrentRegion.Columns.Count
    '搞定第一行数据
    temp.Range("A1").Resize(1, c).Copy Range("A1").Resize(1, c)
    ' 循环写入数据
    Do While fileName <> ""
        If fileName <> ThisWorkbook.Name Then '判断文件是否为本工作簿
            Erow = Range("A1").CurrentRegion.Rows.Count + 1 '取得汇总表中第一条空行行号
            fn = ThisWorkbook.Path & "\" & fileName
            Set wb = GetObject(fn) '将fn代表的工作簿对象赋给变量
            Set sht = wb.Worksheets(1) '汇总的是第1张工作页签
            With sht.Range(sht.Cells(r + 1, "A"), sht.Cells(65536, "B").End(xlUp).Offset(0, c))
                Cells(Erow, "A").Resize(.Rows.Count, .Columns.Count) = .Value '复制数据
                End With
            wb.Close False
        End If
        fileName = Dir '用Dir函数取得其它文件名，并赋给变量
    Loop
    Application.ScreenUpdating = True '开启屏幕更新
    Worksheets(1).Name = "汇总"
End Sub

2.Excel表格拆分

表格拆分是第1部分表格合并的反向操作，常见于我们导出的原始数据是包含所有分类的汇总数据，需要按照某个分类列进行拆分表的情况。

同样在这里，我们分别介绍Python实现和VBA实现两种方案！

2.1.Python实现表格拆分

Python实现表格拆分的逻辑比较简单，就是分组然后将每组的数据单独导出存表即可

原表数据长这样：

用vba执行python python和vba操作excel_微信公众号_02

import pandas as pd
# 选定目标文件所在文件夹
path = 'F:\微信公众号\表格合并与拆分'
# 读取目标文件
df = pd.read_excel(f'{path}\汇总数据表.xlsx')
# 按照品牌进行分组
grouped = df.groupby(by='品牌')
# 输出分组数据导出成单表
for i, data in grouped:
    data.to_excel(f'{path}\\{i}.xlsx',index = False,sheet_name = i)

导出结果如下：

用vba执行python python和vba操作excel_微信公众号_03

思考题：

如果在原有《汇总数据表》中新建新的页签用于存放拆分数据（可以参考《》7.2财务数据处理并导出）

2.2.VBA实现表格拆分

VBA实现表格拆分的逻辑是在指定的拆分列进行遍历，然后按照分类新建表并逐条复制内容

以下为详细代码注释版本

Sub 表格拆分()
    '屏幕刷新=false
    Application.ScreenUpdating = False
    Dim LastRow, LastCol As Long
    Dim Sh, Sht As Worksheet
    'Sh指代当前活动页
    Set Sh = ActiveSheet
    '当前活动页的最后一行
    LastRow = Sh.Cells(Rows.Count, 1).End(xlUp).Row
    '当前活动页的最后一列
    LastCol = Sh.Cells(1, Columns.Count).End(xlToLeft).Column
    '定义D为字典
    Dim D As Object
    Set D = CreateObject("Scripting.Dictionary")
    Dim Col As Integer
    'Col为要手动输入要拆分的列序数
    Col = InputBox("输入用于分组的列序号！")
    '从第2行找到最后一行
    For i = 2 To LastRow
        '查找这个要拆分行，看它在不在字典里
        TempStr = CStr(Sh.Cells(i, Col))
        '如果在字典里
        If D.exists(TempStr) Then
            '将数据放到对应的页里
            Set Sht = Worksheets(TempStr)
            '字典key值对应的项目值记录该页当前内容添加的行数，每次+1
            D(TempStr) = D(TempStr) + 1
            '下面一行可以注释掉了跟下面的重复了……
            'Sht.Cells(D(TempStr), 1) = Sh.Cells(i, 1)
            For j = 1 To LastCol
                Sht.Cells(D(TempStr), j) = Sh.Cells(i, j)
            Next
        Else
            '如果不在字典里，就添加一个新key
            D.Add TempStr, 1
            'i = i - 1是让该行一会儿重新检索一遍就能进到if里了
            i = i - 1
            '在最后一页新加一页，页名就是TempStr
            Sheets.Add After:=Sheets(Sheets.Count)
            Sheets(Sheets.Count).Name = TempStr
            '下面一行也是可以注释掉的
            'Sheets(Sheets.Count).Cells(1, 1) = Sh.Cells(1, 1)
            '把第一行标题行弄过去
            For j = 1 To LastCol
                Sheets(Sheets.Count).Cells(1, j) = Sh.Cells(1, j)
            Next
        End If
    Next
    '激活初始页，视觉上保持不变
    Sh.Activate
    'RT,GDCDSZ
    MsgBox ("完成！")

End Sub

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。