2.2 公式

本節介紹公式的一些基本概念和語法，然后詳細介紹如何建立、修改、移動和復制公式，以及公式的引用，公式的錯誤與審核，用數組計算等內容。

2.2.1 公式概述

公式主要用于計算。可以說，沒有公式的Excel就沒有使用價值。使用公式可以進行簡單的計算，如加、減、乘、除等；也可以完成很復雜的計算，如財務、統計和科學計算等；還可以使用公式進行比較或者操作文本和字符串。工作表中需要計算結果時，使用公式是最好的選擇。

簡單地說，公式就是一個等式，或者說是連續的一組數據和運算符組成的序列。

考察以下公式：

=10*2/3+4

=SUM（Al:A3）

=B5&C6

第一個公式是用戶所熟悉的，只是等號左邊是省略的單元格。第二、第三個公式可能以前沒有接觸過。在Excel中，公式有其本身的特點，并且有自己的規定，或者叫做語法。

在工作表單元格中輸入公式以后，公式的結果就會顯示在工作表中。要想查看產生結果的公式，只需選中該單元格，公式就會出現在公式欄中。要在單元格中編輯公式，雙擊該單元格或者按F2鍵。

操作實例2-1查看工作表中所有的公式內容

（1）選擇“工具”|“選項”命令，打開“選項”對話框。

（2）將“選項”對話框切換到“視圖”選項卡，選中“公式”復選框。

（3）單擊“確定”按鈕完成操作。

這時公式就出現在單元格中。在顯示公式與結果之間切換的快捷鍵是“Ctrl+`”。一般用戶都不想顯示工作表中的公式，但調試工作表時查看工作表的公式是很有用的。

下面介紹公式中的運算符和公式的運算順序。

1．運算符

在Excel中，運算符可以分為4類：算術運算符、比較運算符、文本運算符和引用運算符。

用戶通過算術運算符可以完成基本的數學運算，如加、減、乘、除、乘方和求百分數等，如表2-1所示的是Excel中所有的算術運算符。

比較運算符用于比較兩個數值，并產生邏輯值TRUE和FALSE，如表2-2所示的是Excel中所有的比較運算符。

文本運算符可以將一個或者多個文本連接為一個組合文本。文本運算符只有一個：&，其含義是將兩個文本值連接或串聯起來產生一個連續的文本值，如CLASS&ROOM的結果是CLASSROOM。

引用運算符可以將單元格區域合并運算，如表2-3所示的是Excel中所有的引用運算符。

2．運算順序

當公式中既有加法，又有乘法、除法，還有乘方，Excel是怎樣確定其運算先后順序的呢？這就需要理解運算符的運算順序，也就是運算符的優先級。對于同級運算，則按照由等號開始從左到右進行運算；對于不同級的運算符，則按照運算符的優先級進行運算。如表2-4所示的是常用運算符的運算優先級。

3．文本運算

文本運算符（&）用于連接字符串，例如，公式=“我愛”&“偉大的”&“中國”的結果是“我愛偉大的中國”。當然，文本運算符還可以連接數字，例如，公式=12&34的結果是1234字符串。

注意：

當用&來連接數字時，數字串兩邊的雙引號可以沒有，但對于連接一般的字母、字符串和文本時，雙引號不可去掉，否則公式將返回錯誤值。

4．比較運算

比較運算符可以對兩個數字或者兩個字符串進行比較，以產生邏輯值TRUE或FALSE。例如，公式=200<400的結果是TRUE；而公式=100>400的結果則是FALSE。

注意：

用比較運算符對字符串進行比較的時候，Excel會先將字符串轉化成內部的ASCII碼，然后再作比較。因此公式=“AB”>“BC”的結果是FALSE。

5．數值轉換

在公式中，每個運算符都需要特定類型的數值與之對應。如果輸入數值的類型與所需的類型不同，Excel有時可以對這個數值進行轉換。下面舉幾個例子來說明公式中數值的轉換。

例如，公式=“1”+“2”，產生的結果是3。這是因為使用（+）時，Excel會認為公式中運算項為數值。雖然公式中的引號說明“1”和“2”是文本型數字，但Excel會自動將文本數字轉換成數值。又例如，公式=“1”+“$2.00”，結果也是3，其原因與此相同。又例如，使用函數的公式=SQRT（“9”），則公式也會先將字符“9”轉換成數值9，然后再計算SQRT（）函數，即對9開方（有關函數的使用參看本章后面的章節），得到結果為3。

例如，公式="A"&TRUE，產生的結果是ATRUE。這是因為需要文本時，Excel會自動將數值和邏輯型值轉換成文本。

6．日期和時間

在Excel中，不僅可以對數字或者字符進行運算，同樣可以對日期進行運算。Excel會將日期存儲為一系列的序列數，而將時間存儲為小數，因為時間可以被看成日期的一部分。

用戶可以用一個日期減去另外一個日期來計算兩個日期的差值。例如，公式="98/10/1" -"97/8/1"的結果為426。即1998年10月1日和1997年8月1日之間相差426天。

同樣可以對日期進行其他的混合運算，例如，公式="98/10/1"-"97/8/1"/"98/10/1"的結果為36068.01。

提示：

當在Excel中輸入日期，并且年份輸入為兩位數時，Excel會將在00～29之間的輸入數解釋為2003～2029年，而將在30～99之間的輸入數解釋為1930～1999年。例如，對于10/10/1, Excel會認為這個日期為2010年10月1日，而將95/10/1認為是1995年10月1日。

7．語法

所謂公式的語法，就是公式中元素的結構或者順序。Excel中的公式遵循一個特定的語法：最前面是等號“=”，后面是參與運算的元素和運算符。元素可以是常量數值、單元格引用、標志名稱以及工作表函數。

2.2.2 公式的基本操作

公式的運用在Excel中占有很重要的地位。下面介紹公式的一些基本常用操作。

1．建立公式

公式的建立在前面的一些例子中都提到過，這一節將正式介紹怎樣通過鍵盤和公式選項板來創建公式。

（1）輸入公式

用鍵盤創建公式的操作步驟如下：

[1] 選擇要輸入公式的單元格。

[2] 先輸入等號“=”，然后輸入計算表達式；如果使用的是函數向導向單元格輸入公式，Excel會自動在公式前面插入等號。

[3] 按Enter鍵完成公式的輸入。

注意：

如果在某一區域內輸入同一個公式，單個輸入顯然太慢了。這時可以選中該單元格區域，輸入所需要的公式，然后按Ctrl+Enter鍵，則Excel會自動將所有單元格都粘貼上該輸入公式。這不僅對公式有效，而且對其他文本和字符都有效。

（2）公式選項板

如果創建含有函數的公式，那么公式選項板有助于輸入工作表函數和公式。

要顯示公式選項板，可以單擊編輯欄中的按鈕，當在公式中輸入函數時，公式選項板會顯示函數的名稱、函數中的每個參數、函數的當前結果和整個公式的結果等。“函數參數”對話框，如圖2-74所示。

下面以計算10、25、30的平均值為例來說明公式選項板的使用，這里要借助AVERAGE函數。

操作實例2-2平均值的計算

[1] 選中一個單元格。

[2] 在編輯欄中輸入“=AVERAGE（　）”，如圖2-75所示，此時為了預覽結果，可以用公式選項板。

[3] 單擊編輯欄中的按鈕，將會彈出公式選項板，并且公式選項板上會自動增加AVERAGE函數的使用選項。

[4] 在公式選項板的函數AVERAGE參數欄中分別輸入10、25和30。

[5] 輸入完畢后，計算結果將出現在公式選項板上，如圖2-76所示。

[6] 單擊“確定”按鈕完成公式的輸入，完整的公式將出現在編輯欄中，而計算結果就會顯示在所選單元格上。

2．修改公式

如果發現某個公式有錯誤，就必須對該公式進行修改。對公式的修改非常簡單，下面通過操作實例2-3進行介紹。

操作實例2-3學會修改公式

（1）單擊包含需要修改公式的單元格。

（2）在編輯欄中對公式進行修改。如果需要修改公式中的函數，則替換或修改函數的參數。

3．公式的移動和復制

如果要將含有公式的單元格整個（包括格式、邊框等）移動或者復制到另外的單元格或區域，可以按照前面章節介紹的移動和復制單元格的方法，也可以只粘貼單元格的公式。

如圖2-77所示，在單元格A1中有一個公式“=40+50*3”，現在要將它移動或者復制到C3單元格，可以按照如下步驟進行操作。

操作實例2-4單元格公式的粘貼

（1）單擊A1單元格。

（2）單擊“常用”工具欄中的“剪切”（進行移動操作）或者“復制”按鈕（進行復制操作）。

（3）在C4單元格上單擊鼠標右鍵，在彈出的快捷菜單中選擇“選擇性粘貼”命令，打開“選擇性粘貼”對話框，如圖2-78所示。

（4）在“選擇性粘貼”對話框中選中“公式”單選按鈕。

（5）單擊“確定”按鈕，完成移動或者復制操作。

2.2.3 公式的引用

每個單元格都有自己的行、列坐標位置，在Excel中將單元格行、列坐標位置稱之為單元格引用。在公式中可以通過引用來代替單元格中的實際數值。在公式中不但可以引用本工作簿中任何一個工作表中任何單元格或單元格組的數據，也可以引用其他工作簿中的任何單元格或者單元格組的數據。

引用單元格數據以后，公式的運算值將隨著被引用的單元格數據變化而變化。當被引用的單元格數據被修改后，公式的運算值將自動修改。

1．引用的類型

為滿足用戶的需要，Excel提供了3種不同的引用類型：相對引用、絕對引用和混合引用。在引用單元格數據時，要弄清這3種引用類型。

（1）絕對引用

絕對引用是指被引用的單元格與引用的單元格的位置關系是絕對的，無論將這個公式粘貼到哪個單元格，公式所引用的還是原來單元格的數據。絕對引用的單元格名稱的行和列前都有符號“$”，例如，$A$l, $D$2等。

（2）相對引用

相對引用的格式是直接用單元格或者單元格區域名，而不加符號“$”，例如，A1, D2等。使用相對引用后，系統將會記住建立公式的單元格和被引用的單元格的相對位置關系，在粘貼這個公式時，新的公式單元格和被引用的單元格仍保持這種相對位置。

如圖2-79所示的是包含4位學生成績的成績表。要計算4個人各科的平均分和總評成績。

計算平均分的公式是：4人成績的平均值。

計算總評成績的公式是：總評成績=語文*30%+數學*30%+英語*30%+體育*10%；各科在總評中所占比例已經列于第二行。

操作實例2-5絕對引用和相對引用

[1] 在單元格B9中輸入公式“=AVERAGE（B5, B6, B7, B8）”；在這個公式中，對單元格B5～B8都使用了相對引用。

[2] 確定以后，可以在單元格B9中得到計算結果。下面將單元格B9的公式復制到C9、D9、E9、F9中，復制完成后，用戶就會發現這些單元格中的公式與B9單元格的公式不同了。原來公式中的B分別被改為C、D、E和F。這就是相對引用。

[3] 接下來計算總評成績。

在單元格F5中輸入公式“=B5*$B$2+C5*$C$2+D5*$D$2+E5*$E$2”。

在這個公式中，對單元格B5～D5都使用了相對引用，而對B2～D2則采用了絕對引用。下面將單元格F5的公式復制到F6、F7和F8中，復制完成后，可以發現這些單元格中，公式相對引用的單元格名稱變了，而絕對引用的單元格名稱沒有改變。這時可以按“Ctrl+`”快捷鍵（用來切換查看公式還是公式的結果的快捷鍵）來查看工作表的所有公式。

（3）混合引用

若符號$在數字之前，而字母前沒有$，那么被應用的單元格的行位置是絕對的，列位置是相對的。反之，行的位置是相對的，而列的位置是絕對的。這就是混合引用，例如，$E3或者E$3。

2．引用同一工作簿中的單元格

在當前工作表中可以引用其他工作表中單元格的內容。例如，當前的工作表是Sheetl，如果要在A1單元格中引用工作表Sheet3中B6:B8的內容之和，有以下兩種方法。

（1）直接輸入。在Sheetl中選擇A1單元格，輸入“=SUM（Sheet3! B6:B8）”，然后按Enter鍵。

（2）用鼠標選擇需要引用的單元格。在Sheetl中選擇A1單元格，輸入“=SUM（”；單擊Sheet3工作表的標簽；在Sheet3中選擇B6:B8單元格，然后按Enter鍵。

注意：

當編輯欄中顯示Sheetl中A1單元格的公式“=SUM（Sheet3! B6:B8”時，此公式還缺少一個“）”，這時可以在編輯欄中補上“）”，也可以直接按Enter鍵，Excel會自動加上一個“）”。

3．引用其他工作簿中的單元格

在當前工作表中可以引用其他工作簿中的單元格或者單元格區域的數據或者公式。例如，當前的工作簿是“工作簿2”，如果“工作簿”的Sheet l工作表中的A1單元格要引用“工作簿1”（文件存放的路徑是“C:\My Documetns\工作簿1.xls”中的$B$3:$B$4單元格中的數據），可以按以下步驟進行操作。

（1）直接輸入。在Sheet l中選擇A1單元格，輸入“= SUM（'C:\My Documetns\[工作簿1.xls]Sheet1'! $B$3:$B$4）”，然后按Enter鍵。

（2）選擇需要引用的單元格。在Sheet l中選擇A1單元格，輸入“=SUM（”，打開“工作簿1”，在其中單擊工作表Sheetl的標簽，在Sheetl中選擇$B$3:$B$4單元格，然后按Enter鍵，關閉“工作簿1”。

為了便于操作和觀察，可以選擇“窗口”|“重排窗口”命令，接著單擊“確認”按鈕或者按Enter鍵，使“工作簿1”和“工作簿2”同時顯示在屏幕上，然后再進行上述的操作。

2.2.4 公式的錯誤與審核

審核公式對公式的正確性來說至關重要，它包括循環引用、公式返回的錯誤值、審核及檢查等內容。

1．循環引用

使用公式時引用公式自身所在的單元格，這時公式將把它視為循環引用。所謂公式的循環引用，指的是公式直接或者間接引用該公式所在的單元格的數值。在計算循環引用的公式時，Excel必須使用前一次迭代的結果來計算循環引用中的每個單元格。而迭代的意思就是重復工作表直到滿足特定的數值條件。如果不改變迭代的默認設置，Excel將在100次迭代以后或者兩個相鄰迭代得到的數值變化小于0.001時停止迭代運算。

在使用循環引用時，可以根據需要來設置迭代的次數和迭代的最大誤差，在Excel中默認的迭代次數為100次。

操作實例2-6更改默認的迭代設置

其具體操作步驟如下：

（1）選擇“工具”|“選項”命令，打開如圖2-80所示的“選項”對話框。

（2）單擊“重新計算”標簽，打開“重新計算”選項卡。

（3）選中“迭代計算”復選框。

（4）根據需要在“最多迭代次數”文本框中和“最大誤差”文本框中輸入進行迭代運算時的最多迭代次數和最大誤差。

（5）單擊“確定”按鈕完成操作。

2．公式返回的錯誤值

如果輸入的公式不符合格式或者其他要求，就無法在Excel工作表的單元格中顯示運算結果，該單元格中會顯示錯誤值信息，如“#####! ”、“#DIV/01”、“#N/A”、“# NAME? ”、“#NULL! ”、“#NUM! ”、“#REF! ”、“#VALUE! ”。了解這些錯誤值信息的含義有助于用戶修改單元格中的公式。如表2-5所示的是Excel中的錯誤值及其含義。

3．審核及檢查

Excel提供了公式審核功能，使用戶可以跟蹤選定范圍中公式的引用或者從屬單元格，也可以追蹤錯誤。使用這些功能的操作方法為：選中需要審核的公式所在的單元格，然后選擇“工具”|“公式審核”命令，如圖2-81所示，彈出的子菜單中包含了審核公式功能的各種命令。

如果需要顯示公式引用過的單元格，在圖2-81的菜單中選擇“追蹤引用單元格”命令，或者單擊“公式審核”工具欄中的“追蹤引用單元格”按鈕。這時公式所引用過的單元格就會有追蹤箭頭指向公式所在的單元格。取消該追蹤箭頭的方法是單擊“公式審核”工具欄中的“移去追蹤引用單元格箭頭”按鈕。

如果需要顯示某單元格被哪些單元格的公式引用，可以在圖2-81的菜單中選擇“追蹤從屬單元格”命令，或者單擊“公式審核”工具欄中的“追蹤從屬單元格”按鈕。這時該單元格就會產生指向引用它的公式所在單元格的追蹤箭頭。在刪除單元格之前，最好使用這種方法來檢查該單元格是否已被其他公式所引用。單擊“公式審核”工具欄中的“移去追蹤從屬單元格箭頭”按鈕可取消追蹤箭頭。

當單元格顯示錯誤值時，選擇“公式審核”子菜單中的“追蹤錯誤”命令或者單擊“公式審核”工具欄中的“追蹤錯誤”按鈕，即可追蹤出產生錯誤的單元格。

要取消上述的所有追蹤箭頭，可以在“公式審核”子菜單中選擇“取消所有追蹤箭頭”命令或者單擊“公式審核”工具欄中的“取消所有追蹤箭頭”按鈕。

要顯示“審核”工具欄，可以在“公式審核”子菜單中選擇“顯示‘公式審核’工具欄”命令。要想隱藏“審核”工具欄，重新選擇“公式審核”子菜單中的“顯示‘公式審核’工具欄”命令即可。

2.2.5 數組計算

數組是一組公式或值的長方形范圍，Excel視數組為一組。有些數組公式返回一組出現在很多單元格中的結果。數組是小空間進行大量計算的強有力的方法。它可以代替很多重復的公式。

1．輸入數組公式

操作實例2-7輸入數組公式

其具體操作步驟如下：

（1）選中需要輸入數組公式的單元格或者單元格區域。

（2）輸入公式的內容。

（3）按Shift + Ctrl + Enter快捷鍵結束輸入。

輸入數組公式其實是一個非常簡單的操作過程，但要理解它并不容易。下面舉例來幫助用戶理解怎樣建立數組公式。

如圖2-82所示的內容，要在C列得到A列和B列1～4行相乘的結果，這當然可以在C1單元格輸入公式“=Al*B1”，然后復制。現在要使用數組的方法得到這些結果，這時，A1～A4和B1～B4的數據就是數組的參數。其具體步驟如下：

選定C1～C4單元格區域（注意：4個單元格全部選中），然后在編輯欄中輸入公式“=Al:A4*B1:B4”，按Shift + Ctrl + Enter快捷鍵結束輸入，得到如圖2-83所示的結果。

注意：

數組公式如果返回多個結果，當刪除數組公式時，必須刪除整個數組公式，即選中整個數組公式所在單元格區域然后再刪除，不能只刪除數組公式的一部分。

2．選中數組范圍

通常，輸入數組公式的范圍，其大小與外形應該與作為輸入數據的范圍的大小和外形相同。如果存放結果的范圍太小，就看不到所有的結果；如果范圍太大，有些單元格中就會出現不必要的“#N/A”錯誤。因此，選擇的數組公式的范圍必須與數組參數的范圍一致。

3．數組常量

在數組公式中，通常都使用單元格區域引用，也可以直接輸入數值數組。直接輸入的數值數組被稱為數組常量。當不想在工作表中逐個單元格輸入數值時，可以使用這種方法來建立數組常量。

可以使用以下的方法來建立數組中的數組常量：直接在公式中輸入數值，并且用大括號“{}”括起來，需要注意的是，把不同列的數值用逗號“, ”分開，不同行的數值用分號“; ”分開。例如，如果要表示一行中的100、200、300和下一行中的400、500、600，應該輸入一個2行3列的數組常量{100,200,300;400,500,600}。

在實際應用中，先選中一個2行3列的矩形區域，然后輸入公式={100,200,300;400,500,600}，按Shift + Ctrl + Enter快捷鍵結束輸入，則在這個2行3列的矩形區域即可一次得到所需要的數值，如圖2-84所示。

數組常量有其輸入的規范，因此，無論在單元格中輸入數組常量還是直接在公式中輸入數組常量，并非隨便輸入一個數值或者公式就可以了。

在Excel中，使用數組常量時應該注意以下規定。

數組常量中不能含有單元格引用，并且數組常量的列或者行的長度必須相等。

數組常量可以包括數字、文本、邏輯值FALSE和TRUE以及錯誤值，如“#NAME? ”。

數組常量中的數字可以是整數、小數或者科學記數公式。

在同一數組中可以有不同類型的數值，如{1,2, “A”, TRUE}。

數組常量中的數值不能是公式，必須是常量，并且不能含有$、（　）或者%。

文本必須包含在雙引號內，如“CLASSROOMS”。