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

前言

本号之前已经分享过关于如何使用 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 使用总结如下：

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