共计 5553 个字符,预计需要花费 14 分钟才能阅读完成。
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 这个函数了,如下:
应该留神到,上述过程有个编译的动作,因为编译过的程序可间接执行,且与 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,顿挫感也会更强。
当然,解释型语言也有长处,最大的长处是无需编译即可执行,保护批改都很不便。
esProc
esProc 是业余的数据计算引擎,也提供了一个 Excel add-in,能够应用 esProc 的 SPL 语言编写计算脚本。它与 pyxll 有很多相似之处,比方两者都有丰盛的结构化计算函数,因而能够轻松实现简略算法,比方对指定区域分组汇总,只需编写脚本 groupEmp.dfx:
外围代码只有 A2 一行,十分简洁。之后就能够在 Excel 单元格中援用自定义函数 groupEmp,形如 =dfx(“groupEmp”,A1:D20)。
其余根本算法也能够轻松实现(只留外围代码):
通过理解之前的 add-ins,咱们曾经能够得出结论:是否能不便地实现较简单或非凡的运算,才是判断一款 add-in 的数据计算能力的真正指标。
esProc 可能不便地实现较简单或非凡的运算,这是它比 pyxll 更有劣势的中央。
比方规范化数据并分组汇总,后面用 pyxll 时显得很麻烦,但 esProc 就简略多了:
再比方一行分多行,esProc 代码更简略:
再举个逻辑更简单的例子:计算分期贷款明细。Excel 单元格记录着贷款信息,包含贷款 ID,贷款总额、按月分期数、年利率,示意如下:
自定义函数的目标是计算出各期明细,包含:当期还款额、当期利息、当期本金、残余本金。计算结果在新 sheet 中该当如下:
下面的算法用 pyxll 实现会很麻烦,但 esProc 就很不便:
看起来 esProc 的数据计算能力的确很弱小,然而,十分遗憾的是,esProc 也是基于内部解释器的 add-in,还须要 JVM 来执行,它同样存在卡顿的问题。
esProc through clipboard
有没有方法既能利用 esProc 弱小的计算能力,还能顺滑地操作 esProc?
用剪贴板代替自定义函数!
比方求各科前 3 名的学生:A 列是学生姓名,B- D 列别离是数学、英语、物理问题,需要求出每学科问题前 3 名的学生,并追加到本科问题之后。
解决前数据
选中这些单元格,先用 ctrl+ C 复制到剪贴板,再在 esProc 脚本中执行如下代码:
执行上述脚本后,只需在 Excel 的 B11 格用 ctrl+V,即可将剪切板中的数据复制到 B11-D13,达到和自定义函数雷同的成果,如下:
相似的,大多数自定义函数都能够用剪切板简略代替,除非遇到一些非凡状况,比方多片区域参加运算。
应该留神到,上述过程尽管能够达到顺滑操作的目标,也能够利用到 esProc 弱小的计算能力,但并没有应用 add-in 协定。事实上,如果违心应用剪切板,就没必要部署简单的 add-ins,这对数据分析师来说,难道不是一件减轻负担的坏事吗?
还应该留神到,不仅 esProc 能够利用剪贴板来解决卡顿的问题,pyxll 等 add-ins 实践上也齐全能够,只有它们在未来的版本中提供相似的函数(从剪切板获取数据并转换成外部的结构化数据类型)。
通过后面的比拟,咱们能够得出这样的论断:晦涩的 add-ins 计算能力差;计算能力强的 add-ins 存在卡顿景象。配合剪切板应用 esProc 能够补救卡顿的毛病,更适宜桌面分析师应用。