工作表函数

一个视频快速了解工作表函数

play

工作表函数是SqlCel的得意之作。它简单、灵活、强大,适于制作模板比较固定的表格,尤其是当数据源不在Excel内部的时候。

工作表函数分统计函数和查询函数,它的功能和参数与对应的原Excel工作表函数基本相同。其命名基本上都是在原工作表函数的前面加了一个"D_",其目的是为了便于理解函数的功能和用法。

SqlCel工作表函数与原工作表函数在功能上最大的区别在于原函数用于处理Excel工作表里面的数据,SqlCel工作表函数可用于处理数据库、文本、Excel表格等各种来自当前工作簿之外的数据,同时它也可以配合Excel原生工作表函数处理数据。

sqlcelfuncsinstall

当处理的数据源来自文件夹时,SqlCel的工作表函数不支持WPS。

工作表函数的使用步骤

引入工作表函数

使用SqlCel的工作表函数首先需引入函数。点击“引入函数”按钮即可。SqlCel函数以VBA接口的形式被引入,所以建议将引用函数的工作簿另存为启用宏的工作簿。函数被引入一次之后无需再次引入。

定义数据源

点击“工作表函数”下的“定义数据源”按钮打开定义数据源对话框。第一个字段“SQL/VBA函数名称”用于表示数据的来源,第二个字段“生成的表名”用于给数据源取一个名称便于函数引用。

可以通过以下两种形式定义数据源

  1. SQL语句

用SQL语句定义数据源需指明SQL语句的提交方向(连接名)。提交方向通过“SqlCel”选项卡下的“数据库连接”功能定义。
SQL语句可提交给6种数据源。
a. Mysql
b. SqlServer
c. Access
d. Oracle
e. Csv/Txt
f. Excel
以上a,b,c,d四种数据源的定义方法相同。比如我们要查询Mysql的数据,首先需用“数据库连接”功能定义一个连接,此时会得到一个连接名,比如这个连接名为"FirstMysqlConn",我们可以在“SQL/VBA函数名称”字段下写SQL语句如下:

Mysql!FirstMysqlConn->SELECT * FROM students

以上Mysql表示查询的数据源为Mysql,如数据源为SqlServer则对应为SQL,Access对应为Access,Oracle对应为Oracle。
FirstMysqlConn为Mysql下的一个连接名,SELECT * FROM students为具体的SQL语句。

如果数据源来自csv/txt文件,首先需在“数据库连接”处定义csv/txt文件所在的文件夹。然后我们可以在“SQL/VBA函数名称”字段下写SQL语句如下:

Folder->SELECT * FROM students.csv

如果数据源来自Excel文件,首先需在“数据库连接”处定义Excel文件所在的文件夹。然后我们可以在“SQL/VBA函数名称”字段下写SQL语句如下:

Folder!students.xlsx->SELECT * FROM [Sheet1$]

如果数据源来自Excel文件,首先需在“数据库连接”处定义Excel文件所在的文件夹。然后我们可以在“SQL/VBA函数名称”字段下写SQL语句如下:

Folder!students.xlsx->SELECT * FROM [Sheet1$]

SQL语句的特性:可以在SQL语句中引用Excel单元格的值。比如:

Mysql!FirstMysqlConn->SELECT * FROM students[sys!a1]

此处[sys!a1]表示引用表名为"sys"的工作表的A1单元格的值,假如该单元格的值为202107那么该SQL语句解析后的结果等同于:

Mysql!FirstMysqlConn->SELECT * FROM students202107
  1. VBA函数

通过VBA函数返回数据源需注意两点
第一函数不可以带参数。第二函数的返回值的数据类型需为DataTable或Recordset数据集。DataTable对应的是SqlCel函数中返回的QAX数据类型。建议返回DataTable数据集,因为如果返回Recordset,程序还会把它转为DataTable。

举例:
有以下VBA函数:

Function source_data()
    Set source_data = s.filetoqax("C:\texfiles\students.txt")  '将txt文本读入qax(datatable)
    Set source_data = s.qaxcoltonum(source_data, "score") '将qax的字段"score"转为数字类型
End Function

在定义数据源的时候可以在第一个字段“SQL/VBA函数名称”下写"source_data"表示引用的函数名称。

给数据源命名:

数据源定义完毕后需为数据源取一个表名,以便于引用。比如此处我们为定义的数据源取名为"students"。假如数据源有两个字段"student_name","score"。我们要计算这些学生的平均成绩可以在工作表中写函数如下:

=D_AVERAGE("students!b:b")

如果我们需要给数据源添加一个行标识作为辅助列,在给数据源命名的时候,可以在名称最后添加符号"#",则会自动给数据源添加一个字段名为"#"从0开始编号的辅助字段。比如此处名称可取名为"students#"。

表名的特性:可以在表名中引用Excel单元格的值。比如"students[sys!a1]",可以将sys!a1的值引用到表名中。

定义表名及引用表名举例:

比如我们定义的数据源为

Mysql!FirstMysqlConn->SELECT * FROM students[sys!a1]

该数据源对应的表名为"students[sys!a1]",同样是计算学生的平均成绩,在工作表中写函数如下:

=D_AVERAGE("students[sys!a1]|b:b")

定义的表名在函数中引用的时候需原模原样的放在字符串中。

引用数据源

  1. 用字母编号引用,如"表名|A:D"
    表名即给定义的数据源取的名字,A:D表示引用表的第1到第4个字段。如果只引用一个字段比如引用第一个字段需写成"表名|A:A"而不可以写成"表名|A"。如果表的字段很多分不清每个字段对应的字母序列可以通过“定义数据源“的”预览数据源”功能在Excel中显示每个字段对应的序列。
  2. 用数字编号引用,如"表名|2:4"
    表示引用表的第2到第4个字段。如果只引用一个字段,比如引用第二个字段需写成"表名|2:2"而不可以写成"表名|2"。
  3. 用负数编号引用,比如"表名|-2:-2"
    表示引用表的倒数第二个字段。
  4. 用字段名称引用,比如"表名|字段名"
  5. 用冒号引用连续字段
    "表名|字段1:字段2"表示引用从字段1到字段2之间的所有字段
    "表名|F:"表示引用从F列(第6个字段)到最后一个字段的所有字段
    "表名|:F"表示引用从A列(第1个字段)到F列(第6个字段)的所有字段
    "表名|6:"表示引用从第6个字段到最后一个字段的所有字段
    "表名|:6"表示引用从第1个字段到第6个字段的所有字段
  6. 用逗号引用多个字段
    比如"表名|字段名1,字段名2,字段名3",表示同时引用三个字段
    引用连续字段及多个字段可用于除了D_COUNTIF和D_COUNTIFS之外的所有统计函数,多用于D_SUMIF,D_SUMIFS表示对符合条件的多个字段同时求和。比如:
=D_SUMIF("产品订购|产品名称","in('产品1','产品2','产品3','产品4')","产品订购|2021/6/1:2021/6/30")

用于设置条件的字段只能引用一个字段,比如以上"产品订购|产品名称"只引用了一个字段,而统计字段可引用多个字段,比如以上"产品订购|2021/6/1:2021/6/30"引用了"产品订购"表中"2021/6/1"到"2021/6/30"中的所有字段。

  1. 省略表名引用字段
    当省略表名时,默认使用的表名为公式所在工作表的表名。比如以上公式,如果写公式的工作表名称为"产品订购",则公式可简写为:
=D_SUMIF("|产品名称","in('产品1','产品2','产品3','产品4')","|2021/6/1:2021/6/30")

数据源被引用的时候会被加载到Excel内存,第一次引用会比较耗时,之后直接从内存读取会非常快速。

重算公式

有时候公式引用的单元格的值发生了改变但是公式却没有重算,比如:

=D_FIND("产品订购[sys!B1]#|B:B","产品订购[sys!B1]#|A:A","产品1")

在公式中引用到了sys!B1的值,但是这个引用是在字符串中,如果sys工作表的B1值发生了改变,该公式不会重新计算。此时可点击"SqlCelINN->工作表函数->重算当前工作表"强制重算。不过此处更建议尽量在该公式中引用sys!B1单元格,如下:

=D_FIND("产品订购[sys!B1]#|B:B","产品订购[sys!B1]#|A:A","产品1","产品订购[sys!B1]#|A:A","<>'" & sys!B1 & "'")

在以上公式中最后一个条件就是为了在公式中引用sys!B1,当改变sys!B1的值时,公式也会自动重新计算。

数据源管理

管理数据包括数据源的定义(添加)、删除及修改。点击“定义数据源”按钮打开“定义数据源”对话框进行数据源的管理。

定义数据源

具体定义数据源的方法可参考用法说明。

删除数据源

选中表格中的一行或多行 -> 按"Delete"键删除所选的行 -> 点击“定义数据源”按钮重新定义,即可删除数据源。

修改数据源

修改“VBA函数名称/SQL语句”字段中定义的数据源。此时可修改VBA函数或SQL语句让数据源发生改变,也可以修改函数名称改变数据来源。

预览数据源

预览数据源用于查看已经定义的数据源。选中一行后点击“预览数据源”按钮即可将该行定义的数据源读入当前Excel工作表中。预览数据源便于查看定义的数据源中每个字段对应的字母序列。

刷新缓存数据

刷新缓存数据用于将已读入内存中的数据刷新至最新状态。

函数解释

每一个函数的使用方法在插件里面都有详细的说明。