跳到主要内容

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( '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'

代码的首位要有英文的单引号,且代码都是小写。 并非所有的地址都有标题。

示例: 获取定位的详细地址

  • 配置

  • 效果:

效果体验


文档问题反馈

文档中是否有错别字、内容过期、难以理解等问题? 点此给我们反馈吧