VBA和Python面向Excel数据处理自动化的脚本编程

面向Excel数据处理自动化的脚本编程，目前主要有VBA和Python两种语言可供选择。

从上世纪90年代到目前，VBA一直是Excel脚本编程的主要工具。VBA语言具有简单易学、功能强大的特点，在长达几十年的时间里为提高Excel工作效率作出了贡献，也积累了海量的代码和学习资料。

随着网络时代的全面到来，以及大数据、人工智能等的兴起，Python语言在国内异军突起。Python语言已经成为目前最受欢迎的计算机语言之一，近年来在TIOBE和IEEE等编程语言排行榜上长期占据前三位。在国内，Python也在逐步代替原来的Basic语言，成为小学、中学和大学学生入门学习计算机编程的首选语言。所以，当前使用Python进行Excel脚本编程以提高工作效率的朋友越来越多。

PART01

需求与选择：VBA还是Python?

所以，目前使用或准备使用VBA和Python进行Excel脚本编程，或者说进行Excel数据处理自动化的人主要有3种。

• 第1种是懂Python不懂VBA的，他们有办公自动化和数据分析的需求。就目前可以找到的图书和网络课程来看，主要是结合Python的xlrd, xlwt和OpenPyXL等包进行介绍。这几个包小巧灵活，但是功能上有明显的不足，无法与VBA媲美。使用win32com和xlwings等包，VBA能做的Python都能做，但是这方面的资料很少，特别是语言参考这样一些最基本的文档缺乏。所以，Python用户在了解Excel对象模型的过程中需要参阅大量VBA的资料，即他们有快速掌握VBA语言的需求和获取xlwings基础性文档和使用手册的需求。

• 第2种是原来对VBA比较熟悉的朋友因为各种原因要学习和使用Python进行Excel脚本编程。VBA本身是很强大的，但是Python崛起以后，它通过win32com和xlwings可以使用VBA使用的Excel对象模型，即可以代替VBA；通过pandas等包可以快速方便地处理大型数据。所以，在数据处理自动化方面Python相较于VBA有明显的优势。这也是很多VBA老用户与时俱进，开始学习Python，学习用Python处理Excel数据的主要原因。

• 第3种是没有计算机语言基础但有Excel编程需求的同学。他们面临的是诸如“学VBA好还是学Python好”，“先学VBA还是先学Python”这样的问题。

那么，

究竟是学VBA好还是学Python好呢？

有没有可能同时学好两门语言？

有没有让VBA用户和Python用户快速掌握另外一门语言的捷径？

答案是：有！

PART02

双语对照学习：快速学习语言的捷径

对照学习就是快速学习语言的有效捷径！

所谓的对照学习，不是将两种语言机械地放在一起，自说自话，而是先将两门语言的语法全部打碎，然后实现语法知识点点对点的对照、融合和重建，在自己熟悉的语境中快速理解和掌握另一门语言。

下面的代码使用VBA和Python实现用空格分割给定的字符串。

【Excel VBA】

Sub Test()  Dim strL As String  Dim strArray() As String  strL = "Hello python VBA"  strArray = Split(strL, " ")  '分割字符串  Debug.Print strArray(0)  Debug.Print strArray(1)  Debug.Print strArray(2)End Sub

【Python】

>>> 'Hello python VBA'.split(' ')['Hello', 'python', 'VBA']

下面用VBA和Python实现用多分支if判断结构判断给定成绩的等级。

【Excel VBA】

Sub Test1()  Dim intSC As Integer  intSC = InputBox("请输入一个数字：")  If intSC >= 90 Then    Debug.Print "优秀"  ElseIf intSC >= 80 Then    Debug.Print "良好"  ElseIf intSC >= 70 Then    Debug.Print "中等"  ElseIf intSC >= 60 Then    Debug.Print "及格"  Else    Debug.Print "不及格"  End IfEnd Sub

【Python】

sc= int(input('请输入一个数字：'))if(sc>=90):    print('优秀')elif(sc>=80):    print('良好')elif(sc>=70):    print('中等')elif(sc>=60):    print('及格')else:    print('不及格')

PART03

Python使用xlwings真的能代替VBA吗？

能！因为xlwings间接封装了VBA使用的Excel对象模型。

Excel脚本编程的核心主要有2个，一个是脚本语言，另一个是对象模型。

语言方面Python可以代替VBA，现在xlwings封装了VBA使用的Excel对象模型，即对象模型是一样的，所以Python使用xlwings可以代替VBA进行Excel脚本编程，VBA能做的，Python也能做。

xlwings包将Excel对象模型中一些常用的功能使用新语法进行封装，其他功能用API方式进行调用。但实际上，使用API方式可以以类VBA的语法完成几乎所有的编程。所以，实际上xlwings包提供了两种编程方式，用封装后的新语法进行编程称为xlwings方式，使用API的称为xlwings API方式。

目前出版的图书和网络资料主要介绍新语法。就Excel对象模型提供的功能而言，新语法封装的这部分只是冰山水面上的一小部分，水面下的大部分需要通过API方式实现。

下面举例说明两种使用方式的区别。例如要选择工作表中的A1单元格，可以使用这两种方式进行编程：

【xlwings】

>>> sht=bk.sheets(1)>>> sht.range("A1").select()

【xlwings API】​​​​​​​

>>> sht=bk.sheets(1)>>> sht.api.Range('A1').Select()

可见，在xlwings方式下，range属性和select方法都是小写的，是重新封装后的写法。在xlwings API方式下，在sht对象后面引用api，后面就可以使用VBA中的引用方式，Range属性和Select方法首字母都是大写。所以使用API方式可以使用大多数VBA的编程代码，懂VBA编程的同学可以很快就能上手。当然，使用xlwings方式会有一些编码、效率方面的好处，有一些扩展的功能。

PART04

用VBA和Python操作Excel工作表

由于xlwings封装了VBA使用的Excel对象模型，所以，Python使用xlwings可以操作Excel表格，可以从表格读取数据、将数据写入表格，或者设置表格的属性等，就像VBA的操作一样。

下面分别用VBA和Python获取工作表中数据区域末行的行号。其中，sht为指定的工作表对象。

【Excel VBA】​​​​​​​

intR=sht.Range("A1").End(xlDown).RowintR=sht.Cells(1,1).End(xlDown).RowintR=sht.Range("A" & CStr(sht.Rows.Count)).End(xlUp).RowintR=sht.Cells(sht.Rows.Count,1).End(xlUp).Row

【Python xlwings】​​​​​​​

>>> sht.range('A1').end('down').row>>> sht.cells(1,1).end('down').row>>> sht.range('A'+str(sht.api.Rows.Count)).end('up').row>>> sht.cells(sht.api.Rows.Count,1).end('up').row>>> sht.api.Range('A1').End(xw.constants.Direction.xlDown).Row>>> sht.api.Cells(1,1).End(xw.constants.Direction.xlDown).Row>>> sht.api.Range('A'+str(sht.api.Rows.Count)).\                    End(xw.constants.Direction.xlUp).Row>>> sht.api.Cells(sht.api.Rows.Count,1).\                    End(xw.constants.Direction.xlUp).Row

下面分别用VBA和Python修改单元格区域的属性，将A2单元格的背景色设置为绿色，单元格中文本字体的大小设置为20，加粗并倾斜。其中，sht为指定的工作表对象。

【Excel VBA】​​​​​​​

sht.Range("A2").Interior.Color=RGB(0,255,0)sht.Range("A2").Font.Size=20sht.Range("A2").Font.Bold=Truesht.Range("A2").Font.Italic=True

【Python xlwings】​​​​​​​

>>> sht.range('A2').color=(0,255,0)>>> sht.api.Range('A2').Font.Size=20>>> sht.api.Range('A2').Font.Bold=True>>> sht.api.Range('A2').Font.Italic=True

单元格A2的属性设置效果如图1所示。

图1 单元格属性设置

PART05

用VBA和Python创建Excel图表

目前的图书和网络教程介绍Python自动化办公时主要介绍用Matplotlib创建图表，然后将图表导入到Excel表格。实际上，Python使用xlwings可以创建Excel自己的图表并进行编辑设置。

Excel自己的图表相较于Matplotlib创建的图表主要有几个方面的优势。

• 第1个优势是表格中的绘图数据与图表是关联的，修改数据时图表会即时改变；

• 第2个优势是可以创建透视图这样一些特殊图表；

• 第3个优势是Excel自己的三维图表效果比Matplotlib创建的三维图表好，可以添加光照，设置材质、纹理等。

下面分别用VBA和Python，使用Excel工作表中的数据创建嵌入式图表。

【Excel VBA】​​​​​​​

Sub CreateCharts()  Dim cht As ChartObject  '生成ChartObject对象，指定位置和大小  Set cht = ActiveSheet.ChartObjects.Add(50, 200, 355, 211)  With cht    With .Chart   'Chart属性返回Chart对象，用它设置图表属性      '绑定数据      .SetSourceData Source:=Sheets("Sheet1").Range("A1:H7"), PlotBy:=xlRows      .ChartType = xlColumnClustered        '图表类型      .SetElement msoElementChartTitleCenteredOverlay    '标题居中显示      .ChartTitle.Text = "部分省2011—2016年的GDP数据"    '标题文本    End With  End WithEnd Sub

【Python xlwings】​​​​​​​

import xlwings as xw      #导入xlwings包import os         #导入os包root = os.getcwd()       #获取当前路径app = xw.App(visible=True, add_book=False)  #创建Excel应用，不添加工作簿#打开与本文件相同路径下的数据文件，可写wb=app.books.open(root+r'/GDP数据.xlsx',read_only=False)sht=wb.sheets(1)       #获取工作表对象cht=sht.charts.add(50, 200)       #添加图表cht.set_source_data(sht.range('A1').expand())   #图表绑定数据cht.chart_type='column_clustered'      #图表类型cht.api[1].HasTitle=True        #图表有标题cht.api[1].ChartTitle.Text='部分省2011—2016年的GDP数据'  #标题文本

运行程序后生成的图表如图2所示。

图2  创建嵌入式图表

PART06

用VBA和Python创建Excel数据透视表

通过编程，可以使用向导和缓存两种方式创建数据透视表。

下面分别用VBA和Python，使用缓存方式创建数据透视表。Excel会为数据透视表建立一个缓存，通过该缓存，可以实现对数据源中数据的快速读取。先使用PivotCaches集合的Create方法可以创建PivotCache对象，即缓存对象，然后使用缓存对象的CreatePivotTable方法创建数据透视表。

【Excel VBA】​​​​​​​

Sub CreatePivotTable()Dim shtData As WorksheetDim shtPVT As WorksheetDim rngData As RangeDim rngPVT As RangeDim pvc As PivotCacheDim PVT As PivotTable'数据所在的工作表Set shtData = Worksheets("数据源")'数据所在的单元格区域Set rngData = shtData.Range("A1").CurrentRegion'新建数据透视表所在的工作表Set shtPVT = Worksheets.Add()shtPVT.Name = "数据透视表"'放数据透视表的位置Set rngPVT = shtPVT.Range("A1")
'创建数据透视表关联的缓存Set PVC= ActiveWorkbook.PivotCaches.Create( _             SourceType:=xlDatabase, SourceData:=rngData)'创建数据透视表Set PVT =PVC.CreatePivotTable(TableDestination:=rngPVT, _             TableName:="透视表")

'设置字段With PVT  .PivotFields("类别").Orientation = xlPageField   '页字段  .PivotFields("类别").Position = 1  .PivotFields("产品").Orientation = xlColumnField   '列字段  .PivotFields("产品").Position = 1  .PivotFields("产地").Orientation = xlRowField   '行字段  .PivotFields("产地").Position = 1  .PivotFields("金额").Orientation = xlDataField   '值字段End With
End Sub

【Python】​​​​​​​

import xlwings as xw   #导入xlwings包import os      #导入os包root = os.getcwd()    #获取当前路径#创建Excel应用，可见，不添加工作簿app=xw.App(visible=True, add_book=False)#打开数据文件，可写bk=app.books.open(fullname=root+r'\创建透视表.xlsx',read_only=False)#获取数据源工作表sht_data=bk.sheets.activerng_data=sht_data.api.Range('A1').CurrentRegion#新建数据透视表所在的工作表sht_pvt=bk.sheets.add()sht_pvt.name='数据透视表'

#放透视表的位置rng_pvt=sht_pvt.api.Range('A1')#创建透视表关联的缓冲区pvc=bk.api.PivotCaches().Create(\          SourceType=xw.constants.PivotTableSourceType.xlDatabase,\          SourceData=rng_data)#创建透视表pvt=pvc.CreatePivotTable(\          TableDestination=rng_pvt,\          TableName='透视表')#设置字段pvt.PivotFields('类别').Orientation=\    xw.constants.PivotFieldOrientation.xlPageField    #页字段pvt.PivotFields('类别').Position=1        #页字段中的第1个字段pvt.PivotFields('产品').Orientation=\    xw.constants.PivotFieldOrientation.xlColumnField   #列字段pvt.PivotFields('产品').Position=1        #列字段中的第1个字段pvt.PivotFields('产地').Orientation=\    xw.constants.PivotFieldOrientation.xlRowField    #行字段pvt.PivotFields('产地').Position=1        #行字段中的第1个字段pvt.PivotFields('金额').Orientation=\    xw.constants.PivotFieldOrientation.xlDataField    #值字段

运行程序，生成的数据透视表如图3所示。

图3  使用缓存创建数据透视表

新的数据时代下，ChatGPT兴起，预示着人工智能对我们工作的影响将会越来越大，如果我们只是简单的依靠着手动一键一键敲表格，不仅仅效率低下，而且慢慢就会被AI智能所替代，所以在当代背景下，只有我们有了一技之长才有可能会避免被裁员的风险！

Python经验分享

学好 Python 不论是就业数据分析还是做副业赚钱都不错，但要学会 Python 还是要有一个学习规划。最后大家分享一份全套的 Python 学习资料，给那些想学习 Python 的小伙伴们一点帮助！

Python学习路线

这里把Python常用的技术点做了整理，有各个领域的知识点汇总，可以按照上面的知识点找对应的学习资源。

学习软件

Python常用的开发软件，会给大家节省很多时间。

学习视频

编程学习一定要多多看视频，书籍和视频结合起来学习才能事半功倍。

100道练习题

实战案例

光学理论是没用的，学习编程切忌纸上谈兵，一定要动手实操，将自己学到的知识运用到实际当中。

最后祝大家天天进步！！

上面这份完整版的Python全套学习资料已经上传至CSDN官方，朋友如果需要可以直接微信扫描下方CSDN官方认证二维码免费领取【保证100%免费】。