50种系统函数使用介绍
目前支持五大类型的函数:数学函数
、日期函数
、文本函数
、逻辑函数
、高级函数
,50多种类型,基本满足日常的数据处理需求。
一、数学函数
SUM
功能: 返回多个数字的总和
用法: SUM( 数值1
,数值2
,数值3
,... )
示例: SUM(3,6,8), 结果:17
如果有字段为空时,则按0计算。
其功能和下面的自定义公式运算,得到的结果相同:
- SUM公式
- 自定义运算公式
AVERAGE
功能: 返回多个数字的的平均值
用法: AVERAGE( 数值1
,数值2
,数值3
,... )
示例: AVERAGE(3,6,8) ,结果:5.67
为空为0时怎么计算
字段为空时
例如,3个字段求平均值,其中一个字段为空,另外两个分别是2和4,则平均值是(2+4)/2=3。
字段为0时
例如,3个字段求平均值,其中一个字段为0,另外两个分别是2和4,则平均值是(0+2+4)/3=2。
MIN
功能: 返回多个数字中的最小数
用法: MIN( 数值1
,数值2
,数值3
,... )
示例: MIN(3,6,8), 结果:3
如果有字段为空时,则按0计算。
MAX
功能: 返回多个数字中的最大数
用法: MAX( 数值1
,数值2
,数值3
,... )
示例: MAX(3,6,8), 结果:8
如果有字段为空时,则按0计算。
PRODUCT
功能: 返回多个数字的乘积
用法: PRODUCT( 数值1
,数值2
,数值3
,... )
示例: PRODUCT(3,6,8), 结果:144
如果有字段为空时,则按0计算。
其功能和下面的自定义公式运算,得到的结果相同:
PRODUCT公式
自定义运算公式
COUNTA
功能: 统计多个字段中,不为空的字段数量
用法: COUNTA( 字段1
,字段2
,字段3
,... )
示例: COUNTA(单选题1,单选题2,单选题3) ,结果:2,表示有两个字段不为空。
- 配置
- 效果
ABS
功能: 计算数字的绝对值
用法: ABS( 数值
)
示例:
- ABS(-3.991) , 结果是:3.991
INT
功能: 返回永远小于等于原数字的最接近的整数
用法: INT( 数值
)
示例:
- INT(3.991) , 结果是:3;
- INT(-3.991) ,结果是:-4,
MOD
功能: 返回两数相除的余数
用法: MOD( 被除数
,除数
)
参数说明: 两个参数都是必填的,可以是字段值,也可以是静态参数。
如果被除数字段为空时,则按0计算。 如果除数为空时或为0时,不计算。
示例:
配置
效果
ROUND、ROUNDUP、ROUNDDOWN
1、ROUND
功能: 按小数点指定保留位数,对数字进行四舍五入。
用法: ROUND( 数值字段或常数
,保留小数位数
)
配置示例: ROUND(3.14159,3)
效果示例
结果:3.142。如果第4位大于等于5,则进1位,如果第4位小于5,则直接舍去。
2、ROUNDUP
功能: 以绝对值增大的方向按指定位数舍入数字
用法: ROUNDUP( 数值字段或常数
,保留小数位数
)
示例: ROUNDUP(3.14159,3) ,
结果是:3.142。 无论3位数后的数字是否大于5,只要大于0都直接近1位。
3、ROUNDDOWN
功能: 以绝对值减小的方向按指定位数舍去数字
用法: ROUNDDOWN( 数值字段或常数
,保留小数位数
)
示例: ROUNDDOWN(3.14159,3)
结果:3.141。无论3位数后的数字否小于5都直接舍去。
注意: 1、ROUND、ROUNDDOWN 和 ROUNDUP函数中保留的位数要和数值字段的设置位数要保持一致,不然计算结果可能不合期望。
2、函数中保留位数的参数如果不写,则视为直接取整数。
CEILING、FLOOR
CEILING
功能: 以绝对值增大的方向按指定倍数舍入数字
用法: CEILING( 数值字段或常数
,基数
)
示例: CEILING(9,2)
结果:10,(大于9且是2的最小倍数)
FLOOR
功能: 以绝对值减小的方向按指定倍数舍入数字
用法: FLOOR( 数值字段或常数
,基数
)
示例: CEILING(9,2)
结果:8,(小于9且是2的最大倍数)
POWER
功能: 计算填入数值的次方
用法: POWER( 底数
,指数
)
示例: POWER(4,3)
结果:64。
LOG
功能: 计算填入数值的对数
用法: LOG( 真数
,底数
)
示例: LOG(9,3)
结果:2。
COUNTBLANK
功能: 计算参数中包含的空值个数
用法: COUNTBLANK( 数值1
,数值2
,数值3
)
示例: COUNTBLANK( 12
,
,
)
结果:2。
COUNTCHAR
功能: 统计文本字段的字符数量
用法: COUNTCHAR( 文本字段
)
配置示例
效果
RANDBETWEEN
功能: 随机返回两个数值之间的整数,负数也支持。
用法: RANDBETWEEN( 最小值字段
,最大值字段
)
配置示例
随机获取1到10之间的整数,也可能是1或10。
效果
NUMBER
功能: 将文本等类型的值转为数值
用法: NUMBER( 文本
)
示例:
先将文本类型转为数字,再和其他数字相加。
文本类型和数字类型相加
如果是文本和数值运算,运算结果是两个内容的拼接。
例如,1+5,得到15的文本内容。
效果
二、日期函数
NETWORKDAY
功能: 计算两个日期间工作日的天数。自动去除周六周日,如果特别的日期也算节假日,则可以指定去除。函数返回的是数字类型,数值字段、金额字段和文本字段可以使用此函数。
参数说明:
开始日期和结束日期都必填
去除的指定节假日非必填,如不填写,则只排除周六周日。如果排除指定节假日,则在[]中填写。
统计的日期:
统计的是晚于等于开始日期且早于等于结束日期的日期数。例如[5号,7号],统计到的是5号、6号和7号,共3个工作日。
用法: NETWORKDAY( 开始日期
,结束日期
,[节假日1
,节假日2
,..])
示例1:只排除法定周六周日
配置
NETWORKDAY( '2024-3-1','2024-3-4')
2号和3号是周六周日,结果得到1号和4号共2天.
示例2:除了周六周日,排除指定节假日
配置
NETWORKDAY( '2024-3-1','2024-3-6',['2024-3-4','2024-3-5'])
结果是
2
天。 2号3号周六周日,再排除4号和5号,只有1号和5号是工作日了。
MINTUE、HOUR
功能: 获取指定日期时间的小时数和分钟数。
用法:
和获取年月日的方法相同
HOUR(
日期时间
)获取到0-23的数字
MINTUE(
日期时间
)获取到0-59的数字
WEEKDAY
功能: 获取指定日期的是周几,1到7的数字,其中周一是1,周日是7。
用法: WEEKDAY( 日期时间
)
DAY、MONTH、YEAR
功能: 获取指定日期时间的年份、月份、日
用法:
三个函数用法相同
DAY (
日期
)获取到1-31的数字
MONTH (
日期
)获取到1-12的数字
YEAR (
日期
)获取到具体年份,如2022-12-12,获取到数字 2022
配置:
效果:
DATENOW
功能: 返回当前时间, 日期时间字段和文本字段可使用此函数。
用法: DATENOW() ,无参数。
配置
效果
DATEADD
功能: 为一个日期时间,增加一段时间得到新的日期或时间
用法: DATEADD( 日期
,'加减时长', 输出格式 )
参数:
加减时长 ,为文本类型,格式为: "加减符号" + “数字”+ "时间单位"
时间单位:'Y'-年;'M'-月;'d'-天;'h'-小时;'m'-分钟;
举例: '-1d' 表示减去1天, '+3m'表示增加三分钟,'+3M' 表示增加是3个月
输出格式 数值类型,1代表日期格式,2代表日期+时间格式
示例1: 根据入职日期,得出3个月后的转正日期。
公式:DATEADD( '2021-3-6','+3M',1)
结果是 2021-6-6
示例2: 根据工单提交时间,计算得出1个小时后的待办提醒时间。
公式:DATEADD( '2021-3-6 9:00','+1h',2)
结果是
2021-3-6 10:00
更多示例:
得出当前日期所在周的周一日期
- DATEADD(DATENOW(),CONCAT('-',WEEKDAY(DATENOW())-1,'d'),1)
得出当前日期所在周的周日日期
- DATEADD(DATENOW(),CONCAT('+',7-WEEKDAY(DATENOW()),'d'),1)
特别提醒
如果增加的时差不是固定参数,则需要使用CONCAT()拼接组合,例如:CONCAT('+',计算结果得出的数字
,'d')
DATEIF
功能: 计算两个日期间的时差(由于输入内容带单位,输出的是文本格式)
用法: DATEIF( 开始日期
,结束日期
,1,'输出单位' )
参数:
开始日期和结束日期 必填,可以是日期字段,也可以是静态日期参数。
格式化方式,必填,输入1或者2,如果字段本身有时间部分,则按实际计算,如果没有时间部分,则按格式化时间计算。
格式化1,开始日期的时间格式为00:00 结束日期的时间格式为 00:00
格式化2,开始日期的时间格式为00:00 结束日期的时间格式为 24:00
- 输出单位,非必填,'Y'-年;'M'-月;'d'-天;'h'-小时;'m'-分钟;如果不指定这个参数,则默认为'd'
示例1:
DATEIF( '2021-12-1','2021-12-2',1,'d' )
格式方式1,即计算2021-12-1 00:00 和 2021-12-2 00:00 的天数,结果是1
DATEIF( '2021-12-1','2021-12-2',2,'d' )
格式方式2,即计算2021-12-1 00:00 和 2021-12-2 24:00 的天数,结果是2
DATEIF( '2021-12-1','2021-12-2 12:00',2,'d' )
格式方式1,即计算2021-12-1 00:00 和 2021-12-2 12:00 的天数,结果是1,因为不满2天,向下取整。
配置和效果
时差1
时差2
效果:
如何使用计算结果再进一步进行数据计算
例如,DATEIF函数得到的结果是6天,需要再+5,想得到11。如果直接相加,得到的是 “6天5”这样一个内容。因此,我们需要先把单位天去掉再处理。
参考公式:NUMBER(FIND(DATEIF('2021-3-8','2021-3-14',2,'d'),"","天"))+5
先用FIND函数把天去掉,然后用NUMBER转换为数字格式。
三、文本函数
CONCAT
功能: 将多个内容进行拼接组合成一个新的内容
用法: CONCAT( 内容1
,内容2
....)
示例:CONCAT( 研发部
, -
, 小张
)
拼接的内容可以是字段内容,也可以结合静态参数进行组合拼接。例如部门字段和姓名字段拼接,中间加一个链接符 -。
得到的结果是'研发部-小张'
REPLACE
功能: 将某一个字符串中的某一段内容,替换为其他内容。
用法: REPLACE( 目标内容
, 第几个字符开始 , 几个字符 , 替换的内容
)
参数
第几个字符开始: 数字,1代表从第一个字符开始,且第一个字符也被替换。
几个字符: 数字,被替换的字符数量,字母、汉字、数字、空格都记为1个字符
示例
REPLACE( '19909090909',4,4 , '****' )
将手机号第4-7位,共4个字符,替换为**** ,
结果是:
199****0909
REPLACE( '刘德华',2,1 , '*' )
将姓名从第2位开始,共1个字符,替换为* ,
结果是:
刘*华
配置示例
MID
功能: 从一段内容中间提取若干字符
用法: MID( 目标内容
, 开始位置 ,长度 )
参数
- 目标内容 必填
- 开始位置 数字,必填,即从第几个字符开始截取
- 长度 数字,必填,即截取多少个字符
示例:MID( 412721200511273011
,7,4)
表示从身份证的第7为开始,共截取4个字符,得到的结果是2005
RIGHT
功能: 从一段内容的最右端开始截取指定长度的字符
用法: RIGHT( 目标内容
,长度 )
参数
- 目标内容 必填
- 长度 数字,即截取多少个字符,如果不填写,则只取最右边的字符。
示例:RIGHT( '412721200511273011',4)
表示从身份证最右边开始,共截取4个字符,得到的结果是3011
LEFT
功能: 从一段内容的最左端开始截取指定长度的字符
用法: LEFT( 目标内容
,长度 )
参数
目标内容 必填
长度 数字,即截取多少个字符,如果不填写,则只取最左边的字符。
示例:LEFT( '412721200511273011',2)
表示从身份证最左边开始,共截取2个字符,得到的结果是41
TRIM
功能: 根据一段内容,删除文本首尾的空格
用法: TRIM( 文本5
)
CLEAN
功能: 根据输入的目标内容,删除文本中所有空格
用法: CLEAN( 文本5
)
REPT
功能: 根据一段内容,按照指定的倍数生成重复的文本
用法: REPT( 目标内容
, 2)
参数
- 目标内容 必填
- 倍数 数字,必填。
示例
REPT('*',5) ,结果:* ,将*重复显示5次。
LOWER
功能: 将一段内容中的英文字母全部换为小写字母
用法: LOWER( 目标内容
)
示例: LOWER( 汉字aaaBBB
)
得到的结果是汉字aaabbb
UPPER
功能: 将一段内容中的英文字母全部换为大写字母
用法: UPPER( 目标内容
)
示例:UPPER( 汉字aaaBBB
)
得到的结果是汉字AAABBB
STRING
功能: 将内容转换为文本格式
用法: STRING( 内容1
)
示例:
STRING( 1+5)
结果是
6
,因为先计算1+5,然后转为文本STRING(1)+STRING(5)
结果是
15
,先将数字转为文本字符,再将内容拼接。
FIND
功能: 从一段文本中自左向右截取一段内容。
用法: FIND(原始内容
,"开始字符","结束字符")
- 开始字符:如果是空,表示从第一个字符开始返回
- 结束字符:如果是空,表示返回直至最后一个字符
返回结果中不包括开始和结束字符。
示例:
在文本字段中直接输入计算式(长乘宽),然后分别获取长和宽写入对应字段并用公式字段求结果。
获取长度值
开始字符为空,从第一个字符开始查找,遇到* 结束。
FIND('200*15',"","*")
获取宽度值
从* 开始向右,直到结束。
FIND('200*15',"*","")
FINDA
功能: 从一段文本中获取多段内容并组合成一个数组。
用法: FIND(原始内容
,"间隔符1","间隔符2")
- 间隔符1:如果是空,无结果
- 间隔符2,如果是空,无结果
返回结果中不包括间隔符。
示例:
从一段文本中执行获取()内的内容并组合成文本。
FINDA("(A)(B)(C)","(",")")
结果得到:A,B,C
函数中组成的数组中都不带[],例如本例子中,在字段中显示为:A,B,C
SPLIT
功能: 按照指定的间隔符分割文本,将分割的不同内容打包成数组。
用法: SPLIT(原始内容
,"间隔符")
- 间隔符:如果间隔符参数是空,则将分割每个字符。
返回结果中不包括间隔符。
示例:
将选择的地区拆分后组合成数组
SPLIT(地区字段
,"/")
地区字段:河南省/周口市/扶沟县
得到的结果:河南省,周口市,扶沟县
函数中组成的数组中都不带[],例如本例子中,在字段中显示为:A,B,C
JOIN
功能: 将数组中的所有元素按指定的间隔符拼接在一起。
用法: JOIN(数组
,"间隔符")
示例:
将成员字段(多选)选择的人员通过-拼接在一起。
JOIN(成员
,'-')
得到的结果:张三-李四-王五
四、逻辑函数
IF
功能: 设置条件表达式,然后根据判断结果TRUE或FALSE来返回不同的文本
用法: IF( 表达式
,表达式为真时返回的内容
,表达式为假时返回的内容
)
示例1: 根据分数内容得出不同的考评层级。
IF( 分数
>=60,'及格','不及格')
- 如果分数的值大于等于60,则返回
及格
- 如果分数的值< 60,则返回
不及格
示例2: 细化版:根据分数内容得出不同的考评层级。
IF( 分数
>=60,IF( 分数
>=80,'优秀','及格'),'不及格')
这样分三个层次了:
- >=80 ,优秀
- >=60 ,及格
- < 60 ,不及格
OR
功能: 判断一个或一组条件表达式是否为真。只要有一个表达式为真,则返回真(TRUE),所有条件都为假,返回假(FALSE)。一般不单独使用,常常IF函数使用。
用法: OR( 表达式1
,表达式2
,表达式3
...)
示例: 填空题中,填写两个答案的任何一个都正确,得1分,其他答案得0分。
配置
AND
功能: 判断一个或一组条件表达式的真伪,只要有一个表达式为假,则返回假(FALSE),所有条件都为真,返回真(TRUE)一般不单独使用,常常IF函数使用。
用法: AND( 表达式1
,表达式2
,表达式3
...)
示例: 考生的分数中,所有科目分数大于85,则直接录取
。
配置
NOT
功能: 返回参数逻辑值的反值。 如果条件表达式为真,则返回假(FALSE),如果表达式为假,则返回真(TRUE)。写入到文本字段是TRUE或FALSE。
用法: NOT( 表达式1
)
示例:
NOT(2>1) ,结果返回:
FALSE
。NOT(2>3) ,结果返回:
TRUE
。
ISBLANK
功能: 判断单元格内是否为空,如果为空,返回真,否则返回假,写入到文本字段是 TRUE或FALSE。
用法: ISBLANK( 字段
)
INCLUDE
功能: 判断一个文本中是否包含另一段文本,返回真或假,写入到文本字段是TRUE或FALSE。
用法: INCLUDE( 内容1
,内容2
)
示例:
- INCLUDE(
中华人民共和国
,'人民') ,结果返回:真。
FALSE
功能: 直接返回假,写入到文本中,内容是FALSE
TRUE
功能: 直接返回真,写入到文本中,内容是TRUE
五、高级函数
ENCODEURI
功能: 当存入文本时,进行URI编码操作,同时也可以对包含中文字符的网址进行编码
用法: ENCODEURI( 文本
)
示例1: 当存入的链接中含有空格时,可以用它来去除掉空格
配置
效果
这里的%20,就是空格的转码
示例2: 当存入的链接中含有中文时,可以用它来进行加码
效果
DECODEURI
功能: 将URI编码转换为文本,也可以对包含中文字符的网址进行解码
用法: DECODEURI( 文本2
)
配置
效果
ENCODEURICOMPONENT
功能: 将文本转换为URI编码,可以对包含中文字符的网址进行编码 该方法不会对字母、数字进行编码,也不会对ASCLL标点符号进行编码:如:- . ! ~ * ' ( )
其他字符(比如:; / ? : @ & = + $ , # 这些用于分割URI组件的标点符号),都是由一个或多个十六进制的转义序列替换的。
用法: ENCODEURICOMPONENT( 文本3
)
配置
效果
DECODEURICOMPONENT
功能: 将URI编码转换为文本,可以对包含中文字符的网址进行解码 可以对 encodeURIComponent() 函数编码的 URI 进行解码。
用法: DECODEURICOMPONENT( 文本4
)
配置
效果
DISTANCE
功能: 计算两地之间的距离,结果单位为千米,如果需要米,乘以1000即可.
用法: DISTANCE ( 定位1
,定位2
)
示例: 计算公司到目的地的距离。
配置
效果
固定位置怎么计算?
如果其中一个定位字段是固定值,在函数中的参数直接写成固定的坐标即可。例如打卡时需要计算打卡位置和考勤位置的距离,考勤位置就是固定值了。 写法如下:
DISTANCE(定位字段,"X,Y") ,只需替换X和Y坐标的值即可。
如下图:
如何查看某个位置的坐标:
定位字段设置中勾选[显示经纬度],然后在记录中选择目标位置,即可查看坐标。
GETPOSITION
功能: 获取定位字段中的位置的标题、详细地址、经纬度。
用法: GETPOSITION ( 定位1
,'需要的信息代码')
信息类型 - 代码
- 位置标题,代码是:'title'
- 详细地址,代码是:'address'
- 获取经度,代码是:'x'
- 获取纬度,代码是:'y'
- 获取x和y,代码是:'x,y'
代码的首位要有英文的单引号,且代码都是小写。 并非所有的地址都有标题。
示例: 获取定位的详细地址
配置
效果:
文档问题反馈
文档中是否有错别字、内容过期、难以理解等问题? 点此给我们反馈吧