第14章應(yīng)用公式及函數(shù)

1、應(yīng)用公式與函數(shù)應(yīng)用公式與函數(shù)學(xué)習(xí)目標(biāo)學(xué)習(xí)目標(biāo)在EXCEL 2010中，數(shù)據(jù)的計算是相當(dāng)重要的一項(xiàng)功能，對于簡單的計算，用戶可以自己寫出公式來求計算結(jié)果，對于復(fù)雜的計算，EXCEL 2010都給出了相應(yīng)的函數(shù)，用戶只要輸入函數(shù)中的參數(shù)，便可直接求出結(jié)果。另外，運(yùn)算符的類型以及運(yùn)算的優(yōu)先級，在使用公式或者函數(shù)計算數(shù)據(jù)時也起到了關(guān)鍵的作用。 本章重點(diǎn)本章重點(diǎn) 單元格的引用 應(yīng)用公式 審核公式 應(yīng)用函數(shù)14.1 單元格的引用單元格的引用 在使用公式進(jìn)行數(shù)據(jù)計算時，除了可以直接使用常量數(shù)據(jù)之外，還可以引用單元格。例如：公式=A1+B3-728中，引用了單元格A1和B3，同時還使用了常量728。引用單元格

2、是通過特定的單元格符號來標(biāo)識工作表上的單元格或單元格區(qū)域，指明公式中所使用的數(shù)據(jù)位置。通過單元格的引用，可以在公式中使用工作表中不同單元格的數(shù)據(jù)，或者在多個公式中使用同一單元格的數(shù)值。還可以引用同一工作簿不同工作表的單元格、不同工作簿的單元格，甚至其他應(yīng)用程序中的數(shù)據(jù)。 14.1.1 按地址引用單元格按地址引用單元格是指在Excel工作表的行采用數(shù)字（1、2、3）編號，列采用字母（A、B、C）編號，然后使用單元格所在的行列編號來表示單元格。14.1.2 相對、絕對和混合引用在Excel中有3種不同的引用類型：絕對引用、相對引用和混合引用。它們之間既有區(qū)別，又有聯(lián)系。1. 相對相對引用引用：單元

3、格的相對引用是指在生成公式時，對單元格或單元格區(qū)域的引用基于它們與公式單元格的相對位置。使用相對引用后，系統(tǒng)將會記住建立公式的單元格和被引用的單元格的相對位置關(guān)系，在粘貼這個公式時，新的公式單元格和被引用的單元格仍保持這種相對位置。2. 絕對絕對引用引用：單元格的絕對引用是指在生成公式時，對單元格或單元格區(qū)域的引用是單元格的絕對位置。不論包含公式的單元格處在什么位置，公式中所引用的單元格位置都不會發(fā)生改變。3. 混合混合引用引用：混合引用是指行采用相對引用而列采用絕對引用，或是列采用相對引用而行采用絕對引用。絕對引用列采用$A1、$B1等形式；絕對引用行采用A$1、B$1等形式。14.1.3

4、引用操作在了解了單元格引用方法后，就可以在公式中對單元格進(jìn)行引用。有以下兩種方法。直接在公式中輸入引用單元格。使用鼠標(biāo)框選引用單元格或單元格區(qū)域。14.2 應(yīng)用公式應(yīng)用公式公式是在制作電子表格時常用的內(nèi)容，本節(jié)將對公式的內(nèi)容進(jìn)行具體的介紹，其中包括公式含義、公式的運(yùn)算符號、輸入公式的方法、數(shù)組公式和編輯公式的操作等。14.2.1 公式的概述公式是指使用運(yùn)算符和函數(shù)，對工作表數(shù)據(jù)以及普通常量進(jìn)行運(yùn)算的方程式。在工作表中，可以使用公式和函數(shù)對表格中原始數(shù)據(jù)進(jìn)行處理。通過公式以及在公式中調(diào)用函數(shù)，除了可以進(jìn)行簡單的數(shù)據(jù)計算（如加、減、乘、除）外，還可以完成較為復(fù)雜的財務(wù)、統(tǒng)計及科學(xué)計算等。一個完整的

5、公式由以下幾部分組成。等號“=”：相當(dāng)于公式的標(biāo)記，表示之后的字符為公式。運(yùn)算符：表示運(yùn)算關(guān)系的符號，如加號“+”、引用符號“：”。函數(shù)：一些預(yù)定義的計算關(guān)系，可將參數(shù)按特定的順序或結(jié)構(gòu)進(jìn)行計算，如求和函數(shù)sum。單元格引用：參與計算的單元格或單元格區(qū)域，如單元格A1。常量：參與計算的常數(shù)，如數(shù)字2。14.2.2 公式所用的運(yùn)算符在Excel中，運(yùn)算符是指在公式中用于進(jìn)行計算的加、減、乘、除，以及其他運(yùn)算符等符號。運(yùn)算符可以分為算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。在公式中使用不同類型的運(yùn)算符，產(chǎn)生的結(jié)果截然不同。1. 運(yùn)算符運(yùn)算符分類分類2. 運(yùn)算符的運(yùn)算符的優(yōu)先級優(yōu)先級14.2.

6、3 輸入公式選擇需要輸入公式的單元格，然后輸入“=”作為公式的開始，再輸入公式中的其他元素，如“=5+25”，單擊“輸入”按鈕 或按Enter鍵，計算結(jié)果即可顯示在所選單元格中，在編輯欄中將顯示公式內(nèi)容。14.2.4 應(yīng)用數(shù)組公式選擇需建立數(shù)組公式的單元格區(qū)域。輸入等號（=），開始建立公式。在工作表中，指定引用的單元格和輸入公式運(yùn)算符，公式輸入完成后，按下Ctrl+Shift+Enter組合鍵，即可計算出結(jié)果。14.2.5 修改公式在輸入公式后，如果要對公式進(jìn)行修改，可以采用以下兩種常用的方法。雙擊公式所在的單元格，將光標(biāo)移動要修改對象的位置，刪除不需要的內(nèi)容，或輸入補(bǔ)充的內(nèi)容。選擇公式所在的

7、單元格，在編輯欄中對公式進(jìn)行修改。14.2.6 復(fù)制公式如果要在多個單元格中使用相同的公式，可以通過復(fù)制公式的方法快速完成操作。復(fù)制公式的方法通常包括如下幾種方法。1. 使用使用剪貼板剪貼板2. 使用填充柄使用填充柄 3. 使用快捷鍵使用快捷鍵14.2.7 移動公式移動公式是將創(chuàng)建的公式移動到其他位置，其操作與復(fù)制公式的操作相似。用戶可以使用如下兩種方式移動公式。選擇要移動的單元格，單擊“剪貼板”選項(xiàng)組中的“剪切”按鈕 ，然后選擇將公式移動到的單元格，單擊“剪貼板”選項(xiàng)組中的“粘貼”按鈕 ，即可將公式移動到該單元格中。選擇要移動公式的單元格，按下Ctrl+X組合鍵剪切公式，然后選擇要移動到的單

8、元格，按下Ctrl+V組合鍵進(jìn)行粘貼，完成公式的移動操作。14.3 審核公式審核公式在Excel中輸入的公式如果不符合正確的格式或出現(xiàn)了其他錯誤內(nèi)容，公式的計算結(jié)果就顯示不出來，并且在單元格中會顯示錯誤的信息。14.3.1 顯示/隱藏公式在輸入公式后，Excel將自動計算其結(jié)果，并在單元格中隱藏公式的內(nèi)容。用戶可通過如下方法顯示公式的內(nèi)容。選擇“公式”選項(xiàng)卡，單擊“公式審核”選項(xiàng)組中的“顯示公式”按鈕 ，即可在工作表中顯示存在的公式，當(dāng)用戶再次單擊“顯示公式”按鈕 時，將隱藏公式并顯示公式的結(jié)果。14.3.2 查詢公式錯誤在輸入的公式如果出現(xiàn)了錯誤，會造成公式的計算錯誤。不同原因造成的公式錯誤

9、，產(chǎn)生的結(jié)果也不一樣，下面列舉了產(chǎn)生錯誤公式的各種樣式和相應(yīng)的原因。“#！”：公式計算出的結(jié)果長度超出了單元格的寬度，只需增加單元格列寬即可?！?DIV/0”：除數(shù)為0，當(dāng)單元格里為空時，在進(jìn)行除法運(yùn)算時，就會出現(xiàn)該錯誤。“#N/A”：缺少函數(shù)參數(shù)，或者沒有可用的數(shù)值，產(chǎn)生這個錯誤的原因，往往是因?yàn)檩斎敫袷讲粚?。?NAME?”：公式中引用了無法識別的成分，當(dāng)公式中使用的名稱被刪除時，常會產(chǎn)生這個錯誤。“#NULL”：使用了不正確的單元格或單元格區(qū)域引用。“#NUM！”：在需要輸入數(shù)字的函數(shù)中，輸入了其他格式的參數(shù)，或者輸入的數(shù)字超出了函數(shù)范圍?！?REF！”：引用了一個無效的單元格，當(dāng)該單元

10、格被刪除時，就會產(chǎn)生該錯誤。“#VALUE！”：公式中的參數(shù)產(chǎn)生了運(yùn)算錯誤，或者參數(shù)的類型不正確。14.3.3 檢查公式錯誤Excel會自動對輸入的公式進(jìn)行檢查，如果發(fā)生錯誤，在單元格的左上角會出現(xiàn)一個綠色的小三角，選中該單元格，還會出現(xiàn) 按鈕，單擊該按鈕，會彈出一個快捷提示菜單，提供解決這個錯誤的途徑。14.3.4 追蹤單元格在查找公式中的錯誤時，可以通過查找與公式相關(guān)的單元格，查看該公式引用的單元格中是否存在錯誤。1. 1. 追蹤引用追蹤引用單元格單元格：選擇包含公式的單元格，單擊“公式審核”選項(xiàng)組中的“追蹤引用單元格”按時，系統(tǒng)將用藍(lán)色追蹤箭頭和邊框在工作表中指明為公式提供數(shù)據(jù)的單元格。

11、2. 2. 追蹤從屬追蹤從屬單元格單元格：選擇一個具有數(shù)據(jù)的單元格，單擊“公式審核”選項(xiàng)組的“追蹤從屬單元格”按鈕，系統(tǒng)將用藍(lán)色追蹤箭頭指明該單元格被哪些單元格中的公式所引用。14.3.5 監(jiān)視窗口用戶可以在“監(jiān)視窗口”中監(jiān)視工作表中不可見的單元格，從而方便在龐大的工作表中檢查、審核或確認(rèn)公式計算及其結(jié)果1. 在在“監(jiān)視窗口監(jiān)視窗口”中添加中添加單元格單元格：單擊“公式審核”選項(xiàng)組中的“監(jiān)視窗口”按鈕，在彈出的“監(jiān)視窗口”對話框中單擊“添加監(jiān)視”按鈕，并通過“添加監(jiān)視點(diǎn)”對話框添加監(jiān)視點(diǎn)。2. 在在“監(jiān)視窗口監(jiān)視窗口”中刪除中刪除單元格單元格：在“監(jiān)視窗口”對話框中選擇要刪除監(jiān)視的單元格，然后

12、單擊“刪除監(jiān)視”按鈕，即可將選擇的單元格從“監(jiān)視窗口”對話框中刪除。14.3.6 公式求值使用公式求值功能可以對公式中每個部分單獨(dú)求值，從而對公式進(jìn)行調(diào)試，該功能特別適用于復(fù)雜的公式。選擇要調(diào)試公式的單元格，然后單擊“審核公式”選項(xiàng)組中的“公式求值”按鈕 ，在打開的“公式求值”對話框中單擊“求值”按鈕，可以對公式中畫橫線的部分進(jìn)行計算求值。14.4 應(yīng)用函數(shù)應(yīng)用函數(shù)Excel中所提的函數(shù)其實(shí)是一些預(yù)定義的公式，它們使用一些稱為參數(shù)的特定數(shù)值按特定的順序或結(jié)構(gòu)進(jìn)行計算。可以直接用它們對某個區(qū)域內(nèi)的數(shù)值進(jìn)行一系列處理，如分析、處理日期值和時間值等。 14.4.1 函數(shù)的概述函數(shù)是由Excel內(nèi)部定

13、義的、完成特定計算的公式。例如，要求單元格A1到H1中一系列數(shù)字之和，可以輸入函數(shù)=SUM(A1:H1)，而不是輸入公式=A1+B1+C1+H1。函數(shù)可以使用范圍引用（如SALES）及數(shù)字值（如58.64）。用戶要使用函數(shù)時，可以在單元格中直接輸入函數(shù)，也可以使用函數(shù)向?qū)Р迦牒瘮?shù)。每個函數(shù)都由下面3種元素構(gòu)成。“”等：表示后面跟著函數(shù)（公式）。函數(shù)名（如SUM）：表示將執(zhí)行的操作。變量（如A1:H1）：表示函數(shù)將作用的值的單元格地址。變量通常是一個單元格區(qū)域，還可以表示為更為復(fù)雜的內(nèi)容。14.4.2 函數(shù)的分類在Excel中提供了幾百個預(yù)定義的函數(shù)供用戶使用，其中分為11大類型，如數(shù)學(xué)和三角函數(shù)、文本函數(shù)、財務(wù)函數(shù)、邏輯函數(shù)、統(tǒng)計函數(shù)、日期和時間函數(shù)等。常用的函數(shù)包括如下幾類。1. 數(shù)學(xué)和數(shù)學(xué)和三角函數(shù)三角函數(shù)2. 邏輯函數(shù)邏輯函數(shù)3. 文本文本函數(shù)函數(shù)4. 日期和時間日期和時間函數(shù)函數(shù)5. 統(tǒng)計函數(shù)統(tǒng)計函數(shù)14.4.3 輸入函數(shù)函數(shù)是按照特定的語法順序進(jìn)行運(yùn)算的。函數(shù)的語法是以函數(shù)名開始的，在函數(shù)名后面是括號，括號之間代表著該函數(shù)的參數(shù)。函數(shù)的輸入包括手工輸入、使用函數(shù)向?qū)л斎?、使使函?shù)列表輸入和編輯欄函數(shù)按鈕輸入幾種方式。1.