辅助 Excel 的数据计算 add-ins



Excel 中遇到较复杂的运算,数据分析师常会用 add-ins 辅助解决。本文考察了一些常见的 add-ins,从部署难度、开发难度、流畅程度等方面进行深度对比,并着重考察了数据计算能力,esProc 在这些 add-ins 中的表现相对出色。点击辅助 Excel 的数据计算 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(含有多个自定义函数)。

<DnaLibrary Name="My   name" RuntimeVersion="v4.0">

    <ExternalLibrary   Path="Mylibrary.dll" />

</DnaLibary>

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


A




A

1

=MyFunction("jason")

press enter

-->

1

Bonjour jason

2




2


应该注意到,上述过程有个编译的动作,因为编译过的程序可直接执行,且与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 JavaScriptExcel内置的语言,可以在同一个进程中执行,因此实际效果非常顺滑,执行效率仅次于Excel DNA

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

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

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

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

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

 

pyxll

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

既然是合格的数据计算add-inpyxll实现简单算法时自然无需硬编码,比如对指定区域分组汇总:选中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列),自定义函数需按STYLEBEDROOMS分组,统计SQFEETBATHSPRICE的平均值,其中PRICE列原本是字符串,需去掉$符号,转为数值再计算。

处理前数据


A

B

C

D

E

1

STYLE

SQFEET

BEDROOMS

BATHS

PRICE

2

RANCH

1250

2

1.0

$64,000

3

SPLIT

1190

1

1.0

$65,850

4

RANCH

1500

3

3.0

$86,650

5

CONDO

1400

2

1.5

$80,050

6





处理后的数据在新sheet中。


A

B

C

D

E

1

STYLE

BEDROOMS

SQFEET

BATHS

PRICE

2

CONDO

2

1630.0

1.75

95370.0

3

CONDO

3

1390.0

2.50

79350.0

4

CONDO

4

2105.0

2.50

127150.0

5

RANCH

1

720.0

1.00

34550.0

6





实现上述算法的自定义函数如下(只保留核心代码):

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列存储IDB列存储ID对应的列表ListList有多个成员,以空格为分隔符。自定义函数需将List按空格拆分,使每个ID对应一个成员。

处理前的数据                     

A

B

ID

List

1

A1 B1 C1 D1

2

A2

3

A3 B3 C3

4

A3 B4 D4

处理后的数据在新sheet中:

A

B

ID

List

1

A1

1

B1

1

C1

1

D1

2

A2

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

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 BertRExcel。其中XLwingspyxll同样是基于Pythonadd-ins,优缺点基本一样。BertRExcel是基于Radd-insR专注于科学模型算法,其结构化计算类库不够专业,因此这两款add-ins的计算能力还不如pyxll,顿挫感也会更强。

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

 

esProc

esProc是专业的数据计算引擎,也提供了一个Excel add-in,可以使用esProc SPL语言编写计算脚本。它与pyxll有很多相似之处,比如两者都有丰富的结构化计算函数,因此可以轻松实现简单算法,比如对指定区域分组汇总,只需编写脚本groupEmp.dfx:


A

B

1

=create(eid, name,deptid,salary).record(arg1)

/arg1接收Excel二维表

2

=A1.groups(deptid;sum(salary),avg(salary))

/核心代码:分组汇总

核心代码只有A2一行,非常简洁。之后就可以在Excel单元格中引用自定义函数groupEmp,形如=dfx("groupEmp",A1:D20)

其他基本算法也可以轻松实现(只留核心代码):


A

B

3

=A1.select(salary>8000 && salary<10000)

/过滤

4

=A1.sort(-salary)

/逆向排序

5

=A1.group(deptid).conj(~.sort(salary))

/开窗,按工资对各部门内的员工进行排名

6

=connect@l("mssql").query("select * from   dept")

/从数据库读入部门记录

7

=A1.join@i(deptid,A6:deptid,deptname)

/Excel和数据库关联计算

通过了解之前的add-ins,我们已经可以得出结论:是否能方便地实现较复杂或特殊的运算,才是判断一款add-in的数据计算能力的真正指标。

esProc能够方便地实现较复杂或特殊的运算,这是它比pyxll更有优势的地方。

比如规范化数据并分组汇总,前面用pyxll时显得很麻烦,但esProc就简单多了:


A

2

=A2.run(int(replace(replace(#5,”$”,””),”,”,””)):PRICE)

3

=A3.groups(STYLE,BEDROOMS;avg(SQFEET):SQFEET,avg(BATHS):BATHS,avg(PRICE):PRICE)

再比如一行分多行,esProc代码更简单:


A

2

=A1.news(ANOMALIES.split(" ");ID,~:ANOMALIES)

 

再举个逻辑更复杂的例子:计算分期贷款明细。Excel单元格记录着贷款信息,包括贷款 ID,贷款总额、按月分期数、年利率,示意如下:


A

B

C

D

1

LoanID

LoanAmt

Term

Rate

2

L01

100000

5

4.8

3

L02

20000

2

5.0

4

L03

500000

12

4.5

自定义函数的目的是计算出各期明细,包括:当期还款额、当期利息、当期本金、剩余本金。计算结果在新sheet 中应当如下:


A

B

C

D

E

F

G

H

1

LoanID

LoanAmt

Payment

Term

Rate

interest

princepal

princeplebalance

2

L01

100000

20238.13

5

4.75

395.83

19842.29

80159.71

3

L01

100000

20238.13

5

4.75

317.29

19920.83

60236.87

4

L01

100000

20238.13

5

4.75

238.44

19999.69

40237.18

5

L01

100000

20238.13

5

4.75

159.27

20078.85

20158.33

6








上面的算法用pyxll实现会很麻烦,但esProc 就很方便:


A

2

=A2.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment)

3

=A3.news((t=LoanAmt,Term);LoanID,   LoanAmt, mPayment:payment,   Term, Rate, t* mRate:interest,   payment-interest:principal, t=t-principal:principlebalance)

看起来esProc的数据计算能力确实很强大,但是,非常遗憾的是,esProc也是基于外部解释器的add-in,还需要JVM来执行,它同样存在卡顿的问题。

 

esProc  through clipboard

有没有办法既能利用esProc强大的计算能力,还能顺滑地操作esProc

用剪贴板代替自定义函数!

比如求各科前3名的学生:A列是学生姓名,B-D列分别是数学、英语、物理成绩,需要求出每学科成绩前3名的学生,并追加到本科成绩之后。

处理前数据


A

B

C

D

1

name

math

english

physics

2

lily

97

100

99

3

Joshua

100

99

100

4

Sarah

98

99

96

5

Bertram

94

95

85

6

Paula

91

88

91

7

Sophia

92

81

76

8

Ben

87

80

76

9

Ruth

92

91

87

10

Pag

95

87

87

选中这些单元格,先用ctrl+C复制到剪贴板,再在esProc脚本中执行如下代码:


A

B

1

=clipboard().import@t()

/从剪切板获取数据

2

=A1.top(-3;math).(name)

/math3

3

=A1.top(-3;english).(name)


4

=A1.top(-3;physics).(name)


5

=join@p(A2;A3;A4).export()

/拼成二维表再转成字串

6

=clipboard(A5)


/向剪切板导出数据





执行上述脚本后,只需在ExcelB11格用ctrl+V,即可将剪切板中的数据复制到B11-D13,达到和自定义函数相同的效果,如下:


A

B

C

D


9

Ruth

92

91

87

10

Pag

95

87

87

11


Joshua

Lily

Joshua

12


Sarah

Sarah

Lily

13


lily

Joshua

Sarah

类似的,大多数自定义函数都可以用剪切板简单代替,除非遇到一些特殊情况,比如多片区域参与运算。

应该注意到,上述过程虽然可以到达顺滑操作的目的,也可以利用到esProc强大的计算能力,但并没有使用add-in协议。事实上,如果愿意使用剪切板,就没必要部署复杂的add-ins,这对数据分析师来说,难道不是一件减轻负担的好事吗?

还应该注意到,不仅esProc可以利用剪贴板来解决卡顿的问题,pyxlladd-ins理论上也完全可以,只要它们在将来的版本中提供类似的函数(从剪切板获取数据并转换成内部的结构化数据类型)。

经过前面的比较,我们可以得出这样的结论:流畅的add-ins计算能力差;计算能力强的add-ins存在卡顿现象。配合剪切板使用esProc可以弥补卡顿的缺点,更适合桌面分析师使用。


以下是广告时间

对润乾产品感兴趣的小伙伴,一定要知道软件还能这样卖哟性价比还不过瘾? 欢迎加入好多乾计划。
这里可以低价购买软件产品,让已经亲民的价格更加便宜!
这里可以销售产品获取佣金,赚满钱包成为土豪不再是梦!
这里还可以推荐分享抢红包,每次都是好几块钱的巨款哟!
来吧,现在就加入,拿起手机扫码,开始乾包之旅



嗯,还不太了解好多乾?
猛戳这里
玩转好多乾