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'
代碼的首位要有英文的單引號,且代碼都是小寫。 並非所有的地址都有標題。
示例: 獲取定位的詳細地址
配置
效果:
文檔問題反饋
文檔中是否有錯彆字、內容過期、難以理解等問題? 點此給我們反饋吧