公式概觀

公式是等式,可以執行計算、傳回資訊、操控其他儲存格的內容、測試條件,還有其他作業。公式永遠都是以等號開始 (=)。

下面是您可以輸入工作表中的公式類型範例。

  • =5+2*3    2 乘以 3 再加 5。
  • =A1+A2+A3    將儲存格 A1、A2 和 A3 中的值相加。
  • =SQRT(A1)    使用 SQRT 函數可傳回 A1 中值的平方根。
  • =TODAY()    傳回目前的日期。
  • =UPPER("hello")     使用 UPPER 工作表函數,將文字 "hello" 轉換成 "HELLO"。
  • =IF(A1>0)    測試儲存格 A1 以判斷其值是否大於 0。

本文內容


公式的組成部分

公式中也可以包含下列任一或所有項目:函數 (函數:接受值、執行作業並傳回值之預先撰寫的公式。使用函數可以簡化並縮短工作表上的公式,特別是執行冗長或複雜計算的公式。)儲存格參照 (儲存格參照:儲存格在工作表上佔據的一組座標。例如,出現在欄 B 與列 3 交叉處儲存格的參照是 B3。)運算子 (運算子:指定運算式中執行之計算類型的符號。有數學、比較、邏輯及參照四種運算子。)常數 (常數:不計算因而不變更的值。例如,數字 210 及文字 "Quarterly Earnings" 是常數。運算式或運算式得出的結果值不是常數。)


公式的組成部分

公式的組成部分
圖說文字 1 函數:PI() 函數會傳回 pi 的值:3.142...。
圖說文字 2 參照:A2 會傳回儲存格 A2 中的值。
圖說文字 3 常數:直接輸入公式中的數字或文字值,例如 2。
圖說文字 4 運算子:^ (插入號) 運算子會進行乘冪運算,而 * (星號) 運算子會進行乘法運算。

頁首 頁首

在公式中使用常數

常數是一種非用於計算的值,永遠保持相同的值。例如日期 10/9/2008、數字 210 與文字 "季收入" 都是常數。運算式 (運算式:計算結果為單一值的運算子、欄位名稱、函數、文字及常數組合。運算式可以指定準則 (如訂單數量>10000) 或執行欄位值的計算 (如 Price*Quantity)。)或由運算式產生的數值不是常數。如果公式中使用了常數而沒有儲存格參照 (例如,=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 根據公式中運算子的特定順序,由左至右計算公式。

運算子優先順序

如果您在單一個公式中結合多個運算子,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。
圖說文字 4 引數工具提示。含語法與引數的工具提示會在您輸入函數時出現,例如,輸入 =ROUND( 後就會出現工具提示。只有內建函數才會有工具提示。

輸入函數

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

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

巢狀函數

在某些情況下,您可能需要將某函數做為另一函數的引數 (引數:函數用來執行作業或計算的值。函數使用的引數類型是函數特定的。函數中使用的一般引數包含數字、文字、儲存格參照及名稱。)使用。例如,下列的計算式是使用巢狀 AVERAGE 函數,並將結果與 50 進行比較。

巢狀函數

圖說文字 1 AVERAGE 及 SUM 函數都是 IF 函數的巢狀函數。

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

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

頁首 頁首

在公式中使用參照

參照位址可以識別工作表上的一個儲存格或儲存格範圍,並告知 Excel 到哪裡可以找到公式要用的值或資料。您可以透過參照位址,在一個公式中使用工作表不同部分中所包含的資料,或在多個公式中使用同一個儲存格中的值。您也可以參照至同一個活頁簿中其他工作表上的儲存格。參照到其他活頁簿中的儲存格稱為連結或外部參照 (外部參照:對其他 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 工作表函數會在相同活頁簿的 [Marketing] 工作表上,計算 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 使用儲存在參照開始及結束名稱之間的任何工作表。例如,=SUM (Sheet2:Sheet13!B5) 會加總 Sheet 2 及 Sheet 13 之間所有工作表中儲存格 B5 的值加總 (包含 Sheet 2 及 Sheet 13 )。

移動、複製、插入或刪除工作表時會產生的影響    下列範例會說明當您移動、複製、插入或刪除立體參照中的工作表時會產生的影響。本範例會使用公式 =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 參照樣式在計算巨集 (巨集:可用來自動化工作的動作或一組動作。巨集以 Visual Basic for Applications 程式設計語言記錄。)中的列和欄位置時非常有用。Excel 在 R1C1 樣式中使用 R 加上列號碼和 C 加上欄號碼,來表示儲存格的位置。

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

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

頁首 頁首

在公式中使用名稱

您可以建立已定義的名稱 (名稱:代表儲存格、儲存格範圍、公式或常數值的字或字串。請使用容易理解的名稱。例如,用「產品」來指示難以理解的範圍,如 Sales!C20:C30。)來代表儲存格、儲存格範圍、公式、常數或 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 = 總銷售額 06

名稱類型

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

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

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

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

建立及輸入名稱

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

  • 資料編輯列上的名稱方塊    這是用來為選取範圍建立活頁簿層級名稱的最佳方式。
  • 以選取範圍建立名稱    您可以使用工作表中選取的儲存格,輕鬆地利用現有的列及欄標籤來建立名稱。
  • [新名稱] 對話方塊    當您想要在建立名稱 (例如指定本機工作表層級範圍,或建立名稱註解) 時擁有最大的彈性,這是最佳方式。

 附註   根據預設,名稱會使用絕對儲存格參照 (絕對儲存格參照:公式中儲存格的準確位址,不管包含公式的儲存格位置在何處。絕對儲存格參照的形式是 $A$1。)

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

  • 輸入     舉例來說,您可以輸入名稱做為公式的引數。
  • 使用 [公式自動完成]    使用 [公式自動完成] 下拉式清單,其中會自動為您顯示有效的名稱。
  • 透過 [用於公式] 命令選取    在 [公式] 索引標籤上,從 [已定義之名稱] 群組的 [用於公式] 命令,選取清單中的已定義名稱。

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

頁首 頁首

使用陣列公式及陣列常數

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

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

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

當您輸入陣列公式 (陣列公式:針對一或多組值執行多個計算,然後傳回單一結果或多個結果的公式。陣列公式括在大括弧 { } 中,且藉由按 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}.

頁首 頁首

 
 
適用:
Excel 2010, Excel 2007