任意工作簿快速添加自定义公式到名称管理器以及使用DuckDbQuery自定义函数

自从学习了动态数组公式，把有些常用的公式做成了lambda形式，每次用的时候手动复制粘贴到目标工作簿，但略显繁琐。思考是否有简便办法，想用JSA宏实现，但不会写相应的代码，于是手动粘贴公式到名称管理器并录制为宏，参考其中的代码，

function c宏_加载自定义函数(){
    ActiveWorkbook.Names.Add(
        "cw_λ拼接同名表index",
        "lambda(_工作簿完整路径,_表名,LET(_ix,IFNA(DuckDbQuery(\"load rusty_sheet;select '\"&_工作簿完整路径&\"'as 工作簿名称, '\"&_表名&\"'as 表名 , * from read_sheet('\"&_工作簿完整路径&\"',sheet='\"&_表名&\"',header=false,error_as_null=true)\"),\"\"),IF(left(index(_ix,1,1),4)=\"\#ERR\",1/0,_ix)))"
    );    
}

如果需要添加其他自定义公式，在该函数内继续新增ActiveWorkbook.Names.Add(函数名称，自定义lambda函数）代码即可。

将以上代码保存到xlam文档并加载，把“c宏_加载自定义函数()”添加到快速访问工作栏，添加效果及加添方法如下图：

以后需要快速使用自己定义的函数，就可以直接点击快速访问工具栏里的图标，自定义函数就会批量快速添加到名称管理器里。

小提示，根据自身习惯和喜好规律命名自定义函数有利于快速输入指定函数

以上就是我目前使用的方法

我经常会汇总大量表格，看到论坛里说有duckdb插件可以在WPS表格里使用，该插件大家可以在论坛搜索，利用插件自带的DuckDbQuery函数可以运行SQL语句，duckdb有个第三方插件rusty_sheet很好用，可以读取xls、xlsx、xlsm等多种格式。

综合运用DuckDbQuery、rusty_sheet以及wps里的其他表格函数，我编写了如下自定义函数，该函数有两个参数，一个是要读取工作簿的完整路径，另一个是表名，，实现了不打开就能读取任意工作簿的任意工作表。配合WPS的reduce函数就可以把多个工作簿的多个工作表拼接到一个工作表，如果使用了365版本，基于新出的单元格列表功能，使用map、flatten这两个函数，也可以实现跨工作簿多表拼接，该自定义函数还做了容错处理，如果工作簿的完整路径或者表明不存在，则显示为1/0这种错误值，后续数据处理可以通过tocol函数的第二参数过滤掉错误值。

=LAMBDA(_工作簿完整路径,_表名,LET(_ix,IFNA(DuckDbQuery("load rusty_sheet;select '"&_工作簿完整路径&"'as 工作簿名称, '"&_表名&"'as 表名 , * from read_sheet('"&_工作簿完整路径&"',sheet='"&_表名&"',header=false,error_as_null=true)"),""),IF(LEFT(INDEX(_ix,1,1),4)="#ERR",1/0,_ix)))

以上就是我想和大家分享的内容。