公式概觀

如果您是 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, 共16384欄) 的資料行, 並參照數位 (1 到 1048576) 的資料列。 這些字母及數字被稱為列名與欄名。 若要參照儲存格,請輸入一個欄字母,再加上列號。 例如,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] 與 [工作] (這個範例中的端點) 中插入或複製工作表, Excel Online 會在計算中包含已新增工作表的儲存格 A2 到 A5 中的所有值。

  • 刪除     如果您刪除 [工作表 2] 與 [工作] 之間的工作表, Excel Online 會從計算中移除其值。

  •     如果您將工作表從 [圖表 2] 與 [工作] 移至參照的工作表範圍以外的位置, Excel Online 會從計算中移除其值。

  • 移動端點    如果您將工作表2或工作移至同一個活頁簿中的另一個位置, Excel Online 會調整計算, 以適應它們之間的新工作表範圍。

  • 刪除端點    如果您刪除了 [圖表 2] 或 [工作], Excel Online 會調整計算, 以容納它們之間的工作表範圍。

R1C1 欄名列號表示法

您也可以使用會在工作表上編排列與欄的欄名列號表示法。 R1C1 欄名列號表示法在計算巨集中的列和欄位置時非常有用。 在 R1C1 樣式中, Excel Online 會在儲存格的位置加上 "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 資料表名稱 "Table1", "資料表 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}。

附註:  本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 告訴我們這項資訊是否有幫助? 這裡是供您參考的英文文章

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

這項資訊有幫助嗎?

感謝您的意見反應!

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

×