辅助 Excel 的数据计算 add-ins

作者: 小黄鸭呀 | 来源:发表于2020-11-27 10:56 被阅读0次

Excel 中遇到较复杂的运算，数据分析师常会用 add-ins 辅助解决。本文考察了一些常见的 add-ins，从部署难度、开发难度、流畅程度等方面进行深度对比，并着重考察了数据计算能力，esProc 在这些 add-ins 中的表现相对出色。

对于大多数简单运算，Excel都提供了方便的实现手段，有时是易用的函数，有时是直观的按钮或菜单。但我们还是会遇到的一些较复杂或特殊的运算，依靠Excel本身很难实现。Excel提供了add-in接口，可以通过这个接口执行外部程序，从而借助外部语言或脚本实现这些较复杂或特殊的运算，达到辅助Excel的目的。

下面，让我们深入了解一些Excel的常见数据计算add-ins，并评估它们的计算能力。

Excel DNA

Excel DNA是早期出现的一款Excel add-in，它可以把程序员写好的动态库函数放到Excel里使用，动态库可以使用C#/F#/VB.net等语言等编写。

具体用法上，Excel DNA和其他所有add-ins都类似，首先要编写自定义函数。比如下面C#编写的代码中（引自Excel DNA官网），MyFunction是自定义函数名。

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

using ExcelDna.Integration;

namespace MyLibrary

{

public class Class1

{

[ExcelFunction(Description="few people use this way!")]

public static string MyFunction(string name)

{

return "Bonjour" + name;

}

上面的代码须编译成动态库，之后才能在Excel中使用。

接下来，一般要配置自定义函数和add-in的关系。比如下面的DnaSample.dna文件，表明本add-in的名字是"My name"，对应的动态库是Mylibrary.dll（含有多个自定义函数）。

</DnaLibary>

最后在Excel中配置该add-in，就可以在单元格中调用MyFunction这个函数了，如下：

应该注意到，上述过程有个编译的动作，因为编译过的程序可直接执行，且与Excel集成紧密，因此执行效率非常高。这便带来了Excel DNA最大的优点：顺滑无卡顿。

Excel DNA的其他优点从名字就可以看出来，换句话说，该add-in可以充分利用微软DNA架构提供的便利，比如开发语言、开发工具、Excel集成、联动调试等。

还应该注意到，C#/F#/VB.net等语言的通用性很强，理论上是无所不能的，但官网的代码例子却只是字符串输出，体现不出哪怕丝毫的能力，这到底是为什么呢？

因为理论和实际是有差别的。

C#/F#/VB.net等语言缺乏结构化计算类库，即使最基本的运算都要硬编码实现，代码因此非常繁琐，并不适合做复杂的数据计算。

除了不适合数据计算，还应注意到C#/F#/VB.net是编译型语言，而不是解释型语言，这就要求用户必须维护一套编译环境，以备修改算法后编译所用，而微软的编译环境配置较复杂，桌面数据分析师不易掌握。事实上，C#/F#/VB.net等语言本身的技术门槛就很高，这就导致Excel DNA更适合专业程序员作为接口使用，并不适合大多数桌面数据分析师直接使用。

除了Excel DNA，还有其他一些add-ins同样缺乏结构化计算类库，比如基于JAVA语言的JINX。很容易就能判断出，JINX也不适合数据计算。事实上，Excel自带的VBA在语言能力上和Excel DNA/JINX相当（都不适合数据计算），但VBA免集成免编译，比Excel DNA/JINX更有竞争优势。

Excel JavaScript

显而易见，无论什么add-ins，至少要比VBA方便好用，才值得我们学习研究。微软也发现了这个问题，所以2013年推出了Excel JavaScript，一种比VBA更方便的add-ins专用语言。

Excel JavaScript的用法和其他add-ins类似，这里以及后续都不再赘述。值得强调的是，Excel JavaScript是解释型语言，可以随时修改并立即执行，而无需编译，这一点和Excel DNA区别较大。既然是解释型语言，一般就会存在卡顿问题，但Excel JavaScript是Excel内置的语言，可以在同一个进程中执行，因此实际效果非常顺滑，执行效率仅次于Excel DNA。

内置于Excel还会的带来其他好处，比如无需下载，可直接开发，这便省去了繁琐的部署过程。再比如Excel JavaScript继承了Excel跨平台的能力，只需编写一次，就可以在单机版、网页版、Mac版上无缝迁移。另外，Excel JavaScript可直接访问workbook、sheet、cell等Excel对象，开发效率显著提升。

等一下，上面说的虽然都是Excel JavaScript的优势，但好像VBA也具备同等的优势，所以，说好的“更方便”到底体现在哪里？

比VBA更方便，体现在Excel JavaScript的界面控制能力上。换句话说，Excel JavaScript可以用更简单的语法访问Excel菜单栏、面板按钮、弹出框，可以在JS文件中直接定义add-ins界面，比VBA方便太多了。

唯一的问题是，界面控制并非数据计算add-ins的重点，不值得我们关注……

不错，既然讲的是数据计算add-ins，那数据计算能力才是关注重点，而不是界面控制能力。但遗憾的是，JavaScript依然没有任何结构化计算函数，用于做Excel都难以实现的数据计算也没啥特别的优势，仅仅是让Excel多了一种不同于VBA的脚本语言而已。

pyxll

显然，只有具备结构化计算类库，才算是合格的数据计算add-ins，比如这里要讲的pyxll。Pyxll是基于Python语言的add-in，而Python拥有结构化计算类库Pandas。

既然是合格的数据计算add-in，pyxll实现简单算法时自然无需硬编码，比如对指定区域分组汇总：选中Excel中的一批员工记录，传给自定义函数groupEmp，由pyxll执行分组汇总算法，并返回计算结果，只需编写如下代码：

import pandas as pd

import numpy as np

from pyxll import xl_func

@xl_func("dataframe<index=False, columns=True>")

def groupEmp(df):

df=df.groupby("deptid")['salary'].agg([len, np.sum, np.mean]) #核心代码：分组汇总

return df

上面核心代码只有一行，其他代码基本都是定式。可以看到，具备结构化库函数的pyxll，可以用非常简洁的代码实现分组汇总等简单算法。

当然，有时也会遇到较复杂或特殊的运算，需要用多个函数组合实现，而不是单独使用排序、过滤之类基本函数。遗憾的是，pyxll实现较复杂或特殊的运算时不太方便。

比如规范化数据并分组汇总的例子：针对Excel中的住户户型明细表（A-E列），自定义函数需按STYLE和BEDROOMS分组，统计SQFEET、BATHS、PRICE的平均值，其中PRICE列原本是字符串，需去掉$符号，转为数值再计算。

处理前数据

处理后的数据在新sheet中。

实现上述算法的自定义函数如下（只保留核心代码）：

for i in range(1, len(b)):

b[i][4] = b[i][4].replace(“$”,‘ ‘)

b[i][4] = b[i][4].replace(“,”,‘ ‘)

for i in range(1, len(b)):

for j in [1, 2, 3, 4]:

b[i][j] = eval(b[i][j])

data = pandas.DataFrame(b[1:],columns=b[0])

out = data.groupby([‘STYLE’,‘BEDROOMS’]).mean()

return out

分组还是只有一句，但前面的预处理却要6行，有点麻烦。

再比如一行分多行的例子：A列存储ID，B列存储ID对应的列表List，List有多个成员，以空格为分隔符。自定义函数需将List按空格拆分，使每个ID对应一个成员。

处理前的数据

处理后的数据在新sheet中：

实现上述算法的自定义函数如下：

split_dict = df.set_index('ID').T.to_dict('list')

split_list = []

for key,value in split_dict.items():

anomalies = value[0].split(' ')

key_array = np.tile(key,len(anomalies))

split_df = pd.DataFrame(np.array([key_array,anomalies]).T,columns=['ID','ANOMALIES'])

split_list.append(split_df)

df = pd.concat(split_list,ignore_index=True)

return df

可以看到，即使只保留核心运算功能，pyxll的代码仍然有点复杂。这就是pyxll缺点之一：不擅长实现较复杂或特殊的运算。

pyxll还有一个缺点：Excel要调用外部解释器来解释Python脚本，因此顿挫感较强烈，会严重影响用户体验。当然，顿挫并非pyxll独有的问题，而是所有外部解释型脚本共通的问题，比如XLwings、Bert和RExcel。其中XLwings与pyxll同样是基于Python的add-ins，优缺点基本一样。Bert和RExcel是基于R的add-ins，R专注于科学模型算法，其结构化计算类库不够专业，因此这两款add-ins的计算能力还不如pyxll，顿挫感也会更强。

当然，解释型语言也有优点，最大的优点是无需编译即可执行，维护修改都很方便。