共用公式範例

附註您可以在計算結果欄中使用下列範例。不包含直欄參照的範例可以用來指定欄的預設值。

使用 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 函數來執行此工作。

欄1	欄2	公式	描述
10	10	=欄1-欄2	第一個數值減去第二個數值 (0)
10	10	=IF(欄1-欄2,"",欄1-欄2)	當值為零時，傳回空值 (空白欄)
15	9	=IF(欄1-欄2,"-",欄1-欄2)	當值為零時，傳回虛線 (-)

加上日期

若要在日期中加上天數，請使用加 (+) 運算子。請注意，處理日期時，必須將計算結果欄的傳回類型設定為 [日期及時間]。

欄1	欄2	公式	描述
6/9/2007	3	=欄1+欄2	從 6/9/2007 加上三天 (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 加上三個月 (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 加上三年 (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(欄)+5)	從 6/9/2007 加上 3 年、1 個月和 5 天 (7/14/2010)
12/10/2008	=DATE(YEAR(欄1)+1,MONTH(欄1)+7,DAY(欄1)+5)	從 6/9/2007 加上 1 年、7 個月和 5 天 (1/14/2009)

計算兩個日期之間的差

使用 DATEDIF 函數來執行此工作。

欄1	欄2	公式	描述
01-Jan-1995	15-Jun-1999	=DATEDIF(欄1, 欄2,"d")	傳回兩個日期之間的天數 (1626)
01-Jan-1995	15-Jun-1999	=DATEDIF(欄1, 欄2,"ym")	傳回日期間的月數 (忽略年的部份) (5)
01-Jan-1995	15-Jun-1999	=DATEDIF(欄1, 欄2,"yd")	傳回日期間的天數 (忽略年的部份) (165)

計算兩個時間之間的差

使用減運算子 (-) 和 TEXT 函數，來以標準時間格式 (小時:分鐘:秒) 顯示結果。

若要使此方法工作，則小時不可超過 24，且分鐘和秒不可超過 60。

欄1	欄2	公式	描述
06/09/2007 上午 10:35	06/09/2007 下午 3:30	=TEXT(欄2-欄1,"h")	兩個時間之間的小時數 (4)
06/09/2007 上午 10:35	06/09/2007 下午 3:30	=TEXT(欄2-欄1,"h:mm")	兩個時間之間的小時數和分鐘數 (4:55)
06/09/2007 上午 10:35	06/09/2007 下午 3:30	=TEXT(欄2-欄1,"h:mm:ss")	兩個時間之間的小時數、分鐘數和秒數 (4:55:00)

使用 INT 函數或 HOUR、MINUTE 和 SECOND 函數，來根據某一時間單位顯示總結果。

欄1	欄2	公式	描述
06/09/2007 上午 10:35	06/10/2007 下午 3:30	=INT((欄2-欄1)*24)	兩個時間之間的總小時數 (28)
06/09/2007 上午 10:35	06/10/2007 下午 3:30	=INT((欄2-欄1)*1440)	兩個時間之間的總分鐘數 (1735)
06/09/2007 上午 10:35	06/10/2007 下午 3:30	=INT((欄2-欄1)*86400)	兩個時間之間的總秒數 (104100)
06/09/2007 上午 10:35	06/10/2007 下午 3:30	=HOUR(欄2-欄1)	當差別未超過 24 時，兩個時間之間的小時數。(4)
06/09/2007 上午 10:35	06/10/2007 下午 3:30	=MINUTE(欄2-欄1)	當差別未超過 60 時，兩個時間之間的分鐘數。(55)
06/09/2007 上午 10:35	06/10/2007 下午 3:30	=SECOND(欄2-欄1)	當差別未超過 60 時，兩個時間之間的秒數。(0)

轉換時間

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

欄1	公式	描述
上午 10:35	=(欄1-INT(欄1))*24	從上午 12:00 開始的小時數 (10.583333)
下午 12:15	=(欄1-INT(欄1))*24	從上午 12:00 開始的小時數 (12.25)

若要將小時數從小數點數字轉換到標準時間格式 (小時:分鐘:秒)，則使用除數運算子及 TEXT 函數。

欄1	公式	描述
10:5833	=TEXT(欄1/24, "h:mm")	自上午 12:00 開始的小時數 (10:35)
12:25	=TEXT(欄1/24, "h:mm")	自上午 12:00 開始的小時數 (12:15)

插入凱撒日期

片語「凱撒日期」有時是用來表示目前的年，以及自年初開始之天數的組合。例如，2007001 表示 2007 年 1 月 1 日，而 2003356 則表示 2007 年 12 月 31 日。

凱撒日期通常也用在天文學中，是以 1 月 1 日 4713 B.C.E 開始的序列日期系統。

附註此格式並非基於凱撒曆。

若要將日期轉換為凱撒日期，則使用 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	公式	描述
19-Feb-2007	=TEXT(WEEKDAY(欄1), "dddd")	計算該日期是星期幾並傳回這天的全名 (Monday)
3-Jan-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)

相減數值

使用減 (-) 運算子來執行此工作。

欄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 加上 9000，再將總數除以 12 (2000)

計算數值的平均值

平均值 (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，則計算欄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)	計算包含數值之欄的數目，包括日期及時間值。排除文字和空值。(1)
$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 進位 (-6)
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 捨位 (-5)
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	公式	描述
nancy Davolio	=UPPER(欄1)	將文字變更為大寫 (NANCY DAVOLIO)
nancy Davolio	=LOWER(欄1)	將文字變更為小寫 (nancy davolio)
nancy Davolio	=PROPER(欄1)	將文字變更為字首大寫 (Nancy Davolio)

合併名字和姓氏

使用連字號 (&) 運算子或 CONCATENATE 函數來執行此工作。

欄1	欄2	公式	描述
Nancy	Fuller	=欄1&欄2	合併兩個字串 (NancyFuller)
Nancy	Fuller	=欄1&" "&欄2	合併兩個字串，並以空格分開 (Nancy Fuller)
Nancy	Fuller	=欄2&","&欄1	合併兩個字串，並以逗號分開 (Fuller, Nancy)
Nancy	Fuller	=CONCATENATE(欄2, ",", 欄1)	合併兩個字串，並以逗號分開 (Fuller,Nancy)

合併不同欄的文字和數值

使用 CONCATENATE 和 TEXT 函數及連字號 (&) 運算子來執行此工作。

欄1	欄2	公式	描述
Buchanan	28	=欄1&" sold "&欄2&" units."	將以上內容合併為一個片語 (Buchanan sold 28 units)
Dodsworth	40%	=欄1&" sold "&TEXT(欄2,"0%")&" of the total sales."	將以上內容合併為一個片語 (Dodsworth sold 40% of the total sales)。附註 TEXT 函數會新增欄2 之格式化的值，而不是 .4 的基礎值。
Buchanan	28	=CONCATENATE(欄1," sold ",欄2," units.")	將上述內容合併為一個片語 (Buchanan sold 28 units)

合併文字與日期或時間

使用 TEXT 函數和連字號 (&) 運算子來執行此工作。

欄1	欄2	公式	描述
給付日期	5-Jun-2007	="狀態日期："&TEXT(欄2, "d-mmm-yyyy")	將文字與日期合併 (狀態日期：5-Jun-2007)
給付日期	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)
BD122	BD123	=OR(EXACT("BD121", 欄1, 欄2)	比較字串 "BD121" 和前兩個欄的內容 (No)

檢查欄的值或其中的一部份是否與特定文字相符

若要檢查欄的值或其中的一部份是否與特定的文字相符，則使用 IF、FIND、SEARCH 及 ISNUMBer 函數。

欄1	公式	描述
Davolio	=IF(欄1="Davolio", "OK", "Not OK")	檢查欄1 是否為 Davolio (OK)
Davolio	=IF(ISNUMBER(FIND("v",欄1)), "OK", "Not OK")	檢查欄1 是否包含字母 v (OK)
BD123	=ISNUMBER(FIND("BD",欄1))	檢查欄1 是否包含 BD (Yes)

計算非空白欄的數目

使用 COUNTA 函數來執行此工作。

欄1	欄2	欄3	公式	描述
Sales	19		=COUNTA(欄1, 欄2)	計算非空白欄的數目 (2)
Sales	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 次 (----------)

欄1	公式	描述
10	=欄1/欄2	造成錯誤 (#DIV/0)
10	=IF(ISERROR(欄1/欄2),"NA",欄1/欄2)	當值為錯誤時，傳回 NA
10	=IF(ISERROR(欄1/欄2),"-",欄1/欄2)	當值為錯誤時，傳回虛線