公式概觀

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

如果您剛開始使用 Excel Online,很快地就會發現它不只是可讓您在欄或列中輸入數字的方格而已。 是的,您可以使用 Excel Online 找出欄或列中的數字總和,還可以計算貸款償還、解決數學或工程問題,或是根據您插入的變數找到最佳的案例分析藍本。

Excel Online 會在儲存格中使用公式以達成此目標。公式會在工作表中的資料上執行計算或其他動作。公式永遠都是以等號 (=) 開始 ,後面可以接數字、數學運算子 (例如加號或減號),以及真正能拓充公式功能的函數。

例如,下列公式會先計算 2 乘以 3,然後再將乘積加上 5,最後得到答案 11。

=2*3+5

接下來這個公式則是使用 PMT 函數計算貸款 $200,000 採用百分之 5 利率 (5% 除以 12 個月等於每個月的利率) 分成 30 年 (360 個月) 攤還的貸款償還 ($1,073.64):

=PMT(0.05/12,360,200000)

以下是您可以輸入工作表中的一些其他公式範例。

  • =A1+A2+A3    將儲存格 A1、A2 和 A3 中的值相加。

  • =SQRT(A1)    使用 SQRT 函數可傳回 A1 中值的平方根。

  • =TODAY()    傳回目前的日期。

  • =UPPER("hello")     使用 UPPER 工作表函數,將文字 "hello" 轉換成 "HELLO"。

  • =IF(A1>0)    測試儲存格 A1 以判斷其包含的值是否大於 0。

公式的組成部分

公式中也可以包含下列任一或所有項目:函數、參照、運算子和常數。

公式的組成部分
公式的組成部分

1. 函數:PI() 函數會傳回 pi 的值:3.142...。

2. 參照:A2 會傳回儲存格 A2 中的值。

3. 常數:直接輸入公式的數字或文字值,例如 2。

4. 運算子:^ (插入號) 運算子會進行乘冪運算,而 * (星號) 運算子會進行乘法運算。

在公式中使用常數

常數是一種非用於計算的值,永遠保持相同的值。例如日期 10/9/2008、數字 210 與文字 "季收入" 都是常數。運算式或由運算式產生的值不是常數。如果公式中使用了常數而非儲存格參照 (例如,=30+70+110),則結果只在修改公式時才會發生變更。

在公式中使用計算運算子

運算子會指定您要在公式元素上執行的計算類型。計算會根據預設的順序進行 (通常是遵循一般數學規則),但是您可以使用括弧來變更此順序。

運算子類型

計算運算子有四種不同的類型:算術、比較、文字串連及參照。

算術運算子

若要執行基本的數學運算,例如加法、減法、乘法或除法、合併數字,以及產生數字結果等,請使用下列算術運算子。

算術運算子

意義

範例

+ (加號)

加法

3+3

- (減號)

減法

3–1
–1

* (星號)

乘法

3*3

/ (斜線)

除法

3/3

% (百分比符號)

百分比

20%

^ (插入號)

乘冪

3^2

比較運算子

您可以使用下列運算子來比較兩個值。當使用這些運算子來比較兩個值時,結果會是邏輯值 - TRUE 或 FALSE。

比較運算子

意義

範例

= (等號)

等於

A1=B1

> (大於符號)

大於

A1>B1

< (小於符號)

小於

A1<B1

>= (大於或等於符號)

大於或等於

A1>=B1

<= (小於或等於符號)

小於或等於

A1<=B1

<> (不等於符號)

不等於

A1<>B1

文字串連運算子

使用 & 符號 (&) 將一個或多個文字字串連接起來,以產生單一的一段文字。

文字運算子

意義

範例

& (& 符號)

將兩個值連接或串連起來,產生一個連續的文字值

"North"&"wind" 會產生 "Northwind"

參照運算子

使用下列運算子合併儲存格範圍以進行計算。

參照運算子

意義

範例

: (冒號)

範圍運算子,可以將一個參照擴大到兩個參照之間 (包含這兩個參照) 的所有儲存格。

B5:B15

, (逗號)

聯集運算子,可以將多個參照合併成一個參照

SUM(B5:B15,D5:D15)

(空格)

交集運算子,產生一個儲存格參照,其參照的儲存格為兩個參照交集的儲存格

B7:D7 C6:C8

Excel Online 在公式中執行運算的順序

在某些情況下,執行計算的順序可能會影響公式的傳回值,因此請務必瞭解決定順序的方式,以及要如何變更順序才能取得您想要的結果。

計算順序

公式計算以特定順序中的值。公式一定會開始使用等號 (=)。Excel Online會解譯為公式等號後面的字元。等號之後所要的項目計算 (運算元),例如常數或儲存格參照。這些被以計算運算子。Excel Online計算公式從左到右,根據以特定順序的每一個公式中的運算子。

運算子優先順序

如果您在單一個公式中合併多個運算子,Excel Online 會按照下表中顯示的順序來執行運算。 如果公式中的運算子有相同的優先順序,例如一個公式裡面同時含有乘法和除法運算子,Excel Online 就會由左至右評估運算子。

運算子

描述

: (冒號)

(單一空格)

, (逗號)

參照運算子

負 (如在 -1 中)

%

百分比

^

乘冪

* 和 /

乘和除

+ 和 -

加和減

&

連接兩個文字字串 (串連)

=
< >
<=
>=
<>

比較

括號的使用

若要變更評估順序,請用括弧括住要先計算的公式部分。例如,下列公式的結果是 11,因為 Excel Online 先計算乘法再執行加法運算。公式先將 2 乘以 3,然後與 5 相加來得到結果。

=5+2*3

相反地,如果您使用括號變更語法,Excel Online 會先將 5 加 2,再將結果乘以 3,得到結果 21。

=(5+2)*3

在下面範例中,公式第一部分中的括號強制 Excel Online 先計算 B4+25,然後再除以儲存格 D5、E5 和 F5 中數值的和。

=(B4+25)/SUM(D5:F5)

在公式中使用函數及巢狀函數

函數是預先定義的公式,會使用稱為引數的特定值以特定的順序或結構來執行計算。函數可以用於執行簡單或複雜的計算。

函數的語法

下面 ROUND 函數的範例會將儲存格 A10 中的數值四捨五入來說明函數的語法。

函數的結構
函數的結構

1. 結構。函數結構是以等號 (=) 開頭,後面接函數名稱、左括弧、以逗點分隔的函數引數,以及右括弧。

2. 函數名稱。若要檢視可用的函數清單,請按一下儲存格並按 SHIFT+F3。

3. 引數。引數可以是數字、文字、邏輯值 (例如 TRUE 或 FALSE)、陣列、錯誤值 (例如 #N/A) 或儲存格參照。您指定的引數必須能產生有效的值。引數也可以是常數、公式或其他函數。

4. 引數工具提示。具有語法及引數的工具提示會在您輸入函數時出現。例如,輸入 =ROUND(,就會出現工具提示。只有內建函數才會出現工具提示。

輸入函數

當您建立包含函數的公式時,[插入函數] 對話方塊可協助您輸入工作表函數。將函數輸入公式時,[插入函數] 對話方塊會顯示函數的名稱、函數的每個引數、函數和每個引數的描述、函數目前的結果,以及整個公式目前的結果。

若要更容易地建立及編輯公式,並將輸入及語法錯誤減到最少,可以使用 [公式自動完成]。在您輸入 = (等號) 及開頭字母或顯示觸發程序之後,Excel Online 便會在儲存格下方顯示動態下拉式清單,內含與輸入字母或觸發程序相符的有效函數、引數及名稱。接著您就可以將下拉式清單中的項目插入公式中。

巢狀函數

在某些情況下,您可能需要將某函數做為另一函數的引數使用。例如,下列的公式是使用巢狀 AVERAGE 函數,並將結果與值 50 進行比較。

巢狀函數

1. AVERAGE 及 SUM 函數都是巢嵌在 IF 函數內的函數。

有效的傳回值    巢狀函數作為引數時,巢狀函數必須傳回引數所使用值類型的相同類型值。例如,如果引數傳回 TRUE 或 FALSE 值,那麼巢狀函數也必須傳回 TRUE 或 FALSE 值。如果函數不傳回同類型值,Excel Online 就會顯示 #VALUE! 的錯誤值。

巢狀層級限制    公式最多可以包含七個層級的巢狀函數。一個函數 (稱為函數 B) 在另一個函數 (稱為函數 A) 中當做引數使用時,函數 B 就會以第二層級的函數運作。例如,AVERAGESUM 函數都是第二層級函數 (如果都是當做 IF 函數的引數)。而在巢嵌在巢狀 AVERAGE 函數內部的函數就是第三層級函數,依此類推。

在公式中使用參照

參照可以識別工作表上的一個儲存格或儲存格範圍,並告知 Excel Online 到哪裡可以找到要在公式中使用的值或資料。您可以透過參照,在一個公式中使用工作表不同部分包含的資料,或在多個公式中使用同一個儲存格中的值。您也可以參照至同一個活頁簿中其他工作表上的儲存格,以及參照至其他活頁簿。其他活頁簿中的儲存格參照稱為連結或外部參照。

A1 欄名列號表示法

預設欄名列號表示法    根據預設,Excel Online 會使用 A1 欄名列號表示法,也就是使用字母來指稱欄 (A 到 XFD,合計 16,384 欄),以及使用數字來指稱列 (1 到 1,048,576)。這些字母及數字被稱為列名與欄名。若要參照儲存格,請輸入一個欄字母,再加上列號。例如,B2 表示欄 B 和列 2 相交的儲存格。

若要參照

使用

欄 A 和列 10 的儲存格

A10

欄 A 和列 10 到 20 的儲存格範圍

A10:A20

列 15 和欄 B 到 E 的儲存格範圍

B15:E15

列 5 的所有儲存格

5:5

列 5 到 10 的所有儲存格

5:10

欄 H 的所有儲存格

H:H

欄 H 到 J 的所有儲存格

H:J

欄 A 到 E 和列 10 到 20 的儲存格範圍

A10:E20

參照另一個工作表    在以下範例中,AVERAGE 工作表函數計算相同活頁簿中名為「行銷」的工作表上 B1:B10 範圍內的平均值。

工作表參照範例
參照到同一個活頁簿中其他工作表上的儲存格範圍

1. 參照到名為「行銷」的工作表

2. 參照到 B1 到 B10 之間的儲存格範圍 (包含 B1 及 B10)。

3. 將工作表參照與儲存格範圍參照隔開

絕對、相對及混合參照間的差異

相對參照    公式中的相對儲存格參照 (如 A1) 是根據包含此公式之儲存格和此參照所指向之儲存格的相對位置。如果包含公式的儲存格位置變更,該參照也會變更。若沿著列或欄複製或填滿公式,參照會自動跟著調整。根據預設,新的公式會使用相對參照。例如,如果將儲存格 B2 中的相對參照複製或填滿到儲存格 B3,它會從 =A1 自動調整為 =A2。

具有相對參照的複製公式
具有相對參照的複製公式

絕對參照    公式中的絕對儲存格參照 (如 $A$1) 一律會參照所指定位置的儲存格。如果包含公式的儲存格位置變更,絕對參照將保持不變。若沿著列或欄複製或填滿公式,絕對參照並不會跟著調整。根據預設,新的公式會使用相對參照,因此可能需要將它們切換為絕對參照。例如,如果將儲存格 B2 中的絕對參照複製或填滿到儲存格 B3,兩個儲存格內的參照都會保持相同:=$A$1。

具有絕對參照的複製公式
具有絕對參照的複製公式

混合參照    混合參照有一個絕對欄及相對列,或有一個絕對列及相對欄。絕對欄參照採用的格式為 $A1、$B1 等等。絕對列參照採用的格式為 A$1、B$1 等等。如果包含公式的儲存格位置變更,相對參照也會變更,但是絕對參照將保持不變。若沿著列或欄複製或填滿公式,相對參照將會自動調整,但絕對參照並不會跟著調整。例如,如果將儲存格 A2 中的混合參照複製或填滿到儲存格 B3,它會從 =A$1 調整為 =B$1。

具有混合參照的複製公式
具有混合參照的複製公式

立體欄名列號表示法

方便參照多個工作表    若想分析一個活頁簿中多個工作表上相同儲存格或儲存格範圍中的資料,可以使用立體參照。立體參照包括儲存格或範圍參照,開頭加上工作表名稱的範圍。Excel Online 使用儲存在參照開始及結束名稱之間的任何工作表。例如,=SUM (工作表2:工作表13!B5) 會加總工作表 2 及工作表 13 之間所有工作表中儲存格 B5 的值。

  • 您可以使用立體參照以參照至其他工作表上的儲存格、定義名稱,以及使用下列函數建立公式:SUM、AVERAGE、AVERAGEA、COUNT、COUNTA、MAX、MAXA、MIN、MINA、PRODUCT、STDEV.P、STDEV.S、STDEVA、STDEVPA、VAR.P、VAR.S、VARA 和 VARPA。

  • 立體參照不可用於陣列公式。

  • 立體參照不可用於交集運算子 (單一空格) 或使用隱含交集的公式。

移動、複製、插入或刪除工作表時會產生的影響    下列範例會說明當移動、複製、插入或刪除立體參照中包含的工作表時會產生的影響。本範例會使用公式 =SUM (工作表2:工作表6!A2:A5) 加總 工作表 2 到 6 中之儲存格 A2 到 A5 的值。

  • 插入或複製    如果在工作表2 和工作表6 (本範例中的端點) 之間插入或複製工作表,Excel Online 將在計算中包含新增之工作表中儲存格 A2 到 A5 的所有值。

  • 刪除     若您刪除工作表2 和工作表6 之間的工作表,Excel Online 將會從計算中移除其值。

  • 移動    如果將工作表2 和工作表6 之間的工作表搬移至參照工作表範圍之外的位置,Excel Online 將會從計算中移除其值。

  • 移動端點    若您將工作表2 或工作表6 移至同一活頁簿中的其他位置,Excel Online 會對計算進行調整,以包含它們之間新的工作表範圍。

  • 刪除端點    如果刪除了工作表2 或工作表6,Excel Online 將會對計算進行調整,以包含它們之間的工作表範圍。

R1C1 欄名列號表示法

您也可以使用會在工作表上編號列與欄的欄名列號表示法。R1C1 欄名列號表示法在計算巨集中的列和欄位置時非常有用。Excel Online 在 R1C1 表示法中使用 R 加上列號碼和 C 加上欄號碼,來表示儲存格的位置。

參照

意義

R[-2]C

相對參照至同一欄中上二列的儲存格

R[2]C[2]

相對參照至下二列右二欄的儲存格

R2C2

絕對參照至第二欄中第二列的儲存格

R[-1]

相對參照至作用儲存格上方的整列

R

絕對參照至目前的列

當您記錄巨集時,Excel Online 會使用 R1C1 欄名列號表示法來記錄一些命令。例如,如果您要記錄一個命令 (例如,按一下 [自動加總] 按鈕即插入公式來加總儲存格範圍),Excel Online 會使用 R1C1 表示法 (而非 A1 表示法) 來記錄公式。

在公式中使用名稱

您可以建立已定義名稱來代表儲存格、儲存格範圍、公式、常數或 Excel Online 表格。名稱是有意義的表示法,讓每個看似難懂的儲存格參照、常數、公式或表格更容易瞭解。以下資訊顯示名稱的常見範例,以及在公式中使用名稱協助提升理解度的方式:

範例類型

使用範圍 (而非名稱) 的範例

使用名稱的範例

參照

=SUM(A16:A20)

=SUM(Sales)

常數

=PRODUCT(A12,9.5%)

=PRODUCT(Price,KCTaxRate)

公式

=TEXT(VLOOKUP(MAX(A16,A20),A16:B20,2,FALSE),"m/dd/yyyy")

=TEXT(VLOOKUP(MAX(Sales),SalesInfo,2,FALSE),"m/dd/yyyy")

表格

A22:B25

=PRODUCT(Price,Table1[@Tax Rate])

名稱類型

有許多種類型的名稱可以供您建立及使用。

已定義之名稱    代表儲存格、儲存格範圍、公式或常數值的名稱。 您也可以建立自行定義的名稱。 此外,Excel Online 有時也會為您建立已定義的名稱,例如當您設定列印範圍時。

表格名稱    Excel Online 表格名稱,這是儲存在記錄 (列) 和欄位 (欄) 中之特定主題的資料集合。 Excel Online 每次您插入 Excel Online 表格時,會建立預設 Excel Online 表格名稱 (表格1、表格2,依此類推),但您可以變更名稱,讓這些名稱更有意義。

建立及輸入名稱

您可以使用建立從選取範圍的名稱,以建立名稱。您可以輕鬆地可以使用的工作表中的儲存格選取範圍,從現有列和欄標籤建立名稱。

附註: 根據預設,名稱會使用絕對儲存格參照。

您可以使用下列方式來輸入名稱:

  • 輸入    舉例來說,您可以輸入名稱作為公式的引數。

  • 使用 [公式自動完成]    使用 [公式自動完成] 下拉式清單,其中會自動為您列出有效的名稱。

使用陣列公式及常數陣列

Excel Online 不支援建立陣列公式。 您可以檢視在 Excel 桌面應用程式中建立之陣列公式的結果,但無法編輯或重新計算。如果您有 Excel 桌面應用程式,請按一下 [在 Excel 中開啟] 以處理陣列。

下列陣列範例可計算出股價與股份陣列的總值,而並未使用一列儲存格來計算並顯示每一張股票的個別值。

產生單一結果的陣列公式
產生單一結果的陣列公式

當您輸入公式 ={SUM(B2:D2*B3:D3)} 做為陣列公式時,它會計算每張股票的「股份」與「股價」,然後再加總這些計算的結果。

計算多個結果    有些工作表函數會傳回值陣列,或是要求值陣列作為引數。若要使用陣列公式計算多個結果,您必須將陣列輸入與陣列引數具有相同欄數和列數的儲存格範圍中。

例如,若已知連續三個月 (欄 A) 的三個銷售數字 (欄 B),則 TREND 函數會判斷銷售數字的直線排列值。若要顯示公式所有的結果,結果會輸入在欄 C 的三個儲存格中 (C1:C3)。

產生多個結果的陣列公式
產生多個結果的陣列公式

當您輸入公式 =TREND(B1:B3,A1:A3) 做為陣列公式時,它會根據三個銷售數字與三個月,計算出三個不同的結果 (22196、17079 與 11962)。

使用常數陣列

在一般的公式中,您可以在包含值的儲存格中輸入參照,或輸入稱為常數的值。同樣地,您也可以在陣列中輸入參照,或是輸入儲存格中包含的值之陣列 (也稱為常數陣列)。陣列公式可使用與非陣列公式相同的方式來接受常數,但是您必須按特定格式輸入常數陣列。

常數陣列可以包含數字、文字、邏輯值 (例如 TRUE 或 FALSE) 或錯誤值 (例如 #N/A)。不同類型的值可以在相同的常數陣列中,例如 {1,3,4;TRUE,FALSE,TRUE}。常數陣列中的數字可以是整數、小數或科學記號格式。文字前後必須括上雙引號,例如 "星期二"。

常數陣列中不得包含儲存格參照、長度不等的欄或列、公式或是特殊字元 $ (貨幣符號)、括弧或 % (百分比符號)。

設定常數陣列格式時,請確認:

  • 使用大括弧 ( { } ) 括住常數。

  • 使用逗號 (,) 區隔不同欄的值。例如,輸入 {10,20,30,40} 代表 10、20、30 和 40 等值。這種常數陣列稱作 1 對 4 陣列,相當於 1 列 4 欄的參照。

  • 使用分號 (;) 區隔不同列的值。例如,若要表示一列中的四個值 10、20、30、40 以及下一列中的 50、60、70 和 80 四個值,您必須輸入一個 2 對 4 常數陣列:{10,20,30,40;50,60,70,80}。

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

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×