说到必需学习的数据工具,Excel 无疑是唯一的答案 ,各种基本操作、函数公式、透视表,这些都是非常好用的功能,加上 vba 可以实现自动化需求。 自动化控制 Excel,我认为 vba 是目前最好的平台。但是 vba 的数据处理能力实在有限(别把表格处理与数据处理混淆)。 而 Python 之所以在数据领域受宠,完全是因为他有一些非常好用的库(numpy、pandas等),如果没有这些库,实际上 Python 与 vba 没有多大区别(只是在数据处理方面)。但是 Python 做数据任务有个不太好的地方,没有一个舒服的操作界面(虽然有许多第三方库做界面,但是实在太麻烦) Excel 就是一个很好的操作界面,为何不结合他们? 本系列文章我将完成一些小工具的制作,通过 Excel 完成各种输出格式的自动化,而把数据处理交给 pandas 完成。 这里有我的 pandas 专栏,我相信市面上还没有这么多干货的pandas教程: 这是一个能让你通过简单操作,即可对数据进行各种操作的小工具,如下动图: 可以指定文件路径 可以指定各种基本操作,比如筛选、分组、统计等等 当然也可以让你编写代码 这个工具的一个特点是,你的操作最后都能转化成 pandas 代码 此工具界面完全使用 Excel 制作(大部分情况下不需要编写任何 vba 代码),后台处理使用 Python(大部分情况使用 pandas)。 xlwings 是 Python 的一个第三方库,主要用于让你的 Python 代码可以在 Excel 上被调用。 我们要借助 xlwings 的一个开发工具,因此执行如下命令行: xlwings addin install
此时你打开 Excel ,应该会看到 xlwings 的加载项 暂且不解释他的原理,稍后在实践中再讲解其中的机制。 接下来,我们需要生成一个项目,说白了,就是生成一些必要的文件: 首先导航到你的项目文件夹中,执行如下命令: xlwings quickstart myproject --standalone 其中的 'myproject' 是你的项目名字, 你可以修改为任何合法的名字, 此时你会发现项目文件夹中生成了一个名为 'myproject' 的文件夹,其中有文件: myproject.xlsm,这是带 vba 代码的 Excel 文件 myproject.py,这是带 Python 代码的文件 接下来开始我们的小工具制作 从一个简单任务开始,当我们在一个 Excel 单元格上输入文件路径与工作表名字,下方显示文件中的数据。 这通过动态数组公式完成这个任务。首先打开 myproject.py 文件,自定义一个函数: 行1,2:导入需要的库 行6-11:加载数据的自定义函数,其中的逻辑非常简单,使用 pandas 加载数据,返回结果即可 行4:@xw.func , xlwings 的装饰器,标记此函数是一个公式,这会让函数成为 Excel 中的函数公式 行5:@xw.ret(expand='table') , xlwings 的装饰器 , 'ret' 应该是 'return' 的意思 ,它是函数返回值相关的设置。这里参数 expand='table' ,这会让公式变成一个自动扩展范围的动态数组公式(结果是一个表,行列数都是动态的) Python 的代码已经有了,但是 Excel 是不可能直接识别你定义的函数。 幸运的是,Excel 可以识别 vba 定义的函数。因此,我们需要 xlwings 帮我们自动生成 vba 代码。 打开 Excel 文件 myproject.xlsm(注意要启动宏): 在 xlwings 页中,点击 import Functions 的大按钮,意思是'导入 Python 文件中的函数' 此时我们输入函数公式时,就能从提示中看到函数: 按照参数,选择对应的单元格引用即可: 回车后发现返回一段错误信息: 显然,我们还没有输入文件路径 输入完整的文件路径即可: 你会发现公式自动变成了数组公式 如果文章只是简单列出操作步骤,那么这是一个不合格的教程。你在不懂原理的情况下,很多问题都无法自己解决。 接下来我将讲解其运行机制的直觉理解。 目前为止,我们没有编写一句 vba 代码,只是简单定义出一个加载数据的 Python 自定义函数,就可以在 Excel 上使用公式实现效果。 从步骤上来说: 那么为什么需要点击 '导入函数' 按钮? 如果我修改了 Python 代码,需要重新点击这个按钮吗? 首先,我们之所以能在 Excel 上输入公式时,出现我们的自定义函数,是因为在这个 Excel 文件中,存在 vba 代码,定义了同名的方法: 从 vbe 界面中可以看到,当我们点击'导入函数'按钮时,xlwings 在按照 Python 文件中定义的函数,生成了对应的 vba 代码 其中也能看见,调用时需要 Python 文件名,函数名字,和其他的参数
因此,Excel 公式执行时,会转而执行 Python 文件中的同名函数。 理解这点非常重要,从中可以得知: 如果 Python 中的函数名字或参数数量有增减,则需要重新点击'导入函数'按钮 如果只是函数中的实现代码有变动,无须点击'导入函数'按钮 例如,修改 myproject.py 中的代码: 我们希望返回结果的前10行 修改后,保存一下此 Python 文件,在 Excel 上无须点击'导入函数'按钮,只要公式有刷新(比如修改公式引用到的单元格的值),就能看到最新结果: 只有10行结果 你可能会觉得每次修改都点一下导入按钮,万无一失。 如果你是制作工具给别人使用的情况下,就需要知道代码变动后,哪些文件(Excel或Python文件)需要更新 首先,我们希望返回结果不要把 DataFrame 的行索引输出: 这可以通过装饰器的参数设置: 行5:设置 ret 装饰器的参数 index 为 False 即可 此外通过参数 header 也能控制是否输出表头 你会发现即使数据文件就在项目文件夹中,使用相对路径是读取不到文件。 但是,每次输入全路径太麻烦了,如果能输入相对路径就很好了。 这问题由于 xlwings 在执行 vba 的方法时,会启动一个 Python 进程(只有首次运行的时候),然后加载 myproject.py 中的代码。但他没有设置启动目录。 因此默认情况下启动目录是 Python.exe 所在目录。 我们只需要在 myproject.py 中修改启动目录即可: 现在可以支持相对路径: 文件名字如果可以下拉选择就好了! 我们来看看如何实现 首先,我们需要一个能找出 myproject.py 文件所在目录的所有 Excel 文件,我们使用 Python 实现这功能(这不是 vba 擅长的): 保存文件后,记得'导入函数' 在 Excel 文件 myproject.xlsm 中,创建一个新的工作表(示例中名字为 Sheet2),执行这个公式: 到界面工作表,为 B1 单元格设置数据有效性: 其中来源选择 Sheet2 的A列 现在可以下拉选择文件:
今天内容已经比较多了,以后我们将继续完善和添加其他功能到此工具上,过程中将讲解更多相关机制: 文件下拉选项动态变化 工作表名字的下来选择 支持更多数据源(csv,数据库等) 异步加载数据 数据操作,并生成对应的 pandas 代码 文件缓存:只有在数据文件被修改后,才重新读取文件(现在是每次执行都加载) 锁定 xlwings 版本,如何能在没有安装 xlwings 的电脑上使用工具 |
|