Excel数据处理你是选择Vba还是Python？当然是选pandas！

带我进入dotNET领域的我的师父，它玩dotNET、VBA、Python，样样都溜，给大家转载下，此篇的VBA可是面向对象编程思路，高级货，Python也很不错。总之合适自己的最好，忌三心两意。

前言

本号之前已经分享过关于如何使用 Python 中的数据处理分析包 pandas 处理 Excel 的数据，本文继续分享一个小案例，此案例源于上周末帮朋友做的一个需求，并且是以 vba 编写解决，后来我用 Python 再解决一次，通过本文作简单分享。

数据与需求

此案例的数据如下：

• • 每个开单人员的销售记录
• • 描述为：

  销售员“张三”(开单部门)，把xxx货品(货品编码、货品名字)售出了5件(数量)，此笔订单总价为2000元(价税合计)

• • 上述的括号部分就是表中的列标题
• • 数据行中，有许多无效的行，只要 开单部门 列有名字，就是有效的行

此案例的数据对所有敏感数据进行随机生成替换

需求结果如下图：

• • 按 销售员、货品编码，汇总 货品数量和价税合计
• • 每个销售员单独生成一个表输出
• • 最后再输出一个所有货品的汇总表

vba 的方案简短分析

本文的核心不是 vba ，因此这里只做简单的讲解，如果你是 vba 用户，可以获取源码查看。

vba 的解决方案尽可能采用了面向对象的方式进行，下图为主要结构：

• • m_main 模块包含整个主流程逻辑，入口方法 main
• • 类模块 D_ArrayVar 与 D_GVar ，是为了做到 数据源的列位置变化，也不需要修改程序
• • D_Person 与 D_Sku ，分别表示销售员与货品，里面有关键的累计逻辑
• • 模块 Msys_Function 与类模块 C_GetFile ，是很久以前写的帮助类

如果你是 vba 的高级用户，可能会觉得直接使用 字典+数组 的方式即可完成，但注意，直接 字典+数组 方式会导致代码难以维护

Python 的方案

上面说的 vba 方案，我大概花费了接近1小时的时间(vba 中编写类模块太繁琐了)，期间有一个需求变动，得益于面向对象的优点，在几分钟内完成应对，并且无需要大范围做测试。

但是，这样的需求如果在 Python 中，我们的处理效率可以提高多少呢？我使用 Python 的 pandas 包处理，在5分钟内搞定，并且代码有非常好的阅读性与扩展性。

这次我们直接使用 pandas 读写 excel 数据，而无需使用 xlwings 库

首先定义需要的列与每列的统计方式：

• • 其中核心是 g_agg_funcs 字典，他定义了每个输出列的统计方法。

  凡是文本类型的内容，统一用 first ，就是去组内的第一笔

接着定义加载 excel 数据到 DataFrame：

• • 由于数据源的标题在第3行，因此在调用 read_excel 时，参数 header 设置为 None，表示不需要用 excel 中的数据行作为 DataFrame 的标题
• • header=df.iloc[header_idx,:] ，把指定行的内容读取出来
• • df.columns=header ，赋值作为 df 的标题
• • df.dropna(subset=[g_pName]) ，把名字列中是空的行去掉

然后即可生成结果，如下：

• • df.groupby(cols).agg(g_agg_funcs) ，按销售员与货品分组并统计结果，pandas 中就是这么简单

但是，我们需要每个销售员单独一个 sheet 输出结果。如下图：

• • with pd.ExcelWriter(‘result.xlsx’) as exl: ，由于本案例需要对一个 excel 文件进行批量输出，因此不能直接使用 DataFrame.to_excel 。

  这里先创建一个 ExcelWriter对象

• • res.index.get_level_values(0) ，从分组结果中获得销售人员列，但这里的输出是带重复值的，因此我们需要使用 set 去重复
• • res.loc[idx,:] ，通过一个销售人员，即可获得这个销售员的货品汇总结果(是一个 DataFrame)，这时就可以调用 to_excel 输出结果
• • to_excel 中的参数 startrow ，表示结果输出在第2行

到这里，你可能会问，还有一个按照货品的汇总结果啊，这是非常简单，因为汇总方式是一样的，只是汇总字段有变化而已。如下：

• • 这里特意重复写一次 ExcelWriter ，我们这次是往已经存在的 excel 文件追加数据，因此其参数 mode=‘a’ ，是 append 的意思。

  而要使用追加模式，需要使用 openpyxl 引擎，因此需要设置 engine=‘openpyxl’

新增需求

在完成代码的情况下，如果需要在汇总结果中新增一列对单价列求平均，在 Python 的方案中，只需要在定义 g_agg_funcs 中添加单价列的统计方式，如下：

如果是在 vba 方案中，目前的修改还是比较容易的(在 sku 类模块的 add 方法中添加逻辑)，但是与 Python 的方案比较就显得低效得多。

总结

pandas 使用总结如下：

• • 理解好 pandas 中的索引(特别是多层索引)可以大大提升你的数据处理能力
• • pandas 中如果需要多次输出同一个 excel 文件，可以使用 ExcelWriter，注意追加模式需要设置参数 engine=‘openpyxl’

vba 使用总结如下：

• • 如非一次性代码，请面向领域设计代码(如本文例子)，而非面向数据设计代码

发送 “数据处理” 获取案例数据和源码

屏幕长按二维码

获取更多精彩

数据大宇宙