SPS常見公式樣本

可以在列表或庫的計算欄中使用下面的樣本。不包含欄引用的樣本可用於指定欄的預設值。

本文內容

--------------------------------------------------------------------------------

 條件公式

 日期和時間公式

 數學公式

 文本公式

--------------------------------------------------------------------------------

條件公式
可以使用下面的公式測試語句的條件並傳回值“Yes”或“No”，或者測試“OK”或“Not OK”等替代值，或者返回代表空值的空白或短劃線。

檢查一個數字是大小還是小於另一個數字

使用 IF 函數進行此比較。

Column1 Column2 公式 說明（可能的結果）
15000 9000 =[Column1]>[Column2] Column1 大於 Column2 嗎？(Yes)
15000 9000 =IF([Column1]<=[Column2], "OK", "Not OK") Column1 小於或等於 Column2 嗎？(Not OK)

對欄內容進行比較之後返回一個邏輯值

對於是邏輯值（Yes 或 No）的結果，請使用 AND、OR 和 NOT 函數。

Column1 Column2 Column3 公式 說明（可能的結果）
15 9 8 =AND([Column1]>[Column2], [Column1]<[Column3]) 15 大於 9 且小於 8 嗎？(No)
15 9 8 =OR([Column1]>[Column2], [Column1]<[Column3]) 15 大於 9 或小於 8 嗎？(Yes)
15 9 8 =NOT([Column1]+[Column2]=24) 15 加 9 不等於 24 嗎？(No)

對於是另一個計算的結果，或者是 Yes 或 No 以外的任何其他值的結果，請使用 IF、AND 和 OR 函數。

Column1 Column2 Column3 公式 說明（可能的結果）
15 9 8 =IF([Column1]=15, "OK", "Not OK") 如果 Column1 中的值等於 15，則返回“OK”。(OK)
15 9 8 =IF(AND([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK") 如果 15 大於 9 且小於 8，則返回“OK”。(Not OK)
15 9 8 =IF(OR([Column1]>[Column2], [Column1]<[Column3]), "OK", "Not OK") 如果 15 大於 9 或小於 8，則返回“OK”。(OK)

將零顯示為空白或短劃線

要顯示零，請執行一次簡單計算。要顯示空白或短劃線，請使用 IF 函數。

Column1 Column2 公式 說明（可能的結果）
10 10 =[Column1]-[Column2] 從第一個數字中減去第二個數字 (0)
15 9 =IF([Column1]-[Column2],"-",[Column1]-[Column2]) 值為零時返回一條短劃線 (-)

隱藏欄中的錯誤值

要顯示短劃線、#N/A 或 NA 而不是錯誤值，請使用 ISERROR 函數。

Column1 Column2  公式 說明（可能的結果）
10 0 =[Column1]/[Column2] 產生錯誤 (#DIV/0)
10 0 =IF(ISERROR([Column1]/[Column2]),"NA",[Column1]/[Column2]) 值為錯誤時返回 NA
10 0 =IF(ISERROR([Column1]/[Column2]),"-",[Column1]/[Column2]) 值為錯誤時返回一條短劃線

 返回頁首

日期和時間公式
可以使用下面的公式執行基於日期和時間的計算，例如向某個日期添加若干天、若干月或若干年，計算兩個日期之間的差值，以及將時間轉換為小數值。

添加日期

要向某個日期添加若干天，請使用加法運算子 (+)。

 注釋    對日期進行操作時，計算欄的傳回型別必須設定為“日期和時間”。

Column1 Column2 公式 說明（結果）
6/9/2007 3 =[Column1]+[Column2] 向 6/9/2007 添加 3 天 (6/12/2007)
12/10/2008 54 =[Column1]+[Column2] 向 12/10/2008 添加 54 天(2/2/2009)

要向某個日期添加若干月，請使用 DATE、YEAR、MONTH 和 DAY 函數。

Column1 Column2 公式 說明（結果）
6/9/2007 3 =DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) 向 6/9/2007 添加 3 個月 (9/9/2007)
12/10/2008 25 =DATE(YEAR([Column1]),MONTH([Column1])+[Column2],DAY([Column1])) 向 12/10/2008 添加 25 個月 (1/10/2011)

要向某個日期添加若干年，請使用 DATE、YEAR、MONTH 和 DAY 函數。

Column1 Column2 公式 說明（結果）
6/9/2007 3 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) 向 6/9/2007 添加 3 年 (6/9/2010)
12/10/2008 25 =DATE(YEAR([Column1])+[Column2],MONTH([Column1]),DAY([Column1])) 向 12/10/2008 添加 25 年 (12/10/2033)

要向某個日期添加天數、月數和年數的組合，請使用 DATE、YEAR、MONTH 和 DAY 函數。

Column1 公式 說明（結果）
6/9/2007 =DATE(YEAR([Column1])+3,MONTH([Column1])+1,DAY([Column1])+5) 向 6/9/2007 添加 3 年 1 個月零 5 天 (7/14/2010)
12/10/2008 =DATE(YEAR([Column1])+1,MONTH([Column1])+7,DAY([Column1])+5) 向 12/10/2008 添加 1 年 7 個月零 5 天 (7/15/2010)

計算兩個日期之間的差值

請使用 DATEDIF 函數執行此計算。

Column1 Column2 公式 說明（結果）
01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"d") 返回兩個日期之間的天數 (1626)
01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"ym") 返回兩個日期之間的月數，忽略年 (5)
01-Jan-1995 15-Jun-1999 =DATEDIF([Column1], [Column2],"yd") 返回兩個日期之間的天數，忽略年 (165)

計算兩個時間之間的差值

要使用標準時間格式（時:分:秒）顯示結果，請使用減法運算子 (-) 和 TEXT 函數。要使此方法正常工作，小時不得超過 24，且分和秒不得超過 60。

Column1 Column2 公式 說明（結果）
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h") 兩個時間之間的小時數 (4)
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h:mm") 兩個時間之間的小時數和分鐘數 (4:55) 
06/09/2007 10:35 AM 06/09/2007 3:30 PM =TEXT([Column2]-[Column1],"h:mm:ss") 兩個時間之間的小時數、分鐘數和秒數 (4:55:00) 

要用基於一個時間單位的總計值顯示結果，請使用 INT 函數或 HOUR、MINUTE 或 SECOND 函數。

Column1 Column2 公式 說明（結果）
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*24) 兩個時間之間的總小時數 (28)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*1440) 兩個時間之間的總分鐘數 (1735)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =INT(([Column2]-[Column1])*86400) 兩個時間之間的總秒數 (104100)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =HOUR([Column2]-[Column1]) 當差值不超過 24 時，兩個時間之間的小時數 (4)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =MINUTE([Column2]-[Column1]) 當差值不超過 60 時，兩個時間之間的分鐘數 (55)
06/09/2007 10:35 AM 06/10/2007 3:30 PM =SECOND([Column2]-[Column1]) 當差值不超過 60 時，兩個時間之間的秒數 (0)

轉換時間

要將小時從標準時間格式轉換為小數，請使用 INT 函數。

Column1 公式 說明（結果）
10:35 AM =([Column1]-INT([Column1]))*24 自 12:00 AM 以後的小時數 (10.583333)
12:15 PM =([Column1]-INT([Column1]))*24 自 12:00 AM 以後的小時數 (12.25)

要將小時從小數轉換為標準時間格式（時:分:秒），請使用除法運算子和 TEXT 函數。

Column1 公式 說明（結果）
23:58 =TEXT(Column1/24, "hh:mm:ss") 自 12:00 AM 以後的時、分和秒 (00:59:55)
2:06 =TEXT(Column1/24, "h:mm") 自 12:00 AM 以後的時和分 (0:05)

插入儒略曆日期

儒略曆日期指的是一種日期格式，是當前年份和從當年年初算起的天數的組合。例如，2007 年 1 月 1 日表示為 2007001，而 2007 年 12 月 31 日則表示為 2007365。此格式並不基於儒略曆。

要將日期轉換為儒略曆日期，請使用 TEXT 和 DATEVALUE 函數。

Column1 公式 說明（結果）
6/23/2007 =TEXT([Column1],"yy")&TEXT(([Column1]-DATEVALUE("1/1/"& TEXT([Column1],"yy"))+1),"000") 採用儒略曆格式的日期，年份用兩位元字表示 (07174)
6/23/2007 =TEXT([Column1],"yyyy")&TEXT(([Column1]-DATEVALUE("1/1/"&TEXT([Column1],"yy"))+1),"000") 採用儒略曆格式的日期，年份用四位元字表示 (2007174)

要將日期轉換為在天文學中使用的儒略曆日期，請使用常量 2415018.50。如果使用 1900 日期系統，則此公式只對 1901 年 3 月 1 日之後的日期起作用。

Column1 公式 說明（結果）
6/23/2007 =[Column1]+2415018.50 在天文學中使用的儒略曆格式的日期 (2454274.50)

將日期顯示為一周中的某一天

要將日期轉換為表示一周中某一天的文本，請使用 TEXT 和 WEEKDAY 函數。

Column1 公式 說明（可能的結果）
19-Feb-2007 =TEXT(WEEKDAY([Column1]), "dddd") 計算該日期在一周中所對應的天數，並返回這一天的全名 (Monday)
3-Jan-2008 =TEXT(WEEKDAY([Column1]), "ddd") 計算該日期在一周中所對應的天數，並返回這一天的縮寫名稱 (Thu)

 返回頁首

數學公式
可以使用下面的公式執行各種數學計算，如數位加、減、乘、除，計算一組數位平均值或中值，對數字進行四捨五入以及對數值進行計數。

數字相加

要將一行中兩欄或更多個欄中的數字相加，請使用加法運算子 (+) 或 SUM 函數。

Column1 Column2 Column3 公式 說明（結果）
6 5 4 =[Column1]+[Column2]+[Column3] 將前三個欄中的值相加 (15)
6 5 4 =SUM([Column1],[Column2],[Column3]) 將前三個欄中的值相加 (15)
6 5 4 =SUM(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3]) 如果 Column1 大於 Column2，則將它們的差值與 Column3 相加。否則將 10 與 Column3 相加 (5)

數字相減

要將一行中兩欄或更多個欄中的數字相減，請使用減法運算子 (-) 或帶負數的 SUM 函數。

Column1 Column2  Column3 公式 說明（結果）
15000 9000 -8000 =[Column1]-[Column2] 從 15000 中減去 9000 (6000)
15000 9000 -8000 =SUM([Column1], [Column2], [Column3]) 將前三個欄中的數字相加，包括負值 (16000)

將兩個數字之間的差值計算為百分比

請使用減法運算子 (-)、除法運算子 (/) 和 ABS 函數。

Column1 Column2 公式 說明（結果）
2342 2500 =([Column2]-[Column1])/ABS([Column1]) 百分比變動（6.75% 或 0.06746）

數字相乘

要將一行中兩欄或更多個欄中的數字相乘，請使用乘法運算子 (*) 或 PRODUCT 函數。

Column1 Column2  公式 說明（結果）
5 2 =[Column1]*[Column2] 將前兩個欄中的數字相乘 (10)
5 2 =PRODUCT([Column1], [Column2]) 將前兩個欄中的數字相乘 (10)
5 2 =PRODUCT([Column1],[Column2],2) 將前兩個欄中的數字及數字 2 相乘 (20)

數字相除

要將一行中兩欄或更多個欄中的數字相除，請使用除法運算子 (/)。

Column1 Column2  公式 說明（結果）
15000 12 =[Column1]/[Column2] 用 15000 除以 12 (1250)
15000 12 =([Column1]+10000)/[Column2] 將 15000 與 10000 相加，然後用和除以 12 (2083)

計算一組數位平均值

平均值也稱為平均數。要計算一行中兩欄或更多個欄中的數位平均值，請使用 AVERAGE 函數。

Column1 Column2 Column3 公式 說明（結果）
6 5 4 =AVERAGE([Column1], [Column2],[Column3]) 前三個欄中的數位平均值 (5)
6 5 4 =AVERAGE(IF([Column1]>[Column2], [Column1]-[Column2], 10), [Column3]) 如果 Column1 大於 Column2，則計算其差值與 Column3 的平均值。否則計算數值 10 與 Column3 的平均值 (2.5)

計算一組數位中值

中值是一系列有序數位中間值。請使用 MEDIAN Function Compute一組數位中值。

A B  C D E F 公式 說明（結果）
10  7 9 27 0 4 =MEDIAN(A, B, C, D, E, F) 前六個欄中的數位中值 (8)

計算某個範圍中的最小數或最大數

要計算一行中兩欄或更多個欄中的數字中的最小數或最大數，請使用 MIN 和 MAX 函數。

Column1 Column2  Column3 公式 說明（結果）
10  7 9 =MIN([Column1], [Column2], [Column3]) 最小數 (7)
10 7 9 =MAX([Column1], [Column2], [Column3]) 最大數 (10)

對數值進行計數

要對數值進行計數，請使用 COUNT 函數。

Column1 Column2  Column3 公式 說明（結果）
蘋果樹  12/12/2007 =COUNT([Column1], [Column2], [Column3]) 計算包含數值的欄數。排除日期和時間、文本以及空值 (0)
$12 #DIV/0! 1.01 =COUNT([Column1], [Column2], [Column3]) 計算包含數值的欄數，但排除錯誤和邏輯值 (2)

將一個數字增加或減少一個百分點

請使用百分數 (%) 運算子執行此計算。

Column1 Column2  公式 說明（結果）
23 3% =[Column1]*(1+5%) 將 Column1 中的數字增加 5% (24.15)
23 3% =[Column1]*(1+[Column2]) 將 Column1 中的數字增加 Column2 中的百分數值：3% (23.69)
23 3% =[Column1]*(1-[Column2]) 將 Column1 中的數字減少 Column2 中的百分數值：3% (22.31)

對一個數字進行乘冪運算

使用乘冪運算子 (^) 或 POWER 函數執行此計算。

Column1 Column2  公式 說明（結果）
5 2 =[Column1]^[Column2] 計算 5 的平方 (25)
5 3 =POWER([Column1], [Column2]) 計算 5 的立方 (125) 

對數字進行四捨五入

要對數字向上舍入，請使用 ROUNDUP、ODD 或 EVEN 函數。

Column1 公式 說明（結果）
20.3 =ROUNDUP([Column1],0) 將 20.3 向上舍入為最接近的整數 (21)
-5.9 =ROUNDUP([Column1],0) 將 -5.9 向上舍入為最接近的整數 (-5)
12.5493 =ROUNDUP([Column1],2) 將 12.5493 向上舍入為最接近的百分位，即兩個小數位元 (12.55)
20.3 =EVEN([Column1]) 將 20.3 向上舍入為最接近的偶數 (22)
20.3 =ODD([Column1]) 將 20.3 向上舍入為最接近的奇數 (21)

要對數字向下舍入，請使用 ROUNDDOWN 函數。

Column1 公式 說明（結果）
20.3 =ROUNDDOWN([Column1],0) 將 20.3 向下舍入為最接近的整數 (20)
-5.9 =ROUNDDOWN([Column1],0) 將 -5.9 向下舍入為最接近的整數 (-6)
12.5493 =ROUNDDOWN([Column1],2) 將 12.5493 向下舍入為最接近的百分位，即兩個小數位元 (12.54)

要將數字四捨五入為最接近的數字或分數，請使用 ROUND 函數。

Column1 公式 說明（結果）
20.3 =ROUND([Column1],0) 將 20.3 向下舍入，因為其分數部分小於 .5 (20)
5.9 =ROUND([Column1],0) 將 5.9 向上舍入，因為其分數部分大於 .5 (6)
-5.9 =ROUND([Column1],0) 將 -5.9 向下舍入，因為其分數部分小於 -.5 (-6)
1.25 =ROUND([Column1], 1) 將數字四捨五入到最接近的十分位（一個小數位元）。因為要進行四捨五入的部分為 0.05 或更大，該數字被向上舍入（結果：1.3）
30.452 =ROUND([Column1], 2) 將數字四捨五入到最接近的百分位（兩個小數位元）。因為要四捨五入的部分 (0.002) 小於 0.005，該數字被向下舍入（結果：30.45）

要將數字四捨五入到 0 以上的有效位元，請使用 ROUND、ROUNDUP、ROUNDDOWN、INT 和 LEN 函數。

Column1 公式 說明（結果）
5492820 =ROUND([Column1],3-LEN(INT([Column1]))) 將數字四捨五入到 3 個有效位元 (5490000)
22230 =ROUNDDOWN([Column1],3-LEN(INT([Column1]))) 將數字向下舍入到 3 個有效位元 (22200)
5492820 =ROUNDUP([Column1], 5-LEN(INT([Column1]))) 將數字向上舍入到 5 個有效位元 (5492900)

 返回頁首

文本公式
可以使用下面的公式處理文本，例如組合或串連多個欄中的值，比較一些欄中的內容，刪除字元或空格以及重複字元。

更改文本的大小寫

要更改文本的大小寫，請使用 UPPER、LOWER 或 PROPER 函數。

Column1 公式 說明（結果）
nina Vietzen =UPPER([Column1]) 將文本更改為大寫形式 (NINA VIETZEN)
nina Vietzen =LOWER([Column1]) 將文本更改為小寫形式 (nina vietzen)
nina Vietzen =PROPER([Column1]) 將文本更改為詞首大寫形式 (Nina Vietzen)

將名字和姓氏進行組合

要將名字和姓氏進行組合，請使用“與”運算子 (&) 或 CONCATENATE 函數。

Column1 Column2 公式 說明（結果）
Carlos Carvallo =[Column1]&[Column2] 對兩個字串進行組合 (CarlosCarvallo)
Carlos Carvallo =[Column1]&" "&[Column2] 對兩個字串進行組合，並用空格分隔 (Carlos Carvallo)
Carlos Carvallo =[Column2]&", "&[Column1] 對兩個字串進行組合，並用逗號和空格分隔 (Carvallo, Carlos)
Carlos  Carvallo =CONCATENATE([Column2], ",", [Column1]) 對兩個字串進行組合，並用逗號分隔 (Carvallo,Carlos)

將不同列中的文本和數字進行組合

要將文本和數字進行組合，請使用 CONCATENATE 函數、“與”運算子 (&) 或 TEXT 函數和“與”運算子。

Column1 Column2 公式 說明（結果）
Yang 28 =[Column1]&" sold "&[Column2]&" units." 將上面的內容組合成一個短語 (Yang sold 28 units.)
Dubois 40% =[Column1]&" sold "&TEXT([Column2],"0%")&" of the total sales." 將上面的內容組合成一個短語 (Dubois sold 40% of the total sales.)
 注釋    TEXT 函數追加 Column2 的帶格式的值而不是基礎值 .4。
 
Yang 28 =CONCATENATE([Column1]," sold ",[Column2]," units.") 將上面的內容組合成一個短語 (Yang sold 28 units.)

將文本與日期或時間進行組合

要將文本與日期或時間進行組合，請使用 TEXT 函數和“與”運算子 (&)。

Column1 Column2 公式 說明（結果）
Billing Date 5-Jun-2007 ="Statement date: "&TEXT([Column2], "d-mmm-yyyy") 將文本與日期進行組合 (Statement date: 5-Jun-2007)
Billing Date 5-Jun-2007 =[Column1]&" "&TEXT([Column2], "mmm-dd-yyyy") 將不同欄中的文本與日期組合為一欄 (Billing Date Jun-05-2007)

比較欄中的內容

要將一欄同另一欄或值列表進行比較，請使用 EXACT 和 OR 函數。

Column1 Column2 公式 說明（可能的結果）
BD122 BD123 =EXACT([Column1],[Column2])  對前兩欄中的內容進行比較 (No) 
BD122 BD123 =EXACT([Column1], "BD122") 將 Column1 中的內容與字串“BD122”進行比較 (Yes) 

檢查欄值或欄值的一部分是否與特定文本匹配

要檢查欄值或欄值的一部分是否與特定文本匹配，請使用 IF、FIND、SEARCH 和 ISNUMBER 函數。

Column1 公式 說明（可能的結果）
Vietzen =IF([Column1]="Vietzen", "OK", "Not OK") 檢查確定 Column1 是否為 Vietzen (OK)
Vietzen =IF(ISNUMBER(FIND("v",[Column1])), "OK", "Not OK") 檢查確定 Column1 中是否包含字母 v (OK)
BD123 =ISNUMBER(FIND("BD",[Column1])) 檢查確定 Column1 中是否包含 BD (Yes)

計算非空欄的數目

要計算非空欄的數目，請使用 COUNTA 函數。

Column1 Column2 Column3 公式 說明（結果）
銷售額 19  =COUNTA([Column1], [Column2])  計算非空欄的數目 (2) 
銷售額 19  =COUNTA([Column1], [Column2], [Column3]) 計算非空欄的數目 (2) 

刪除文本中的字元

要刪除文本中的字元，請使用 LEN、LEFT 和 RIGHT 函數。

Column1 公式 說明（結果）
Vitamin A =LEFT([Column1],LEN([Column1])-2) 從左邊開始返回 7 (9-2) 個字元 (Vitamin) 
Vitamin B1 =RIGHT([Column1], LEN([Column1])-8) 從右邊開始返回 2 (10-8) 個字元 (B1)

刪除欄開頭和結尾處的空格

要刪除欄中的空格，請使用 TRIM 函數。

Column1 公式 說明（結果）
  Hello there! =TRIM([Column1]) 刪除開頭和結尾處的空格 (Hello there!) 

重複欄中的字元

要重複欄中的字元，請使用 REPT 函數。

公式 說明（結果）
=REPT(".",3) 將句號重複 3 次 (...)
=REPT("-",10) 將短劃線重複 10 次 (----------)