Excel 公式概觀

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

公式的組成部分   

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

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

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

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

常數是一種非用於計算的值，永遠保持相同的值。例如日期 10/9/2008、數字 210 與文字 "季收入" 都是常數。運算式或由運算式產生的值不是常數。如果公式中使用了常數而非儲存格參照 (例如，=30+70+110)，則結果只在修改公式時才會發生變更。一般而言，最好將常數置於個別的儲存格以方便需要時變更，然後於公式中參照這些儲存格。

運算子會指定您想要在公式元素上執行的計算類型。Excel 在計算時會遵循一般數學規則，也就是按照括號、指數、乘法和除法以及加法和減法，或順序為 PEMDAS 的縮寫 (Please Excuse My Dear Aunt Sally)。使用括號可讓您變更計算的順序。

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

• 算術運算子

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

  算術運算子	意義	範例
  + (加號)	加法	=3+3
  - (減號)	減法 負	=3-3 =-3
  * (星號)	乘法	=3*3
  / (斜線)	除法	=3/3
  % (百分比符號)	百分比	30%
  ^ (插入號)	乘冪	=3^3

• 比較運算子

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

  比較運算子	意義	範例
  = (等號)	等於	=A1=B1
  > (大於符號)	大於	=A1>B1
  < (小於符號)	小於	=A1<B1
  >= (大於或等於符號)	大於或等於	=A1>=B1
  <= (小於或等於符號)	小於或等於	=A1<=B1
  <> (不等於符號)	不等於	=A1<>B1

• 文字串連運算子

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

  文字運算子	意義	範例
  & (& 符號)	將兩個值連接或串連起來，產生一個連續的文字值	="北"&"風" 的結果為「北風」。 當 A1 為「姓氏」而 B1 為「名字」時，=A1&", "&B1 的結果則是「姓氏, 名字」。

• 參照運算子

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

  參照運算子	意義	範例
  : (冒號)	範圍運算子，可以將一個參照擴大到兩個參照之間 (包含這兩個參照) 的所有儲存格。	B5:B15
  , (逗號)	聯集運算子，可以將多個參照合併成一個參照	=SUM(B5:B15,D5:D15)
  (空格)	交集運算子，產生一個儲存格參照，其參照的儲存格為兩個參照交集的儲存格	B7:D7 C6:C8

頁面頂端

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

• 計算順序

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

• Excel 公式中的運算子優先順序

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

  運算子	描述
  : (冒號) (單一空格) , (逗號)	參照運算子
  –	負 (如在 -1 中)
  %	百分比
  ^	乘冪
  * 和 /	乘和除
  + 和 -	加和減
  &	連接兩個文字字串 (串連)
  = < > <= >= <>	比較

• 在 Excel 公式中使用括號

  若要變更評估順序，請用括號括住最先計算的公式部分。例如，下列公式的結果是 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)

  頁面頂端

函數是預先定義的公式，會使用稱為引數的特定值以特定的順序或結構來執行計算。函數可以用於執行簡單或複雜的計算。您可以在功能區的 [公式​​] 索引標籤上找到所有的 Excel 函數：

• Excel 函數語法

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

  

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

  2. 函數名稱。若要檢視可用的函數清單，請按一下儲存格並按 SHIFT+F3，這會啟動 [插入函數] 對話方塊。

  

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

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

  附註: 輸入函數時不需要全部大寫 (例如 =ROUND)，因為 Excel 會在您按下 Enter 時自動將函數名稱轉為大寫。假設您拼錯函數的名稱，例如您寫了 =SUME(A1:A10) 而不是 =SUM(A1:A10)，那麼 Excel 會傳回 #NAME? 錯誤。

• 輸入 Excel 函數

  當您建立包含函數的公式時，[插入函數] 對話方塊可協助您輸入工作表函數。當您從 [插入函數] 對話方塊選取函數時，Excel 便會啟動函數精靈，顯示函數的名稱、函數的每個引數、函數和每個引數的描述、函數目前的結果，以及整個公式目前的結果。

  

  若要更容易地建立及編輯公式，並將輸入及語法錯誤減到最少，可以使用 [公式自動完成]。在您輸入 = (等號) 及函數的開頭字母時，Excel 便會顯示動態下拉式清單，內含與輸入字母相符的有效函數、引數及名稱。接著您就可以從下拉式清單選取其中一個項目，而 Excel 會為您完成輸入。

  

• 巢狀放置 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 的儲存格範圍

  3. 以驚嘆號 (!) 將工作表參照與儲存格範圍參照隔開

  附註: 如果參照的工作表名稱有空格或數字，那麼您需要在它前後加上單引號 (')，如 ='123'!A1 或 ='January Revenue'!A1。

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

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

     以相對參照複製的公式   

     

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

     以絕對參照複製的公式   

     

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

     以混合參照複製的公式   

     

• 立體欄名列號表示法

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

  • 您可以使用立體參照以參照至其他工作表上的儲存格、定義名稱，以及使用下列函數建立公式：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 將在計算中包含新增之工作表中從儲存格 A2 到 A5 的所有值。

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

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

  • 移動端點   ：若您將工作表2 或工作表6 移至同一活頁簿中的其他位置，Excel 會調整計算，以納入其間的新工作表範圍。

  • 刪除端點   ：如果刪除工作表2 或工作表6，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 表格。名稱是有意義的表示法，讓每個看似難懂的儲存格參照、常數、公式或表格更容易了解。以下資訊會顯示名稱的常見範例，以及在公式中使用名稱協助提升理解度的方式：

範例 1

範例類型	使用範圍 (而非名稱) 的範例	使用名稱的範例
參照	=SUM(A16:A20)	=SUM(Sales)
常數	=PRODUCT(A12,9.5%)	=PRODUCT(Price,TaxRate)
公式	=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])

範例 2

請在下列表格中複製範例資料，再將之貼到新 Excel 工作表中的儲存格 A1。若要讓公式顯示結果，請選取公式，按 F2，然後再按 Enter。如有需要，您可以調整欄寬來查看所有資料。

範例類型	範例，沒有使用名稱	範例，使用名稱	公式和結果，使用名稱
參照	'=SUM(A9:A13)	'=SUM(Sales)	=SUM(Sales)
公式	'=TEXT(VLOOKUP(MAX(A9:13),A9:B13,2,FALSE),"m/dd/yyyy")	'=TEXT(VLOOKUP(MAX(Sales),SalesInfo,2,FALSE),"m/dd/yyyy")	=TEXT(VLOOKUP(MAX(Sales),SalesInfo,2,FALSE),"m/dd/yyyy")
價格
$995
銷售	Sale Date
$249	3/17/2011
$399	4/2/2011
$643	4/23/2011
$275	4/30/2011
$447	5/4/2011

• 名稱類型

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

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

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

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

• 建立及輸入名稱

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

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

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

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

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

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

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

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

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

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

頁面頂端

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

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

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

• 產生單一結果的陣列公式   ：這種類型的陣列公式可以用單一陣列公式取代多個不同的公式，來簡化工作表模組。

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

  

  當您輸入公式 ={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}。

頁面頂端

當您刪除公式時也會一併刪除公式的結果。不過，您也可以只移除公式，並將結果值顯示在儲存格中。

• 若要刪除公式及其結果值，請執行下列動作：

  1. 選取包含公式的儲存格或儲存格範圍。

  2. 按 DELETE。

• 若要刪除公式但不移除結果值，請執行下列動作：

  1. 選取包含公式的儲存格或儲存格範圍。

     如果公式是陣列公式，請選取包含陣列公式的儲存格範圍。

     如何選取包含陣列公式的儲存格範圍

     1. 按一下陣列公式內的儲存格。

     2. 在 [常用] 索引標籤上，按一下 [編輯] 群組中的 [尋找與選取]，然後按一下 [到]。

     3. 按一下 [特殊]。

     4. 按一下 [目前陣列]。

  2. 在 [常用] 索引標籤上的 [剪貼簿] 群組中，按一下 [複製] 。
     
     
     鍵盤快速鍵   ：您也可以按 CTRL+C。

  3. 在 [常用] 索引標籤的 [剪貼簿] 群組中，按一下 [貼上] 下方的箭號，然後按一下 [貼上值]。

頁面頂端

下表摘要列出您在輸入公式時可能會犯的一些最常見錯誤，以及避免公式錯誤的方法：

請務必...	其他資訊
公式中所有的左右括號都必須成對顯示	公式中使用的所有括號都為成對顯示。您建立公式時，Excel 會將輸入的括號以彩色顯示。
使用冒號指出您在公式中輸入的範圍	請以冒號 (:) 分隔參照範圍中第一個和最後一個儲存格。例如，A1:A5。
輸入所有必要的引數	函數可以有必要與選用引數 (在語法中以方括號表示)。您應該要輸入所有必要的引數。同時，請確定沒有輸入過多的引數。
在公式中執行最多 64 層函數的巢狀結構	公式中巢狀函數的上限為 64 層。
以單引號括住活頁簿或工作表的名稱	在參照其他工作表或活頁簿中的值或儲存格時，如果這些活頁簿或工作表的名稱含有非字母字元，則必須用單引號 (') 括住它們的名稱。
包含外部活頁簿的路徑	外部參照必須包含活頁簿名稱和其路徑。
輸入無格式的數字	您在公式中輸入的數字不應含有十進位分隔符號 (,) 或貨幣符號 ($) 格式，因為逗號已使用於引數分隔符號，而貨幣符號則用來標記絕對參照。例如，請在公式中輸入 1000，而不是 $1,000。

頁面頂端