共用公式範例

重要:  本文係由機器翻譯而成,請參閱免責聲明。本文的英文版本請見這裡,以供參考。

您可以在清單或文件庫的計算結果欄中使用下列範例。不包含欄參照的範例可以用來指定欄的預設值。

本文內容

條件公式

日期及時間的公式

數學公式

文字的公式

條件化公式

下列公式可以用來驗證陳述式的條件並傳回 Yes 或 No 值、驗證 OK 或 Not OK 等替代值,或是傳回空白或虛線以代表 Null 值。

核取 [數字是否大於或小於另一個數字

使用 IF 函數來執行這項比較。

欄 1

欄 2

公式

說明 (可能結果)

15000

9000

=[欄 1]>[欄 2]

欄 1 是否大於欄 2?(結果為 Yes)

15000

9000

=IF([欄 1]<=[欄 2], "OK", "Not OK")

欄 1 是否小於或等於欄 2?(結果為 Not OK)

後比較欄位的內容,傳回邏輯值

針對為邏輯值 (Yes 或 No) 的結果,使用 AND、OR 及 NOT 函數。

欄 1

欄 2

欄 3

公式

說明 (可能結果)

15

9

8

=AND([欄 1]>[欄 2], [欄 1]<[欄 3])

15 是否大於 9 且小於 8?(結果為 No)

15

9

8

=OR([欄 1]>[欄 2], [欄 1]<[欄 3])

15 是否大於 9 或小於 8?(結果為 Yes)

15

9

8

=NOT([欄 1]+[欄 2]=24)

15 加 9 是否不等於 24?(結果為 No)

針對其他計算的結果,或除了 Yes 或 No 之外的任何其他值,使用 IF、AND 及 OR 函數。

欄 1

欄 2

欄 3

公式

說明 (可能結果)

15

9

8

=IF([欄 1]=15, "OK", "Not OK")

如果欄 1 中的值等於 15,則會傳回 "OK" (結果為 OK)

15

9

8

=IF(AND([欄 1]>[欄 2], [欄 1]<[欄 3]), "OK", "Not OK")

如果 15 大於 9 且小於 8,則會傳回 "OK" (結果為 Not OK)

15

9

8

=IF(OR([欄 1]>[欄 2], [欄 1]<[欄 3]), "OK", "Not OK")

如果 15 大於 9 或小於 8,則會傳回 "OK" (結果為 OK)

將零顯示為空白或虛線

若要顯示零,請執行簡單的計算;若要顯示空白或虛線,則請使用 IF 函數。

C olumn1

欄 2

公式

說明 (可能結果)

10

10

=[欄 1]-[欄 2]

將第一個數字減去第二個數字 (0)

15

9

=IF([欄 1]-[欄 2],"-",[欄 1]-[欄 2])

當值為零時,會傳回虛線 (-)

隱藏欄中的錯誤值

若要顯示虛線、#N/A 或 NA 來取代錯誤值,請使用 ISERROR 函數。

欄 1

欄 2

公式

說明 (可能結果)

10

0

=[欄 1]/[欄 2]

會產生錯誤 (#DIV/0)

10

0

=IF(ISERROR([欄 1]/[欄 2]),"NA",[欄 1]/[欄 2])

當值為錯誤時,會傳回 NA

10

0

=IF(ISERROR([欄 1]/[欄 2]),"-",[欄 1]/[欄 2])

當值為錯誤時,會傳回虛線

頁面頂端

日期及時間公式

下列公式可以用來執行以日期及時間為主的計算,例如從某個日期加上數天、數個月或數年;計算兩個日期之間的差距;以及將時間轉換為十進位值。

新增日期

若要從某個日期加上數天,請使用加法運算子 (+)。

附註: 在處理日期時,必須將計算結果欄的傳回類型設定為 [日期及時間]。

欄 1

欄 2

公式

描述 (結果)

6/9/2007

3

=[欄 1]+[欄 2]

從 6/9/2007 加上 3 天 (6/12/2007)

12/10/2008

54

=[欄 1]+[欄 2]

從 12/10/2008 加上 54 天 (2/2/2009)

若要從某個日期加上數個月,請使用 DATE、YEAR、MONTH 及 DAY 函數。

欄 1

欄 2

公式

描述 (結果)

6/9/2007

3

=DATE(YEAR([欄 1]),MONTH([欄 1])+[欄 2],DAY([欄 1]))

從 6/9/2007 加上 3 個月 (9/9/2007)

12/10/2008

25

=DATE(YEAR([欄 1]),MONTH([欄 1])+[欄 2],DAY([欄 1]))

從 12/10/2008 加上 25 個月 (1/10/2011)

若要從某個日期加上數年,請使用 DATE、YEAR、MONTH 及 DAY 函數。

欄 1

欄 2

公式

描述 (結果)

6/9/2007

3

=DATE(YEAR([欄 1])+[欄 2],MONTH([欄 1]),DAY([欄 1]))

從 6/9/2007 加上 3 年 (6/9/2010)

12/10/2008

25

=DATE(YEAR([欄 1])+[欄 2],MONTH([欄 1]),DAY([欄 1]))

從 12/10/2008 加上 25 年 (12/10/2033)

若要從某個日期加上日、月及年的組合,請使用 DATE、YEAR、MONTH 及 DAY 函數。

欄 1

公式

描述 (結果)

6/9/2007

=DATE(YEAR([欄 1])+3,MONTH([欄 1])+1,DAY([欄 1])+5)

從 6/9/2007 加上 3 年、1 個月及 5 天 (7/14/2010)

12/10/2008

=DATE(YEAR([欄 1])+1,MONTH([欄 1])+7,DAY([欄 1])+5)

從 12/10/2008 加上 1 年、7 個月及 5 天 (7/15/2010)

計算兩個日期之間的差異

使用 DATEDIF 函數來執行這項計算。

欄 1

欄 2

公式

描述 (結果)

01/01/1995

06/15/1999

=DATEDIF([欄 1], [欄 2],"d")

會傳回這兩個日期之間的天數 (1626)

01/01/1995

06/15/1999

=DATEDIF([欄 1], [欄 2],"ym")

會傳回這兩個日期之間的月數 (忽略年的部分) (5)

01/01/1995

06/15/1999

=DATEDIF([欄 1], [欄 2],"yd")

會傳回這兩個日期之間的天數 (忽略年的部分) (165)

計算兩個時間之間的差異

若要以標準時間格式 (小時:分鐘:秒) 來顯示結果,請使用減法運算子 (-) 及 TEXT 函數。若要使這個方法能正常運作,小時不可超過 24,且分鐘及秒不可超過 60。

欄 1

欄 2

公式

描述 (結果)

06/09/2007 10:35 AM

06/09/2007 3:30 PM

=TEXT([欄 2]-[欄 1],"h")

這兩個時間之間的小時數 (4)

06/09/2007 10:35 AM

06/09/2007 3:30 PM

=TEXT([欄 2]-[欄 1],"h:mm")

這兩個時間之間的小時數及分鐘數 (4:55)

06/09/2007 10:35 AM

06/09/2007 3:30 PM

=TEXT([欄 2]-[欄 1],"h:mm:ss")

這兩個時間之間的小時數、分鐘數及秒數 (04:55:00)

若要根據某一時間單位來顯示總結果,請使用 INT 函數或是 HOUR、MINUTE 或 SECOND 函數。

欄 1

欄 2

公式

描述 (結果)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=INT(([欄 2]-[欄 1])*24)

這兩個時間之間的總小時數 (28)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=INT(([欄 2]-[欄 1])*1440)

這兩個時間之間的總分鐘數 (1735)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=INT(([欄 2]-[欄 1])*86400)

這兩個時間之間的總秒數 (104100)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=HOUR([欄 2]-[欄 1])

當差距未超過 24 時,這兩個時間之間的小時數 (4)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=MINUTE([欄 2]-[欄 1])

當差距未超過 60 時,這兩個時間之間的分鐘數 (55)

06/09/2007 10:35 AM

06/10/2007 3:30 PM

=SECOND([欄 2]-[欄 1])

當差距未超過 60 時,這兩個時間之間的秒數 (0)

轉換時間

若要將小時數從標準時間格式轉換為十進位數字,請使用 INT 函數。

欄 1

公式

描述 (結果)

10:35 AM

=([欄 1]-INT([欄 1]))*24

從上午 12:00 開始計算的小時數 (10.583333)

12:15 PM

=([欄 1]-INT([欄 1]))*24

從上午 12:00 開始計算的小時數 (12.25)

若要將小時數從十進位數字轉換為標準時間格式 (小時:分鐘:秒),請使用除法運算子及 TEXT 函數。

欄 1

公式

描述 (結果)

23:58

=TEXT(欄 1/24, "hh:mm:ss")

從上午 12:00 開始計算的小時數、分鐘數及秒數 (00:59:55)

2:06

=TEXT(欄 1/24, "h:mm")

從上午 12:00 開始計算的小時數及分鐘數 (0:05)

插入凱撒日期

「凱撒日期」一詞是指由目前年份以及自年初開始計算的天數所組成的日期格式。例如,2007 年 1 月 1 日是以 2007001 來表示,而 2007 年 12 月 31 日則是以 2007365 來表示。這種格式並非根據凱撒曆。

若要將日期轉換為凱撒日期,請使用 TEXT 及 DATEVALUE 函數。

欄 1

公式

描述 (結果)

6/23/2007

=TEXT([欄 1],"yy")&TEXT(([欄 1]-DATEVALUE("1/1/"& TEXT([欄 1],"yy"))+1),"000")

以凱撒日期格式表示且年份為兩位數的日期 (07174)

6/23/2007

=TEXT([欄 1],"yyyy")&TEXT(([欄 1]-DATEVALUE("1/1/"&TEXT([欄 1],"yy"))+1),"000")

以凱撒日期格式表示且年份為四位數的日期 (2007174)

若要將日期轉換為天文學中所使用的凱撒日期,請使用常數 2415018.50。這個公式只對 3/1/1901 以後的日期,以及當您使用 1900 日期系統時才有作用。

欄 1

公式

描述 (結果)

6/23/2007

=[欄 1]+2415018.50

以天文學中所使用的凱撒日期格式表示的日期 (2454274.50)

將日期顯示為星期幾

若要將日期轉換為星期幾的文字,請使用 TEXT 及 WEEKDAY 函數。

欄 1

公式

說明 (可能結果)

02/19/2007

=TEXT(WEEKDAY([欄 1]), "dddd")

會計算該日期是星期幾並傳回星期幾的全名 (Monday)

01/03/2008

=TEXT(WEEKDAY([欄 1]), "ddd")

會計算該日期是星期幾並傳回星期幾的縮寫 (Thu)

頁面頂端

數學公式

下列公式可以用來執行各種不同的數學計算,例如將數字相加、相減、相乘及相除;計算數字的平均值或中間數;將數字四捨五入;以及計算數值的數目。

將數字相加

若要將某一列中兩欄以上的數字相加,請使用加法運算子 (+) 或 SUM 函數。

欄 1

欄 2

欄 3

公式

描述 (結果)

6

5

4

=[欄 1]+[欄 2]+[欄 3]

將最前面三欄中的值相加 (15)

6

5

4

=SUM([欄 1],[欄 2],[欄 3])

將最前面三欄中的值相加 (15)

6

5

4

=SUM(IF([欄 1]>[欄 2], [欄 1]-[欄 2], 10), [欄 3])

如果欄 1 大於欄 2,則將其差值與欄 3 相加,否則將 10 與欄 3 相加 (5)

數字相減

若要將某一列中兩欄以上的數字相減,請使用減法運算子 (-),或者若有負數則使用 SUM 函數。

欄 1

欄 2

欄 3

公式

描述 (結果)

15000

9000

-8000

=[欄 1]-[欄 2]

將 15000 減去 9000 (6000)

15000

9000

-8000

=SUM([欄 1], [欄 2], [欄 3])

將最前面三欄中的數字相加,包括負值 (16000)

計算百分比的兩個數字之間的差異

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

欄 1

欄 2

公式

描述 (結果)

2342

2500

=([欄 2]-[欄 1])/ABS([欄 1])

百分比變更 (6.75% 或 0.06746)

將數字相乘

若要將某一列中兩欄以上的數字相乘,請使用乘法運算子 (*) 或 PRODUCT 函數。

欄 1

欄 2

公式

描述 (結果)

5

2

=[欄 1]*[欄 2]

將最前面兩欄中的數字相乘 (10)

5

2

=PRODUCT([欄 1], [欄 2])

將最前面兩欄中的數字相乘 (10)

5

2

=PRODUCT([欄 1],[欄 2],2)

將最前面兩欄中的數字與 2 相乘 (20)

將數字相除

若要將某一列中兩欄以上的數字相除,請使用除法運算子 (/)。

欄 1

欄 2

公式

描述 (結果)

15000

12

=[欄 1]/[欄 2]

將 15000 除以 12 (1250)

15000

12

=([欄 1]+10000)/[欄 2]

將 15000 與 10000 相加,再將總數除以 12 (2083)

計算數字的平均值

平均值 (Average) 也稱為平均值 (Mean)。若要計算一列中,兩個或多個欄中之數值的平均值,則使用 AVERAGE 函數。

欄 1

欄 2

欄 3

公式

描述 (結果)

6

5

4

=AVERAGE([欄 1], [欄 2], [欄 3])

最前面三欄中數字的平均值 (5)

6

5

4

=AVERAGE(IF([欄 1]>[欄 2], [欄 1]-[欄 2], 10), [欄 3])

如果欄 1 大於欄 2,則計算其差值與欄 3 的平均值,否則就計算數值 10 與欄 3 的平均值 (2.5)

計算數字的中位數

中間數是在以順序排列之範圍的數值中心值。使用 MEDIAN 函數計算一組數值的中間數。

A

B

C

D

E

F

公式

描述 (結果)

10

7

9

27

0

4

=MEDIAN(A, B, C, D, E, F)

最前面 6 欄中數字的中間數 (8)

計算範圍中的最小或最大數字

若要計算某一列中兩欄以上數字的最小數或最大數,請使用 MIN 及 MAX 函數。

欄 1

欄 2

欄 3

公式

描述 (結果)

10

7

9

=MIN([欄 1], [欄 2], [欄 3])

最小數 (7)

10

7

9

=MAX([欄 1], [欄 2], [欄 3])

最大數 (10)

計算值

若要計算數值的數目,請使用 COUNT 函數。

欄 1

欄 2

欄 3

公式

描述 (結果)

Apple

12/12/2007

=COUNT([欄 1], [欄 2], [欄 3])

會計算包含數值的欄數,但是會排除日期及時間、文字及 Null 值 (0)

$12

#DIV/0!

1.01

=COUNT([欄 1], [欄 2], [欄 3])

會計算包含數值的欄數,但是會排除錯誤及邏輯值 (2)

增加或減少的百分比數字

使用百分比運算子 (%) 來執行這項計算。

欄 1

欄 2

公式

描述 (結果)

23

3%

=[欄 1]*(1+5%)

會將欄 1 中的數字增加 5% (24.15)

23

3%

=[欄 1]*(1+[欄 2])

會將欄 1 中的數字依照欄 2 中的百分比值增加:3% (23.69)

23

3%

=[欄 1]*(1-[欄 2])

會將欄 1 中的數字依照欄 2 中的百分比值減少:3% (22.31)

提高數字乘冪

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

欄 1

欄 2

公式

描述 (結果)

5

2

=[欄 1]^[欄 2]

會計算 5 的平方 (25)

5

3

=POWER([欄 1], [欄 2])

會計算 5 的立方 (125)

將數字四捨五入

若要將數字進位,請使用 ROUNDUP、ODD 或 EVEN 函數。

欄 1

公式

描述 (結果)

20.3

=ROUNDUP([欄 1],0)

將 20.3 進位到最接近的整數 (21)

-5.9

=ROUNDUP([欄 1],0)

將 -5.9 進位到最接近的整數 (-5)

12.5493

=ROUNDUP([欄 1],2)

將 12.5493 進位到最接近的百分位,即兩個小數位數 (12.55)

20.3

=EVEN([欄 1])

將 20.3 進位到最接近的偶數 (22)

20.3

=ODD([欄 1])

將 20.3 進位到最接近的奇數 (21)

若要將數字捨位,請使用 ROUNDDOWN 函數。

欄 1

公式

描述 (結果)

20.3

=ROUNDDOWN([欄 1],0)

將 20.3 捨位到最接近的整數 (20)

-5.9

=ROUNDDOWN([欄 1],0)

將 -5.9 捨位到最接近的整數 (-6)

12.5493

=ROUNDDOWN([欄 1],2)

將 12.5493 捨位到最接近的百分位,即兩個小數位數 (12.54)

若要將數字四捨五入到最接近的整數或分數,請使用 ROUND 函數。

欄 1

公式

描述 (結果)

20.3

=ROUND([欄 1],0)

將 20.3 捨位,因為分數部分小於 .5 (20)

5.9

=ROUND([欄 1],0)

將 5.9 進位,因為分數部分大於 .5 (6)

-5.9

=ROUND([欄 1],0)

將 -5.9 捨位,因為分數部分小於 -.5 (-6)

1.25

=ROUND([欄 1], 1)

將數值四捨五入到最接近的十分位 (一個小數位數)。因為要四捨五入的部分是 0.05 或更大的數,所以此數值要進位 (結果:1.3)

30.452

=ROUND([欄 1], 2)

將數值四捨五入到最接近的百分位 (兩個小數位數)。因為要四捨五入的部分 (0.002) 小於 0.005,所以會捨位該數值 (結果:30.45)

若要將數字四捨五入到 0 以上的有效數字,請使用 ROUND、ROUNDUP、ROUNDDOWN、INT 及 LEN 函數。

欄 1

公式

描述 (結果)

5492820

=ROUND([欄 1],3-LEN(INT([欄 1])))

將數字四捨五入到 3 個有效數字 (5490000)

22230

=ROUNDDOWN([欄 1],3-LEN(INT([欄 1])))

將末尾數捨位到 3 個有效數字 (22200)

5492820

=ROUNDUP([欄 1], 5-LEN(INT([欄 1])))

將首位數進位到 5 個有效數字 (5492900)

頁面頂端

文字公式

下列公式可以用來處理文字,例如將多欄的值合併或串連起來、比較欄的內容、移除字元或空格,以及重複某些字元。

變更文字大小寫

若要變更文字的大小寫,請使用 UPPER、LOWER 或 PROPER 函數。

欄 1

公式

描述 (結果)

nina Vietzen

=UPPER([欄 1])

將文字變更為大寫 (NINA VIETZEN)

nina Vietzen

=LOWER([欄 1])

將文字變更為小寫 (nina vietzen)

nina Vietzen

=PROPER([欄 1])

將文字變更為首字母大寫 (Nina Vietzen)

合併名字和姓氏

若要合併姓名,請使用 & 符號運算子或 CONCATENATE 函數。

欄 1

欄 2

公式

描述 (結果)

Carlos

Carvallo

=[欄 1]&[欄 2]

合併這兩個字串 (CarlosCarvallo)

Carlos

Carvallo

=[欄 1]&" "&[欄 2]

合併這兩個字串,並以空格分開 (Carlos Carvallo)

Carlos

Carvallo

=[欄 2]&", "&[欄 1]

合併這兩個字串,並以逗號及空格分開 (Carvallo, Carlos)

Carlos

Carvallo

=CONCATENATE([欄 2], ",", [欄 1])

合併這兩個字串,並以逗號分開 (Carvallo,Carlos)

合併文字和數字,從不同的資料行

若要合併文字及數字,請使用 CONCATENATE 函數、& 符號運算子,或是 TEXT 函數與該符號運算子。

欄 1

欄 2

公式

描述 (結果)

Yang

28

=[欄 1]&" 賣出了 "&[欄 2]&" 個單位。"

會將上述內容合併為一個片語 (Yang 賣出了 28 個單位。)

Dubois

40%

=[欄 1]&" 賣出了 "&TEXT([欄 2],"0%")&" 的總銷售量。"

會將上述內容合併為一個片語 (Dubois 賣出了 40% 的總銷售量。)

附註: TEXT 函數會使用欄 2 的格式化值,而不是 .4 的基礎值。

Yang

28

=CONCATENATE([欄 1]," 賣出了 ",[欄 2]," 個單位。")

會將上述內容合併為一個片語 (Yang 賣出了 28 個單位。)

合併文字與日期或時間

若要合併文字與日期或時間,請使用 TEXT 函數及 & 符號運算子。

欄 1

欄 2

公式

描述 (結果)

Billing Date

5-Jun-2007

="收據日期:"&TEXT([欄 2], "d-mmm-yyyy")

會將文字與日期合併 (收據日期:5-Jun-2007)

Billing Date

5-Jun-2007

=[欄 1]&" "&TEXT([欄 2], "mmm-dd-yyyy")

會將不同欄中的文字與日期合併成一欄 (給付日期 Jun-05-2007)

比較欄的內容

若要將某一欄與另一欄或數值清單做比較,請使用 EXACT 及 OR 函數。

欄 1

欄 2

公式

說明 (可能結果)

BD122

BD123

=EXACT([欄 1],[欄 2])

會比較最前面兩欄的內容 (No)

BD122

BD123

=EXACT([欄 1], "BD122")

會比較欄 1 的內容與字串 "BD122" (Yes)

檢查資料行的值或部分符合特定文字

若要檢查欄的值或其中的一部分是否與特定文字相符,請使用 IF、FIND、SEARCH 及 ISNUMBER 函數。

欄 1

公式

說明 (可能結果)

Vietzen

=IF([欄 1]="Vietzen", "OK", "Not OK")

會檢查欄 1 是否為 Vietzen (OK)

Vietzen

=IF(ISNUMBER(FIND("v",[欄 1])), "OK", "Not OK")

會檢查欄 1 是否包含字母 v (OK)

BD123

=ISNUMBER(FIND("BD",[欄 1]))

會檢查欄 1 是否包含 BD (Yes)

計算非空白資料行

若要計算非空白欄的數目,請使用 COUNTA 函數。

欄 1

欄 2

欄 3

公式

描述 (結果)

銷售額

19

=COUNTA([欄 1], [欄 2])

計算非空白欄的數目 (2)

銷售額

19

=COUNTA([欄 1], [欄 2], [欄 3])

計算非空白欄的數目 (2)

移除文字的字元

若要移除文字中的字元,請使用 LEN、LEFT 及 RIGHT 函數。

欄 1

公式

描述 (結果)

Vitamin A

=LEFT([欄 1],LEN([欄 1])-2)

會傳回 7 (9-2) 個字元,從左邊開始算起 (Vitamin)

Vitamin B1

=RIGHT([欄 1], LEN([欄 1])-8)

會傳回 2 (10-8) 個字元,從右邊開始算起 (B1)

移除開頭和結尾的資料行中的空格

若要移除欄中的空格,請使用 TRIM 函數。

欄 1

公式

描述 (結果)

 Hello there!

=TRIM([欄 1])

移除開頭及結尾的空格 (Hello there!)

重複資料行中的一個字元

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

公式

描述 (結果)

=REPT(".",3)

重複句點 3 次 (...)

=REPT("-",10)

重複虛線 10 次 (----------)

頁面頂端

附註: 機器翻譯免責聲明︰本文係以電腦系統翻譯而成,未經人為介入。Microsoft 提供此等機器翻譯旨在協助非英語系使用者輕鬆閱讀 Microsoft 產品、服務及技術相關內容。基於本文乃由機器翻譯而成,因此文中可能出現詞辭、語法、文法上之錯誤。

擴展您的技能
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與其中一位 Office 支援專員連絡以深入了解您的意見。

×