No_asset_id

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

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

例如,下列公式會先計算 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 在公式中執行運算的順序

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

計算順序

公式按特定順序計算數值。Excel 中的公式通常以等號 (=) 開始,Excel 將等號之後的字元解譯為公式。等號之後就是要計算的元素 (運算元),如常數或儲存格參照,這些元素是由計算運算子加以分隔。Excel 根據公式中運算子的特定順序,由左至右計算公式。

運算子優先順序

若單一個公式中結合多個運算子,Microsoft Excel 會按照下表中顯示的順序來執行運算。如果公式裡面的運算子有相同的前導參照 (例如,一個公式裡面同時有乘法和除法運算子) Excel 會由左至右評估運算子。

運算子

描述

: (冒號)

(一個空格)

, (逗號)

參照運算子

負值 (例如 –1)

%

百分比

^

乘冪

* 和 /

乘和除

+ 和 -

加和減

&

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

=
< >
<=
>=
<>

比較

括號的使用

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

=5+2*3

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

=(5+2)*3

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

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

頁面頂端​​

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

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

函數的語法

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

函數的結構

函數的結構

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

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

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

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

輸入函數

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

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

巢狀函數

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

巢狀函數

1. AVERAGE 及 SUM 函數都是 IF 函數的巢狀函數。

有效傳回    將巢狀函數當做引數使用時,巢狀函數傳回的值類型必須與引數使用的值類型相同。例如,如果引數傳回 TRUE 或 FALSE 值,則巢狀函數也必須傳回 TRUE 或 FALSE 值。如果函數不傳回同類型值,Excel 就會顯示 #VALUE! 錯誤值。

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

頁面頂端​​

在公式中使用參照

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

A1 參照樣式

預設欄名列號表示法    根據預設,Excel 會使用 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 及 B 10)。

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 使用儲存在參照開始及結束名稱之間的任何工作表。例如,=SUM (Sheet2:Sheet13!B5) 會加總 Sheet 2 及 Sheet 13 之間所有工作表中儲存格 B5 的值加總 (包含 Sheet 2 及 Sheet 13)。

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

  • 立體參照無法用於陣列公式。

  • 立體參照無法搭配交集運算子 (單一空格) 使用,或用於使用隱含交集的公式。

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

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

  • 刪除    如果刪除了 Sheet2 和 Sheet6 之間的工作表,Excel 將會從計算中移除它們的值。

  • 移動     如果將 Sheet2 和 Sheet6 之間的工作表搬移到參照工作表區域之外的位置,Excel 將會從計算中移除它們的值。

  • 移動起迄工作表     如果將 Sheet2 或 Sheet6 移到同一活頁簿中的其他位置,Excel 會對計算進行修正,以包含它們之間新的工作表範圍。

  • 刪除起迄工作表    如果刪除了 Sheet2 或 Sheet6,Excel 將會對計算進行調整,以包含它們之間的工作表範圍。

R1C1 參照樣式

您可以在工作表上欄列編號的地方,使用參照樣式。R1C1 參照樣式在計算巨集中的列和欄位置時非常有用。Excel 在 R1C1 樣式中使用 R 加上列號碼和 C 加上欄號碼,來表示儲存格的位置。

參照

意義

R[-2]C

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

R[2]C[2]

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

R2C2

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

R[-1]

相對參照使用中儲存格上方的整列

R

絕對參照目前列

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

您可以在 [選項] 對話方塊的 [公式] 類別中,藉由在 [運用公式] 區段底下選取或取消選取 [[R1C1] 欄名列號表示法] 核取方塊,來開啟或關閉 [R1C1] 欄名列號表示法。若要顯示這個對話方塊,請按一下 [檔案] 索引標籤。

頁面頂端​​

在公式中使用名稱

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

範例類型

不含名稱的範例

含有名稱的範例

參照

=SUM(C20:C30)

=SUM(第一季銷售)

常數

=PRODUCT(A5,8.3)

=PRODUCT(價格,WA 營業稅)

公式

=SUM(VLOOKUP(A1,B1:F20,5,FALSE), -G5)

=SUM(Inventory_Level,-Order_Amt)

表格

C4:G36

=TopSales06

名稱類型

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

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

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

如需 Excel 表格的詳細資訊,請參閱搭配 Excel 表格使用結構化參照

建立及輸入名稱

您可以使用下列方式來建立名稱:

  • 資料編輯列上的名稱方塊    這是用來為選取範圍建立活頁簿層級名稱的最佳方式。

  • 從選取範圍建立名稱    藉由使用工作表中的儲存格選取範圍,可以方便地從現有列和欄標籤建立名稱。

  • [新名稱] 對話方塊    當您想要在建立名稱 (例如指定本機工作表層級範圍,或建立名稱註解) 時擁有最大的彈性,這是最佳方式。

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

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

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

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

  • 選取 [用於公式] 命令    在 [公式] 索引標籤上,從 [已定義之名稱] 群組的 [用於公式] 命令,選取清單中的已定義名稱。

如需詳細資訊,請參閱定義及使用公式中的名稱

頁面頂端​​

使用陣列公式及陣列常數

陣列公式可以同時執行多個計算然後傳回單一結果或多個結果。陣列公式可以在兩組以上的數值上 (也就是陣列引數) 進行運算。每個陣列引數必須具有相同的列數和欄數。除了不能用 CTRL+SHIFT+ENTER 組合鍵產生公式外,建立陣列公式的方法與建立其他公式的方法相同。部分的內建函數是陣列公式,而且必須以陣列格式進行輸入才能得到正確的結果。

當您不想在工作表的每個儲存格輸入所有的常數值時,陣列常數可以用來代替參照。

使用陣列公式計算單一結果或多個結果

當您輸入陣列公式時,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}。陣列常數中的數字可以是整數、小數或科學記號格式。文字前後必須加上雙引號,例如 "Tuesday"。

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

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

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

  • 使用逗號 (,) 區隔不同欄的值。例如,輸入 {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}.

頁面頂端​​

這項資訊有幫助嗎?

太好了! 還有其他意見反應嗎?

我們應該如何改進?

感謝您的意見反應!

×