Excel 插件使用说明

使用集算器桌面版的Excel插件,可在Excel中执行SPL脚本并获得结果集后填充到Excel中。

第 1 章 安装与配置

1、环境

要求64WindowsExcel,不能用32位的,否则出现异常。

判断WindowsExcel是否为64位的办法:

1、 Windows: 点击操作系统的【设置】-【系统】-【关于】

undefined

2、 Excel:打开Excel,点击【文件】-【账户】-【关于Excel】,弹出对话框里会显示当前Excel版本,如下所示:

About Microsoft® Excel® 2019

Microsoft® Excel® 2019MSO (Version 2202 Build 16.0.14931.20116) 64Bit

Product ID: 00405-32554-86889-AAOEM

Session ID: 5DCA85DD-F5BE-4570-8CE4-4D56EF93273D

语言:请安装与操作系统语言相同的集算器桌面版,即如果操作系统是英文的,则装英文版的集算器桌面版,如果操作系统是中文的,则装中文版的集算器桌面版,否则容易出现乱码等问题。

如果中文windows操作系统安装中文集算器桌面版后,会出现乱码等现象,请检查操作系统如下配置:

【设置】->【时间和语言】->【语言】->【管理语言设置】->【更改系统区域设置】

看看这里是否配置的中文,如果不是,改成中文即可

2、依赖文件

ExcelSpl.xll插件文件,位于【集算器桌面版安装目录】/esProcDesk/bin

scu-Excel-xll-2.10.jarxll4j-0.0.2.jar依赖jar,位于【集算器桌面版安装目录】/esProcDesk/lib

ExcelSpl.ini配置文件,位于【集算器桌面版安装目录】/esProcDesk/config

esproc_template.xla宏文件位于【集算器桌面版安装目录】/esProcDesk/bin目录下

3、加载ExcelSpl.xll插件

打开Excel,点击菜单【文件】->【选项】->【加载项】,找到管理Excel加载项,点击【转到】弹出加载项列表选择窗口,点击【浏览】,选择【集算器桌面版安装目录】/esProcDesk/bin下的插件ExcelSpl.xll,确定新增的加载项为勾选状态后,重启Excel生效。成功加载后再查看加载项如下图:

undefined

加载成功后,Excel中将增加spl()函数

当前步骤容易遇到的问题:

情况1

选择完插件文件ExcelSpl.xll点确定后,出现鼠标一直转,始终不进入下一步,此时可以强制中断Excel并重启Excel

重启Excel后会提示:某某加载项加载不成功,是否需要禁用?

点击禁用,然后重新按上述操作重新加载ExcelSpl.xll插件即可成功。

情况2

选择完插件文件ExcelSpl.xll点确定后,出现鼠标一直转,始终不进入下一步,此时可以强制中断Excel并重启Excel

重启Excel后没有任何提示,此时打开加载项配置页面,会发现插件可能已经加载成功(出现前述加载成功画面)。

情况3

选择完插件文件ExcelSpl.xll点确定后,出现鼠标一直转,始终不进入下一步,此时可以强制中断Excel并重启Excel

重启Excel后没有任何提示,此时打开加载项配置页面,会发现插件并没有加载成功,重复操作加载,依旧出现卡死现象,始终无法加载成功

此时解决办法是先关闭Excel,然后再打开windows的注册表,添加如下注册表项:

undefined

补充说明:

1、 这里OPEN项的值为字符串/R再加上由双引号括起来的字符串,串的内容是加载项ExcelSpl.xll的全路径文件名

2、 注册表OPEN项的位置,取决于Excel安装版本,不同版本,这个路径可能不一样,比如Excel2019

【计算机\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\16.0\Excel\Options

低一些的版本可能是

【计算机\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\15.0\Excel\Options

具体位置和版本相关,根据实际情况灵活应对

添加完后,打开Excel,此时可以发现加载项配置页面里已经有插件了,表示加载成功。

4、在Excel中加载宏文件

打开【集算器桌面版安装目录】\esProcDesk\bin目录,找到esproc_template.xla文件,把它复制到【Excel 安装目录 \XLSTART】目录下,例如:C:\Program Files\Microsoft Office\Office16\XLSTART,只有放在此目录下的宏文件,Excel才会在启动时自动加载,放别的目录无法加载成功。

打开Excel,点击菜单【文件】->【选项】->【信任中心】->【信任中心设置】->【宏设置】中勾选【启用所有宏】,然后重启Excel

补充说明:

1、 不同版本的Excel该目录可能不同,所以一定要打开Excel找到这个目录,再把esproc_template.xla复制过去

2、 esproc_template.xla中定义了一个宏及其热键(ctrl-enter),用于处理多返回值,有冲突时可以修改热键。

3、 spl()函数返回单值时,不需要用到这个宏,也不必加载此宏文件。

5、配置 SPL IDE 主目录

SPL IDE 中要配置【主目录】,被调用的脚本文件(.splx)必须位于主目录下,否则找不到,配置方法如下:

打开 SPL IDE,点击菜单【工具】->【选项】->选择【环境】->找到【主目录】

undefined

配置完主目录后,需要重启 SPL IDE 和Excel才会生效

6、日志

可以通过日志文件查看异常信息,日志文件位于%appdata%\esproc\tmp\Excel.log

第 2 章 在 Excel 中使用 spl() 函数

安装配置好之后,Excel中会增加spl()函数,其功能是在Excel单元格中执行SPL脚本并获得计算后的结果集。

其语法有两种:

l spl(exp, arg1, …)

参数说明:

exp

=开头的SPL语法的表达式串。其中用问号代表参数引用,?1对应第一个参数,?2对应第二个参数,依此类推

arg1,…

参数值,参数可以有0个或多个,至多30个。可以填入常数,或引用当前Excel单元格,允许单格和片区。

复杂计算需求,执行用SPL写好的脚本文件,格式为

l spl(splx(…), … )

参数说明:

splName()

脚本文件名称,无后缀,统一用.splx,且必须加上()

脚本文件中定义的参数,按顺序和后面赋予的参数匹配

arg1,…

同前面规则

spl()函数可能返回单值、一维数组或二维数组。

示例1:无参数

生成100以内的整数随机数:

undefined

计算结果:

undefined

示例2:单个参数

有如下Excel文件,第一列是随机数的范围,第二列是根据第一列给定的范围,生成一个相应范围内的整数随机数:

undefined

计算结果:

undefined

B2单元格拖动复制到每一个格子:

undefined

示例3:表达式中有双引号

有如下日期数据:

undefined

由于某些原因,这些数据没有日期格式,无法被Excel识别,被当成了普通的数字串,现在需要把它们变成日期数据。

SPL有很简单的写法:=date(A2,"yyyyMMdd"),这样会按照"yyyyMMdd"的格式,把A2解析成日期。但是这样的SPL脚本里本身含有双引号,写到spl()函数里,需要使用Excel的规则对双引号进行转义,即把"写成"",如下所示:

undefined

运算结果如下:

undefined

然后把B2拖动复制到每一行,结果如下:

undefined

补充说明:

1、 需要先把日期数据所在单元格类型设为文本类型,设置方法如下:

选中单元格->鼠标右键点击【设置单元格格式】->在【数字】tab页中选择【文本】

2、 确认设置成功的标准是单元格的左上角有个绿色的小三角,如果按照1中操作,没有出现绿色小三角,可以双击单元格进入编辑状态,然后移开焦点即可

示例4:多个参数

有如下Excel文件,其中r, g, b, a分别代表颜色中的红、绿、蓝及透明分量:

undefined

现需要在右边增加一列,算出每一行颜色分量组合成的颜色值,输入SPL代码如下:

undefined

代码中分别用?1,?2,?3,?4对应第一个参数A2,第二个参数B2,第三个参数C2,第四个参数D2

输入完上述表达式后,即可算出当前行的颜色值如下:

undefined

然后再把E2单元格拖动复制到每一个格子

undefined

示例5:一行片区参数

Excel文件如下图所示,现需要对每行数据去掉最大值和最小值,再求中位数,把结果填在最右侧的列中:

imagepng

I2单元格中输入=spl("=?.conj().sort().m(2:-2).median()",A2:H2)Excel将把A2:H2单元格片区组成的数组传给SPL表达式"=?.conj().sort().m(2:-2).median()",替代其中的参数?,返回去掉最大值和最小值后的中位数,计算结果:

undefined

补充说明:

1Excel会自动把数组参数处理成二维数组,即使事实上只一行行(或一列)也是如此,因此在SPL脚本里需要用conj()拼成单层序列。

此时再把I2单元格的表达式向下拖拽复制到下面的每一行,即可自动算出所有行的中位数,如下图所示:

undefined

示例6:一列片区参数

Excel文件如下图所示,现需要对每列数据去掉最大值和最小值,再求中位数,把结果填在最底下的行中:

imagepng

A14单元格中输入=spl("=?.conj().sort().m(2:-2).median()",B1:B13)Excel将把B1:B13单元格片区组成的数组传给SPL表达式"=?.conj().sort().m(2:-2).median()",替代其中的参数?,返回去掉最大值和最小值后的中位数,计算结果:

undefined

补充说明:

1Excel会自动把数组参数处理成二维数组,即使事实上只一行行(或一列)也是如此,因此在SPL脚本里需要用conj()拼成单层序列。

此时再把B14单元格的表达式向右拖拽复制到右边的每一列,即可自动算出右边所有列的中位数,如下图所示:

undefined

示例7:多行多列片区参数

有如下Excel文件:

undefined

现在需要在右侧增加一列,计算取样数据的累计平均值,即第一天的记录只算第一天的平均值,第二天的记录则算前两天总的平均值,第三天则算前三天的总平均值,依此类推。同样的,计算平均值之前需要先去掉最大值和最小值,计算结果如下:

undefined

Z3单元格中输入表达式:=spl("=?.conj().sort().m(2:-2).avg()",$B$3:Y3),此时算出结果5.40

从表达式可以看出,传入的参数$B$3:Y3,第一个单元格加了$符号,因为要算的是累计平均,在向下复制该表达式时要保持始终从B3单元格开始累计,所以加$

Z3单元格的表达式拖动复制到每一行,最后算出所有行的累计平均值

示例8:混合参数

有如下Excel文件,为每一天24个小时的整点取样数据:

undefined

现在需要在右侧增加一列,找出截止到当天本月所有取样数据中的最大n个值的平均值,n取决于Z列的数据。

undefined

AA3中输入SPL表达式如下:

=spl("=?1.top@2(-?2).avg()", $B$3:Y3, Z3)

第一个参数:=?1.top@2(-?2).avg() spl公式,表示对参数?1topnn由参数?2决定;然后再对topn算平均值

第二个参数:$B$3:Y3,是传给spl公式的参数,对应?1

第三个参数:Z3,是传给spl公式的参数,对应?2

AA3的公式拖动复制到每一行,最后结果如下:

undefined

示例9:返回一维数组

生成10100以内的随机整数,按顺序填入第一列中:

undefined

输入表达式后,按ctrl-enter执行在esproc_template.xla中定义的宏,将计算结果填充到相邻格中:

undefined

表达式返回一维数组时,按ctrl-enter后,会把数组成员以当前格为起始格,向下依次填入一列单元格中。不使用ctrl-enter则只会将第一个成员填入表达式所在格。

示例10:返回二维数组

现有Employee.xlsx文件内容如下:

undefined

现需要按部门DEPT字段统计每个部门的人数,在A1格输入SPL脚本如下:

undefined

Employee.xlsx最好放在 SPL IDE 主目录下,这样可以不写路径名找到,否则要在表达式中写上全路径名

ctrl-enter将计算结果填入相邻单元格:

undefined

补充说明

1、 返回二维数组时也需要按ctrl-enter触发填充动作,以当前格为起始格,向右向下依次填入一片单元格中。不使用ctrl-enter时只会在表达式所在格填入数组的第一个成员。

2、 SPL脚本中如果有",需要按Excel规则进行转义,写成""

示例11:动态拼SPL表达式

有如下Excel文件:

undefined

现在需要在右侧增加一列,计算取样数据的累计汇总值,汇总函数由Z列给出的函数名决定,如果是median,则算中位数;如果是avg则算平均值;sum则求和;依此类推。

AA3单元格中输入表达式:=spl("=?.conj()."&Z3&"()",$B$3:Y3),第一个参数"=?.conj()."&Z3&"()"是动态拼出的SPL表达式,第二个参数$B$3:Y3传入从B3Y3组成的数组。

传入参数$B$3:Y3中,第一个单元格加了$符号,因为算的是累计汇总,在向下复制该表达式时要保持始终从B3单元格开始累计,该单元格不能变化。

AA3单元格的表达式拖动复制到每一行,最后算出所有行的累计汇总值。

undefined

第 3 章 SPL 的编辑

某些计算的SPL表达式可能很复杂,甚至不止一句,直接在Excel中编辑时很不方便,SPL IDE 提供了Excel Copy/Paste功能,可以将编辑好的代码复制到Excel中使用

示例12:用 SPL IDE 生成复杂SPL脚本

Excel数据文件如下所示:

undefined

现在需要在底下增加一行,计算1月份中,每个时辰的取样数据的平均值,希望写一个表达式,就把整行的平均值都算出来,不需要每个格子都复制一遍表达式。

计算结果如下所示:

undefined

本例需要把B2:Y33作为参数,打开 SPL IDE,定义参数如下:

undefined

注意:这里参数名称必须是B2:Y33

编辑脚本:


A


1

=E('B2:Y33')

/将传入的二维数组参数'B2:Y33'变成序表,第一行当列名。因为参数名和单元格同名,要加上单引号区分

2

=to(24).("avg('"+string(~)+"'):'"+string(~)+"'")

/因为列名为1-24,为了简化书写,动态拼出24个列的汇总表达式

3

=A1.groups(;${A2.concat@c()})

/将汇总表达式拼入groups函数里,算出所有字段的平均值

4

return A3(1).array()

/将结果记录转成单层序列并返回

着点击菜单【编辑】->【复制】->Excel 复制】,此时即可把代码复制到剪贴板上。

5CDLOJ2_0G_TNSNM1Q4png

回到Excel文件,在单元格B34中,按ctrl-v即可把代码粘贴进来,如下所示:

undefined

此代码返回的是一维数组,按ctrl-enter,把返回数组值按顺序向右填到一行单元格上,最后结果如下所示:

undefined

补充说明:

1、当返回结果为一维数组时,扩展方式默认为纵向,用户可以在 Excel 中点击【加载项】-【SPL】-【Show Setting…】选择扩展方式:【Horizontal display】未勾选状态表示纵向扩展,勾选则为横向扩展。按 ctrl-enter 后,纵向扩展时结果集向下按顺序填充单元格,横向扩展时结果集向右按顺序填充单元格

2、当传给SPL脚本的参数是一片(单行、单列、多行多列)单元格时,会自动组成两层序列,此时在SPL脚本中可以用E()函数转成序表

3、参数的规则和单行代码一样,用?1表示第一个参数,?2表示第二个参数,依此类推

示例13:返回 SPL IDE 中修改脚本

接上例,代码复制到Excel中使用后,我们可能把 SPL IDE 关了,代码也没有保留,此时如果需要修改此代码,直接在Excel里修改肯定不方便,我们可以用 SPL IDE 的Excel Paste的功能把SPL脚本复制回 SPL IDE 中修改,操作方法如下:

Excel中选中spl完整代码,按ctrl-c把代码复制到剪贴板上,然后打开 SPL IDE,点击菜单【编辑】->【粘贴】->Excel 粘贴】,此时即可把代码原样恢复到 SPL IDE 中,如下所示:

undefinedundefined

示例14:超长SPL脚本

现有Employee.xlsx文件(该文件必须放在 SPL IDE 的主目录下)内容如下:

undefined

需要对该文件做查询,查询结果只需要显示EID,NAME,GENDER,DEPT,SALARY,BIRTHDAY,HIREDATE,STATE这几个字段,SURNAME字段不用显示,查询条件如下:

undefined

此例中需要传到SPL脚本中的参数有B1, B2, D1, D2, F1, F2

打开 SPL IDE,首先定义参数如下:

undefined

编辑脚本如下:

undefined

接着点击菜单【编辑】->【复制】->Excel 复制】,此时即可把多行代码复制到剪贴板上。

5CDLOJ2_0G_TNSNM1Q4png

再打开Excel,在单元格A4中,按ctrl-v,就把代码粘贴进来了,如下所示:

由于Excel表达式中的字符串有长度限制,超长代码复制至Excel时会自动被拆成多段,每段长度不超过240,代码段用\结尾,表示代码尚未结束,下一个参数依旧是代码

输入完上述代码后,按ctrl-enter,结果如下:

undefined

示例15:直接调用SPL脚本文件

对于较长的SPL代码,也可以不把SPL脚本复制到Excel中,而是在spl()函数里直接调用SPL脚本文件。如上例,我们把SPL脚本文件存为【SPL IDE 主目录】\Example15.splx,然后在ExcelA4单元格中写入如下表达式:

undefined

从表达式中可以看出,直接调用脚本文件的语法是:=spl("脚本文件名(?n……)",参数n….),脚本有几个参数,括号里就写几个问号,和传入参数的对应关系规则与直接写代码是一样的,都是?1对应第一个参数,?2对应第二个参数,依此类推

输入完上述代码后,按ctrl-enter,结果如下:

undefined


以下是广告时间

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



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