第5章公式與函數(shù)的使用

1、第5章 公式與函數(shù)的使用 Excel強(qiáng)大的計算功能主要依賴于公式和函數(shù)，利用公式和函數(shù)可以對表格中的數(shù)據(jù)進(jìn)行各種計算和處理操作，從而提高我們在制作復(fù)雜表格時的工作效率及計算準(zhǔn)確率。 本章內(nèi)容提要 使用公式 公式中的應(yīng)用設(shè)置 公式中的錯誤和審核使用函數(shù)數(shù)組公式及其應(yīng)用 5.1 使用公式使用公式 公式是工作表中用于對單元格數(shù)據(jù)進(jìn)行各種運(yùn)算的等式，它必須以等號“=”開頭。一個完整的公式，通常由運(yùn)算符和操作數(shù)組成。運(yùn)算符可以是算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符；操作數(shù)可以是常量、單元格地址和函數(shù)等。 5.1.1 公式中的運(yùn)算符公式中的運(yùn)算符 運(yùn)算符是用來對公式中的元素進(jìn)行運(yùn)算而規(guī)定的特殊符

2、號。 1算術(shù)運(yùn)算符算術(shù)運(yùn)算符 算術(shù)運(yùn)算符的作用是完成基本的數(shù)學(xué)運(yùn)算，并產(chǎn)生數(shù)字結(jié)果。 2比較運(yùn)算符比較運(yùn)算符 比較運(yùn)算符的作用是可以比較兩個值，結(jié)果為一個邏輯值，不是“TRUE（真）”，就是“FALSE（假）”。 3文本運(yùn)算符文本運(yùn)算符 使用文本運(yùn)算符“”（與號）可將兩個或多個文本值串起來產(chǎn)生一個連續(xù)的文本值。例如：輸入“2010年”“上海世博會”會生成“2010年上海世博會”。 4引用運(yùn)算符引用運(yùn)算符 引用運(yùn)算符可以將單元格區(qū)域進(jìn)行合并計算。 5.1.2 運(yùn)算符的優(yōu)先級運(yùn)算符的優(yōu)先級 若要更改求值的順序，可以將公式中要先計算的部分用括號括起來 5.1.3 創(chuàng)建和編輯公式創(chuàng)建和編輯公式 1創(chuàng)建

3、公式創(chuàng)建公式 對于簡單的公式，我們可以直接在單元格中輸入：首先單擊需輸入公式的單元格，接著輸入=（等號），然后輸入公式內(nèi)容，最后單擊編輯欄上的“輸入”按鈕 或按【Enter】鍵結(jié)束。 若要在編輯欄中輸入公式，可單擊要輸入公式的單元格，然后單擊編輯欄，依次在編輯欄中輸入等號“=”，操作數(shù)和運(yùn)算符，輸入完畢，按下【Enter】鍵或單擊編輯欄上的“輸入”按鈕 。 2編輯公式編輯公式 要修改公式，可單擊含有公式的單元格，然后在編輯欄中進(jìn)行修改，修改完畢按【Enter】鍵即可。 5.1.4 移動和復(fù)制公式移動和復(fù)制公式 1移動公式移動公式 要移動公式，最簡單的方法就是：選中包含公式的單元格，將鼠標(biāo)指針移

4、到單元格的邊框線上，當(dāng)鼠標(biāo)指針變成十字箭頭 形狀時，按住鼠標(biāo)左鍵不放，將其拖到目標(biāo)單元格后釋放鼠標(biāo)即可。 2復(fù)制公式復(fù)制公式 在Excel中，復(fù)制公式可以使用填充柄，也可以使用復(fù)制、粘貼命令。在復(fù)制公式的過程中，一般情況下，系統(tǒng)會自動地改變公式中引用的單元格地址。 當(dāng)我們想將某個單元格中的公式復(fù)制到同列（行）中相鄰的單元格時，可以通過拖動“填充柄”來快速完成。方法是：按住鼠標(biāo)左鍵向下（也可以是上、左或右，據(jù)實際情況而定）拖動要復(fù)制公式的單元格右下角的填充柄，到目標(biāo)位置后釋放鼠標(biāo)即可。 利用填充柄復(fù)制公式 5.1.5 自動求和功能自動求和功能 當(dāng)對一行或一列數(shù)據(jù)進(jìn)行求和時，首先要在該行的右側(cè)或該

5、列的下方選定一個空白單元格，然后單擊“常用”工具欄中的自動求和按鈕 ，最后按一下回車鍵，結(jié)果就會出現(xiàn)在所選定的單元格中。 5.2 公式中的引用設(shè)置公式中的引用設(shè)置 引用的作用是通過標(biāo)識工作表中的單元格或單元格區(qū)域，來指明公式中所使用的數(shù)據(jù)的位置。 5.2.1 相對引用、絕對引用和混合引用相對引用、絕對引用和混合引用 1相對引用相對引用 相對引用指的是單元格的相對地址，其引用形式為直接用列標(biāo)和行號表示單元格，例如B5，或用引用運(yùn)算符表示單元格區(qū)域，如B5：D15。如果公式所在單元格的位置改變，引用也隨之改變。默認(rèn)情況下，公式使用相對引用。 從編輯欄中可看出：引用的單元格區(qū)域發(fā)生改變 引用單元格區(qū)

6、域時，應(yīng)先輸入單元格區(qū)域起始位置的單元格地址，然后輸入引用運(yùn)算符，再輸入單元格區(qū)域結(jié)束位置的單元格地址。 2絕對引用絕對引用 絕對引用指引用單元格的精確地址，與包含公式的單元格位置無關(guān)，其引用形式為在列標(biāo)和行號的前面都加上“$”號。則不管將公式復(fù)制或移動到什么位置，引用的單元格地址的行和列都不會改變。 3混合引用混合引用 引用中既包含絕對引用又包含相對引用的稱為混合引用，如A$1或$A1等，用于表示列變行不變或列不變行變的引用。 如果公式所在單元格的位置改變，則相對引用改變，而絕對引用不變。 5.2.2 引用不同工作表間的單元格引用不同工作表間的單元格 在同一工作簿中，不同工作表中的單元格可以

7、相互引用，它的表示方法為：“工作表名稱!單元格或單元格區(qū)域地址”。 5.2.3 引用不同工作簿間的單元格引用不同工作簿間的單元格 在當(dāng)前工作表中引用不同工作簿中的單元格的表示方法為： 5.3 公式中的錯誤和審核公式中的錯誤和審核 5.3.1 公式中返回的錯誤代碼含義公式中返回的錯誤代碼含義 （1 1）錯誤值：）錯誤值： 含義：輸入到單元格中的數(shù)據(jù)太長或單元格公式所產(chǎn)生的結(jié)果太大，使結(jié)果在單元格中顯示不下，或是對日期和時間格式的單元格做減法，出現(xiàn)了負(fù)值。 解決辦法：增加列的寬度，使結(jié)果能夠完全顯示。如果是由日期或時間相減產(chǎn)生了負(fù)值引起的，可以改變單元格的格式，比如改為文本格式。（2 2）錯誤值：

8、）錯誤值：DIV/0!DIV/0! 含義：試圖除以0。這個錯誤的產(chǎn)生通常有下面幾種情況：除數(shù)為0、在公式中除數(shù)使用了空單元格或是包含零值單元格的單元格引用。 解決辦法：修改單元格引用，或者在用作除數(shù)的單元格中輸入不為零的值。（3 3）錯誤值：）錯誤值：VALUE!VALUE! 含義：輸入引用文本項的數(shù)學(xué)公式。如果使用了不正確的參數(shù)或運(yùn)算符，或者當(dāng)執(zhí)行自動更正公式功能時不能更正公式，都將產(chǎn)生錯誤信息VALUE!。 解決辦法：應(yīng)確認(rèn)公式或函數(shù)所需的運(yùn)算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。例如，單元格C4中有一個數(shù)字或邏輯值，而單元格D4包含文本，則在計算公式=C4D4時，系統(tǒng)不能將

9、文本轉(zhuǎn)換為正確的數(shù)據(jù)類型，因而返回錯誤值VALUE!。（4 4）錯誤值：）錯誤值：REF!REF! 含義：刪除了被公式引用的單元格范圍。 解決辦法：恢復(fù)被引用的單元格范圍，或是重新設(shè)定引用范圍。（5 5）錯誤值：）錯誤值：N/AN/A 含義：無信息可用于所要執(zhí)行的計算。在建立模型時，用戶可以在單元格中輸入#N/A，以表明正在等待數(shù)據(jù)。任何引用含有#N/A值的單元格都將返回#N/A。 解決辦法：在等待數(shù)據(jù)的單元格內(nèi)填上數(shù)據(jù)。（6 6）錯誤值：）錯誤值：NUM!NUM! 含義：提供了無效的參數(shù)給工作表函數(shù)，或是公式的結(jié)果太大或太小而無法在工作表中表示。 解決辦法：確認(rèn)函數(shù)中使用的參數(shù)類型正確。如果

10、是公式結(jié)果太大或太小，就要修改公式。（7 7）錯誤值：）錯誤值：NAME?NAME? 含義：在公式中使用了Excel不能識別的文本。例如，可能是輸錯了名稱，或是輸入了一個已刪除的名稱。另外，如果沒有將文字串括在雙引號中，也會產(chǎn)生此錯誤值。 解決辦法：如果是使用了不存在的名稱而產(chǎn)生這類錯誤，應(yīng)確認(rèn)使用的名稱確實存在；如果是函數(shù)名拼寫錯誤，改正過來就可以了；將文本串括在雙引號中；確認(rèn)公式中使用的所有區(qū)域引用都使用了冒號（:），如SUM（C1:C10）。（8 8）錯誤值：）錯誤值：NULLNULL！ 含義：在公式中的兩個范圍之間插入一個空格以表示交叉點，但這兩個范圍沒有公共單元格。例如，輸入“=SU

11、M(A1:A10 C1:C10)”，就會產(chǎn)生這種情況。 解決辦法：改正區(qū)域運(yùn)算符使之正確；更改引用使之相交。例如，在兩個區(qū)域之間用逗號隔開“=SUM(A1:A10，C1:C10)”。5.3.2 公式審核公式審核 1追蹤單元格追蹤單元格 u追蹤引用單元格 Excel可以追蹤公式中引用的單元格（即指明所選單元格中的數(shù)據(jù)是由哪幾個單元格中的數(shù)據(jù)通過公式計算得出的），并以箭頭的形式標(biāo)識引用的單元格。 u追蹤從屬單元格 從屬單元格是指使用所選單元格值的單元格，從屬單元格通常包含公式或常數(shù)。 2取消追蹤取消追蹤 要取消所有追蹤箭頭，可選擇“工具”“公式審核”“取消所有追蹤箭頭”菜單或單擊“公式審核”工具欄

12、中的“取消所有追蹤箭頭”按鈕 。 3顯示公式顯示公式 要顯示單元格中的公式，可單擊工作表中的任意單元格，然后選擇“工具”“公式審核”“公式審核模式”菜單，單元格中顯示計算的公式。 再次選擇該菜單，可在單元格中顯示計算結(jié)果4追蹤錯誤追蹤錯誤 Excel 2003會自動對輸入的公式進(jìn)行檢查，若發(fā)生錯誤，在單元格的左上角會出現(xiàn)一個綠色的小三角。此時選中含有錯誤值的單元格，然后選擇“工具”“公式審核”“追蹤錯誤”菜單，即可標(biāo)識出產(chǎn)生錯誤的單元格 若單擊含有錯誤的單元格，會在該單元格左側(cè)出現(xiàn) 按鈕。單擊 按鈕，會彈出快捷菜單，提供解決此錯誤的途徑。 5逐步求值逐步求值 在Excel中，可以使用“公式求值

13、”對話框驗證所選單元格中公式的運(yùn)算過程，從而找出發(fā)生錯誤的原因。 單擊“求值”按鈕，在“求值”編輯框中即可顯示帶下劃線的表達(dá)式結(jié)果，并且結(jié)果也用下劃線顯示。多次單擊“求值”按鈕，“求值”編輯框中將逐步顯示計算結(jié)果6監(jiān)視公式監(jiān)視公式 使用Excel 2003的“監(jiān)視窗口” 工具能將另一工作表內(nèi)與編輯內(nèi)容相關(guān)聯(lián)的數(shù)據(jù)，開啟成一個可移動的小窗口，若更改現(xiàn)有的工作表內(nèi)容，監(jiān)視窗口中關(guān)聯(lián)的內(nèi)容也會跟著改正。 打開監(jiān)視窗口 添加第一個監(jiān)視點 添加第二個監(jiān)視點 修改數(shù)值后的監(jiān)視窗口 刪除監(jiān)視點 5.4 使用函數(shù)使用函數(shù) 函數(shù)是預(yù)先定義好的表達(dá)式，它必須包含在公式中。每個函數(shù)都由函數(shù)名和參數(shù)組成，其中函數(shù)名表

14、示將執(zhí)行的操作，參數(shù)表示函數(shù)將作用的值的單元格地址，通常是一個單元格區(qū)域，也可以是更為復(fù)雜的內(nèi)容。在公式中合理地使用函數(shù)，可以完成如求和、邏輯判斷、財務(wù)分析等眾多數(shù)據(jù)處理功能。 5.4.1 函數(shù)的分類函數(shù)的分類 常用的函數(shù)類型和使用范例 5.4.2 函數(shù)的使用方法函數(shù)的使用方法 使用函數(shù)時，應(yīng)首先確認(rèn)已在單元格中輸入了“=”號，即已進(jìn)入公式編輯狀態(tài)。接下來可輸入函數(shù)名稱，再緊跟著一對括號，括號內(nèi)為一個或多個參數(shù)，參數(shù)之間要用逗號來分隔。 1手工輸入函數(shù)手工輸入函數(shù) 單擊要輸入函數(shù)的單元格，然后依次輸入等號、函數(shù)名、左括號、具體參數(shù)和右括號，最后單擊編輯欄中的“輸入”按鈕或按【Enter】鍵，此

15、時在輸入函數(shù)的單元格中將顯示運(yùn)算結(jié)果。 2函數(shù)向?qū)л斎牒瘮?shù)函數(shù)向?qū)л斎牒瘮?shù) 如果不能確定函數(shù)的拼寫或參數(shù)，可以使用函數(shù)向?qū)л斎牒瘮?shù)。 5.5 數(shù)組公式及其應(yīng)用數(shù)組公式及其應(yīng)用 數(shù)組公式可以對兩組或兩組以上的數(shù)據(jù)（兩個或兩個以上的單元格區(qū)域）同時進(jìn)行計算。在數(shù)組公式中使用的數(shù)據(jù)稱為數(shù)組參數(shù)，數(shù)組參數(shù)可以是一個區(qū)域數(shù)組，也可以是常量數(shù)組。 5.5.1 數(shù)組公式的建立方法數(shù)組公式的建立方法 首先選定用來存放結(jié)果的單元格或單元格區(qū)域（如果數(shù)組公式將返回一個結(jié)果，單擊需要輸入數(shù)組公式的單元格；如果數(shù)組公式將返回多個結(jié)果，則要選定需輸入數(shù)組公式的單元格區(qū)域），然后輸入公式，最后按【Ctrl+Shift+E

16、nter】組合鍵生成數(shù)組公式。 1用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積用數(shù)組公式計算兩個數(shù)據(jù)區(qū)域的乘積 2用數(shù)組公式計算多列數(shù)據(jù)之和用數(shù)組公式計算多列數(shù)據(jù)之和 5.5.2 使用數(shù)組公式的規(guī)則使用數(shù)組公式的規(guī)則 u輸入數(shù)組公式時，首先選擇用來保存計算結(jié)果的單元格區(qū)域，如果將產(chǎn)生多個計算結(jié)果，必須選擇一個與計算結(jié)果所需大小和形狀都相同的單元格區(qū)域。u數(shù)組公式輸入完成后，按下【Ctrl+Shift+Enter】組合鍵，這時在編輯欄中可以看見公式的兩邊自動加上了花括號，表示該公式是一個數(shù)組公式。 不要自己鍵入花括號，否則，Excel認(rèn)為輸入的是一個正文標(biāo)簽。 u在數(shù)組公式所涉及的區(qū)域中，不能編輯、清除或移動單個單元格，也不能插入或刪除其中任何一個單元格，即數(shù)組公式所涉及的單元格區(qū)域只能作為一個整體