我用Python开发了一个强大的excel自定义函数，需要的话学起来

猪猪俠

广州疫情放开后，自己不想出门，宅在家又捣鼓了我的EFunction项目。这次用python开发了Excel一个自定义函数了，准确点是五个自定义函数，主要为了完成一项功能。超级好用，不仅感叹用python开发Excel自定义函数效率太高了（相比VBA），python的强大之处就在于有海量的数据分析处理工具包。废话不多说，文章主要分两部分：

• 第一部分，介绍开发自定义函数过程，包括源代码，后续开发自己的自定义函数，参照这个模板就可以了。
  
• 第二部分，介绍这个函数的使用用法，伸手党，基本可以直接使用了，拿到源代码后稍加修改，就可以使用这个自定义函数了。
  

第一部分函数开发

Python开发自定义函数，是在之前做的EFunction项目基础之上搞的。整个工作耗时一天，又是充实的一天（油腻宅男糟糕的一天）。
项目完整代码如下图。


项目需求背景：

有个网友提议说，EFunction内置SQL引擎不完美，大体量数据时，数据体量比较大，执行起来，非常耗时。数据如果全部存储在Excel文件内，随着数据累计，Excel文件会越来越大，同时不希望将数据导入数据库这么麻烦，希望有个简单的方式用来存储数据。
有了这个需求后，了解知道sqlite数据库为嵌入式数据库，该数据库具有以下优点：

• 免费，无需授权问题，很多开发语言均支持该数据库开发，当然Python也支持；
  
• 跨平台，基本不用考虑数据库安装、兼容性问题，数据库直接使用，无须安装；
  
• 简约而不简单，该数据库虽然简单，但功能却不简单。
  

基于以上背景决定使用sqlite作为EFunction新的自定义函数的数据库，因为EFunction工具已经支持Python开发自定义函数了。所以就决定使用Python来开发自定义函数。
函数企划：

Excel自定义函数，需要综合考虑易用性和功能性。
易用性，尽量使函数简约，尽量符合Excel系统自带的函数使用习惯，函数参数不应该太多。力求函数的学习曲线平缓，易学易用。
功能，我总共考虑了一下几点：

• 数据库主要用来存储数据，尽量扩充Excel只能够存储百万行数据的缺点，数据主要是不变的数据，工具不是用来替代数据库（数据库可以使用EFunction自定义函数，和数据库互动，或者使用EFunction的jupyter notebook功能和数据库互动），工具主要目的为扩充Excel数据存储限制及计算限制；
  
• 自定义函数能够从数据库之中查询数据，同时也能向数据库之中写入数据；
  
• 考虑到Excel数据易变特点，自定义函数应该考虑到灵活性。
  

基于以上三点，决定开发五个自定义函数，功能基于不同使用场景需求：
自定义函数及功能：

序号	函数名称	功能注解	备注
1	ef_freeze	将Excel数据存储到数据库之中，数据支持增量存储和全量存储
2	ef_sql	从数据库之中查询数据
3	ef_table	显示数据库之中，已经有的表格
4	ef_run	执行SQL脚本
5	ef_free	对Excel选区内数据进行查询，创建临时数据库

前四个函数（ef_freeze、ef_sql、ef_table、ef_run）是高度相关的，使用同一个数据库，其中：

• ef_freeze函数，向固定的数据库之中写入数据，该函数向数据库指定表之中插入数据，如果数据插入成功返回true，失败，返回错误原因
  
• ef_sql函数，该函数就是文ef_freeze函数指定的库之中的数据查询数据；
  
• ef_table函数，该函数将显示freeze管理的数据库之中有所有数据表名称；
  
• ef_run函数，该函数，将执行SQL命令，函数执行成功返回true，失败返回错误原因。
  

第五个函数ef_free函数，函数功能只有查询功能，函数数据不会被存入ef_freeze管理的数据库。其数据只会被存储临时数据库之中。查询完成后数据就失效。
第二部分函数使用

上文已经介绍了开发出来的五个自定义函数。任何Excel文件，使用这些函数前，先要注册这五个函数。在EFunction内，启动jupyter notebook文件，第一个cell之中，粘贴上文截图之中完整脚本。执行完成后，会在jupyter notebook当前目录生成文件。
第二个cell之中，执行以下代码，会向window系统注册这五个自定义函数。
%run efunction.py第三个cell之中，执行以下脚本，在当前Excel 工作簿之中，就可以使用这五个自定义函数了。需要注意该工作簿，需要另存为xlsm格式文件。下次打开这个文件后，可以直接使用自定义函数。不然又得重新执行，这三个步骤。
%ef_vba PythonComTestObject 使用ef_freeze将数据存入数据库

ef_freeze函数用法非常简单，函数有三个参数，ef_freaze(data,name,model)

• 第一个参数data：为需要存入数据库的数据选区，
  
• 第二个参数name：为存入数据库的数据表名称
  
• 第三个参数model：为存入数据库的模式，两种模式分别为“replace”，数据会全量更新，替换掉数据库之中已经存在的数据，该模式适合数有更新，需要进行替换场合，“append”向数据库之中已经存在的数据之中追加数据。
  

下图为全球订单完整数据（网络公开数据集），使用ef_freeze函数，将下表数据存入数据库。完整公式为：
=ef_freeze(表1[#全部],"opdate","replace")。
执行完成后函数返回true，表示数据存入成功。如果是其他信息，说明存在问题。查找具体原因



样例数据

需要注意，如果数据存入数据库成功，请将该函数删除，或者给注释掉，不然该函数会重复向数据库插入数据。
使用ef_table查看数据库之中有哪些表

ef_table函数用法非常简单，该函数无参数，保持为空即可，数据库内如果有多个表，该函数，将结果以数组方式返回，365支持动态数组，可以将所有的表显示出来，如果是低版本Excel可以使用ETResize函数。完整用法为：=ETResize(ef_table)函数。



获取数据库之中所有的数据

使用ef_sql从数据库之中之中数据

通过上述步骤，已经成功将数据插入数据库之中，现在就可以使用ef_sql函数，执行SQL查询命令，从数据库之中查询分析数据了。
统计每个细分市场销售额，销售额按照降序排序，完整SQL脚本为：
select
a.细分市场,
sum(a.销售额) 销售额  
from opdate as a
group by a.细分市场
order by 销售额 desc查询结果为：



ef_sql查询结果

需要注意ef_sql函数，只能够查询数据，并将查询结果返回给Excel。函数结果为数组数据。
使用ef_run执行SQL脚本，该函数能够执行完备的数据操作。

ef_run命令，函数功能比较完备，能够执行所有的SQL命令，不过该函数执行查询命令时，不会返回结果，如果使用查询命令，请使用ef_sql函数。
ef_run函数也只有一个参数，函数参数为SQL脚本，例如上述已经在数据库之中创建了数据表。我想将“订单”表给删除掉
drop table 订单


删除表，成功返回TRUE

还有个需要，将上述统计出来的分析市场销售额，在数据库之中存入一个叫做销售额的表之中。那SQL脚本为：
CREATE TABLE 细分销售额 AS
select
a.细分市场,
sum(a.销售额) 销售额  
from opdate as a
group by a.细分市场
order by 销售额 desc查询结果为：



将查询结果插入新的表

通过上面启发之后，你是否想到，平时数据处理，可以使用ef_run函数执行SQL批处理脚本，将处理结果存入数据库，执行完成后使用ef_sql，将计算结果返回到Excel之中。这样子，数据计算交给数据库，数据结果排版交给Excel完成，达到双剑合璧。
常见问题

ef_free函数支持连接查询吗，该函数支持连接查询的，但该函数因为只有一张临时表，这就是限制了和多表之间数据互动查询。如果复杂查询，建议将数据存储固定库之中，再进行复杂查询。
ef_free函数适合场合 每引用一次函数，函数就会临时创建数据库，所以该函数效率相对较低。该函数适合数据灵活，易变且数据量相对较少场合，其他场合请使用固定数据库函数
ef_run函数使用场合 该函数使用数据批处理场合，也可以用来数据库管理工作
ef_sql函数使用场合 该函数相比ef_run函数，该函数将查询结果返回到Excel之中
总结

时间关系，先介绍到这里，五个新的自定义函数，完整脚本，建议关注EFunction函数大师拿完整代码。因为Excel管理数据比较多元化，而sqlite是规范化数据库，两者之间数据互动，应该还存在问题，大家使用过程之中有什么问题，欢迎提出来。本节就到这里。后面再分享怎样用好这个工具样例。
Hi Excel和Python：花了一周时间，终于搭建好了Excel和Python交互数据分析平台
Hi Excel和Python：Excel 高级函数插件EFunction加载和卸载

季光波

在哪里获取源码

广安市华鑫建设

关注EFunction函数大师，索要！