跳至主要内容

50種系統函數

目前支持五大類型的函數：數學函數、日期函數、文字函數、邏輯函數、高級函數，50多種類型，基本滿足日常的資料處理需求。

一、數學函數​

SUM​

功能：傳回多個數字的和

用法： SUM ( value1, value2, value3,... )

例： SUM(3,6,8)，結果：17

如果任意欄位為空，則計算為 0。

此函數給出與以下自訂公式相同的結果。

  • 求和公式

  • 定製配方

AVERAGE​

功能：傳回多個數字的平均值

用法：平均（value1, value2, value3,... ）

例： AVERAGE(3,6,8) ，結果：5.67

  • 當欄位為空時

    例如，求 3 個欄位的平均值。如果其中一個欄位為空，另外兩個欄位為 2 和 4，則平均值為 (2+4)/2=3。

  • 當欄位為0時

    例如，求 3 個欄位的平均值。如果其中一個欄位為0，另外兩個欄位為2和4，則平均值為(0+2+4)/3=2。

MIN​

功能：傳回多個數中最小的數

用法：MIN( value1, value2, value3,... )

例： MIN(3,6,8)，結果：3

如果任意欄位為空，則計算為 0。

MAX​

功能：傳回多個數中最大的數

用法：MAX( value1, value2, value3, ... )

例： MAX(3,6,8)，結果：8

如果任意欄位為空，則計算為 0。

PRODUCT​

功能：傳回多個數字的乘積

用法：產品( value1, value2, value3,... )

例：產品(3,6,8)，結果：144

如果某個欄位為空，則計算為 0。

它給出與以下自訂公式相同的結果。

  • PRODUCT公式

  • 自定義運算公式

COUNTA​

功能：統計多個欄位中不為null的欄位個數

用法：COUNTA( field1, field2, field3,... )

範例：COUNTA(Choice1, Choice2, Choice3) ，結果：2，表示兩個欄位不為空。

  • 設定

  • 效果如下圖

ABS​

功能：計算數字的絕對值

用途： ABS( Value )

範例：

  • ABS(-3.991)，結果：3.991 

INT​

功能：傳回始終小於或等於原數的最接近的整數

用法：INT(  Value  )

範例：

  • INT(3.991) ，結果：3；

  • INT(-3.991) ，結果：-4。

MOD​

功能：傳回除法的餘數

用法：MOD( Dividend,Divisor )

參數：兩個參數都是必要的，可以是欄位值或靜態參數。

如果除數為空，則計算為 0。如果除數為空或0，則不計算。

範例：

  • 設定

  • 效果如下圖

練習一下

ROUND、ROUNDUP、ROUNDDOWN​

1. ROUND​

功能：指定小數點保留位數，對數字進行四捨五入。

用法：ROUND( numeric field or constant, decimal places)

  • 設定範例：ROUND(3.14159,3)

  • 效果範例

結果：3.142。若第4位大於等於5，則輸入1位，若第4位小於5，則四捨五入。

2. ROUNDUP​

功能：將數字以絕對值遞增方向四捨五入指定位數

用法：ROUNDUP( numeric field or constant, decimal places)

範例：ROUNDUP(3.14159,3) ，結果為：3.142。

結果：3.142。 3位後的數字是否大於5，只要大於0直接接近1位即可。

3. ROUNDDOWN​

功能：將數字以絕對值減少的方向四捨五入指定位數

用法：ROUNDDOWN( numeric fields or constants, decimal places)

範例：ROUNDDOWN(3.14159,3)

結果：3.141。無論3位後的數字是否小於5，都直接四捨五入。

注意事項：

  1. ROUND、ROUNDDOWN、ROUNDUP 中結果保留的數字應與數字欄位中的數字相同。
  2. 如果函數中不寫入保留位的參數，則直接取整數。

POWER​

功能：計算填入數值的次方

用法:POWER( base number, exponent)

例： POWER(4,3)

結果：64。

LOG​

功能：**計算某個數值的對數。

用法： LOG( antilog, base number)

範例： LOG(12,3)

結果：2。

COUNTBLANK​

功能：統計參數中包含空值的個數

用法：COUNTBLANK( value1, value2, value3)

範例：COUNTBLANK( 12 , , )

結果：2。

COUNTCHAR​

功能：統計文字欄位中的字元數

用法：COUNTCHAR( text field)

  • **設定範例

  • **效果範例

RANDBETWEEN​

功能：傳回兩個值之間的隨機整數，也支援負數。

用法： RANDBETWEEN( min field, max field)

  • 設定範例

    隨機取得 1 到 10 之間的整數，或可能是 1 或 10。

  • 效果範例

NUMBER​

功能：將文字和其他類型的值轉換為數值

用法：NUMBER( text)

設定範例：

  • 首先將文字欄位轉換為數字欄位，然後將其添加到其他數字。

  • 新增文字欄位和數字欄位

    如果是文字和數字運算，則結果是兩個內容的串聯。

    例如，1 + 5 給出文本內容 15。

  • 效果範例

二、日期函數​

NETWORKDAY​

**功能：計算兩個日期之間的工作天數。全國法定假日和週六週日自動排除。如果特殊日期同時也是假日，可以設定去除。此函數傳回的結果是數值類型。數字欄位、金額欄位和文字欄位都可以使用此功能。

參數說明：

  • 開始日期和結束日期都是必需的。
  • 需要排除的指定假日不需要。如果不填寫y，則僅排除週六和週日。如果要排除指定假日，請填寫[]。

計算日期：

計算的天數晚於或等於開始日期且早於結束日期。例如，如果是(5th,7th)，則計算的天數是第5和第6，2個工作天。

用法： NETWORKDAY( start date, end date, [ date1, date2,...] )

範例 1：僅排除週六和週日

  • 設定

    NETWORKDAY('2022-1-1','2022-1-6')

    結果是 {3,4,5} ，3 個工作天。

範例2：排除除週六和週日之外的其他指定日期

  • 設定

    NETWORKDAY( '2022-1-1','2022-1-6', ['2022-1-3','2022-1-4'] )

    結果是1天。 1日和2日為週六和週日，除去3日和4日，僅5日為工作日。

MINTUE、HOUR​

功能：取得指定日期的小時數和分鐘數。

用法：

這與獲取月、日、年的方法相同。

  • 小時（date/time）

    取得 0 到 23 之間的數字。

  • 分鐘( date/time)

    取得 0 到 59 之間的數字。

  • 效果範例

WEEKDAY​

功能：取得指定日期的第幾天，1到7，其中星期一為1，星期日為7。

用法：WEEKDAY( date/time)

  • 效果範例

DAY、MONTH、YEAR​

功能：取得指定日期時間的年月日

用法：

三個函數的用法是一樣的。

  • DAY（date）

    取得 1 到 31 之間的數字。

  • MONTH（date）

    取得 1 到 12 之間的數字。

  • YEAR（date）

    取得年份的數字，例如2022-12-12，得到的數字是2022。

設定：

效果如下圖：

DATENOW​

功能：返回當前時間。此功能適用於日期欄位和文字欄位。

用法： DATENOW()。無參數。

  • 設定

  • 效果如下圖

DATEADD​

功能：在日期或時間上加上一段時間，得到新的日期或時間。

用法：DATEADD( date,'要加/減的時間',輸出格式 )

範圍：

  • 加減時間：文字類型，格式為“加減符號”+“數字”+“時間單位”

    • 時間單位：'Y'-年； 「M」——月份； 'd'-日； 'h'-小時； 'm' - 分鐘。

      例：'-1d'表示減去1天； '+3m'表示增加3分鐘； “+3M”表示增加 3 個月。

  • 輸出格式：數字類型。 1代表日期格式； 2表示日期+時間格式。

範例1：根據入職日期取得3個月後轉正的日期。

公式：DATEADD('2021-3-6','+3M',1)

結果是2021-6-6。

範例2：根據工單提交時間取得1小時內的提醒時間。

  • 公式：DATEADD('2021-3-6 9:00','+8h',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( start date, end date,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之間的天數。結果是 1。

  • 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。因此，您需要先刪除單位[天] 。

參考公式：NUMBER(FIND(DATEIF('2021-3-8','2021-3-14',2,'d'),"", "day"))+5

首先使用 FIND 函數刪除天數，然後使用 NUMBER 轉換為數字格式。

三、文字函數​

CONCAT​

功能：將多個內容拼接在一起，形成一個新的內容

用法 ： CONCAT( Content 1，Content 2....)

範例 ： CONCAT( R&D Department, -, Joey)

組合的內容可以是欄位或靜態參數。例如，要組合部門欄位和名稱欄位，中間需要一個連結字元 - 。

結果是'R&D Department-Joey'

REPLACE​

功能：將字串中的某些內容替換為其他內容。

用法： REPLACE( target content, 起始字元 , 字元數 , content to replace)

參數

  • 起始字元：數字。 1表示從第一個字元開始，第一個字元也被替換。

  • 字元數：數字。要替換的字元數。字母、漢字、數字或空格均視為 1 個字元。

  • 範例

  • 替換('19909090909',4,4,'****')

    將電話號碼的第4-7位，共4個字符，替換為 **** 。

    結果是199****0909。

範例

MID​

功能：從內容中提取多個字元。

用法： MID( target content, 起始位置 , 長度 )

參數

  • 目標內容：必填
  • 起始位置：數字，必需，即從哪個字元開始。
  • Length：數字，必需，即要提取多少個字元。

例： MID( A123456789,7,4)

表示從身分證號的第7個字符開始提取4個字符，結果為6789。

功能：取內容右側的幾個字符

用法： RIGHT( 目標內容, 長度 )

參數

目標內容：必填

長度：數字，即要提取多少個字符，如果不填，則只取最右邊的字符。

例： RIGHT('A123456789',4)

意思是從身分證號碼右側提取4個字符，結果為6789。

LEFT​

功能：從內容的左側開始提取多個字元。

用法： LEFT( 目標內容, 長度)

參數

目標內容：必填

長度：數字，即要提取多少個字符，如果不填，則只取最左邊的字符。

範例： LEFT('A123456789',2)

表示從身分證號碼左側提取2個字符，結果為A1。

TRIM​

功能：刪除文字開頭和結尾的空格。

用法： TRIM( text 5)

CLEAN​

功能：根據輸入的目標內容刪除文字中的所有空格。

用法：CLEAN( Text 5)

REPT​

功能：根據目標內容產生指定倍數的重複文字。

用法： REPT( target content, 2)

參數

目標內容：必填  倍數：數字，必填。

範例

REPT('  ',5) 表示重複顯示 5 次，結果為*。

LOWER​

功能：將內容中的英文字母全部替換為小寫字母。

用法： LOWER( Target content)

例： LOWER( aaaBBB)

結果是aaabbb。

UPPER​

功能：將內容中的英文字母全部替換為大寫字母。

用法： UPPER( Target content)

範例： UPPER( aaaBBB)

結果是AAABBB。

STRING​

功能：將內容轉換為文字格式。

用法： STRING( content1)

範例：

  • 字串（1+5）

    結果是6。首先計算 1 加 5，然後將其轉換為文字。

  • 字串(1)+字串(5)

    結果是15，先將數字轉換為文本，然後再組合內容。

FIND​

功能：從左到右提取文字內容。

用法： FIND( original content, "起始字元", "結束字元")

  • 起始字元：如果為空，則表示從第一個字元開始傳回。
  • 結束符：如果為空，則表示返回到最後一個字元。

傳回的結果不包含起始字元和結束字元。

範例：

在文字欄位中輸入計算公式（長度*寬度），然後將所得的長度和寬度寫入對應欄位並使用公式欄位得到結果。

  • 取得長度

    如果起始字元為空，則從第一個字元開始，以 * 結束。

    求('200  15',"","")

  • 取得寬度

    從*開始，向右走，直到結束。

    求('200  15',"","")

FINDA​

功能：從一段文字中取得多個段落，並將它們組合成一個陣列**。

用法： FINDA( original content, "間隔 1", "間隔 2")

  • 間隔 1：如果為空，則無結果
  • 間隔 2：如果為空，則無結果

傳回結果不包含間隔字元。

範例：

從文本中取出()裡面的內容，組合成文字。

FINDA("(A)(B)(C)","(",")")

結果是A、B、C。

函數中組成的陣列不包含 [ ] ，例如上例中，在欄位中顯示為 A,B,C。

SPLIT​

功能：依照指定的spacer分割文本，並將分割後的不同內容合併到一個陣列中。

用法： SPLIT( original content, "間隔符號")

  • Spacer：如果spacer參數為空，則每個字元將被分割。

傳回的結果不包含間隔符。

範例：

分割選定的區域並將它們組合成一個陣列。

分裂（region field，”/”）

地區領域：河南省/週口市/扶溝縣

結果：河南省周口市扶溝縣

函數中組成的陣列不包含 [ ] ，例如上例中，在欄位中顯示為 A,B,C。

JOIN​

功能：將陣列中的所有元素依指定的間隔符號組合起來。

用法： JOIN( array, "間隔符號")

範例：

將在成員（多項選擇）欄位中所選的人員按 - 組合起來。

加入（member，'-'）

結果是 Ada-Joey-Ross。

四、邏輯函數​

IF​

功能：設定條件表達式，根據結果TRUE或FALSE傳回不同的文字。

用法： IF( expression, content returned when expression is true, content returned when expression is false)

範例1：根據分數回傳不同的評估。

IF( score>=60,pass,fail)

  • 如果分數大於或等於 60，則傳回pass。
  • 如果分數小於 60，則傳回fail。

**範例2：根據更詳細的分數回傳不同的評估。

IF( Score>=60,IF( Score>=80,'優','及格'),'不及格')

將分為三個等級：

  •  =80，優秀

  •  =60，及格

  • < 60，不及格

OR​

功能：確定一個條件式或一組條件式是否為真。只要有表達式為true，就回傳TRUE；如果所有條件均不成立，則傳回 FALSE。此函數一般不單獨使用，常與IF函數一起使用。

用法： OR( expression1, expression2, expression3...)

例：在填空題中，如果填寫的兩個答案中的任何一個正確，則您得 1 分，另一個答案得 0 分。

  • 設定

AND​

功能：確定一個條件式或一組條件式是否為真。只要有一個表達式為假，就回傳FALSE；如果所有條件都為真，則傳回 TRUE。此函數一般不單獨使用，常與IF函數一起使用。

用法： AND( expression1, expression2, expression3...)

範例：如果考生所有科目的分數都高於 85 分，則directly admit。

  • 設定

NOT​

功能：傳回參數邏輯值的倒數。如果條件表達式為 true，則傳回 FALSE；如果表達式為 false，則傳回 TRUE。寫入文字欄位的內容是 TRUE 或 FALSE。

用法： NOT( expression1)

範例：

  • NOT(2>1) ，返回FALSE。
  • NOT(2>3) ，返回TRUE。

ISBLANK​

功能：判斷儲存格是否為空，如果是則回傳TRUE，否則回傳FALSE。寫入文字欄位的內容是 TRUE 或 FALSE。

用法： ISBLANK( field)

INCLUDE​

功能：判斷一個文本是否包含另一個文本，回傳TRUE或FALSE。寫入文字欄位的內容是 TRUE 或 FALSE。

用法：INCLUDE( content1, content2)

範例：

  • INCLUDE( People's Republic of China, People)，回傳 TRUE。

FALSE​

功能：直接返回FALSE，寫入文字欄位。

TRUE​

功能：直接返回TRUE，寫入文字欄位。

五、高級函數​

ENCODEURI​

功能：存入文字時對URI進行編碼，包含漢字的URL也進行編碼。

用法： ENCODEURI( text)

範例1：當連結中含有空格時，可以使用此函數去除空格。

  • 設定

  • 效果如下圖

這裡的%20是空格的轉碼。

例2：當連結包含中文時，可以使用此功能新增代碼。

  • 效果如下圖

DECODEURI​

功能：將URI的編碼轉換為文本，同時對包含漢字的URL進行解碼。

用法： DECODEURI( text2)

  • 設定

  • 效果如下圖

ENCODEURICOMPONENT​

功能：將文字轉換為URI編碼。此函數可以對包含中文字元的URL進行編碼。此方法不編碼字母或數字，也不編碼 ASCLL 標點符號，例如 - 。 ！ 〜*'（）。

其他字元（例如 ; / ? : @ & = + $ 、 # 和其他用於分割 URI 元件的標點符號）將替換為一個或多個十六進位轉義序列。

用法： ENCODEURICOMPONENT( text3)

  • 設定

  • 效果如下圖

DECODEURICOMPONENT​

功能：可以將URI的編碼轉換為文本，也可以對包含漢字的URL進行解碼。它可以解碼由encodeURIComponent()函數編碼的URI。

用法：解碼URICOMPONENT( text4)

  • 設定

  • 效果如下圖

DISTANCE​

功能：計算兩地之間的距離（以公里為單位）。

用法：距離 ( position1, position2)

範例：計算從公司到目的地的距離。

  • 設定

固定位置怎麼計算​

如果其中一個定位欄位為固定值，則函數中的參數可以直接寫為固定座標。例如，上班打卡時需要計算打卡位置與考勤位置之間的距離，那麼考勤位置就是固定值。

DISTANCE(定位欄位,“X,Y”)，只需替換X和Y座標的值即可。

如下圖所示：

如何查看某個位置的座標

設定定位欄位時勾選【顯示經緯度】，然後在資料中選擇目標位置即可查看座標。

GETPOSITION​

功能：取得定位欄位中位置的標題、詳細位址、經緯度。

用法：GETPOSITION( position1,'所需資訊代碼')

訊息類型 - 代碼

  • 地點標題，代號為：'title'
  • 詳細地址，代碼為：'address'
  • 取得經度，代碼為：'x'
  • 取得緯度，代碼為：'y'
  • 取得x和y，代碼為：'x,y'

程式碼的第一部分應該用單引號引起來，並且程式碼是小寫的。並非所有地址都有標題。

範例：取得該位置的詳細位址。

  • 設定

文檔問題反饋

文檔中是否有錯字、內容過期、難以理解等問題？ 點此給我們反饋吧